Using VDM with SQLAlchemy

The Revision object is used to encapsulate changes to the domain model/database. It also allows changes to multiple objects/rows to be part of a single ‘revision’:

class vdm.sqlalchemy.Revision(**kw)

A Revision to the Database/Domain Model.

All versioned objects have an associated Revision which can be accessed via the revision attribute.

class vdm.sqlalchemy.Revisioner(revision_table)

SQLAlchemy MapperExtension which implements revisioning of sqlalchemy mapped objects.

In essence it implements copy on write.

However various additional features such as:

• Checking for ‘real’ changes – often sqlalchemy objects are marked as changed when not (just a related attribute has changed).
• support for ignored attributes (these attributes will be ignored when checking for changes and creating new revisions of the object)

vdm.sqlalchemy.modify_base_object_mapper(base_object, revision_obj, state_obj)

vdm.sqlalchemy.add_stateful_m2m(object_to_alter, m2m_object, m2m_property_name, attr, basic_m2m_name, **kwargs)

Attach active and deleted stateful lists along with the association proxy based on the active list to original object (object_to_alter).

To illustrate if one has:

class Package(object):

    # package_licenses is the basic_m2m_name attribute
    # 
    # it should come from a simple relation pointing to PackageLicense
    # and returns PackageLicense objects (so do *not* use secondary
    # keyword)
    #
    # It will usually not be defined here but in the Package mapper:
    #
    # 'package_licenses':relation(License) ...
    
    package_licenses = ... from_mapper ...

Then after running:

add_stateful_m2m(Package, PackageLicense, 'licenses', 'license',
'package_licenses')

there will be additional properties:

# NB: licenses_active and licenses_deleted are lists of PackageLicense # objects while licenses (being an assoc proxy) is a list of Licenses licenses_active # these are active PackageLicenses licenses_deleted # these are deleted PackageLicenses licenses # these are active Licenses

@param attr: the name of the attribute on the Join object corresponding to

the target (e.g. in this case ‘license’ on PackageLicense).

@arg **kwargs: these are passed on to the DeferredProperty.

Example

Here is a full demonstration of using vdm which can be found in vdm/sqlalchemy/demo.py:

'''Demo of vdm for SQLAlchemy.

This module sets up a small domain model with some versioned objects. Code
that then uses these objects can be found in demo_test.py.
'''
from datetime import datetime
import logging
logger = logging.getLogger('vdm')

from sqlalchemy import *
from sqlalchemy import __version__ as sqla_version
# from sqlalchemy import create_engine

import vdm.sqlalchemy

# engine = create_engine('sqlite:///:memory:')
engine = create_engine('postgres://tester:pass@localhost/vdmtest')
metadata = MetaData(bind=engine)

## VDM-specific tables

revision_table = vdm.sqlalchemy.make_revision_table(metadata)

## Demo tables

license_table = Table('license', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String(100)),
        Column('open', Boolean),
        )

import uuid
def uuidstr(): return str(uuid.uuid4())
package_table = Table('package', metadata,
        # Column('id', Integer, primary_key=True),
        Column('id', String(36), default=uuidstr, primary_key=True),
        Column('name', String(100)),
        Column('title', String(100)),
        Column('license_id', Integer, ForeignKey('license.id')),
        Column('notes', UnicodeText),
)

tag_table = Table('tag', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String(100)),
)

package_tag_table = Table('package_tag', metadata,
        Column('id', Integer, primary_key=True),
        # Column('package_id', Integer, ForeignKey('package.id')),
        Column('package_id', String(36), ForeignKey('package.id')),
        Column('tag_id', Integer, ForeignKey('tag.id')),
        )


vdm.sqlalchemy.make_table_stateful(license_table)
vdm.sqlalchemy.make_table_stateful(package_table)
vdm.sqlalchemy.make_table_stateful(tag_table)
vdm.sqlalchemy.make_table_stateful(package_tag_table)
license_revision_table = vdm.sqlalchemy.make_revisioned_table(license_table)
package_revision_table = vdm.sqlalchemy.make_revisioned_table(package_table)
# TODO: this has a composite primary key ...
package_tag_revision_table = vdm.sqlalchemy.make_revisioned_table(package_tag_table)



