crud.archive for full app auditing

[mdipierro]

Here is a new feature in trunk.

Say you have the following table:

db.define_table('person',
Field('name'),

Field('created_by',default=auth.user_id,update=auth.user_id,writable=False),

Field('created_on',default=request.now,update=request.now,writable=False))

and you want to store all previous version of this record as it gets
edited. Now you can do:

1) create a table where to store them:

db.define_table('person_archive',Field('current_record',db.person),db.person)

(the name has to be <table>_archive and it must contain a
'current_record' field pointing to the actual table, it must also
contain by all fields of the actual table).

2) use onaccept=crud.archive in crud.update

def index():
form = crud.update(db.person, request.args(0),
onaccept=crud.archive)
return dict(form=form)

Details:
- actually you do not need step 1, the archive table is created
automatically in step 2. you need step 1 only if/when you want to
access the archive table for other purpose such as retrieving the
data.
- you can change 'person_archive' and 'current_record' by passing
parameters to crud.archive.
- there is nothing special about the fields 'created_by' and
'created_on', you should have them but can call them as you like.

Pros:

- Just adding "onaccept=crud.archive" to crud.update of your current
app makes sure all changes are archived and you have full auditing for
you app.
- references never break (because current_record never changes id).
- It does not slow down the app because current data and archived data
are on different tables
- no unnecessary code since the archive table is defined only when
needed
- works on GAE

Cons:
- if you delete a record, the last one gets archived but it does not
record who deleted the record. To achieve this you would need an extra
field, for example "active", and set this to false, instead of
deleting the record. Then modify logic of the app to use this "active"
field. Not really a cons actually. This is the only way to do it that
allows users to un-delete records or restore previous revisions
without breaking links.

[Jonathan Lundell]

On Feb 12, 2010, at 9:18 PM, mdipierro wrote:

> Here is a new feature in trunk.

Not to look a gift horse in the mouth, but a docstring would be nice.

> --
> You received this message because you are subscribed to the Google Groups "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
>

[mdipierro]

LOL. Ok tomorrow.

[Matthew]

Is there a way to do this (and to use CRUD, in general) in a scheduled
job rather than just the web frontend?

[mdipierro]

given

db.define_table('mytable',....)
db.define_table('mytable_history',

Field('current_record',db.mytable),
db.mytable)

before

db(db.mytable.id==id).update(**fields)

you need to retrieve and archive the current record

current = db.mytable[id]
current.current_record = current.id # move id

db.mytable_history.insert(**mytable_history._filter_fields(current))