First upload: 15.10.2007, Marc Petitmermet
Last modification: 15.10.2007, Marc Petitmermet
- added more footnotes
- added graphic
26.03.2008, Marc Petitmermet
- added some more details
- added "Please choose..." option
I was developing a new simple web application with a mysql database in the back end. The database has some ENUM and SET filed types and I wanted to have different views of the data on Silva pages based on these fields. For example: There is an ENUM field with
color = 'red', 'green', 'yellow'
in the database "items" and I want a Silva page where only those rows are shown where the color is 'red' or 'yellow'. To achieve this I created a Silva Code Source [1] with the parameter "color" which is of type MultiCheckBoxField. The field of this MultiCheckBoxField includes the above values.
As the database evolves over time the ENUM and SET fields gets expanded, e.g. more colors are added. Being lazy and forgetful I did not want to update the items of the parameters in the Silva Code Source by hand; the items list should be populated automatically by looking up the mysql database. And here's how to do it.
It's quite easy to populate list items in forms using some python scripts [2] and the Silva API [3]. The form expects a list like the following:
[('red', 'red'), ('green', 'green'), ('yellow', 'yellow')]
Instead of using the Silva API we need just some zsql methods and python scripts to get it directly from the mysql back end.
mysql has a nice command called "describe" which, well, describes a table. But describe also returns the description of a single column when the column name is given as well; e.g. the column "color" in the database "items":
mysql> describe items color;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| type | enum('red','green','yellow') | NO | | red | |
+-------+------------------------------+------+-----+---------+-------+
So we write a single line zsql-method with one parameter where we pass the mysql column name:
## Z SQL Method "sql_getDescription" ##Arguments: field DESCRIBE items <dtml-sqlvar field type="string">
This zsql method returns more or less the same as the mysql console does.
The output of the zsql-method cannot yet used for populating list items in forms because the format is not correct yet. Therefore, we need a python script that modifies the output so that the form can directly read it [4].
## Script (Python) "getEnumSetList"
##bind container=container
##bind context=context
##bind namespace=
##bind script=script
##bind subpath=traverse_subpath
##parameters=parameter
##title=
##
values = context.sql_getDescription(field=parameter)[0][1]
values = values.split('(')[1]
values = values[1:-2]
values = values.split("','")
list = []
list.extend([('Please choose...','')])
list.extend([(value,value) for value in values])
return list
The above returns the required list for the form; e.g.:
[('red', 'red'), ('green', 'green'), ('yellow', 'yellow')]
The getEnumSetList script is now called in the TALES tab of the MultiCheckBoxField paramter "color" like this:
python:form.getEnumSetList('color')
Whenever the external source in a silva document, in this case a Silva Code Source, is reloaded a query is sent to the mysql database and the shown check boxes are being updated. Very Nice!
In the final step we want to present all items of colors red or yellow in a silva document. Therefore, we include our Silva Code Source into a Silva Document and check the boxes for red and yellow. We need to pass these parameters to another zsql method which selects the items we want from the database:
## Z SQL Method "sql_getSelection" ##Arguments: color SELECT * FROM items WHERE color IN (<dtml-var color>)
Please note that we are using "dtml-var" and not "dtml-sqlvar". "dtml-var" takes the input as it is given without modifications (beware of sql injections!) whereas "dtml-sqlvar" does escaping and adds additional protection by restricting the input data type.
In the page template layout we read the color parameter, cut off the brackets and pass it to the sql_getSelection query and present the data in a table:
<div metal:define-macro="table"
lang="en"
xml:lang="en"
xmlns="http://www.w3.org/1999/xhtml"
xmlns:metal="http://xml.zope.org/namespaces/metal"
xmlns:tal="http://xml.zope.org/namespaces/tal"
tal:define="
view here;
color python:str(options['color'])[1:-1];
table python:here.sql_getSelection(color=color)">
<table
tal:attributes="class string:silvatable list"
tal:condition="table">
<tbody>
<tal:block tal:repeat="row table">
[snip]
</tal:block>
</tbody>
</table>
</div>
The figure below summarizes how everything is interconnected.
The above How-to can probably be used with other parameter types as well; I've used it so far with MultiCheckBoxField and ListField.