XSLT, DOM, SQL and the web

Mr. Ben "monkeyiq" Martin

Abstract

The selection of information from a relational database using both SQL and XSLT for delivery on the web. Focus is on the use of Ferris to bring the relational database into the world of XML by mounting either a table or query and presenting that data as parsed XML in the form of a Document Object Model (DOM). The DOM is then transformed into HTML using XSL.


Table of Contents

Overview
Setting up the database
Mounting the relational database or query
Changing the query but not the XML file
Wrapping ferris in DOM
XSLT and the DOM
Conclusion
Bibliography

Overview

Ferris is a VFS that provides information using standard C++ iostreams and meta data using Extended Attributes (EA). Some metadata is created on demand such as md5 hashes and YUV data from mpeg2 streams. More information on Ferris can be found by viewing [FerrisWebSite] in Mozilla (or anything that handles 8 bit alpha in png files).

This document progresses through creation of a test database, mounting a query on that database as a filesystem, the wrapping of that filesystem in a DOM, and the final XSLT processing of the DOM into HTML for web deployment.

The example and supporting XML, XSL, and SQL files can be found in the tests/xml/XSLTransform directory of the 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.

Example 1. setupdb.sql

$ cat setupdb.sql
create database if not exists ferristest;
use ferristest;

drop table if exists ferrisxsl;
create table ferrisxsl (
  pk int(10) primary key auto_increment,
  userid int(5)  not null default '5',
  username varchar(100),
  addr varchar(100) default 'unknown address'
) type=MyISAM;

insert into ferrisxsl (userid,username,addr) values (1, 'fred', '15 backers' );
insert into ferrisxsl (userid,username,addr) values (5, 'harry', '15 credability st' );
insert into ferrisxsl (userid,username,addr) values (5, 'frodo', 'bags end' );
insert into ferrisxsl (userid,username,addr) values (5, 'underhill', 'bree' );
insert into ferrisxsl (userid,username,addr) values (5, 'sam', 'mordor' );
insert into ferrisxsl (userid,username,addr) values (2, 'strider2', 'bree' );
insert into ferrisxsl (userid,username,addr) values (2, 'strider3', 'bree' );
insert into ferrisxsl (userid,username,addr) values (2, 'strider4', 'bree' );
insert into ferrisxsl (userid,username,addr) values (2, 'strider5', 'bree' );

select * from ferrisxsl;
If the user has permission to access the local mysql server then this can be setup using:
$ mysql <setupdb.sql

Mounting the relational database or query

Most database APIs allow one to submit SQL and obtain a resulting set of tuples. Ferris allows one to view this result as though it were a filesystem. This is performed using a non standard URL method (as indicated by the leading x-),

x-sqlplus:///localhost/databasename/tablename
authentication is performed using a dotfile to lookup username and passwords for the given host, similar to netrc(5). This is done to make URLs more universal and to avoid the issue of embedding secret information in a public identifier.

SQL Queries are mounted using ferris' subcontext creation API [FerrisCreatePaper]. This involves submitting SQL information to the Context::createSubContext() method to obtain the new context that has the results of the query.

The XSLTransform example that comes with recent versions of ferris embeds this sql information into an XML document.

$ cat query.xml

<query>
	<queryview>
	    <name>mount-sql-as-xml</name>
	    <sql>select * from ferrisxsl where userid=5</sql>
	</queryview>
</query>

For the above XML to be used as metadata a URL that locates a suitable database is required. The database sought will have the ferrisxsl table in it and have been created using the setupdb.sql script. The context that supports this has the URL
x-sqlplus:///localhost/ferristest
To mount the XML query, first one resolves the database context and then calls the Context::createSubContext() method to create a new context from the results of that query.

For any given context one can call fh_istream Context::getCreateSubContextSchema() to obtain the XSD of what is acceptable for subcontext creation using Context::createSubContext() on that context. The data passed to createSubContext() should be valid under the given XSD for that context. For more information on sub context creation see [FerrisCreatePaper].

The C++ for the database binding:

const char* DatabaseContextURL = "x-sqlplus://localhost/ferristest";
const char* DatabaseQueryMetaDataURL = "./query.xml/query";
fh_context md     = Resolve( DatabaseQueryMetaDataURL );
fh_context c      = Resolve( DatabaseContextURL );
fh_context queryc = c->createSubContext("", md );
at this point the query has been executed and the resulting filesystem is accessible through queryc.

Changing the query but not the XML file

With ferris one can "override" the data from the XML file at runtime. By executing

$ ./XSLTransform --help
...
Overload context options:
  --override-name=mount-sql-as-xml
  --override-sql=select * from ferrisxsl where userid=5
