[xalan-2] org.apache.xalan.lib.sql

[Scott Boag/CAM/Lotus <Sc...@lotus.com>]

I have written an experimental extension, somewhat based on an old LotusXSL
extension, that can access a JDBC driver from an XSLT page.  This feeds
into the discussions on streaming we have been having on the Xalan dev
list.  The extension operates mainly through it's DOM interface (contrary
to the discussion on streaming, where we focused on the NodeIterator), and
reuses the 'row' element and it's descendants over and over, in a truly
streaming manner.  There are no real check for the non-streaming kinds of
things you can do, so you have to be very careful right now.  (If you do
non-streaming things, you won't re-trigger a query, you will just get
bizarre results.)

This extension is somewhat different from the "producer" philosophy of
Cocoon... both the production and transformation takes place in a single
process.  Both ways of doing things have advantages and disadvantages.
Hey, it's just an experiment.  Something to think about, and discuss.

One of the keys to implementing this was to have the nodes implement a
NodeSetFilter interface, so they can access information about the given
XPath step they're being used for in getFirstChild and getNextSibling.  The
XPath engine sets the NodeTest just before it calls these functions (if the
given node implements the interface).

The structure generated from the extension is something along the lines of:

<!DOCTYPE row-set [
  <!ELEMENT row-set (column-header|row)*>
  <!ELEMENT column-header EMPTY>
  <!ATTLIST column-header
      catalogue-name CDATA #IMPLIED
      column-display-size CDATA #IMPLIED
      column-label CDATA #IMPLIED
      column-name CDATA #IMPLIED
      column-type CDATA #IMPLIED
      column-type-name CDATA #IMPLIED
      precision CDATA #IMPLIED
      scale CDATA #IMPLIED
      schema-name CDATA #IMPLIED
      table-name CDATA #IMPLIED
      case-sensitive CDATA #IMPLIED
      definitely-writable CDATA #IMPLIED
      nullable CDATA #IMPLIED
      signed CDATA #IMPLIED
      writable CDATA #IMPLIED
      searchable CDATA #IMPLIED
      >
  <!ELEMENT row (column)+>
  <!ELEMENT column #PCDATA>
  <!ATTLIST column
      catalogue-name CDATA #IMPLIED
      column-display-size CDATA #IMPLIED
      column-label CDATA #IMPLIED
      column-name CDATA #IMPLIED
      column-type CDATA #IMPLIED
      column-type-name CDATA #IMPLIED
      precision CDATA #IMPLIED
      scale CDATA #IMPLIED
      schema-name CDATA #IMPLIED
      table-name CDATA #IMPLIED
      case-sensitive CDATA #IMPLIED
      definitely-writable CDATA #IMPLIED
      nullable CDATA #IMPLIED
      signed CDATA #IMPLIED
      writable CDATA #IMPLIED
      searchable CDATA #IMPLIED
      >
]>

Everything is created lazely, including the attributes.  So you don't pay
the expense if you don't use it.  Also, the column attributes are actually
the column header attributes... so no real overhead is incurred from having
the ability to access the properties of a given column from the data point.

You create a connection object, and then get a query object (which is a
NodeIterator that yields the Document node).  The query object yields nodes
via getFirstChild and getNextSibling, and should be able to handle simple
XPath querys that descend down a subtree.  You have to be careful about
assigning a node to a variable, since nodes in the row subtree are reused.
I would like to add error handling for non-streaming usage down the line a
bit, and also add a non-streaming option.

This extension is very interesting as an experiment for a streaming model,
and is also useful in it's own right, even though it is very fragile at the
moment.  The following stylesheet queries an instantdb database (the sample
that comes with the database, found at http://instantdb.enhydra.org).  You
can pass an SQL query in as a parameter, or have it use the default.  It
produces an HTML table with odd rows in gray background, and rows with
UnitsInStock values of zero in red text.  A sample invocation from the
command line:

C:\x\xml-xalan\java>testo sqltest1 -param query "SELECT
ProductName,UnitPrice,UnitsInStock,UnitsOnOrder FROM import1" -out foo.html

(testo is just my bat file for launching Xalan).

The stylesheet:

<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0"
                xmlns:sql="org.apache.xalan.lib.sql.XConnection"
                extension-element-prefixes="sql">
  <xsl:output method="html" indent="yes"/>
  <xsl:param name="query" select="'SELECT * FROM import1'"/>

  <xsl:template match="/">
    <xsl:variable name="products"
                select="sql:new('org.enhydra.instantdb.jdbc.idbDriver',
                                'jdbc:idb:C:
\instantdb\Examples\sample.prp')"/>
    <HTML>
      <HEAD>
        <META http-equiv="Content-Type"
              content="text/html; charset=iso-8859-1"/>
        <META http-equiv="Expires" content="0"/>
        <STYLE type="text/css">
                  BODY { margin-left:2em;
                         background-color:#C0C0C0}
                  .important { color:red;font-style:italic; }
                  .oddfalse { background-color:gray; }
                  .evenfalse { background-color:white; }
                  .oddtrue { background-color:gray;color:red; }
                  .eventrue { background-color:white;color:red; }
        </STYLE>
      </HEAD>
      <BODY>
        <TABLE border="1">
          <xsl:variable name="table" select='sql:query($products,
                         $query)'/>
          <CAPTION><H1>
            <xsl:text>Demo of access to InstantDB Database - tablename:
</xsl:text>
            <xsl:value-of select
="$table/row-set/column-header[1]/@table-name"/>
            </H1></CAPTION>
          <TR>
          <xsl:for-each select="$table/row-set/column-header">
            <TH><xsl:value-of select="@column-label"/></TH>
          </xsl:for-each>
          </TR>
          <xsl:apply-templates select="$table/row-set/row"/>
          <xsl:text>&#10;</xsl:text>
        </TABLE>
      </BODY>
    </HTML>
    <xsl:value-of select="sql:close($products)"/>
  </xsl:template>

  <xsl:template match="row">
    <xsl:choose>
      <xsl:when test="position() mod 2">
        <TR class="odd{col[@column-name='UnitsInStock']=0}">
          <xsl:apply-templates select="col"/>
        </TR>
      </xsl:when>
      <xsl:otherwise>
        <TR class="even{col[@column-name='UnitsInStock']=0}">
          <xsl:apply-templates select="col"/>
        </TR>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <xsl:template match="col">
    <TD>
      <xsl:value-of select="text()"/>
    </TD>
  </xsl:template>

</xsl:stylesheet>

I won't have too much time to spend on this for a little while.  I would
dearly love it if someone became interested enough to take over development
of this module.

-scott

Re: [xalan-2] org.apache.xalan.lib.sql

[Edwin Glaser <ed...@pannenleiter.de>]

Scott Boag wrote:
> I have written an experimental extension, somewhat based on an old LotusXSL
> extension, that can access a JDBC driver from an XSLT page.
>
> One of the keys to implementing this was to have the nodes implement a
> NodeSetFilter interface, so they can access information about the given
> XPath step they're being used for in getFirstChild and getNextSibling.  The
> XPath engine sets the NodeTest just before it calls these functions (if the
> given node implements the interface).

No Reply ???

As the maintainer of the rmdms.sourcforge.net application server,
I am looking for such a thing. In my opinion, this is the way to
go, but I was too lazy to do it this way.

Is this module still alive ?

-- 
Edwin Glaser -- edwin@pannenleiter.de