Zopebuch: Inhaltsverzeichnis 

Dieses Dokument wird momentan �bersetzt und ist zur Zeit leider nur in englisch verf�gbar.

Chapter 12: Relational Database Connectivity

Zope uses an object database to store Zope objects. Relational databases such as Oracle, Sybase and PostgreSQL use a different store information in a different way. Relational databases store their information in tables as shown in Figure 10-1.

Figure 10-1 Relational Database Table

Information in the table is stored in rows. The table's column layout is called the schema. A standard language, called the Structured Query Language (SQL) is used to query and change tables in relational databases.

Zope does not store its information this way. Zope's object database allows for many different types of objects that have many different types of relationships to each other. Relational data does not easily map onto objects since relational data assumes a much simpler table-oriented data model. Zope provides several mechanisms for taking relational data and using it in Zope's object-centric world including Database Adapters and SQL Methods which we will discuss in detail in this chapter.

The most common use for Zope's relational database support is to put existing relational databases on the web. For example, suppose your Human Resources Department has an employee database. Your database comes with tools to allow administrators run reports and change data. However, it is hard for employees to see their own records and perform simple maintenance such as updating their address when they move. By interfacing your relational database with Zope, your employees can use any web browser to view and update their records from the office or at home.

By using your relational data with Zope you get all of Zope's benefits including security, dynamic presentation, networking, and more. You can use Zope to dynamically tailor your data access, data presentation, and data management.

To use a relational database in Zope you must create two different Zope objects, a Database Connection and a Z SQL Method. Database Connections tell Zope how to connect to a relational database. Z SQL Methods describe an action to take on a database. Z SQL Methods use Database Connections to connect to relational databases. We'll look more closely at these two types of objects in this chapter.

Using Database Connections

Database Connections are used to establish and manage connections to external relational databases. Database Connections must be established before database methods can be defined. Moreover, every Z SQL Method must be associated with a database connection. Database adapters (or DAs for short) are available for a number of databases:

Oracle

Oracle is a powerful and popular commercial relational database. This DA is written and commercially supported by Zope Corporation. Oracle can be purchased or evaluated from the Oracle Website.

Sybase

Sybase is another popular commercial relational database. The Sybase DA is written and commercially supported by Zope Corporation. Sybase can be purchased or evaluated from the Sybase Website.

ODBC

ODBC is a cross-platform, industry standard database protocol supported by many commercial and open source databases. The ODBC DA is written and commercially supported by Zope Corporation.

PostgreSQL

PostgreSQL is a leading open source relational database. There are several database adapters for PostgreSQL including ZPoPy which is maintained by Zope community member Thierry Michel. You can find more information about PostgreSQL at the PostgreSQL web site.

MySQL

MySQL is a fast open source relational database. You can find more information about MySQL at the MySQL web site. The MySQL DA is maintained by Zope community member Monty Taylor.

Interbase

Interbase is an open source relational database from Borland/Inprise. You can find more information about Interbase at the Borland web site. You may also be interested in FireBird which is a community maintained offshoot of Interbase. The Zope Interbase adapter is maintained by Zope community member Bob Tierney.

Gadfly

Gadfly is a relational database written in Python by Aaron Waters. Gadfly is included with Zope for demonstration purposes and small data sets. Gadfly is fast, but is not intended for large amounts of information since it reads the entire database into memory. You can find out more about Gadfly at the Chordate website.

Other than Gadfly, all relational databases run as processes external to Zope. In fact, your relational database need not even run on the same machine as Zope, so long as Zope can connect to the machine that the database is running on. Installing and setting up relational databases is beyond the scope of this book. All of the relational databases mentioned have their own installation and configuration documentation that you should consult for specific details.

Because Gadfly runs inside Zope, you do not need to specify any connection information for Zope to find the database. Since all other kinds of databases run externally to Zope, they require you to specify how to connect to the database. This specification, called a connection string, is different for each kind of database. For example, Figure 10-2 shows the PostgreSQL database connection add form.

Figure 10-2 PostgreSQL Database Connection

For PostgreSQL, the connection string format is shown above in Figure 10-2.

In order to use your relational database of choice from Zope, you must download and install the database adapter for your specific relational database. Database adapters can be downloaded from the Products section of Zope.org The exception to this is Gadfly, which is included with Zope. All the examples in this chapter use Gadfly, but the procedures described apply to all databases.

