Section 12.1. Customizing SQLObject Classes with sqlmeta

12.1. Customizing SQLObject Classes with sqlmeta

Sometimes the default behaviors of SQLObject get in your way, and tables aren't named the way you want them to be. Fortunately, SQLObject gives you a simple way to control that kind of thing.

You've already seen how to declare a model class that includes columns and corresponds to a DB table. But you haven't seen how to take control of this process to get what you want. SQLObject enables you to control many aspects of the way your model class behaves. This is done by setting attributes of a nested class called sqlmeta. Here is a quick example:

class Person(SQLObject):   class sqlmeta:     table='people'   name = StringCol()

This code looks pretty simple, but why is the nested class necessary?

Well, it is not really necessary, and in previous versions of SQLObject the meta attributes were declared in the model class itself. (The attribute names were prefixed by an underscore.) The problem with this approach is that two different concepts, DB columns and metabehaviors, were specified using the same syntactical element in the same scope (class attributes). Adding the sqlmeta class provides a cleaner way to group together the meta attributes in their own namespace.

12.1.1. Inside the sqlmeta cless

sqlmeta is a class instance that is attached as a class attribute to every model class that"s derived from SQLObject. This isn't exactly intuitive, but it is necessary because of the metaclass hocus-pocus SQLObject uses to transparently provide you with all these nice services. The bottom line is that metaclass tricks deal with classes, so things you might normally do in your code with objects, SQLObject has to do with classes.

sqlmeta stores the column information, joins, and indexes of its corresponding model class in addition to some control parameters. There are three types of meta attributes:

• Read-only (conceptually), which just provide meta information on the model class

• Writable, which you can set

• And one instance method that operates on a single instance, not on the entire class

Although there's nothing to stop you from modifying the read-only attributes, it is not recommended because elaborate work is necessary to create valid values.

12.1.2. Columns, Indexes, and Joins

The sqlmeta class contains a bunch of collections (lists and dictionaries) called columns, columnList, columnDefinitions, joins, indexes, joinDefinitions, and indexDefinitions. They must be kept in sync and shouldn't be modified by the user after they have been created. When you define your model class and specify column types and constraints, indexes, and joins, SQLObject calls methods such as addColumn, addIndex, and addJoin on the appropriate sqlmeta class.

Enough people have tried to change these things at run time, and have been bitten by database synchronization problems because they modified "the read-only attributes." So, to make a long story short, you really shouldn't mess with them after you've created your tablesunless you know exactly what you are doing.

Here are the column attributes, of a sample class:

class Metaful(SQLObject):   name = StringCol()   age = IntCol()   lazy = ForeignKey("LazyBum') print Metaful.sqlmeta.columns print Metaful.sqlmeta.columnList print Metaful.sqlmeta.columnDefinitions

Output:

{'age': <SOIntCol age>, 'lazyID': <SOForeignKey lazyID connected to LazyBum>, 'name': <SOStringCol name>} [<SOStringCol name>, <SOIntCol age>, <SOForeignKey lazyID connected to LazyBum>] {'age': <IntCol ecf950 age>, 'lazyID': <ForeignKey ecf9b0 lazy>, 'name': <String- Col ecf970 name>}

12.1.3. Name Acrobatics

Names of class models and their attributes differ from the corresponding table names and column names in the DB. SQLObject uses a default naming scheme that basically generates a lowercase underscore-separated table name (for example, table_name) for a CamelCase class name (for example, TableName), so LazyBum becomes lazy_bum. You can override the naming scheme at different levels.

The following code for a Person model class results in a database table called person:

class Person(SQLObject):   name = StringCol() Person.createTable(ifNotExists=True) CREATE TABLE person (     id INTEGER PRIMARY KEY,     name TEXT );

If you want the table to be called people because you are sharing your database with a Rails application that creates pluralized names for your tables or because you are using some legacy database where the DB schema already has a people table, you can just use table attribute of sqlmeta:

class Person(SQLObject):   class sqlmeta:     table='people'   name = StringCol() Person.createTable(ifNotExists=True) CREATE TABLE people (     id INTEGER PRIMARY KEY,     name TEXT );

As you can see, SQLObject automatically created a primary key column called id. If your DB schema uses a different convention for the primary key, you can tell SQL-Object about it using the idName attribute:

class Person(SQLObject):   class sqlmeta:     idName='person_id'     table='people'   name = StringCol() Person.createTable(ifNotExists=True) CREATE TABLE people (     person_id INTEGER PRIMARY KEY,     name TEXT );

Finally, you can completely change the naming style of tables and columns by setting the style attribute. There are three style classes:

• Style (the base class and the trivial no-translation style)

• MixedCaseUnderscoreStyle (the default style)

• MixedCaseStyle (leaves column names as mixed case)

You can use one of the other styles or derive your class from Style and provide it to sqlmeta. You will have to override various methods such as pythonAttrToDBColumn and pythonClassToDBTable. Here is our Person table with the MixedCaseStyle:

class Person(SQLObject):   class sqlmeta:     style = MixedCaseStyle()   name = StringCol() Person.createTable(ifNotExists=True) CREATE TABLE Person (     id INTEGER PRIMARY KEY,     Name TEXT );

The SQLObject documentation claims that MixedCaseStyle creates mixed-case long IDs (for instance, PersonID rather than id), but this is not really the case (pun intended). If you want a long ID, you need to specify longID=True in the style constructor:

style = MixedCaseStyle(longID=True)

12.1.4. Lazy Updates and Caching

Let's keep up with the lazy bum and see the effects of the lazyUpdate and cacheValues settings. By default, SQLObject updates the DB every time you set a column attribute of an instance. This isn't what you want sometimes (for example, if you need to modify multiple attributes of the same instance). So, lazyUpdate tells SQLObject to wait with the update for an explicit sync() or syncUpdate() call. SQLObject also caches everything by default. If you set lazyUpdate to true, you will probably want to set cacheValues to False so that queries will return the actual state in the DB and not the partial update that might not satisfy the DB schema constraints. Well, this is one area where SQLObject can really confuse you. In the following code samples, all the asserts succeed, believe it or not (or try it yourself):

o = LazyBum(name='leave me alone...') assert o.name == 'leave me alone...' o.name = 'what do you want now?' assert o.name == 'leave me alone...' o.sync() assert o.name == 'what do you want now?'

I create an instance (insert a row to the DB) of a LazyBum and assert that its name is indeed leave me alone.... Then, I set the name to what do you want now? but lo and behold the name is still leave me alone.... But, after the call to sync(), the name suddenly becomes what do you want now?.

What gives? SQLObject maintains an internal dictionary (called _SO_createValues) and a dirty flag for changed values under certain conditions such as lazy update and when inserting a new row. When you access an attribute for reading, it fetches it from the DB (because caching is turned off) and retrieves the original name even though you modified the instance.

Note that turning off caching is useful in other situations such as transaction management, which I cover later.

12.1.5. Expired Rows

When using caching, SQLObject retrieves a row just one time from the database and returns the cached result for subsequent query. Some situations require that some row be retrieved again from the DB without turning off caching for the entire table. For example, maybe there is a special row in a table that every process that modifies the table needs to update (perhaps for some internal bookkeeping purpose). SQLObject enables you to expire a particular instance, while the other rows of this table will remain cached (after the first time they are queried).

12.1.6. Default Order

The default order is the order expression that is passed to queries by default and used to order the query results.