16 July 2008 - AjaxToaster Version 1.0.0.RC2 released.
AjaxToaster is an Ajax application server which makes it easy to create RESTful web services for rich web client applications. It runs under any of the main Java servlet containers (tomcat/glassfish/websphere/jboss/jetty) and you can download the war file and release notes now.

9 July 2008 - XMLToaster Version 1.0.12 released. Another small enhancement - You may now choose the type of logging that you want XMLToaster to use - either standard Java logging or Log4J. The jar file is now available for download.

8 July 2008 - XMLToaster Version1.0.11 released. The runtime is now Java 5 and you may now specify a namespace for an XML message that does not have an associated XSD schema.

3 May 2008 - XMLToaster Version1.0.10 released. See the downloads page. This is a bugfix release which increases support for MySQL, improves error handling to give meaningful messages and some tweaks for compatibility with AjaxToaster.

3 May 2008 - We’ve put the new AjaxToaster code on the download page. This is a server which is designed to make it very easy for your ajax applications to get data from, and persist data to your databases. The goal is to provide a powerful but simple and easy to use SOA.

The downloads page contains a beta version of the ajaxToaster servlet that can be installed under any application server. Read the tutorial for information on how to set it up.

It also contains an alpha version of an “all in one” development environment for windows - this installer comes bundled with an application server and DBMS. After installing just go to http://localhost:8089 to access example applications and scripts and the ajaxToaster admin console. Apologies that this is only for windows at the moment, but a debian package is in the works.

News Archive…

Build a RESTful Web Service in Just a Minute - Part 2

This article is the followup to the post Build a RESTful Web Service in Just a Minute, which showed how to build a REST web service which returned a person record from a database.

This article will show you how you can write and deploy another one-minute web service - this one maintains the person table and in subsequent articles we will see AjaxToaster services that handle complex data structures such as you might find in real world applications.

Before we get started it’s assumed that you have AjaxToaster installed and that you have your environment set up (See the resources section for details). It will also be useful if you have created the retrieve service from the first article. (AjaxToaster is an Ajax application server which runs under Tomcat/JBOSS/Glassfish/Websphere - you need an AjaxToaster server to run any of these examples)

Creating the Person maintenance service

To create the service that will update the “person” entity in the database, create a file named “PersonUpdate.xts ” in a directory called ‘contacts’ under the Toaster services directory (If you are running Tomcat on windows this is likely to be found under c:\program files\Tomcat 6.0\webapps\toaster)
e.g

Add the following into PersonUpdate.xts.

  update table person using (insert,update)
  set personID = {person.id, key:1, uid:true, type:'integer'},
       firstname = {person.forename},
       lastname = {person.surname},
       dob        = {person.dateOfBirth};
  \g

This script tells AjaxToaster how to save the ‘person’ data into the database. The update statement is similar to a standard SQL update statement, but the phrase “using (insert, update)” means that it can insert OR update data into the ‘person’ database.
Full details of the update syntax can be found in the documentation.

The line “personID = {person.id, key:1, uid:true, type:'integer'},” tells it that personID is the key and that it is defined as an auto-generated key in the database.
If a row is inserted into the database the auto generated key will be populated in the return message.

This statement alone is enough to build the service. It can now be run as a non-restful service with parameters specified in the URL -
e.g.
http://localhost:8080/toaster/contacts/PersonUpdate?inputjson={person:{forename:’Fred’,surname:’Flintstone’,dateOfBirth:’1952-02-02′}}
(see the AjaxToaster parameters documentation for more details)

To turn it into a RESTful service we need to add two new mappings to the “AjaxToaster.urimappings” file. The first mapping is used when person data is being updated (the client application will know the ID of the person) and the second mapping is used when a new person is being created.

And thats it! Now in 7 lines of SQL, 2 lines of config, and zero lines of crazy Java annotations you have a fully operational RESTful service to update and create “person” entities.
If you want to see this service (and the one from the first article) in action then you can download the example code. The file “restful_part2_example.html” uses generic Ajax to invoke the service and POST updates and inserts to it.

Resources

Build a RESTful Web Service in Just a Minute

This article will show you how you can write and deploy a RESTful web service (that does something useful!) in around a minute.

It is planned to be the first part in a series of articles that will use AjaxToaster to demonstrate RESTful web services interacting with a relational database. In this first article we will show you a service that retrieves a simple data structure, in subsequent articles we will move onto a service that updates data and then services that handle complex data structures such as you might find in real world applications.