After installing the database adapter product for your database, you can create a new database connection by selecting it from the Add List. All database connections are fairly similar. Select the Z Gadfly Database Connection from the add list. This will take you to the add form for a Gadfly database connection.

Select the Demo data source, specify Gadfly_database_connection for the id, and click the Add button. This will create a new Gadfly Database Connection. Select the new connection by clicking on it.

You are looking at the Status view of the Gadfly Database Connection. This view tells you if you are connected to the database, and there is a button to connect or disconnect. In general Zope will manage the connection to your database for you so there is little reason to manually control the connection. For Gadfly connecting and disconnecting are meaningless, but for external databases you may wish to connect or disconnect manually to do database maintenance.

The next view is the Properties view. This view shows you the data source and other properties of the Database Connection. This is useful if you want to move your Database Connection from one data source to another. Figure 10-3 shows the Properties view.

Figure 10-3 The Properties view

You can test your connection to a database by going to the Test view. This view lets you type SQL code directly and run it on your database. This view is just for testing your database and issuing one time SQL commands (like creating tables). This is not the place where you will enter most of your SQL code. SQL commands reside in Z SQL Methods which are discussed later in this chapter.

Let's create a table in your database to use in this chapter's examples. The Test view of the Database Connection allows you to send SQL statements directly to your database. You can create tables by typing SQL code directly into the Test view; there is no need to use a SQL Method to create tables. Create a table called employees with the following SQL code:

      CREATE TABLE employees
      (
      emp_id integer,
      first varchar,
      last varchar,
      salary float
      )

Click the Submit Query button to run the SQL command. Zope should return a confirmation screen that tells you what SQL code was run and the results if any.

The SQL used here may differ depending on your database. For the exact details of creating tables with your database, check the user documentation from your specific database vendor.

This SQL will create a new table in your Gadfly database called employees. This table will have four columns, emp_id, first, last and salary. The first column is the employee id, which is a unique number that identifies the employee. The next two columns have the type varchar which is similar to a string. The salary column has the type float which holds a floating point number. Every database supports different kinds of types, so consult your documentation to find out what kind of types your database supports.

To ensure that the employee id is a unique number you can create an index on your table. Type the following SQL code in the Test view:

      CREATE UNIQUE INDEX emp_id ON employees
      (
      emp_id
      )

Now you have a table and an index. To examine your table, go to the Browse view. This view lets you view your database's tables and their schemas. Here, you can see that there is an employees table, and if you click on the plus symbol, the table expands to show four columns, emp_id, first, last and salary as shown in Figure 10-4.

Figure 10-4 Browsing the Database Connection

This information is very useful when creating complex SQL applications with lots of large tables as it lets you discover the schemas of your tables. Not all databases support browsing of tables.

Now that you've created a database connection and have defined a table, you can create Z SQL Methods to operate on your database.

Using Z SQL Methods

Z SQL Methods are Zope object that execute SQL code through a Database Connection. All Z SQL Methods must be associated with a Database Connection. Z SQL Methods can both query databases and change data. Z SQL Methods can also contain more than one SQL command.

Next, you need to create a new Z SQL Method called hire_employee that inserts a new employee in the employees table. When a new employee is hired this method is called and a new record is inserted in the employees table that contains the information about the new employee. Select Z SQL Method from the Add List. This will take you to the add form for Z SQL Methods, as shown in Figure 10-5.

Figure 10-5 The Add form for Z SQL Methods

As usual, you must specify an id and title for the Z SQL Method. In addition you need to select a Database Connection to use with this Z SQL Methods. Give this new method the id hire_employee and select the Gadfly_database_connection that you created in the last section.

Next you can specify arguments to the Z SQL Method. Just like Scripts, Z SQL Methods can take arguments. Arguments are used to construct SQL statements. In this case your method needs four arguments, the employee id number, the first name, the last name and the employee's salary. Type "emp_id first last salary" into the Arguments field. You can put each argument on its own line, or you can put more than one argument on the same line separated by spaces. You can also provide default values for argument just like with Python Scripts. For example, empid=100 gives the empid argument a default value of 100.

The last form field is the Query template. This field contains the SQL code that is executed when the Z SQL Method is called. In this field, enter the following code:

      insert into employees (emp_id, first, last, salary) values
      (, 
       , 
       ,
       
      )

