Re: [Sqlalchemy-tickets] [sqlalchemy] #2501: the DELETE before INSERT problem

[sqlalchemy <mi...@zz...>]

#2501: the DELETE before INSERT problem
------------------------------+---------------------------------------
      Reporter:  zzzeek       |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.9.xx
     Component:  orm          |   Severity:  very major - up to 2 days
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+---------------------------------------

Comment (by schlamar):

 I'm running in the same issue than #2765 but I have more complicated edge
 cases.

 My main functionality is to update an object from a dict, so e.g. `d =
 {'children': [dict(z=1), dict(id=1, z=2)]` will create a new child for the
 first entry and will use the existing Child with id 1 from the database.

 There are a lot of cases to be considered: replace an existing with a new
 one, the other way round, swap two existing children and probably more.

 This gets interesting as soon as you have an ordered relationship with
 `ordering_list` and a `delete-orphan` and a unique constraint on
 (position, parent_id) in the child.



 Right now I have "solved" all issues except the swapping by triggering
 cascade delete for all existing children and then "reviving" the children
 which are still needed by `make_transient`. Next, I fix possible ordering
 issues by only adding previously existing objects to the relationship
 (because if child id=1 with position=2 should now be on position=1 and a
 new child should be on 2, you try to insert child=2 with position=2
 first). At last, I set all objects to the relationship.

 Here is my code. `get_related_obj` is responsible for creating or querying
 the child object.

 {{{
 old_ids = set([o.id for o in getattr(obj, field)])
 value = [get_related_obj(session, obj, v, field) for v in value]
 setattr(obj, field, list())
 session.flush()  # possibly trigger delete cascade
 for v in value:
     state = inspection.inspect(v)
     if state.deleted:
         orm.make_transient(v)  # put it back to life
 new_filtered = [o for o in value if o.id and o.id in old_ids]
 setattr(obj, field, new_filtered)
 session.flush()  # trigger update of position
 # now set the correct list
 setattr(obj, field, value)
 }}}

 I'm not sure if this is covering all edge cases, maybe you found something
 I missed?

 However, my main purpose of this comment is to give you some cases you
 should test for this patch. I'm pretty sure that not all of them are
 covered :)

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2501#comment:11>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python