What's a good general way to look SQLAlchemy transactions, complete with authenticated user, etc?

Question

I'm using SQLAlchemy's declarative extension. I'd like all changes to tables logs, including changes in many-to-many relationships (mapping tables). Each table should have a separate "log" table with a similar schema, but additional columns specifying when the change was made, who made the change, etc.

My programming model would be something like this:

row.foo = 1
row.log_version(username, change_description, ...)

Ideally, the system wouldn't allow the transaction to commit without row.log_version being called.

Thoughts?

Answer 1

There are too many questions in one, so they that full answers to all them won't fit StackOverflow answer format. I'll try to describe hints in short, so ask separate question for them if it's not enough.

Assigning user and description to transaction

The most popular way to do so is assigning user (and other info) to some global object (threading.local() in threaded application). This is very bad way, that causes hard to discover bugs.

A better way is assigning user to the session. This is OK when session is created for each web request (in fact, it's the best design for application with authentication anyway), since there is the only user using this session. But passing description this way is not as good.

And my favorite solution is to extent Session.commit() method to accept optional user (and probably other info) parameter and assign it current transaction. This is the most flexible, and it suites well to pass description too. Note that info is bound to single transaction and is passed in obvious way when transaction is closed.

Discovering changes

There is a model_obj._sa_instance_state property that have all information you need. The most useful for you is probably model_obj._sa_instance_state.committed_state dictionary which contains original state for changed fields (including many-to-many relations!). There is also sqlalchemy.org.attributes.get_history() function, but it less convenient.

Automatically storing changes

SQLAlchemy supports mapper extension that can provide hooks before and after update, insert and delete. You need to provide your own extension with all before hooks (you can't use after since the state of objects is changed on flush). For declarative extension it's easy to write a subclass of DeclarativeMeta that adds a mapper extension for all your models. Note that you have to flush changes twice if you use mapped objects for log, since a unit of work doesn't account objects created in hooks.

Answer 2

We have a pretty comprehensive "versioning" recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/LogVersions . It seems some other users have contributed some variants on it. The mechanics of "add a row when something changes at the ORM level" are all there.

Alternatively you can also intercept at the execution level using ConnectionProxy, search through the SQLA docs for how to use that.