Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [stellation-res] Yet another DB abstraction idea...
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 
-- 

 MAXPROGRAMS
 IBM Business Partner
 Microsoft MSDN Business Connection Partner
 rmraya@xxxxxxxxxxxxxxx
 http://www.maxprograms.com

Back to the top