Hello Michael, I changed delete as you suggested.
def delete(self, synchronize_session='evaluate'): columns = self.column_descriptions # don't know to delete on multiple tables in # the same statement assert len(columns) == 1 entity = columns[0] if 'type' in entity and \ hasattr(entity['type'], 'deleted_at'): self.update({'deleted_at': int(time.time())}, synchronize_session=synchronize_session) return super(DeletedAtQuery, self).\ delete(synchronize_session=synchronize_session) I hope this covers all the cases. Thank you very much, Andrei Chirila On Wed, Sep 21, 2011 at 4:52 PM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Sep 21, 2011, at 9:53 AM, Andrei Chirila wrote: > > Hello, > > I looked closely at Query implementation and I ended up with this: > > > 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) > > 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. > > > OK so if it were me, I'd look instead at the entities which the Query is > against and look for a deleted_at attribute. The most public API way to > get at the entities being queried is via the query.column_descriptions > accessor: > http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.column_descriptions. > From that you'd pretty much have a list of length 1 since you're doing > query(entity).delete(), you'd take a look at hasattr(entities[0]['type'], > 'deleted_at'). Would be cleaner than the compile_context approach. > > > > > > 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. > > > -- > 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.