Displaying All Composers
The XML File
The XML file contains the embedded SQL command for getting all the data:
displayComposers.html
<page:content>
<t:heading level="1">SQL Display All Results</t:heading>
<execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
<query name="displayAll" database="composers">select * from composer</query>
</execute-query>
<t:p><link:link type="uri" ref="/examples/sql/sql.html">Return to SQL index</link:link></t:p>
<t:p><link:link type="uri" ref="/examples/sql/logout.html">Logout</link:link> from admin pages.</t:p>
</page:content>
Note that only the content part of XML page is shown for brevities sake.
Extending the Sitemap
Only a single matcher in the sitemap is need for this, together with a declaration of the necessary transforms:
sitemap.xmap
<map:transformers default="xslt">
<map:transformer name="xslt" src="resource://lib/transforming/TRAXTransformer" cachable="no">
<map:use-request-parameters>true</map:use-request-parameters>
</map:transformer>
<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="hsfr"/>
<map:parameter name="password" value="xxxxxxxx"/>
</map:transformer>
</map:transformers>
...
<map:pipeline>
<map:match pattern="sql.html">
<map:aggregate element="root" label="aggr-content">
<map:part src="cocoon:/menus.xml" element="menus" strip-root="true"/>
<map:part src="cocoon:/sql.xml" element="content" strip-root="true"/>
</map:aggregate>
<map:call resource="outputPage"/>
</map:match>
<map:match pattern="displayAllComposers.html">
<map:aggregate element="root" label="aggr-content">
<map:part src="cocoon:/menus.xml" element="menus" strip-root="true"/>
<map:part src="cocoon:/displayComposers.xml" element="content" strip-root="true"/>
</map:aggregate>
<map:transform type="mysql" label="sql-content">
<map:parameter name="show-nr-of-rows" value="true"/>
</map:transform>
<map:transform src="context://resources/transforms/sql2xml.xsl" label="sql-transform"/>
<map:call resource="outputPage"/>
</map:match>
</map:pipeline>
Returned Data
After the mysql transformer the document is:
<page:content>
<t:heading level="1">SQL Display All Results</t:heading>
<default:row-set nrofrows="3" name="displayAll" xmlns="http://apache.org/cocoon/SQL/2.0">
<default:row>
<default:id>16</default:id>
<default:name>Dvořák</default:name>
<default:forenames>Antonin</default:forenames>
<default:birth>1841-09-08</default:birth>
<default:death>1904-05-01</default:death>
</default:row>
<default:row>
<default:id>15 </default:id>
<default:name>Mozart</default:name>
<default:forenames>Wolfgang Amadeus</default:forenames>
<default:birth>1756-01-27</default:birth>
<default:death>1791-12-05</default:death>
</default:row>
<default:row>
<default:id>13</default:id>
<default:name>Barber</default:name>
<default:forenames>Samuel</default:forenames>
<default:birth>1910-03-09</default:birth>
<default:death>1981-01-23</default:death>
</default:row>
</default:row-set>
<t:p>
<link:link type="uri" ref="/examples/sql/sql.html">Return to SQL index</link:link>
</t:p>
<t:p>
<link:link type="uri" ref="/examples/sql/logout.html">Logout</link:link> from admin pages.
</t:p>
</page:content>
Processing Data
To process this there is a transformer to change to the XML that the outputPage requires:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:list="http://www.hsfr.org.uk/Schema/List"
xmlns:t="http://www.hsfr.org.uk/Schema/Text"
xmlns:sql="http://apache.org/cocoon/SQL/2.0"
version="1.0">
<xsl:template match="sql:row-set[ @name = 'displayAll' ]">
<xsl:element name="list:list">
<xsl:attribute name="type">unordered</xsl:attribute>
<xsl:for-each select="sql:row">
<xsl:element name="list:item">
<xsl:value-of select="sql:name"/>
<xsl:text>, </xsl:text>
<xsl:value-of select="sql:forenames"/>
<xsl:choose>
<xsl:when test="not( sql:birth='' and sql:death='' )">
<xsl:text> (</xsl:text>
<xsl:value-of select="sql:birth"/>
<xsl:text> – </xsl:text>
<xsl:value-of select="sql:death"/>
<xsl:text>)</xsl:text>
</xsl:when>
<xsl:when test="not( sql:birth='' )">
<xsl:text> (b.</xsl:text>
<xsl:value-of select="sql:birth"/>
<xsl:text>)</xsl:text>
</xsl:when>
<xsl:when test="not( sql:death='' )">
<xsl:text> (d.</xsl:text>
<xsl:value-of select="sql:death"/>
<xsl:text>)</xsl:text>
</xsl:when>
</xsl:choose>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
<!-- Soak up any remaining elements not processed by the above -->
<xsl:template match="node()|@*" priority="-1">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
After processing the document becomes (suitably indented by me):
<page:content>
<t:heading level="1">SQL Display All Results</t:heading>
<list:list xmlns:list="http://www.hsfr.org.uk/Schema/List" type="unordered">
<list:item>Dvořák, Antonin (1841-09-08 — 1904-05-01)</list:item>
<list:item>Mozart, Wolfgang Amadeus (1756-01-27 — 1791-12-05)</list:item>
<list:item>Barber, Samuel (1910-03-09 — 1981-01-23)</list:item>
</list:list>
<t:p><link:link type="uri" ref="/examples/sql/sql.html">Return to SQL index</link:link></t:p>
<t:p><link:link type="uri" ref="/examples/sql/logout.html">Logout</link:link> from admin pages.</t:p>
</page:content>
The output of this appears as:
The next section shows how to add more entries to the list.
Copyright 2006 — 2010 Hugh Field-Richards. All Rights Reserved.