Introduction

Many (not all) modern relational databases allow you to program
directly in the database
itself. PostgreSQL is a star in
this regard, as it allows you
to write
database procedures in Perl, Python, Tcl, Java, Guile, pgSQL, and
more. You can write entire applications in this fashion, and doing so
simplifies or eliminates much of the tedium of solving tricky
concurrency and reliable transaction problems that databases solve as
a matter of course.

So how do you interact with such an application? Well, the nice
thing is, it doesn’t really matter. You can come at a database
application from many directions, all at once, and the database will
iron out all of the race conditions and other such nuisances for you.
So you might write some
simple shell
scripts to do some batch processing, another client interface
via JDBC, and bang at it with
Perl via
the Pg
module. When the database encapsulates all of the program logic,
all of these interfaces are just veneer, and can happily coexist with
little additional effort, and no adverse consequences whatsoever.

One particular application interface I’ve been interested in lately is
XForms. The W3C created
the XForms standard to address many of the shortcomings inherent in
designing application interfaces using current web standards. You can
use XForms today, the standard continues to be improved, and XForms will
be incorporated as a native component of the next version of XHTML.
This is a good time to get a head start on a technology of growing
importance. And amen to anything that promises to simplify web
application design and development.

Putting all this together: if we can find a way to glue XForms to a
database application, we may be able to create a web application almost
entirely declaratively. Maybe it’s just the way my mind works, or maybe
I’m just a relational database nerd, but I find this approach appealing.
The trick here is to translate SQL stored procedures into a language
that XForms understands: web services. There’s more than one way to
cook this meal, but my favorite magic sauce is the
WSO2 Data
Services Server, one of
many amazing products produced
by the good folks at WSO2. While my goal in this
article is to use web services to hook up with XForms, looking at
the entire WSO2 product portfolio may give you many other
reasons to be interested in the the world of web services, even if
XForms itself doesn’t really float your boat.

So the WSO2 Data Services Server will allow us to easily transform a
SQL stored procedure into a web service. Web services are XForms’
native language. There’s one remaining problem: how do we render an
XForm? Browsers themselves don’t generally understand XForms (yet).
There are a couple of plug-ins available for various browsers, but in my
experience, none of them are very mature. So what do we do? We’ll use
a server side application called Orbeon
Forms to translate our XForms application into an AJAX web app that
can be run in pretty much any standard browser. Someday, we can hope
browsers will speak native XForms, but until then we can fake it.

That’s a lot of ground to cover. Unfortunately, I don’t have room
for a detailed exposition on all of the particulars; instead I’m hoping
to pique your interest in a particular design methodology. This is not an
in-depth article about PostgreSQL, or Web Services, or XForms (each
deserve books in their own right), but rather a cursory explanation of
how they can work together. I don’t want to leave you completely
stranded, however, so I’ve attempted to collect enough reference
material to help you find your way. I’ve also written a couple of short
appendices to help you get the basic infrastructure up and running, in
case you’d like to play along. After all, it’s a lot more fun to
actually run an example than to simply read about it.

Building a PostgreSQL Application

Speaking of examples, what should our example application be? Once
you have put a few systems online, you realize that almost everything
begins with account management; so I thought that might be a fun place
to start here also. Our little app will allow us to register a new
username and password; to log in; to change our password; and to log
out. If you log in as the special administrative user we define, you
can also change other people’s passwords. Of course, a real account
management application would include many more operations than that, but
this should suffice for our current purpose.

If you’d like to install and run this example database application,
download the database setup files to your
database server. All of the database DDL statements are in
the ddl.sql file, which you might want
to refer to while reading the following discussion.

