Essential SQLAlchemy, 2nd Edition (2010)

Chapter 9. Elixir: A Declarative Extension to SQLAlchemy

This chapter describes Elixir, a module developed to automate some of the more common tasks in SQLAlchemy by providing a declarative layer atop “base” or “raw” SQLAlchemy. This chapter also describes the various extensions to Elixir that provide features such as encryption and versioning.

Introduction to Elixir

The Elixir module was developed as a declarative layer on top of SQLAlchemy, implementing the “active record” pattern described in Chapter 6. Elixir goes out of its way to make all of the power of SQLAlchemy available, while providing sensible default behavior with significantly less code than “raw” SQLAlchemy. This chapter describes versions 0.4 and 0.5 of Elixir, corresponding to the 0.4 version of SQLAlchemy. Differences between versions 0.4 and 0.5 are discussed in the upcoming sidebar, Differences Between Elixir 0.4 and 0.5.”

So, what exactly does Elixir do? Well, consider a simple product database. In SQLAlchemy, we might set up the products, stores, and prices with the following code:

product_table = Table(

'product', metadata,

Column('sku', String(20), primary_key=True),

Column('msrp', Numeric))

store_table = Table(

'store', metadata,

Column('id', Integer, primary_key=True),

Column('name', Unicode(255)))

product_price_table = Table(

'product_price', metadata,

Column('sku', None, ForeignKey('product.sku'), primary_key=True),

Column('store_id', None, ForeignKey('store.id'), primary_key=True),

Column('price', Numeric, default=0))

class Product(object):

def __init__(self, sku, msrp):

self.sku = sku

self.msrp = msrp

self.prices = []

def __repr__(self):

return '<Product %s>' % self.sku

class Store(object):

def __init__(self, name):

self.name = name

def __repr__(self):

return '<Store %s>' % self.name

class Price(object):

def __init__(self, product, store, price):

self.product = product

self.store = store

self.price = price

def __repr__(self):

return '<Price %s at %s for $%.2f>' % (

self.product.sku, self.store.name, self.price)

mapper(Product, product_table, properties=dict(

prices=relation(Price, backref='product')))

mapper(Store, store_table, properties=dict(

prices=relation(Price, backref='store')))

mapper(Price, product_price_table)

In Elixir, the corresponding setup is much simpler:

class Product(Entity):

sku=Field(String(20), primary_key=True)

msrp=Field(Numeric)

prices=OneToMany('Price')

def __repr__(self):

return '<Product %s>' % self.sku

class Store(Entity):

name=Field(Unicode(255))

prices=OneToMany('Price')

def __repr__(self):

return '<Store %s>' % self.name

class Price(Entity):

price=Field(Numeric, default=0)

product=ManyToOne('Product')

store=ManyToOne('Store')

def __repr__(self):

return '<Price %s at %s for $%.2f>' % (

self.product.sku, self.store.name, self.price)

Differences Between Elixir 0.4 and 0.5

The main difference between Elixir versions 0.4 and 0.5 is in the way your entities get transformed into SQLAlchemy tables and mappers. In version 0.4, Elixir introduced the idea of “autosetup,” where entities were “set up” when they were first accessed. Under 0.4, you could delay the setup of an entity by specifying autosetup=False in the using_options() DSL statement. In this case, you would need to manually set up the entity at some point before using it by calling either setup_all(), which will set up all entities defined, or setup_entities(entities), which will set up all the entities in the entities list.

In version 0.5, entities do not use autosetup by default, so you are responsible for manually applying either setup_all() or setup_entities() once all your entities have been defined. If you would still like to use autosetup, you can either specify autosetup=True for each entity in itsusing_options() statement or specify that all entities should use autosetup via:

elixir.options_defaults['autosetup'] = True

In version 0.5, autosetup is not only not the default, but also “is not recommended” according to the official Elixir documentation. So, using setup_all() is probably the most “future-proof” way of defining your model.

