Chapter 5. Zope and Relational Databases

Zope talks to other backends through what is known as a Database Adapter ( DA). If you want to know if you can connect to a specific relational database, you must make sure if there is a DA available for it. To our knowledge there are database adapters available for:

Before you can make a connection to a database you must install the database adapter in the Products directory and restart Zope.

For the purpose of this course, we will use Gadfly since both the database and adapter come already installed with Zope. Gadfly is not a database that should generally be used in production, but it is perfect for demonstration purposes.

To connect to a database, one has to add a new database connection in the ZMI. Select Z Gadfly Database Connection from the product Add list. A Gadfly database connection only requires you to fill in an id and a data source. Other database connections require additional information, such as a connection string (e.g. user=pete host=dbserver dbname=demo password=somepass). These details are not required for Gadfly, because it runs as a process inside Zope.

Type in demo as id, select the demo datasource and click on Add to create the database connection. Browse to the demo database connection and explore the management tabs it provides.

The Status tab shows whether the connection is open or not, and lets you control the connection status.

The Properties tab reflects the values you typed in when you added the connection. The connection's settings can be changed here.

The Test tab gives you a textarea where arbitrary SQL can be entered and executed. This should only be used for "test" queries against the database --- Zope provides a much more elegant way of executing SQL against a database, which we cover later on. If you enter select * from demo and submit the query, you should get a NameError saying: " DEMO no such relation in DB". This means the Status tab is not lying --- we are connected.