I created the
aforementioned functions
in
PostgreSQL’s PL/pgSQL
procedural language. We want to distinguish the procedures that
comprise our public interface from the tables and internal functions
that should remain inaccessible. We’ll do this using
two schemas:
one is called 'private', and the other is called 'interface'. The create.sh script
sets the permissions on the 'private' schema such that the tables and
functions it contains are only accessible by the database owner. The
permissions on our 'interface' schema are set to allow the user we call
'jq_public' to access them. The interface functions themselves must be
able to access our private data, of course. The 'SECURITY DEFINER'
qualification in our interface function definitions configures those
functions to run with the permissions of the user that defined the
function, i.e. the database owner. This is similar in principle to the
setuid bit in Unix. So our unprivileged 'jq_public' user can run the
functions, because they are in the 'interface' schema to which he has
permission. The functions can access the private data they need because
they run with permissions available to the database owner 'accadmin' we
use to create the database.

The PostgreSQL Setup appendix
explains how to install the provided sample account management database.
If you’d like to follow along with your own working example, this would
be a good time to install the database portion of our account management
application.

We can use the 'psql' command to connect to our new database as user
jq_public:

psql -U jq_public -d accounts -h localhost

Use the password you specified when creating the database. We didn’t
put any database objects in the 'public' schema, so once connected, you
need to set your search path before you can do anything. You can check
that your permissions are properly limited by trying to access objects
in the 'private' schema, and then the 'interface' schema.

Although there are certainly tables and functions in the private
schema, those objects should be invisible to the jq_public role. If you
perform the same operations as the database owner, you can see
everything. Try running some of the interface functions. If you like,
log in to the accounts database as the database owner or PostgreSQL
admin and then query the underlying tables to see how they are
affected.

Translating Database Procedures into a Web Service

We could very easily wrap these functions up in a small set of shell
scripts and we’d have a fully functional (though rather anemic) account
management application. Let’s do something a little more interesting
now, though, and turn these procedures into a web service.

There’s more than one way to create a web service, but if you are
starting with a database, the simplest way I’ve run across (so easy
it’s almost embarrassing) is to use
WSO2’s Data
Services Server (let’s call it DSS for short.)
The WSO2 folks make some of the nicest
software to use I’ve run into in some time. Using the DSS, we can
transform our PostgreSQL procedures into a web service with no coding
whatsoever; we only need to do a little configuration. You can use the
web admin interface to accomplish this; but I find that editing the
configuration files directly is just as easy, and also a little easier
to understand. Download the configuration
file I’ve included, and open it in your favorite editor.

The configuration file begins by describing the JDBC connection
parameters the Data Services Server needs to connect to our database.
We’ll connect as the 'jq_public' role we created with our setup script.
You’ll need to edit the configuration and modify the connection
parameters to use the password you previously defined for this user.
Because, as we saw earlier, this user has no permission to any database
objects beyond our interface functions, neither will our web service.

The rest of the configuration file describes how to map input values
to our stored procedures’ input parameters, and how to map our
procedures’ output values to XML element values. Our application is
rather simple, so our XML output is also quite simple, as you can see
in Figure 2, below. I don’t want to wander too far off track, so I’ll
refer you to the fine documentation for a complete description of how
this file works (you can probably figure my simple example out yourself,
when you see the output). The Data Services Server’s samples/dbs
directory also includes a number of illustrative examples of what you
can do.

Deploy this service by putting your updated accounts.dbs file into
the WSO2 Data Services Server’s
repository/deployment/server/dataservices directory, alongside the other
sample .dbs files you will find there. You can also upload a new
service description using the web based administrative interface
instead, if you prefer.

Assuming the DSS is running, it will hot-deploy (and update on edit) the
web service we described; so now, if you click on the services 'List'
link on the left side of the DSS web interface, you should see a new
service called 'Accounts' in the service list.

Figure 1. Service list

If you click the 'Try this service' link to the right of the service
name you can interact with this web service using a handy web interface.
Try registering a new account. Log in. Copy the session id that the
login service returns to the clipboard, and paste it into the logout
service to log out.

Figure 2. Try it

Once you have that all working, log into your DSS as your DSS admin
user. In the accordion menu on the left, open the 'Monitor' section.
There you will find a link to the 'SOAP Tracer' tool. This is a very
handy way to see the XML messages being passed back and forth when you
use your web service. You’ll need to know what these messages look like
for our next step, which is to connect our web service to XForms. The
XForms instance data we submit and consume must match the form of the
XML messages you can see here. Go ahead and turn the tracer on.

Go back to the 'Try this service' page and do something so that we can
see what the input and output messages look like. If you like, just for
grins, instead of using the web form interface, you might also try
calling the service with a parametrized URL. Expand the top left of
the web form, where it says 'Choose endpoint'. Copy the URL, and paste
it into the location tab of your browser (use a new tab so you can flip
back and forth). Append the function name and the input parameters to
the URL just like you’d append parameters to any old URL. The
RegisterAccount function might look like so, for example:

Paste this URL into your browser’s location bar and hit enter. Flip back to the SOAP tracer view in the DSS administrative interface, and you should see the SOAP messages that were passed back and forth.

Figure 3. SOAP Tracer

At this point you have successfully converted a database application
written as a collection of rules and stored procedures into a web
service. The approach we’ve takeen is essentially the inverse of the
usual database-agnostic but language specific application paradigm.
Instead, we’ve made a specific decision about our database and we’ve
used it to encapsulate all significant program logic, thereby allowing
us to be almost completely agnostic about the interface.

Consuming a Web Service with XForms

To wrap this up, we’ll go one step further, and briefly discuss one
possible way to build a web UI on top of our web service, namely using
XForms. Take a look at the SOAP messages we generated earlier (do a
page refresh of the SOAP tracer view to see recent traffic). The
messages generated by the RegisterAccount service should look as
follows, for example:

We use the structure of the XML messages sent to and from our web
service to construct analogous instance data within our XForms model.
For example, the view.xhtml file we'll see shortly contains some
instance data that looks like this:

Notice that the form of this instance data exactly resembles what we
observed in the DSS SOAP tracer. The namespace prefixes are different,
and they are defined elsewhere, but the XML structure is identical.

As mentioned previously, we cannot run XForms directly in our browser
(yet). Until we can, we’ll have to use a server side engine to deploy
the XForms documents we create as an AJAX application that we can run in any
modern browser. I’m going to use Orbeon, but other solutions, such as
Chiba, are also able to do this.

If you’ve deployed Orbeon as a war file into Tomcat, you’ll find the
application under Tomcat’s webapps folder, i.e. at
${CATALINA_HOME}/webapps/orbeon. From here, if you navigate to
WEB-INF/resources/apps, you’ll find the location where Orbeon XForms
applications live. Our example XForms application consists of
a small handful of files in addition to the XHTML
file which contains our XForms document. Make a new directory called
'accountmgt' in the Orbeon web applications area
(e.g. WEB-INF/resources/apps/accountmgt), and copy these files to this
new 'accountmgt' directory. Make sure the permissions of the files are
such that Tomcat can read them, of course.

Construct the URL of the application thus created by erasing the
string /home/ from the Orbeon Forms home page location, and appending
the name of the directory 'accountmgt' in its stead, like:

The Orbeon documentation and other online resources can explain our
application’s directory contents better than I can here. Briefly, the
page-flow.xml file tells Orbeon how to translate URL locations into
particular XForms documents. I baked our little application’s simple
work flow entirely into our single XForms document; but in an application
of any substance, the smart way to organize things would probably be to
split the application into multiple documents, and
to use
page-flow.xml to describe the application’s work flow.

The theme.xsl file is a copy of
${CATALINA_HOME}/webapps/orbeon/WEB-INF/resources/config/theme-simple.xsl.
If you copy and paste this XSLT theme into your application directory
and call it theme.xsl, your application will use this theme instead of
the theme configured by default. I’m doing this so you can see the
application without any Orbeon window dressing. Rename or remove
theme.xsl to run the application within the Orbeon web interface.

