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 -~----------~----~----~----~------~----~------~--~---
