[stellation-res] Database abstraction

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

So... I'm looking at trying to create a database abstraction layer that
will work accross Postgres, DB2, Oracle, MySQL/InnoDB, and
Firebird. 

One option is to totally automate the SQL generation process. This
allows us to smoothly handle things like the INNODB declarations
that need to go onto the end of table creations, variations in typenames,
etc.

I've tried a first cut at doing this for table creation. I'm looking at a 
bit of test code, and I can't decide whether I like it (it's nicely abstract,
and quite easy to write), or hate it (it's hard to see the SQL in it). Here's
a sample: what do you folks think: is this a good direction, or am I being
idiotically abstract?

Here's the original SQL:

	CREATE TABLE Properties (INTEGER aid,
					        INTEGER vid,
					        INTEGER inheritable,
						VARCHAR(200) name,
						VARCHAR(1600) value,
			CONSTRAINT fk3 FOREIGN KEY (aid, vid) REFERENCES Versions(aid, vid)
			PRIMARY KEY(aid, vid, name))


Here's the corresponding code:
        DatabaseCreateTableCommand cmd = 
                    _db.createTable("Properties")
                        .addIntegerField("aid")
                        .addIntegerField("vid")
                        .addIntegerField("inheritable")
                        .addShortStringField("name")
                        .addLongStringField("value")
                       .beginForeignKeyConstraint("fk3", "FOREIGN KEY")
                            .addConstraintField("aid")
                            .addConstraintField("vid")
                           .beginForeignKeyReferences("Versions")
                             .addConstraintField("aid")
                             .addConstraintField("vid")
                           .endForeignKeyConstraint()
                       .beginPrimaryKeyConstraint()
                          .addConstraintField("aid")
                          .addConstraintField("vid")
                          .addConstraintField("name")
                       .endPrimaryKeyConstraint()
                     .endCommand();

-- 
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