Reverse delete orphan?

[Jonathan Gordon]

How can I cascade delete from a many to one only when deleting the many means this one has no other children?

Imagine the following mapping:

    

class Container(Base):

    __tablename__ = 'container'

    pk = sa.Column(sa.Integer, primary_key=True)

    entries = relationship('Entry', backref='container', lazy='dynamic', cascade="all, delete, delete-orphan")

class Entry(Base):

    __tablename__ = 'entry'

    pk = sa.Column(sa.Integer, primary_key=True)

    container_pk = sa.Column(sa.Integer, sa.ForeignKey('container.pk'),

    tag_pk = sa.Column(sa.Integer, sa.ForeignKey('tag.pk'))

class Tag(Base):

    __tablename__ = 'tag'

    pk = sa.Column(sa.Integer, primary_key=True)

    description = sa.Column(sa.String, nullable=False)

    entries = relationship("Entry", backref='tag')

A Container has zero or more Entry objects. An Entry may be grouped with other Entry objects with a Tag. A Tag is never shared with Entry instances from more than one Container.

If I delete a Container instance, all of its Entry instances get deleted via cascade. What I'd also like to happen is to cascade the delete to all of the Tags as well. Most of the documentation I've seen for cascading deletes talks about deleting parents cascading to their children. In this case, it seems like I want to delete the parent (Tag) if I'm the last child (Entry). I'd like to avoid putting the Container pk on the Tag since it's already on the Entry and that seems like it's not properly normalized.

My example seems close to the "Many-to-many orphan deletion" given in sqlalchemy-utils, except it's many-to-one.

http://sqlalchemy-utils.readthedocs.org/en/latest/listeners.html#many-to-many-orphan-deletion

Any ideas?

 

[Mike Bayer]

On 4/7/15 4:43 PM, Jonathan Gordon wrote:

How can I cascade delete from a many to one only when deleting the many means this one has no other children?

Imagine the following mapping:

    

class Container(Base):

    __tablename__ = 'container'

    pk = sa.Column(sa.Integer, primary_key=True)

    entries = relationship('Entry', backref='container', lazy='dynamic', cascade="all, delete, delete-orphan")

class Entry(Base):

    __tablename__ = 'entry'

    pk = sa.Column(sa.Integer, primary_key=True)

    container_pk = sa.Column(sa.Integer, sa.ForeignKey('container.pk'),

    tag_pk = sa.Column(sa.Integer, sa.ForeignKey('tag.pk'))

class Tag(Base):

    __tablename__ = 'tag'

    pk = sa.Column(sa.Integer, primary_key=True)

    description = sa.Column(sa.String, nullable=False)

    entries = relationship("Entry", backref='tag')

A Container has zero or more Entry objects. An Entry may be grouped with other Entry objects with a Tag. A Tag is never shared with Entry instances from more than one Container

If I delete a Container instance, all of its Entry instances get deleted via cascade. What I'd also like to happen is to cascade the delete to all of the Tags as well.

While there is a limited form of "many-to-one cascade" supported, it requires that Tag is only linked to a single Entry, which I'm gathering is not the case here.

Typically events are used to intercept when a flush occurs so that additional steps can be taken to delete the extra objects.

One event approach would be the before_flush() event, you search through session.deleted for all Container objects; within Container, locate all Tag entries with that Container as the ultimate parent and mark them all as deleted as well.   With the before_flush() approach, you can use the Session and your objects in the usual way.

Another event approach would be to use the before_delete event on the Container mapping.    When the Container row is marked for deletion, perform a Core level DELETE on the given connection that deletes all Tag rows from the "tag" table.    With the before_delete() approach, the event occurs deep within the flush process, so it's not safe to manipulate the state of the Session; instead, you emit SQL directly on the given connection.

Still a third approach is to run a simple query at the end of the flush, where you search for Tag objects that have no Entry objects associated, and delete them.   I came up with that for this stackoverflow answer: http://stackoverflow.com/questions/9234082/setting-delete-orphan-on-sqlalchemy-relationship-causes-assertionerror-this-att/9264556#9264556.   You can probably just adapt that one.

Most of the documentation I've seen for cascading deletes talks about deleting parents cascading to their children. In this case, it seems like I want to delete the parent (Tag) if I'm the last child (Entry). I'd like to avoid putting the Container pk on the Tag since it's already on the Entry and that seems like it's not properly normalized.

My example seems close to the "Many-to-many orphan deletion" given in sqlalchemy-utils, except it's many-to-one.

http://sqlalchemy-utils.readthedocs.org/en/latest/listeners.html#many-to-many-orphan-deletion

Any ideas?

 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Jonathan Gordon]

On Tuesday, April 7, 2015 at 4:00:28 PM UTC-7, Michael Bayer wrote:

One event approach would be the before_flush() event, you search through session.deleted for all Container objects; within Container, locate all Tag entries with that Container as the ultimate parent and mark them all as deleted as well.   With the before_flush() approach, you can use the Session and your objects in the usual way.

Thanks Michael, that was very helpful. We ended up going with your first approach and it seems to be working out for us. I really appreciate the detailed response. I'm new to SQL Alchemy, coming to it from a Java/Hibernate background and I've been very impressed with the framework and the accompanying documentation.