CGI invocation of parameterized SQL using XSLT, XML, and Ferris

Mr. Ben "monkeyiq" Martin

Abstract

An XSQL like solution for exposing a relational database through server side parameterized SQL queries invoked through XSQL like CGI roundtrips. Ferris is used as the underlying tool to mount SQL queries and expose the results as a Filesystem or DOM.

Table of Contents

Overview

Setting up the database

Setting up apache

Ferris database authentication
General apache setup

XSQL query file and processing

CGIFerrisTransform: Close up

CGICC: the road to DOM
The DOM structure
Flow of control

Round trip walk through

Conclusion

Bibliography

Overview

The many components required to build a web presence based on a clean separation of content and style are presented in a tutorial layout. The target environment is a Linux machine using apache 1.3.22+, Ferris 0.9.2+, xerces-c 1.3+, and xalan-c 1.3+. Note that ferris tar.bz2 files and xerces-c and xalan-c rpms are available at the ferris distribution website.

This is a follow on paper from the work done in exposing relational database information in [FerrisXSL1Paper]. This paper rounds out some of the rough edges of the previous work, integrates the solution with apache to provide content dynamically to the web and presents parameterized SQL using information from CGI parameters.

The basic workings are

Web browser performs an HTTP request for a document such as
```
http://www.yournamehere.com/gimmedata.fxsql?name=frodo
```
Apache redirects this request to a CGI script, CGIFerrisTransform.
CGIFerrisTransform creates an XML document from its CGI parameters and wraps the contents of the gimmedata.fxsql file with an XSLT skeleton. This allows the gimmedata.fxsql XML file to access the CGI parameters using standard XSLT selections such as {@name}.
CGIFerrisTransform performs the XSLT translation using the stylesheet created from wrapping gimmedata.fxsql and passes the resulting XML to ferris to mount it as a filesystem.
CGIFerrisTransform wraps the resulting filesystem in a DOM using a one line call to Ferris.
CGIFerrisTransform may either: print out the XML from the query result, or perform an XSLT translation on the DOM before presenting the document. This final transform can create HTML or pdf files for web delivery in response to the original HTTP request.

The example and supporting XML, XSL, and SQL files can be found in the tests/xml directory tree of the Ferris 0.9.2+ ferris distribution .

Setting up the database

This example uses an SQL database as a data source. A script is supplied to create the database, table and populate it for the query. Be aware that this script assumes that it can drop and recreate some tables in the "ferristest" database.

Example 1. tests/xml/CGITransform/setupdb.sql

tests/xml/CGITransform $ cat setupdb.sql
#
#
#
create database if not exists ferristest;
use ferristest;

drop table if exists actors;
create table actors
(
  fname varchar(100),
  lname varchar(100),
  addr  varchar(100) default 'unknown address',
  custid int(10) primary key auto_increment
) type=MyISAM;

insert into actors (fname,lname,addr) values ('fred', 'smith', '15 backers' );
insert into actors (fname,lname,addr) values ('harry', 'sack', '15 credability st' );
insert into actors (fname,lname,addr) values ('frodo', 'baggins', 'bree' ); 
insert into actors (fname,lname,addr) values ('underhill', 'mcgoo', 'bree' );
insert into actors (fname,lname,addr) values ('sam', 'x', 'mordor' );
insert into actors (fname,lname,addr) values ('ring wrath', 'stealthboy', 'bree' );
insert into actors (fname,lname,addr) values ('cold drake', 'cold flame', 'bree' );

drop table if exists items;
create table items
(
  description varchar(100),
  iid  int(10) primary key auto_increment
) type=MyISAM;

insert into items (description) values ('bread');
insert into items (description) values ('sting');
insert into items (description) values ('staff');
insert into items (description) values ('cape');
insert into items (description) values ('xslt');
insert into items (description) values ('black cape');
insert into items (description) values ('the one ring');
insert into items (description) values ('magic boots');

drop table if exists holdings;
create table holdings
(
  custid int(10) not null,
  iid int(10) not null
) type=MyISAM;

