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 <[email protected]>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
> <[email protected]>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
>> <[email protected]>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 [email protected].
>>> > To unsubscribe from this group, send email to
>>> [email protected].
>>> > 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 [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group at
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>>
>>
>>
>> --
>> Mobile: +49 151 42647382
>> e-Mail: [email protected]
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> 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 [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
>
>
> --
> Mobile: +49 151 42647382
> e-Mail: [email protected]
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
Mobile: +49 151 42647382
e-Mail: [email protected]
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
