Log in
Forgot your password?
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 [10-1].
Anonymous User - Apr. 30, 2002 1:59 am: delete "use a different"
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.
Anonymous User - Jan. 18, 2003 8:38 pm: These people simply do not know what they are talking about I suggest you spend some time here: <href a="http://www.dbdebunk.com"></href> Also search for "relational model" on Google.
Anonymous User - Jan. 20, 2003 12:16 am: Being one of "these people", I have no idea what you're saying or asking for. -C
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.
Anonymous User - June 13, 2002 9:32 pm: You say that Zope allows one to "change data" such as "updating [an] address". However, you only cover inserting new data and listing existing data. You do not cover updating data or deleting data. I recommend leaving more advanced topics (like grouping) until you have show readers how to build a simple application that allows them to add, edit, delete, and list their data.
Anonymous User - Sep. 24, 2002 3:37 am: There are plenty of SQL tutorials in the world already, it would be a shame to distract the reader (me) from the Zope information by getting too hung up on SQL. The fundamental differences between Object-centric and Data-centric (i.e. relational) paradigms are relevent, as is "the Zope way" of doing the Object-centric thing.
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.
Anonymous User - May 30, 2002 4:03 pm: Whoa! "Database Adaptors" come out of left field, with no description of what they are and how they fit into the scheme of "Database Connectors" and "Z SQL Methods", both of which have been described and related to each other.
Anonymous User - June 26, 2002 2:33 pm: I have figured this out; you only need a Connection object for the specific database, for example zope includes Z Gadfly Connection, you can install the MySql Connection and you will use the same Z Sql Methods as always, the connector will do the job of translating the zope languaje to the database interface... is this right?
Anonymous User - June 26, 2002 4:33 pm: This is right. Although you aren't *limited* to one, you can have several different database connections for each kind of database. For example, you can have two MySQL connections in the same Zope, each of which point to a different MySQL database.
Anonymous User - Aug. 22, 2002 4:55 am: I was wondering whether each user is handled in a different session. So if 2 users connect to a site (not cached) that uses batching. Will each user be handled separately or could there be a conflict? The same applies to the connection: Is each user using the same connection? Does Zope handle the sharing of this single connection? The same applies to the ZSQL as well of course. -Mac-
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.
Anonymous User - June 20, 2002 10:46 am: Would be nice to see this in the tutorial #11. No where does it mention you need to do something to set up the database...
Anonymous User - July 23, 2002 11:56 am: I've downloaded my MySQL DA but Zope can't seem to find MySQL on my system (Zope and MySQL are installed in different directories). Is there someplace I can add a command so that Zope will look outside of its immediate vicinity? Judging by the messages, it's not looking very far afield: File /home/nancy/Zope-2.5.1-linux2-x86/lib/python/OFS/Application.py, line 531 , in import_product File /home/nancy/Zope-2.5.1-linux2-x86/lib/python/Products/ZMySQLDA/__init__.p y, line 91, in ? File /home/nancy/Zope-2.5.1-linux2-x86/lib/python/Products/ZMySQLDA/DA.py, lin e 92, in ? File /home/nancy/Zope-2.5.1-linux2-x86/lib/python/Products/ZMySQLDA/db.py, lin e 89, in ? ImportError: No module named _mysql Thanks.
Anonymous User - July 23, 2002 12:15 pm: This book is not the right place to ask this question. Use the zope maillist ([email protected]).
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, [10-2] shows the PostgreSQL database connection add form.
Anonymous User - Sep. 23, 2002 8:09 am: Hello! I have a project with Oracle and Zope. I'va installed Oracle on a server and Zope on my computer with Oarcle's adapter In order ton connect to Oracle Database, we have to login user and password. So, I put it in the connection String, but when I try to connect, it throw me an error: Invalid connection string But my ODBC works well and user and password are written like this: [...]" or enter a Database Connection String 1: dbname=MY_DATABASE user=USER_NAME password=MY_PASSWORD Please help me to connect to Oracle Database!
Figure 10-2 PostgreSQL Database Connection
Anonymous User - June 13, 2002 8:41 am: Should people be encouraged to use psycopg now that this is the most active DA, and might be better for some?
For PostgreSQL, the connection string format is shown above in [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.
Anonymous User - Nov. 29, 2002 10:33 am: I know that this request is probably beyond the scope of this book and more work than the authors wish to do, but I tell you it would be a wonderful service if you would go through step by step how to download and install a database adaptor for postgresql. It would be nice to explain how to do it for a zope installation that runs on the Linux and windows platforms. This information is absolutely nowhere. There are hints all over the place about zpopyDA and zpypqslgDA and zpsycoDA but nowhere is it comprehensively explained. You can download stuff all day long but there are no instructions on how to get it to work.
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. [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 )
Anonymous User - Aug. 10, 2002 1:38 am: What other kind of column types does ZSQL support?
Anonymous User - Aug. 13, 2002 4:33 pm: These are postgresql specific SQL commands. ZSQL supports whatever commands the underlying SQL DB supports.
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.
Anonymous User - Sep. 28, 2002 5:43 am: /employee id, which is a unique number/ is that correct or is the wish the father of the thought? i dont see any uniquness constraints and, AFAIK, gadfly hasnt any.
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 )
Anonymous User - Nov. 16, 2002 7:02 pm: emp_id is already the field. Name the index something like emp_id_idx or you will generate an error, and you are not using a good naming convention.
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 [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 [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.
Anonymous User - Dec. 12, 2002 10:56 am: I presume you meant to say "emp_id=100 gives the emp_id argument"
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 (<dtml-sqlvar emp_id type="int">, <dtml-sqlvar first type="string">, <dtml-sqlvar last type="string">, <dtml-sqlvar salary type="float"> )
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 [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.
Anonymous User - Apr. 22, 2002 11:22 pm: 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) Submit Query 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.
Anonymous User - Aug. 14, 2002 1:24 pm: A useful tip is that you can access the column headings of your database table by calling <results object>.names()
Anonymous User - Sep. 28, 2002 6:10 am: what a zsql method really returns (the Result object) is a mystery; for use in python read http://www.zope.org/Members/spinwing/ZSQL_Results for dtml, zsql method automatically returns this dictionary, which is a pythonized table, namely a list of dictionarys, one for each row, each row dictionary w columnnames as keys like {colname1:val1, colname2:val2,...}
Anonymous User - Sep. 28, 2002 6:19 am: /The items in the sequence of results returned by a Z SQL Method are called Result objects/ No. A ZSQL Method returns a *Result object*, which has methods names(), data_dictionary() and dictionaries(), the last having above described structure. blf
Anonymous User - Nov. 15, 2002 11:03 pm: Another piece of information missing is that if you have a filed in your database (I used mysql) of type datetime then the returned object for this particular filed is of type DateTime and not a string.
Anonymous User - Nov. 15, 2002 11:04 pm: Another piece of information missing is that if you have a field in your database (I used mysql) of type datetime then the returned object for this particular field is of type DateTime and not a string.
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:
<dtml-var standard_html_header>
<ul>
<dtml-in list_all_employees>
<li><dtml-var emp_id>: <dtml-var last>, <dtml-var first>
makes <dtml-var salary fmt=dollars-and-cents> a year.
</li>
</dtml-in>
</ul>
<dtml-var standard_html_footer>
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:
<html>
<body>
<ul>
<li>42: Roberts, Bob
makes $50,000 a year.
</li>
<li>101: leCat, Cheeta
makes $100,000 a year.
</li>
<li>99: Junglewoman, Jane
makes $100,001 a year.
</li>
</ul>
</body>
</html>
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.
Anonymous User - May 28, 2002 12:03 pm: the the don't you mean the
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.
Anonymous User - Sep. 5, 2002 10:31 am: You should make the "hireEmployee" be "hireEmployeeID" to make it clear where this value got created. You could even make it "hireEmployeeRptID."
Anonymous User - Nov. 16, 2002 7:29 pm: This delivers an error. You need to check off one of the options: Generate DTML Methods or Generate Page Templates, and for this scenario you want DTML.
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:
<dtml-var standard_html_header>
<dtml-in hire_employee size=50 start=query_start>
<dtml-if sequence-start>
<dtml-if previous-sequence>
<a href="<dtml-var URL><dtml-var sequence-query
>query_start=<dtml-var
previous-sequence-start-number>">
(Previous <dtml-var previous-sequence-size> results)
</a>
</dtml-if previous-sequence>
<table border>
<tr>
</tr>
</dtml-if sequence-start>
<tr>
</tr>
<dtml-if sequence-end>
</table>
<dtml-if next-sequence>
<a href="<dtml-var URL><dtml-var sequence-query
>query_start=<dtml-var
next-sequence-start-number>">
(Next <dtml-var next-sequence-size> results)
</a>
</dtml-if next-sequence>
</dtml-if sequence-end>
<dtml-else>
There was no data matching this <dtml-var title_or_id> query.
</dtml-in>
<dtml-var standard_html_footer>
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:
<dtml-var standard_html_header> <dtml-call hire_employee> <h1>Employee <dtml-var first> <dtml-var last> was Hired!</h1> <p><a href="listEmployees">List Employees</a></p> <p><a href="hireEmployeeForm">Back to hiring</a></p> <dtml-var standard_html_footer>
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.
Anonymous User - Oct. 1, 2002 10:31 pm: hireEmployeeForm, hire_employee, hireEmployee...I'm getting confused with using the same name over and over like this. This is a bad naming convention. There's got to be some better way of naming these things.
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.
rogererens - July 5, 2002 7:37 am: When someone creates his own user interface to the database, the following is noteworthy. It is not possible to call a ZSQL Method from a form directly (explained in the mailing list archives). The action of the form has to be a DTML-method/document or Page Template, which in turn calls the ZSQL Method. Just like the Z Search Interface created the form 'hireEmployeeForm' which has 'hireEmployee' as the action. The DTML Method 'hireEmployee' then calls the ZSQL Method 'hire_employee'
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.
Anonymous User - Sep. 28, 2002 6:29 am: http://www.zope.org/Members/jshell/ZSQLMethods-InsertingData blf
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=<dtml-var 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=<dtml-sqlvar emp_id type=int>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.
Anonymous User - Nov. 22, 2002 12:39 pm: I'd like to see a brief explanation of handling other types/syntax that databases (like MS Access) may require. For example, MS Access has Date/Time data type that requires #[date/time]# in a query.
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 <dtml-sqltest salary op=gt type=float>
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 > 10000sqltest 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" ,
<dtml-sqltest name type="string" multiple>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:
<dtml-sqltest name type="string" op="like">
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.
Anonymous User - Jan. 28, 2003 2:22 pm: White on white translucent black capes Back on the rack Bela Lugosi's dead The bats have left the bell tower The victims have been bled Red velvet lines the black box Bela Lugosi's dead Undead undead undead The virginal brides file past his tomb Strewn with time's dead flowers Bereft in deathly bloom Alone in a darkened room The count Bela Logosi's dead Undead undead undead
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 <dtml-sqlgroup where> <dtml-sqltest salary op=gt type=float optional> <dtml-and> <dtml-sqltest first op=eq type=nb multiple optional> <dtml-and> <dtml-sqltest last op=eq type=nb multiple optional> </dtml-sqlgroup>
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
<dtml-sqlgroup where>
<dtml-sqlgroup>
<dtml-sqltest first op=like type=nb>
<dtml-and>
<dtml-sqltest last op=like type=nb>
</dtml-sqlgroup>
<dtml-or>
<dtml-sqltest salary op=gt type=float>
</dtml-sqlgroup>
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.
Anonymous User - Apr. 6, 2002 12:06 pm: I think you mean "connectivity", as in "database connectivity".
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:
<dtml-var standard_html_header>
<dtml-in expr="employee_by_id(emp_id=42)">
<h1><dtml-var last>, <dtml-var first></h1>
<p><dtml-var first>'s employee id is <dtml-var emp_id>. <dtml-var
first> makes <dtml-var salary fmt=dollars-and-cents> per year.</p>
</dtml-in>
<dtml-var standard_html_footer>
Anonymous User - Oct. 1, 2002 10:20 am: "the arguments are automatically collected from the environment" Need more details on how this automatic process works 'cos it trips a lot of people up. Doesn't it only search the REQUEST, and even then only if it's passed as the first positional parameter to the method?
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
Anonymous User - Sep. 28, 2002 7:19 am: /you calling/you were calling/
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 [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
(
<dtml-sqlvar department_id type=string>,
<dtml-sqlvar description type=string>,
<dtml-sqlvar quantity type=int>,
<dtml-sqlvar unit_cost type=float>
)
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
Anonymous User - Jan. 28, 2003 2:23 pm: Call the curtain Raise the roof Spirits on tonight
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 <dtml-sqltest emp_id op=eq type=int>
Anonymous User - May 5, 2002 2:24 am: The *only* way I could get this to work was to change the statement to: select * from employees <dtml-sqlgroup where> <dtml-sqltest emp_id op=eq type=int optional> </dtml-sqlgroup> For whatever reason, if emp_id is not *optional*, then a "Missing input, emp_id" error is always generated. Using binary release of Zope 2.5.1 on RHL 7.2.
Anonymous User - June 9, 2002 6:01 pm: I believe you are right. I just went through the same ordeal myself. <dtml-sqltest> requires the var to exist, otherwise you get an error. That makes coding sql queries with <dtml-anything> more cumbersome because you first have to check if they exist and then check their values, instead of having sqltest do both
Anonymous User - July 4, 2002 5:06 am: Em. I'd suggest using "optional=true" instead of optional in above example. (But that's a comment to the comment...)
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.
Anonymous User - Sep. 28, 2002 8:03 am: /where as if/as if/ blf
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.
Anonymous User - Sep. 28, 2002 8:05 am: /where as if/as if/ blf sorry for prev paragraf comment, should be here
Anonymous User - Sep. 28, 2002 8:10 am: - With ZSQL Methods that do return more than one result you mean that the Result object is a table? - is the special url syntax also possible w several argument values?
Anonymous User - Oct. 1, 2002 12:06 am: When I try this sort of thing in Zope 2.5.1, I get the error: The object at http://myurl/update_person_by_id/id/9 has an empty or missing docstring. Objects must have a docstring to be published. What do I do about this? For ZSQL Methods that have a single argument, I can select 'simple direct traversal' and leave out the variable name, and everything's ok. Is it something about the variable name 'id'? Or what?
Anonymous User - Oct. 1, 2002 12:20 am: Ok, there *is* something special about 'id' (probably because it's a DTML variable in the namespace). If I change to person_id, I get a new error "The requested resource does not exist". Hmm.
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:
<dtml-var standard_html_header> <h1><dtml-var last>, <dtml-var first></h1> <p><dtml-var first>'s employee id is <dtml-var emp_id>. <dtml-var first> makes <dtml-var salary fmt=dollars-and-cents> per year.</p> <dtml-var standard_html_footer>
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.
Anonymous User - Aug. 8, 2002 9:31 am: This Class is tied to a SQL-Method returning just one row per call. Is it possible to use this feature with SQL-Methods returning multiple rows ? This would be nice - e.g. for filtering query results in the Class, reducing pollution of the container folder by dozens of SQL-Methods.
Anonymous User - Sep. 28, 2002 8:20 am: /A result object has an attribute for each column in results row./ is this correct english? i dont understand. maybe you mean /A result object has in each of its results rows attributes for each table column./ AFAIK, its not an attribute, but a dictionary key. OTOH, getattr is redefined. very foggy here...
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:
<dtml-var standard_html_header> <h1><dtml-var fullName></h1> <p><dtml-var first>'s employee id is <dtml-var emp_id>. <dtml-var first> makes <dtml-var salary fmt=dollars-and-cents> per year.</p> <dtml-var standard_html_footer>
Anonymous User - July 18, 2002 6:34 pm: s/employeeView/viewEmployee/
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:
<dtml-in employee_by_id> <dtml-var fullName> </dtml-in>
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 <dtml-sqltest emp_id type=int op=eq>
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:
<dtml-if manager>
<dtml-with manager>
<p> My manager is <dtml-var first> <dtml-var last>.</p>
</dtml-with>
</dtml-if>
Anonymous User - Sep. 28, 2002 8:28 am: ? <p> My manager is <dtml-var fullName>.</p>
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 [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.
Anonymous User - Sep. 10, 2002 6:30 am: The transactions are covered in Chapter 2 with the Undo concept, not in Chapter one!
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.
Anonymous User - Sep. 28, 2002 8:37 am: /second operations fails/second operation fails/ or /second operations fails/second operations fail/ blf
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.
Anonymous User - June 20, 2002 9:16 pm: I cannot find anything in the book relating to the scope of zope transactions other than with regard to site changes. How does one logically group a set of changes initiated through more than one method/script?
Anonymous User - Sep. 10, 2002 6:36 am: I agree, the transaction concept regarding Z SQL Methods should be covered more thoroughly.
Anonymous User - Sep. 28, 2002 8:57 am:
AFAIK i know, a Zope transaction is erverything from start of request until the response is sent to the
client browser.
Zope braces the request processing
with "start transaction; try" <process request here> "except rollback; else commit".
Say, in processing a web request, a method M calls several ZSQL Methods "A", "B1", "B2",... and inserting "A"
returns a unique primary key used in the calls "Bx" to insert as a foreign key, like
k=A(); B1(k); B2(k);
if one of the ZSQL methods fails, the method M also fails and the database wont change. blf
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.