how to use sqlalchemy versioning on relationships

Question

I'm trying to implement versioning for my sqlalchemy database (using the example here: http://docs.sqlalchemy.org/en/rel_0_7/orm/examples.html#versioned-objects, and the history_meta.py from the source tarball.)

Using the example, I'm able to successfully get an old version of an object:

history = Person.__history_mapper__.class_
old_person = history.filter(Person.id = instance.id, 
    history.version==someoldversion).one()

I can also get old versions of any objects it points to.

The way it is implemented in the sqlalchemy versioning code is by adding an extra table called person_history. This table contains all the fields the Person had in a specific version + a version field.

However, what I need is to put versioning on relations. For example, perhaps this Person used to have a Car but not anymore, and I need to get the version where (s)he had. Can anyone suggest the best way to extend the example to make this possible?

Answer 1

Your question might be solved more easily as a data modeling problem than figuring out a feature in SA. In your Person-Car example, one could imagine the implied many-to-many relationship (car_ownership_timeperiods) as a Title, the object documenting a Person's ownership of a Car.

By promoting the implicit many-to-many relationship table into a full-blown object in your schema, you retain the ability to relate Persons and Cars, and can use all of SA's features on Titles as well, including tracking history if you wish (although the query for your example, which cars a person owned in the past, is a plain equijoin).