Re: [stellation-res] Yet another DB abstraction idea...

["Mark C. Chu-Carroll" <mcc@xxxxxxxxxxxxxx>]

On Wednesday 14 August 2002 04:43 pm, Rodolfo M. Raya wrote:
> On Wed, 2002-08-14 at 11:49, Jim Wright - IBM Research wrote:
>
>     Hi,
>
>     For me, the template version is significantly more readable than the
> pseudo-XML version (and frankly, I'm more familiar with XML than with
> SQL...). I had to read the pseudo-XML several times to determine
>     a) the CONSTRAINT was not declared;
>
> The CONSTRAINT is defined in
>
>         <key type="FOREIGN">
>            <key_name>fk3</key_name>
>            <references.
>               <table_name>versions</table_name>
>               <fields>
>                  <field_name>aid</field_name>
>                  <field_name>vid</field_name>
>               </fields>
>            </references>
>         </key>
>
> It's not defined explicitly as a CONSTRAINT but as FOREIGN KEY. The
> stylesheet should provide the right syntax for each DB.
>
> I did this on purpose. When I write a script that creates a database I
> always create the tables without constraints. Once the tables are
> commited I alter the tables and add the foreign keys. This makes the
> table creation order independent of the constraints. An example:
>
> CREATE TABLE MASTERTABLE (
>    ID INTEGER,
>    NAME VARCHAR(200)
>    PRIMARY KEY( ID )
> );
> CREATE TABLE DETAILTABLE (
>    MASTER INTEGER,
>    ITEM1 INTEGER,
>    DESCRIPTION VARCHAR(200)
>    PRIMARY KEY(MASTER, ITEM1)
> );
> COMMIT;
> ALTER TABLE DETAILTABLE ADD FOREIGN KEY(MASTER) REFERENCES MASTER( ID );
>
> If you write the script this way it doesn't matter which table is
> created first.




>
>     b) there was no apparent equivalent for CREATE_TRAILER.
>     Presumably CREATE_TRAILER would be handled via XSLT (so it's not needed
> in the pseudo-XML), and therefore it's not necessary to explicitly group
> the parameters
>     for CREATE_TRAILER (as the template version does using parentheses).
>     However, the very fact that CREATE_TRAILER is buried in the style sheet
>     arguably
>     makes the code harder to follow.
>
> There's no mention of any trailer in the SQL statement that Mark wrote.
> I modeled the statement, not the template.
>
>
>       I also think the much more condensed
>     template format
>     (6 lines, vs. 38 for the pseudo-XML) is easier to parse visually
>     (admittedly, that may be
>     a style preference from my old C++ hacking days....)
>
>
> This can be improved. I only wrote a draft.
>
>     It also seems problematic that VARCHAR field lengths are specified
>     explicitly in
>     the pseudo-XML.  I thought one goal was to abstract this kind of
> low-level, db-specific
>     detail *out* of the individual db command constructs, hiding it within
> the 'db' abstraction.
>
>
> This can be rewritten as:
>     <field>
>        <field_name>name</field_name>
>        <field_type>VARCHAR(200)</field_type>
>     </field>
>
>
>     The template version seems like the best approach yet.
>
>
> I think that there are two different things to be done:
>
> 1) The original script to create a repository.
> 2) Database maintenance and addition of new artifacts.
>
> The first part could be bypassed having 6 different scripts. No XML or
> template necessary.
>
> To solve the second problem Mark proposed a template. This template
> should be interpreted for each database type to generate the proper SQL
> statement. This is very similar to writing a stylesheet for each
> database.
>
> In my previous message I omitted some things:
>
> * There should be a DTD (maybe a schema) to follow when writing the XML
> specification for the table. If we create a DTD that is simple enough,
> creating documents will be easy too.
>
> * The XML document can be generated in memory as DOM object and passed
> to an XSLT tool like Xalan, so nobody has to read it (it doesn't matter
> if it has 6 or 38 lines).
>
> * We can hide the XML document creation behind a class. If we create a
> class that can be serialized as XML DOM the code might look like:
>
>
>     Table table = new Table( "Properties" );
>     table.addField( "aid", "INTEGER");
>     ...
>     table addField( "name","VARCHAR(2000)");
>
>     String fields[] = {"aid","vid"};
>
>     table.addPrimaryKey( fields }
>     table.addForeignKey( "fk3","versions",fields); // (name, referenced
>     table, fields)
>
>     org.w3c.dom.Document doc = table.generateDOM();
>
>
> Transform doc using any XSLT tool and generate the right SQL
> statement(s) to execute.
>
> Maybe there are more aspects to consider before discarding this
> approach.
>
> Rodolfo

-- 
Mark Craig Chu-Carroll,  IBM T.J. Watson Research Center  
*** The Stellation project: Advanced SCM for Collaboration
***		http://www.eclipse.org/stellation
*** Work Email: mcc@xxxxxxxxxxxxxx  ------- Personal Email: markcc@xxxxxxxxxxx