StormMigrationGuide

Not logged in - Log In / Register

This guide explains how certain SQLObject concepts map to equivalent Storm concepts. It expects a level of familiarity in how SQLObject works (or at least how it is used in Launchpad). It is not a full tutorial on how to use Storm either – see https://storm.canonical.com/Tutorial for that.

Differences

Now that we've landed the Storm code, Launchpad is running on top of Storm's SQLObject compatibility layer. This is not the end of the story though, since we want to move to using the native Storm API. Due to the way the compatibility layer is structured it is possible to start using many of Storm's native APIs right away, so this will be a gradual process rather than a single big change as with the first stage.

Connections

With SQLObject, each database class has a connection associated with it which is used for loading objects and performing queries. With Storm, the equivalent concept is a Store. Unlike SQLObject, stores are bound to instances rather than their classes. This means that a single class can be used to refer to objects in multiple databases (or to objects in the same database over different DB connections, as you might want to do in tests).

There are two main ways to access the main store. One is explicitely via the IStoreSelector utility:

Use the master flavor if you need to update the objects. Use the slave flavor to offload a search to a replica database and don't mind the search being made on data a few seconds out of date. Use the default flavor if you don't need to make changes, but need an up to date copy of the database (eg. most views, as the object you are viewing might just have been created) - Launchpad will choose an appropriate flavor.

The other method is from an existing object:

The second form is often more convenient, and is preferred if you don't need to make updates and want them to play nicely with objects from an unknown store (eg. passed in via your method parameters).

Utility methods and Stores

If you are writing a utility method like MailingListSet.get, use the default store. Utility methods can't know whether the caller will be writing to objects it retrieves. But the default choice makes pretty good guesses about whether your operation needs the master store. It uses the master store:

So the only times you'll run into trouble are if:

We plan to address these issues better once we're using Python 2.5 and its support for with statements / context management.

Adding Objects

/!\ Note: this section applies to classes that are not defined using the compatibility layer. Classes using the compatibility layer continue to provide the SQLObject behaviour.

With SQLObject, a default constructor is added to database classes that inserts a new row in the database that takes column values as keyword arguments. Storm does not provide a default constructor, so classes will need to add one.

Furthermore, Storm does not add the object to the database on instantiation: that must be done separately. There are two ways that an object can be added to a store. It can be added directly:

Or you can link it to an existing object, which will add it to that object's store:

Removing Objects

Objects can be removed from the database using the Store.remove method. To remove an object from its store, you can use:

Getting Objects by ID

The equivalent of SQLObject's Class.get() method is Store.get. It takes the class and the primary key of the object as arguments:

Querying Objects

The equivalent of SQLObject's select, selectBy, selectOne, selectOneBy, selectFirst and selectFirstBy methods is Store.find(). It acts quite similar to the equivalent SQLObject methods, and the following are equivalent:

Note that the ".q." bit is not required in the second example. The first two versions are preferred to direct SQL since they allow Storm to determine which tables are being used in the query automatically. As with SQLObject, no query is issued when executing find(): that is delayed until you try to access the result set.

The behaviour of selectOne and selectFirst are covered by the one and first methods on the result set. You can chain them with the find call if it is appropriate:

Result sets can be indexed, sliced and iterated over as with SQLObject. An ordering can be applied to the result set with the order_by method:

Unlike SQLObject, the ordering is applied to the result set rather than creating another one. The method does return the result set though, to make it possible to chain the calls when constructing a result set. Similar to SQLObject, a table can specify the default ordering for results with the __storm_order__ class attribute.

See the storm.store.ResultSet doc strings and the Storm tutorial for more details on what is possible.

Defining Tables

Some of the primary differences between SQLObject and Storm database class definitions are:

Foreign Key References

The equivalent of SQLObject's ForeignKey class is Reference. A Storm Reference property creates a relationship between a local column and a remote column. Unlike ForeignKey, it does not implicitly create the FK column. So the following definitions are equivalent:

The columns can be passed directly to Reference(), or can be passed as strings that are looked up on first use.

The Reference class is also used to replace SQLObject's SingleJoin class:

Reference Sets

The SQLMultipleJoin and SQLRelatedJoin classes are replaced by Storm's ReferenceSet:

While the SQLObject properties return plain result sets, the Storm properties return BoundReferenceSet objects. Some differences include:

Property Setters / Validators

SQLObject provided two ways of controlling how variables were set:

  1. magic _set_columnName() methods.

  2. the validator argument on column definitions.

Storm does not support magic methods but does have validators (albeit in a simpler form than SQLObject). A validator is a function that takes (object, attr_name, new_value) as arguments and returns the value that should be set. This allows validation to be performed on the new value (by raising an exception on bad values), and transformation of the value if appropriate (by returning something other than new_value).

A validator can be set for a column with the validator argument in the column definition.

You may notice some uses of storm_validator in code using the compatibility layer. As the compatibility layer does not implement the either of the SQLObject validation APIs, this was done to allow use of Storm validators without completely rewriting the definitions.

Prejoins

Storm's equivalent of prejoins is tuple finds. To select all products that are part of launchpad-project and their owners, we can do:

