From SQL to MVC - tutorials

How to map an arbitrary complex SQL query to a made-to-measure XML document, and how to expose this query as a component of a MVC architecture usable both in batch scripts and Web applications.

You need to know a little of SQL for this tutorial.

Description of the "purchase order" database

Troubleshootings
  • This tutorial has been tested with MySQL, PostgreSQL, and HSQLDB ; if you fail to connect to your database, notice that sometimes the user/password have to be included within the URL connection, sometimes supplied separately with a parameter. That is to say, replace jdbc:mysql://localhost:3306/po with jdbc:mysql://user:pwd@localhost:3306/po
  • The jar SQL connector for MySQL supplied with the distribution (v3.1.11) is bugged. Use instead the nightly build of v3.0
  • Oracle uses VARCHAR2 instead of VARCHAR, etc ; don't use it with RefleX. Future versions of RefleX should fix this.
  • See other known limitations.

For this tutorial, you are not compelled to install a database and a server. RefleX supplies one, HSQLDB, with the database of this tutorial ready to use.

However, if you do prefer using your own database, you'll have to create the relevant tables and populate them, add the Jar that contains the JDBC driver in the classpath, and run the CLI with the relevant URL connexion; for Web applications similar fitting-outs have to be done (please refer to the install section). You can use the scripts supplied for initializing MySQL or PostgreSQL databases, or write your own for other databases.

Here are the structure and the datas of our "purchase order" database :

PO
IDPNQTY
12343211
12346542
12349873
56781234
PRODUCTS
pnPRODUCTPRICECURRENCY
321Lawnmower138.95dollar
654Baby monitor29.99dollar
987Roquefort Cheese11.27euro
123RefleX full distribution0.00euro
456Kamasutra for Dummies2500.00yen

