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.