My use of web2py requires an audit trail for (essentially) all database
tables; The preferable way to do that is to keep a "foo_history" table for
each table "foo". The records (both audited and original) need to have a
"revision" that increments with each update, and "last modified on", "last
modified by" fields that get updated with the user_id and the time on each
update.
At this point in time, there is no need to delete records - but if there
was, I'm not sure what the proper way would be -- possibly having another
field "deleted" on the audit trail table, which would be set to "true" on
the revision that was the latest version.
Now, the built in Crud system gives me most of what I want (I don't see how
to make it keep the history of the deleted table above, though for now
that's not a problem). But there's quite a bit of application logic that
needs this which cannot be implemented using the Crud.
The "classic" solution is to write this as stored procedures. I would like
to avoid that for portability and maintenance reasons. Another solution is
to use my own "update/insert/delete" methods that update audit tables
whenever a record updates. That's error prone, and I would rather avoid
that, especially since I _do_ want to build on web2py infrastructure such as
Crud for other things.
The solution that I'm thinking of is to hook the DAL; specifically, the
following functions would be sufficient for me; It seems that all database
modifications go through them eventually, whether called directly or when
manipulating Rows or Row objects.
insert: (copy all newly added records to the audit table table - easy)
update: (copy all modified records to the audit table, add a 'revision =
revision + 1' to the SET code - easy)
delete: (copy all deleted records to the audit table, adding a 'deleted =
True' - possibly a little more complicated)
For completeness, hooking "truncate" and "drop table" to do the same thing
(either raise an error if that is disallowed, or transform it into a delete
of each individual record so that everything goes into the audit). And I
would also disable migrations if I do that, because they may change the
database without updating my audit trail.
Now, before I start working on a monkey patch (or full patch) to dal.py -
perhaps I've missed a simpler way to do the same thing?
If I haven't - I'm currently working with Postgres and SQLite; I would like
to contribute the resulting patch to the web2py project, but have no time to
evaluate and test on other databases/gae at the moment. Is there anything
else I should be aware of that would be required for other databases?
====
Finally, a question about transaction rollback - in my app, I sometimes want
to rollback everything (as if I did e.g. raise
MySecurityException("Forbidden")), but actually return a non-error HTML
result to the user. I am aware of db.rollback(), but the exception handling
code does more, e.g., today it does:
if response._custom_rollback:
response._custom_rollback()
else:
BaseAdapter.close_all_instances('rollback')
I would suggest adding an "response._transaction_verb" variable, which would
default to 'COMMIT', but could be changed to 'ROLLBACK', and would determine
what to do with the database (commit/rollback) in the case of a successful
completion - meaning raising of an HTTP exception or simple return from
controller.
On other exception, I would expect rollback always (and the application code
can call db.commit or BaseAdapter.close_all_instances('commit') or whatever
if needed) - because committing on error is something that has to be
extremely explicit.
What say you?