Notice that this SQL code also contains DTML. The DTML code in this template is used to insert the values of the arguments into the SQL code that gets executed on your database. So, if the emp_id argument had the value 42, the first argument had the value Bob your last argument had the value Uncle and the salary argument had the value 50000.00 then the query template would create the following SQL code:

      insert into employees (emp_id, first, last, salary) values
      (42,
       'Bob',
       'Uncle',
       50000.00
      )

The query template and SQL-specific DTML tags are explained further in the next section.

You have your choice of three buttons to click to add your new Z SQL Method. The Add button will create the method and take you back to the folder containing the new method. The Add and Edit button will create the method and make it the currently selected object in the Workspace. The Add and Test button will create the method and take you to the method's Test view so you can test the new method. To add your new Z SQL Method, click the Add button.

Now you have a Z SQL Method that inserts new employees in the employees table. You'll need another Z SQL Method to query the table for employees. Create a new Z SQL Method with the id list_all_employees. It should have no arguments and contain the SQL code:

      select * from employees

This simple SQL code selects all the rows from the employees table. Now you have two Z SQL Methods, one to insert new employees and one to view all of the employees in the database. Let's test your two new methods by inserting some new employees in the employees table and then listing them. To do this, click on the hire_employee Method and click the Test tab. This will take you to the Test view of the Method, as shown in Figure 10-6.

Figure 10-6 The hire_employee Test view

Here, you see a form with four input boxes, one for each argument to the hire_employee Z SQL Method. Zope automatically generates this form for you based on the arguments of your Z SQL Method. Because the hire_employee Method has four arguments, Zope creates this form with four input boxes. You can test the method by entering an employee number, a first name, a last name, and a salary for your new employee. Enter the employee id "42", "Bob" for the first name, "McBob" for the last name and a salary of "50000.00". Then click the Test button. You will then see the results of your test.

The screen says This statement returned no results. This is because the hire_employee method only inserts new information in the table, it does not select any information out of the table, so no records were returned. The screen also shows you how the query template get rendered into SQL. As expected, the sqlvar DTML tags rendered the four arguments into valid SQL code that your database executed. You can add as many employees as you'd like by repeatedly testing this method.

To verify that the information you added is being inserted into the table, select the list_all_employees Z SQL Method and click on its Test tab.

This view says This query requires no input, indicating the list_all_employees does not have any argument and thus, requires no input to execute. Click on the Submit Query button to test the method.

The list_all_employees method returns the contents of your employees table. You can see all the new employees that you added. Zope automatically generates this tabular report screen for you. Next we'll show how you can create your own user interface to your Z SQL Methods to integrate them into your web site.

Calling Z SQL Methods

Querying a relational database returns a sequence of results. The items in the sequence are called result rows. SQL query results are always a sequence. Even if the SQL query returns only one row, that row is the only item contained in a list of results. Hence, Z SQL Methods always return a sequence of results which contains zero or more results records.

The items in the sequence of results returned by a Z SQL Method are called Result objects. Result objects can be thought of as rows from the database table turned into Zope objects. These objects have attributes that match the schema of the database results.

An important difference between result objects and other Zope objects is that result objects do not get created and permanently added to Zope. Result objects are not persistent. They exist for only a short period of time; just long enough for you to use them in a result page or to use their data for some other purpose. As soon as you are done with a request that uses result objects they go away, and the next time you call a Z SQL Method you get a new set of fresh result objects.

Result objects can be used from DTML to display the results of calling a Z SQL Method. For example, add a new DTML Method to your site called listEmployees with the following DTML content:

        

          

          
            
: ,  
              makes  a year.
            
          
          

        

This method calls the list_all_employees Z SQL Method from DTML. The in tag is used to iterate over each Result object returned by the list_all_employees Z SQL Method. Z SQL Methods always return a list of objects, so you will almost certainly use them from the DTML in tag unless you are not interested in the results or if the SQL code will never return any results, like hire_employee.

The body of the in tag is a template that defines what gets rendered for each Result object in the sequence returned by list_all_employees. In the case of a table with three employees in it, listEmployees might return HTML that looks like this:

