|
From: SourceForge.net <no...@so...> - 2005-10-31 22:34:51
|
Bugs item #1305062, was opened at 2005-09-26 13:07 Message generated for change (Comment added) made by adustman You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1305062&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: General Group: SQLObject release (specify) Status: Open Resolution: None Priority: 5 Submitted By: Andy Dustman (adustman) Assigned to: Nobody/Anonymous (nobody) Summary: 0.7a: RelatedJoin() glue table has no constraints Initial Comment: Consider the example from the online 0.6.1 documentation, with the User and Role objects, where each object does a RelatedJoin() to the other. This results in a glue table that looks like this: CREATE TABLE user_role ( user_id INT NOT NULL, role_id INT NOT NULL ) The schema lacks any FOREIGN KEY constraints, and a PRIMARY KEY. It should probalby look more like this: CREATE TABLE user_role ( user_id INT NOT NULL, CONSTRAINT user_id_exists FOREIGN KEY (user_id) REFERENCES user (id), role_id INT NOT NULL, CONSTRAINT role_id_exists FOREIGN KEY (role_id) REFERENCES role (id), PRIMARY KEY (user_id, role_id) ) ON DELETE CASCADE may be needed as well. Note that I am using patch #1299109 to add FOREIGN KEY support for MySQL. However, looking at join.py, it does not seem to do anything with foreign keys, so I do not think this is a dependency on MySQL; I think this behavior should be reproducable on other databases. The intermediate table schema is created in dbconnection.DBAPI._SO_createJoinTableSQL(), and it calls self.joinSQLType(join) to get the SQL for the column. In all the implementations (except Sybase which is slightly different), it returns 'INT NOT NULL' The table creation order looks like it could be a problem, as the glue/intermediate table seems to be created before the second of the other two tables, which would produce referential integrity problems (i.e.reference to a non-existent table). ---------------------------------------------------------------------- >Comment By: Andy Dustman (adustman) Date: 2005-10-31 17:34 Message: Logged In: YES user_id=71372 BTW, the table creation order comment is really more of a problem for Turbogears. I've done a bit of testing by altering an existing glue table to use ON DELETE CASCADE and it seems to work as expected/desired. Without ON DELETE CASCADE, you have to delete the glue entry tables manually, (typically with obj.removeXXX(other_id)); otherwise your glue table has a bunch of dead entries, though if you have the FOREIGN KEY entry without ON CASCADE DELETE, then you can't remove the parent objects without first deleting the glue records. In other words, having FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE keeps your glue table clean when you remove either object of the RelatedJoin(). ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1305062&group_id=74338 |
