[dtp-dev] Fw: Exemplary Derby Routine Editor first version is pending to

[Der Ping Chou <dpchou@xxxxxxxxxx>]

Re-forward it to DTP.

Der-Ping Chou
Information Management Tooling
Development Manager - Data Tools
Seattle IBM Office
tel : 1-206-587-5946 (T/L: 277-5946)

----- Forwarded by Der
Ping Chou/Redmond/IBM on 03/02/2006 05:10 PM -----

Hemant S Kolwalkar/Redmond/IBM

03/02/2006 04:59 PM

To	Hui.Cao@xxxxxxxxxx
cc	Brian Payton/Santa Teresa/IBM@IBMUS, Der Ping Chou/Redmond/IBM@IBMUS, dtp-dev@xxxxxxxxxxx, Hung.Hsi@xxxxxxxxxx
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commitLink

Hi Hui,

i have extracted common properties of
Event from various vendor manuals. the Event class we will add to SQLModel
will end up as follows:

Event
---------
Name          
      String          
      (inherited from SQLObject)
Comment        String
               (inherited
from SQLObject)

For        
       String        
       (type of object that the event applies)
Condition        String
               (condition
to be satisfied inorder to activate the event)
Action        
       String        
       (what action the event performs after
being activated)
Enabled        boolean
       (event state: enabled or disabled)

The Event class will be separate from
Routine. The container for the Event will be the Database.

About the relationship between Privilege
and SQLObject, pl see snapshot below from the Privileges diagram in the
AccessControl package. does that help you? Routine extends from SQLObject.

thanks,

- Hemant

Hui.Cao@xxxxxxxxxx

03/01/2006 10:03 PM

To	Hemant S Kolwalkar/Redmond/IBM@IBMUS
cc	Brian Payton/Santa Teresa/IBM@IBMUS, Der Ping Chou/Redmond/IBM@IBMUS, dtp-dev@xxxxxxxxxxx, Hung.Hsi@xxxxxxxxxx
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Hi Hemant, 
        Thanks for your explanation. 
        I know there're variations across vendors as
to the Event object, but is it possible to define the least denominator
in the base SQLModel? Even without additional attributes for Event is fine,
as long as we agree that Event is a kind of Routine and will be implemented
by (some) vendors. Then vendor specific model extensions can come into
play by extending the base Event object. 

        I fail to see how User and Priviledge are associated
with SQLObject, but I can see that they are associated with Schema. Can
you give me additional info about this? Thanks! 
        

Best Regards!

Max ( Hui ) Cao
DTP SQL Dev Tools Committer
Sr S/W Eng - Dev
Sybase, Inc. Shanghai, China

Hemant S Kolwalkar <hskolwal@xxxxxxxxxx>

03/02/2006 08:14 AM

To	Hui.Cao@xxxxxxxxxx
cc	Brian Payton <bpayton@xxxxxxxxxx>, Der Ping Chou <dpchou@xxxxxxxxxx>, dtp-dev@xxxxxxxxxxx, Hung.Hsi@xxxxxxxxxx
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Hi Hui, 

thanks for the links. they were quite helpful. 

i see the CREATE EVENT DDL, although similar across the different vendors,
has a number of fine variations. 

whenever there have been variations in the DDL syntax of a catalog object
across vendors, we have pushed the object's definition to a vendor specific
model extension. your tool will need to have some kind of pluggable architecture
so it knows how to handle vendor specific model extensions containing objects
which are not defined in the SQLModel. 

about the access control package. it defines a User who can have Privileges
and a Privilege can be associated with SQLObject, which is root class of
Routine and Trigger. i am not familiar with your use-case for "sourceVisible"
but i would suggest you take a look to see if the User/Privilege objects
can be leveraged to satisfy your need. 

if it need be, we can help to create a vendor specific model extension
for Derby. 

thanks, 

- Hemant

Hui.Cao@xxxxxxxxxx

02/28/2006 05:39 PM

