Using the SQLObject Support package

Introduction

sqlos is a toolkit for using SQLObject (an Object Relational Mapper) inside Zope 3 or Zope 2. It tries to take care of many of the fiddly details that always seem to take a lot of time. The major ways it helps are:

• Integrates the Zope transaction system and SQLObject, allowing SQLObject to be used in lazyUpdate mode.
• Uses Zope Database adapters in SQLObject.
• Provide zcml configuration directives.
• Provide ZODB persistent objects that can act as a "window" into the database.
• A testing infrastructure for testing SQLObject based applications.
• Caches SQLObjects and database connections to improve performance.

This file describes how to use the sqlos package to use SQLObject together with Zope.

Zope 3 or Zope 2??

For a quite few reasons, it is better and easier to use sqlos in Zope 3, but it can be used with Zope 2. Heavily leveraging the work by the Five project, a Zope 2 product (FiveSQLOS) was created to enable this.

The following table shows which sqlos version can and should be used with which Zope 2 and Zope 3 versions.

.	Zope 2.9	Zope 2.10
.	Zope 3.2	Zope 3.3
sqlos 0.2.x	X	D
sqlos 0.3.x		X

• X = Runs, all tests pass, no deprecation warnings.
• D = Runs, trivial tests fail, deprecation warnings.

Dependencies

• setuptools >= 0.6a11

• SQLObject >= 0.7.1

  Place the sqlobject package somewhere in python's sys.path ($ZOPEHOME/lib/python is a good location).

• Zope Database Adapter

  Install the adapter appropriate for your database backend. For example, install the psycopgda package to connect to PostgreSQL. The pyscopgda package is located in the zopeproducts CVS repository.

Background

sqlos was originally developed for the SIP project. You can find more information about SIP, including some screencasts on the SIP project homepage.

Currently we're using a SQLObjectContainer object, that behaves like a normal folder, except that its contents doesn't come from the ZODB, but instead, they come from an SQL connection, through SQLObject.

Given the pluggable nature of Zope 3, it was quite easy to integrate it with SQLObject. Originally, SQLObject had its own DBConnection objects, that handle all the operations with the SQL backend, and even some other backends, like DBM. These connection objects implement the DBAPI, and work over a SQL cursor that is generated on the makeConnection method of the DBConnection-based classes. Each database connection implements a given set of methods to generate queries and convert data types. To allow using a connection from Zope 3 in SQLObject, we used an adapter, which adapts a IZopeDatabaseAdapter utility service to the same interface that a DBConnection object implements. This adapter just subclasses the *Connection classes from SQLObject (eg: MySQLConnection), overriding the __init__ and makeConnection method to initialize an instance variable with a reference to the ZopeConnection object and to return a ZopeCursor instance respectively.

For the SQLObjectContainer class, we made it so it is possible to choose a set of class names that will be available inside that container.

Based on this, the methods of the IContainer class were implemented so as all operations are done using SQLObject methods. For example, the keys method does a select query and returns the available ids, the items method uses the SQLObject class select() method to generate one object for each row in the database and return them.

At this point, we have an object that behaves just like a folder, and its flexible enough that you can make their objects come from any connection and any registered class you wish.

Installation

A Sample Application

The functional test setup for sqlos is really a sample application. You can run and play with this to see how sqlos works.

First install a Z3 database adapter, create a database, edit src/sqlos/ftesting.zcml to change the connection settings as described below in "Setting up a connection for SQLObject".

NOTE: This currently does not work with the default testing database (memory based SQLite) due to threading issues. In order to run the testing application, change the rdb connection to point to a real file instead of an in-memory database within the file ftesting.zcml.

Then, from a checkout of sqlos run:

$ make run-sampleapp

This should download Zope3 install sqlos and set up the functional test suite as a sample application. You can then log into zope3 on http://localhost:8080 with gandalf:123.

Try adding a "SQLObject MultiContainer" and then some people.

