Generic "soft delete" implementation

[Jaco Breitenbach]

Dear experts,

All the entity tables in my data model have an "is_deleted" column.  The many-to-many mapping tables, however, do not.  I was wondering if it is possible to implement a system-wide, generic soft delete mechanism, specifically focused on data retrieval where any entity records that have been marked as deleted will automatically be excluded from any SQLAlchemy select statements being generated.  This should include specific queries as well as automatic selects due to pre-defined table relationships.

Is there an easy way to implement this e.g. by sub-classing Base or Query?  I'm still new to SQLAlchemy, so any advice would be much appreciated.

Thank you and kind regards,

Jaco

[Jonathan Vanasco]

The FAQ
http://docs.sqlalchemy.org/en/latest/faq/sessions.html#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query

points to an example in the wiki:

https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery

[Jaco Breitenbach]

Thank you, Jonathan, that wiki example has helped a lot.  I've extended it a bit to also add the extra condition to joined tables.

I want to take it one step further now and automatically convert session.delete()s into update statements (set is_deleted = true) instead of delete statements.  I've experimented with a custom SessionExtension, but my implementation is not having the desired effect. Here is my code:

class SoftDeleteExtension(SessionExtension):

    def before_flush(self, session, flush_context, instances):

        print("SoftDeleteExtension::before_flush()")

        for instance in session.deleted:

            if not attributes.instance_state(instance).has_identity:

                continue

            instance._deleted = True   # Modify the deleted instance

            session.deleted.pop()        # Remove the instance from the list of entities to be deleted

Thank you and kind regards,

Jaco

[mike bayer]

On 05/17/2017 05:25 PM, Jaco Breitenbach wrote:
> Thank you, Jonathan, that wiki example has helped a lot. I've extended
> it a bit to also add the extra condition to joined tables.
>
> I want to take it one step further now and automatically convert
> session.delete()s into update statements (set is_deleted = true) instead
> of delete statements. I've experimented with a custom SessionExtension,

don't use "Extensions", use the event system:

http://docs.sqlalchemy.org/en/rel_1_1/orm/events.html?highlight=before_flush#sqlalchemy.orm.events.SessionEvents.before_flush

> session.deleted.pop() # Remove the instance from the
> list of entities to be deleted

ouch...that's not going to do anything. everything in Python is
mutable and it's kind of hard to make every collection everywhere that
only is a view of things immutable (though this is custom collection,
could be made immutable with a flag or something). To undo the delete,
add the object back, session.add(object).

>
>
> Thank you and kind regards,
> Jaco
>
>
> On Tuesday, 16 May 2017 17:20:12 UTC+1, Jonathan Vanasco wrote:
>
> The FAQ
> http://docs.sqlalchemy.org/en/latest/faq/sessions.html#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query
> <http://docs.sqlalchemy.org/en/latest/faq/sessions.html#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query>
>
> points to an example in the wiki:
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery

> <https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jaco Breitenbach]

Hi Mike,

Thank you very much.  I've managed to get it working after a fashion.  Unfortunately this technique seems to interfere with the automatic clean-up of the secondary (mapping) table in the case of a many-to-many relationship.  In the example below I have a contact_group table and user table and a many-to-many relationship set up via a contact_group_user_map table.  Since I've used backref in defining the relationship, I expect the row in contact_group_user_map to automatically be deleted when I do session.delete(userObj).  This works perfectly as long as the before_flush handler is disabled.  If I intercept the delete of userObj and turn it into an update, the delete from contact_group_user_map no longer happens.  I've noticed that the list of objects in session.deleted which is passed to the before_flush handler does not include the contact_group_user_map entity, so I guess the determination to delete the row from contact_group_user_map takes place after the before_flush handler returns.  Do you have any advice on resolving this issue?

from sqlalchemy import create_engine, event, BigInteger, Column, DateTime, Float, ForeignKey, Index, Integer, String, text

from sqlalchemy.orm import attributes, relationship, sessionmaker

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy_repr import PrettyRepresentableBase

Base = declarative_base(cls=PrettyRepresentableBase)

metadata = Base.metadata

engine = None

Session = None

def handle__before_flush(session, flush_context, instances):

    print("handle__before_flush()")

    for instance in session.deleted:

        print(instance)

        if not attributes.instance_state(instance).has_identity:

            continue

        if not hasattr(instance, '_deleted'):

            continue

#        instance._deleted = True

#        session.add(instance)

def getSession(conn):

    global engine, Session

    if engine is None or Session is None:

        engine = create_engine(conn, echo=True)

        Session = sessionmaker(bind=engine, query_cls=SoftDeleteQuery)

        event.listen(Session, 'before_flush', handle__before_flush)

    return Session()

class ContactGroup(Base):

    __tablename__ = 'contact_group'

    id = Column(BigInteger, primary_key=True)

    _deleted = Column(Integer, nullable=False, server_default=text("'0'"))

    name = Column(String(200), nullable=False)

    description = Column(String(1000))

    users = relationship("User", secondary="contact_group_user_map", backref="contact_groups")

class ContactGroupUserMap(Base):

    __tablename__ = 'contact_group_user_map'

    id = Column(BigInteger, primary_key=True)

    contact_group_id = Column(ForeignKey(u'contact_group.id'), nullable=False, index=True)

    user_id = Column(ForeignKey(u'user.id'), nullable=False, index=True)

    contact_group = relationship(u'ContactGroup')

    user = relationship(u'User')

class User(Base):

    __tablename__ = 'user'

    id = Column(BigInteger, primary_key=True)

    _deleted = Column(Integer, nullable=False, server_default=text("'0'"))

    username = Column(String(200), nullable=False, unique=True)

    password = Column(String(1000))

Thank you and kind regards,

Jaco

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

On 05/18/2017 09:20 AM, Jaco Breitenbach wrote:
> Hi Mike,
>
> Thank you very much. I've managed to get it working after a fashion.
> Unfortunately this technique seems to interfere with the automatic
> clean-up of the secondary (mapping) table in the case of a many-to-many
> relationship. In the example below I have a contact_group table and
> user table and a many-to-many relationship set up via a
> contact_group_user_map table. Since I've used backref in defining the
> relationship, I expect the row in contact_group_user_map to
> automatically be deleted when I do session.delete(userObj). This works
> perfectly as long as the before_flush handler is disabled. If I
> intercept the delete of userObj and turn it into an update, the delete
> from contact_group_user_map no longer happens.

well you are turning delete into update, so if you also want to mutate
that collection, you need to do that manually. should be easy w/ the
backref, e.g.:

my_soft_deleted_instance.users = []

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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

> <mailto:sqlalchemy+...@googlegroups.com>.