The Security, Undo and Ownership tabs are seen on most other Zope objects, and they provide the same functionality in the context of database connections. (Note that Undo refers to ZODB transactions: it won't roll back SQL statements executed on a connected database.)

The Browse tab provides an interface to browse the table structure of the database we're connected to. Since there are no tables in the demo database, go back to the Test tab and submit the following SQL:

create table dummy (
    column_a varchar,
    column_b integer,
    column_c float
)

Go back to the Browse tab, and explore the table strucutre of the dummy table we just created.

ZSQL Methods are used to write SQL queries that can be executed through a database connection.

Select ZSQL method from the product Add list so we can explore the inputs required by the add form.

Figure 5.1. Adding a ZSQL Method

Id and an optional Title is common to all Zope objects.

Connection Id is the database connection the ZSQL method should use when executing SQL.

Arguments specifies the arguments that can be passed to a ZSQL Method for use in the Query Template. Arguments can be separated by spaces or by putting each argument on its own line.

Query Template contains the SQL that a ZSQL Method will execute. Over and above standard SQL, the Query Template can include a SQL variant of DTML that simplifies the writing of SQL queries.

To illustrate the power of ZSQL Methods, we'll create a very simple article database that stores Dublin Core metadata about articles. For this, we will need ZSQL Methods to create an article table, and for selecting, inserting, updating and deleting records in the article table.

First, create a folder named articles in the root of the ZMI, which will hold the code for our article database. You don't have to create a new database connection, we'll use the demo database connection for all our ZSQL Methods. Browse to the articles folder --- we'll be adding all the code needed for our application here.

Add a ZSQL Method named sql_create_article and enter the following SQL statement in its Query Template field:

create table article (
    article_id integer,
    title varchar,
    subject varchar,
    creator varchar,
    publisher varchar,
    description varchar,
    creationdate float, 
    effectivedate float,
    modificationdate float,
    expirationdate float,
    format varchar,
    language varchar,
    rights varchar
)

We'll use a floating point representation for dates, since Gadfly doesn't have a native date datatype. Once created, go to the g�Test' tab and submit the query to create the table.

Next, add a ZSQL Method named sql_insert_article. First, we need to specify the arguments that can be passed to the ZSQL method. This is simply a list of all the column names present in our table:

article_id
title
subject
creator
publisher
description
creationdate
effectivedate
modificationdate
expirationdate
format
language
rights

Our Query Template should insert the values of the arguments passed to the ZSQL method into the table:

insert into article (
    article_id, title, subject, creator, publisher,
    description, creationdate, effectivedate, modificationdate,
    expirationdate, format, language, rights)
values (
    <dtml-sqlvar article_id type="int">,
    <dtml-sqlvar title type="string">,
    <dtml-sqlvar subject type="string">,
    <dtml-sqlvar creator type="string">,
    <dtml-sqlvar publisher type="string">,
    <dtml-sqlvar description type="string">,
    <dtml-sqlvar creationdate type="float">,
    <dtml-sqlvar effectivedate type="float">,
    <dtml-sqlvar modificationdate type="float">,
    <dtml-sqlvar expirationdate type="float">,
    <dtml-sqlvar format type="string">,
    <dtml-sqlvar language type="string">,
    <dtml-sqlvar rights type="string">
)

The sqlvar DTML tag will dynamically insert the values passed in as arguments when generating the insert SQL statement. sqlvar accepts name, type and optional as arguments:

<dtml-sqlvar 'name' 'type'="int" 'optional'>,

Next, create a ZSQL Method named sql_update_article with all the arguments listed for sql_insert_article, and add the following query template:

update article 
set title = <dtml-sqlvar title type="string">,
    subject = <dtml-sqlvar subject type="string">,
    creator = <dtml-sqlvar creator type="string">,
    publisher = <dtml-sqlvar publisher type="string">,
    description = <dtml-sqlvar description type="string">,
    creationdate = <dtml-sqlvar creationdate type="float">,
    effectivedate = <dtml-sqlvar effectivedate type="float">,
    modificationdate = <dtml-sqlvar modificationdate type="float">,
    expirationdate = <dtml-sqlvar expirationdate type="float">,
    format = <dtml-sqlvar format type="string">,
    language = <dtml-sqlvar language type="string">,
    rights = <dtml-sqlvar rights type="string">
where
    <dtml-sqltest article_id type="int">

In the where condition, we introduce the sqltest tag, used for equality comparison operations. We could have written the condition as:

where 'article_id' = <dtml-sqlvar article_id type="int">

but the sqltest tag ensures that the condition is formatted correctly for the underlying database. This helps to make the template portable accross different databases.

sqltest accepts the following parameters:

Next, create a ZSQL Method named sql_delete_article with article_id as argument and the following query template:

delete from article
where <dtml-sqltest article_id type="int">

For our select ZSQL Method we want to have a template that is flexible enough to select records based on criteria for any of the columns in our article table. This will enable us to reuse it for select a single article record as well as build a complete search interface for our article application.

Create a ZSQL Method named sql_select_article with the following arguments:

sql_article_id
sql_title
sql_subject
sql_creator
sql_publisher
sql_description
sql_creationdate
sql_effectivedate
sql_modificationdate
sql_expirationdate
sql_format
sql_language
sql_rights

We are prefixing all the arguments with sql_ to ensure that none of them are acquired when we call the method. We know that title is an attribute common to all Zope objects, to it will certainly be acquired if it isn't explicitly supplied as parameter. We don't want to query for an article on its id and get no results because a title was unintentionally acquired, causing the template to render an unwanted title = "Some folder title" as a condition in the where clause of our SQL statement. While acquisition can be used to your advantage, allowing you to populate arguments from the context instead of passing them in explicitly, it should be carefully managed.

The query template for this ZSQL Method is as follows:

select * from article 
<dtml-sqlgroup where>
    <dtml-sqltest sql_article_id column="article_id" type="int" optional>
<dtml-and>
    <dtml-sqltest sql_title column="title" type="string" optional>
<dtml-and>
    <dtml-sqltest sql_subject column="subject" type="string" optional>
<dtml-and>
    <dtml-sqltest sql_creator column="creator" type="string" optional>
<dtml-and>
    <dtml-sqltest sql_publisher column="publisher" type="string" optional>
<dtml-and>
    <dtml-sqltest sql_description column="description" type="string" optional>
<dtml-and>
    <dtml-sqltest sql_creationdate column="creationdate" type="float" optional>
<dtml-and>
    <dtml-sqltest sql_effectivedate column="effectivedate" type="float" optional>
<dtml-and>
    <dtml-sqltest sql_modificationdate column="modificationdate" type="float" optional>
<dtml-and>
    <dtml-sqltest sql_expirationdate column="expirationdate" type="float" optional>
<dtml-and>
    <dtml-sqltest sql_format column="format" type="string" optional>
<dtml-and>
    <dtml-sqltest sql_language column="language" type="string" optional>
<dtml-and>
    <dtml-sqltest sql_rights column="rights" type="string" optional>
</dtml-sqlgroup>

This template introduces the sqlgroup and and tags.

The sqlgroup tag is a very clever block level tag that only renders its arguments when the tag body contains text. Thus, the where clause will only be rendered if there are conditions. The sqlgroup tag contains a number of blocks delimited by the and tag. The and keyword is inserted in the SQL statement if the subsequent block renders a value.

Notice that we're are calling all the sqltest tags with an optional argument. If the value for a specific argument is not present, the sqltest will render no value. As a result the template will only render and between arguments that are present. If we call the ZSQL Method with arguments:

sql_title='ZSQL Methods', sql_subject='sql', sql_language='English'

it will render the following SQL:

select * from article 
where
(title = 'ZSQL Methods'
and subject = 'sql'
and language = 'English'
)

Note: if you try this from the method's Test tab, the generated SQL will contain some constraints on blank strings, as the form submits all fields as arguments. It should be called from a Python script instead, passing only the desired arguments.

In addition to the and tag, one can use the or tag:

<dtml-sqlgroup where>
    <dtml-sqltest title type="string">
<dtml-or>
    <dtml-sqltest subject type="string"> 
</dtml-sqlgroup>

sql_delimiter is an argument that can be passed to the DTML var tag. We don't use it in our article database, but it deserves a mention since it is very useful when a ZSQL Method needs to execute multiple SQL statements. A common case would be where records must be deleted in two tables with a master-detail relationship:

delete from order
where <dtml-sqltest order_id type="int">

<dtml-var sql_delimiter> 

delete from orderitems
where <dtml-sqltest order_id type="int">

We now have all the necessary ZSQL Methods to support a simple user interface that lists, adds, deletes and updates article metadata.

Create a Page Template with the id index_html. This will be our public interface for articles:

<html>
<head><!-- Use a page-level macro if you want customised looks -->
</head>
<body tal:define="
    results here/sql_select_article;
    start request/start|python:0;
    batch python:modules['ZTUtils'].Batch(results, size=20, start=start);
    previous python:batch.previous;
    next python:batch.next">

 <h1>Article database</h1>

 <p> <!-- Previous/Next navigation -->
 <a tal:condition="previous"
    tal:attributes="href string:${request/URL0}?start:int=${previous/first}"
    href="previous_url">
    previous <span tal:replace="previous/length">20</span> results</a>
 <a tal:condition="next"
    tal:attributes="href string:${request/URL0}?start:int=${next/first}"
    href="next_url">
    next <span tal:replace="next/length">20</span> results</a>
 </p>

 <form action="." method="post">
 <table border="1">
 <tr>
     <th>ARTICLE ID</th>
     <th>TITLE</th>
     <th>DESCRIPTION</th>
 </tr>

 <tr tal:repeat="result batch">
 <td>
    <input type="checkbox" name="article_ids:list"
           value="article_id"
           tal:attributes="value result/article_id">
     <a href="edit_article_form"
        tal:attributes="href string:edit_article_form?article_id=${result/article_id}"
        tal:content="result/article_id">ARTICLE_ID goes here</a>
  </td>
 <td><span tal:replace="result/title">TITLE goes here</span></td>
 <td><span tal:replace="result/description">DESCRIPTION goes here</span></td>
 </tr>

 <tr>
 <td colspan="3">
 <input type="submit" name="add_article_form:method" value=" Add ">
 <input tal:condition="results"
        type="submit" name="delete_article:method" value=" Delete ">
 </td>
 </tr>

 </table>

 <p>
 <a tal:condition="previous"
    tal:attributes="href string:${request/URL0}?start:int=${previous/first}"
    href="previous_url">
    previous <span tal:replace="previous/length">20</span> results</a>
 <a tal:condition="next"
    tal:attributes="href string:${request/URL0}?start:int=${next/first}"
    href="next_url">
    next <span tal:replace="next/length">20</span> results</a>
 </p>

</body>
</html>

In the define statement on the body tag above, the ZSQL Method is called without any arguments, using a TAL path expression:

results here/sql_select_article;

The result of the call to sql_select_article is assigned to the name results. ZSQL Methods return a Result object, that contains a sequence of Record objects. A Record object can be subscripted either with a column index, or with a column name. result[0] and 'result['article_id']' will both yield the same value, if article_id is the first column in the record. In the above template, we iterate over the records in the Result object using:

tal:repeat="result results"

We then subscript result (the record object) with the name of the column to render the value in that column:

<td>
   <input type="checkbox" name="article_ids:list"
          value="article_id"
          tal:attributes="value result/article_id">
    <a href="edit_article_form"
       tal:attributes="href string:edit_article_form?article_id=${result/article_id}"
       tal:content="result/article_id">ARTICLE_ID goes here</a>
 </td>
<td><span tal:replace="result/title">TITLE goes here</span></td>
<td><span tal:replace="result/description">DESCRIPTION goes here</span></td>

From the above template, it should be clear that we still need to create methods for add_article_form, edit_article_form and delete_article.

Add a PageTemplate named add_article_form with the following content:

<html>
<head></head>
<body> 

<h2>Adding article</h2> 

<form action="add_article" method="post">

<table> 
<tr> <th>Id</th>
     <td><input name="article_id" size="40" value=""></td>
</tr> 
<tr> <th>Title</th>
    <td><input name="title" size="40" value=""></td>
</tr> 
<tr> <th>Subject</th>
    <td><input name="subject" size="40" value=""></td>
</tr> 
<tr> <th>Creator</th>
     <td><input name="creator" size="40" value=""></td>
</tr> 
<tr> <th>Publisher</th>
     <td><input name="publisher" size="40" value=""></td>
</tr> 
<tr> <th>Description</th>
     <td><textarea name="description" cols="40" rows="5"></textarea></td>
</tr> 
<tr> <th>Creation date</th>
     <td><input name="creationdate:date" size="40" value=""
                tal:attributes="value python:DateTime().Date()">
     </td>
</tr> 
<tr> <th>Effective date</th>
     <td><input name="effectivedate:date" size="40" value=""
                tal:attributes="value python:DateTime().Date()">
     </td>
</tr> 
<tr> <th>Modification date</th>
     <td><input name="modificationdate:date" size="40" value=""
                tal:attributes="value python:DateTime().Date()">
     </td>
</tr> 
<tr> <th>Expiration date</th>
     <td><input name="expirationdate:date" size="40" value=""
                tal:attributes="value python:DateTime().Date()">
     </td>
</tr> 
<tr> <th>Format</th>
     <td><input name="format" size="40" value=""></td>
</tr> 
<tr> <th>Language</th>
     <td><input name="language" size="40" value=""></td>
</tr> 
<tr> <th>Copyright information</th>
     <td><input name="rights" size="40" value=""></td>
</tr> 
<tr>
   <td colspan="2" align="center">
   <input type="SUBMIT" name="SUBMIT" value=" Add article ">
</td>
</tr> 

</table>
</form> 

</body>
</html>

add_article_form submits to a Python Script named add_article, which takes REQUEST as parameter and redirects back to index_html when an article is successfully added. Here is 'add_article':

context.sql_insert_article(REQUEST)
REQUEST.RESPONSE.redirect('index_html')

Here we call a ZSQL Method from a Python Script for the first time. We call the ZSQL Method with REQUEST as the only parameter, since all values entered in the form reside on the REQUEST, and the ZSQL Method will automagically retrieve values from the REQUEST that have keys matching its argument names.

Create another Page Template named 'edit_article_form':

<html>
<head></head>
<body tal:define="article python:here.sql_select_article(
                          sql_article_id=request.article_id)[0]"> 

