Object-Relational mapping in Zope

I've been interested in object-relational mapping in Zope for a while. I see this as the keystone of a proper enterprise-grade application server, and I've been investigating existing tools and what can be done with them. Here's a quick rundown.

ZSQL methods

Using ZSQL methods (a standard feature of Zope), you can have your application talk to an SQL database, but you're not really dealing with object-relational mapping, there are no transparently persistent objects. So let's skip it.

SQLObject / sqlos

SQLObject is a pure python mapper, and it is used in Zope 3 through sqlos. They provides declarative mapping for your classes where you can write for instance:

class Person(SQLOS):
    implements(IPerson)
    _columns = [StringCol("username", length=20, notNull=True),
                StringCol("firstname", length=20, notNull=True),
                StringCol("lastname", length=20, notNull=True)]

Then any instance of a Person will actually be stored in SQL behind your back. A unique id is generated for each Person and also stored in SQL. There are various facilities to provide relations between tables, and map them to lists in the python objects.

Archetypes + SQLStorage

SQLStorage is an Archetypes storage that uses SQL as a backend. You can write an Archetypes schema such as:

schema = BaseSchema + Schema((
    StringField("username", required=1, primary=1, searchable=1,
                storage=PostgreSQLStorage(),
                 index="FieldIndex",
                ),
    StringField("firstname", required=1,
                storage=PostgreSQLStorage(),
                ),
    StringField("lastname", required=1,
                storage=PostgreSQLStorage(),
                ),
    )) + TemplateMixin.schema

class PersonSQL(TemplateMixin, BaseContent):
    archetype_name = "Person SQL"
    schema = schema

SQLStorage relies on the Archetypes UIDs to uniquely identify objects. A Relation field can be used to have relations to other objects. Note that Archetypes objects stored through SQLStorage still have a presence in the ZODB, which means it's not a solution if you totally want to get rid of Data.fs bloat.

As you can see in the way things are declared, both SQLObject and Archetypes require you to specify in your code that you will use SQL for some objects. Things are not "transparent" from the programmer's point of view.

Ape

Ape (Adaptable Persistence Engine) is a framework to do object-relational mapping at a lower level than the above two solutions, because it works at the ZODB Storage level. Ape is used as a ZODB Mount Point inside Zope. In fact, Ape is not tied to SQL but is designed to do configurable and flexible mapping to anything; storing to the filesystem is another option provided by default.

Ape relies on the ZODB framework, and especially its classes for persistence and transaction management, backed by the storage APIs themselves. This means that from the programmer's point of view, there's no difference when using Ape than when using a FileStorage or a ZEO ClientStorage. Everything is transparent.

This is a big advantage for the programmer, but the downside is that the structure of the tables in the SQL database is chosen by the framework. However Ape's default SQL mapper already tries hard to provide data mapping in a natural way; for instance all properties are made available in a natural manner, object titles or containment relationship are also naturally expressed.

Hornet

Hornet is an SQL bridge (alpha software for now) that also works at the ZODB layer. In contrast to Ape, it is much more geared toward existing datasets, or regular SQL access to tables. It requires you to define schemas in code too, but once this is done object access is totally transparent, as in Ape. You have to use code like:

PySchema(fieldsets=(
    FieldSet("employee_type", label="Employee Type", widgets=(
        StringWidget("code", label="Employee Code"),
        StringWidget("description", label="Description"),
        )),
    ))

This is then used by the mount point and the underlying Hornet framework for a given table to decide how to map table lines to objects. (The schema is also used to autogenerate forms.)

I want more

To me Hornet and Ape are promising because I believe they integrate at the right level. Ape is better because it doesn't require explicit schema declaration, and that's very important when you have flexible objects where the users add new fields on document instances (which happens all the time in CPS using FlexibleTypeInformation-based content objects).

I believe a large part of the success of the infamous Rails has been that it makes it trivial to interface to an existing SQL database, provided it's regularly named. And I want to bring Zope to that level, but that's just going to be a side effect of a number of other goals.

First, I want to get rid of the catalog and move it to its proper place, which is in a relational database. The ZCatalog is a hack (a successful one I must admit) that tries to bring into the ZODB world something that has been working much better for ages in the relational world. The ZCatalog indexes use a truly gigantic number of small persistent objects to work. Querying an index trashes the ZODB cache which could be used for better things (to cache the objects the user is really accessing). Also the ZCatalog fails to provide any relational features, like JOIN queries. The sorting is weak (you cannot sort on something that's in a full-text index without adding another index). Finally, the "metadata" is duplicating lots information that can be found elsewhere.

If you store your data in SQL, then you can dump the ZCatalog implementation and delegate indexing and searching to SQL.

Second, I want to store blobs in the filesystem. This can be done at the application level by various products such as CPS DiskFileField, Archetypes ExternalStorage, chrism's Blob product, and others. It can also be done transparently at the ZODB storage level using Ape, and that's a much simpler way to do it.

Finally I want my application-specific data (document metadata, document hierarchy, versioning information, various customer specific record-like objects, user definitions, access rights, etc) to be stored in SQL because most of them really live naturally in SQL tables. And because I want to do aggregates on them, like sums and means.

To achieve this, I plan on using the most flexible (and underused) framework available, which is Ape. I'll write various classifiers, and mappers so that a typical CMF or CPS site can be mapped naturally to SQL. I'll also replace the catalog by an implementation that does SQL queries. This will not be a simple endeavour, but I feel this is the only way to get what I truly need in the end, without sacrifying any flexibility.

If Ape proves to hard to work with (because it imposes its own framework of mapping), I'll go the Hornet way of writing a storage directly, with flexible enough policies for the mapping to SQL or the filesystem (or LDAP for that matter).

It goes without saying that this will apply to Zope 3, because that's part of what I think an ECM system should provide. But there's no reason not to make it work in Zope 2, and that's probably where I'll start.

Update: I was asked how this would fit with the "do things in python" philosophy that Martijn has been rightly pushing. Ape is first a pure python library that provides a new database class for use with ZODB (which itself can be considered independent of Zope). Ape also provides Zope-specific mappers that know how to best deal with a number Zope-specific classes. Whenever possible, I'd like to keep to that organisation (also keep in mind that a good separation will make it reusable both by Zope 2 and Zope 3).