Re: [Sqlalchemy-users] Problem understanding deletes

[Michael Bayer]

On Mar 25, 2006, at 12:17 AM, Tiago Cogumbreiro wrote: this is correct, SQLAlchemy's primary job is to generate SQL statements. it has minimal attribute management capabilities in order to track changes for the purposes of committing the changes on a graph of objects, as well as a "backreference" function that will keep bi-directional relationships evened up, but otherwise it isnt going to automatically manipulate object structures in response to SQL operations.  So your "father" on "child3" object hanging around is because you havent detached it (or removed "child3" from "child1's" list). Why don't the bi-directional relationships handle that? bi-directional relationships do this: del child1.childs[child3] assert child3.father is None objectstore.delete() doesnt do anything to object relationships, it instructs the unit of work that a SQL DELETE statement will be issued when it is committed. all of the problems you are having with this program are because you are expecting the above relationship to be automatically modified by marking "child1" as deleted with the unit of work, which it doesnt.  the unit of work expects that you will do everything to your objects manually.  Once all of your objects are just the way you want them, you then can additionally mark objects that you want deleted from the database as deleted, and then calling objectstore.commit() will persist all those changes and deletions you have indicated to the database. there was a comment some weeks earlier that objectstore.delete(obj) should remove "obj" from the parent list that its attached to.  to which I replied, what if "obj" is attached to 308 different parent lists ?  then SA would have to maintain a lookup table of every object every other object is attached to, which seems like a huge amount of cruft, certain to introduce a lot more bugs than it "fixes", just to be a minor convenience for an application that doesnt want to worry about how its own domain objects are structured.  its all way out of the scope of SA, which just wants to generate SQL statements, load objects, and save them.  it doesnt want to be involved with how you manipulate them.  the backref thing is the *only* exception to this, as it was requested by users, and i almost regret it for all the confusion it creates. I tried adding these two lines after 'objectstore.clear()': objectstore.expire(child1) objectstore.refresh(child3) And none of them made the test pass, I must be failling to understand what are these methods supposed to do. remove the "clear()".   refresh()/expire() require that the object youre dealing with is still present in the unit of work.  issuing a clear() replaces the existing unit of work with a new one, so it essentially removes everything. but also, your program shouldnt expect anything from child3 at all because you cleared the objectstore beforehand; generally clearing the objectstore means youre starting fresh and will re-load everything from the database. I don't understand what you mean with "I shouldn't expect anything from child3". If clear means that fields will be reloaded why isn't the 'childs' property reloaded after a 'clear', IMHO it would _expect_ it to be reloaded after a 'commit'. every mapped object that you deal with is tracked by a unit of work object, which is accessible via objectstore.get_session().   when you clear() the current unit of work, it creates a new session.  objects that are lying around from the previous session will not interact with unit of work operations very well, since they are no longer present in the unit of work.  those objects are completely gone from the UOW. they will not have their fields refreshed, they wont be marked as dirty, they wont be saved, or deleted, or anything, even if you pass them into various unit of work functions the results are undefined.  if you clear your unit of work its expected that you are starting totally clean.  It has a clear on line 47 and a comment asking why I need to call it explicitly. If I comment it out the test on line 53 and 54 fail, which means that if I don't call it the references on *new* objects will not be updated and therefore 'new_root is root' and 'new_child3 is child3'. the program is based on the example that comes with the SA distribution.  the version that comes with the distribution does not have a clear() inside it, so I found it strange that you thought clear() was required. if you comment out the clear(), the subsequent tests fail because you are selecting objects that already exist in the current unit of work session.  the unit of work maintains an "identity map" of objects, such that only one instance of an object corresponding to a particular database identity can exist within the session at one time.  so new_child3 is the same object as child3 (*provided you dont clear the unit of work in between*), just do an assertion on that and youll see. clear() it is useful in examples and test cases as something to do before you load data from the database, to guarantee that all mapper queries will return newly loaded objects as opposed to objects still lying around in the identity map.  like the docs mention, it is also pretty important for web applications at the beginning of a request so that nothing from the last request is left lying around. What kind of things can be lying around? That's what I don't get. From my tests it looks like it's something regarding to marking the objects that referenciate a certain OID as 'dirty' which will in turn make the mapper create new instances of the mapped class (more on this latter). if you write a web application, say a mod python handler, you write a function called handle(r), where 'r' is a mod_python request object.  Suppose you load three or four objects from the database within your handle(). then you change some around, save them, etc., then send a response to the user. then three hours later, another user comes in (its a very unpopular site), calls the same handle(r) function.  you go to select objects from the database, some of which are the same objects used in the last request, some of which are not.  the objects that you loaded in the previous request *will be the same*, since they are still present in the unit of work from last time, and wont get refreshed, even when you query the databse for them.  the second user now gets data of which some of it is three hours old, some of it fresh.  thats why you clear the objectstore. OK fair enough, but its due to the fact that deleting an object has pretty much no effect on the remaining "ecosystem", it just sends a DELETE instruction to the database, as well as assuming you wont do much with that object anymore since it gets removed from the current uow session. The big problem here is, how do I propagate a delete on cached properties, like 'childs'? How do I mark them as 'dirty' in order for the 'objectstore.commit()' or 'objectstore.clear()' makes the next call to this field to refresh the value from the database? if you want deletes to cascade, use the "private=True" flag: assign_mapper(Tree, table,               properties={       'childs':relation(Tree, foreignkey=table.c.father_id, primaryjoin=table.c.father_id==table.c.id,  backref=backref('father', uselist=False, foreignkey=table.c.id), private=True) }) so then this code will work: child1.delete() objectstore.commit()  # deletes child1 and child3 new_child3 = Tree.get_by(name="child3") assert new_child3 is None however, remember when I say "deletes", I am only talking about SQL.  your old "child3" and "child1" objects are still present in your program, just not in the database.  its expected that you will dereference those objects yourself. Making the use refresh all the references to the object seems a little strange since that there are cases where those objects are not aware that such action (an object removal) has happened and it's specially ackward that I need to refresh my reference to a _new_ object, instead of updating the content of the old one. In my idea there should be only one copy of each mapped object. Every 'mapper.get(some_id)' should return *always* the same object. yah, thats exactly what it does, not like theres an entire section in the docs on that or anything..... http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork_identity ;)