Section A.10. Class Definitions

A.10. Class Definitions

The most direct path you can use to migrate your model definition is to use ActiveMapper. This section outlines the basic steps required to migrate from SQLObject to SQLAlchemy with ActiveMapper. Before getting into the detailed steps of doing a conversion, you can get a feel for the differences by looking at some code in both SQLAlchemy and SQLObject. We'll go through the TurboGears Identity model class by class, showing the SQLObject version and then the SQLAlchemy with ActiveMapper version.

The Visit class for handling anonymous and authenticated site visitors is as follows:

      class Visit(SQLObject):        class sqlmeta:            table="visit"        visit_key= StringCol( length=40, alternateID=True,                              alternateMethodName="by_visit_key")        created= DateTimeCol( default=datetime.now )        expiry= DateTimeCol()        def lookup_visit( cls, visit_key ):            try:                return cls.by_visit_key( visit_key )            except SQLObjectNotFound:                return None        lookup_visit= classmethod(lookup_visit)

To convert this over to ActiveMapper, we need to turn the column definitions inside out, declaring column objects and specifying their types rather than declaring columns of specific types. Other than that, the two look quite similar:

    class Visit(ActiveMapper):         class mapping:             __table__ = "visit"             visit_key = column(String(40), primary_key=True)             created = column(DateTime, nullable = False, default=datetime.now)             expiry = column(DateTime)         def lookup_visit(cls, visit_key):             return Visit.get( visit_key );         lookup_visit= classmethod(lookup_visit)

The VisitIdentity class maps from a visit to the identity of a known user:

    class VisitIdentity(SQLObject):         visit_key = StringCol(length=40, alternateID=True,                               alternateMethodName="by_visit_key")         user_id = IntCol()

