[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [stellation-res] Yet another DB abstraction idea...
|
On Wednesday 14 August 2002 06:26 pm, Rodolfo M. Raya wrote:
> On Wed, 2002-08-14 at 10:20, Mark C. Chu-Carroll wrote:
>
> OK, you are right.
>
> Thinking about it twice I see that the XML/XSL stuff is not needed if
> the class Table can generate the SQL stuff.
>
> The problem I see with your template is simple: it includes SQL reserved
> words inside the template.
>
> String command =
> db.expandCommand("CREATE TABLE Properties " +
> "($INT aid, $INT vid, $INT inheritable, $SHORTSTRING
> name" +
> "$LONGSTRING value, " +
> "CONSTRAINT fk4 FOREIGN KEY (aid, vid) REFERENCES
> Versions(aid,vid), " +
> "PRIMARY KEY(aid, vid, name))$CREATE_TRAILER");
>
> The template should not include words like CONSTRAINT, REFERENCES or
> anything that may change between different database vendors. In theory
> those words are part of a standard, but every vendor has its own DDL
> syntax.
>
> The statement you wrote:
>
> 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))
>
> must be rearranged as
>
> CREATE TABLE Properties ( aid INTEGER NOT NULL,
> vid INTEGER NOT NULL,
> inheritable INTEGER,
> name VARCHAR(200),
> value VARCHAR(1600),
> CONSTRAINT fk3 FOREIGN KEY (aid, vid) REFERENCES
> Versions(aid, vid)
> PRIMARY KEY(aid, vid, name))
>
> to be executed in Firebird or InterBase.
>
> Notice the swap of the field name and the field type. The template has a
> fixed position for each word and if we replace $INT with INTEGER we get
> a statement that fails.
This is actually an example of the kind of confusion that I worry about. We
abstracted things partially in our current code, so that I haven't typed the
raw SQL in a while. I got the order wrong. *No* database will take what
I gave. (And no one noticed it until now! :-) )
I'm not sure whether that's an argument in favor of saying "SQL DDL syntax
is so arbitrary and so arcane that we shouldn't muck with it explicitly", or
"Too much abstraction creates enough distance to breed confusion".
> Also, the template missed to represent the NOT NULL clauses that are
> necessary to build a primary index in the Firebird/InterBase example.
Same goes for Postgres and DB2. I was just lazy about the typing. Personally,
I try, whenever possible, to have all column types include "NOT NULL".
> Other databases require the field name between quotes ( i.e.: "aid"
> INTEGER ), so the template would fail again.
Out of curiosity: which ones?
I'm still sort of sitting on the fence. In theory, I kind of like the abstract
factory approach. But each time I try writing out the table construction
code for a factory approach, it just feels overwrought - too heavyweight.
The template approach has less design elegance. But it's got the advantages of
brevity, and concreteness. So in practice, I lean towards the templates.
I also think we might be spending more time than this is really worth. It
might be time to try some coding. I'm going to implement the template
approach. If you have time, try putting together at least a sketched
implementation of the factory approach with a Firebird implementation.
Then I'll write the intrinsic tables in both approaches, and we can
really gauge what seems better in the code.
-Mark
--
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