insert into holdings (custid,iid) values ('1','1');
insert into holdings (custid,iid) values ('2','1');
insert into holdings (custid,iid) values ('3','1');
insert into holdings (custid,iid) values ('4','4');
insert into holdings (custid,iid) values ('5','5');
insert into holdings (custid,iid) values ('6','1');
insert into holdings (custid,iid) values ('7','3');

insert into holdings (custid,iid) values ('6','6');
insert into holdings (custid,iid) values ('3','7');
insert into holdings (custid,iid) values ('3','8');

select fname, description
from holdings,items,actors
where holdings.iid    = items.iid
and   holdings.custid = actors.custid

If the user has permission to access the local mysql server then this can be setup using:

$ mysql < setupdb.sql

Setting up apache

Ferris database authentication

Ferris uses a dotfile to obtain username and password information to access its databases. This is like the netrc(5) style, only ferris uses Berkeley databases to store this authentication information. One must setup this database if the apache user is not allowed access by default.

Assuming the paths of a Redhat 7.2 machine, to set this up open a root shell and type

mkdir /var/www/.ferris
chown apache:apache /var/www/.ferris
su --command="ferris-capplet-sqlplus" --shell="/bin/bash" apache

In the capplet set the server to localhost and the username and password to use to connect to the local database.

Figure 1. Ferris sqlplus capplet

It is advised that you turn down debugging output for production use as it can slow down ferris by orders of magnitude depending on how syslog is configured. Use this command and drag the "all" sliderbar to none.

su --command="ferris-capplet-logging" --shell="/bin/bash" apache

Figure 2. Ferris logging capplet

General apache setup

This section assumes that one has already installed an apache 1.3.22+ web server. This example may also work with earlier versions. The configuration assumes one has root access to the server machine, this being the case the CGIFerrisTransform is put into the global /cgi-bin/ and the fxsql and XSL files are located in a custom path of /ferrisql/ under the server. Information in this section is based on information found at [Apache handler manual].

We make CGIFerrisTransform the default action for fxsql files by adding to our /etc/httpd/conf/httpd.conf the following

Action     call-cgi-ferris-transform /cgi-bin/CGIFerrisTransform
AddHandler call-cgi-ferris-transform .fxsql

CGIFerrisTransform has been designed to work in this manner and can obtain the name of the fxsql file to transform from apache.

Note CGIFerrisTransform guesses the name of the xsl sheet to apply by changing the name of the fxsql file by removing fxsql and appending xsl. If you wish to apply an XSL sheet with a different location you can override this with the CGI_FERRISTRANSFORM_XSLT environment variable. The below shows how this might be done for a single fxsql query.

SetEnvIf   Request_URI "query\.fxsql$" CGI_FERRISTRANSFORM_XSLT=/somewhere/dark.xsl

If the implicit XSL file does not exist then the raw XML resultset is output. If there is an explicit CGI_FERRISTRANSFORM_XSLT environment variable and that file does not exist then it is an error.

The above configuration assumes that one is installing CGIFerrisTransform into the cgi-bin dir. On a RedHat 7.2 machine this can be performed using

su -
cd tests/xml/CGITransform
make install

On machines using locations other than /var/www for the web root, one will need to move the CGIFerrisTransform and supporting fxsql and xsl files to the appropriate place. See /etc/httpd/conf/httpd.conf for a line like the following

ScriptAlias /cgi-bin/ "/var/www/cgi-bin/"

to find ones cgi-bin and the web root is generally just ../html from the cgi-bin.

XSQL query file and processing

The format of a fxsql file contains a few elements and some SQL.

Example 2. tests/xml/CGITransform/query.fxsql


<queryview>
    <name>resultingtable</name>
    <sql>
select fname, description, addr
from holdings, items, actors 
where holdings.iid    = items.iid
and   holdings.custid = actors.custid 
and   addr     = '<xsl:value-of select="@address" />'  
    </sql>
</queryview>

As can be seen there is a parameter to restrict the address of the items shown in the result.

The interesting thing about this file is that CGIFerrisTransform converts this XML file into an XSLT file by wrapping it in something like the following.

