[Checkins] SVN: zope2book/trunk/ Restify RelationalDatabases chapter.
Tres Seaver
tseaver at palladion.com
Tue Feb 10 18:43:08 EST 2009
Log message for revision 96432:
Restify RelationalDatabases chapter.
Changed:
D zope2book/trunk/RelationalDatabases.stx
A zope2book/trunk/source/RelationalDatabases.rst
U zope2book/trunk/source/index.rst
-=-
Deleted: zope2book/trunk/RelationalDatabases.stx
===================================================================
--- zope2book/trunk/RelationalDatabases.stx 2009-02-10 23:26:53 UTC (rev 96431)
+++ zope2book/trunk/RelationalDatabases.stx 2009-02-10 23:43:08 UTC (rev 96432)
@@ -1,1367 +0,0 @@
-Relational Database Connectivity
-
- The Zope Object Database (ZODB) is used to store all the pages,
- files and other objects you create. It is fast and requires almost
- no setting up or maintenance. Like a filesystem, it is especially
- good at storing moderately-sized binary objects such as graphics.
-
- Relational Databases work in a very different way. They are based on
- tables of data such as this::
-
- Row First Name Last Name Age
- === ========== ========= ===
- 1 Bob McBob 42
- 2 John Johnson 24
- 3 Steve Smith 38
-
- 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. This chapter assumes a basic knowledge of SQL,
- if you do not know SQL there are many books and tutorials on the web.
-
- Relational databases and object databases are very different and
- each possesses its own strengths and weaknesses. Zope allows you to
- use either, providing the flexibility to choose the storage
- mechanism which is best for your data. The most common reasons to
- use relational databases are to access an existing database or to
- share data with other applications. Most programming languages and
- thousands of software products work with relational
- databases. Although it is possible to access the ZODB from other
- applications and languages, it will often require more effort than
- using a relational database.
-
- By using your relational data with Zope you retain all of Zope's
- benefits including security, dynamic presentation, and
- networking. You can use Zope to dynamically tailor your data access,
- data presentation and data management.
-
- Common Relational Databases
-
- There are many relational database systems. The following is a brief
- list of some of the more popular database systems:
-
- Oracle -- Oracle is arguably the most powerful and popular
- commercial relational database. It is, however, relatively
- expensive and complex. Oracle can be purchased or evaluated from
- the "Oracle Website":http://www.oracle.com/.
-
- DB2 -- DB2 from IBM is the main commercial competitor to Oracle.
- It has similar power but also similar expense and complexity.
- More information from
- "http://www.ibm.com/software/data/db2/":http://www.ibm.com/software/data/db2/
-
- PostgreSQL -- PostgreSQL is a leading open source relational
- database with good support for SQL standards. You can
- find more information about PostgreSQL at the "PostgreSQL web
- site":http://www.postgresql.org/.
-
- MySQL -- MySQL is a fast open source relational database. You
- can find more information about MySQL at the "MySQL web
- site":http://www.mysql.com/.
-
- SAP DB -- An open source database developed by SAP. Has an Oracle 7
- compatibility mode. More information and downloads from
- "http://www.sapdb.org/":http://www.sapdb.org/.
-
- Sybase -- Sybase is another popular commercial relational database.
- Sybase can be purchased or evaluated from the
- "Sybase Website":http://www.sybase.com/.
-
- SQL Sever -- Microsoft's full featured SQL Server for the
- Windows operating systems. For any serious use on Windows, it is
- preferable to Microsoft Access. Information from
- "http://www.microsoft.com/sql/":http://www.microsoft.com/sql/
-
- Interbase -- Interbase is an open source relational database
- from Borland/Inprise. You can find more information about
- Interbase at the "Borland web
- site":http://www.borland.com/interbase/. You may also be
- interested in
- "FireBird":http://sourceforge.net/projects/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 its
- entire data set into memory. You can find out more about Gadfly
- at
- "http://gadfly.sourceforge.net/":http://gadfly.sourceforge.net/.
- Gadfly should never be used for production systems because it is
- not thread-safe and does not scale.
-
- The mechanics of setting up relational database is different for
- each database and is thus 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.
-
- Zope can connect to all the above-listed database systems; however,
- you should be satisfied that the database is running and operating
- in a satisfactory way on its own before attempting to connect it to
- Zope. An exception to this policy is Gadfly, which is included with
- Zope and requires no setup.
-
-
- Database Adapters
-
- A database can only be used if a Zope Database Adapter is available,
- though a Database Adapter is fairly easy to write if the database has
- Python support. Database adapters can be downloaded from the "Products
- section of Zope.org":http://www.zope.org/Products/ The exception to
- this is Gadfly, which is included with Zope.
-
- At the time of writing the following adapters were available, but this
- list constantly changes as more adapters are added.
-
- Oracle -- "DCOracle2":http://www.zope.org/Members/matt/dco2 package
- from Zope Corporation includes the ZoracleDA
-
- DB2 -- ZDB2DA from
- "Blue Dynamics":http://products.bluedynamics.org/Products/ZDB2DA
-
- PostgreSQL -- The newest and prefered DA is ZPsycopgDA included in
- "psycopg":http://initd.org/software/psycopg package. The older
- "ZpopyDA":http://sourceforge.net/projects/zpopyda/ is also
- available.
-
- MySQL -- "ZMySQLDA":http://www.zope.org/Members/adustman/Products/ZMySQLDA Available as source and a Linux binary package.
-
- SAP DB -- "ZsapdbDA":http://www.zope.org/Members/jack-e/ZsapdbDA by
- Ulrich Eck.
-
- Sybase -- "SybaseDA":http://www.zope.org/Products/DA/SybaseDA/ is
- written by Zope Corporation (but no longer maintained).
-
- SQLServer -- "mxODBC":http://www.egenix.com is written by Egenix
- and very well maintained. There is also
- "ZODBC DA":http://www.zope.org/Products/DA/ZODBCDA is
- written by Zope Corporation. Available
- for the Windows platform only. This DA is no longer actively
- maintainted.
-
- Interbase/Firebird -- There are a number of DAs available including
- "kinterbasdbDA":http://www.zope.org/Members/mwoj/kinterbasdbDA and
- "gvibDA":http://www.zope.org/Members/bkc/gvibDA
-
- Informix -- "ZinformixDA":http://www.zope.org/Members/mark_rees/ZInformixDA
- which requires the
- "informixdb":http://starship.python.net/crew/sturner/informixdb.html
- product.
-
- Gadfly -- The Gadfly Database Adapter is built into Zope.
-
- If you will need to connect to more than one database or wish to connect
- as to the same database as different users then you may use multiple
- database connection objects.
-
- Setting up a Database Connection
-
- Once the database adapter has been downloaded and installed you may
- create a new *Database Connection* from the *Add* menu on the Zope
- management pages. All database connection management interfaces are
- fairly similar.
-
- The database connection object is used to establish and manage the
- connection to the database. Because the database runs externally to
- Zope, they may require you to specify information necessary to
- connect successfully to the database. This specification, called a
- *connection string*, is different for each kind of database. For
- example, the figure below shows the PostgreSQL database connection
- add form.
-
- "PostgreSQL Database Connection":img:17-1:Figures/psycopg.png
-
- We'll be using the Gadfly database for the examples in this chapter,
- as it requires the least amount of configuration. If you happen to
- be using a different database while "playing along", note that
- Database Connections work slightly differently depending on which
- database is being used, however most have a "Test" tab for issuing a
- test SQL query to the database and a "Browse" tab which will show
- the table structure. It is good practice to use these tabs to test
- the database connection before going any further.
-
- Select the *Z Gadfly Database Connection* from the add list. This
- will take you to the add form for a Gadfly database connection.
- Select and add a Gadlfy connection to Zope. Note that because Gadfly
- runs inside Zope you do not need to specify a "connection string".
-
- 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 it exposes a button to connect or disconnect from the
- database. In general Zope will manage the connection to your
- database for you, so in practice there is little reason to manually
- control the connection. For Gadfly, the action of connecting and
- disconnecting is 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. The figure below shows the *Properties* view.
-
- "The Properties view":img:17-2:Figures/10-3.png
-
- 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 used for testing your database and issuing
- "one-time" SQL commands (like statements for creating tables). This
- is *not* the place where you will enter most of your SQL code. SQL
- commands typically reside in *Z SQL Methods* which will be discussed
- in detail later in this chapter.
-
- Let's create a table in your database for 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 by entering it into the
- *Test* tab::
-
- CREATE TABLE employees
- (
- emp_id integer,
- first varchar,
- last varchar,
- salary float
- )
-
- Click the *Submit Query* button of the *Test* tab to run the SQL
- command. Zope should return a confirmation screen that confirms that
- the SQL code was run. It will additionally display the results, if
- any.
-
- The SQL used here works under Gadfly but 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 you
- will need to consult your documentation to find out what kind of
- types your database supports.
-
- To examine your table, go to the *Browse* view. This lets you view
- your database's tables and the schema of each table. 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-3].
-
- "Browsing the Database Connection":img:17-3:Figures/10-4.png
-
- 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. However, 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.
-
- Z SQL Methods
-
- *Z SQL Methods* are Zope objects 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 and change
- database data. Z SQL Methods can also contain more than one SQL
- command. In detail a Z SQL method may contain multiple INSERT
- or UPDATE statements but at most one SELECT statement.
-
- A ZSQL Method has two functions: it generates SQL to send to the
- database and it converts the response from the database into an
- object. This has the following benefits:
-
- - Generated SQL will take care of special characters that may need to be
- quoted or removed from the query. This speeds up code development.
-
- - If the underlying database is changed (for example, from Postgres
- to Oracle), then the generated SQL will, in some cases,
- automatically change too, making the application more portable.
-
- - Results from the query are packaged into an easy to use object which
- will make display or processing of the response very simple.
-
- - Transactions are mediated. Transactions are discussed in more
- detail later in this chapter.
-
- Examples of ZSQL Methods
-
- 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 the figure
- below.
-
- "The Add form for Z SQL Methods":img:17-4:Figures/10-5.png
-
- 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, 'emp_id=100' gives the 'emp_id'
- 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
- (<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. 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 of this chapter.
-
- 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
- following 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 the figure below.
-
- "The hire_employee Test view":img:17-5:Figures/10-6.png
-
- 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
- *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 website.
-
- Displaying Results from 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.
-
- Somewhat predictably, as Zope is "object
- oriented":ObjectOrientation.stx, a Z SQL method returns a *Result
- object*. All the result rows are packaged up into one object. For
- all practical purposes, the result object can be thought of as rows
- in the database table that have been turned into Zope objects.
- These objects have attributes that match the schema of the database
- result.
-
- 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> Euro a year.
- </li>
- </dtml-in>
- </ul>
-
- <dtml-var standard_html_footer>
-
- and the ZPT version::
-
- <div>
- <ul>
- <li tal:repeat="row here/list_all_employees">
- <span tal:content="string:${row/id}: ${row/last} ${row/first}
- makes ${row/salary} Euro a year.
- </li>
- </ul>
- </div>
-
- 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*.
-
- Zope Database Adapters behave slightly differently regarding how
- they handle different types of data. However the more modern ones
- will return the Python type that is closest to the SQL type - as
- there are far more types in SQL than in Python there cannot be a
- complete match. For example, a date will usually be returned as a
- Zope DateTime object; char, varchar and text will all be returned as
- strings.
-
- 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.
-
- 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
- *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 the chapter entitled "Searching and Categorizing
- Content":SearchingZCatalog.stx, 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
- "hireEmployeeReport" for the *Report Id*, "hireEmployeeForm" for the
- *Search Id* and check the "Generate DTML Methods" button then 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 *hireEmployeeReport* DTML Method a little to make it more
- informative. Select the *hireEmployeeReport* 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 generated *hireEmployeeReport*
- 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.
-
- You now 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=<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>
-
- 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 <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 > 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" , '<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":AppendixA.stx 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">
-
- 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
- <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 connectivity 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 REQUEST. 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> Euro per year.</p>
- </dtml-in>
-
- <dtml-var standard_html_footer>
-
- and the ZPT version::
-
- <div>
- <tal:div tal:repeat="row python: here.employee_by_id(emp_id=42)">
- <h1 tal:content="string: ${row/last}, ${row/first}" />
- <p>
- <span tal:content="string:${row/first}s employee id is ${row/emp_id}.
- ${row/first} makes ${row/salary} Euro per year.
- </tal:div>
- </div>
-
- 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 were 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 the below figure, which shows a
- collection of Folders in a organization's website.
-
- "Folder structure of an organizational website":img:17-6:Figures/10-7.png
-
- 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 four
- arguments, *department_id*, *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
-
- ... 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 the chapter entitled "Users and
- Security":Security.stx, 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
- obtaining 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*, with *emp_id* in the 'Arguments' field and the
- following in the SQL Template::
-
- select * from employees where
- <dtml-sqltest emp_id op="eq" type="int">
-
- 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 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::
-
- <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.
-
- Other Result Object Methods
-
- Up to now we have just been iterating through the attributes of
- the Result object in DTML. The result object does however provide
- other methods which can be easier in some situations. These
- methods can be accessed from Python scripts, page templates and
- from DTML. For example in Python we could write::
-
- result=context.list_all_employees()
- return len(result)
-
- which in DTML would be::
-
- <dtml-var "_.len(list_all_employees())">
-
- and in ZPT::
-
- <span tal:content="python: len(list_all_employees())" />
-
-
- Assuming that we have set 'result' to being a result object we can
- use the following methods:
-
- 'len(result)' -- this will show the number rows returned (which
- would be 3 in the example above).
-
- 'result.names()' -- a list of all the column headings,
- returning a list containing 'emp_id', 'first', 'last' and 'salary'
-
- 'result.tuples()' -- returns a list of tuples in our example::
-
- [(43, 'Bob', 'Roberts', 50000),
- (101, 'Cheeta', 'leCat', 100000),
- (99, 'Jane', 'Junglewoman', 100001)]
-
- 'result.dictionaries()' -- will return a list of dictionaries,
- with one dictionary for each row::
-
- [{'emp_id': 42, 'first': 'Bob','last': 'Roberts', 'salary': 50000},
- {'emp_id': 101, 'first: 'Cheeta', 'last': 'leCat', 'salary': 100000},
- {'emp_id': 99, 'first': 'Jane', 'last': 'Junglewoman', 'salary': 100001}]
-
- 'result.data_dictionary()' -- returns a dictionary describing the
- structure of the results table. The dictionary has the key 'name',
- 'type', 'null' and 'width'. Name and type are self explanatory, 'null' is
- true if that field may contain a null value and width is the width in
- characters of the field. Note that 'null' and 'width' may not be set by
- some Database Adapters.
-
- 'result.asRDB()' -- displays the result in a similar way to a relational
- database. The DTML below displays the result below::
-
- <pre>
- <dtml-var "list_all_employees().asRDB()">
- </pre>
-
- ... displays ...
-
- emp_id first last salary
- 42 Bob Roberts 50000
- 101 Cheeta leCat 100000
- 99 Jane Junglewoman 100001
-
- 'result[0][1]' -- return row 0, column 1 of the result, 'bob' in this
- example. Be careful using this method as changes in the schema will
- cause unexpected results.
-
- Binding Classes to Result Objects
-
- A Result object has an attribute for each column in a results row.
- As we have seen there are some basic methods for processing these
- attributes to produce some more useful output. However we can go
- further by writing our own custom methods and adding them into the
- Result object.
-
- There are two ways to bind a method to a Result object. As you
- saw previously, 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 binding mechanism
- described in the chapter entitled "Advanced Zope
- Scripting":ScriptingZope.stx. 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 *viewEmployee* 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>
-
- 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. With a bit of Python, you could create brains
- classes that accessed network resources, called other Z SQL
- Methods, or performed all kinds of business logic. Since advanced
- Python programming is not within the scope of this book, we
- regrettably cannot provide a great number of examples of this sort
- of functionality, but we will at least provide one below.
-
- 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>
-
- 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 the figure below.
-
- "Caching controls for Z SQL Methods":img:17-7:Figures/10-8.png
-
- 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 was mentioned in the chapter entitled "Zope Concepts and
- Architecture":ZopeArchitecture.stx, 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 operation 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.
-
- In our example, the transaction would start with the customer
- submitting the form from the web page and would end when the page
- is displayed. It is guaranteed that operations in this transaction
- are either all performed or none are performed even if these
- operations use a mix of Zope Object Database and external
- relational database.
-
- Further help
-
- The zope-db at zope.org is the place to ask questions about relational
- databases. You can subscribe or browse the archive of previous postings
- at http://lists.zope.org/mailman/listinfo/zope-db
-
- 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.
Copied: zope2book/trunk/source/RelationalDatabases.rst (from rev 96426, zope2book/trunk/RelationalDatabases.stx)
===================================================================
--- zope2book/trunk/source/RelationalDatabases.rst (rev 0)
+++ zope2book/trunk/source/RelationalDatabases.rst 2009-02-10 23:43:08 UTC (rev 96432)
@@ -0,0 +1,1431 @@
+Relational Database Connectivity
+================================
+
+The Zope Object Database (ZODB) is used to store all the pages,
+files and other objects you create. It is fast and requires almost
+no setting up or maintenance. Like a filesystem, it is especially
+good at storing moderately-sized binary objects such as graphics.
+
+Relational Databases work in a very different way. They are based on
+tables of data such as this::
+
+ Row First Name Last Name Age
+ === ========== ========= ===
+ 1 Bob McBob 42
+ 2 John Johnson 24
+ 3 Steve Smith 38
+
+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. This chapter assumes a basic knowledge of SQL,
+if you do not know SQL there are many books and tutorials on the web.
+
+Relational databases and object databases are very different and
+each possesses its own strengths and weaknesses. Zope allows you to
+use either, providing the flexibility to choose the storage
+mechanism which is best for your data. The most common reasons to
+use relational databases are to access an existing database or to
+share data with other applications. Most programming languages and
+thousands of software products work with relational
+databases. Although it is possible to access the ZODB from other
+applications and languages, it will often require more effort than
+using a relational database.
+
+By using your relational data with Zope you retain all of Zope's
+benefits including security, dynamic presentation, and
+networking. You can use Zope to dynamically tailor your data access,
+data presentation and data management.
+
+Common Relational Databases
+---------------------------
+
+There are many relational database systems. The following is a brief
+list of some of the more popular database systems:
+
+Oracle
+ Oracle is arguably the most powerful and popular
+ commercial relational database. It is, however, relatively
+ expensive and complex. Oracle can be purchased or evaluated from
+ the `Oracle Website <http://www.oracle.com/>`_.
+
+DB2
+ DB2 from IBM is the main commercial competitor to Oracle.
+ It has similar power but also similar expense and complexity.
+ More information from http://www.ibm.com/software/data/db2/ .
+
+PostgreSQL
+ PostgreSQL is a leading open source relational
+ database with good support for SQL standards. You can
+ find more information about PostgreSQL at the `PostgreSQL web
+ site <http://www.postgresql.org/>`_.
+
+MySQL
+ MySQL is a fast open source relational database. You
+ can find more information about MySQL at the `MySQL web
+ site <http://www.mysql.com/>`_.
+
+SAP DB
+ An open source database developed by SAP. Has an Oracle 7
+ compatibility mode. More information and downloads from
+ http://www.sapdb.org/ .
+
+Sybase
+ Sybase is another popular commercial relational database.
+ Sybase can be purchased or evaluated from the
+ `Sybase Website <http://www.sybase.com/>`_.
+
+SQL Server
+ Microsoft's full featured SQL Server for the
+ Windows operating systems. For any serious use on Windows, it is
+ preferable to Microsoft Access. Information from
+ http://www.microsoft.com/sql/
+
+Interbase
+ Interbase is an open source relational database
+ from Borland/Inprise. You can find more information about
+ Interbase at the `Borland web site <http://www.borland.com/interbase/>`_.
+ You may also be interested in `FireBird
+ <http://sourceforge.net/projects/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 its
+ entire data set into memory. You can find out more about Gadfly
+ at http://gadfly.sourceforge.net/.
+ Gadfly should never be used for production systems because it is
+ not thread-safe and does not scale.
+
+The mechanics of setting up relational database is different for
+each database and is thus 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.
+
+Zope can connect to all the above-listed database systems; however,
+you should be satisfied that the database is running and operating
+in a satisfactory way on its own before attempting to connect it to
+Zope. An exception to this policy is Gadfly, which is included with
+Zope and requires no setup.
+
+
+Database Adapters
+-----------------
+
+A database can only be used if a Zope Database Adapter is available,
+though a Database Adapter is fairly easy to write if the database has
+Python support. Database adapters can be downloaded from the `Products
+section of Zope.org <http://www.zope.org/Products/>`_ The exception to
+this is Gadfly, which is included with Zope.
+
+At the time of writing the following adapters were available, but this
+list constantly changes as more adapters are added.
+
+Oracle
+ `DCOracle2 <http://www.zope.org/Members/matt/dco2>`_ package
+ from Zope Corporation includes the ZoracleDA
+
+DB2
+ ZDB2DA from `Blue Dynamics
+ <http://products.bluedynamics.org/Products/ZDB2DA>`_
+
+PostgreSQL
+ The newest and prefered DA is ZPsycopgDA included in
+ `psycopg <http://initd.org/software/psycopg package>`_. The older
+ `ZpopyDA <http://sourceforge.net/projects/zpopyda/>`_ is also
+ available.
+
+MySQL
+ `ZMySQLDA <http://www.zope.org/Members/adustman/Products/ZMySQLDA>`_
+ Available as source and a Linux binary package.
+
+SAP DB
+ `ZsapdbDA <http://www.zope.org/Members/jack-e/ZsapdbDA>`_ by Ulrich Eck.
+
+Sybase
+ `SybaseDA <http://www.zope.org/Products/DA/SybaseDA/>`_ is
+ written by Zope Corporation (but no longer maintained).
+
+SQLServer
+ `mxODBC <http://www.egenix.com>`_ is written by Egenix
+ and very well maintained. There is also
+ `ZODBC DA <http://www.zope.org/Products/DA/ZODBCDA>`_ is
+ written by Zope Corporation. Available
+ for the Windows platform only. This DA is no longer actively
+ maintainted.
+
+Interbase/Firebird
+ There are a number of DAs available including
+ `kinterbasdbDA <http://www.zope.org/Members/mwoj/kinterbasdbDA>`_ and
+ `gvibDA <http://www.zope.org/Members/bkc/gvibDA>`_.
+
+Informix
+ `ZinformixDA <http://www.zope.org/Members/mark_rees/ZInformixDA>`_
+ which requires the
+ `informixdb <http://starship.python.net/crew/sturner/informixdb.html>`_
+ package.
+
+Gadfly
+ The Gadfly Database Adapter is built into Zope.
+
+If you will need to connect to more than one database or wish to connect
+as to the same database as different users then you may use multiple
+database connection objects.
+
+Setting up a Database Connection
+--------------------------------
+
+Once the database adapter has been downloaded and installed you may
+create a new *Database Connection* from the *Add* menu on the Zope
+management pages. All database connection management interfaces are
+fairly similar.
+
+The database connection object is used to establish and manage the
+connection to the database. Because the database runs externally to
+Zope, they may require you to specify information necessary to
+connect successfully to the database. This specification, called a
+*connection string*, is different for each kind of database. For
+example, the figure below shows the PostgreSQL database connection
+add form.
+
+.. figure:: ../Figures/psycopg.png
+
+ PostgreSQL Database Connection
+
+We'll be using the Gadfly database for the examples in this chapter,
+as it requires the least amount of configuration. If you happen to
+be using a different database while "playing along", note that
+Database Connections work slightly differently depending on which
+database is being used, however most have a "Test" tab for issuing a
+test SQL query to the database and a "Browse" tab which will show
+the table structure. It is good practice to use these tabs to test
+the database connection before going any further.
+
+Select the *Z Gadfly Database Connection* from the add list. This
+will take you to the add form for a Gadfly database connection.
+Select and add a Gadlfy connection to Zope. Note that because Gadfly
+runs inside Zope you do not need to specify a "connection string".
+
+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 it exposes a button to connect or disconnect from the
+database. In general Zope will manage the connection to your
+database for you, so in practice there is little reason to manually
+control the connection. For Gadfly, the action of connecting and
+disconnecting is 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. The figure below shows the *Properties* view.
+
+.. figure:: ../Figures/10-3.png
+
+ 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 used for testing your database and issuing
+"one-time" SQL commands (like statements for creating tables). This
+is *not* the place where you will enter most of your SQL code. SQL
+commands typically reside in *Z SQL Methods* which will be discussed
+in detail later in this chapter.
+
+Let's create a table in your database for 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 by entering it into the
+*Test* tab::
+
+ CREATE TABLE employees
+ (
+ emp_id integer,
+ first varchar,
+ last varchar,
+ salary float
+ )
+
+Click the *Submit Query* button of the *Test* tab to run the SQL
+command. Zope should return a confirmation screen that confirms that
+the SQL code was run. It will additionally display the results, if
+any.
+
+The SQL used here works under Gadfly but 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 you
+will need to consult your documentation to find out what kind of
+types your database supports.
+
+To examine your table, go to the *Browse* view. This lets you view
+your database's tables and the schema of each table. 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-3].
+
+.. figure:: ../Figures/10-4.png
+
+ 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. However, 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.
+
+Z SQL Methods
+-------------
+
+*Z SQL Methods* are Zope objects 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 and change
+database data. Z SQL Methods can also contain more than one SQL
+command. In detail a Z SQL method may contain multiple INSERT
+or UPDATE statements but at most one SELECT statement.
+
+A ZSQL Method has two functions: it generates SQL to send to the
+database and it converts the response from the database into an
+object. This has the following benefits:
+
+- Generated SQL will take care of special characters that may need to be
+ quoted or removed from the query. This speeds up code development.
+
+- If the underlying database is changed (for example, from Postgres
+ to Oracle), then the generated SQL will, in some cases,
+ automatically change too, making the application more portable.
+
+- Results from the query are packaged into an easy to use object which
+ will make display or processing of the response very simple.
+
+- Transactions are mediated. Transactions are discussed in more
+ detail later in this chapter.
+
+Examples of ZSQL Methods
+-------------------------
+
+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 the figure
+below.
+
+.. figure:: ../Figures/10-5.png
+
+ 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, 'emp_id=100' gives the 'emp_id'
+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
+ (<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. 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 of this chapter.
+
+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
+following 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 the figure below.
+
+.. figure:: ../Figures/10-6.png
+
+ 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
+*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 website.
+
+Displaying Results from 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.
+
+Somewhat predictably, as Zope is `object oriented
+<ObjectOrientation.html>`_, a Z SQL method returns a *Result object*. All
+the result rows are packaged up into one object. For all practical
+purposes, the result object can be thought of as rows in the database table
+that have been turned into Zope objects. These objects have attributes
+that match the schema of the database result.
+
+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> Euro a year.
+ </li>
+ </dtml-in>
+ </ul>
+
+ <dtml-var standard_html_footer>
+
+and the ZPT version::
+
+ <div>
+ <ul>
+ <li tal:repeat="row here/list_all_employees">
+ <span tal:content="string:${row/id}: ${row/last} ${row/first}
+ makes ${row/salary} Euro a year.
+ </li>
+ </ul>
+ </div>
+
+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*.
+
+Zope Database Adapters behave slightly differently regarding how
+they handle different types of data. However the more modern ones
+will return the Python type that is closest to the SQL type - as
+there are far more types in SQL than in Python there cannot be a
+complete match. For example, a date will usually be returned as a
+Zope DateTime object; char, varchar and text will all be returned as
+strings.
+
+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.
+
+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
+*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 the chapter entitled `Searching and Categorizing
+Content <SearchingZCatalog.html>`_, 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
+"hireEmployeeReport" for the *Report Id*, "hireEmployeeForm" for the
+*Search Id* and check the "Generate DTML Methods" button then 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 *hireEmployeeReport* DTML Method a little to make it more
+informative. Select the *hireEmployeeReport* 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 generated *hireEmployeeReport*
+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.
+
+You now 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=<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>
+
+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 <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 > 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" , '<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 <AppendixA.html>`_ 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">
+
+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
+ <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 connectivity 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 REQUEST. 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> Euro per year.</p>
+ </dtml-in>
+
+ <dtml-var standard_html_footer>
+
+and the ZPT version::
+
+ <div>
+ <tal:div tal:repeat="row python: here.employee_by_id(emp_id=42)">
+ <h1 tal:content="string: ${row/last}, ${row/first}" />
+ <p>
+ <span tal:content="string:${row/first}s employee id is ${row/emp_id}.
+ ${row/first} makes ${row/salary} Euro per year.
+ </tal:div>
+ </div>
+
+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 were 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 the below figure, which shows a
+collection of Folders in a organization's website.
+
+.. figure:: ../Figures/10-7.png
+
+ Folder structure of an organizational website
+
+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 four
+arguments, *department_id*, *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
+
+... 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 the chapter entitled `Users and
+Security <Security.html>`_, 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
+obtaining 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*, with *emp_id* in the 'Arguments' field and the
+following in the SQL Template::
+
+ select * from employees where
+ <dtml-sqltest emp_id op="eq" type="int">
+
+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 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::
+
+ <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.
+
+Other Result Object Methods
+~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+Up to now we have just been iterating through the attributes of
+the Result object in DTML. The result object does however provide
+other methods which can be easier in some situations. These
+methods can be accessed from Python scripts, page templates and
+from DTML. For example in Python we could write::
+
+ result=context.list_all_employees()
+ return len(result)
+
+which in DTML would be::
+
+ <dtml-var "_.len(list_all_employees())">
+
+and in ZPT::
+
+ <span tal:content="python: len(list_all_employees())" />
+
+
+Assuming that we have set 'result' to being a result object we can
+use the following methods:
+
+'len(result)'
+ this will show the number rows returned (which would be 3 in the example
+ above).
+
+'result.names()'
+ a list of all the column headings, returning a list containing 'emp_id',
+ 'first', 'last' and 'salary'
+
+'result.tuples()'
+ returns a list of tuples in our example::
+
+ [(43, 'Bob', 'Roberts', 50000),
+ (101, 'Cheeta', 'leCat', 100000),
+ (99, 'Jane', 'Junglewoman', 100001)]
+
+'result.dictionaries()'
+ will return a list of dictionaries, with one dictionary for each row::
+
+ [{'emp_id': 42, 'first': 'Bob','last': 'Roberts', 'salary': 50000},
+ {'emp_id': 101, 'first: 'Cheeta', 'last': 'leCat', 'salary': 100000},
+ {'emp_id': 99, 'first': 'Jane', 'last': 'Junglewoman', 'salary': 100001}]
+
+'result.data_dictionary()'
+ returns a dictionary describing the structure of the results table. The
+ dictionary has the key 'name', 'type', 'null' and 'width'. Name and type
+ are self explanatory, 'null' is true if that field may contain a null
+ value and width is the width in characters of the field. Note that 'null'
+ and 'width' may not be set by some Database Adapters.
+
+'result.asRDB()'
+ displays the result in a similar way to a relational database. The DTML
+ below displays the result below::
+
+ <pre>
+ <dtml-var "list_all_employees().asRDB()">
+ </pre>
+
+ ... displays ...
+
+ emp_id first last salary
+ 42 Bob Roberts 50000
+ 101 Cheeta leCat 100000
+ 99 Jane Junglewoman 100001
+
+'result[0][1]'
+ return row 0, column 1 of the result, 'bob' in this example. Be careful
+ using this method as changes in the schema will cause unexpected results.
+
+Binding Classes to Result Objects
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+A Result object has an attribute for each column in a results row.
+As we have seen there are some basic methods for processing these
+attributes to produce some more useful output. However we can go
+further by writing our own custom methods and adding them into the
+Result object.
+
+There are two ways to bind a method to a Result object. As you
+saw previously, 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 binding mechanism
+described in the chapter entitled `Advanced Zope
+Scripting <ScriptingZope.html>`_. 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 *viewEmployee* 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>
+
+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. With a bit of Python, you could create brains
+classes that accessed network resources, called other Z SQL
+Methods, or performed all kinds of business logic. Since advanced
+Python programming is not within the scope of this book, we
+regrettably cannot provide a great number of examples of this sort
+of functionality, but we will at least provide one below.
+
+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>
+
+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 the figure below.
+
+.. figure:: ../Figures/10-8.png
+
+ 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 was mentioned in the chapter entitled `Zope Concepts and
+Architecture <ZopeArchitecture.html>`_, 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 operation 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.
+
+In our example, the transaction would start with the customer
+submitting the form from the web page and would end when the page
+is displayed. It is guaranteed that operations in this transaction
+are either all performed or none are performed even if these
+operations use a mix of Zope Object Database and external
+relational database.
+
+Further help
+------------
+
+The zope-db at zope.org is the place to ask questions about relational
+databases. You can subscribe or browse the archive of previous postings
+at http://lists.zope.org/mailman/listinfo/zope-db
+
+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.
Modified: zope2book/trunk/source/index.rst
===================================================================
--- zope2book/trunk/source/index.rst 2009-02-10 23:26:53 UTC (rev 96431)
+++ zope2book/trunk/source/index.rst 2009-02-10 23:43:08 UTC (rev 96432)
@@ -28,6 +28,7 @@
ScriptingZope.rst
ZopeServices.rst
SearchingZCatalog.rst
+ RelationalDatabases.rst
AppendixA.rst
Contributions.rst
More information about the Checkins
mailing list