To	Hemant S Kolwalkar/Redmond/IBM@IBMUS
cc	Brian Payton/Santa Teresa/IBM@IBMUS, Der Ping Chou/Redmond/IBM@IBMUS, dtp-dev@xxxxxxxxxxx, Hung.Hsi@xxxxxxxxxx
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Hi Hemant, 
      Besides MySQL, Sybase ASA also supports "CREATE
EVENT" statement. IBM DB2 has a similiar "CREATE EVENT MONITOR"
statement and Microsoft SQL Server has a "CREATE EVENT NOTIFICATION"
statement. Although the syntax and function may vary, all these "Event"s
do provide additional notification mechanism in response to some trace
events other than DML and DDL statements, which are handled by "Trigger"s.
Here are some url links: 
http://manuals.sybase.com/onlinebooks/group-sas/awg0703e/dbugen7/@Generic__BookTextView/40444
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000915.htm
http://www.informit.com/articles/article.asp?p=327394&seqNum=5&rl=1
      As to "sourceVisible", you're right, it's
related to user privileges. Thanks for pointing out the access control
package. I just don't know how the access control package associates with
Routine and Trigger objects.   

Thanks & Regards!

Max ( Hui ) Cao
DTP SQL Dev Tools Committer
Sr S/W Eng - Dev
Sybase, Inc. Shanghai, China

Hemant S Kolwalkar <hskolwal@xxxxxxxxxx>

03/01/2006 08:47 AM

To	Hui.Cao@xxxxxxxxxx
cc	Brian Payton <bpayton@xxxxxxxxxx>, Der Ping Chou <dpchou@xxxxxxxxxx>, dtp-dev@xxxxxxxxxxx
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Hi Hui, 

this is about Bugzilla Bug 129706Extend SQLModel: Event and Routine.sourceVisible.

except for MySQL, i could not find any other database vendor having Event
as a specific catalog object (there is a CREATE EVENT statement
in MySQL). Most vendors use the Trigger for events.

as far as "sourceVisible" goes, what is the use-case for this?
could you elaborate more ? is "sourceVisible" related to user
privileges? we do have an access control package in the SQLModel where
privileges, etc are defined. 

thanks, 

- Hemant

Hui.Cao@xxxxxxxxxx

02/27/2006 08:01 PM

To	Der Ping Chou/Redmond/IBM@IBMUS
cc	Brian Payton/Santa Teresa/IBM@IBMUS, dtp-dev@xxxxxxxxxxx, Hemant S Kolwalkar/Redmond/IBM@IBMUS
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Hi Der Ping, 
    I've verified the code you just checked in for bug 109112.
It's OK. 

    My proposed SQLModel extension is not in SQL 99 or 2003 standards.
I'm also curious to know whether all the SQL models are defined in SQL
standards. Thanks! 

Best Regards!

Max ( Hui ) Cao
DTP SQL Dev Tools Committer
Sr S/W Eng - Dev
Sybase, Inc. Shanghai, China

Der Ping Chou <dpchou@xxxxxxxxxx>

02/28/2006 10:59 AM

To	Hui.Cao@xxxxxxxxxx
cc	dtp-dev@xxxxxxxxxxx, Hemant S Kolwalkar <hskolwal@xxxxxxxxxx>, Brian Payton <bpayton@xxxxxxxxxx>
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Hui, 

Appreciated that you pointed out this is generic to all major database
vendor. It is actually a preferable approach to add this into SQLModel
if they fall into this category. Just curious to know if this also part
of the SQL 99 or 2003 specification that we missed.

Have you have a chance to verify the code that were checked in so far?

Thanks, 

Der-Ping Chou
Information Management Tooling
Development Manager - Data Tools
Seattle IBM Office
tel : 1-206-587-5946 (T/L: 277-5946)

Hui.Cao@xxxxxxxxxx

02/27/2006 06:34 PM

To	Der Ping Chou/Redmond/IBM@IBMUS, dtp-dev@xxxxxxxxxxx
cc
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Thanks Der Ping, 
  I've just create a new bug for the SQLModel part: bug129706: "Extend
