Another question :-) I will call session.delete(user) and the generated SQL commands are roughly (in 0.2.8, in 0.3.2 there is almost no difference): BEGIN SELECT FROM addresses DELETE FROM addresses DELETE FROM users COMMIT
But what happens when there is no tables locking before the SELECT and someone (in a different connection) will insert some related address between those two DELETEs? The second DELETE will result in an IntegrityError. Is that a bug or a feature? :-) Michael Bayer píše v Po 11. 12. 2006 v 18:41 -0800: > ml wrote: > > 1) Why does the SA the second SELECT for addresses to obtain primary > > keys? Why there is not a direct "DELETE FROM addresses WHERE user_id=?"? > > because SA's mapper is only a simple entity mapper. the way an > instance gets deleted is by it being marked as "deleted", and then the > instance gets passed to its mapper in a list of items to be deleted. > the origin of that "delete" marking, in this case due to a cascade, is > not known at that point - only that the object was marked for deletion. > the "cascade" rules are not linked into the query generation like > that, and you could just as well marked all your child objects deleted > individually without using any "cascade" at all. > > alternatively, if you didnt have the delete cascade, when deleting the > parent item it would update all the child objects with a parent id of > "NULL", using a similar mechanism (i.e. updating individual objects, > not an across-the-board update). > > to have the cascade rule wire itself into a "delete from table where > parent_id=x" type of query is harder than you might think. the main > thing that can go wrong is that the child items themselves might have > dependencies. If I delete an object X, which has a child of Y, but > then Y also has a child of Z, I have to somehow know to update/delete > the Z instance before deleting all of X's children, which necessitates > that SA knows explicitly that Y is attached to X, which necessitates a > select. (and also would necessitate individual queries issued for each > child of X). > > SA's current entity-based approach, which by default requires that it > knows every instance individually (yes there is a way to do what you > want, just read on), has no problem with this kind of scenario. > > another thing that can go wrong, is that X's child items might be > hanging around in the session, but the session doesnt know that those > child items are attached to X since they were loaded through some other > means. when the mass delete (or update) of X's children occur, now > theres a bunch of invalid objects hanging around in the session. > *unless*, once again, we did a SELECT to explicitly figure out all the > objects that are attached to X. > > so the entity approach is advantageous in that it does not screw up in > the generic case, and a core philosophy of SA is to go for total > correctness first, optimized cases second. > > > 2) If it is neccessary to do this SELECT, why is the SA selecting all > > columns? E.g. I will have 1 user with 100000 addresses - an idea of > > selecting all 100000 addresses to get their id's is spooky :-) > > so now for how to do what you want. it does the select by default > because it needs to know about all the child items individually when > the parent is being deleted, else it wouldnt be able to delete or > update them and there would be a constraint violation. but you can > disable this by using "passive_deletes=True" flag on the relationship > so that SA does not load in all the objects upon a delete operation, > and you combine it with ON DELETE (CASCADE/SET NULL) directives on your > table so that your database handles the en-masse delete for you (this > is another core SA philosophy, "dont do what the database can already > do"). > > "passive_deletes" is a fairly new setting and someone on IRC was > complaining that it wasnt completely working (but then he couldnt show > me a test case), so if you see your ORM loading a bunch of objects just > to delete them with that flag on, send a test case to the list so I can > improve upon its functioning. > > as far as working the mass-delete into SA's ORM directly, it might make > a nice feature add, but because it would be very hard to make it > "automatic" and "smart" enough to detect conditions where it *cant* be > used, as well as the surprise side effects it has, would almost be > better as just an explcit user-defined setting > "cascade_deletes"...which is essentially the same as the "ON DELETE" > rule you put on your table (and therefore is largely a reinvention of > the wheel, unless youre stuck with MySQL ISAM tables). > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
