Zope And MySQL - Of Methods And Madness (
Page 9 of 9 )
The final item on the agenda involves
building an interface and methods to edit existing records in the database table.
Again, I'll hark back to the "list" object created right at the beginning. You'll
remember that this "list" object also contained a link to an "edit" object, which
was passed a record ID (in much the same way as the "delete" object discussed
on the previous page).
<font size="-1"><a href="edit?id=<dtml-var id>">edit</a> | <a
href="delete?id=<dtml-var
id>">delete</a></font>
Let's look at this "edit" object in detail:
<dtml-var standard_html_header>
<dtml-if submit>
<dtml-call updateMethod>
<h2>Item
edited!</h2>
<p>
<a href="list">View the entire collection</a>
or
<a href="add">add
another title</a>
<dtml-else>
<h2>Edit DVD</h2>
<dtml-in
selectOneMethod>
<form action="edit" method="POST">
<table border=0>
<tr>
<td>Title</td>
<td><input
name="title" width=30 value="<dtml-var name="title">"></td>
</tr>
<tr>
<td>Director</td>
<td><input
name="director" width=30 value="<dtml-var
name="director">"></td> </tr>
<tr>
<td>Cast</td>
<td><input
name="cast" width=30 value="<dtml-var name="cast">"></td>
</tr>
<tr>
<td>Genre</td>
<td><input
name="genre" width=30 value="<dtml-var name="genre">"></td>
</tr>
<tr>
<td
colspan=2 align=center>
<input type="hidden" name="id" value=<dtml-var
name="id">>
<input
type="submit" name="submit" value="Edit DVD"> </td>
</tr>
</table>
</form>
</dtml-in>
</dtml-if>
<dtml-var
standard_html_footer>
Again, this consists of both a form and a form processor, separated from each
other by a DTML "if" test and the "submit" variable. Let's look at the form first:
<dtml-if submit>
// snip
<dtml-else>
<h2>Edit DVD</h2>
<dtml-in
selectOneMethod>
<form action="edit" method="POST">
<table border=0>
<tr>
<td>Title</td>
<td><input
name="title" width=30 value="<dtml-var name="title">"></td>
</tr>
<tr>
<td>Director</td>
<td><input
name="director" width=30 value="<dtml-var
name="director">"></td> </tr>
<tr>
<td>Cast</td>
<td><input
name="cast" width=30 value="<dtml-var name="cast">"></td>
</tr>
<tr>
<td>Genre</td>
<td><input
name="genre" width=30 value="<dtml-var name="genre">"></td>
</tr>
<tr>
<td
colspan=2 align=center>
<input type="hidden" name="id" value=<dtml-var
name="id">>
<input
type="submit" name="submit" value="Edit DVD"> </td>
</tr>
</table>
</form>
</dtml-in>
</dtml-if>
The first thing this form does is call the Z SQL Method named "selectOneMethod".
This Z SQL Method is similar to the "selectAllMethod" discussed a few pages back,
except that it includes an additional modifier - a record ID - in order to return
a single record from the table rather than a list of all available records. Here's
what it looks like:
SELECT * FROM dvd WHERE id = <dtml-sqlvar id type="int">
The record ID, obviously, gets passed to this method as an argument from the
"list" object.
After the Z SQL method has been invoked, the fields in the resulting record set
are converted into DTML variables and used in the form, via the <dtml-var>
construct, to pre-fill the various form fields. Here's what it looks like:
Notice, from the DTML code above, that the record ID is again passed forward
to the form processor via a hidden field in the form.
<input type="hidden" name="id" value=<dtml-var name="id">>
Once this form is submitted, the form processor takes over.
<dtml-if submit>
<dtml-call updateMethod>
<h2>Item edited!</h2>
<p>
<a
href="list">View the entire collection</a> or <a href="add">add
another
title</a>
<dtml-else>
// snip
</dtml-if>
As you can see, the form processor invokes a Z SQL Method, "updateMethod", to
update the database with the new information for the selected record. Here's what
"updateMethod" looks like:
UPDATE dvd SET title=<dtml-sqlvar title type="string">,
director=<dtml-sqlvar
director type="string">, cast=<dtml-sqlvar cast
type="string">, genre=<dtml-sqlvar
genre type="string"> WHERE id =
<dtml-sqlvar id type="int">
As with the original "insertMethod", the UPDATE query above is dynamically constructed
on the basis of form input variables.
Once the Z SQL Method is successfully executed, the resulting output looks like
this:
And you're done! You now have a Zope interface to add, edit and delete records
from a MySQL database. Wasn't all that hard, was it?{mospagebreak title=An Object
Lesson} If you've been paying attention, the process of communicating with MySQL
through Zope should be fairly clear. Assuming a working database connection, there
are two basic things you need: Z SQL Methods to execute queries (these Z SQL Methods
can be passed arguments so that queries can be constructed dynamically, as demonstrated
in this article) and DTML Methods to invoke the Z SQL Methods (and pass them arguments,
where required).
If you're used to PHP or Perl, this hard separation between methods and their
invocation may be somewhat difficult to grasp at first. However, if you persist,
you'll soon find that Zope's object-based approach to SQL is actually preferable
to the looser approach in those languages. By encapsulating specific SQL functionality
into objects, Zope immediately allows reuse of those objects - and, by implication,
their specific functionality - across a Web site; this, in turn, makes it possible
to package complex queries into a single object and invoke it wherever required,
as many times as required.
One of the most obvious advantages of this approach becomes visible when you
need to perform a series of related queries - for example, insert a record into
table A, retrieve the ID of the inserted record, use that ID as a foreign key
when inserting a record into Table B, and so on - multiple times. Zope makes it
possible to create a generic Z SQL Method that accepts a series of arguments and
performs - internally - as many queries as are needed to achieve the desired result.
A user of this Z SQL Method can invoke it transparently, as many times as required,
blissfully unaware of the activities that take place within it. Take it one step
further: if changes are required, they can take place within the Z SQL Method,
requiring no changes at all to the DTML Methods that invoke the object; this obviously
makes maintenance easier.
Anyway, that's about all I have time for. I hope you found this article interesting,
and that it gave you some insight into how Zope can be connected to external database
systems. In case you need more information, these links should offer you a starting
point:
A discussion of the ZODB versus a regular RDBMS, at
http://www.zope.org/Members/anthony/sql_vs_ZODB
Zope-MySQL software, at
http://sourceforge.net/projects/mysql-python
Zope-MySQL installation instructions, at
http://www.zope.org/Members/alanpog/zmysqlda_steps
The official Zope Web site, at
http://www.zope.org/
Zope documentation, at
http://www.zope.org/Documentation
Till next time...stay healthy! Note: All examples in this article have been tested
on Linux/i586 with Zope 2.5. Examples are illustrative only, and are not meant
for a production environment. Melonfire provides no warranties or support for
the source code described in this article. YMMV!
