col[2], col[1] = col[1], col[2] loses

[jek]

List collections lose on this:

col[2](2), col[1](1) = col[1](1), col[2](2)

The collections package makes the assumption that a child will only be represented once in a collection, and as such fires 1 delete event too many here, orphaning one of the children in the ORM's bookkeeping while leaving it in the actual list. It's easy to see it here if the child has a backref to the parent- it'll be None after this assignment.

I think the approach here is to keep a refcount in the CollectionAdapter and quash events while the membership count for a particular child is > 1. It would need to do bookkeeping based on the id() of the child to avoid conflicts with user comparison overrides.

Initially reported by maqr on the channel.

[Mike Bayer]

a full test case:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Text, ForeignKey
from sqlalchemy import inspect

sa_engine = create_engine("sqlite:///:memory:", echo=True)

Session = sessionmaker(bind=sa_engine)

session = Session()

Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    children = relationship('Child', backref='parent')
    def __repr__(self):
        return self.name


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    def __repr__(self):
        return self.name

Base.metadata.create_all(sa_engine)

p = Parent(name='Thomas')

session.add(p)

c1 = Child(name="Mary")
c2 = Child(name="John")
c3 = Child(name="Kenny")

p.children.append(c1)
p.children.append(c2)
p.children.append(c3)

session.commit()

p = session.query(Parent).get(1)

print(p.children)  # prints [John, Kenny](Mary,)

p.children[1](1), p.children[2](2) = p.children[2](2), p.children[1](1)

print(p.children)  # prints [Kenny, John](Mary,)

session.commit()

print(p.children)  # prints [John](Mary,). Oh my God! They killed Kenny!

workarounds:

p.children[1:2](1:2) = [c1](c2,)

or assign again:

p.children[1](1), p.children[2](2) = p.children[2](2), p.children[1](1)
p.children[1](1) = p.children[1](1)

[Mike Bayer]

consider relationship(..., track_dupes=True), as this bookkeeping is simple but expensive.