Example 3. XSLT wrapper for query.fxsql


<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" version="1.0" standalone="yes" />
    <xsl:template match="/*/">
    ...
    </xsl:template>
</xsl:stylesheet>

A in memory DOM is then created to represent the CGI parameters and that CGI DOM is then translated using the XSLT created from the fxsql file. This is where parameters such as {@address} in Example 2 would be replaced with the CGI parameter.

This would mean that for a URL invocation of

http://localhost/ferrisql/query.fxsql?address=bree

the generated XML file would look something like

Example 4. XML ready to mount


<queryview>
    <name>resultingtable</name>
    <sql>
select fname, description, addr
from holdings, items, actors 
where holdings.iid    = items.iid
and   holdings.custid = actors.custid 
and   addr     = 'bree'
    </sql>
</queryview>

Note that as all CGI parameters are placed into the document to be transformed, conditional processing is possible in the fxsql file. For example, an xsl:if could check for a shortlisting attribute and if it is present then only return a partial projection of the available data. The following presents all the data when debug mode is on, otherwise only the interesting stuff is shown.

Example 5. fxsql for a partial presentation


<queryview>
    <name>result</name>
    <sql>
select 
        <xsl:choose>
        <xsl:when test="@debug = yes">
*
        </xsl:when>
        <xsl:otherwise>
fname, description
        </xsl:otherwise>
        </xsl:choose>
from holdings,items,actors
where holdings.iid    = items.iid
and   holdings.custid = actors.custid
and   addr     = '<xsl:value-of select="@address" />'  
    </sql>
</queryview>

CGIFerrisTransform: Close up

CGICC: the road to DOM

One major difference between the path followed in this paper and [FerrisXSL1Paper] is that we need to take an HTTP POST or GET method and create an XML DOM from the CGI parameters.

Instead of creating ad hoc code to parse the QUERY_STRING or stdin material, the GNU CGICC library [GNU CGICC website] was used to obtain the CGI data in a more palatable manner. The one covert being that Ferris uses the std:: material from STLPort 4.5. So in order to avoid having two versions of std::string in CGIFerrisTransform the source to CGICC must be compiled with STLPort 4.5+. This can be done by exporting a suitable environment and then rebuilding an existing src.rpm of the CGICC library [CGICC src rpm from makdrake Linux].

export CXXFLAGS=" `stlport-config --cflags` "
export LDFLAGS=" `stlport-config --libs` "
rpm --rebuild /store/cgicc-3.1.5-1mdk.src.rpm
su -
rpm -Uvh /userbuiltrpms/cgicc-3.1.5-1mdk.i686.rpm

The above assumes that the machine is setup to make 686 srcs by default and allows users to rebuild src.rpms. Alternately one can download the cgicc binary rpms created with the above procedure from ferris distribution website.

The DOM structure

Each of the CGI parameters are enumerated and placed into the root element twice; once as attributes and once as sub elements. This is done so that complex forms that post many values for the same key can be accommodated. If a multivalued key is found the first value becomes a attribute and an element, and successive values only become elements.

For example, the url

http://localhost/ferrisql/test.fxsql?name=frodo&enemy=glurung

Would create the following DOM


<?xml version='1.0'?>
<cgidata name="frodo" enemy="glurung">
    <name>frodo</name>
    <enemy>glurung</enemy>
</cgidata>

Flow of control

The CGIFerrisTransform program has a small main that uses popt and getenv to acquire all the needed cmd line arguments allowing the app to run both from the cmd line or from apache.

There is some optional debugging framework using the -1 through -4 numerical switches to enable each phase of debug output. Other than that there is a straightforward function DOM_Document CGItoDOM() which creates a DOM from POST or GET data. To use this from the command line, simply use

QUERY_STRING="name=frodo&address=bree"  ./CGIFerrisTransform >|/tmp/index.html

at the bash prompt.

