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/.

You can test psycopg by restarting Zope and adding a new product in the root directory. (Unfortunately, restarting Zope is the only way to tell the system that a new product has been installed.) The product you want to install is called Z Psycopg Database Connection in the “add product” menu in the upper right-hand corner of the Zope management screen.

Each database connection object allows you to connect to a single database on a single host, with a single user name and password. This means that if you have divided your information across two different databases (or two different database servers), you will need two connection objects.

When you choose Z Psycopg Database Connection from the “add product” menu, you are then asked to provide some basic details about this database connection. You must enter an ID (which must be unique within a folder) and a title (which will appear in the management screen), as well as a database connection string. This connection string tells Zope how to find and connect to a PostgreSQL server. In my office, the atf database sits on the PostgreSQL server on “databases”, and I can connect to it with the user “reuven” and no password. Thus, I enter the following connection string:

host=databases dbname=atf user=reuven

You can leave the rest of the items in their default state if you wish. Click on the Add button, and you will be returned to the folder in which you added the new connection object.

Clicking on the connection object displays several Zope tabs that you can use to administer the database connection. The four most interesting ones are:

Now that we have a database connection, we can create one or more ZSQL methods. Each ZSQL method is a single SQL query (with variable arguments, if you want) that works with a connection.

Let's create a ZSQL method that lets us add a new name into an address book. Of course, this means that we must first have an appropriate table defined in our database. We can create the table by sending the content of Listing 1 to PostgreSQL, either in the test tab for our database connection or by using the traditional psql command-line interface.

Listing 1. Creating a Table

If you try to add a ZSQL method when no database connection is available, Zope will display an error message, complaining that it could not find any suitable database connection.

Zope's built-in system of “acquisition” means that a ZSQL method can use any database connection above it in the Zope hierarchy. A user thus can choose a different database connection for each method, making it possible to integrate information from different databases in a single application—or to migrate your web site from one brand of database to another.

To create a ZSQL method, go to the folder in which you created your database connection and choose “ZSQL method” from the “add product” menu. You will be asked to enter several items: an ID (which must uniquely identify your object in this folder), a title (which will be visible in the management interface), arguments (which we will discuss in the next section) and finally the SQL itself. Your SQL query can be as simple or as complex as you like and can perform an INSERT, UPDATE or DELETE.

Once you have added your ZSQL method to the system, clicking on it brings up a number of Zope tabs. One of these tabs is labeled test, and (as you might expect) it allows you to test the query. If your query has arguments, then you are asked to enter them in an HTML form. If not, you are simply asked to click on the Submit Query button. This returns, as with the test tab from our database connection object, an HTML table describing the results of our query—or a message indicating that our query returned no results.

We can create a ZSQL method for each query we wish to perform. While this might seem a bit odd, it's actually a very flexible and elegant solution that I've grown to appreciate more and more. If I expect to perform 20 queries in a web application, I can put each of them in a separate ZSQL method and then invoke those methods from within DTML pages.

Within a DTML page, we can retrieve results from a ZSQL method by naming it in a <dtml-in> tag. For example, I create a ZSQL method that implements the following query:

    SELECT first_name, last_name, phone_number,
           fax_number, cell_number
      FROM AddressBook
  ORDER BY last_name, first_name

If I give this ZSQL the name “names-and-phone-numbers”, then I can invoke it from within a DTML document with the code in Listing 2. In just a few lines of DTML, we have successfully managed to produce a simple (but useful and flexible) ZSQL method. But how does it work?

Listing 2. Invoking ZSQL from within a DTML Document

When Zope receives a request for this DTML document, it parses the DTML and executes each of the tags contained within. The <dtml-in> loop construct expects a sequence as an argument; in this particular case, the sequence is the result from invoking the names-and-phone-numbers method. The <dtml-in> tag also assigns one variable for each column in the returned result set. This is how we can use the <dtml-var first_name> tag to print the user's first name; Zope automatically assigns the value of the first_name column to a variable called first_name.

In order to avoid printing unnecessary and blank lines, we use <dtml-if> to check that we did not receive a NULL or empty value back from PostgreSQL.

It's obvious how we can use ZSQL methods and DTML to perform the same query each time. But if we want to modify our basic query each time it is run, we will need to define one or more arguments.