NOTE: changing debug=1 in src/sqlos/adapter.py will cause the SQL executed to be printed on the terminal

Setting up a connection for SQLObject

This is an example connection configuration with psycopgda:

<configure
   xmlns='http://namespaces.zope.org/zope'
   xmlns:rdb="http://namespaces.zope.org/rdb"
   xmlns:sqlos='http://namespaces.sqlobject.org/sqlos'>

 <sqlos:connectionName name='pgsql' />

 <!-- Here you configure the dsn for a given connection -->
 <rdb:provideConnection
  name='pgsql'
  component='psycopgda.adapter.PsycopgAdapter'
  dsn='dbi://zope3:123@localhost:5432/zope3'
  />

</configure>

Making SQLObject-based objects editable inside Zope

Firstly you need to create an IPerson interface:

>>> from zope.schema import TextLine
>>> from zope.interface import Interface, Attribute

>>> class IPerson(Interface):
...
...     id = Attribute('Id')
...     username = TextLine(title=u'Username',
...                         description=u'The name of the user')
...     fname = TextLine(title=u'Given Name',
...                      description=u'The given name of the user')
...     lname = TextLine(title=u'Surname',
...                      description=u'The surname of the user')

Now, for making the SQLObject's editable there are only a few more steps. We already created an interface called ISQLObject, with all the methods and declared that the SQLObject class implements this interface. The class chooser used on the SQLObjectContainer uses this interface to query the registered classes for the ones that it is able to create. Let's see how to create a SQLObject from scratch now.

See this example:

>>> from zope.interface import implements
>>> from sqlobject import *
>>> from sqlos.zsqlobject import SQLOS

>>> class SimplePerson(SQLOS):
...     implements(IPerson)
...     username = StringCol('username', length=20, notNull=True)
...     fname = StringCol('fname', length=20, notNull=True)
...     lname = StringCol('lname', length=20, notNull=True)

Here you can see that there is almost nothing special needed for making the object be recognized by Zope 3. In fact, if you remove the implements(IPerson) line, and inherit from SQLObject directly instead of SQLOS, it is exactly the same code you would use for a standard SQLObject class (Note: if you want, you can declare that an object implements a given interface in a separate file, or even in ZCML).