Before we start the stopwatch :-), it’s assumed that you have AjaxToaster installed in your environment and that you have our fictitious “contacts” database set-up. For instructions on how to do this, use the links in the resources section at the end of the article.

Ready, steady, go!!!

First you should create a new directory named “contacts ” in the toaster web applications’ “services” directory. On a standard Windows installation of Tomcat 6.0 this directory will be “C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps\toaster\services”. This is where we will create our web services.

Create a file named “service.config ” in your new directory and add the following content.

The content of this file sets the default database for all service script files in the directory. We are assuming that the ‘mydb’ database connection has been setup as described in the resources section.

Now create a file in the ‘contacts’ directory called “PersonService.stx ” with the following content:

Now edit the “AjaxToaster.urimappings ” file (which is in the “services” directory) and add the following line to define your RESTful URI:

When a client accesses a URI in the format “…/person/999” using an HTTP GET, the “PersonService.stx” script will be invoked and passed 999 as the “PERSONID” parameter. This will run the query above against the database and return person number 999 to you.

So that’s it! You are now ready to use your new RESTful web service. Point your web browser at a URL such as http://localhost:8080/toaster/person/1 and you should see the person data as shown below.

You can change the ‘person’ that you retrieve simply by changing the number on the end of the URL (use 1 through 5 for the person records that are setup by the database creation script).

The client can also decide to receive the data as a JSON message by either adding a parameter to the URL called “returnjson” (for example http://localhost:8080/toaster/person/1?returnjson) or by setting the HTTP request header “Accept ” to “text/json “.

In the next article we will look at how the client can set the HTTP request header and how we can update data in the database using AjaxToaster RESTful web services.

Resources

This is pretty straightforward and easy to understand, and overall we’re happy with the concept, but is not without it’s quirks and ambiguities. For instance, there isn’t a distinction between “services” and operations. There isn’t any way to tell how parameters are passed into the service on a POST - in the post body or as url parameters.

It is also not so simple to use the resulting object from javascript. Here’s an example of a service description:

"services": {
        "foo": {
            "transport": "GET",
            "target": "executeFoo.php",
            "parameters": [
                {"name":"paramOne", "type": "string"},
                {"name":"paramTwo", "type": "integer", "default": 5},
                {"name":"paramThree", "type": "integer", "optional": true}
            ]
        },
        "add": {
            "transport": "POST",
            "envelope": "JSON-RPC-2.0",
            "additionalParameters": {"type": "integer", "default": 0},
            "parameters": [
                {"type": "integer", "default": 0},
                {"type": "integer", "default": 0}
            ]
        }
    }
}

This SMD contains two services, “foo” and “add”. Note that the services aren’t in an array, so the properties for a particular service can only be referred to directly if you already know the name of the service e.g. alert(services.foo.transport);
Most of the time you won’t know this, so you have to resort to tricks - e.g using eval….

for (x in services) {
    servicedetails = eval("services."+x);
    alert(servicedetails.transport);
}

Complaints aside, we are looking forward to adding this to the next version of AjaxToaster, as it will make documenting and testing services much easier.

The Smell of Burning XML - Introducing XMLToaster

Java can be a very blunt tool when it comes to working with XML - there are lots of concepts to understand, lots of APIs to learn, and lots of code to write. Even when you have made it through the concepts and APIs you’ve only made it to the starting line!
Now you need to get some data to go with your XML, which often means learning yet another API to integrate with your relational database!

In this article I will explain how the XMLToaster can simplify the process of working in the XML and relational database worlds, without giving away any of the power and flexibility of either.

The examples in this article are based on the database shown in figure 1 which supports the XML messages we will be generating for an imaginary address book system.

Figure 1. The database schema

A simple example

For the first example of an XMLToaster query I will show you how to generate a list of names from the data in the “person” table.

   
xmlselect using noschema
select
    p.firstname     into xml:{addressBook.person.firstname},
    p.surname       into xml:{addressBook.person.surname}
from
    person p
order by
    surname  newValue throwNew xml:{addressBook.person}
   

If you are familiar with SQL you will see that it’s a normal SQL select statement with a little extra “syntactic sugar” sprinkled on top. In fact, I am going to assume that you’re familiar with the SQL “select … from.. order by” syntax so I will just describe the extra statements that tell the XMLToaster what the XML output is going to look like.

