Flow Layer Database API

[Christopher Oliver]

I've just committed [experimental] changes that provide a simple 
database API for the Cocoon flow layer modeled after JSTL 
(http://java.sun.com/products/jsp/jstl). This will allow you to perform 
a database query in a flow script that produces a bean-like object for 
use by your presentation layer (i.e. you can pass it to sendPage*() or 
use it as part of your XMLForm model), without requiring a heavyweight 
object-relational mapping.

In addition, hopefully this will remove the need for ESQL and satisfy 
issues like the one raised here: 
http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=104533819604446&w=2

The cocoon object now supports a new function

    getConnection([String] dataSourceName);

that returns a Database connection object for any configured data-source 
in cocoon.xconf. The returned object provides two methods, analagous to 
JSTL's <sql:update> and <sql:query> tags:

   update([String] sql)

   query([String] sql, [Number] startRow, [Number] maxRows)

The update() function can be used to execute INSERT, UPDATE, and DELETE 
statements, as well as statements that create or remove database 
objects, such as CREATE TABLE and DROP TABLE. It returns the number of 
rows affected by the statement.

The query() function executes a SQL SELECT statement. You can limit the 
result with startRow and maxRows (which are optional). The object 
returned by query() contains the same properties as in JSTL:

rows             An array with a case-insensitive object per row
                 with properties matching column names and values
                 matching column values.
rowsByIndex      An array with an array per row with column values.

columnNames      An array with column names.

rowCount         The number of rows in the result.

limitedByMaxRows True if not all matching rows are included due to
                 reaching a specified max rows limit.


Here is an example script:

function test() {
    var conn = cocoon.getConnection("hsql");
    conn.update("create table employee (first varchar(15), last 
varchar(20), address varchar(30), city varchar(20), state varchar(20))");
    conn.update("insert into employee values ('joe', 'blow', '1 Main 
St.', 'Los Angeles', 'CA')");
    conn.update("insert into employee values ('joe', 'smith', '10 North 
St.', 'Sacramento', 'CA')");
    var emps = conn.query("select * from employee");
    sendPage("templates/employee.vm", {emps: emps})
}

where employee.vm looks like this:

?xml version="1.0"?>
<page>
 <title>Employees</title>
 <content>
#foreach ($emp in $emps.rows)
  <para>$emp.first $emp.last from $emp.city, $emp.state</para>
#end
 </content>
</page>
Let me know what you think.

Regards,

Chris