• 42: Roberts, Bob makes $50,000 a year.
• 101: leCat, Cheeta makes $100,000 a year.
• 99: Junglewoman, Jane makes $100,001 a year.

The in tag rendered an HTML list item for each Result object returned by list_all_employees.

Next we'll look at how to create user interfaces in order to collect data and pass it to Z SQL Methods.

Providing Arguments to Z SQL Methods

So far, you have the ability to display employees with the the listEmployees DTML Method which calls the list_all_employees Z SQL Method. Now let's look at how to build a user interface for the hire_employee Z SQL Method. Recall that the hire_employee accepts four arguments, emp_id, first, last, and salary. The Test tab on the hire_employee method lets you call this method, but this is not very useful for integrating into a web application. You need to create your own input form for your Z SQL Method or call it manually from your application.

The Z Search Interface can create an input form for you automatically. In Chapter 11, "Searching and Categorizing Content", you used the Z Search Interface to build a form/action pair of methods that automatically generated an HTML search form and report screen that queried the Catalog and returned results. The Z Search Interface also works with Z SQL Methods to build a similar set of search/result screens.

Select Z Search Interface from the add list and specify hire_employee as the Searchable object. Enter the value "hireEmployee" for the Report Id and "hireEmployeeForm" for the Search Id and click Add.

Click on the newly created hireEmployeeForm and click the View tab. Enter an employee_id, a first name, a last name, and salary for a new employee and click Submit. Zope returns a screen that says "There was no data matching this query". Because the report form generated by the Z Search Interface is meant to display the result of a Z SQL Method, and the hire_employee Z SQL Method does not return any results; it just inserts a new row in the table. Edit the hireEmployee DTML Method a little to make it more informative. Select the hireEmployee Method. It should contain the following long stretch of DTML:

        

        

           

              

                
                (Previous  results)
                

              

              

              
              

                 
                 (Next  results)
                 

              

           

        

          There was no data matching this  query.

        

        

This is a pretty big piece of DTML! All of this DTML is meant to dynamically build a batch-oriented tabular result form. Since we don't need this, let's change the hireEmployee method to be much simpler:

        

        

        
Employee   was Hired!


        
List Employees

        
Back to hiring

        

Now view hireEmployeeForm and hire another new employee. Notice how the hire_employee method is called from the DTML call tag. This is because we know there is no output from the hire_employee method. Since there are no results to iterate over, the method does not need to be called with the in tag. It can be called simply with the call tag.

Now you have a complete user interface for hiring new employees. Using Zope's security system, you can now restrict access to this method to only a certain group of users whom you want to have permission to hire new employees. Keep in mind, the search and report screens generated by the Z Search Interface are just guidelines that you can easily customize to suite your needs.

Next we'll take a closer look at precisely controlling SQL queries. You've already seen how Z SQL Methods allow you to create basic SQL query templates. In the next section you'll learn how to make the most of your query templates.

Dynamic SQL Queries

A Z SQL Method query template can contain DTML that is evaluated when the method is called. This DTML can be used to modify the SQL code that is executed by the relational database. Several SQL specific DTML tags exist to assist you in the construction of complex SQL queries. In the next sections you'll learn about the sqlvar, sqltest, and sqlgroup tags.

Inserting Arguments with the Sqlvar Tag

It's pretty important to make sure you insert the right kind of data into a column in a database. You database will complain if you try to use the string "12" where the integer 12 is expected. SQL requires that different types be quoted differently. To make matters worse, different databases have different quoting rules.

In addition to avoiding errors, SQL quoting is important for security. Suppose you had a query that makes a select:

        select * from employees 
          where emp_id=

This query is unsafe since someone could slip SQL code into your query by entering something like 12; drop table employees as an emp_id. To avoid this problem you need to make sure that your variables are properly quoted. The sqlvar tag does this for you. Here is a safe version of the above query that uses sqlvar:

        select * from employees 
          where emp_id=

The sqlvar tag operates similarly to the regular DTML var tag in that it inserts values. However it has some tag attributes targeted at SQL type quoting, and dealing with null values. The sqlvar tag accepts a number of arguments:

name

The name argument is identical to the name argument for the var tag. This is the name of a Zope variable or Z SQL Method argument. The value of the variable or argument is inserted into the SQL Query Template. A name argument is required, but the "name=" prefix may be omitted.

type