<h2>Editing article <span tal:replace="article/article_id">1</span></h2> 

<form action="edit_article" method="post">

<table> 
<tr> <th>Title</th>
     <td><input name="title" size="40" value=""
                tal:attributes="value article/title">
     </td>
</tr> 
<tr> <th>Subject</th>
     <td><input name="subject" size="40" value=""
                tal:attributes="value article/subject">
     </td>
</tr> 
<tr> <th>Creator</th>
     <td><input name="creator" size="40" value=""
                tal:attributes="value article/creator">
     </td>
</tr> 
<tr> <th>Publisher</th>
     <td><input name="publisher" size="40" value=""
                tal:attributes="value article/publisher">
     </td>
</tr> 
<tr> <th>Description</th>
     <td><textarea name="description" cols="40" rows="5"
                   tal:content="article/description">
                   description</textarea>
     </td>
</tr> 
<tr> <th>Creation date</th>
     <td><input name="creationdate:date" size="40" value=""
                tal:attributes="value 
                    python:DateTime(article['creationdate']).Date()">
     </td>
</tr> 
<tr> <th>Effective date</th>
     <td><input name="effectivedate:date" size="40" value=""
                tal:attributes="value
                    python:DateTime(article['effectivedate']).Date()">
     </td>
</tr> 
<tr> <th>Modification date</th>
     <td><input name="modificationdate:date" size="40" value=""
                tal:attributes="value python:DateTime(article['modificationdate']).Date()">
     </td>
