SQL Worked Example
The following describes a worked example of using a SQL database with authorised pages. It shows how to construct forms to enter and
remove data, and display that data. The example is based on a directory structure:
The SQL database is a simple table of composers and their dates similar to the one I used in the SQL How-to:
Note that the character set used for the two text fields is UTF-8 UniCode, which allows the correct representation of names such as
"Dvořák". The Paloose pipes use this encoding; the output serializers output according to the encoding parameter.
The index page for the example is a simple list:
/examples/sql/sql.html
<?xml version="1.0" encoding="UTF-8"?>
<page:page
xmlns:xi="http://www.w3.org/2001/XInclude"
xmlns:t="http://www.hsfr.org.uk/Schema/Text"
xmlns:page="http://www.hsfr.org.uk/Schema/Page"
xmlns:list="http://www.hsfr.org.uk/Schema/List"
xmlns:pf="http://www.paloose.org/schemas/Forms/1.0"
xmlns:link="http://www.hsfr.org.uk/Schema/Link"
xmlns:paloose="http://www.paloose.org/schemas/Paloose/1.0">
<page:meta>
<page:title>Paloose — SQL Example</page:title>
<page:copyright>Copyright 2006 – 2010 Hugh Field-Richards. All Rights Reserved.</page:copyright>
</page:meta>
<page:content>
<t:heading level="1">SQL Example</t:heading>
<t:p>This example allows you to set up and query a database. They are all working with a data base
"composers".</t:p>
<list:list type="unordered">
<list:item><link:link type="uri" ref="addComposer.html">Create
composer</link:link></list:item>
<list:item><link:link type="uri" ref="deleteComposer.html">Delete
composer</link:link></list:item>
</list:list>
<list:list type="unordered">
<list:item><link:link type="uri" ref="displayAllComposers.html">Display
entire database</link:link></list:item>
</list:list>
<t:p><link:link type="uri" ref="logout.html">Logout</link:link> from
admin pages.</t:p>
</page:content>
</page:page>
The sitemap to read this file is fairly simple at this level. First the necessary components (it is worth noting that some of these
declarations can be placed in the calling sitemap when this is a subsitemap. I have elected to turn off the caching here.
/examples/sql/sitemap.xmap
<?xml version="1.0" encoding="UTF-8"?>
<map:generators default="file">
<map:generator name="file" src="resource://lib/generation/FileGenerator" cachable="no">
<map:use-request-parameters>true</map:use-request-parameters>
</map:generator>
</map:generators>
<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:transformers>
<map:serializers default="xml">
<map:serializer name="xhtml" mime-type="text/html" src="resource://lib/serialization/XHTMLSerializer">
<doctype-public>-//W3C//DTD XHTML 1.0 Transitional//EN</doctype-public>
<doctype-system>http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd</doctype-system>
<encoding>ISO-8859-1</encoding>
</map:serializer>
<map:serializer name="xml" mime-type="text/xml" src="resource://lib/serialization/XMLSerializer"/>
</map:serializers>
<map:matchers default="wildcard">
<map:matcher name="wildcard" src="resource://lib/matching/WildcardURIMatcher"/>
<map:matcher name="regexp" src="resource://lib/matching/RegexpURIMatcher"/>
</map:matchers>
The only resource here is a means of outputting the final assembled XML page.
/examples/sql/sitemap.xmap - continued
<map:resources>
<map:resource name="outputPage">
<map:transform src="context://resources/transforms/page2html.xsl" label="page-transform">
<map:parameter name="page" value="{1}"/>
</map:transform>
<map:transform src="context://resources/transforms/stripNamespaces.xsl"/>
<map:serialize type="xhtml"/>
</map:resource>
</map:resources>
When I worked the original example I declared a series of views to help with the debugging.
/examples/sql/sitemap.xmap - continued
<map:views>
<map:view name="aggr" from-label="aggr-content">
<map:call resource="outputXML"/>
</map:view>
<map:view name="xml" from-label="xml-content">
<map:call resource="outputXML"/>
</map:view>
<map:view name="px" from-label="px-content">
<map:call resource="outputXML"/>
</map:view>
<map:view name="transform" from-label="page-transform">
<map:call resource="outputXML"/>
</map:view>
<map:view name="menus" from-label="menus-content">
<map:call resource="outputXML"/>
</map:view>
<map:view name="pforms-1" from-label="pforms-violations">
<map:call resource="outputXML"/>
</map:view>
<map:view name="pforms-2" from-label="pforms-default">
<map:call resource="outputXML"/>
</map:view>
<map:view name="pforms-3" from-label="pforms-html">
<map:call resource="outputXML"/>
</map:view>
<map:view name="sql" from-label="sql-content">
<map:call resource="outputXML"/>
</map:view>
<map:view name="sql-transform" from-label="sql-transform">
<map:call resource="outputXML"/>
</map:view>
</map:views>
The first page has a very simple pipeline:
/examples/sql/sitemap.xmap - continued
<map:pipelines>
<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:pipeline>
<map:pipeline internal-only="true">
<map:match pattern="menus.xml">
<map:generate src="cocoon:/../menus.xml" label="menus-content"/>
<map:serialize/>
</map:match>
<map:match pattern="*.xml">
<map:generate src="cocoon:/{1}.xml" label="xml-content"/>
<map:serialize/>
</map:match>
</map:pipeline>
</map:pipelines>
</map:sitemap>
Using the same styles as the Paloose site the above would appear as:
The next section shows adding the link to display all the entries in the
data base.
Copyright 2006 — 2010 Hugh Field-Richards. All Rights Reserved.