For example, if we want to retrieve information about someone based on their last name (or a portion thereof, using SQL regular expressions), we will want to define the following sort of ZSQL method:

    SELECT first_name, last_name, phone_number,
           fax_number, cell_number
      FROM AddressBook
     WHERE last_name LIKE
  ORDER BY last_name, first_name

In DTML, we can replace the XXXXXX with the <dtml-sqlvar> tag, which automatically handles quoting for us. We must name the SQL variable that we are using, as well as indicate its type:

    SELECT first_name, last_name, phone_number,
           fax_number, cell_number
      FROM AddressBook
     WHERE last_name LIKE <dtml-sqlvar name_sqlregexp
                           type="string">
  ORDER BY last_name, first_name

We can put even more of the burden on Zope if we use a <dtml-sqltest> tag, which operates similarly to <dtml-sqlvar>:

    SELECT first_name, last_name, phone_number,
           fax_number, cell_number
      FROM AddressBook
     WHERE <dtml-sqltest name_sqlregexp op="like"
            type="string">
  ORDER BY last_name, first_name

If we have stored the above query in a ZSQL method named select_by_last_name, then Zope can automatically produce skeleton DTML documents that allow users to enter search terms and see results. To do this, simply choose the “Z Search Interface” product from the “add product” list. You will be able to choose from all of the searchable objects on the system, including the ZSQL method that we just created (select_by_last_name). Choose this, and give the report an ID (I used search_by_last_name). Also give a name to the “input ID”, which is a fancy term for the HTML form that will be used to send input to search_by_last_name. (I named it search_by_last_name_form.) In modern versions of Zope, you also must indicate whether you want the system to create DTML methods or page templates; we want the former.

Clicking on Add creates two new DTML methods in the current folder, corresponding to the names that you entered in the form. Clicking on the “input ID” URL will present a simple HTML form into which you can enter an SQL regular expression. Clicking on the submit button will send your query to the search_by_last_name DTML method, which will in turn invoke our ZSQL method (select_by_last_name), which will then pass along our query to PostgreSQL. PostgreSQL returns results to select_by_last_name, which returns a result set to search_by_last_name, which then displays them in our web browser.

You can, of course, modify the DTML methods that are created to match the style of your own site. You also can copy the DTML that Zope created automatically into your own DTML pages, using them as examples of how to create your own database queries.

The only major task left is the implementation of an INPUT query, which adds items into the database. Luckily, this is rather easy: we create a ZSQL method that inserts a row into the database. Then we create a DTML document that submits its HTML form elements to another DTML document. This second document invokes <dtml-call> to our ZSQL method. Voilà--our record is inserted into the database.

Listing 3 shows the ZSQL method that we need, which I named insert_address_data. Now we'll create a simple DTML document, which will contain an HTML form (see Listing 4).

Listing 3. The ZSQL Method insert_address_data

Listing 4. DTML Document Containing HTML Form

Finally, we create the DTML document insert_address that receives input from insert_address_form and passes its arguments along to the ZSQL method insert_address_data:

<dtml-var standard_html_header>
<h2><dtml-var title_or_id></h2>
<dtml-try>
    <dtml-call insert_address_data>
<dtml-except>
    <p>Sorry, but the INSERT didn't work.</p>
<dtml-else>
    <p>Successfully inserted!
</dtml-try>
<dtml-var standard_html_footer>

Users can now insert information into our PostgreSQL table using an HTML form pointed at insert_address_form, and they can retrieve it using search_by_last_name_form. It's rather impressive that we can do so much in so few files—and even more so that we didn't have to touch a text editor once in order to get this to work, but that it could all be done using nothing more than our web browser.

While they are not perfect, I find ZSQL methods to be an elegant way to connect a page of HTML with an underlying database. ZSQL is yet another way in which Zope demonstrates its very flexible, elegant approach to web development—albeit one that makes you scratch your head several times before it all becomes clear and obvious. Someone who already knows DTML and SQL can easily integrate a database into their Zope application—and with ZSQL methods, it's possible to divide work on a site between those who know SQL (and work on the ZSQL methods) and those who want to work on the DTML methods that invoke them.

Resources