The type argument determines the way the sqlvar tag should format the value of the variable or argument being inserted in the query template. Valid values for type are string, int, float, or nb. nb stands for non-blank and means a string with at least one character in it. The sqlvar tag type argument is required.

optional

The optional argument tells the sqlvar tag that the variable or argument can be absent or be a null value. If the variable or argument does not exist or is a null value, the sqlvar tag does not try to render it. The sqlvar tag optional argument is optional.

The type argument is the key feature of the sqlvar tag. It is responsible for correctly quoting the inserted variable. See Appendix A for complete coverage of the sqlvar tag.

You should always use the sqlvar tag instead of the var tag when inserting variables into a SQL code since it correctly quotes variables and keeps your SQL safe.

Equality Comparisons with the Sqltest Tag

Many SQL queries involve equality comparison operations. These are queries that ask for all values from the table that are in some kind of equality relationship with the input. For example, you may wish to query the employees table for all employees with a salary greater than a certain value.

To see how this is done, create a new Z SQL Method named employees_paid_more_than. Give it one argument, salary, and the following SQL template:

        select * from employees 
          where 

Now click Add and Test. The op tag attribute is set to gt, which stands for greater than. This Z SQL Method will only return records of employees that have a higher salary than what you enter in this input form. The sqltest builds the SQL syntax necessary to safely compare the input to the table column. Type "10000" into the salary input and click the Test button. As you can see the sqltest tag renders this SQL code:

        select * from employees
          where salary > 10000

The sqltest tag renders these comparisons to SQL taking into account the type of the variable and the particularities of the database. The sqltest tag accepts the following tag parameters:

name

The name of the variable to insert.

type

The data type of the value to be inserted. This attribute is required and may be one of string, int, float, or nb. The nb data type stands for "not blank" and indicates a string that must have a length that is greater than 0. When using the nb type, the sqltest tag will not render if the variable is an empty string.

column

The name of the SQL column, if different than the name attribute.

multiple

A flag indicating whether multiple values may be provided. This lets you test if a column is in a set of variables. For example when name is a list of strings "Bob" , "Billy" , renders to this SQL: name in ("Bob", "Billy").

optional

A flag indicating if the test is optional. If the test is optional and no value is provided for a variable then no text is inserted. If the value is an empty string, then no text will be inserted only if the type is nb.

op

A parameter used to choose the comparison operator that is rendered. The comparisons are: eq (equal to), gt (greater than), lt (less than), ge (greater than or equal to), le (less than or equal to), and ne (not equal to).

See Appendix A for more information on the sqltest tag. If your database supports additional comparison operators such as like you can use them with sqlvar. For example if name is the string "Mc%", the SQL code:

would render to:

        name like 'Mc%'

The sqltest tag helps you build correct SQL queries. In general your queries will be more flexible and work better with different types of input and different database if you use sqltest rather than hand coding comparisons.

Creating Complex Queries with the Sqlgroup Tag

The sqlgroup tag lets you create SQL queries that support a variable number of arguments. Based on the arguments specified, SQL queries can be made more specific by providing more arguments, or less specific by providing less or no arguments.

Here is an example of an unqualified SQL query:

        select * from employees

Here is an example of a SQL query qualified by salary:

        select * from employees
        where(
          salary > 100000.00
        )

Here is an example of a SQL query qualified by salary and first name:

        select * from employees 
        where(
          salary > 100000.00
          and
          first in ('Jane', 'Cheetah', 'Guido')    
        )

Here is an example of a SQL query qualified by a first and a last name:

        select * from employees 
        where(
          first = 'Old'
          and
          last = 'McDonald'     
        )

All three of these queries can be accomplished with one Z SQL Method that creates more specific SQL queries as more arguments are specified. The following SQL template can build all three of the above queries:

        select * from employees 
        
          
        
          
        
          
          

The sqlgroup tag renders the string where if the contents of the tag body contain any text and builds the qualifying statements into the query. This sqlgroup tag will not render the where clause if no arguments are present.

The sqlgroup tag consists of three blocks separated by and tags. These tags insert the string and if the enclosing blocks render a value. This way the correct number of ands are included in the query. As more arguments are specified, more qualifying statements are added to the query. In this example, qualifying statements restricted the search with and tags, but or tags can also be used to expand the search.

