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!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
