Hi Nick Name,
Did you ever find a solution that met all your requirements? What you've
described is exactly what I am facing now.
Joel
On Friday, May 6, 2011 6:18:49 PM UTC-4, nick name wrote:
>
> 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?
>
