Hello,
I looked closely at Query implementation and I ended up with this:
from sqlalchemy.orm.query import Query
import sqlalchemy.event as event
class DeletedAtQuery(Query):
""" Query adding a default condition that,
if deleted_at exists, it should be NULL
for the rows returned """
def get(self, ident):
return Query.get(self.populate_existing(), ident)
def __iter__(self):
return Query.__iter__(self.private())
def from_self(self, *ent):
return Query.from_self(self.private(), *ent)
def private(self):
mzero = self._mapper_zero()
if mzero is not None:
if hasattr(mzero.class_, 'deleted_at'):
crit = mzero.class_.deleted_at == None
return self.filter(crit)
return self
def delete(self, synchronize_session='evaluate'):
context = self._compile_context()
assert len(context.statement.froms) == 1
if 'deleted_at' in context.statement.froms[0].columns:
self.update({'deleted_at': int(time.time())},
synchronize_session=synchronize_session)
return
super(DeletedAtQuery, self).\
delete(synchronize_session=synchronize_session)
def paranoid_delete(session, flush_context, instances):
""" Intercepts item deletions (single item deletions)
and, if there is a field deleted_at for that
object, it's updating deleted at instead of really
deleting the object """
for obj in session.deleted:
if hasattr(obj, 'deleted_at'):
print "moving object back: ", obj
obj.deleted_at = int(time.time())
session.add(obj)
def configure_events(session):
""" Helper for applying before_flush hook """
event.listen(session, 'before_flush', paranoid_delete)
I'm using _compile_context private method, and I would like to know if it's
anything I could improve in order to get to a complete implementation of
safe delete.
Thank you,
Andrei Chirila
On Tue, Sep 20, 2011 at 4:18 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On Sep 20, 2011, at 10:12 AM, Andrei Chirila wrote:
>
> Hello Michael,
>
> Thank you :)
>
> I changed the code according to your indications:
>
> def paranoid_delete(session, flush_context, instances):
> """ Intercepts item deletions (single item deletions)
> and, if there is a field deleted_at for that
> object, it's updating deleted at instead of really
> deleting the object """
> # print "current session", session
> # print "context", flush_context
> # print "dirty", session.dirty
> # print "deleted", session._deleted
> for obj in session.deleted:
> # print ">> deleted object:", instance, " -> ", obj
> if hasattr(obj, 'deleted_at'):
> print "moving object back: ", obj
> obj.deleted_at = int(time.time())
> session.add(obj)
>
> and it seems to do what it was intended.
>
> Do you know where could I add a hook for before_bulk_delete?
>
>
> Well since you're subclassing Query I'd add an overriding delete() method
> to your LimitingQuery, do your hook in there, then call the delete() method
> of Query itself.
>
>
>
>
> Thank you,
> Andrei Chirila
>
>
> On Tue, Sep 20, 2011 at 3:59 PM, Michael Bayer
> <mike...@zzzcomputing.com>wrote:
>
>>
>> On Sep 20, 2011, at 8:33 AM, Andrei Chirila wrote:
>>
>> > Hello,
>> >
>> > I'm working at implementing a safe delete on my models (having a
>> > deleted_at field which is checked and setting a deleted_at field when
>> > a model is deleted).
>> >
>> > I looked over the LimitingQuery recipe and at
>> > SessionEvents.before_flush and bellow is my solution. As I'm kind of
>> > new SqlAlchemy user, I would like to ask if anyone can see any obvious
>> > mistake in the following code (I'm sure I missed bulk deletion and
>> > session explunge, but I don't really know how to integrate them).
>>
>> I'd stay away from identity_map.add(object), as that skips a lot of
>> bookkeeping that occurs within the Session. and definitely *never* modify
>> anything with an _underscore on Session, your code becomes totally non
>> portable to even small changes in SQLAlchemy (which occur frequently as we
>> have new releases, bugfixes you'll want, etc). The public (read-only)
>> accessor for the "deleted" collection is Session.deleted.
>>
>> If you have an object marked for deletion, you can re-add it,
>> session.add(object), will remove it from the deleted list.
>>
>> If there's something you need the Session to do that there is not a public
>> API for, ask me on the list here first, as I'd rather ensure there are
>> maintainable paths for use cases.
>>
>>
>>
>>
>> >
>> > Any feedback would be appreciated.
>> >
>> > Thank you,
>> > Andrei Chirila
>> >
>> > from sqlalchemy.orm.query import Query
>> > import sqlalchemy.event as event
>> >
>> >
>> > class DeletedAtQuery(Query):
>> > """ Query adding a default condition that,
>> > if deleted_at exists, it should be NULL
>> > for the rows returned """
>> >
>> > def get(self, ident):
>> > return Query.get(self.populate_existing(), ident)
>> >
>> > def __iter__(self):
>> > return Query.__iter__(self.private())
>> >
>> > def from_self(self, *ent):
>> > return Query.from_self(self.private(), *ent)
>> >
>> > def private(self):
>> > mzero = self._mapper_zero()
>> > if mzero is not None:
>> > if hasattr(mzero.class_, 'deleted_at'):
>> > crit = mzero.class_.deleted_at == None
>> >
>> > return self.filter(crit)
>> > return self
>> >
>> >
>> > def paranoid_delete(session, flush_context, instances):
>> > """ Intercepts item deletions (single item deletions)
>> > and, if there is a field deleted_at for that
>> > object, it's updating deleted at instead of really
>> > deleting the object """
>> > # print "current session", session
>> > # print "context", flush_context
>> > # print "dirty", session.dirty
>> > # print "deleted", session._deleted
>> > deleted_copy = set(session._deleted)
>> > for instance in deleted_copy:
>> > obj = instance.obj()
>> > # print ">> deleted object:", instance, " -> ", obj
>> > if hasattr(obj, 'deleted_at'):
>> > # print "moving instance to dirty states"
>> > instance.deleted = False
>> > obj.deleted_at = int(time.time())
>> > session.identity_map.add(instance)
>> > del session._deleted[instance]
>> >
>> >
>> > def configure_events(session):
>> > """ Helper for applying before_flush hook """
>> > event.listen(session, 'before_flush', paranoid_delete)
>> >
>> > if __name__ == "__main__":
>> >
>> > import time
>> > from sqlalchemy import *
>> > from sqlalchemy.orm import *
>> > from sqlalchemy.ext.declarative import declarative_base
>> >
>> > Base = declarative_base()
>> >
>> > class Group(Base):
>> > __tablename__ = 'groups'
>> >
>> > id = Column(Integer, primary_key=True)
>> > name = Column(String)
>> > users = relation("User", backref="group", cascade="all")
>> >
>> > def __repr__(self):
>> > return "<Group(%d, %s)>" % (self.id, self.name)
>> >
>> > class User(Base):
>> > __tablename__ = 'users'
>> >
>> > id = Column(Integer, primary_key=True)
>> > name = Column(String)
>> > group_id = Column(Integer, ForeignKey('groups.id'))
>> > deleted_at = Column(Integer)
>> > pets = relation("Pet", backref="user", cascade="all, delete-
>> > orphan")
>> >
>> > def __repr__(self):
>> > return "<User(%d, %s)>" % (self.id, self.name)
>> >
>> > class Pet(Base):
>> > __tablename__ = 'pets'
>> >
>> > id = Column(Integer, primary_key=True)
>> > name = Column(String)
>> > user_id = Column(Integer, ForeignKey('users.id'),
>> > nullable=False)
>> > deleted_at = Column(Integer)
>> >
>> > def __repr__(self):
>> > return "<Pet(%d, %s)>" % (self.id, self.name)
>> >
>> > engine = create_engine("sqlite://", echo=False)
>> > Base.metadata.create_all(engine)
>> >
>> > ClearSession = sessionmaker(bind=engine)
>> > Session = sessionmaker(bind=engine, query_cls=DeletedAtQuery)
>> >
>> > sess = Session()
>> > configure_events(sess)
>> > clearsess = ClearSession()
>> >
>> > sess.add_all([Group(name='Group A', users=[
>> > User(name='User A1', pets=[
>> > Pet(name='Mause A1'),
>> > Pet(name='Cat A1')])]),
>> > Group(name='Group B', users=[
>> > User(name='User B1'),
>> > User(name='User B2', pets=[
>> > Pet(name='Dog B2'),
>> > Pet(name='Mause B2'),
>> > Pet(name='Hamster B2')
>> > ]),
>> > User(name='User B3', pets=[
>> > Pet(name='Fish B3'),
>> > Pet(name='Snake B3'),
>> > Pet(name='Horse B3')
>> > ])])])
>> >
>> > sess.commit()
>> >
>> > group_a = sess.query(Group).filter(Group.name == 'Group
>> > A').first()
>> > assert group_a
>> >
>> > users_in_a = group_a.users
>> > assert users_in_a
>> > assert len(users_in_a) == 1
>> > users_in_a_by_query = sess.query(User).\
>> > filter(User.group_id == group_a.id).\
>> > all()
>> >
>> > assert users_in_a_by_query
>> > assert len(users_in_a_by_query) == 1
>> >
>> > pets_in_a = users_in_a[0].pets
>> > assert pets_in_a
>> > assert len(pets_in_a) == 2
>> >
>> > # delete one pet
>> > pet_id = pets_in_a[0].id
>> > sess.delete(pets_in_a[0])
>> > sess.commit()
>> >
>> > pets_in_a = users_in_a[0].pets
>> > assert pets_in_a
>> > assert len(pets_in_a) == 1
>> >
>> > pet = clearsess.query(Pet).filter(Pet.id == pet_id).first()
>> > assert pet
>> > assert pet.deleted_at
>> >
>> > sess.delete(users_in_a[0])
>> > sess.commit()
>> >
>> > assert len(sess.query(User).\
>> > filter(User.group_id == group_a.id).\
>> > all()) == 0
>> > assert len(clearsess.query(User).\
>> > filter(User.group_id == group_a.id).\
>> > all()) == 1
>> > assert len(sess.query(Pet).all()) == 6
>> > assert len(clearsess.query(Pet).all()) == 8
>> >
>> > group_b = sess.query(Group).filter(Group.name == 'Group
>> > B').first()
>> > assert group_b
>> > assert len(group_b.users) == 3
>> >
>> > # don't know how to implement batch delete hook yet
>> > #sess.query(Pet).delete()
>> > #sess.commit()
>> >
>> > #assert len(sess.query(Pet).all()) == 0
>> > #assert len(clearsess.query(Pet).all()) == 8
>> >
>> > sess.delete(group_b)
>> > sess.commit()
>> > groub_b_deleted = sess.query(Group).filter(Group.name == 'Group
>> > B').first()
>> > assert groub_b_deleted is None
>> >
>> > assert len(sess.query(Pet).all()) == 0
>> > assert len(clearsess.query(Pet).all()) == 8
>> > assert len(sess.query(User).all()) == 0
>> > assert len(clearsess.query(User).all()) == 4
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups "sqlalchemy" group.
>> > To post to this group, send email to sqlalchemy@googlegroups.com.
>> > To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> > For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>> >
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>>
>
>
> --
> Mobile: +49 151 42647382
> e-Mail: andre...@gmail.com
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
Mobile: +49 151 42647382
e-Mail: andre...@gmail.com
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