There are also some overloaded xsltransform methods that may be of interest to the keen. The main execution occurs in the operator() method as detailed below.


    void operator()()
        {
            /*
             * convert cgiDOM into a new DOM using FerrisXSQLURL and then bind
             * md to that new dom
             */
            DOM_Document cgiDOM = CGItoDOM(); 
            debugCGIDOM( cgiDOM );

            fh_istream xsql_xslt = get_XSQL_XSLT(); 
	    DOM_Document mdDOM = xsltransform( cgiDOM, &amp;xsql_xslt ); 
            debugTransformed_fxsql( mdDOM );
            
            md = Factory::mountDOM( mdDOM ); 
            debugWrappedMetaData( md );

            dbc = Resolve( DatabaseContextURL ); 
            
            DG() << "Creating the subcontext for the query... dbc:"
                 << dbc->getURL() << endl;
            fh_context queryc = dbc->createSubContext("", md ); 

            DG() << "Creating the DOM for:" << queryc->getURL() << endl;
            DOM_Document theDOM =  Factory::makeDOM( queryc ); 
            debugQueryDOM( theDOM );

            if( !FinalXSLTURL.length() )
            { 
                fh_istream x = tostream( theDOM );
                std::copy( std::istreambuf_iterator<char>( x ),
                           std::istreambuf_iterator<char>(),
                           std::ostreambuf_iterator<char>(cout));
                cout << endl;
            }
            else
            { 
                xsltransform( theDOM, 
                              FinalXSLTURL.c_str(),
                              cout);
            }
        }

	This line translates the CGI parameters to a DOM using cgicc.
	Create the XSLT stylesheet by wrapping the users fxsql given in either --fxsql or the PATH_TRANSLATED environment variable.
	Transform the DOM created from the cgi parameters using the fxsql XSLT to obtain the meta data for Ferris to mount a relational database.
	Mount a DOM as a filesystem with Ferris
	Mount the relational database at the database context. This means that the server and database name are encoded into the `DatabaseContextURL` parameter.
	Mount the meta data as a new filesystem attached to the database.
	Create a facade DOM that exposes a filesystem as a document
	Copy the DOM to raw XML output on cout
	Perform an XSLT and present the result on cout

Round trip walk through

The fxsql file needed for the query has been shown in Example 2 and discussed in the section called “XSQL query file and processing”. This section shows the other side of the circuit, the XSL sheet to create a nice looking HTML output from the resultset of the query.

This example uses the "Muench method" as detailed on page 144 of [ORA XSLT]. The technique allows sorted grouping of the result set. We sort first on the name of the actor and then on the description of the item that actor is carrying.

The XSLT also presents information that is not sourced from the database but is added by Ferris. This information is the primary-key and the md5. The primary key is what ferris is using to key each tuple and the value of that composite field is used as the name EA for each tuple to uniquely identify it. The md5 is created by exporting each tuple as XML and then taking the md5 digest of that XML. This allows one to verify that data for each tuple has or has not changed between queries.

We now present the XSL sheet for the query presentation.

Example 6. tests/xml/CGITransform/query.xsl