</tr> 
<tr> <th>Expiration date</th>
     <td><input name="expirationdate:date" size="40" value=""
                tal:attributes="value python:DateTime(article['expirationdate']).Date()">
     </td>
</tr> 
<tr> <th>Format</th>
     <td><input name="format" size="40" value=""
                tal:attributes="value article/format">
     </td>
</tr> 
<tr> <th>Language</th>
     <td><input name="language" size="40" value=""
                tal:attributes="value article/language">
     </td>
</tr> 
<tr> <th>Copyright information</th>
     <td><input name="rights" size="40" value=""
                tal:attributes="value article/rights">
     </td>
</tr> 
<tr>
<td colspan="2" align="center">
  <input type="hidden" name="article_id" value=""
         tal:attributes="value article/article_id">
  <input type="SUBMIT" name="SUBMIT" value="Submit changes">
</td>
</tr> 
</table>
</form> 

</body>
</html>

On the body tag of the above template a different method of calling a ZSQL Method is illustrated:

<body tal:define="article python:here.sql_select_article(
                          sql_article_id=request.article_id)[0]">

Note that we are calling the ZSQL Method with keyword arguments. If you want to assign values to specific arguments of the ZSQL Method, you must explicitly call the method with keyword arguments. If you call it with a single argument, this must be a mapping or an object that behaves like a mapping, such as the REQUEST. You can try these alternatives:

