Installing the PostgreSQL Connector for Zope 3

(I have not yet gotten this to work. I first tried the tarball and failed. Now I'm trying the version in the Zope subversion repository, with better luck but still no success.)

First, successfully talking to PostgreSQL from Zope 3 involves several layers of software, each of which must be just right to work.

1. ====================> PostgreSQL server instance
2. ================> PostgreSQL client libraries
3. ============> mxDateTime
4. ========> psycopg-v1.x
5. ====> PsygopgDA
6. Zope 3 Connector

Step 1 - PostgreSQL Server Instance

You'll need an installed PostgreSQL server someplace, either on your machine or another. The details for this are OS/distribution-specific and won't be covered here.

What's important is that you know your connection details:

• name of machine on which the server resides
• port on which the server is listening
• username to login with
• password to login with
• name of database to connect to

Step 2 - PostgreSQL Client Libraries

Again these are OS/distribution-specific but it sometimes is necessary to know the directories in which the (a) link libraries and (b) header files are located. This information is used in step 3 if the automatic build configuration cannot determine these on its own.

To find the header files directory, do a file search for "pg_config.h".

 [jrush]# locate pg_config.h

To find the link libraries directory, do a file search for "libpq.so":

 [jrush]# locate libpq.so

Step 3 - mxDateTime Python Package

Version 1 of the 'psycopg' database adapter uses a 3rd party date/time module to represent the SQL date type.

Therefore, mxDateTime must be installed first.

Version 2 of the 'psycopg' database adapter uses the date/time module built into Python and no longer needs mxDateTime.

Step 4 - psycopg v1.x

'psycopg' is a general-purpose database adapter for talking to PostgreSQL from the Python language and is not specific to Zope.

Instructions for installing it are here.

Step 5 - PsygopgDA

The package 'psycopgda' (note the trailing DA) is a thin layer over 'psycopg' for hooking it into the Zope 3 component framework.

The version of 'psycopgda' to use with Zope 3.1.0 is NOT the tarball but that within the Zope 3 subversion repository. To retrieve it:

 [jrush]# cd /usr/share/zope-3.1.0/lib/python
 [jrush]# svn co svn://svn.zope.org/repos/main/psycopgda/trunk psycopgda

There is no separate installation step. By placing the package under the Zope installation directory, it will automatically be available to any Zope instances you create from here on. If you already have Zope instances, do the following copy step.

To inform Zope 3 that this database adapter is now present, you must copy the file 'psycopg-configure.zcml' into the 'package-includes' directory of your Zope instance.

NOTE: Be sure you do not install or use the ZPsycopgDA package from Zope 2. Althought similar is name and function, you cannot mix Zope 2 and Zope 3 packages.

Any new Zope instance will now have this package at its disposal. If you want to make the package available to existing Zope 3 instances, you must perform the following sequence of commands:

  [jrush]# cd ~/zope3
  [jrush]# cp lib/python/psycopgda/psycopgda-configure.zcml etc/package-includes/

Step 6 - Zope 3 Connector

A database connection in Zope 3 is registered as a utility.

1. Open a web browser on your Zope root folder (http://localhost:8080/(approve sites) if you use the default settings in zope.conf.in).
2. Click on the 'Manage Site' action on the right side of the screen. You should see a screen which reads 'Common Site Management Tasks'
3. Around the middle of that page, you should see a link named 'Add Utility'. Click on it.
4. Select 'Psycopg DA' and type in a name at the bottom of the page.
5. Enter the database connection string. It looks like this:
   dbi://username:password@host:port/databasename
6. Click on the 'Add' button.
7. You should be on a page which reads 'Add Database Connection Registration'. There you can configure the permission needed to use the database connection, the name of the registration and the registration status. You can use any name for 'Register As' field, as long as it doesn't clash with an existing one. Choose a permission. Choose between 'Registered' and 'Active' for the 'Registration Status'. Only one component of a kind can be 'Active' at a time, so be careful.
8. You should be redirected to the 'Edit' screen of the connection utility.
9. If you want to, you can go to the Test page and execute arbitrary SQL queries to see whether the connection is working as expected.