Attached you'll find my first go at a database audit module for web2py. It
will log every change to a table. The crud versioning system is nice, but it
only versions stuff that happens on crud. If you insert/modify records not
through crud, you'd need to manually update it -- the attached code will do
that automatically for you.
The app I'm developing needs a searchable, browseable audit of all changes
to database, for which simple SQL logging is not sufficient.
WARNING: only very lightly tested code, probably doing everything the wrong
way. Use at your own risk.
To use: place audit.py in your models directory; then, after defining a
table with
mytable = db.define_table('mytable', ....)
call:
with_audit(mytable)
It will create a table called 'audit_mytable', which has in addition to all
your fields, 4 more: oid (original id), time, user, and action
(insert/update/delete)
any modification to the original table will result in a record being added;
inserted records are copied after insertion; updated records are copied
after update; deleted records are copied before delete.
KNOWN ISSUES:
* audit table is placed on the same adapter as the audited table - I want to
allow the audit to be on a different db/dal/adapter (needs distributed
transaction to work properly!)
* old id is always integer for lack of a better type; it should be a "weak"
reference type
* reference types are still reference type, so when deleting linked records,
"on delete cascade" will remove audit records, this is bad! other integrity
constraints (notnull, validators etc.) are not copied to audit table -- only
references.
* action type is int, no way to specify efficient char(1) in web2py
* audit happens within same transaction as original update, so you commit or
rollback both -- I think that's expected behaviour.
* On first use, it patches the adapter *class*. This means that, unlike
usual web2py debugging, if you edit audit.py, you'll have to restart web2py.
Writing it now, I can't think of a good reason why I did that rather than
patch the adapter *instance* -- but that's the case for the attached code.
A better solution would probably be to add pre/post hooks to
insert/update/delete in BaseAdapter, but for now the audit.py approach seems
to work on 1.95 - 1.97
#
# General audit support for web2py:
# When a table is set to be "audited", a shadow table is defined for
# it, with 3 additional fields: "oid" (for original id), "audit_time", "audit_user", "audit_type" (insert/update/delete)
# on insert: after insert, the new record is copied to audit table
# on update: after update, the new records are copied to the audit table
# on delete: before deleting, records are copied to the audit table with audit_delete=true
#
# no new/additional transactions. that is, rollback will also roll back the audit; commit
# will also commit the audit.
# TODO: deactivate "on delete cascade" actions -> turn them to "restrict" or even drop the reference?
# TODO: add "idtype" to adapter - a type that can be used to store id - a "weak" reference
# TODO: is there a char(1) record type? audit_type would benefit from that
class AuditError(RuntimeError):
pass
AUDIT_OID = Field("audit_oid", "integer") # -> switch to idtype/weak reference
AUDIT_TIME = Field("audit_time", "datetime")
AUDIT_USER = Field("audit_user", "string") # or auth_user
AUDIT_TYPE = Field("audit_type", "integer") # 1=insert, 2=update, 3=delete
AUDIT_FIELDS = [AUDIT_OID, AUDIT_TIME, AUDIT_USER, AUDIT_TYPE]
AUDIT_TYPE_INSERT = 1
AUDIT_TYPE_UPDATE = 2
AUDIT_TYPE_DELETE = 3
AUDIT_TEMPLATE = 'audit_%s'
def audited_record(shadow, record, audit_type):
"""Make a record into the audit record with a given type in place"""
record[AUDIT_OID.name] = record.id
del record['id'] # audit records gets their own id
del record['update_record'] # since we retrieve id, we get this ?!!?
del record['delete_record'] # since we retrieve id, we get this ?!?!
record[AUDIT_TIME.name] = request.now
record[AUDIT_USER.name] = 'unknown'
record[AUDIT_TYPE.name] = audit_type
shadow.insert(**record)
def audited_insert(self, table, fields):
shadow = self.shadow.get(table._tablename)
if not shadow: return self.unaudited_insert(table, fields)
rid = self.unaudited_insert(table, fields)
if isinstance(rid, int): # either int or Reference
record = table[int(rid)] # retrieve just inserted record
audited_record(shadow, record, AUDIT_TYPE_INSERT)
return rid
raise AuditError, "Cannot audit inserts to table %s"%table
# TODO: how do we audit a change to id? should we block it?
def audited_update(self, tablename, query, fields):
shadow = self.shadow.get(tablename)
if not shadow: return self.unaudited_update(tablename, query, fields)
db = self.db
table = db[tablename]
# find which records are about to be updated by this ....
rows = self.select(query, ['id'], {})
rows = [x['id'] for x in rows] # we only care about the ids.
count = self.unaudited_update(tablename, query, fields) # do the update
# retrieve updated records
rows = self.select(table.id.belongs(rows), [str(field) for field in table], {})
for record in rows:
audited_record(shadow, record, AUDIT_TYPE_UPDATE)
return count
def audited_delete(self, tablename, query):
shadow = self.shadow.get(tablename)
if not shadow: return self.unaudited_delete(table, query)
db = self.db
table = db[tablename]
# retrieve records about to be deleted
rows = self.select(query, [str(field) for field in table], {})
count = self.unaudited_delete(tablename, query) # do the delete
# retrieve updated records
for record in rows:
audited_record(shadow, record, AUDIT_TYPE_DELETE)
return count
def patch_db(dal):
db = dal._adapter
if not hasattr(db,'shadow'): db.shadow = {} # patch instance every time
if hasattr(db, 'unaudited_insert'): return # patch class once
print 'patched db'
x = db.__class__
x.unaudited_insert = x.insert
x.unaudited_delete = x.delete
x.unaudited_update = x.update
x.insert = audited_insert
x.update = audited_update
x.delete = audited_delete
# remove field constraints when creating audit table.
# TODO - switch all references to weakref or at least ON CASCADE RESTRICT?
def with_audit(table):
name = table._tablename
dal = table._db
patch_db(dal)
if name in dal._adapter.shadow: return # audit already set
fields = AUDIT_FIELDS + [Field(field, table[field].type) for field in table.fields if field != 'id']
shadow = db.define_table(AUDIT_TEMPLATE%name, *fields)
dal._adapter.shadow[name] = shadow
