Hello Joel,
web2py has change since one year ago. Now you simply do:
db.define_table('mytable',....fields...,auth.signature)
... define more table ...
auth.enable_record_versioning(db)
and mytable will have a mytable_archive that works as Nick suggested.
On Tuesday, 3 July 2012 16:15:26 UTC-5, Joel Carrier wrote:
>
> 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?
>>
>