The HSQLDB database is in the doc/tutorial/sql/db/ directory and was populated with this initialization script (that you don't have to run).

First steps with SQL

This example shows a very simple mapping from SQL to XML. It simply creates the list of products in XML.

[doc/tutorial/sql/map/products.xcl]

<?xml version="1.0" encoding="iso-8859-1"?>
<!-- Invoke this active sheet with the following environment variables : - db-url : the URL connexion to the database - user : the user to connect to the database - password : its password --> <xcl:active-sheet xmlns:sys="http://ns.inria.org/active-tags/sys" xmlns:xcl="http://ns.inria.org/active-tags/xcl" xmlns:rdbms="http://ns.inria.org/active-tags/rdbms"> <!--the URL connexion to the database--> <rdbms:connect name="db" url="{ string( $sys:env/db-url ) }"
user="{ string( $sys:env/user ) }" password="{ string( $sys:env/password ) }"/> <!--the very simple SQL query--> <rdbms:select connection="{ $db }" name="products" query="SELECT * FROM PRODUCTS"/> <!--let's build a result document--> <xcl:document name="products-list"> <products> <xcl:for-each name="product" select="{ $products/* }"> <product pn="{ $product/PN }" price="{ $product/PRICE }"
currency="{ $product/CURRENCY }">{ string( $product/PRODUCT ) }</product> </xcl:for-each> </products> </xcl:document> <!--serialize to the standard output--> <xcl:transform output="{ $sys:out }" source="{ $products-list }"/> </xcl:active-sheet>

The RDBMS module allows to <rdbms:connect> to a source and submits a query with an SQL statement with the <rdbms:select> element.

The query result is browsed in a <xcl:for-each> statement. XPath expressions like $product/PRICE allow to select items with the name of the column of the table they come from. Notice the string() function for the one that is not set as an attribute value but in the content of the <product> : since items have a name, using this function avoid to get the content wrapped like this : <PRODUCT>Lawnmower</PRODUCT>. You can of course edit the source and observe the result get yourself if you remove that function.

To run it, open a console and at the prompt, type the following command from the RefleX home directory (remember that the (line cut) (line cut) icon means that you MUST NOT insert a line break; ensure that you didn't have copied "line cut" too) :

 $ java -Duser=sa (line cut)
     -Ddb-url=jdbc:hsqldb:file:doc/tutorial/sql/db/po (line cut)
     -cp libext/hsqldb.jar:reflex-0.4.0.jar org.inria.ns.reflex.ReflexCLI (line cut)
     -driver org.hsqldb.jdbcDriver run doc/tutorial/sql/map/products.xcl

Here is the XML result (for readability, a convenient indentation has been applied) :

<?xml version="1.0" encoding="UTF-8"?>
<products>
    <product currency="euro"   pn="123" price="0.0"   >RefleX full distribution</product>
    <product currency="dollar" pn="321" price="138.95">Lawnmower</product>
    <product currency="yen"    pn="456" price="2500.0">Kamasutra for Dummies</product>
    <product currency="dollar" pn="654" price="29.99" >Baby monitor</product>
    <product currency="euro"   pn="987" price="11.27" >Roquefort Cheese</product>
</products>

SQL to XML : complex mapping

This example shows a more complex mapping from SQL to XML : from a simple SQL request that accept a parameter, one build a made-to-measure XML document. Unlike other vendor specific technology, Active Tags allows :

  • to choose precisely which data becomes an element, which becomes an attribute, and which becomes text content,
  • to name yourself the elements name and attributes name,
  • to add arbitrary container elements,
  • to use functions or perform computations other than those permitted by SQL,
  • etc

Batch script

This script performs an SQL request and creates directly (without the help of XSLT) an arbitrary XML structure. This is a (very bad) structure somewhat simple but complex enough to prove that it can't be created directly by any RDBMS vendor proprietary mechanism :p (please let me know if I'm wrong, I'll create a new one that you won't be able to produce directly).

[doc/tutorial/sql/map/purchase-order.xcl]

<?xml version="1.0" encoding="iso-8859-1"?>
<!-- Invoke this active sheet with the following environment variables : - db-url : the URL connexion to the database - user : the user to connect to the database - password : its password - order-id : the order ID to query --> <xcl:active-sheet xmlns:sys="http://ns.inria.org/active-tags/sys" xmlns:xcl="http://ns.inria.org/active-tags/xcl" xmlns:rdbms="http://ns.inria.org/active-tags/rdbms"> <!--the URL connexion to the database--> <rdbms:connect name="db" url="{ string( $sys:env/db-url ) }"
user="{ string( $sys:env/user ) }" password="{ string( $sys:env/password ) }"/> <!--the SQL query, that has a parameter--> <rdbms:select connection="{ $db }" name="orders"
query="SELECT ID,PO.PN,QTY,PRODUCT,PRICE,CURRENCY FROM PO, PRODUCTS WHERE PO.PN = PRODUCTS.PN AND ID = ? ORDER BY CURRENCY"> <!--the parameter to transmit to the SQL query--> <xcl:param value="{ string( $sys:env/order-id ) }"/> </rdbms:select> <!--let's build a result document--> <xcl:document name="purchase-order"> <!--a litteral element with an attribute which value is computed--> <order id="{ string( $sys:env/order-id ) }"> <!-- browsing the result of the SQL query, grouping by currency xcl:group() return a list of list --> <xcl:for-each name="outer" select="{ xcl:group( $orders/*, string( CURRENCY ) ) }"> <!--build an element, which name is computed--> <xcl:element name="{ CURRENCY }"><!--give <dollar> or <euro>--> <!--each item of the inner list has the same currency--> <xcl:for-each name="inner" select="{ $outer/* }"> <!--a litteral, with computed attribute values--> <item part-number="{ $inner/PN }" quantity="{ $inner/QTY }" price="{ $inner/PRICE }"> <!--as "product" is a named item, it is forced to be text content--> <xcl:text>{ string( $inner/PRODUCT ) }</xcl:text> </item> </xcl:for-each> <xcl:fallback/> </xcl:element> </xcl:for-each> </order> </xcl:document> <!--serialize to the standard output--> <xcl:transform output="{ $sys:out }" source="{ $purchase-order }"/> </xcl:active-sheet>

The <rdbms:select> element accept parameters given by the <xcl:param> element.

The query result is browsed in a <xcl:for-each> statement where the items of the result are grouped (xcl:group()) by currency. When this function is involved, the current object is set to the first item of the inner group (relative XPath expressions are applied on the current object).

Open a console and at the prompt, type the following command from the RefleX home directory (note that the (line cut) (line cut) icon means that you MUST NOT insert a line break) :

 $ java -Dorder-id=1234 -Duser=sa (line cut)
     -Ddb-url=jdbc:hsqldb:file:doc/tutorial/sql/db/po (line cut)
     -cp libext/hsqldb.jar:reflex-0.4.0.jar org.inria.ns.reflex.ReflexCLI (line cut)
     -driver org.hsqldb.jdbcDriver run doc/tutorial/sql/map/purchase-order.xcl

...which will create the XML structure of the purchase order n°1234 (with indentation) :

<?xml version="1.0" encoding="UTF-8"?>
<order id="1234">
    <dollar>
        <item part-number="321" price="138.95" quantity="1">
            Lawnmower
        </item>
        <item part-number="654" price="29.99" quantity="2">
            Baby monitor
        </item>
    </dollar>
    <euro>
        <item part-number="987" price="11.27" quantity="3">
            Roquefort Cheese
        </item>
    </euro>
</order>

Web application

This Web application is configured to work with HSQLDB (read more here).

This HTTP service provides the same XML result as in the batch example.

[doc/tutorial/sql/map/web/WEB-INF/sql.web]

<?xml version="1.0" encoding="iso-8859-1"?>
<web:service xmlns:rdbms="http://ns.inria.org/active-tags/rdbms" xmlns:web="http://ns.inria.org/active-tags/web" xmlns:xcl="http://ns.inria.org/active-tags/xcl"> <!-- URL matched : [webapp]/index.xml --> <web:mapping match="^/index\.xml$"> <rdbms:connect name="db" url="{ string( $web:application/db-url ) }"/> <rdbms:select connection="{ $db }" name="orders"
query="SELECT ID,PO.PN,QTY,PRODUCT,PRICE,CURRENCY FROM PO, PRODUCTS WHERE PO.PN = PRODUCTS.PN AND ID = ? ORDER BY CURRENCY"> <xcl:param value="{ string( $web:request/order-id ) }"/> </rdbms:select> <xcl:document name="xml"> <order id="{ string( $web:request/order-id ) }"> <xcl:for-each name="outer" select="{ xcl:group( $orders/*, string( CURRENCY ) ) }"> <xcl:element name="{ CURRENCY }"> <xcl:for-each name="inner" select="{ $outer/* }"> <item part-number="{ $inner/PN }" quantity="{ $inner/QTY }" price="{ $inner/PRICE }"> <xcl:text>{ string( $inner/PRODUCT ) }</xcl:text> </item> </xcl:for-each> <xcl:fallback/> </xcl:element> </xcl:for-each> </order> </xcl:document> <xcl:transform source="{ $xml }" output="{ value( $web:response/@web:output ) }"/> </web:mapping> </web:service>

Running the Web application :

  • Start the server from a console :
     $ java -cp reflex-0.4.0.jar:xunit-0.4.0.jar:libext/hsqldb.jar (line cut)
        winstone.Launcher --httpPort=9999 --webroot=doc/tutorial/sql/map/web/
    (notice that you have to be in the RefleX home directory in order to run it with HSQLDB ; please check the URL connexion in the web.xml file)
  • Open a Web browser to http://localhost:9999/

You can run it like shown above, but if you want to use this Web application with your favorite RDBMS, feel free to change the driver and connection URL as appropriate.

As an exercice, you can change the XML output to HTML with XSLT. Exactly like in the previous example, and in the next...

Component-based architecture (MVC)

In the previous example, we have seen how to query a relational database with SQL to build a made-to-measure XML instance. However, the batch script and the HTTP service are almost the same (just compare the body of the XML code). The idea of a component based architecture is to design a common business-model that provides the same XML result, but that can be applied with the command line interface as well as in a Web application, or even embedded in a non-Web application. It is simply a separation of concerns.

To achieve this, we simply design our business-model in an external custom module. The module is then registered to the processor with a catalog so that it can be used like any other module. The benefits are that this module can be reused in any application. This architecture also enhances the independancy of the different parts of the application.

The module definition

We have to name our active tags, say <po:xml-order> for getting a purchase order and <po:xml-orders> for getting the list of purchase orders, and the namespace URI, say urn:acme-business-model:purchase-orders ; then, the module definition will just contain a macro for these tags. In the case where a Java code would be available, it would be possible to bind directly a Java class to each tag, but in our case, a simple macro is suitable, that is to say that our 2 custom tags are defined thanks to other tags.

The module definition is registered to the RefleX engine thanks to an Active Catalog ; both files are used in the batch application and in the Web application.

[doc/tutorial/sql/component/web/WEB-INF/po-module.exp]

<?xml version="1.0" encoding="iso-8859-1"?>
<exp:module version="1.0" target="po" xmlns:exp="http://ns.inria.org/active-tags/exp" xmlns:xcl="http://ns.inria.org/active-tags/xcl" xmlns:rdbms="http://ns.inria.org/active-tags/rdbms" xmlns:po="urn:acme-business-model:purchase-orders"> <!-- Usage : <po:xml-order name="..." db-url="..." order-id="..."/> --> <exp:element name="po:xml-order"> <rdbms:connect name="db" url="{ string( $exp:params/@db-url ) }"/> <rdbms:select connection="{ $db }" name="orders"
query="SELECT ID,PO.PN,QTY,PRODUCT,PRICE,CURRENCY FROM PO, PRODUCTS WHERE PO.PN = PRODUCTS.PN AND ID = ? ORDER BY CURRENCY"> <xcl:param value="{ string( $exp:params/@order-id ) }"/> </rdbms:select> <xcl:document name="xml-order"> <order id="{ string( $exp:params/@order-id ) }"> <xcl:for-each name="outer" select="{ xcl:group( $orders/*, string( CURRENCY ) ) }"> <xcl:element name="{ CURRENCY }"> <xcl:for-each name="inner" select="{ $outer/* }"> <item part-number="{ $inner/PN }" quantity="{ $inner/QTY }" price="{ $inner/PRICE }"> <xcl:text>{ string( $inner/PRODUCT ) }</xcl:text> </item> </xcl:for-each> <xcl:fallback/> </xcl:element> </xcl:for-each> </order> </xcl:document> <exp:exports> <exp:export name="{ string( $exp:params/@name ) }" value="{ $xml-order }"/> </exp:exports> </exp:element> <!-- Usage : <po:xml-orders name="..." db-url="..."/> --> <exp:element name="po:xml-orders"> <rdbms:connect name="db" url="{ string( $exp:params/@db-url ) }"/> <rdbms:select connection="{ $db }" name="orders"
query="SELECT DISTINCT ID FROM PO ORDER BY ID"/> <xcl:document name="xml-orders"> <orders> <xcl:for-each select="{ $orders/* }"> <order id="{ ID }"/> </xcl:for-each> </orders> </xcl:document> <exp:exports> <exp:export name="{ string( $exp:params/@name ) }" value="{ $xml-orders }"/> </exp:exports> </exp:element> </exp:module>

<exp:module> is used to define a module ; each custom active tag (<po:xml-order> and <po:xml-orders>) is defined thanks to <exp:element>. The $exp:params predefined property contains the attributes of the caller element (its attributes are used to pass parameters). The <exp:exports> element is used to set some properties in the caller active sheet, actually the XMLized result of the SQL query.

The catalog looks like this :

[doc/tutorial/sql/component/web/WEB-INF/catalog.cat]

<?xml version="1.0" encoding="iso-8859-1"?>
<cat:catalog xmlns:cat="http://ns.inria.org/active-catalog" xmlns:exp="http://ns.inria.org/active-tags/exp" xmlns:asl="http://ns.inria.org/active-schema"> <cat:resource name="urn:acme-business-model:purchase-orders" uri="po-module.exp"
selector="exp:module"/> <cat:resource name="urn:acme-business-model:purchase-orders" uri="po-schema.asl"
selector="asl:schema"/> </cat:catalog>

2 entries bound to the same URI are defined in the catalog ; a selector allows to distinguish them thanks to a qualified name :

  • the former indicates where is located the module,
  • the latter indicates where is located a schema that could check the validity of the custom elements (but this schema has not been made, the catalog entry is just here for demonstration, and this application won't use it).

Now that we have a module and a catalog, we can make a batch script and a Web application that use our custom active tags.

Batch script

The following scripts are invoking our custom module that performs the SQL request.

[doc/tutorial/sql/component/po.xcl]

<?xml version="1.0" encoding="iso-8859-1"?>
<xcl:active-sheet xmlns:sys="http://ns.inria.org/active-tags/sys" xmlns:xcl="http://ns.inria.org/active-tags/xcl" xmlns:po="urn:acme-business-model:purchase-orders"> <!-- get the purchase order --> <po:xml-order name="po" order-id="{ string( $sys:env/order-id ) }"
db-url="{ string( $sys:env/db-url ) }"/> <xcl:transform output="{ $sys:out }" source="{ $po }"/> </xcl:active-sheet>
 $ java -Dorder-id=1234 -Duser=sa (line cut)
     -Ddb-url=jdbc:hsqldb:file:doc/tutorial/sql/db/po (line cut)
     -cp libext/hsqldb.jar:reflex-0.4.0.jar org.inria.ns.reflex.ReflexCLI (line cut)
     -driver org.hsqldb.jdbcDriver -c doc/tutorial/sql/component/web/WEB-INF/catalog.cat (line cut)
     run doc/tutorial/sql/component/po.xcl
See also

The @exp:use-catalog attribute can be set on the root element instead of using the -c option of the CLI.

Compared to the script of the previous exemple, this one becomes very light. The custom tags are used simply like any other tags. Notice that the catalog is specified within the arguments of the CLI (the catalog is located in the directory of a Web application because it will also be used in the next example). The XML result for the purchase order is the same as the previous example, and can be view here.

The next script computes the list of purchase orders :

[doc/tutorial/sql/component/po-list.xcl]

<?xml version="1.0" encoding="iso-8859-1"?>
<xcl:active-sheet xmlns:sys="http://ns.inria.org/active-tags/sys" xmlns:xcl="http://ns.inria.org/active-tags/xcl" xmlns:po="urn:acme-business-model:purchase-orders"> <!-- get the list of orders in XML --> <po:xml-orders name="po-list" db-url="{ string( $sys:env/db-url ) }"/> <xcl:transform output="{ $sys:out }" source="{ $po-list }"/> </xcl:active-sheet>
 $ java -Duser=sa -Ddb-url=jdbc:hsqldb:file:doc/tutorial/sql/db/po (line cut)
     -cp libext/hsqldb.jar:reflex-0.4.0.jar org.inria.ns.reflex.ReflexCLI (line cut)
     -driver org.hsqldb.jdbcDriver -c doc/tutorial/sql/component/web/WEB-INF/catalog.cat (line cut)
     run doc/tutorial/sql/component/po-list.xcl

The XML result for the list of purchase orders available can be view here.

Web application

This Web application is configured to work with HSQLDB (notice that there is a parameter that refer to our catalog). This is the same Web application as before, but with our custom tags, with the XML results transformed in HTML, and that also computes the list of purchase orders in the home page.

Putting all the parts together :

  • a business model which was also used in the previous batch application,
  • a set of views that are XSLT stylesheets (doc/tutorial/sql/component/web/WEB-INF/xslt/orders.xsl, doc/tutorial/sql/component/web/WEB-INF/xslt/po.xsl),
  • a controler that handles the HTTP requests and invoke the right business model to transform it with the right stylesheet :

    [doc/tutorial/sql/component/web/WEB-INF/controler.web]

    <?xml version="1.0" encoding="iso-8859-1"?>
    <web:service xmlns:rdbms="http://ns.inria.org/active-tags/rdbms" xmlns:web="http://ns.inria.org/active-tags/web" xmlns:xcl="http://ns.inria.org/active-tags/xcl" xmlns:io="http://ns.inria.org/active-tags/io" xmlns:po="urn:acme-business-model:purchase-orders"> <web:init> <xcl:parse-stylesheet name="xslt-orders" source="web:///WEB-INF/xslt/orders.xsl" scope="shared"/> <xcl:parse-stylesheet name="xslt-po" source="web:///WEB-INF/xslt/po.xsl" scope="shared"/> <xcl:set name="db-url" value="jdbc:hsqldb:file:doc/tutorial/sql/db/po" scope="shared"/> </web:init> <!-- [webapp]/index.html --> <web:mapping match="^/index\.(xml|html)$"> <!-- get the list of orders in XML --> <po:xml-orders name="xml" db-url="{ $db-url }"/> <xcl:set name="xslt" value="{ $xslt-orders }"
    xcl:if="{ $web:request/@web:match = 'html' }"/> <xcl:transform source="{ $xml }" output="{ value( $web:response/@web:output ) }"
    stylesheet="{ $xslt }"/> </web:mapping> <!-- [webapp]/purchase-order.html?order-id=1234 --> <web:mapping match="^/purchase-order\.(xml|html)$"> <!-- get the purchase order --> <po:xml-order name="xml" order-id="{ string( $web:request/order-id ) }" db-url="{ $db-url }"/> <xcl:set name="xslt" value="{ $xslt-po }" xcl:if="{ $web:request/@web:match = 'html' }"/> <xcl:transform source="{ $xml }" output="{ value( $web:response/@web:output ) }"
    stylesheet="{ $xslt }"/> </web:mapping> <!-- [webapp]/this.xml --> <web:mapping match="^/this\.xml$"> <xcl:transform source="{ $this }" output="{ value( $web:response/@web:output ) }"/> </web:mapping> </web:service>

Running the Web application :

  • Start the server from a console :
     $ java -cp reflex-0.4.0.jar:xunit-0.4.0.jar:libext/hsqldb.jar (line cut)
        winstone.Launcher --httpPort=9999 --webroot=doc/tutorial/sql/component/web/
    (notice that you have to be in the RefleX home directory in order to run it with HSQLDB ; please check the URL connexion in the web.xml file)
  • Open a Web browser to http://localhost:9999/

You can run it like shown above, but if you want to use this Web application with your favorite RDBMS, feel free to change the driver and connection URL as appropriate.

Note that the output exist in 2 flavours : change the end of the URLs in your browser from .html to .xml.

Variant

The user-defined components are exposed as a set of active tags (<po:xml-order> and <po:xml-orders>) but they could also be exposed as XPath functions in the same way (po:xml-order() and po:xml-orders() for example). As an exercice, you could try to define macro-functions instead of macro-tags.

Please refer to the EXP specification for that purpose.

[TODO: A Web application with SQL updates]