There are several interesting things to notice in the Elixir listing. First, note that the declaration of the tables has been moved inside the class definitions, and that the classes are derived from Elixir’s Entity class. This is in keeping with Elixir’s “active record” model, where the mapped classes are responsible for “remembering” the necessary data for persisting themselves. Second, notice that we didn’t declare any primary keys for the store or the price tables. If no primary key is declared, then Elixir will autogenerate an integer primary key with a sequence providing default values. Third, notice that the relationships were declared according to their behavior in the ORM (OneToMany, ManyToOne), and that no foreign key information was included in the model. Elixir will, based on the types of relationships declared, automatically generate foreign key columns as well as any auxiliary tables required for ManyToMany joins.

Note

Because of the various types of assumptions Elixir makes about table layout, it is suited mainly for “blue sky” development, where there is no need to maintain an existing legacy database, and where the primary schema definition exists in Python, not in SQL. It is possible to use Elixir where Elixir does not provide the primary schema definition, but it’s easy to shoot yourself in the foot if you’re not aware of the assumptions Elixir makes about the schema, particularly when dealing with autogenerated tables and columns.

Installing Elixir

Elixir, like SQLAlchemy, is best installed using SetupTools and easy_install. Assuming you have already installed SetupTools and SQLAlchemy as described in Chapter 2, you can install Elixir on Unix-like systems—including Linux, BSD, and OS X—as follows:

$ sudo easy_install -UZ Elixir

On Windows, the command is similar:

c:\>easy_install -UZ Elixir

To verify that Elixir is installed properly, open up an interactive Python interpreter, import the module, and verify its version:

>>> import elixir

>>> elixir.__version__

'0.4.0'

And that’s all there is to it. Elixir is installed!

Using Elixir

Elixir has two syntaxes for defining your classes: an attribute-based syntax (shown previously) and a “domain specific language” (DSL) syntax. Both have similar power; which one you use is mostly a matter of personal style. The DSL-based syntax may be phased out in the future, as it is no longer the “default” syntax, but it is not currently deprecated, so it is covered in this chapter. If we were to define the product database using the DSL syntax, for instance, we would write the following (with the methods for each class omitted for clarity):

from elixir import *

metadata.bind = 'sqlite://'

class Product(Entity):

has_field('sku', String(20), primary_key=True)

has_field('msrp', Numeric)

has_many('prices', of_kind='Price')

class Store(Entity):

has_field('name', Unicode(255))

has_many('prices', of_kind='Price')

class Price(Entity):

has_field('price', Numeric, default=0)

belongs_to('product', of_kind='Product')

belongs_to('store', of_kind='Store')

There is a rough correspondence between the functionality of the attribute-based syntax for defining entities and the DSL syntax. The attribute-based classes are listed in Table 9-1 along with their corresponding DSL function. Note that the mapping from attribute-based syntax to DSL syntax is not perfect; consult the rest of this chapter for the specific differences.

Table 9-1. Correspondence between attribute-based syntax and DSL syntax

Attribute class	DSL function
Field	has_field
ColumnProperty, GenericProperty	has_property
ManyToOne	belongs_to
OneToMany	has_many
OneToOne	has_one
ManyToMany	has_and_belongs_to_many

Note

Unlike SQLAlchemy, Elixir currently requires that your entities be defined in a module (or in several modules) and imported; they cannot be defined at the interactive Python prompt. This is due partly to the fact that Elixir uses the module name in determining how to “autoname” the tables it creates.

In all of the following examples, we will show the attribute-based syntax first, followed by the DSL syntax.

Fields and Properties

In Elixir, most columns are defined via the Field⁠(⁠ ⁠) class (attribute syntax) and/or the has_field() statement (DSL syntax). The Field constructor has only one required argument, its type. There are also some optional arguments parsed by Elixir. Any remaining arguments are passed along to the SQLAlchemy Column constructor. The Elixir-parsed optional keyword arguments are described here:

required

Specifies whether the field can be set to None (corresponds to the inverse of the nullable option in the Column constructor). Defaults to False unless this is a primary key column, in which case it defaults to True.

colname

The name of the column to be used for this field. By default it will be the same as the name used for the attribute.

deferred

If True, use deferred loading on the underlying Column object. If set to a string value, add the underlying Column to the named deferred loading column group.

synonym

Specifies a synonym value for the field. This is equivalent to using the synonym() function in SQLAlchemy.

Like the Field constructor, the has_field() statement passes along unrecognized keyword arguments to the Column constructor. has_field() takes two required arguments: the name of the field being defined and its type. Elixir also supports the following optional arguments:

through

The name of a relation to go through to get the field. This uses the associationproxy SQLAlchemy extension, which is described in Chapter 11. This allows proxying fields from a related class onto the class being mapped. The relation must be with only one object, of course, viaManyToOne / belongs_to() or OneToOne / has_one().

attribute

The name of the attribute on the related object used in conjunction with the through parameter. If this is omitted, the name of the current field will be used.

With the through and attribute arguments to has_field(), we can proxy a related class’s attribute as follows:

class Price(Entity):

has_field('price', Numeric, default=0)

belongs_to('product', of_kind='Product')

belongs_to('store', of_kind='Store')

has_field('store_name', through='store', attribute='name')

has_field('sku', through='product')

Using this definition of the Price entity and the definitions of Product and Store used previously (all saved in a module named model.py), let’s import the model, create the database, and see what Elixir does in the background:

>>> from elixir import *

>>> from model import *

>>>

>>> create_all()

>>>

>>> stores = [ Store('Main Store'),

... Store('Secondary Store') ]

>>> products = [

... Product('123', 11.22),

... Product('456', 33.44),

... Product('789', 123.45) ]

>>> prices = [ Price(product=product, store=store, price=10.00)

... for product in products

... for store in stores ]

>>>

>>> session.flush()

This will create all the tables used to implement the entities defined up to this point.

Elixir provides a thread-local contextual session where all the entities are defined.

Now, to access the store_name attribute on a price, we can do the following:

>>> metadata.bind.echo = True

>>> price = Price.get(1)

2007-11-20 17:44:46,141 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT model_price.id AS model_price_id, model_price.price AS

... model_price_price, model_price.product_sku AS

... model_price_product_sku, model_price.store_id AS

... model_price_store_id

FROM model_price

WHERE model_price.id = ? ORDER BY model_price.oid

2007-11-20 17:44:46,141 INFO sqlalchemy.engine.base.Engine.0x..90

... [1]

>>> price.store_name

2007-11-20 17:44:49,229 INFO sqlalchemy.engine.base.Engine.0x..90

... SELECT model_store.id AS model_store_id, model_store.name AS

... model_store_name

FROM model_store

WHERE model_store.id = ? ORDER BY model_store.oid

2007-11-20 17:44:49,230 INFO sqlalchemy.engine.base.Engine.0x..90

... [1]

u'Main Store'

Two things are important to note here. The first is that our has_field() statement did indeed create a “proxy” statement to the Store entity’s name field. The second is Elixir’s naming convention. By default, tables created to implement entities have names generated by combining the module name with the entity name.

Elixir deferred properties

In some cases, you may need to have access to the underlying table to define an Entity’s properties, particularly when creating properties that correspond to calculated SQL values that were handled by SQLAlchemy’s column_property() function. This presents a problem in Elixir, as the underlying Table objects have not been created when the Fields are being defined. Elixir solves this problem by allowing fields to be created in a “deferred” manner. Elixir supports this in the attribute-based syntax via the GenericProperty and ColumnProperty classes, and in the DSL syntax via the has_property() statement.

Each of these methods of defining deferred properties takes a callable, which will be passed the underlying Table object’s c attribute and should return a property to be added to the Entity’s mapper. In the case of ColumnProperty, rather than returning a property object, you simply return a ClauseElement, which will be wrapped in a SQLAlchemy column_property():

class Product(Entity):

has_field('sku', String(20), primary_key=True)

has_field('msrp', Numeric)

# Using has_property DSL

has_property(

'sale_price1',

lambda c: column_property(c.msrp * 0.9))

# Using GenericProperty attribute

sale_price2 = GenericProperty(

lambda c: column_property(c.msrp * 0.8))

# Using ColumnProperty attribute

sale_price2 = ColumnProperty(

lambda c: c.msrp * 0.8)

Relations

Relations with Elixir are extremely similar to relations using “bare” SQLAlchemy, except that in Elixir, relations are defined by their cardinality (one to many, one to one, etc.) rather than inferred by foreign key relationships. In fact, Elixir will automatically create the foreign key columns necessary to implement the relations as defined.

Related Entity Names

In all of the relations supported by Elixir, you must “name” the Entity to which you are relating the mapped Entity. If the related Entity is in the same module as the Entity being mapped, simply use the name of the Entity. Otherwise, you need to give a module path to the other entity. If you defined Entity1 in package/model1.py and Entity2 in package/model2.py, and Entity2 needs a ManyToOne relationship to Entity1, you would define Entity2 as follows:

class Entity2(Entity):

entity1=ManyToOne('package.model1.Entity')

Attribute-based syntax

In the attribute-based syntax, relationships are declared via the ManyToOne, OneToMany, OneToOne, and ManyToMany classes. Each of these class constructors takes one required argument, a string specifying the name of the class being related to. Each also supports some arguments unique to Elixir and pass any unrecognized arguments along to the underlying SQLAlchemy relation() function. Note that the OneToMany and OneToOne relationships require a corresponding ManyToOne relationship in order to set up the foreign key column used to relate the classes.

Warning

Elixir automatically generates a few arguments of its own to pass to the relation() function, so they should not be provided to the relation-creating classes unless you are trying to override the value provided by Elixir. These arguments are uselist, remote_side, secondary,primaryjoin, and secondaryjoin.

The ManyToOne optional parameters are listed here:

inverse

Specifies the inverse property on the related class corresponding to this property. Generally this is not required unless there are multiple relationships between this class and the related class. Note that this does not set up the inverse relationship; the inverse relationship must be defined in the related class.

colname

The name of the foreign key column to be created. The default is entity_key, where entity is the related Entity and key is the name of the related entity’s primary key.

required

If True, specifies that the generated foreign key column has a nonnull constraint. Defaults to False.

primary_key

If True, specifies that the generated foreign key column participates in the primary key of this Entity. Defaults to False.

column_kwargs

A dict containing additional keyword arguments to be passed to the foreign key’s Column constructor.

use_alter

If True, add the ForeignKeyConstraint after the table has been created using an ALTER TABLE constraint. This is useful, for instance, when creating entities with circular foreign key dependencies.

ondelete

Value for the ForeignKeyConstraint’s ondelete parameter.

onupdate

Value for the ForeignKeyConstraint’s onupdate parameter.

constraint_kwargs

A dict containing additional keyword arguments to be passed to the ForeignKeyConstraint’s constructor.

The following list contains the OneToMany constructor’s optional parameters:

inverse

Specifies the inverse property on the related class corresponding to this property. Generally, this is not required unless there are multiple relationships between this class and the related class. Note that this does not set up the inverse relationship; the inverse relationship must be defined in the related class.

order_by

Either a string or a list of strings specifying the field names used to sort the contents of the generated list of related items. If a field is prefixed by a minus ('-'), the list will be sorted in descending order on that field.

The following is the OneToOne constructor’s optional parameter:

inverse

The ManyToMany constructor takes the following optional parameters:

inverse

tablename

Specifies a custom name for the intermediate “join table” used in the relationship. By default, the join table is named based on the entity names being joined.

remote_side

A list of columns or column names specifying which columns in the join table are used to key the remote side of a self-referential relationship.

local_side

A list of columns or column names specifying which columns in the join table are used to key the local side of a self-referential relationship.

order_by

Either a string or a list of strings specifying field names used to sort the contents of the generated list of related items. If a field is prefixed by a minus ('-'), the list will be sorted in descending order on that field.

Note that no feature in Elixir corresponds to the SQLAlchemy backref parameter on relation()s. This means that if you want the back reference, you must explicitly declare it in the class to which it is related.

DSL syntax

In the DSL syntax, relationships are declared via the belongs_to( ), has_many(), has_one(), and has_and_belongs_to_many() statements. Each of these functions takes two required arguments. The first is the name of the relation being defined. (This will be the name of the attribute in the mapped class.) The second argument, which must be declared using the of_kind keyword argument, is the name of the Entity being related to.

Like the has_field() statement, all the DSL relation statements take the optional parameters through and via in order to proxy attributes of the related class(es) to the mapped class. See the earlier section Fields and Properties” for more information on these parameters.

All of the keyword arguments supported in the attribute-based syntax are also supported in the DSL syntax. Refer to Table 9-1 earlier in this chapter for the correspondence between attribute-based classes and DSL statements.

Inheritance

Inheritance in Elixir is handled via either the single table inheritance mapping or the joined table inheritance mapping supported by SQLAlchemy (and described in detail in Chapter 8). Elixir also supports specifying whether polymorphic or nonpolymorphic loading should be used with the mapped classes. Both the inheritance method (joined table or single table) and whether the loader should be polymorphic are specified via the DSL statement using_options(). There is currently no corresponding attribute-based syntax for specifying options on entities. So, to create theProduct, Clothing, and Accessory hierarchy described in Chapter 8 in Elixir as a joined table (“multiple”) and polymorphic hierarchy, we would write the following (with methods omitted for clarity):

class Product(Entity):

using_options(inheritance='multi', polymorphic=True)

sku=Field(String(20), primary_key=True)

msrp=Field(Numeric)

class Clothing(Product):

using_options(inheritance='multi', polymorphic=True)

clothing_info=Field(String)

class Accessory(Product):

using_options(inheritance='multi', polymorphic=True)

accessory_info=Field(String)

The with_options() statement takes a number of other options, described here:

inheritance

Specifies the type of inheritance used: either 'single' for single table inheritance mapping or 'multi' for joined (“multiple”) table inheritance mapping. Concrete table inheritance mapping is not supported in Elixir. Defaults to 'single'.

polymorphic

Specifies whether the polymorphic loader should be used in an inheritance hierarchy. Defaults to False.

metadata

Specifies a custom MetaData to be used for this Entity. Defaults to the global elixir.metadata. You can also specify a custom MetaData on a per-module basis by defining the module-global variable __metadata__.

autoload

Automatically loads field definitions from an existing database table. The default is False.

tablename

Use the specified table name. This can be either a string or a callable that takes one parameter (the entity being defined) and returns a string. The default name is autogenerated by Elixir.

shortnames

If True, rather than naming the underlying Table based on the full module path to the entity, use the lowercased Entity name without any module path information. Defaults to False.

auto_primarykey

If this is a string, it will be used as the name of the primary key column automatically generated by Elixir (which will be an Integer column with a corresponding sequence). If this is True, allows Elixir to autocreate a primary key (with an autogenerated name) if none is defined in theEntity. If False, this disallows autocreation of the primary key column.

version_id_col

If this is a string, it will be used as the name of a version ID column (see Chapter 6 for the corresponding mapper() option version_id in SQLAlchemy). If this is True, it uses an autogenerated name for a version_id_col. The default is False.

order_by

The default ordering on this Entity, given as a string or list of strings representing the field names to sort by. If a field is prefixed by a minus ('-'), the list will be sorted in descending order on that field.

session

Use the specified contextual session for this Entity. The default is to use the globally-defined elixir.session, a contextual thread-local Session. You can also specify a custom Session on a per-module basis by defining the module-global variable __session__.

autosetup

If set to True, the underlying Table and mapper() will be set up for the Entity when they are first required (via access to the Entity’s c, table, mapper, or query attributes) or when the MetaData’s create_all() method is called. If set to False, you must explicitly set up theEntity either via the setup_all() or the setup_entities(entities)Elixir functions. This defaults to True in version 0.4 of Elixir, and to False in version 0.5.

allowcoloverride

If True, allows a relation to be defined with the same name as a mapped column (the column will not be mapped in this case). If False, the name conflict will generate an exception. Corresponds to the SQLAlchemy mapper() option allow_column_override. Defaults to False.

Querying Using Elixir

One of the nice things about Elixir is that the Entity base class contains a rich set of methods that can be used instead of the normal SQLAlchemy Session and Query methods. In fact, each Entity contains a class-level attribute named query that returns a query on the mapped class. It is also unnecessary to explicitly save() entities to the Session, as they are automatically save()d when they are created.

To retrieve a mapped object from its identity (primary key), simply use the get() method. (In “base” SQLAlchemy, this would be accomplished by Session.get( class_, id).)

>>> Product.get('123')

Elixir also adds the get_by() method for retrieving a single instance based on nonprimary key columns. (The corresponding query in SQLAlchemy is a filter_by() followed by one().)

>>> Product.get_by(msrp=33.44)

Of course, you can always access the underlying Session query via the query attribute:

>>> Product.query.all()

[<Product 123>, <Product 456>, <Product 789>]

The complete set of (nondeprecated) methods on the Entity class is described in the following list. Each of these methods is a proxy for the corresponding Session methods, covered in Chapter 7, and any arguments provided to these methods are passed along unmodified to the underlyingSession methods:

flush(self, *args, **kwargs)

Flush the changes to this instance to the database.

delete(self, *args, **kwargs)

Mark this instance for deletion from the database.

expire(self, *args, **kwargs)

Expire this instance from the Session.

refresh(self, *args, **kwargs)

Reload this instance from the database, overwriting any in-memory changes.

expunge(self, *args, **kwargs)

Expunge this instance from the Session.

merge(self, *args, **kwargs)

Merge the instance with the instance in the Session.

save(self, *args, **kwargs)

Save this instance to the Session.

update(self, *args, **kwargs)

Bring this (detached) instance into the Session.

save_or_update(self, *args, **kwargs)

Save or update this instance, based on whether it is in the session already.

get_by(self, *args, **kwargs)(classmethod)

Retrieve an instance from the database based on the given keyword arguments. Equivalent to instance.query.filter_by( *args, **kwargs).one().

get(self, *args, **kwargs)(classmethod)

Retrieve an object from the database or the Session’s identity map based on its primary key.

Elixir Extensions

In addition to its base functionality, Elixir provides a number of extensions that allow for more advanced uses.

Associable Extension

In many database schemas, there may be one table that relates to many others via a many-to-many or a many-to-one join. The elixir.ext.associable extension provides a convenient way to specify this pattern and to generate the appropriate association tables. This is accomplished by the associable() function, which returns a DSL statement that can be used in the definition of the related entities.

For instance, suppose we have a schema that represents brands and retailers, each of which may have multiple addresses stored in the database. This can be accomplished as follows:

class Address(Entity):

has_field('street', Unicode(255))

has_field('city', Unicode(255))

has_field('postal_code', Unicode(10))

# Create the DSL statement.

is_addressable = associable(Address, 'addresses')

class Brand(Entity):

has_field('name', Unicode(255)),

has_field('description', Unicode)

is_addressable()

class Retailer(Entity):

has_field('name', Unicode(255)),

has_field('description', Unicode)

is_addressable()

To implement this pattern, the associable extension actually implements something like joined table inheritance mapping, where the entity being associated joins to an intermediate association table, which is in turn joined to a “virtual base class” for each associable class. The tables created for the previous schema show this more clearly in Figure 9-1.

Associable table relationships

Figure 9-1. Associable table relationships

The associable() function takes one required argument, the Entity to be associated, as well as some optional arguments:

plural_name=None

The default name to be used for the property generated by the returned DSL statement. By default, this is the lowercased name of the associable Entity. (This can be overridden when using the generated statement.)

lazy=True

Indicates whether the property generated by the returned DSL statement should be lazy-loaded by default. (This can be overridden when using the generated statement.)

The generated DSL statement also takes some optional arguments:

name=None

The name to be used for the property generated. This will use the value of the plural_name parameter from associable() if not specified here.

uselist=True

Whether to use a list in the generated property (representing a ManyToMany relation) or a scalar attribute (representing a ManyToOne relation).

lazy=True

Whether the property generated should be lazy-loaded by default.

The generated DSL statement, in addition to adding the named property, also adds the helper methods select_by_property and select_property, which are simply proxies for filtering the Entity by values in the associated Entity. For instance, we can return a list of all Brands in Albuquerque with the following query:

>>> Brand.select_by_addresses(city='Albuquerque')

Encrypted Extension

The elixir.ext.encrypted extension provides encrypted field support for Elixir using the Blowfish algorithm from the PyCrypto library, which must be installed separately. (PyCrypto is available from the Python Package Index via “easy_install pycrypto”.) The encrypted extension provides the DSL statement acts_as_encrypted⁠(⁠ ⁠), which takes the following parameters:

for_fields=[]

List of field names for which encryption will be enabled

with_secret='abcdef'

A secret key used to perform encryption on the listed fields

The encrypted extension is particularly useful when data must be stored on an untrusted database or as part of a defense-in-depth approach to security. For instance, you might encrypt passwords that are stored in the database. Keep in mind, however, that the source code of your application must be kept in a trusted location because it specifies the encryption key used to store the encrypted columns.

Versioned Extension

The elixir.ext.versioned extension provides a history and versioning for the fields in an entity. These services are provided by the acts_as_versioned() DSL statement. Marking an entity as versioned will apply the following operations:

§ A timestamp column and a version column will be added to the versioned entity table.

§ A history table will be created with the same columns as the primary entity table, including the added timestamp and version columns.

§ A versions attribute will be added to the versioned entity that represents a OneToMany join to the history table.

§ The instance methods revert(), revert_to(), compare_with(), and get_as_of() will be added to the versioned entity.

Whenever changes are made to a versioned entity, the version column is incremented and the previous values for all the columns are saved to the history table. Note that at the current time, relationships are not handled automatically by the versioning process (relationship changes are not tracked in the history table) and must be handled manually. The size of the history table can be managed by specifying fields not to include via the ignore option to acts_as_versioned().

Using the acts_as_versioned() statement enables us to keep a reasonable audit trail of changes to an entity. If we set up our model as follows:

class Product(Entity):

has_field('sku', String(20), primary_key=True)

has_field('msrp', Numeric)

acts_as_versioned()

def __repr__(self):

return '<Product %s, mrsp %s>' % (self.sku, self.msrp)

@after_revert

def price_rollback(self):

print "Rolling back prices to %s" % self.msrp

we can then use the audit trail as follows:

>>> prod = Product(sku='123', msrp=11.22)

>>> session.flush()

>>> print prod

>>> print prod.version

>>> print len(prod.versions)

>>> prod.msrp *= 1.2

>>> session.flush()

>>> print prod

>>> print prod.version

>>> prod.msrp *= 1.3

>>> session.flush()

>>> print prod

>>> print prod.version

>>> print prod.compare_with(prod.versions[0])

{'timestamp': (datetime.datetime(2007, 11, 21, 15, 50, 43, 951977),

... datetime.datetime(2007, 11, 21, 15, 50, 43, 893200)), 'msrp':

... (17.5032, Decimal("11.22"))}

>>> for ver in prod.versions:

... print ver.version, ver.timestamp, ver.msrp

...

1 2007-11-21 15:50:43.893200 11.22

2 2007-11-21 15:50:43.939225 13.464

3 2007-11-21 15:50:43.951977 17.5032

>>> prod.revert()

Rolling back prices to 17.5032

>>> prod = Product.get('123')

>>> print prod

The behaviors of the new methods added by acts_as_versioned() are listed here:

revert( self )

Revert the entity to the last version saved in the history table. After reverting, the instance in memory will be expired and must be refreshed to retrieve the reverted fields.

revert_to(self, to_version)

Revert the entity to a particular version number saved in the history table. After reverting, the instance in memory will be expired and must be refreshed to retrieve the reverted fields.

compare_with(self, version)

Compare the current field values of the entity with the values in a particular version instance. The return value from this method is a dict keyed by the field name with values of pairs (named_version_value, current_value). Note that instances in the entity’s versions attribute also have a compare_with() method, allowing historical versions to be compared with other versions.

get_as_of(self, dt)

Retrieves the most recent version of the entity that was saved before the datetimedt. If the current version is the most recent before dt, then it is returned.

The versioned extension also provides a decorator, @after_revert, which can be used to decorate methods in the versioned entity that should be called after the entity is reverted.