XForms is typically deployed by piggybacking on top of XHTML, which
is what we’ll do here. All of our important XForms stuff, then, is in
view.xhtml. Our leading <html ...> element sets the xhtml
namespace as the default namespace for our document, and defines other
namespace prefixes we’ll need throughout the rest of the document. Our
<head> element includes all of the XForms model data, while the
<body> element contains all of the UI elements.

Our XForms model consists of four main things:

Instance data. In our case, our instance data is structured to
match the SOAP messages we previously observed in the WSO2 Data
Services Server. The only difference is that we’ve moved the namespace
declarations to the top of our XHTML document, rather than embedding
them in each individual instance fragment. There is also an 'id'
attribute associated with each instance so that we can create XPath
references to those instances from other XForms elements.

Model item properties. I didn’t do a lot with model item
properties here, but I did use them to bind the values of certain
instance elements to each other so that they all have the same value.
For example, we want the sessionId to be the same whether we’re
calling the logout function (thereby submitting the 'logout-submit'
instance data) or the change password function (thereby submitting the
'change-password-submit' instance data).

Submission elements. This is where we call our web service. The
submissions we define here will be called when we click on the
'submit' elements that we put in our interface. The main thing our
submissions do is to send the right instance data to the appropriate
web service function, and then update corresponding instance data with
the data returned by our web service function’s response.

Some of our submission elements also describe how to conditionally
respond according to the values returned by the associated web service
function. For example, our login submission responds differently if
the login succeeds than if it fails.

CSS style definitions. It’s not really necessary to include
any of this, but I thought it might be illustrative to include a few
stylesheet examples.

It should come as no great surprise that our XForms visible elements
appear in our XHTML <body> element. Our XForms elements are
interspersed with standard XHTML elements. Our XForms elements are
identified using the XML namespace prefixes we defined in our XHTML
header above.

XForms controls are bound to instance data using XPath expressions.
Updating the value of an XForms control is synonymous with updating the
value of the underlying instance data. If the instance data changes,
the value displayed by the control will change; and if you change the
value in a control, you are changing the value of the instance data it
is bound to.

The visible state of our form changes according to the state of our
application. If we’re not logged in yet, we see a username and password
field, and are asked to either register or log in. If we log in, then
the visible form changes to allow us to change our password if we
choose, or we can log out. If you log in as the administrative user,
you can also change the password of users other than yourself. These
visible states are defined using <switch> and <case>
elements. Only one <case> element may be visible at a time. Our
submission elements indicate which case should be active according to
the results of the submission’s associated web service call.

Conclusion

'XRX' is the somewhat recent acronym describing the use of XQuery,
Rest, and XForms to create web applications. We’ve created an
application in a similar fashion; but instead of an XML database that
can run XQuery (e.g. eXist), we’re using a relational database and a web
services translation layer. I also happened to use SOAP instead of
REST, but either approach could work. Perhaps we could call this
approach SSX, for SQL, SOAP, and XForms (not to be confused with
Snowboard Supercross, heh.)

Clearly a real account management application would need more
functionality than I demonstrated here, but that wasn’t really the point
of this article. A web interface would also hopefully be styled to look
like something that was created less than fifteen years ago. Making
this application pretty and truly functional is left as an exercise for
the reader. Maybe now that you are able to register users, you should
consider writing an application that gives them something to do. If you
do write something, in the spirit of all good EULAs, I’d like to ask
that you not hold me responsible for any problems that might result
from using
any derived code to run a nuclear reactor, etc. Happy hacking.

Ron Peterson is a Network & Systems Manager at Mount Holyoke College in
the happy hills of western Massachusetts. He enjoys lecturing his three
small children about the maleficent influence of proprietary media
codecs while they watch Homestar Runner cartoons together.