Thread: [SQLObject] Foreign Key + Constraints?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Sidnei da S. <si...@aw...> - 2003-12-03 15:04:47
|
I just realized that when using ForeignKey, the created table doesn't create the foreign key constraints. I would like to add that + the 'cascade' option to ForeignKey, at least on Postgres. Is there any objection on doing so? []'s -- Sidnei da Silva <si...@aw...> http://awkly.org - dreamcatching :: making your dreams come true http://plone.org/about/team#dreamcatcher Recursion is the root of computation since it trades description for time. |
From: Ian B. <ia...@co...> - 2003-12-03 16:31:34
|
On Dec 3, 2003, at 8:59 AM, Sidnei da Silva wrote: > I just realized that when using ForeignKey, the created table doesn't > create the foreign key constraints. I would like to add that + the > 'cascade' option to ForeignKey, at least on Postgres. Is there any > objection on doing so? That's fine. It would be nice if MySQL and SQLite could also do cascades, from the SQL side, i.e., destroySelf() would do the cascade manually -- at least consider the necessary hooks. Firebird is probably like Postgres, but I don't know if the syntax matches. -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |
From: Sidnei da S. <si...@aw...> - 2003-12-03 18:49:29
|
On Wed, Dec 03, 2003 at 10:28:08AM -0600, Ian Bicking wrote: | On Dec 3, 2003, at 8:59 AM, Sidnei da Silva wrote: | >I just realized that when using ForeignKey, the created table doesn't | >create the foreign key constraints. I would like to add that + the | >'cascade' option to ForeignKey, at least on Postgres. Is there any | >objection on doing so? | | That's fine. It would be nice if MySQL and SQLite could also do | cascades, from the SQL side, i.e., destroySelf() would do the cascade | manually -- at least consider the necessary hooks. Firebird is | probably like Postgres, but I don't know if the syntax matches. Ok, attached is my first try. There's two things I would like to discuss before going on supporting the other databases: First, I'm getting the 'foreign' class via findClass(col.foreignKey), without passing the registry parameter, because AFAICT, I can't get to the SQLObject class from the column. Is that correct? How much trouble can that buy us? :) Second, I think that as I'm getting the 'dependent'[1] classes via findDependencies, It would be simple to pass them into DBConnection.dropTable() so that we can do the cascade the manual way -- by iterating on the result of select() and calling destroySelf() on each object. Does that sound reasonable? [1] I considered 'dependent' a class that has a column which refers to our class on the 'foreignKey' attribute, and also has 'cascade' set to True. Thoughts? -- Sidnei da Silva <si...@aw...> http://awkly.org - dreamcatching :: making your dreams come true http://plone.org/about/team#dreamcatcher Counting in binary is just like counting in decimal -- if you are all thumbs. -- Glaser and Way |
From: Ian B. <ia...@co...> - 2003-12-03 19:25:36
|
I haven't looked at the patch in context yet, but a couple comments... On Dec 3, 2003, at 12:43 PM, Sidnei da Silva wrote: > Ok, attached is my first try. There's two things I would like to > discuss before going on supporting the other databases: > > First, I'm getting the 'foreign' class via findClass(col.foreignKey), > without passing the registry parameter, because AFAICT, I can't get to > the SQLObject class from the column. Is that correct? How much trouble > can that buy us? :) SOCol should get the foreign class, but not during instantiation (because it's instantiated when the class is created, but the foreign class may not be instantiated yet). There should be a function that gets called in SOCol when the foreign class is created, and you should set up anything you need then. > Second, I think that as I'm getting the 'dependent'[1] classes via > findDependencies, It would be simple to pass them into > DBConnection.dropTable() so that we can do the cascade the manual way > -- by iterating on the result of select() and calling destroySelf() on > each object. Does that sound reasonable? You mean, delete every instance from the table, then delete the table? Hmm... dropping a table is a pretty severe thing whatever way you do it, I'm not sure how important destroySelf is at that point. OTOH, maybe if you just want to drop without any checks (and presumably restart your process), you should do it with plain SQL and not the class method. (E.g., test code will want to do that drop, so it can start from a consistent state regardless of previous possible corruption) > [1] I considered 'dependent' a class that has a column which refers to > our class on the 'foreignKey' attribute, and also has 'cascade' set to > True. I suppose it follows that if the rows are dependent, the table as a whole is as well. OTOH, that wouldn't occur to me naturally -- it's something of a corner case. The principle of least surprise would indicate that we should leave the table around. But that's no good, as we're inconsistent at that point. And what about circular dependencies? These can only happen if NULL is also allowed in the foreign key, but it does happen. In this case we'd want to NULL out the one table, remove the first, then maybe remove the second. -- Ian Bicking | ia...@co... | http://blog.ianbicking.org |
From: Sidnei da S. <si...@aw...> - 2003-12-03 20:06:38
|
On Wed, Dec 03, 2003 at 01:25:43PM -0600, Ian Bicking wrote: | I haven't looked at the patch in context yet, but a couple comments... | | >Second, I think that as I'm getting the 'dependent'[1] classes via | >findDependencies, It would be simple to pass them into | >DBConnection.dropTable() so that we can do the cascade the manual way | >-- by iterating on the result of select() and calling destroySelf() on | >each object. Does that sound reasonable? | | You mean, delete every instance from the table, then delete the table? | Hmm... dropping a table is a pretty severe thing whatever way you do | it, I'm not sure how important destroySelf is at that point. OTOH, | maybe if you just want to drop without any checks (and presumably | restart your process), you should do it with plain SQL and not the | class method. (E.g., test code will want to do that drop, so it can | start from a consistent state regardless of previous possible | corruption) Ok, I think I misunderstood Postgres here. I though that 'DROP TABLE $tablename CASCADE' would remove the dependant records from the other tables, but that doesn't seem to be true. It changes the dependant tables only when you delete a row, which is good. I didn't had understood that. | >[1] I considered 'dependent' a class that has a column which refers to | >our class on the 'foreignKey' attribute, and also has 'cascade' set to | >True. | | I suppose it follows that if the rows are dependent, the table as a | whole is as well. OTOH, that wouldn't occur to me naturally -- it's | something of a corner case. The principle of least surprise would | indicate that we should leave the table around. But that's no good, as | we're inconsistent at that point. I don't follow you 100% here, but I think its related to the above comment. | And what about circular dependencies? These can only happen if NULL is | also allowed in the foreign key, but it does happen. In this case we'd | want to NULL out the one table, remove the first, then maybe remove the | second. I didn't understood this one as well. -- Sidnei da Silva <si...@aw...> http://awkly.org - dreamcatching :: making your dreams come true http://plone.org/about/team#dreamcatcher People are going to scream bloody murder about that. -- Seen on linux-kernel |
From: Sidnei da S. <si...@aw...> - 2003-12-03 21:02:21
Attachments:
cascade.patch
|
So, I've got an improved version of the patch, which should work with the other databases as well, though I didn't tested yet (have to setup mysql at least). Let me summarize what I've understood, and what I implemented to make sure I got it right this time. I'm not a DB master, just a dirty little python hacker ;) 1. If you create a Foreign Key, it is possible in Postgres to create a constraint to 'enforce' that the foreign key exists in the foreign table. It is also possible to specify that you want to 'cascade delete'. This means that when one record is deleted from the foreign table, all records refering to this record in the local table are deleted as well. This is recursive. 2. In the same sense, you may also create a 'restrict delete' constraint, which prevents a record from being deleted from the foreign table until no more records on the local table reference it. 3. If you drop the foreign table, the local table is not affected, no matter how many records reference the foreign table. 4. It is impossible to work around the cascade delete unless you drop the constraint. Does that sound right? Here goes the new patch attached, and off I go, to test with MySQL. []'s -- Sidnei da Silva <si...@aw...> http://awkly.org - dreamcatching :: making your dreams come true http://plone.org/about/team#dreamcatcher Not only Guinness - Linux is good for you, too. -- Banzai on IRC |
From: Sidnei da S. <si...@aw...> - 2003-12-03 21:17:14
|
| >First, I'm getting the 'foreign' class via findClass(col.foreignKey), | >without passing the registry parameter, because AFAICT, I can't get to | >the SQLObject class from the column. Is that correct? How much trouble | >can that buy us? :) | | SOCol should get the foreign class, but not during instantiation | (because it's instantiated when the class is created, but the foreign | class may not be instantiated yet). There should be a function that | gets called in SOCol when the foreign class is created, and you should | set up anything you need then. The foreign class is only needed when postgresCreateSQL is called on the column. I can't really see where _registry is set, but it seems to be done on MetaSQLObject. If thats true, would it be fine to set and hold reference to the class on the column? Maybe having the column know the value of the class' _registry would be a better idea to avoid cyclic references? -- Sidnei da Silva <si...@aw...> http://awkly.org - dreamcatching :: making your dreams come true http://plone.org/about/team#dreamcatcher I wish you humans would leave me alone. |
From: Sidnei da S. <si...@aw...> - 2003-12-03 18:50:09
Attachments:
cascade.patch
|
Argh. As usual, I've forgot the attachment. Here it goes. -- Sidnei da Silva <si...@aw...> http://awkly.org - dreamcatching :: making your dreams come true http://plone.org/about/team#dreamcatcher Sendmail may be safely run set-user-id to root. -- Eric Allman, "Sendmail Installation Guide" |