Search | Download | Documentation | Resources | Members

Guest

Zope Exits

Zope Newbies
Technocrat.net
Appwatch.com
CodeCatalog.com

Welcome to Zope.org

This is an early draft chapter from a forthcoming book on Zope, to be published by O'Reilly & Associates. The material has not been through O'Reilly's editorial process, nor has it been reviewed for technical accuracy. O'Reilly & Associates disclaims responsibility for any errors in this draft and advises readers to use the information contained herein with caution.

O'Reilly & Associates grants readers the right to read this material and to print copies or make electronic copies for their own use. O'Reilly & Associates does not grant anyone the right to use this material as part of a commercial product or to modify and distribute it. When O'Reilly & Associates publishes the final draft of this book in print form, the content will be made available under an open content license, but this chapter is not open content.

If you have any comments on the material in this chapter, you should send them to the authors, Michel Pelletier and Amos Latteier, at docs@digicool.com.

Relational Database Connectivity

Introduction

Object/Relational Integration

Zope's database is an object database. Many popular databases such as Oracle, Sybase and Postgress work on a different data model called a relational database.

Relational databases store their information in table data structures. A table can be pictured like this:

        <diagram>

Information in the table is stored in rows, and the columns of a table are called the table schema.

Relational data models are not object oriented. Zope provides several useful mechanisms for taking relational data and using them in Zope's object oriented way.

If you are unfamiliar with relational databases or are uninterested in learning this part of Zope, you can skip onto the next Chapter which has got some good bits about the Catalog.

Transactional Integrity

A transaction is a group of operations that can be undone all at once. All changes done to Zope are done within a transaction. This undoing action is often refered to as rolling back a transaction.

The reason for this is 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 occours.

Immagine the case where you have a web page that bills a customer for good 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 the change to the inventory doesn't take effect.

Relational Transactions

Transactions are very popular in relational databases. Almost all commercial and free relational databases support transactions (with the notable exception of MySQL).

Relational databases support transactions for the same reasons Zope does. If you try to INSERT one hundred rows into a table and the 99th INSERT fails, you want to make sure all the changes you made are are not saved so that your database is still consistent.

An important thing to consider is that usualy both Zope and your relational database support transactions. Because Zope can modify data in the relational database, it is important to make sure that Zope and the relational database know how to work with each others transactions.

Lets reconsider the good recieved example. Lets say you store all of your inventory in Zope, but all of you customer accounts are stored in a relational database. If you deduct the good from inventory but the INSERT into the relational database fails you want to first transaction to be rolled back.

Zope is designed so that it works properly with all supported databases and their transactions. You do not need to worry about making sure all your data is consistent if an error ocurrs while working with databases.

Database Connection

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 Connections provide a management interface for connecting to and disconnecting from the external database. Some database connections provide interfaces for browsing database schema information. Database connections are provided in Zope database adapter products. Database adapters are available for a number of databases, including ODBC, Solid, Oracle, MySQL and Gadfly:

      <screenshot of neat table browsing?>

The information needed to connect to a database depends on the specific database being used. Some database adapters provide database connection creation interfaces that let you select from a known set of databases, while others require you to enter a connection string.

Database connections are established when a database connection is created and later whenever a database connection is used. Database connections are automatically closed after a period of disuse and reopened when necessary.

Making an Example Gadfly Connection

XXX

ZSQLMethods

Introduction

ZSQLMethods integreate relational databases into the Zope framework. Relational databases store their information in tables. Tables can logically thought of as a spread-sheet like grid of information:

        <diagram>

The vertical columns of the grid represent the schema of the table, and the horizontal rows of the grid represent the individual rows of data.

Each row in a table is an entry in the database. It can be treated like an object which attributes (the names of the schema columns) and values.

SQL Content

ZSQLMethods contain SQL content. When a ZSQLMethod is called in Zope, the SQL content is executed by the relational database. If the SQL content was a query, then the results are returned as a sequence of rows of data encapsulated in Record objects.

DTML

ZSQLMethod contents can contain DTML that is evaulated 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 in the construction of complex SQL queries.

Sqlvar Tag and Friends

Validating Arguments

It's pretty important to make sure you put the right kind of information into a column in a database. A low-level, database specific error you would be returned if you tried to INSERT the string twelve where the integer 12 should have been used. These errors can be caught easily