Iterating over this result will give us (product, person) tuples. The above case performs an inner join, so is not appropriate for cases where the foreign key linking the tables can be NULL. In those cases, a slightly different syntax is needed:

This result set will return (product, project) tuples, with project set to None where appropriate.

If you need to select a table multiple times, it is necessary to alias it. For example:

This result set will return (product, owner, driver) tuples.

Direct SQL Queries

To perform direct SQL queries, we previously used the cursor() function from lp.services.database.sqlbase to get a cursor on the connection being used by SQLObject. These uses should be converted to use Store.execute(), which will make sure pending changes have been flushed to the database first in order to stay consistent.

This method returns a result object with get_one and get_all methods that act like a cursor's fetchone and fetchall methods. It also supports iteration.

Migration Plan

A good order to migrate code is:

  1. Convert column properties to use the Storm syntax. This should be a no-op change, and not affect external code.

  2. Convert ForeignKey() definitions to an appropriate pair of Int() and Reference() definitions.

  3. Convert sync(), syncUpdate(), destroySelf(), etc calls to Storm equivalents.

  4. Convert uses of Class.select*() to use find(). Note that you lose prejoins support here, so use tuple finds as appropriate. Change queries to use Storm expressions rather than sqlbuilder expressions.

  5. Convert SQLMultipleJoin and SQLRelatedJoin to ReferenceSet(). As this changes the API of the class a bit, it will probably require changes external to the class.

  6. Change the class to derive from lp.services.database.stormbase.StormBase instead of SQLBase.

This list is roughly ordered based on the locality of changes and based on dependencies between changes.

For new code, consider using native Storm API from the start, rather than continuing to use the compatibility layer.

Tips on Converting Tests

From "Tips in converting tests to Storm", May 30, 2008, 

Below are some tips on writing Storm code for Launchpad.  I won't go
too deep into the Storm API, and instead concentrate on some of the
differences between SQLObject and Storm's SQLObject compatibility
layer.

1. Storm is stricter with respect to the types it accepts in various
situations.  Most of the cases where this has caused problems in tests
have indicated problems in Launchpad or its tests.  Below are a number
of the common problems I've encountered:

(a) The SQLObject EnumCol accepts values other than enumeration values
on the Python side.  With the upgrade to Storm, things are a bit
stricter, and the correct enumeration values need to be passed in.

I found a few cases where some code had an enumeration value and then
passed in item.value.  In a few tests, the numeric constants were
being used.

This affects both creating/updating objects and building queries.

(b) When assigning to a foreign key attribute, you need to assign an
object of the right type.  It seems that SQLObject would accept any
class for such assignments.  And provided a row from the correct table
existed with the same ID existed you'd get no complaints from the
database.  There were a few tests that had bugs like this.

(c) SQLObject lets you assign a result set to a foreign key reference
when creating or updating an object, while Storm does not.

This might sound like a missing feature, except for the fact that
SQLObject seems to treat result sets as NULLs when generating SQL.  So
any new errors caused by this are genuine errors.

2. Storm flushes changes to the database implicitly before various
operations.  In general, this is good since you don't need to remember
to flush changes before running select().  That said, we have a number
of cases where we have code that relies on changes not being flushed
to disk.  Some examples include:

* the code to warn about assigning bugs to non developers transitioned
to the new assignee and then checked to see if the new assignee had
any bugs assigned to them.  As Storm flushed the change in assignee,
it always looked like the user had assigned bugs.  Switching the order
of these two operations fixed the bugs.

* Some of the PPA tests would set a PPA to private and then set the
buildd password.  Database constraints require that private PPAs have
a password, so in some tests where a flush occurred between the two
operations an IntegrityError was raised.  Reordering the two
statements fixed the problem.

Of course, there are cases where it is useful to have implicit flushes
turned off.  There is an API to block implicit flushes, and I've made
use of it for our security policy (which could otherwise introduce
flushes to almost any attribute access) and most event subscribers.
I've done this in most cases with the
lp.services.database.sqlbase.block_implicit_flushes function decorator.

3. Storm flushes some changes later than SQLObject.  Namely inserts or
deletes to the database.  Furthermore, the order that objects are
added in a single flush is not defined.  This exhibits itself in two
ways:

* If inserting a row would cause an integrity error, that error will
occur at flush time rather than object construction time.  Tests for
such failures need to explicitly flush the object.

* Some tests would create a number of objects in a single flush group
and expect them to have IDs in the same order.  Such tests need to
either take this into account or add explicit flushes to preserve the
ordering.

* In cases where objects are created that reference each other in a
loop, a manual flush will be needed before closing the loop.
Otherwise Storm won't know what order to insert them in.

4. sqlbuilder expressions do not yield SQL from str().  Storm uses
quite a different method to convert sql expression objects to SQL
statements, and this is a result of that.  There were a few cases of
code that took a builder expression and substituted it into a string
to form a larger query.  I've generally fixed cases like this by
converting the string expression to builder objects.

Constant expressions (e.g. UTC_NOW) will still work with sqlvalues(),
but not when substituted directly.


James.

Questions

12-Aug-2008

03-Oct-2008

StormMigrationGuide (last edited 2020-07-08 23:34:21 by cjwatson)