# calling a ZSQL method with the REQUEST as parameter
REQUEST['arg'] = 'somevalue'
sql_select_article(REQUEST)

# calling a ZSQL method with a mapping as parameter
args = {'arg': 'somevalue'}
sql_select_article(REQUEST)

# calling a ZSQL method with keyword arguments
sql_select_article(arg1=value1, arg2=value2)

Also notice that we are subscripting the result of the ZSQL Method with 0 to return the first (and only) record in the result set:

sql_select_article(sql_article_id=request.article_id)[0]
                                                     ^^^

edit_article_form posts to a Python Script named edit_article that takes REQUEST as its only parameter. Here it is:

container.sql_update_article(REQUEST)
REQUEST.RESPONSE.redirect('index_html')

Finally, create a Python Script named delete_article that takes REQUEST as its only parameter:

for article_id in REQUEST.get('article_ids', []):
    container.sql_delete_article(article_id=article_id)
REQUEST.RESPONSE.redirect('index_html')

In this script we iterate over the selected article ids and call sql_delete_article for each article id in the list.

Before we continue, take some time to interact with the article database and see if adding, editing and deleting of articles work properly.

Go to the Advanced tab of the sql_select_article method. A description of the properties on this tab follows:

Connection Hook takes the id of a method that returns a connection when called. This functionality is rarely required and is only there for backwards compatibility.