<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="html" indent="yes"/>
  
  <xsl:key name="fnamekey" match="context" use="@fname" />

  <xsl:template match="/*">
    <html>
      <head>
        <title>ferrisxsl query results</title>
        <style>
          td.light { background-color:#ddcccc; }
          td.dark  { background-color:lightgrey; }
        </style>
      </head>
      <body  bgcolor="#ccaaaa" text="#000000">
        <table border="1" >

          <tr bgcolor="338822" color="#000000" >
            <td><font color="#FFFFFF">name</font></td>
            <td><font color="#FFFFFF">description</font></td>
            <td><font color="#FFFFFF">primary-key</font></td>
            <td><font color="#FFFFFF">address</font></td>
            <td><font color="#FFFFFF">md5</font></td>
          </tr>
          
          <!-- page 144 ORA XSLT animal book -->
          <xsl:for-each select="//context[generate-id(.)=
                                  generate-id(key('fnamekey', @fname )[1])]">
            <xsl:sort select="@fname"/>    
            <xsl:variable name="fnamepos" select="position()"/>

            <xsl:for-each select="key('fnamekey', @fname )">
              <xsl:sort select="@description"/>

              <xsl:apply-templates select=".">
                <xsl:with-param name="fnamepos" select="$fnamepos"/>
                <xsl:with-param name="descpos" select="position()"/>
              </xsl:apply-templates>
              
            </xsl:for-each>
          </xsl:for-each>
          
        </table>
      </body>
    </html>
  </xsl:template>

  
  <xsl:template match="context">
    <xsl:param name="fnamepos"/>
    <xsl:param name="descpos"/>

              <tr BGCOLOR="#DDCCCC" >
                <xsl:if test="$descpos = 1">
                  <td valign="left" bgcolor="#999999">
                    <xsl:attribute name="rowspan">
                      <xsl:value-of select="count(key('fnamekey', @fname ))"/>
                    </xsl:attribute>
                    <b>
                      <xsl:value-of select="@fname"/>
                    </b>
                  </td>
                </xsl:if>

                <xsl:variable name="bgcolor">
                  <xsl:choose>
                    <xsl:when test="($descpos) mod 2">light</xsl:when> 
                    <xsl:otherwise>dark</xsl:otherwise>
                  </xsl:choose>
                </xsl:variable>

                <td class="{$bgcolor}"><xsl:value-of select="@description"/> </td>
                <td class="{$bgcolor}"><xsl:value-of select="@primary-key"/> </td>
                <td class="{$bgcolor}"><xsl:value-of select="@addr"/></td>
                <td class="{$bgcolor}"><xsl:value-of select="@md5"/>
              </td>
              </tr>
  </xsl:template>
  
</xsl:stylesheet>

The generated page when looking for an address of "bree" displays like

Figure 3. query.fxsql in action

Adding a form

It would be nice to add a form to drive the fxsql query and present a user friendly interface. We will do this by adding another fxsql that selects all the address locations from the relational database and places the output into a web form.

The fxsql for this is very simple

Example 7. tests/xml/CGITransform/queryform.fxsql


<queryview>
    <name>resultingtable</name>
    <sql>
select * from actors
    </sql>
</queryview>

The xsl sheet for this example is a little more complex because it drops duplicate address values and presents addresses in a sorted manner

Example 8. tests/xml/CGITransform/queryform.fxsql


<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="html" indent="yes"/>
  
  <xsl:key name="addrkey" match="context" use="@addr" />

  <xsl:template match="/*">
    <html>
      <head>
        <title>ferrisxsl query results</title>
        <style>
          td.light { background-color:#ddcccc; }
          td.dark  { background-color:lightgrey; }
        </style>
      </head>
      <body  bgcolor="#ccaaaa" text="#000000">

        <h1>Select address to query for</h1>

        <form action="query.fxsql" METHOD="GET">
          <select name="address" size="6" >

            <xsl:for-each select="//context[generate-id(.)=
                                  generate-id(key('addrkey', @addr )[1])]">
              <xsl:sort select="@addr"/>    
              <xsl:apply-templates select="." />
            </xsl:for-each>            
            </select>
          <br></br>
          <input type="submit" name="subbutton" value="Submit"></input>
        </form>

      </body>
    </html>
  </xsl:template>

  
  <xsl:template match="context">
    <option value="{@addr}" ><xsl:value-of select="@addr"/></option>
  </xsl:template>
  
</xsl:stylesheet>

Which generates a web form looking like the below

Figure 4. Display of queryform.fxsql

Conclusion

This paper has presented in great detail both the CGI client of Ferris and the mechanics allowing one to expose relational data to the web using modern practices. Technologies such as XML, XSLT and Ferris were used to allow a clean separation of style and semantics where the query itself is contained in a XML file and can use XSLT constructs to react to CGI data in a more abstract manner than one would if directly coding CGI clients.

Source code for ferris 0.9.2+ and its dependencies for a Redhat 7.2 enigma machine can be found at Ferris downloads . Note that not all of the dependencies are required. for example if the a52dec rpm is not detected during configure time Ferris will not build support for a52 audio.

For discussion about this document please use the Ferris mailing list or on irc.openprojects.net/#ferris.