On 7/14/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
OK, in continuing with the UNIQUE issue of the other day, id like to
continue making the whole "constraint" methodology as simple and
consistent as possible, including getting all the reflection to
work. this is more complicated than I first realized.
So, Id like to move all the logic for creating primary and foreign
key constraints, i.e. within a CREATE TABLE statement, into the new
Constraint objects I've made. Nothing changes with the API, you
still can say "primary_key=True", ForeignKey('blah'), etc., but
internally it will tack on a PrimaryKeyConstraint or a
ForeignKeyConstraint to the Table's list of elements. Similarly, if
you explicitly specify PrimaryKeyConstraint or ForeignKeyConstraint,
it tacks on "primary_key=True", ForeignKey('blah') to the Column
objects represented.
The logic for issuing the SQL to create those constraints will be
centralized, as opposed to right now where theres ad-hoc stuff inside
all the database modules.
This should make reflection easier since most DB's want to give you
the foreign key constraints as a separate list, and we also get to
name the constraints, we'll get the ON UPDATE/ON DELETE for them
etc., its all much more like the database actually works and should
have been this way from the start.
as a side note, Im probably also going to replace Postgres'
reflection to use PG_CATALOG due to ticket #71, an issue which wont
be fixed in PG until 8.2. so the information_schema module will not
be used by anything.
So this means is that the actual CREATE statements when tables are
created change from:
CREATE TABLE foo (
id INTEGER PRIMARY KEY SERIAL,
foo_id INTEGER REFERENCES (lala.foo_id)
)
to:
CREATE TABLE foo (
id INTEGER SERIAL,
foo_id INTEGER,
PRIMARY KEY(id),
FOREIGN KEY(foo_id) REFERENCES lala (foo_id)
)
which is completely standard SQL, so there shouldnt be a problem.
Im going to try to run all of these today for the full suite of
databases I can try, which means i have to power up all my linux/
windows boxes, but if anyone knows of any quirks with the second
syntax (particularly Firebird since I cannot test firebird at this
moment), let me know.
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
This is actually somewhat unrelated, but how are circular foreign key dependencies handled? In the example above, what if table lala had to be created after table foo because of such a dependency? While you're refactoring this code, it might be worth it to process the constraints separately ( i.e. not linked to table creation), so the syntax would be:
...
CREATE TABLE foo (
id INTEGER SERIAL,
foo_id INTEGER,
)
...
ALTER TABLE foo
ADD PRIMARY KEY(id),
ADD FOREIGN KEY(foo_id) REFERENCES lala (foo_id)
Just a thought.
-David Shoemaker
--
--- I'd give my right arm to be ambidextrous. ---
------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