## -------------------
## Mapped classes

        
class License(vdm.sqlalchemy.RevisionedObjectMixin,
    vdm.sqlalchemy.StatefulObjectMixin,
    vdm.sqlalchemy.SQLAlchemyMixin
    ):
    def __init__(self, **kwargs):
        for k,v in kwargs.items():
            setattr(self, k, v)

class Package(vdm.sqlalchemy.RevisionedObjectMixin,
        vdm.sqlalchemy.StatefulObjectMixin,
        vdm.sqlalchemy.SQLAlchemyMixin
        ):

    def __init__(self, **kwargs):
        for k,v in kwargs.items():
            setattr(self, k, v)


class Tag(vdm.sqlalchemy.SQLAlchemyMixin):
    def __init__(self, name):
        self.name = name


class PackageTag(vdm.sqlalchemy.RevisionedObjectMixin,
        vdm.sqlalchemy.StatefulObjectMixin,
        vdm.sqlalchemy.SQLAlchemyMixin
        ):
    def __init__(self, package=None, tag=None, state=None, **kwargs):
        logger.debug('PackageTag.__init__: %s, %s' % (package, tag))
        self.package = package
        self.tag = tag
        self.state = state
        for k,v in kwargs.items():
            setattr(self, k, v)


## --------------------------------------------------------
## Mapper Stuff

from sqlalchemy.orm import scoped_session, sessionmaker, create_session
from sqlalchemy.orm import relation, backref
# both options now work
# Session = scoped_session(sessionmaker(autoflush=False, transactional=True))
# this is the more testing one ...
if sqla_version <= '0.4.99':
    Session = scoped_session(sessionmaker(autoflush=True, transactional=True))
else:
    Session = scoped_session(sessionmaker(autoflush=True, autocommit=False))

# mapper = Session.mapper
from sqlalchemy.orm import mapper

# VDM-specific domain objects
State = vdm.sqlalchemy.State
Revision = vdm.sqlalchemy.make_Revision(mapper, revision_table)

mapper(License, license_table, properties={
    },
    extension=vdm.sqlalchemy.Revisioner(license_revision_table)
    )

mapper(Package, package_table, properties={
    'license':relation(License),
    # delete-orphan on cascade does NOT work!
    # Why? Answer: because of way SQLAlchemy/our code works there are points
    # where PackageTag object is created *and* flushed but does not yet have
    # the package_id set (this cause us other problems ...). Some time later a
    # second commit happens in which the package_id is correctly set.
    # However after first commit PackageTag does not have Package and
    # delete-orphan kicks in to remove it!
    # 
    # do we want lazy=False here? used in:
    # <http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/association/proxied_association.py>
    'package_tags':relation(PackageTag, backref='package', cascade='all'), #, delete-orphan'),
    },
    extension = vdm.sqlalchemy.Revisioner(package_revision_table)
    )

mapper(Tag, tag_table)

mapper(PackageTag, package_tag_table, properties={
    'tag':relation(Tag),
    },
    extension = vdm.sqlalchemy.Revisioner(package_tag_revision_table)
    )

vdm.sqlalchemy.modify_base_object_mapper(Package, Revision, State)
vdm.sqlalchemy.modify_base_object_mapper(License, Revision, State)
vdm.sqlalchemy.modify_base_object_mapper(PackageTag, Revision, State)
PackageRevision = vdm.sqlalchemy.create_object_version(mapper, Package,
        package_revision_table)
LicenseRevision = vdm.sqlalchemy.create_object_version(mapper, License,
        license_revision_table)
PackageTagRevision = vdm.sqlalchemy.create_object_version(mapper, PackageTag,
        package_tag_revision_table)

from base import add_stateful_versioned_m2m 
vdm.sqlalchemy.add_stateful_versioned_m2m(Package, PackageTag, 'tags', 'tag',
        'package_tags')
vdm.sqlalchemy.add_stateful_versioned_m2m_on_version(PackageRevision, 'tags')

## ------------------------
## Repository helper object

from tools import Repository
repo = Repository(metadata, Session,
        versioned_objects = [ Package, License,  PackageTag ]
        )