This example also illustrates multiple attribute on sqltest tags. If the value for first or last is a list, then the right SQL is rendered to specify a group of values instead of a single value.

You can also nest sqlgroup tags. For example:

        select * from employees
        
          
             
          
             
          
        
          
        

Given sample arguments, this template renders to SQL like so:

        select * from employees
        where
        ( (first like 'A%'
           and
           last like 'Smith'
          )
          or
          salary > 20000.0
        )

You can construct very complex SQL statements with the sqlgroup tag. For simple SQL code you won't need to use the sqlgroup tag. However, if you find yourself creating a number of different but related Z SQL Methods you should see if you can't accomplish the same thing with one method that uses the sqlgroup tag.

Advanced Techniques

So far you've seen how to connect to a relational database, send it queries and commands, and create a user interface. These are the basics of relational database conductivity in Zope.

In the following sections you'll see how to integrate your relational queries more closely with Zope and enhance performance. We'll start by looking at how to pass arguments to Z SQL Methods both explicitly and by acquisition. Then you'll find out how you can call Z SQL Methods directly from URLs using traversal to result objects. Next you'll find out how to make results objects more powerful by binding them to classes. Finally we'll look at caching to improve performance and how Zope handles database transactions.

Calling Z SQL Methods with Explicit Arguments

If you call a Z SQL Method without argument from DTML, the arguments are automatically collected from the environment. This is the technique that we have used so far in this chapter. It works well when you want to query a database from a search form, but sometimes you want to manually or programmatically query a database. Z SQL Methods can be called with explicit arguments from DTML or Python. For example, to query the employee_by_id Z SQL Method manually, the following DTML can be used:

        

          
            
, 

            
's employee id is .   makes  per year.
          

        

Remember, the employee_by_id method returns only one record, so the body of the in tag in this method will execute only once. In the example you calling the Z SQL Method like any other method and passing it a keyword argument for emp_id. The same can be done easily from Python:

        ## Script (Python) "join_name"
        ##parameters=id
        ##
        for result in context.employee_by_id(emp_id=id):
            return result.last + ', ' + result.first

This script accepts an id argument and passes it to employee_by_id as the emp_id argument. It then iterates over the single result and joins the last name and the first name with a comma.

You can provide more control over your relational data by calling Z SQL Methods with explicit arguments. It's also worth noting that from DTML and Python Z SQL Methods can be called with explicit arguments just like you call other Zope methods.

Acquiring Arguments from other Objects

Z SQL can acquire information from other objects and be used to modify the SQL query. Consider Figure 10-7, which shows a collection of Folders in a organization's web site.

Figure 10-7 Folder structure of an organizational web site

Suppose each department folder has a department_id string property that identifies the accounting ledger id for that department. This property could be used by a shared Z SQL Method to query information for just that department. To illustrate, create various nested folders with different department_id string properties and then create a Z SQL Method with the id requisition_something in the root folder that takes three arguments, description, quantity, and unit_cost. and the following query template:

        INSERT INTO requisitions 
          (
            department_id, description, quantity, unit_cost
          )
        VALUES
          (
            ,
            ,
            ,
            
          )

Now, create a Z Search Interface with a Search Id of "requisitionSomethingForm" and the Report id of "requisitionSomething". Select the requisition_something Z SQL Method as the Searchable Object and click Add.

Edit the requisitionSomethingForm and remove the first input box for the department_id field. We don't want the value of department_id to come from the form, we want it to come from a property that is acquired.

Now, you should be able to go to a URL like:

        http://example.org/Departments/Support/requisitionSomethingForm

and requisition some punching bags for the Support department. Alternatively, you could go to:

        http://example.org/Departments/Sales/requisitionSomethingForm

And requisition some tacky rubber key-chains with your logo on them for the Sales department. Using Zope's security system as described in Chapter 7, "Users and Security", you can now restrict access to these forms so personnel from departments can requisition items just for their department and not any other.

The interesting thing about this example is that department_id was not one of the arguments provided to the query. Instead of getting the value of this variable from an argument, it acquires the value from the folder where the Z SQL Method is accessed. In the case of the above URLs, the requisition_something Z SQL Method acquires the value from the Sales and Support folders. This allows you to tailor SQL queries for different purposes. All the departments can share a query but it is customized for each department.

