Track changes to rows

[brettatoms]

Implement some sort of versioning for rows to keep a history of any changes.

See http://sqlobject.org/Versioning.html and related code for ideas.

[brettatoms (brettatoms)]

See:
http://martinfowler.com/ap2/auditLog.html
http://groups.google.com/group/sqlalchemy/browse_thread/thread/5f742c294eeccf33
http://groups.google.com/group/sqlalchemy/browse_thread/thread/97681ed0be198729/adfcc885c516ebff?lnk=gst&q=Auditlog#adfcc885c516ebff

[brettatoms (brettatoms)]

Basically the idea is to implement our own MapperExtension that will log the changes after they have been committed to the database. The important part is that we don't miss any changes. We could need to check that every changed attribute on the mapper passed to the MapperExtension is recorded either as changes on a particular row on a particular table.

Also, we can't forget to record the user name of the person that made the change.

[brettatoms (brettatoms)]

Should also create some sort of view, maybe with the command :changes that would show a history of all the changes made in order of the change.

Providing a way to rollback to a certain date would be nice but I wouldn't consider it a priority.

[brettatoms (brettatoms)]

The one problem is that some changes could be missed if the changes aren't made withed the mapped object, i.e. are done directly via insert statements. This can cause temporal "jumps" causing some changes not to show up.

Realistically, changes are pretty much always made using mapped objects unless we're doing and import and then we probably wouldn't want to record every import anyway.

[brettatoms (brettatoms)]

See http://martinfowler.com/articles/evodb.html

[brettatoms (brettatoms)]

Also see:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/d91c83f33e2931c8/42c960c1664c3b61?show_docid=42c960c1664c3b61