Using SQL data to set up a selector list in PForms.
Occasionally it is necessary to set up a select input field using data
dynamically extracted from a database. Assume that we have the database:
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 |
| 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>
From this list we would like to use the name field as the selection
data for a multiple select field. In other words we would like a Paloose form:
<pf:select appearance="full" ref="composers">
<pf:value>Select composers</pf:value>
<pf:choices>
<pf:item checked="false">
<pf:label>Mozart, Wolfgang Amadeus</pf:label>
<pf:value>Mozart</pf:value>
</pf:item>
<pf:item>
...
</pf:item>
</pf:choices>
</pf:select>
First we define an XML document (using the Paloose site document format):
<?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"
>
<page:meta>
...
</page:meta>
<page:content>
...
<t:group id="composerAsSelector">
<execute-query xmlns="http://apache.org/cocoon/SQL/2.0">
<query name="music">select * from composer</query>
</execute-query>
</t:group>
</page:content>
</page:page>
This will return a list of the composers:
<?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"
>
<page:meta>
...
</page:meta>
<page:content>
...
<t:group id="composerAsSelector">
<default:row-set nrofrows="8" name="music" xmlns="http://apache.org/cocoon/SQL/2.0">
<default:row>
<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-set>ß
</t:group>
</page:content>
</page:page>
This needs processing to a suitable PForm selector:
sql2xml.xsl
<?xml version="1.0"?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:t="http://www.hsfr.org.uk/Schema/Text"
xmlns:page="http://www.hsfr.org.uk/Schema/Page"
xmlns:sql="http://apache.org/cocoon/SQL/2.0"
xmlns:pf="http://www.paloose.org/schemas/Forms/1.0"
version="1.0">
<!-- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -->
<xsl:template match="t:group[ @id = 'composerAsSelector' ]">
<xsl:element name="pf:form">
<xsl:attribute name="id">composersSelectionForm</xsl:attribute>
<xsl:attribute name="flow">composersSelection</xsl:attribute>
<xsl:attribute name="continuation">
<xsl:text>{flow:continuation.id}</xsl:text>
</xsl:attribute>
<xsl:attribute name="session">
<xsl:text>{flow:__flowId}</xsl:text>
</xsl:attribute>
<xsl:element name="pf:label">Select composers</xsl:element>
<xsl:element name="pf:select">
<xsl:attribute name="appearance">full</xsl:attribute>
<xsl:attribute name="ref">composers</xsl:attribute>
<xsl:element name="pf:value">{flow:roles}</xsl:element>
<xsl:apply-templates mode="selectComposers"/>
</xsl:element>
</xsl:element>
</xsl:template>
<xsl:template match="sql:row-set" mode="selectComposers">
<xsl:element name="pf:choices">
<xsl:for-each select="sql:row">
<xsl:element name="pf:item">
<xsl:element name="pf:label">
<xsl:value-of select="sql:name"/>
<xsl:text>, </xsl:text>
<xsl:value-of select="sql:forenames"/>
</xsl:element>
<xsl:element name="pf:value">
<xsl:value-of select="sql:name"/>
</xsl:element>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
<!-- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -->
<xsl:template match="node()|@*" priority="-1">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Allowing for some appropriate value for the form data (such as the continuation variables).
This would process the input to something similar to:
<pf:form id="composersSelectionForm" flow="composersSelection"
continuation="{flow:__flowId}" xmlns:pf="http://www.paloose.org/schemas/Forms/1.0">
<pf:label>Select composers</pf:label>
<pf:select appearance="full" ref="composers">
<pf:choices>
<pf:item>
<pf:label>Mozart, Wolfgang Amadeus</pf:label>
<pf:value>Mozart</pf:value>
</pf:item>
...
</pf:choices>
</pf:select>
</pf:form>
There would obviously be other PForm elements as well, such as the buttons.
In order to process this we need a pipeline similar to the following:
<map:pipelines>
<map:pipeline>
<!-- Deals with the sql root page -->
<map:match pattern="**.html">
<map:generate src="context://{1}.xml"/>
<map:transform type="mysql">
<map:parameter name="show-nr-of-rows" value="true"/>
</map:transform>
<map:transform src="context://resources/transforms/sql2xml.xsl" label="sql-transform"/>
<!-- The next 3 transforms are part of Paloose PForms not the user's system -->
<map:transform src="resource://resources/transforms/pforms-violations.xsl" label="pforms-violations">
<map:parameter name="formViolations" value="{session:__violations}"/>
</map:transform>
<map:transform src="resource://resources/transforms/pforms-default.xsl" label="pforms-default"/>
<map:transform src="resource://resources/transforms/pforms2html.xsl" label="pforms-html"/>
<map:transform src="context://resources/transforms/page2html.xsl" label="page-transform"/>
<map:transform src="context://resources/transforms/stripNamespaces.xsl"/>
<map:serialize type="html"/>
</map:match>
</map:pipeline>
</map:pipelines>
Copyright 2006 – 2024 Hugh Field-Richards. All Rights
Reserved.