By using acquisition and explicit argument passing you can tailor your SQL queries to your web application.

Traversing to Result Objects

So far you've provided arguments to Z SQL Methods from web forms, explicit argument, and acquisition. You can also provide arguments to Z SQL Methods by calling them from the web with special URLs. This is called traversing to results objects. Using this technique you can walk directly up to result objects using URLs.

In order to traverse to result objects with URLs, you must be able to ensure that the SQL Method will return only one result object given one argument. For example, create a new Z SQL Method named employee_by_id that accepts one argument, emp_id, and has the following SQL Template:

        select * from employees where
          

This method selects one employee out of the employees table based on their employee id. Since each employee has a unique id, only one record will be returned. Relational databases can provide these kinds of uniqueness guarantees.

Zope provides a special URL syntax to access ZSQL Methods that always return a single result. The URL consists of the URL of the ZSQL Method followed by the argument name followed by the argument value. For example, http://localhost:8080/employee_by_id/emp_id/42. Note, this URL will return a single result object where as if you queried the ZSQL Method from DTML and passed it a single argument it would return a list of results that happend to only have one item in it.

Unfortunately the result object you get with this URL is not very interesting to look at. It has no way to display itself in HTML. You still need to display the result object. To do this, you can call a DTML Method on the result object. This can be done using the normal URL acquisition rules described in Chapter 10, "Advanced Zope Scripting". For example, consider the following URL:

        http://localhost:8080/employee_by_id/emp_id/42/viewEmployee

Here we see the employee_by_id Z SQL Method being passed the emp_id argument by URL. The viewEmployee method is then called on the result object. Let's create a viewEmployee DTML Method and try it out. Create a new DTML Method named viewEmployee and give it the following content:

        

          
, 

          
's employee id is .   makes  per year.

        

Now when you go to the URL http://localhost:8080/employee_by_id/emp_id/42/viewEmployee the viewEmployee DTML Method is bound the result object that is returned by employee_by_id. The viewEmployee method can be used as a generic template used by many different Z SQL Methods that all return employee records.

Since the employee_by_id method only accepts one argument, it isn't even necessary to specify emp_id in the URL to qualify the numeric argument. If your Z SQL Method has one argument, then you can configure the Z SQL Method to accept only one extra path element argument instead of a pair of arguments. This example can be simplified even more by selecting the employee_by_id Z SQL Method and clicking on the Advanced tab. Here, you can see a check box called Allow "Simple" Direct Traversal. Check this box and click Change. Now, you can browse employee records with simpler URLs like http://localhost:8080/employee_by_id/42/viewEmployee. Notice how no emp_id qualifier is declared in the URL.

Traversal gives you an easy way to provide arguments and bind methods to Z SQL Methods and their results. Next we'll show you how to bind whole classes to result objects to make them even more powerful.

Binding Classes to Result Objects

A result object has an attribute for each column in results row. However, result objects do not have any methods, just attributes.

There are two ways to bind a method to a Result object. As you saw in the previous section, you can bind DTML and other methods to Z SQL Method Result objects using traversal to the results object coupled with the normal URL based acquisition bind mechanism described in Chapter 10, "Advanced Zope Scripting". You can also bind methods to Result objects by defining a Python class that gets mixed in with the normal, simple Result object class. These classes are defined in the same location as External Methods in the filesystem, in Zope's Extensions directory. Python classes are collections of methods and attributes. By associating a class with a Result object, you can make the Result object have a rich API and user interface.

Classes used to bind methods and other class attributes to Result classes are called Pluggable Brains, or just Brains. Consider the example Python class:

        class Employee:

          def fullName(self):
            """ The full name in the form 'John Doe' """
            return self.first + ' ' + self.last

When result objects with this Brains class are created as the result of a Z SQL Method query, the Results objects will have Employee as a base class. This means that the record objects will have all the methods defined in the Employee class, giving them behavior, as well as data.

To use this class, create the above class in the Employee.py file in the Extensions directory. Go the Advanced tab of the employee_by_id Z SQL Method and enter Employee in the Class Name field, and Employee in the Class File field and click Save Changes. Now you can edit the employeeView DTML Method to contain:

        

          


          
's employee id is .   makes  per year.

        

