SQLTransformer

Warning
Note that this does not match the Cocoon method 100%. There are important differences that are discussed in below.

SQL Transformers provide an interface between Paloose and SQL-savvy database engines. They take a query and return the results of that query to the pipeline (or a suitable error message). A typical declaration of the SQLTransformer component would be:

<map:transformers default="xslt"> <map:transformer name="mysql" src="resource://lib/transforming/SQLTransformer"> <map:parameter name="type" value="mysql"/> <map:parameter name="host" value="localhost:3306"/> <map:parameter name="user" value="root"/> <map:parameter name="password" value="*******"/> </map:transformer> <map:transformer name="xslt" src="resource://lib/transforming/TRAXTransformer"> <map:use-request-parameters>true</map:use-request-parameters> </map:transformer> </map:transformers>

where

The pipeline would then be:

<map:pipeline> <map:match pattern="**.html"> <map:generate src="context://content/{1}.xml" label="xml-content"/> <map:transform type="mysql" label="sql-transform"> <map:parameter name="show-nr-of-rows" value="true"/> </map:transform> ... </map:match>

where

Errors

Errors from the database engine are reported in the following typical fashion

<page:content xmlns:default="http://apache.org/cocoon/SQL/2.0" xmlns:t="http://www.hsfr.org.uk/Schema/Text"> <t:heading level="1">SQL Transform Test</t:heading> <default:sql-error xmlns="http://apache.org/cocoon/SQL/2.0"> <default:host>localhost:3306</default:host> <default:user>root</default:user> <default:password></default:password> <default:message>SQL query error: => query: select * from composer </default:message> </default:sql-error> </page:content>

Using the SQLTransformer

Warning
Note that this does not match the Cocoon method 100%. There are important differences that are indicated below.

The SQL Transformer provides a link between the sitemap and user's site and a database using SQL queries. For this example I am going to assume the following database on a MySQL database on a local machine being accessed by user "root". The database has the following form:

mysql> use music; Database changed mysql> describe composer; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | name | varchar(40) | YES | | NULL | | | forenames | varchar(40) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select * from composer; +------------+------------------+------------+------------+ | name | forenames | birth | death | +------------+------------------+------------+------------+ | Mozart | Wolfgang Amadeus | 1756-01-27 | 1791-12-05 | | Beethoven | Ludvig van | 1770-12-15 | 1827-03-26 | | Bach | Johann Sebastian | 1685-03-21 | 1750-07-28 | | Bach | Johann Christian | 1735-09-05 | 1782-01-01 | | Haydn | Franz Joseph | 1732-03-31 | 1809-05-31 | | Bernstein | Leonard | 1918-08-25 | 1990-10-14 | | Boccherini | Luigi | 1743-02-19 | 1805-05-28 | | Ravel | Joseph Maurice | 1875-03-07 | 1937-12-28 | +------------+------------------+------------+------------+ 8 rows in set (0.00 sec) mysql>

Very simple but it will suffice.

Sitemap

The root sitemap needs to have the SQL Transformer declared as a component:

<map:components> <map:transformers default="xslt"> <map:transformer name="mysql" src="resource://lib/transforming/SQLTransformer"> <map:parameter name="type" value="mysql"/> <map:parameter name="host" value="localhost:3306"/> <map:parameter name="user" value="root"/> <map:parameter name="password" value="xxxxxxx"/> </map:transformer> ... </map:transformers>

where

The sitemap that we will use for the following examples has a pipeline:

<map:match pattern="**.html"> <map:generate src="context://content/{1}.xml" label="xml-content"/> <map:transform type="mysql" label="sql-transform"> <map:parameter name="show-nr-of-rows" value="true"/> </map:transform> ... </map:match>

where

A simple query

Say we wished to get a list of all composers named "Bach" and output their details. First of all we need to form a XML query in the input file. This has the general form:

<execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> <query> <!-- The SQL query statement --> </query> </execute-query>

All very simple. So a real example to query the composers would be (using the samme XML form as these pages):

<page:content> <t:heading level="1">SQL Transform Test</t:heading> <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> <query name="displayAllBach" database="music"> select * from composer where name = "Bach" </query> </execute-query> </page:content>

where the attributes of <query> are:

Warning

Note that the quary attributes have change after Version 1.3.5. The old name attribute is now the database attribute

The results are displayed in the form of each row that matches the criteria:

<page:content xmlns:default="http://apache.org/cocoon/SQL/2.0" <t:heading level="1">SQL Transform Test</t:heading> <default:row-set nrofrows="2" name="displayAllBach" xmlns="http://apache.org/cocoon/SQL/2.0"> <default:row> <default:name>Bach</default:name> <default:forenames>Johann Sebastian</default:forenames> <default:birth>1685-03-21</default:birth> <default:death>1750-07-28</default:death> </default:row> <default:row> <default:name>Bach</default:name> <default:forenames>Johann Christian</default:forenames> <default:birth>1735-09-05</default:birth> <default:death>1782-01-01</default:death> </default:row> </default:row-set> </page:content>

It is then up to the user to provide the correct XSL transform to process this information.

A simple query with substitution.

It is sometimes useful to have information put into the query at run time. For example a user name from login, or possibly a selection critera from the query request string in the URL. For example taking the query above say we wanted to select the composer name from the query, we would present the query as:

http://localhost/...?composer=Bach

and rewrite the XML file as

<page:content> <t:heading level="1">SQL Transform Test</t:heading> <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> <query name="displayQuery" database="music"> select * from composer where name = "{request-param:composer}" </query> </execute-query> </page:content>

which would give the same result as the first example. It is also possible to input information from the sitemap:

<map:match pattern="**.html"> <map:generate src="context://content/{1}.xml" label="xml-content"/> <map:transform type="mysql" label="sql-transform"> <map:parameter name="show-nr-of-rows" value="true"/> <map:parameter name="composer" value="Bach"/> </map:transform> <map:transform src="context://resources/transforms/xml2xhtml.xsl"/> <map:serialize type="html"/> </map:match>

with a query as follows:

<page:content> <t:heading level="1">SQL Transform Test</t:heading> <execute-query xmlns="http://apache.org/cocoon/SQL/2.0"> <query name="displayQuery" database="music"> select * from composer where name = "{param:composer}" </query> </execute-query> </page:content>
Warning
Note that Paloose does not follow the Cocoon scheme. The latter uses an embedded tag structure. I may change this in future if it proves to be a problem.
Copyright 2006 – 2023 Hugh Field-Richards. All Rights Reserved.