This conversion follows the same pattern as the last:

    class VisitIdentity(ActiveMapper):         class mapping:             __table__="visit_identity"             visit_key = column(String, # foreign_key="visit.visit_key",                                primary_key=True)             user_id = column(Integer, foreign_key="tg_user.user_id", index=True)

A Group collects up a bunch of users so that they can all be granted a set of permissions at one time. Here is the SQLObject definition for a Group:

    class Group(SQLObject):         """         An ultra-simple group definition.         """         # names like "Group", "Order" and "User" are reserved words in SQL         # so we set the name to something safe for SQL         class sqlmeta:             table="tg_group"         group_name = UnicodeCol(length=16, alternateID=True,                                 alternateMethodName="by_group_name")         display_name = UnicodeCol(length=255)         created = DateTimeCol(default=datetime.now)         # collection of all users belonging to this group         users = RelatedJoin("User", intermediateTable="user_group",                             joinColumn="group_id", otherColumn="user_id")         # collection of all permissions for this group         permissions = RelatedJoin("Permission", joinColumn="group_id",                                   intermediateTable="group_permission",                                   otherColumn="permission_id")

This is one case where ActiveMapper has not yet caught up with SQLObject's simplicity. Group has two many-to-many relationships. SQLObject automatically defines the join tables for those relationships, whereas ActiveMapper does not. The definitions are straightforward:

    # tables for SQLAlchemy identity     user_group = Table("user_group", metadata,                           Column("user_id", Integer,                                   ForeignKey("tg_user.user_id"),                                   primary_key=True),                           Column("group_id", Integer,                                   ForeignKey("tg_group.group_id"),                                   primary_key=True))     group_permission = Table("group_permission", metadata,                                 Column("group_id", Integer,                                         ForeignKey("tg_group.group_id"),                                         primary_key=True),                                 Column("permission_id", Integer,                                     ForeignKey("permission.permission_id"),                                         primary_key=True))

With those two tables in hand, we can define the SQLAlchemy version of Group following the pattern we've used for the other tables:

    class Group(ActiveMapper):         """         An ultra-simple group definition.         """         class mapping:             __table__="tg_group"             group_id = column(Integer, primary_key=True)             group_name = column(Unicode(16), unique=True)             display_name = column(Unicode(255))             created = column(DateTime, default=datetime.now)             users = many_to_many("User", user_group, backref="groups")             permissions = many_to_many("Permission", group_permission,                                        backref="groups")

The definition of the User class is a little more involved than the previous classes. It has a handful of fields on it, a special setter for encrypting the password, and a getter to retrieve all of the permissions that the user has been granted:

    class User(SQLObject):         """         Reasonably basic User definition. Probably would want additional attributes.         """         # names like "Group", "Order" and "User" are reserved words in SQL         # so we set the name to something safe for SQL         class sqlmeta:             table="tg_user"         user_name = UnicodeCol(length=16, alternateID=True,                                alternateMethodName="by_user_name")         email_address = UnicodeCol(length=255, alternateID=True,                                    alternateMethodName="by_email_address")         display_name = UnicodeCol(length=255)         password = UnicodeCol(length=40)         created = DateTimeCol(default=datetime.now)         # groups this user belongs to         groups = RelatedJoin("Group", intermediateTable="user_group",                              joinColumn="user_id", otherColumn="group_id")        def _get_permissions(self):            perms = set()            for g in self.groups:                perms = perms | set(g.permissions)            return perms        def _set_password(self, cleartext_password):            "Runs cleartext_password through the hash algorithm before saving."            hash = identity.encrypt_password(cleartext_password)            self._SO_set_password(hash)        def set_password_raw(self, password):            "Saves the password as-is to the database."            self._SO_set_password(password)

The ActiveMapper User class defines the getter method for the permissions attribute, but does it in the standard Python form with a call to property. The current version of the SQLAlchemy Identity model does not include the encryption feature:

     class User(ActiveMapper):         """         Reasonably basic User definition. Probably would want additional attributes.         """     class mapping:         __table__="tg_user"         user_id = column(Integer, primary_key=True)         user_name = column(Unicode(16), unique=True)         email_address = column(Unicode(255), unique=True)         display_name = column(Unicode(255))         password = column(Unicode(40))         created = column(DateTime, default=datetime.now)         groups = many_to_many("Group", user_group, backref="users")     def permissions(self):         perms = set()         for g in self.groups:             perms = perms | set(g.permissions)         return perms     permissions = property(permissions)

Finally, we come to the definition of a Permission. This one just uses features that we've seen in previous classes:

    class Permission(SQLObject):         permission_name = UnicodeCol(length=16, alternateID=True,                                      alternateMethodName="by_permission_name")         description = UnicodeCol(length=255)         groups = RelatedJoin("Group",                             intermediateTable="group_permission",                              joinColumn="permission_id",                              otherColumn="group_id")

The ActiveMapper version is basically the same, and we're able to reuse the many-to-many join table definition that we created earlier:

    class Permission(ActiveMapper):         class mapping:             __table__="permission"             permission_id = column(Integer, primary_key=True)             permission_name = column(Unicode(16), unique=True)             description = column(Unicode(255))             groups = many_to_many("Group", group_permission,                                   backref="permissions")

Here are the basic steps required to migrate your database model using ActiveMapper:

Change the imports.
1. Remove the SQLobject import line.
2. Add the SQLalchemy imports:
```
    from sqlalchemy import *     from sqlalchemy.ext.activemapper import ActiveMapper, column, \     one_to_many, one_to_one, many_to_many
```
3. Remove the lines related to the PackageHub, which is part of TurboGears's SQLObject support.
4. Add from turbogears.database import metadata.
Change the base class for your model class from SQLObject to ActiveMapper.
Create a new inner class named mapping and indent all the column definitions to be a part of that class.

Update your column definitions to use column from ActiveMapper (see Table A-1).

Table A-1. Column Type Conversions
SQLObject	SQLAlchemy
`SQLObject`	`SQLAlchemy/ActiveMapper`
`StringCol()`	`column(String)`
	`column(String(40)`
`UnicodeCol()`	`column(Unicode)`
`IntCol()`	`column(Integer)`
`FloatCol()`	`column(Float)`
`DecimalCol()`	`column(Numeric)`
`DateTimeCol()`	`column(Datetime)`
`DateCol()`	`column(Date)`
`TimeCol()`	`column(Time)`
`BLOBCol()`	`column(Blob)`
`BoolCol()`	`column(Boolean)`

`PickleCol()`	`column(PickleType)`
	`one_to_many('Class')`

Migrate sqlmeta parameters into your mapping class. table in sqlmeta becomes _table_ in mapping.
Use Python's property built-in function to create properties from your _get_* and _set_* methods. For example, if you have _get_password and _set_password you should add a line that says password = property(_get_password, _set_password).
Create many-to-many join tables. As of this writing, SQLAlchemy does not create many-to-many join tables for you. You have to create these Table objects yourself (see below for an example).
Be sure to provide column and table names based on SQLObject's "style." By default, SQLObject converts mixedCaseNames to names that contain underscores when you create the database. If you have a table that is named something like TableName, the name of the table in the database with SQLObject will be table_name. You can tell SQLAlchemy/ActiveMapper to make this translation by setting _table_ = table_name in your mapping class. If you have a column called columnName, it will be translated by SQLObject to column_name in the database. You can do the same with ActiveMapper by passing colname=column_name in your column() call.
Create a primary key column for each table. SQLObject automatically creates a primary key column called id for your tables. ActiveMapper can automatically create an ID column for you, too, but it does not use the same naming convention. You just need to add something like this to your mapping class:

id = column(Integer, primary_key=True).

Table A-2. Column Options
SQLObject	SQLAlchemy
`SQLObject`	`SQLAlchemy`
`notNone`	`nullable`
`unique`	`unique`
`alternateID`	`unique`

As of this writing, ActiveMapper does not automatically create join tables for you for a many-to-many mapping (RelatedJoin in SQLObject). Luckily, they are easy to define:

    join_table = Table("join_table_name", metadata,                         Column("class1_id", Integer,                                 ForeignKey("table1name.id"),                                 primary_key=True),                         Column("class2_id", Integer,                                 ForeignKey("table2name.id"),                         primary_key=True))

You should define this table object before the first class that is part of the many-to-many relationship.

A.10. Class Definitions

Table A-1. Column Type Conversions

Table A-2. Column Options