Twisted Documentation: Twisted Enterprise Row Objects

The twisted.enterprise.row module is a method of interfacing simple python objects with rows in relational database tables. It has two components: the RowObject class which developers sub-class for each relational table that their code interacts with, and the Reflector which is responsible for updates, inserts, queries and deletes against the database.

The row module is intended for applications such as on-line games, and websites that require a back-end database interface. It is not a full functioned object-relational mapper for python - it deals best with simple data types structured in ways that can be easily represented in a relational database. It is well suited to building a python interface to an existing relational database, and slightly less suited to added database persistance to an existing python application.

If row does not fit your model, you will be best off using the low-level database API directly, or writing your own object/relational layer on top of it.

Class Definitions

To interface to relational database tables, the developer must create a class derived from the twisted.enterprise.row.RowObject class for each table. These derived classes must define a number of class attributes which contains information about the database table that class corresponds to. The required class attributes are:

rowColumns - list of the column names and types in the table with the correct case
rowKeyColumns - list of key columns in form: [(columnName, typeName)]
rowTableName - the name of the database table

There are also two optional class attributes that can be specified:

rowForeignKeys - list of foreign keys to other database tables in the form: [(tableName, [(childColumnName, childColumnType), ...], [(parentColumnName, parentColumnType), ...], containerMethodName, autoLoad]
rowFactoryMethod - a method that creates instances of this class

For example:

class RoomRow(row.RowObject):
    rowColumns       = [("roomId",  "int"), 
                        ("town_id", "int"),
                        ("name",    "varchar"),
                        ("owner",   "varchar"),
                        ("posx",    "int"),
                        ("posy",    "int"),
                        ("width",   "int"),
                        ("height",  "int")]
    rowKeyColumns    = [("roomId",  "int4")]
    rowTableName     = "testrooms"
    rowFactoryMethod = [testRoomFactory]

The items in the rowColumns list will become data members of classes of this type when they are created by the Reflector.

Initialization

The initialization phase builds the SQL for the database interactions. It uses the system catalogs of the database to do this, but requires some basic information to get started. The class attributes of the classes derived from RowClass are used for this. Those classes are passed to a Reflector when it is created.

There are currently two available reflectors in Twisted Enterprise, the SQL Reflector for relational databases which uses the python DB API, and the XML Reflector which uses a file system containing XML files. The XML reflector is currently extremely slow.

An example class list for the RoomRow class we specified above using the SQLReflector:

from twisted.enterprise.sqlreflector import SQLReflector

dbpool = adbapi.ConnectionPool("pyPgSQL.PgSQL")
reflector = SQLReflector( dbpool, [RoomRow] )

Creating Row Objects

There are two methods of creating RowObjects - loading from the database, and creating a new instance ready to be inserted.

To load rows from the database and create RowObject instances for each of the rows, use the loadObjectsFrom method of the Reflector. This takes a tableName, an optional user data parameter, and an optional where clause. The where clause may be omitted which will retrieve all the rows from the table. For example:

def gotRooms(rooms):
    for room in rooms:
        print "Got room:", room.id

d = reflector.loadObjectsFrom("testrooms", 
                              whereClause=[("id", reflector.EQUAL, 5)])
d.addCallback(gotRooms)

For more advanced RowObject construction, loadObjectsFrom may use a factoryMethod that was specified as a class attribute for the RowClass derived class. This method will be called for each of the rows with the class object, the userData parameter, and a dictionary of data from the database keyed by column name. This factory method should return a fully populated RowObject instance and may be used to do pre-processing, lookups, and data transformations before exposing the data to user code. An example factory method:

def testRoomFactory(roomClass, userData, kw):
    newRoom = roomClass(userData)
    newRoom.__dict__.update(kw)
    return newRoom

The last method of creating a row object is for new instances that do not already exist in the database table. In this case, create a new instance and assign its primary key attributes and all of its member data attributes, then pass it to the insertRow method of the Reflector. For example:

newRoom = RoomRow()
    newRoom.assignKeyAttr("roomI", 11)
    newRoom.town_id = 20
    newRoom.name = 'newRoom1'
    newRoom.owner = 'fred'
    newRoom.posx = 100
    newRoom.posy = 100
    newRoom.width = 15
    newRoom.height = 20
    reflector.insertRow(newRoom).addCallback(onInsert)

This will insert a new row into the database table for this new RowObject instance. Note that the assignKeyAttr method must be used to set primary key attributes - regular attribute assignment of a primary key attribute of a rowObject will raise an exception. This prevents the database identity of RowObject from being changed by mistake.

Relationships Between Tables

Specifying a foreign key for a RowClass creates a relationship between database tables. When loadObjectsFrom is called for a table, it will automatically load all the children rows for the rows from the specified table. The child rows will be put into a list member variable of the rowObject instance with the name childRows or if a containerMethod is specified for the foreign key relationship, that method will be called on the parent row object for each row that is being added to it as a child.

The autoLoad member of the foreign key definition is a flag that specifies whether child rows should be auto-loaded for that relationship when a parent row is loaded.

Duplicate Row Objects

If a reflector tries to load an instance of a rowObject that is already loaded, it will return a reference to the existing rowObject rather than creating a new instance. The reflector maintains a cache of weak references to all loaded row objects by their unique keys for this purpose.

Updating Row Objects

RowObjects have a dirty member attribute that is set to 1 when any of the member attributes of the instance that map to database columns are changed. This dirty flag can be used to tell when RowObjects need to be updated back to the database. In addition, the setDirty method can be overridden to provide more complex automated handling such as dirty lists (be sure to call the base class setDirty though!).

When it is determined that a RowObject instance is dirty and need to have its state updated into the database, pass that object to the updateRow method of the Reflector. For example:

reflector.updateRow(room).addCallback(onUpdated)

For more complex behavior, the reflector can generate the SQL for the update but not perform the update. This can be useful for batching up multiple updates into single requests. For example:

updateSQL = reflector.updateRowSQL(room)

Deleting Row Objects

To delete a row from a database pass the RowObject instance for that row to the Reflector deleteRow method. Deleting the python Rowobject instance does not automatically delete the row from the database. For example:

reflector.deleteRow(room)