python - SQLAlchemy audit logging; how to handle deletes? -

i'm using modified version of versioning code example comes sqlalchemy record user id , date on changes. however, want modify deletes done marking is_deleted type flag instead of running actual sql delete. problem i'm not sure how capture delete , replace update.

here's have far:

''' http://docs.sqlalchemy.org/en/rel_0_8/orm/examples.html?highlight=versioning#versioned-objects ''' sqlalchemy.ext.declarative import declared_attr sqlalchemy.orm import mapper, class_mapper, attributes, object_mapper, scoping sqlalchemy.orm.session import session sqlalchemy.orm.exc import unmappedclasserror, unmappedcolumnerror sqlalchemy import table, column, foreignkeyconstraint, datetime, string, boolean sqlalchemy import event sqlalchemy.orm.properties import relationshipproperty datetime import datetime sqlalchemy.schema import foreignkey sqlalchemy.sql.expression import false  def col_references_table(col, table):     fk in col.foreign_keys:         if fk.references(table):             return true     return false  def _history_mapper(local_mapper):     cls = local_mapper.class_      # set "active_history" flag     # on on column-mapped attributes old version     # of info loaded (currently sets on attributes)     prop in local_mapper.iterate_properties:         getattr(local_mapper.class_, prop.key).impl.active_history = true      super_mapper = local_mapper.inherits     super_history_mapper = getattr(cls, '__history_mapper__', none)      polymorphic_on = none     super_fks = []     if not super_mapper or local_mapper.local_table not super_mapper.local_table:         cols = []         column in local_mapper.local_table.c:             if column.name.startswith('version_'):                 continue              col = column.copy()             col.unique = false              if super_mapper , col_references_table(column, super_mapper.local_table):                 super_fks.append((col.key, list(super_history_mapper.local_table.primary_key)[0]))              cols.append(col)              if column local_mapper.polymorphic_on:                 polymorphic_on = col          if super_mapper:             super_fks.append(('version_datetime', super_history_mapper.base_mapper.local_table.c.version_datetime))             super_fks.append(('version_userid', super_history_mapper.base_mapper.local_table.c.version_userid))             super_fks.append(('version_deleted', super_history_mapper.base_mapper.local_table.c.version_deleted))             cols.append(column('version_datetime', datetime, default=datetime.now, nullable=false, primary_key=true, info={'colanderalchemy': {'exclude': true}}))             cols.append(column('version_userid', string(60), foreignkey("user.login"), nullable=true, info={'colanderalchemy': {'exclude': true}}))             cols.append(column('version_deleted', boolean, server_default=false(), nullable=false, info={'colanderalchemy': {'exclude': true}}))         else:             cols.append(column('version_datetime', datetime, default=datetime.now, nullable=false, primary_key=true, info={'colanderalchemy': {'exclude': true}}))             cols.append(column('version_userid', string(60), foreignkey("user.login"), nullable=true, info={'colanderalchemy': {'exclude': true}}))             cols.append(column('version_deleted', boolean, server_default=false(), nullable=false, info={'colanderalchemy': {'exclude': true}}))          if super_fks:             cols.append(foreignkeyconstraint(*zip(*super_fks)))          table = table(local_mapper.local_table.name + '_history', local_mapper.local_table.metadata,            *cols         )     else:         # single table inheritance.  take additional columns may have         # been added , add them history table.         column in local_mapper.local_table.c:             if column.key not in super_history_mapper.local_table.c:                 col = column.copy()                 col.unique = false                 super_history_mapper.local_table.append_column(col)         table = none      if super_history_mapper:         bases = (super_history_mapper.class_,)     else:         bases = local_mapper.base_mapper.class_.__bases__     versioned_cls = type.__new__(type, "%shistory" % cls.__name__, bases, {})      m = mapper(             versioned_cls,             table,             inherits=super_history_mapper,             polymorphic_on=polymorphic_on,             polymorphic_identity=local_mapper.polymorphic_identity             )     cls.__history_mapper__ = m      if not super_history_mapper:         local_mapper.local_table.append_column(             column('version_datetime', datetime, default=datetime.now, nullable=false, primary_key=false, info={'colanderalchemy': {'exclude': true}})         )         local_mapper.add_property("version_datetime", local_mapper.local_table.c.version_datetime)         local_mapper.local_table.append_column(             column('version_userid', string(60), foreignkey("user.login"), nullable=true, info={'colanderalchemy': {'exclude': true}})         )         local_mapper.add_property("version_userid", local_mapper.local_table.c.version_userid)         local_mapper.local_table.append_column(             column('version_deleted', boolean, server_default=false(), nullable=false, info={'colanderalchemy': {'exclude': true}})         )         local_mapper.add_property("version_deleted", local_mapper.local_table.c.version_deleted)   class versioned(object):     @declared_attr     def __mapper_cls__(cls):         def map(cls, *arg, **kw):             mp = mapper(cls, *arg, **kw)             _history_mapper(mp)             return mp         return map   def versioned_objects(iter):     obj in iter:         if hasattr(obj, '__history_mapper__'):             yield obj  def create_version(obj, session, deleted = false):     obj_mapper = object_mapper(obj)     history_mapper = obj.__history_mapper__     history_cls = history_mapper.class_      obj_state = attributes.instance_state(obj)      attr = {}      obj_changed = false      om, hm in zip(obj_mapper.iterate_to_root(), history_mapper.iterate_to_root()):         if hm.single:             continue          hist_col in hm.local_table.c:             if hist_col.key.startswith('version_'):                 continue              obj_col = om.local_table.c[hist_col.key]              # value of             # attribute based on mapperproperty related             # mapped column.  allow usage of mapperproperties             # have different keyname of mapped column.             try:                 prop = obj_mapper.get_property_by_column(obj_col)             except unmappedcolumnerror:                 # in case of single table inheritance, there may                 # columns on mapped table intended subclass only.                 # "unmapped" status of subclass column on                 # base class feature of declarative module of sqla 0.5.2.                 continue              # expired object attributes , deferred cols might not in             # dict.  force load no matter using getattr().             if prop.key not in obj_state.dict:                 getattr(obj, prop.key)              a, u, d = attributes.get_history(obj, prop.key)              if d:                 attr[hist_col.key] = d[0]                 obj_changed = true             elif u:                 attr[hist_col.key] = u[0]             else:                 # if attribute had no value.                 attr[hist_col.key] = a[0]                 obj_changed = true      if not obj_changed:         # not changed, have relationships.  ok         # check         prop in obj_mapper.iterate_properties:             if isinstance(prop, relationshipproperty) , \                 attributes.get_history(obj, prop.key).has_changes():                 obj_changed = true                 break      if not obj_changed , not deleted:         return      attr['version_datetime'] = obj.version_datetime     attr['version_userid'] = obj.version_userid     attr['version_deleted'] = obj.version_deleted     hist = history_cls()     key, value in attr.items():         setattr(hist, key, value)     session.add(hist)     obj.version_datetime = datetime.now()     obj.version_userid = getattr(session, 'userid', none)     obj.version_deleted = deleted  def versioned_session(session):     @event.listens_for(session, 'before_flush')     def before_flush(session, flush_context, instances):         obj in versioned_objects(session.deleted):             create_version(obj, session, deleted = true)         obj in versioned_objects(session.dirty):             create_version(obj, session)  def add_userid_to_session(userid, session):     if isinstance(session, scoping.scoped_session):         thread_local_session = session.registry()         thread_local_session.userid = userid     elif isinstance(session, session):         session.userid = userid     else:         raise typeerror("not sure how add userid session of type {}".format(type(session)))  

