Move your ZSQL methods to the filesystem

by Troy Davis — last modified Dec 30, 2008 03:03 PM

Make your Plone/Zope products more maintainable by storing ZSQL methods in your product's skins directory.

There are numerous reason why you should store zsql methods in the FS instead of keeping them in the ZMI. A thorough list of these can be found in Joel Burton's howto "Working off the file system".

But this howto will focus on ZSQL methods. If you haven't done so already, please be sure to setup a ZSQL connection for your database. My system uses MySQL, but I gather that other databases are possible. Here's what we'll do to get this working:

Write and TEST your SQL statement
Prepare a .zsql file with your statement in it
Add code to your page templates to use the zsql method

Detail:

Step 1 should be fairly obvious, you're going to need syntactically valid SQL to make this work, so write a query and test it before proceeding.

Next, setup your .zsql file. Here's a sample:

<dtml-comment>
   title:List Items in a Table
   arguments:
   connection_id:your_zsql_conn_id
   max_rows:25
   max_cache:300
   cache_time:0
   class_name:
   class_file:
</dtml-comment>
select * from your_table

Replace the title, connection_id and sql statement with appropriate information for your use case. Save this file in your skins folder with a file name like: list_your_table.zsql

For the last step, add some tal expressions to your page template like so:

<table border="1">
   <tr>
      <th>Column 1</th>
      <th>Column 2</th>
      <th>Column 3</th>
   </tr>
   <tr tal:repeat="my_row here/list_your_table">
      <td><span tal:content="my_row/col1">Value 1</span></td>
      <td><span tal:content="my_row/col2">Value 2</span></td>
      <td><span tal:content="my_row/col3">Value 3</span></td>
   </tr>
</table>

Voila! Now you can keep SQL statements with your product, where they belong.

Warning

Move your ZSQL methods to the filesystem

Detail:

Contribute