Starting from the top, the statement “xmlselect using noschema” indicates that the generated XML doesn’t have to conform to an XML schema. For simple queries this is normally fine and for complex messages where fine control over the generated XML is essential you can specify a schema (as we shall see in a later example).

The “into xml:{addressBook.person.firstname}” says where each person’s firstname will appear in the output XML. In this case it will go underneath <addressBook><person> and appear in an element called <firstname>.

Finally, “newValue throwNew xml:{addressBook.person}” after surname in the “order by” part of the select statement means that a new <person> element will be created in the output XML every time a new value of surname is encountered, so we’ll get a new <person> block with every new surname.

Here is the XML that is generated by the query…

   
<?xml version="1.0" encoding="UTF-8"?>
<addressBook>
  <person>
    <surname>Abbingdon</surname>
    <firstname>Alice</firstname>
  </person>
  <person>
    <surname>Bloomsbury</surname>
    <firstname>Bob</firstname>
  </person>
  <person>
    <surname>Chesterfield</surname>
    <firstname>Charles</firstname>
  </person>
</addressBook>
   

Example using some of SQL features and a child XML element

Since the XMLToaster syntax is similar to standard SQL you can write something a little more complex and can join tables, use SQL functions and restrict data with a normal where clause.
The query below expands the first example. Now the

xmlselect using noschema
select
    p.personId,
    concat(p.firstname, ' ', p.surname) into xml:{addressBook.person.name}
from
    person p
xmlunion
select
    p.personId,
    n.nicknameId,
    concat('A.K.A. ', n.nickname)       into xml:{addressBook.person.nickname}
from
    nickname n,
    person   p
where
    p.personId = n.personId
order by
    personId   newValue throwNew xml:{addressBook.person},
    nicknameId newValue throwNew xml:{addressBook.person.nickname}

In this query we are using the “concat” function to format the SQL data before we put it into the XML structure.

The query also contains the keyword “xmlunion” which is similar to the standard SQL “union” keyword, in that it pools the
results of the various select statements, but you don’t have to have the same resultset columns in each select clause.

The order by clause of this query contains two columns, and two “throwNew” keywords. This allows us to create a new “person”
element whenever the “personId” changes in the resultset, and to create a new “nickname” element underneath the correct
“person” element for each nickname that is returned.

When the query is run against our test database, XMLToaster produces the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<addressBook>
  <person>
    <name>Alice Abbingdon</name>
    <nickname>A.K.A. Ally</nickname>
  </person>
  <person>
    <name>Bob Bloomsbury</name>
    <nickname>A.K.A. Bobby</nickname>
    <nickname>A.K.A. Billy</nickname>
  </person>
  <person>
    <name>Charles Chesterfield</name>
  </person>
</addressBook>

An awkward example - When the tables and XML schema don’t match.

In this next example we are going to generate an XML message that lists the people and their home and work addresses.
In the database the home and work addresses are kept in the same table (personAddress), but the XSD schema that we have been asked to use has the two types of addresses separated in the XML heirachy. This can be awkward for traditional forms of relational to XML mapping because they like the XML data heirachy to closely match the database table relationship heirachy. Unfortunately in real life there are frequently differences - maybe the database was designed to different requirements than your application (and vice versa), or maybe it’s a legacy database. This can force you to perform unnecessary transformations of the XML you get from the database into the XML that you really require, which is costly in terms of development/maintenance effort and system performance.

OK, back to the code. This is how you can generate the list of people and their addresses.

xmlselect using schema 'addressList.xsd'
   /* Person Name - put person's name into <person> */
select
   p.personId,
   p.firstName         into xml:{addressBook2.person.firstname},
   p.surname           into xml:{addressBook2.person.surname}
from
   person p

xmlunion
    /* Home Address - if they have a home address put that into <person><home> */
select
   p.personId,
   a.line1             into xml:{addressBook2.person.home.line1},
   a.line2             into xml:{addressBook2.person.home.line2},
   a.city              into xml:{addressBook2.person.home.city},
   a.state             into xml:{addressBook2.person.home.state},
   a.zip               into xml:{addressBook2.person.home.zip}
from
   address       a,
   personAddress p
where
   p.addressId   = a.addressId
and p.addressType = 'HOME'