As you can see, there's not much here either. We're just saying that classes that implement IPerson will have an id attribute (all SQLObject instance have that by default) and a username attribute, which happens to be a TextLine (we'll see more about that later) with a label and a description.

This interface is used to auto-generate the edit forms inside Zope 3. Let's see what is needed to do that:

<configure
   xmlns='http://namespaces.zope.org/zope'
   xmlns:browser='http://namespaces.zope.org/browser'
   xmlns:sqlos='http://namespaces.sqlobject.org/sqlos'>

  <browser:editform
     schema="myproject.interfaces.IPerson"
     name="edit.html"
     menu="zmi_views"
     label="Edit a Person"
     permission="zope.ManageContent"
     />

  <content class="myproject.person.Person">

    <require
        permission="zope.ManageContent"
        interface="myproject.interfaces.IPerson"
        set_schema="myproject.interfaces.IPerson"
      />

  </content>

  <sqlos:factory
        id="myproject.Person"
        title="Person"
        description="A Simple Person"
        />

</configure>

There are a few things that you need to know about this piece of ZCML:

First, the <browser:editform /> part is what does generate the automatic editform for the Person class. As you can see, we don't reference the class explicitly, but instead, we are saying that for the IPerson schema (a schema is just a flavor of interface), there will be an auto-generated editform called edit.html and it will be registered with the menu zmi_views. <browser:editform /> is a directive, which is also registered using ZCML and is created as part of the metaconfiguration of Zope 3. I'll not enter this into detail now. All you need to know is that this is a special view that spits out an edit form in HTML based on the schema you defined.

Second, the <content class="...."> directive is used to register some information about the class, like what factory will be using for creating instances of it and what are the permissions needed to do stuff with this class. The <sqlos:factory> directive register the class as a IFactory utility and also registers the class as a IISQLObject utility.

Let's take a look at the <require> directive:

As you can see, there are 3 attributes on the directive. The permission attribute specifies the permission that is required. The interface attribure specifies what is the interface that is protected by this permission. All the methods and properties defined on this interface are protected for reading by this permission. And last, the set_schema attribute specifies what is the interface which is going to be protected for setting attributes on the instances of this class. This is needed for the auto-generated form to work. If you don't specify a permission to protect set_schema on the interface, you won`t be able to modify the instance.

Create A Container

In addition to defining the content for Zope, you need a place for zope to locate the content. sqlos already provides nearly all of the implementation in the SQLObjectContainer class, however this ready-made container is constrained such that it can not contain any content. SQLObjectContainer must be extended to adjust this constraint and make the data in the database appear to be in this container.

As always, the first step is to declare the interface:

>>> from zope.app.container.constraints import ItemTypePrecondition
>>> from sqlos.interfaces.container import ISQLObjectContainer

>>> class IPersonContainer(ISQLObjectContainer) :
...
...     def __setitem__(name, item) :
...         pass
...     __setitem__.precondition = ItemTypePrecondition( IPerson )

The zope container infrastructure checks the __setitem__ precondition to determine what type of content the container is allowed to contain.

Next, define the implementation:

>>> from sqlos.container import SQLObjectContainer

>>> class PersonContainer(SQLObjectContainer) :
...     implements(IPersonContainer)

All that needs to be done at this point is to connect an implementation with the interface. SQLObjectContainer provides all the (core) logic necessary. Later you may want to add custom methods and logic for convenience in manipulating the SQL-backed records.

Finally, write the ZCML configuration to put all the pieces together:

<class class="myproject.PersonContainer">

  <implements interface="zope.app.container.interfaces.IContentContainer" />

  <factory
      id="myproject.PersonContainer"
      title="PersonEntry Container"
      description="A container for SQLObject instances" />

  <require
      permission="zope.View"
      interface="zope.app.container.interfaces.IReadContainer"
      />

  <require
      permission="zope.ManageContent"
      interface="zope.app.container.interfaces.IWriteContainer"
      />

  <require
      permission="zope.View"
      attributes="select"
      />

</content>

<browser:addMenuItem
    class="myproject.PersonContainer"
    title="Person Container"
    description="A persistent container for SQL-backed Person objects"
    permission="zope.ManageContent"
    />

Demonstration

At this point you have a complete web interface for your database. It is time to test it and demonstrate that it does, indeed, work.

Start zope and point your browser to the ZMI. In the list of addable items on the left you should see our Person Container type. Add Person Container to your site. When you view the container's @@contents.html view, you should see a simple listing of all the people in your database.

A Nice Touch (Title / DublinCore)

Although the base @@contents.html view is fully functional, it leaves plenty of usability to be desired. You will almost certainly want to replace the built-in container contents listing with a custom view tailored to the type of content your container handles. However, the built-in contents can trivially be given a nice touch that vastly improves the usability. The view queries the content for its DublinCore metadata, such as Title. We will provide this metadata with an adapter to adapt the IPerson interface to IZopeDublinCore.

The code is as follows:

>>> from zope.dublincore.interfaces import IZopeDublinCore

>>> class PersonDublinCore(object):
...     """An adapter providing DublinCore metadata for IPerson objects"""
...
...     implements(IZopeDublinCore)
...     __used_for__ = IPerson
...
...     def __init__(self, context):
...         self.context = context
...
...     def Title(self) :
...         return "%s, %s" % (self.context.lname, self.context.fname)
...
...     title = property(Title)



  <adapter
      provides="zope.dublincore.interfaces.IZopeDublinCore"
      for="myproject.interfaces.IPerson"
      factory="myproject.PersonDublinCore"
      trusted="true"
      />