Maximum rows to retrieve limits the result to the number of rows specified. The default is 1000. You should be aware of this if you required the ZSQL Method to retrieve more records than this.

Maximum results to cache specifies how many results or queries should be cached. Caching can dramatically improve the performance if a ZSQL Method is regularly called with the same arguments, and the data source isn't continually updated. The default number of results to cache is 'Wo 21 Jul 2004 18:47:16 SAST results of 100 queries with distinct arguments will be cached.

Maximum time (sec) to cache makes the cache time-bound. If a result has been cached for a time longer than the maximum time specified, the query will be executed agains. The default cache time of 0 disables caching.

The Class Name and Class File properties refer to a custom class that can be used to represent results, instead of the Record class that is used by default. This class can have additional methods that compute values based on the attributes of the record object. This is quite a powerful mechanism. The following section illustrates how it works.

Let's say that we want our record objects to implement the Dublin Core interface. We can achieve this by defining methods required by this interface in a custom record class.

Create a new module named Article.py in the Extensions directory of your Zope instance home and define the following class inside it:

from DateTime import DateTime

class Article:

    def Title( self ):
        "Dublin Core element - resource name"
        return self.title

    def Creator( self ):
        return self.creator

    def Subject( self ):
        "Dublin Core element - resource keywords"
        return self.subject

    def Publisher( self ):
        "Dublin Core element - resource publisher"
        return self.publisher

    def Description( self ):
        "Dublin Core element - resource summary"
        return self.description

    def Date( self ):
        "Dublin Core element - default date"
        # Return effective_date if set, modification date otherwise
        date = DateTime(self.effectivedate)
        if self.effective_date == 0.0:
            date = DateTime(self.modificationdate)
        return date.ISO()

    def CreationDate( self ):
        """ Dublin Core element - date resource created.
        """
        if self.creationdate == 0.0:
            return None
        else:
            return DateTime(self.creationdate).ISO()

    def EffectiveDate( self ):
        """ Dublin Core element - date resource becomes effective.
        """
        if self.effectivedate == 0.0:
            return None
        else:
            return DateTime(self.effectivedate).ISO()

    def ExpirationDate( self ):
        """ Dublin Core element - date resource expires.
        """
        if self.expirationdate == 0.0:
            return None
        else:
            return DateTime(self.expirationdate).ISO()

    def ModificationDate( self ):
        """ Dublin Core element - date resource last modified.
        """
        return DateTime(self.modificationdate).ISO()

    def Type( self ):
        "Dublin Core element - Object type"
        return self.type

    def Format( self ):
        """ Dublin Core element - resource format
        """
        return self.format

    def Identifier( self ):
        "Dublin Core element - Object ID"
        return self.article_id

    def Language( self ):
        """ Dublin Core element - resource language
        """
        return self.language

    def Rights( self ):
        """ Dublin Core element - resource copyright
        """
        return self.rights

Notice how we format floating point values in our class using the DateTime module. Once our record class takes care of this, we don't have to do date formatting in our Page Template anymore, allowing us to simplify the relevant tal:attributes statements as follows. This original:

<input name="creationdate:date" size="40" value=""
    tal:attributes="value python:DateTime(article['creationdate']).Date()">

can be replaced by a simple path expression:

<input name="creationdate:date" size="40" value=""
    tal:attributes="value article/CreationDate">

This illustrates a core principle when writing Page Template: as soon as you find yourself knitting your brows about a long python: statement that's refusing to fit on one line, consider whether the functionality can be provided by a Python Script elsewhere.

To use Article as our record class, go back to the Advanced tab of the sql_select_article and enter Article as Class Name and Article.py as Class File.

This section presents the standard RDBMS interface that ships with Zope. Various other approaches have been taken to Zope/ RDBMS integration. These include ZPatterns (an early database independence framework), Znolk (a wizard to generate add/edit/delete forms and corresponding ZSQL Methods), APE (Adaptable Persistence), and SQLStorage for Archetype Field instances, which stores individual fields of Archetypes-based instances in a database.

These approaches all have different strengths and tradeoffs, and should be investigated with specific applications in mind.