xmlunion
    /* Work Address - if the person has a work address, put that into <person><work> */
select
   p.personId,
   a.line1             into xml:{addressBook2.person.work.line1},
   a.line2             into xml:{addressBook2.person.work.line2},
   a.city              into xml:{addressBook2.person.work.city},
   a.state             into xml:{addressBook2.person.work.state},
   a.zip               into xml:{addressBook2.person.work.zip}
from
   address       a,
   personAddress p
where
   p.addressId   = a.addressId
and p.addressType = 'WORK'

/* notice that XMLToaster uses the "order by personId" to join all three unions together */
order by
   personId   newValue throwNew xml:{addressBook2.person}

In the query statement we have three different selects separated by the “xmlunion” keyword.
The three seperate select’s are then effectively joined together in the order by clause of the statement,
such that data with the same “personId” is processed by XMLToaster together.
In this way you can build in as much complexity to your selects as you require.

When the query is run against our test database, the following XML is produced:

<?xml version="1.0" encoding="UTF-8"?>
<addressBook2>
 <person>
   <firstname>Alice</firstname>
   <surname>Abbingdon</surname>
   <home>
     <line1>21 Pardee Lane</line1>
     <line2 nil="true" />
     <city>Oxford</city>
     <state>California</state>
     <zip>765231</zip>
   </home>
   <work>
     <line1>37 Edgewater Drive</line1>
     <line2 />
     <city>Cambridge</city>
     <state>Calfornia</state>
     <zip>655332</zip>
   </work>
 </person>
 <person>
   <firstname>Bob</firstname>
   <surname>Bloomsbury</surname>
   <home>
     <line1>7 Capwell Drive</line1>
     <line2 />
     <city>Melrose</city>
     <state>California</state>
     <zip>765783</zip>
   </home>
   <work>
     <line1>18 Pendleton Way</line1>
     <line2 />
     <city>Eastcote</city>
     <state>California</state>
     <zip>765656</zip>
   </work>
 </person>
 <person>
   <firstname>Charles</firstname>
   <surname>Chesterfield</surname>
   <home>
     <line1>15 Wistar Road</line1>
     <line2 />
     <city>Westbridge</city>
     <state>California</state>
     <zip>765322</zip>
   </home>
   <work>
     <line1>42 Ashton Avenue</line1>
     <line2 />
     <city>Northville</city>
     <state>California</state>
     <zip>76529</zip>
   </work>
 </person>
</addressBook2>

Hidden power

In this last example I am going to show how you can use XMLToaster to produce an XML message containing the results of a search against the database.
The requirement is to find a distinct list of people with either a firstname or nickname that matches the search name. A query to select this from our database would be

select distinct p.personid, firstname, n.nickname, surname
from person p left outer join nickname n on n.personid = p.personid
where p.firstname like 'Bob%' or n.nickname like 'Bob%'

This may sound straightforward, but what happens when the search criteria match both a persons’ firstname and their nickname? For instance if we search for “Bob*” and it matches someone with a firstname of “Bob” and nickname of “Bobby” they would appear in the results twice
This would result in the person appearing multiple times in the select resultset, something that we don’t want.
We could try using select distinct to cure the problem, because, assuming you need to know which nickname the match was against, the resultset rows will already be distinct. To solve this problem we can use the power of the XMLToaster “throwNew” keyword,
which gives us control over when a new element is created in the XML - in these circumstances, only when a new person is returned in the resultset.

Here is the select statement to perform the search (note that parameters are passed into XMLToaster queries using the “${XYZ}” syntax).

xmlselect using noschema
select
   p.personId,
   p.firstName             into xml:{searchResults.person.firstname},
   p.surname               into xml:{searchResults.person.surname}
from
   person  p
where
   firstName like '${NAME}%'
xmlunion
select
   n.personId,
   n.nicknameId,
   p.firstName             into xml:{searchResults.person.firstname},
   p.surname               into xml:{searchResults.person.surname},
   n.nickname              into xml:{searchResults.person.nickname}
from
   person   p,
   nickname n
where
   p.personId    = n.personId
and n.nickname like '${NAME}%'

order by
   surname,
   personId   newValue throwNew xml:{searchResults.person},
   nicknameId newValue throwNew xml:{searchResults.person.nickname}

When run with the “NAME” paramater set to “B” we get a message with a single instance of Bob, even though the search criteria matched his firstname and two of his nicknames.

