4

I'm starting to use SQLAlchemy for a new project where I was planning to implement an audit trail similar to the one proposed on these questions:

As I will have the full history of the "interesting" objects, I was thinking in allowing users to rollback to a given version, giving them the possibility to have unlimited undo.

Would this be possible to be done in a clean way with SQLAlchemy?

What would be the correct way to expose this feature in the internal API (business logic and ORM)?

I was something along the ways of user.rollback(ver=42).

Francisco Puga
  • 23,869
  • 5
  • 48
  • 64
Esteban Küber
  • 36,388
  • 15
  • 79
  • 97

1 Answers1

8

Although I haven't used SQLAlchemy specifically, I can give you some general tips that can be easily implemented in any ORM:

  • Separate out the versioned item into two tables, say Document and DocumentVersion. Document stores information that will never change between versions, and DocumentVersion stores information that does change.
  • Give each DocumentVersion a "parent" reference. Make a foreign key to the same table, pointing to the previous version of the document.
  • Roll back to previous versions by updating a reference from Document to the "current" version. Don't delete versions from the bottom of the chain.
  • When they make newer versions after rolling back, it will create another branch of versions.

Example, create A, B, C, rollback to B, create D, E:

(A)
 |
(B)
 | \
(C) (D)
     |
    (E)
Christian Oudard
  • 48,140
  • 25
  • 66
  • 69
  • The allowing of branching is cool, but wouldn't it be really really hard to do merging down the road? – Esteban Küber Sep 23 '09 at 20:23
  • 2
    This is not like source control. From the user's perspective, you have totally deleted C. You will never need to merge it in. The only reason it is still around is as an audit trail, or in case you need to manually fish out a deleted item for some reason. – Christian Oudard Sep 29 '09 at 20:12