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.
