Databases and Zope

Just about anyone who creates a serious web site will eventually want to connect it to a relational database. Relational database systems might be 30-year-old technology, but they're flexible, safe and fast. Using a database ensures that we can store and retrieve data needed by our web application without having to create our own persistent storage layer. This results in fewer bugs, greater speed and far greater safety.

Zope, the object-oriented web application server that we have discussed over the last few months, includes a built-in object database known as ZODB. ZODB is both powerful and easy to use; everything in Zope, including DTML documents and folders, is stored as an object in ZODB. The fact that ZODB supports such database concepts as transactions means that you can use it to store serious data, confident that no one else will be modifying information during the execution of a long, complex query.

But in many cases, ZODB isn't a good match for the data we want to store and retrieve. In many cases, this is because the data already exists, and we simply want to use Zope to access it. Perhaps we're creating a new persistent storage layer but want people to be able to access it from outside of Zope. Perhaps our data is more suited for the relational database model than an object database. And finally, perhaps our organization's IT department requires that all information be stored in a relational database.

For all of these reasons and situations, the standard Zope installation defines a ZSQL method object. This month, we'll take a look at ZSQL methods and at the general integration of Zope with relational databases. As you'll see, it's very easy to turn a simple Zope site into one that reads and writes data in a relational database.

Before we can work with a database, we must first connect to it. In Zope, we accomplish this by creating a database connection object. A Zope site can contain any number of such objects, each of which is then available for sending SQL queries to a database.

Zope comes with a single kind of database connection, which allows you to work with the simple Gadfly relational database. But while Gadfly is good for demonstrating Zope's database connectivity, it cannot match any other relational database in terms of speed or functionality. I suggest skipping Gadfly completely, installing a database adapter for the server to which you intend to connect.

I have a running PostgreSQL server on my office database server, so I decided to install the psycopg database adapter, one of several PostgreSQL adapters currently available on the Internet. (See Resources for more information on psycopg.) When installing these (and other) packages, remember that Zope typically comes with its own copy of Python, which is independent of any other copies that might be installed on your system. This means that you must install psycopg into the Python library defined by Zope (using $ZOPE/bin/python) rather than /usr/local/bin/python or /usr/bin/python.

Before we can install psycopg, we must first install the mxDateTime class written and distributed by eGenix. This package makes it possible to work with dates and times beyond the current UNIX limits (starting in 1970 and lasting until 2038) and provides a number of convenience routines to work with dates and times in various formats. Even if you don't use this module, you still will need to install it in order to get psycopg to install correctly. You can download mxDateTime from www.egenix.com/files/python/eGenix-mx-Extensions.html.

Note that you will want to download the “base” extensions package (which is free), rather than the commercial extensions package. Even if you are using an RPM-compatible distribution of Linux, you should not download the RPMs for mxDateTime. This is because we need to compile and install the libraries into our Zope Python tree, rather than the system Python tree.

After downloading and unpacking the mxBase package, you should be able to install it by switching into the mxBase directory and typing

$ZOPE/bin/python setup.py install

This will compile and install the mx module into your Python installation.

We're almost ready to install psycopg, a combination of Python and C that requires you to have the PostgreSQL development libraries installed. If you install PostgreSQL using RPMs, then you will need the postgresql-devel RPM for the appropriate version of PostgreSQL that you are running. This should install files in /usr/local/pgsql and /usr/include/pgsql, although some installations use postgresql instead of pgsql in both of these paths.

Now download the psycopg source code from initd.org/pub/software/psycopg. I retrieved version 1.0.4, but new versions seem to arrive every few weeks, so be sure to retrieve a recent version. In order to unpack and install psycopg, you will need to make the makesetup shell script (installed into $ZOPE/lib/python2.1/config in Zope 2.5b1, the latest version as of this writing) executable:

chmod 775 $ZOPE/lib/python2.1/config

To configure psycopg, change into its source directory and enter the following:

./configure
    --with-python=$ZOPE/bin/python
    --with-zope=$ZOPE
    --with-mxdatetime-includes=$ZOPE/lib/python2.1/
      site-packages/mx/DateTime/mxDateTime
    --with-postgres-includes=/usr/include/pgsql

While I remain convinced that there is a way to avoid doing so by passing configure another option, it seems that you must now edit the Makefile by hand to add a new header directory to the CFLAGS variable. Open the Makefile in your favorite editor and modify the CFLAGS definition (line 90 in my version) to include headers from $ZOPE/include/python2.1. Thus, if $ZOPE is /usr/local/zope, you would add the following to CFLAGS:

-I/usr/local/zope/include/python2.1

Save the Makefile, and then install psycopg with

make && make install && make install-zope

Finally, move the psycopg shared library (psycopgmodule.so) from $ZOPE/lib/python2.1/site-packages to $ZOPE/lib/python2.1/lib-dynload/.