<?xml version="1.0" encoding="UTF-8"?>
<searchResults>
 <person>
   <surname>Bloomsbury</surname>
   <firstname>Bob</firstname>
   <nickname>Bobby</nickname>
   <nickname>Billy</nickname>
 </person>
</searchResults>

Saving XML into a database

XMLToaster also provides a means of saving XML messages into a relational database without first transforming the XML, converting it into an object and then performing an Object-to-Relational mapping. This is achieved by specifying where in the input XML the data for each column of a table or parameter of a stored procedure comes from. These mapping specifications can be more complicated than the simple SQL select syntax used to get XML data out of a database, but are still very intuitive and powerful. You can see an example here.

System Performance (There’s a RAT in the system what am I gonna do?)

Traditional heirachical processing tends to have a naive brute-force approach to extracting data where each level of the heirachy is fetched from the database a child-at-a-time. This means that for each row in our “person” table, using traditional relational to XML mapping techniques would perform two singleton selects to get their home and business addresses. If we have 1000 rows in the person table, this means 2000 singleton selects executing against the database each time we want to produce the address list.
This doesn’t sound too bad in theory, after all the same amount of data is ultimately read from the disk, right? In practice it is nearly always orders of magnitude slower. Relational databases perform much, much better when extracting (say) 5000 rows in a single query than running 5000 queries that extract a row each.

Another significant drain on performance that heirachical processing fetches from the database a child-at-a-time, executing many small select statements on the database server rather than a few big statements.
For instance, in our “person” table, using traditional relational to XML mapping techniques, we would have to perform two singleton selects to get their home and business addresses. If we have 1000 rows in the person table, this means 2000 singleton selects executing against the database each time we want to produce the address list. The problem gets proportionally worse the more levels there are to the hierachy and can cripple an application.

Many of the performance problems in database-centric applications can be traced to this kind of iterative processing of database rows (often referred to as “Row At a Time” processing, or RAT’s). These tight loops of small statements continually thrown at the DBMS are one of the best ways to send your DBA insane (or at least a bit more insane), as they are difficult to mediate against unless you control the source code. Throwing hardware and memory (i.e $money) at this problem tends to result in linear improvements. Good indexing and caching can move some of the load from disk to cpu, but the ultimate fix is to change the coding to be “set oriented”. This requires far more complex back-end processing than the naive brute force approach to build the correct select statements, but can give exponential performance improvement.
This is exactly what XMLToaster is designed to do. By giving the developer the power to extract objects using unrestricted joins, we make use of the set processing performance boost provided by the relational database.

Conclusion

In this short introduction to XMLToaster I hope that I’ve been able to show that it is an easy to use, yet powerful tool for building XML messages from relational database data. Its’ simplicity lends itself to situations where you don’t need the overhead of a heavywieght toolset, for example where XML is just the data format and you don’t need to hold the data in a transient object. I also think that by being able to work directly to an XSD schema the development process for XML messaging systems is vastly improved. This allows you to skip any transformation steps that might have been required to convert the data extracted from the database structure into the structure that the message consumer requires. This should speed up development times and, just as importantly, decrease maintainence effort when things inevitably change.

An article like this we can only scratch the surface of what you can do with XMLToaster. For anyone who wants to learn more about using XMLToaster I would advise that they read the tutorial which is referenced in the resources section below. If you don’t find what you’re looking for there then feel free to contact us, or, for the ultimate documentation there is always the source code available at sourceforge ;)

Resources

• Sample code for this article
• Toaster Documentation Page
• SourceForge home of XMLToaster code

An example of updating a table with the new, simplified syntax: If this is the xml message to save to the database….

<message>
    <dish  id=12>
        <dishname>Dry  Toast</dishname>
        <dishtype>MAIN</dishtype>
        <prices>
            <retail>1.50</retail>
            <wholesale>0.05</wholesale>
        </prices>
    </dish>

    <dish  id=14>
          :
          etc
          :
    </dish>
</message>


Then the xmltoaster update statement you would need is…

 update table menu using (update)
 set
  dishid = {message.dish.@id, key:1 },
  dishname = {message.dish.dishname, type:'string'},
  dishtype = {message.dish.type, type:'string'},
  price = {message.dish.prices.retail }
 ;
 onError('Error updating the menu table!!.', abort, rollback);


Easy, huh?