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.0
     Component:  orm          |   Severity:  very major - up to 2 days
    Resolution:               |   Keywords:
Progress State:  in progress  |
------------------------------+---------------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * milestone:  blue sky => 0.9.0
 * severity:  refactor - over two days => very major - up to 2 days
 * status_field:  not decided upon => in progress


Comment:

 fine, a patch is attached.  it would be safe to release since it takes no
 effect unless a new mapper setting "maintain_unique_attributes" is used.
 As far as what breaks when one uses this attribute, hard to say, though i
 think mostly the breakage would be when the mapper has lots of other
 things dependent on it.

 Usage so far requires that one can detect a "dupe" before the UOW assigns
 foreign keys.  So in the example like we got in #2765, it has to check for
 uniques on the relationship-bound attribute where the change has occurred.
 but you can also give it several pairs of things to check.

 {{{
 #!python
 from sqlalchemy import create_engine
 from sqlalchemy import Column, Integer, String, Float, UniqueConstraint,
 ForeignKey
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import sessionmaker, relationship, backref
 from sqlalchemy.orm.dependency import OneToManyDP, attributes

 Base = declarative_base()

 class ParentEntity(Base):
     __tablename__ = 'parent'

     id = Column(Integer, primary_key=True)
     name = Column(String)

 class ChildEntity(Base):
     __tablename__ = 'child'

     __table_args__ = (UniqueConstraint('parent_id', 'z',
 name='child_uk'),)

     __mapper_args__ = {
         "maintain_unique_attributes": [("parent", "z")]

         # to detect conflicts for both relationship as well as on the
 integer
         # attribute
         # "maintain_unique_attributes": [("parent", "z"), ("parent_id",
 "z")]
     }

     id = Column(Integer, primary_key=True)
     parent_id = Column(Integer, ForeignKey('parent.id'), nullable=False)
     z = Column(Float)

     parent = relationship("ParentEntity",
                           backref=backref("children",
                                           cascade="all, delete-orphan"))

 engine = create_engine('sqlite://', echo=True)
 Session = sessionmaker(bind=engine)

 sess = Session()
 Base.metadata.create_all(engine)

 p = ParentEntity(name='Datum')
 p.children = [ChildEntity(z=2.5), ChildEntity(z=3.5), ChildEntity(z=5.5)]
 sess.add(p)
 sess.commit()

 # Remove all existing children and create new, one of which is
 # identical to a previously existing child
 p.children = [ChildEntity(z=4.0), ChildEntity(z=5.5)]
 sess.commit()

 c5 = sess.query(ChildEntity).filter_by(z=5.5).one()
 sess.delete(c5)
 sess.add_all([
     ChildEntity(z=5.5, parent=p)
 ])
 sess.commit()
 }}}

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