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
- type — the type of this database. In
this case it is a MySQL database. (At present this is the only value and will be
extended in future versions.
- host — the host where the database
server is running.
- user — the database login
user.
- password — the password associated
with this user.
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
- show-nr-of-rows — is a flag to
determine whether the number of rows found is output with the result (true or
false).
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
- type — the type of this database. In this case it is a MySQL
database. (At present this is the only value and will be extended in future versions.
- host — the host where the database server is
running.
- user — the database login user.
- password — the password associated with this
user.
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
- show-nr-of-rows — is a flag to determine whether the number
of rows found is output with the result (true or false).
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:
- name — the name of this query which will be used to tag the
returned data,
- database — the name of the database being
queried.
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.