Sunday, April 05, 2009

Dashboards and Widgets in XQuery

Jim Fuller's recent article on Dashboards in XQuery makes a very good case for using XQuery for mashups generally. Jim's dashboard application reminded me of work I had been doing with my students last term on a configurable web page containing widgets to display NOAA weather data, RSS feeds, Google Maps and their own choice of data source. For this we used PHP with Simple XML, but to demonstrate the power of XQuery, I needed to show them the same application built on XQuery. It also seemed to me that the business dashboard would benefit from a design which split widget code from company-specific data.

The basic architecture of the approach here is to create a set of generalised widgets as XQuery functions and to define the specifics of the widget in a configuration file.

Here are a couple of configurations : jim.xml which is based on Jim's Dashboard example

and dsa.xml which is based on our course weather display.

The second example has a page refresh rate set, but I'm working on making the refresh rate widget- rather than page- specific using AJAX.

In the demo interface code, each widget links to its XQuery source code and hence to the code of called functions. Widget executions are timed and there is a link to the configuration file itself. Here is a basic main script:

import module namespace widgets = "http://www.cems.uwe.ac.uk/xmlwiki/widgets" at "widgets.xqm";

declare option exist:serialize "method=xhtml media-type=text/html omit-xml-declaration=no indent=yes
doctype-public=-//W3C//DTD XHTML 1.0 Transitional//EN
doctype-system=http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";

let $configuri := request:get-parameter("config",())
let $config := doc($configuri)/config
return
<html>
<head>
{$config/title}
<link rel="stylesheet" href="{$config/@css}" type="text/css"/>
</head>
<body>
<h1>{$config/title/text()} </h1>
{
for $section in $config/section
let $component := $section/*
return
<div>
<h3>{$section/@title/string()} </h3>
{widgets:render($section)}
</div>
}
</body>
</html>


Run it

There are a couple of ideas used in the code.

Each widget is defined by its own section in the configuration file. As a simple example, to configure the widget to get the date and time:

<section column="c2" title="Time in Bristol">
<datetime>
<format>EE dd/MM HH:mm</format>
</datetime>
</section>


The main script processes the configuration file, and each widget is rendered by calling a function of the same name. The code for this dispatching currently uses the eXist function util:eval(), within a util:catch call, to implement late binding:



declare function widgets:render($section as element(section)) as element(div) {
(: dispatch section to the matching function :)
let $component := $section/*[1]
let $widget := local-name($component)
let $function := concat("widgets:", $widget, "($component)")
return
<div>
{util:catch( "*", util:eval($function), <span>Missing or bad widget.</span>) }
</div>
};



A safer alternative would be to use typeswitch :

declare function widgets:render2($section as element(section)) as element(div) {
let $component := $section/*[1]
return
typeswitch ($component)
case element(datatime) return widgets:datetime($component)
case element(SQL) return widgets:SQL($component)
case element(monitor) return widgets:monitor($component)
....
default return
<div> Missing widget {local-name($component)} </div>
};


but this needs updating every time a new widget is added to the module.

To help with processing table data from different sources such as SQL, Excel and Google Spreadsheets, support functions transform these to a common XML structure. This standard form is then transformed to its final HTML with the XSLT included in the configuration.

For example here is the configuration for an SQL widget, reading data from a (readonly) MySQL database:

<section column="c2" title="Overpaid executives">
<SQL>
<username>cwstudent</username>
<password>cwstudent</password>
<database>jdbc:mysql://stocks.cems.uwe.ac.uk/Emp</database>
<query>select ename,sal from emp where sal > 2000 order by sal desc</query>
<xsl>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
<xsl:template match="table">
<table border="1">
<xsl:apply-templates select="row"/>
</table>
</xsl:template>
<xsl:template match="row">
<tr>
<td>
<xsl:value-of select="ename"/>
</td>
<td>
<xsl:value-of select="sal"/>
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
</xsl>
</SQL>
</section>


and its widget code:


declare function widgets:SQL($component as element(SQL)) as element (div) {
<div>
{
let $connection := sql:get-connection("com.mysql.jdbc.Driver", $component/database, $component/username, $component/password)
let $result := sql:execute($connection, $component/query, false())
let $table:= widgets:sql-result-to-table($result)
return transform:transform($table,$component/xsl/*,())
}
</div>
};


widgets:sql-result-to-table converts the sql result to a common internal XML format:


declare function widgets:sql-result-to-table($result as element(sql:result)) as element(table) {
<table>
{for $row in $result/sql:row
return
<row>
{for $col in $row/sql:field
return
element {$col/@name}
{string($col)}
}
</row>
}
</table>
};


This simplifies the XSLT transformation to the final HTML div element.

There's more to do on this framework, including adding support for access to passworded Google SS, Calendar and RSS feeds for which Jim's code will be handy. The framework also needs to support widgets from other modules and I'm not sure about the intermediate XML format and I'd like to use this more widely to layer the weather widgets as well. Getting the right split between widget code and configuration data is always tricky of course. However this framework seems quite useful and I intend to revisit some of the WikiBook examples to restructure as widgets.

I'll post the full code to the XQuery Wikibook shortly.

No comments: