Michael Bayer wrote:
On Feb 23, 2006, at 12:21 AM, Kapil Thangavelu wrote:
not sure if its related but i've looking for on delete and on update
actions functionality, ie. on delete cascade. i've been using a
foreign key subclass below and some db specific engine changes
(attached) in get_col_spec method (though its straight ansi afaik,
though not in mysql till 5.0), the affects of this on in memory
representation of relation collections aren't clear to me, ie. if this
is a use case where such bookkeeping would be nesc. even if its not
such a case, this functionality is something i'd like to see in
sqlalchemy, as on delete/on update fk column action specification is
really helpful, i think.
I will look into this, namely I want to see where it fits into ANSI sql,
how well it applies to all the DB's etc. I also want to look into, if I
add this to the base schema package, then what else do we probably need
to add (i.e. i dont want the schema package to fulfill an arbitrary
level of features but then be missing others.)
anyway I just logged this in as ticket # 75, we can comment further over
there.
i've attached some findings to the issue, for discussion to a wider
audience, i've mirrored them here.
Ansi Compliance
---
re this being ansi compliant and implemented, i spent a few minutes
googling found a few explicit references to this being part of sql-92
ansi standards for referential integrity actions
http://www.dbmsmag.com/9606d17.html
google cache of ppt on sqlserver impl
http://72.14.207.104/search?q=cache:P3gmHXvMwrEJ:www.sql.co.il/ug/13/Maintaining%2520Referential%2520Integrity.ppt+ANSI+SQL-92+on+delete&hl=en&gl=us&ct=clnk&cd=1
both reference it as being part of the standard.
in terms of implementation, every db i've looked at supports this
(postgres, oracle, firebird, sqlserver, mysql), the only laggard being
mysql's support till v5, as support previous to that had idiosyncratic
behavior and was non standards compliant, see some of the comments from
the link below for more details.
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
out of curosity does anyone know a good online ansi sql reference?
Additional Constraints
---
in terms of other constraint and action items that might be of interest
for the schema module to have full coverage of sql constraints.
- check constraints for columns
mysql does not support check constraints, however i'd rather not have
all of sqlalchemy dummed down to mysql inadequacies, considering that
all the others i checked for this feature do implement it (oracle,
postgres, firebird). i think it better instead to support the feature,
with standard engine option of warn_unsupported or some such, which when
features not supported in schema are utilized determines if the engine
operates silently without processing the additional instructions, or
instead raises an exception ( probably better if both log).
- unique constraint for column
while this can be worked around via creating a unique index after the
fact, i think its better to support the same conceptual construction
offered by the sql model and support directly creating this constraint
on the column.
cheers,
kapil
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
