Well, the articles I found seemed straightforward enough, but things get a bit tricky when you introduce relationships between tables. The article on codeproject does mention how to handle foreign keys: create a unique identifier per ‘object’ that you want to version (they call it the ‘PermanentRecordId’). This identifier cannot be used as a proper foreign key, since the Audit (or version) table contains multiple rows sharing the same key.
But of course the above would not keep us from using it as one in a JOIN construct. However, a side effect is that if two tables are teamed up in such a way, they are versioned independently from each other. This wouldn’t pose a problem for the specific example that the article gives, but could pose one in different circumstances.
I modified SQLAlchemy to support versioned tables. Took a bit of work, but managed to get a number of basic tests working. Didn’t see one thing coming, though. Let me illustrate in (pseude)code:
> versioned_object = VersionedObject(attr='hello') > VersionedObject.query().all() [<VersionedObject@0x01>] > versioned_object.attr = 'goodbye' > VersionedObject.query().all() [<VersionedObject@0x01>, <VersionedObject@0x02>] > versioned_object <VersionedObject@0x01>;
In words: once you modify an attribute and commit to the DB your ORM object (instance) will be unusable (since SQLAlchemy uses the PrimaryKey to identify the thing in the DB).
Argh!
I suppose that if you modify an object only once after retrieving it from the DB –which might be a frequent occurrence for webservices– the thing is still somewhat useful.