Now when you go to the URL http://localhost:8080/employee_by_id/42/viewEmployee the fullName method is called by the viewEmployee DTML Method. The fullName method is defined in the Employee class of the Employee module and is bound to the result object returned by employee_by_id

Brains provide a very powerful facility which allows you to treat your relational data in a more object-centric way. For example, not only can you access the fullName method using direct traversal, but you can use it anywhere you handle result objects. For example:

For all practical purposes your Z SQL Method returns a sequence of smart objects, not just data.

This example only scratches the surface of what can be done with Brains classes. Python programming is beyond the scope of this book so we will only go a little farther here. However, you could create brains classes that accessed network resources, called other Z SQL Methods, performed all kinds of business logic.

Here's a more powerful example of brains. Suppose that you have an managers table to go with the employees table that you've used so far. Suppose also that you have a manager_by_id Z SQL Method that returns a manager id manager given an emp_id argument:

        select manager_id from managers where
                  

You could use this Z SQL Method in your brains class like so:

        class Employee:

            def manager(self):
                """
                Returns this employee's manager or None if the
                employee does not have a manager.
                """
                # Calls the manager_by_id Z SQL Method.
                records=self.manager_by_id(emp_id=self.emp_id)
                if records:
                    manager_id=records[0].manager_id
                    # Return an employee object by calling the
                    # employee_by_id Z SQL Method with the manager's emp_id
                    return self.employee_by_id(emp_id=manager_id)[0]

This Employee class shows how methods can use other Zope objects to weave together relational data to make it seem like a collection of objects. The manager method calls two Z SQL Methods, one to figure out the emp_id of the employee's manager, and another to return a new Result object representing the manager. You can now treat employee objects as though they have simple references to their manager objects. For example you could add something like this to the viewEmployee DTML Method:

        
          
            
 My manager is  .
          
        

As you can see brains can be both complex and powerful. When designing relational database applications you should try to keep things simple and add complexity slowly. It's important to make sure that your brains classes don't add lots of unneeded overhead.

Caching Results

You can increase the performance of your SQL queries with caching. Caching stores Z SQL Method results so that if you call the same method with the same arguments frequently, you won't have to connect to the database every time. Depending on your application, caching can dramatically improve performance.

To control caching, go to the Advanced tab of a SQL Method. You have three different cache controls as shown in Figure 10-8.

Figure 10-8 Caching controls for Z SQL Methods

The Maximum number of rows received field controls how much data to cache for each query. The Maximum number of results to cache field controls how many queries to cache. The Maximum time (in seconds) to cache results controls how long cached queries are saved for. In general, the larger you set these values the greater your performance increase, but the more memory Zope will consume. As with any performance tuning, you should experiment to find the optimum settings for your application.

In general you will want to set the maximum results to cache to just high enough and the maximum time to cache to be just long enough for your application. For site with few hits you should cache results for longer, and for sites with lots of hits you should cache results for a shorter period of time. For machines with lots of memory you should increase the number of cached results. To disable caching set the cache time to zero seconds. For most queries, the default value of 1000 for the maximum number of rows retrieved will be adequate. For extremely large queries you may have to increase this number in order to retrieve all your results.

Transactions

A transaction is a group of operations that can be undone all at once. As you saw in Chapter 1, "Introducing Zope", all changes done to Zope are done within transactions. Transactions ensure data integrity. When using a system that is not transactional and one of your web actions changes ten objects, and then fails to change the eleventh, then your data is now inconsistent. Transactions allow you to revert all the changes you made during a request if an error occurs.

Imagine the case where you have a web page that bills a customer for goods received. This page first deducts the goods from the inventory, and then deducts the amount from the customers account. If the second operations fails for some reason you want to make sure the change to the inventory doesn't take effect.

Most commercial and open source relational databases support transactions. If your relational database supports transactions, Zope will make sure that they are tied to Zope transactions. This ensures data integrity across both Zope and your relational database. If either Zope or the relational database aborts the transaction, the entire transaction is aborted.

Summary

Zope allows you to build web applications with relational databases. Unlike many web application servers, Zope has its own object database and does not require the use of relational databases to store information.

Zope lets you use relational data just like you use other Zope objects. You can connect your relational data to business logic with scripts and brains, you can query your relational data with Z SQL Methods and presentation tools like DTML, and your can even use advanced Zope features like URL traversal, acquisition, undo and security while working with relational data.