SQLModel: Event and Routine.sourceVisible". 
  I think deriving a new specialized Derby model from SQLModel is
a great idea. This extension mechanism will be very useful. But for this
bug, We expect them to be added into the base model instead of into a separate
derived model since they look generic to all databases. In this way, SQL
Editor Framework can depend on them. 

Best Regards!

Max ( Hui ) Cao
DTP SQL Dev Tools Committer
Sr S/W Eng - Dev
Sybase, Inc. Shanghai, China

Der Ping Chou <dpchou@xxxxxxxxxx>

02/28/2006 09:43 AM

To	Hui.Cao@xxxxxxxxxx
cc	dtp-dev@xxxxxxxxxxx, dtp-pmc@xxxxxxxxxxx, Hemant S Kolwalkar <hskolwal@xxxxxxxxxx>
Subject	Re: Fw: Exemplary Derby Routine Editor first version is pending to commit

Hui, 

We just checked in the changes for dbdefinition model and also updated
the API. In order to stream line the changes, we have updated the UML model
and regenerate the ecore model. Here are the snapshot of the changes:

• A new DebuggerDefinition class is added
• Attributes (eventSupported, sqlUDFSupported and, storedProcedureSupported) are added in Database VendorDefinition class.
• Did not add the request for trigger attribute, since it already exists.
• org.eclipse.datatools.connectivity.sqm.core\src\org\eclipse\datatools\connectivity\sqm\internal\core\definition package is also changed to consume the new dbdefinition model.

As I mentioned in my previous e-mail, please file a new defect for SQLModel
related change. Most likely we will come up with a specialized model for
Derby. 

Thanks, 

Der-Ping Chou
Information Management Tooling
Development Manager - Data Tools
Seattle IBM Office
tel : 1-206-587-5946 (T/L: 277-5946)

Hui.Cao@xxxxxxxxxx

02/20/2006 01:20 AM

To	Der Ping Chou/Redmond/IBM@IBMUS
cc	dtp-dev@xxxxxxxxxxx
Subject	Fw: Exemplary Derby Routine Editor first version is pending to commit

Hi Der Ping, 
Could you please have a look at the Bug 109112: "Needs new attributes
(sqlUdfSupported, eventSupported) and class (DebuggerDefinition)"
? I had modified my SQL Dev Tools code based on the patches I uploaded
for the Bug. Without this fix, I can not proceed. Thanks!

Best Regards!

Max ( Hui ) Cao
DTP SQL Dev Tools Committer
Sr S/W Eng - Dev
Sybase, Inc. Shanghai, China

----- Forwarded by Hui Cao/SYBASE on 02/20/2006 04:54 PM -----

Hui Cao/SYBASE

02/17/2006 09:50 PM

To	dtp-dev@xxxxxxxxxxx
cc
Subject	Exemplary Derby Routine Editor first version is pending to commit

Hi, 
I was about to commit the exemplary derby routine editor when I found that
this feature relies on some modified model base and connectivity code and
this checkin would break the build. I've created/updated bug entries for
the prerequisite tasks: 
Bug 109112: "Needs new attributes (sqlUdfSupported, eventSupported)
and class (DebuggerDefinition)" (I've uploaded the patches)
Bug 128396: "ManagedConnection.isConnected() returns wrong value"

Once the above mentioned bugs have been fixed, I'll proceed with the commit.
This feature is still at its crude stage, only being able to show the source
code for Derby Trigger. Detailed information can be found in Bug 128392:
"Routine.getSource() returns null". As to the "save"
function, I expect it can be accomplished in Bug 128394 by connectivity
layer: "Lack of means to persist the Routine object back to database".
But I'm always glad to discuss it with anyone who don't think so. Thanks!

Best Regards!

Max ( Hui ) Cao
DTP SQL Dev Tools Committer
Sr S/W Eng - Dev
Sybase, Inc. Shanghai, China