...
One sees that there is provision to change some of the information loaded from the XML file that contains the SQL query. This is handled in the client via the following code.
        fh_context md = Resolve( DatabaseQueryMetaDataURL );

        struct poptOption optionsTable[] = {
            FERRIS_POPT_OPTIONS
            FERRIS_CONTEXTPOPT_OPTIONS( md ) // 1
            POPT_AUTOHELP
            POPT_TABLEEND
        };

        optCon = poptGetContext(PROGRAM_NAME.c_str(), argc, argv, optionsTable, 0);
        poptSetOtherOptionHelp(optCon, "[OPTIONS]* ...");

        /***/
        char ch=-1;
        while ((ch = poptGetNextOpt(optCon)) >= 0)
            {}
1

md is read and the default popt options that are seen above are created. after option processing then md might contain data other than that which is on disk if the user passed in any --override options.

Wrapping ferris in DOM

The ferris API is designed to work in a C++ environment. As such it takes full advantage of smart pointers and its own derived standard iostreams compatible stream classes. This makes for a very nice C++ API but a wrapper is needed if filesystems exposed via Ferris are to be accessed by things expecting XML documents. The solution is to take an arbitrary Ferris context and create a DOM rooted at that node.

This operation is exposed to the developer using a factory function that creates a xerces-c DOM object from a ferris context.

DOM_Document Ferris::Factory::makeDOM( fh_context c );

Presently this factory method creates a deep copy of all contexts nested under the one given. In the future this should return a Document that is just a proxy object using the Ferris API underneath. This future design would allow one to get a DOM of the "/" filesystem.

The DOM returned exposes not only the contents of each object but also the EA and generated EA for each context. For example, each context has an "md5" and "sha1" EA that is the hash of the context.

XSLT and the DOM

Once a standard xerces-c DOM is obtained from the Ferris context the XSLT can be applied to create a HTML document suitable for web delivery. Using this method the load on the XSL processor is much less because only nodes that are relevant to the final page need be presented in the DOM. This allows a database with many thousand tuples to have a dozen selected, wrapped in DOM and presented to the XSL processor rather than a document with many thousand irrelevant nodes.

Carrying on the above example using queryc from the above sql mount the output HTML can be created.

 const char* XSLFileName = "./query.xsl";
 DOM_Document theDOM =  Factory::makeDOM( queryc );

 XercesDOMSupport theDOMSupport;
 XercesParserLiaison theParserLiaison(theDOMSupport);
        
 const XercesDOMWrapperParsedSource parsedSource(
     theDOM, theParserLiaison, theDOMSupport );

 XalanTransformer theXalanTransformer;
 int theResult = theXalanTransformer.transform( 
     parsedSource, XSLFileName, cout);

Assuming a very basic query.xsl that just presents the result set as an HTML table with some trivial formating.

Example 2. 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:template match="/mount-sql-as-xml">
    <html>
      <head><title>ferrisxsl query results</title></head>
      <body  bgcolor="#ccaaaa" text="#000000">
        <table border="1" >
          <tr bgcolor="338822" color="#000000" >
            <td><font color="#FFFFFF">pk</font></td>
            <td><font color="#FFFFFF">userid</font></td>
            <td><font color="#FFFFFF">uname</font></td>
            <td><font color="#FFFFFF">addr</font></td>
          </tr>
          <xsl:apply-templates/>
        </table>
      </body>
    </html>
  </xsl:template>

  <xsl:template match="context">

    <tr BGCOLOR="#DDCCCC" >
      <td><xsl:value-of select="@pk"/> </td>
      <td><xsl:value-of select="@userid"/> </td>
      <td><xsl:value-of select="@username"/> </td>
      <td><xsl:value-of select="@addr"/> </td>
    </tr>

    <xsl:apply-templates/>
  </xsl:template>
  
</xsl:stylesheet>

This can then be run in the style of a CGI program and the produced HTML fed into a file for browsing.

$ ./XSLTransform  >|/tmp/out.html

The resulting HTML should look something like this

Figure 1. out.html

Conclusion

A method of allowing one to access information from a relational database has been presented. This method maintains a clean separation of style (xsl) from content (rdbms). Using Ferris affords one the luxury of using a relational database to store information and to generate XML style content on demand.

There are plans to create a lighter weight DOM wrapper and to present a PHP4 interface to the system so to allow tighter integration with existing web infrastructure.

Other uses for wrapping filesystems in a DOM API are already being considered. These include a reimplementation of the find(1) command wrapping the given paths in a DOM and using XSLT to apply the -name etc predicates to generate the output.

Source code for ferris 0.9.0+ 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.

Bibliography

Websites

[FerrisWebSite] Ben Martin. Copyright © 2001 Ben Martin.

[FerrisCreatePaper] Ben Martin. Copyright © 2001 Ben Martin.

[XSD W3C]

[Berkeley DB]

[MySQL]

Books