and here's how i'm using (all non-essential parts have been cut out):

base = declarative_base()  class user(versioned, base):     __tablename__ = 'user'     login = column(string(60), primary_key=true, nullable=false)      groups = association_proxy('user_to_groups', 'group', creator=lambda group: usertogroup(group_name=group.name))      def __init__(self, login, groups=none):         self.login = login         if groups:             group in groups:                 self.groups.append(group)  class group(versioned, base):     __tablename__ = 'group'     name = column(string(100), primary_key=true, nullable=false)     description = column(string(100), nullable=true)      users = association_proxy('group_to_user', 'user', creator=lambda user: usertogroup(user_login=user.login))      def __eq__(self, other):         return self.name == other.name  class usertogroup(versioned, base):     __tablename__ = 'user_to_group'     user_login = column(string(60), foreignkey(user.login), primary_key=true)     group_name = column(string(100), foreignkey(group.name), primary_key=true)      user = relationship(user, backref=backref('user_to_groups', cascade='all, delete-orphan'))     group = relationship(group, backref=backref('group_to_user', cascade='all, delete-orphan'))   session.configure(bind=engine) add_userid_to_session("test", session.registry()) versioned_session(session) user = session.query(user).filter(user.login=='test').one() user.groups.remove(group(name ="g:admin")) 

before running code database has 1 user called 'test' , 2 groups user attached called 'g:admin' , 'g:superadmin'.

what is: copy existing user_to_group entry 'test' => 'g:admin' mapping , copy history table. delete entry user_to_group.

what i'd copy value history table , update entry in user_to_group have version_deleted set true.

i'm thinking way snatch entry out of session.deleted (that's why changed order original code) , modify put session.dirty. i'm not sure "safest" way of doing this.

another issue (which require question) how detect relationships covered in table system makes copy of 'user' row history table , updates version information despite no real changes being made row.

edit: i've decided things bit differently, still have problem... instead of having "deleted" flag in live tables delete content , record history item indicating when deletion occurred. if i'm deleting object directly works correctly. if delete object off of relationship i'm not able properly. delete get's issued relationship table remove link, can't seem figure out how detect deletion in "create_version" method.

for example, if do:

group = session.query(group).filter(group.name=='g:admin').one() group.users.remove(group.users[0]) 

no objects placed in session.deleted. can detect sort of deletion via attributes.get_history(obj, prop.key), seems indicate deletion of usertogroup object group (which want detect , record history item on), indicates deletion of group usertogroup object (which don't want because actual group not being deleted).