Z SQL Methods implement some extra DTML tags to aid in writing correct (and safe!) SQL. SQLVAR is the one most likely to be used heavily in INSERT statements. SQLVAR operates similarly to the regular DTML VAR tag in that in inserts values into the SQL Method, but it has some tag attributes targeted at SQL level type safety, and dealing with inserting NULL values if desired. For the most part, this is the most preferred way with type checking and safety with Z SQL Methods. See the Z SQL Methods documentation for more details.

Arguments

SQL Methods can be given a list of arguments to expect when they are called.

Selecting employees out of a datbase - Example: XXX

Calling SQL Methods

Introduction

Traditionally, querying a relational database returns a sequence of results. The rows in the sequence are called result rows.

Zope takes a very object oriented view of the data returned by relational databases. When a SQL method is queried, it returns a Result object.

The Form/Action Pattern SQL Reprise

The Form/Action pattern is very useful when working with SQL Methods.

The Z Search Interface can be used to automatically build query forms and result reports that use the Form/Action pattern.

Using ZSI and SQL Method - Example

Scenario:

Search a database of houses for sale.

Create a ZSQL Method searchListsings

Arguments: city, square_ft, price

SQL:

            from houses SELECT city, square_ft, house where...

Add ZSI.

Select searchListsings.

Name search and report form/action pair.

View search form.

Perform search.

Bob is your uncle.

Calling with Explicit Arguments

SQL Methods can be called with explicit arguments from DTML.

Explicit Query - Example: XXX

Acquiring Query Context

ZSQL can acquire information from other objects and be used to modify the SQL query. Consider the structure:

          Budget/
            Departments/
              Engineering/
              Marketing/
              Sales/
              Support/

Give each department folder department_id string properties.

Placing the following query in the method Foo in Budget:

          o id: sqlInsertRequisition

          o argument: description, qty, unit_cost

          o SQL content::

            INSERT INTO requisitions 
              (
                department_id, description, qty, unit_cost
              )
            VALUES
              (
                <dtml-sqlvar department_id type=string>,
                <dtml-sqlvar description type=string>,
                <dtml-sqlvar qty type=int>,
                <dtml-sqlvar unit_cost type=float>
              )

Create/Modify Data

Creating Tables: SQL Methods can be used to execute SQL Data Description Language code that is used to build table structures in relational databases.

Record Objects

Introduction

Traditionally a SQL query results in a sequence of rows of information from the database table. This can be pictured like the following::

The results of ZSQLMethods are very similar, they are sequences of results that corespond to rows, but instead of the rows being just flat arrays of information, Zope will turn each row into a Record Object. A Record Object has an attribute for each Column in the database whose value is the value of that column for the result row::

Consider the example:

          <dtml-var standard_html_header>
          <dl>
            <dtml-in Employees>
              <dt><dtml-var employee_id></dt>
              <p>First Name: <dtml-var first_name><br>
              Last Name: <dtml-var last_name>
              </dd>
            </dtml-in>
          </dl>  

          <dtml-var standard_html_footer>

Example screenshot:

          <screen shot>

Accessing Results Through the Web

Results from SQL queries can be views through the web by calling the SQL Method with a special URL.

Displaying Employee Information - Example: Use direct traversal to display employee Record objects by querying them from a SQL Method.

Brains

Brains allow you to associate a Python or ZClass with a Record object. Consider the example class:

          class Employee:

            def fullName(self):
              """ return the full name in the form 'John Doe' """
              return self.first_name + ' ' + self.last_name

            def yearlyIncome(self):
              """ calculate the employees yearlyIncome """

              return (self.weekly_hours * self.hourly_wage * 52)

This class is then mixed in the with Record class which defines the behavior for Record objects. When a Record object with Brains is instanciated as the result of a SQL query, those objects will have Employee as a base class, giving the resultant Record objects behavior, as well as data:

          <dtml-var standard_html_header>
          <dl>
            <dtml-in Employees>
              <dt><dtml-var employee_id></dt>
              <dd><p><dtml-var fullName> makes an estimated 
              <dtml-var yearlyIncome fmt=dollars> per year.</p>
              </dd>
            </dtml-in>
          </dl>  

          <dtml-var standard_html_footer>

If you have any comments on the material in this chapter, you should send them to the authors, Michel Pelletier and Amos Latteier, at docs@digicool.com.