versions.py

from datetime import datetime, timedelta

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Version(Base):
    __tablename__ = 'versions'
    __table_args__ = {'sqlite_autoincrement': True}

    id = Column(Integer, primary_key=True)
    object_id = Column(Integer, ForeignKey('objects.id'))

    def __repr__(self):
        return 'Version(id={.id})'.format(self)


class Object(Base):
    __tablename__ = 'objects'
    __table_args__ = {'sqlite_autoincrement': True}

    id = Column(Integer, primary_key=True)

    # This is great if all I need is the pkey of the version...
    version_id = column_property(
        select([func.max(Version.id)])
        .where(Version.object_id == id)
        .correlate_except(Version),
        deferred=True,
    )

    def __repr__(self):
        return 'Object(id={.id})'.format(self)

    # I was not able to get a version of this to work, but the benefit would be
    # caching the Version object itself, and the ability to use joinedload on
    # it. I think it might be possible with viewonly=True, but I don't want to
    # give up the ability to modify the Version and save it, even though it's
    # obvious assigning to current_version doesn't make sense.
    current_version = relationship(Version,
                                   primaryjoin=Version.id == version_id.expression,
                                   foreign_keys=version_id.expression,
                                   remote_side=Version.id,
                                   )

    # This is fine if I need *all* versions, but I rarely do
    all_versions = relationship(Version,
                                primaryjoin=Version.object_id == id,
                                order_by=Version.id.desc(),
                                foreign_keys=id,
                                uselist=True,
                                )

    # This works with joinedload etc (with joinedload('all_versions')), but it
    # requires all of the versions to be loaded
    @property
    def current_ver(self):
        return self.all_versions[0]

    # More options considered but not adequate: hybrid_property and property,
    # since they also don't interact with joinedloads.


if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:', echo=False)
    Base.metadata.bind = engine
    Base.metadata.create_all()
    session = sessionmaker(bind=engine)()

    o = Object()
    session.add(o)
    session.flush()
    o_id = o.id
    v1 = Version(object_id=o_id)
    v2 = Version(object_id=o_id)
    session.add_all([v1, v2])
    session.commit()

    o = session.query(Object).filter_by(id=o_id).one()
    print 'Object:', o
    print 'Object.current_ver:', o.current_ver
    print 'Object.version_id:', o.version_id
    for v in o.all_versions:
        print v