Database Extensions

Introduction

The ObjectBox database extensions binds advanced enterprise application capabilities - self-adjusting connection pooling, transaction handling and RDB-to-XML mappings - with a really straightforward o:XML interface.

This article documents the database extensions of ObjectBox v0.9 to v1.0. The documentation for newer versions of the database extensions can be found here.

Using the Database Extensions

The required classfiles themselves are included in the ObjectBox jar file (obox.jar). In addition you will need to have the JDBC drivers for your specific database in your classpath.

If you are running the ObjectBox as a servlet then the database extensions will be activated automatically. If you are running the command-line version of ObjectBox you need to activate the extensions by including the following parameter: -e org.oXML.extras.db.DatabaseExtensions, for example:


  java org.oXML.engine.ObjectBox -e org.oXML.extras.db.DatabaseExtensions program.oml

For more information on running and configuring the ObjectBox, please see this page: Using the ObjectBox.

Namespaces

The database extensions and o:XML instructions are defined in different XML namespaces. For brevity we haven't included the namespace declarations in most of the examples, instead we consistently use the prefix db: for the database extensions, and o: for o:XML instructions. To use the examples, the prefixes have to be declared with their respective namespace URI's. The database extensions namespace URI is defined to be http://www.o-xml.com/db/", as the next example illustrates.


<o:program xmlns:o="http://www.o-xml.org/lang/">
  <db:connection xmlns:db="http://www.o-xml.com/db/">
    ...
  </db:connection>
</o:program>

Creating and Using a Connection

To access an SQL database you will need to have a valid username, password and URL to connect.

In the following very simple example, we create a connection to a MySQL database and execute a SQL statement. It shows how to define the connection and a query, and how to call the query with parameter values.


<o:program>

  <db:connection>
    <db:url>jdbc:mysql://localhost:3306/theDataBase</db:url>
    <db:driver>org.gjt.mm.mysql.Driver</db:driver>
    <db:username>fred</db:username>
    <db:password>secret</db:password>
  </db:connection>

  <db:query name="setUserName">
    <db:param name="userID" type="Number"/>
    <db:param name="name" type="String"/>
    <db:sql>update users set name = [name] where id = [userID]</db:sql>
  </db:query>

  <db:call query="setUserName" userID="666" name="'belzebuub'"/>

</o:program>

Here you pass parameters to the query in much the same way as you would to an o:XML procedure, the difference is that we're using the db:call mapping instead. In the query definition you can see which parameters appear where in the SQL statement - they're surrounded by angle brackets. If a parameter is of type String, it will be quoted before the statement is executed.

Still, this is not much fun since we want to do more than just execute queries. How about getting some results back?

Query Results

Let's try to define a SQL select statement:


<db:query name="getUserDetails">
  <db:param name="userID" type="Number"/>
  <db:sql>select id, name, age from users where id = [userID]</db:sql>
  <db:result>
    <user userID="[id]">
      <name>[name]</name>
      <age>[age]</age>
    </user>
  </db:result>
</db:query>

<db:call query="getUserDetails" userID="666"/>

<-- could give as result: -->
<user userID="666">
  <name>belzebuub</name>
  <age>infinite</age>
</user>

What's new here is another element in the definition: db:result. It will be used as a template for the results created - each row will result in a user element with its content set from the database (though in this case we're unlikely to ever get more than one row returned). Here, the angle brackets refer to the column names, or result value names, of the executed query. They can be used to populate either attribute values or text sections, or any combination of the two.

Nested Queries

What we have so far is an almost complete database-to-XML information mapping tool. But in order to make it really useful, we will have to overcome at least one more problem, that has to do with data structure. Since relational databases are made up of interrelated, flat tables, and since XML is really much better with hierarchically structured data than it is with relations, we have a pair of conflicting data paradigms. Since we don't want to 'dumb down' our XML by simply producing vast amounts of flat, unstructured data we need a way of binding related data together. To some degree this can be done with SQL joins and views, but it really only works well for one-to-one relations. But what if you perform a nested query, from within your result template? It might look something like this:


<-- first define a query to select user preferences from a separate table -->
<db:query name="getUserPreferences">
  <db:param name="userID" type="Number"/>
  <db:sql>select name, value from preferences where user_id = [userID]</db:sql>
  <db:result>
    <preference name="[name]">[value]</preference>
  </db:result>
</db:query>

<-- then call it from inside our user details -->
<db:query name="getFullUserDetails">
  <db:param name="username" type="String"/>
  <db:sql>select id, name from users where name = [username]</db:sql>
  <db:result>
    <user userID="[id]">
      <name>[name]</name>
      <preferences>
	<db:call query="getUserPreferences" userID="[id]"/>
      </preferences>
    </user>
  </db:result>
</db:query>

<-- possible query and result -->
<db:call query="getFullUserDetails" name="'mars'"/>

<user userID="123">
  <name>mars</name>
  <preferences>
    <preference name="browser">phoenix</preference>
    <preference name="editor">emacs</preference>
  </preferences>
</user>

Note two important details here; firstly the parameter passed to the second query (the [id] parameter) is taken from the result of the first one - it doesn't have to be passed in as an argument! Second, the first query can return one, five, zero or any number of rows. So what we have now is a one-to-many relation, producing structured XML datasets. Not bad!

SQL Execute

There's another way to use the database extensions to execute SQL queries without defining a query. This can be useful when you only want to use a query once anyhow, and so don't really gain much from predefining it. Instead of passing parameters to the query, you embed dynamic data using attribute escaping - or in other words, by putting o:Path expressions in curly braces:


  <db:execute sql="update users set status = {$userStatus} where id = {$userId}"/>

Here, if you want to have results returned, you simply specify your result template inside the db:execute statement:


  <db:execute sql="select id from users where name = {db:quote($userName)}}">
    [id]
  </db:execute>

Note the use of the db:quote() function - it serves to protect string values from SQL interpretation.

There is no other limitation on the result templates used with db:execute than that they cannot contain nested execute statements.

Multiple Connections

Sometimes you will want to access more than one database from the same program. With the Database Extensions you can define any number of different connections, name them, and use them individually with your queries and execute commands. The default name, if none is specified, is db:connection. This is also the default value of the optional connection attribute for both query and execute.


<-- define a named connection -->
<db:connection name="ex:users">
  ...
</db:connection>

<db:query connection="ex:users">...</db:query>

<db:execute connection="ex:horses" sql="..."/>

Connection Pooling and Transactions

To achieve resilience, performance and fault tolerance in your o:XML applications, you will need two more things: connection pooling, and transaction handling. The Database Extensions can take care of this; simply specify how large a pool you want, and how many automatic transaction retries, when defining your connection.

The connection pool will try to maintain a steady number of free resources. This is extremely useful when the ObjectBox runs multi-threaded, as is the case with the ObjectBox Servlet. Every new request will execute an o:XML program in a separate thread, which will require a connection. The pool monitors the number of free resources, and will automatically create new ones if the lower threshold is reached. This means that the next request will never have to wait for resources to be freed up, or for a new connection to be created, before it can execute database queries. The pool will also close down connections if, after a peak in the number of requests, the higher threshold of free connections is reached.

With the current release of the Database Extensions, transactions are automatically committed when a SQL statement (insert, update etc) is successfully executed. If an error occurs, an exception is thrown that will terminate the program. This stops a program from carrying on after a fatal error, however database errors are sometimes transient in nature. A timed-out or faulty connection, temporary network problems and the like could often be overcome by simply retrying the query using a different connection. To avoid having this sort of errors disrupt your services, you can specify the number of transaction retries in the connection definition.

Comprehensive transaction control including isolation levels, commit and rollback commands will be incorporated in a future release of the o:XML Database Extensions.


<-- define a pooled connection on which SQL statements are 
     automatically retried if there is a connection error -->
<db:connection>
  <db:url>jdbc:mysql://localhost:3306/theDataBase</db:url>
  <db:driver>org.gjt.mm.mysql.Driver</db:driver>
  <db:username>fred</db:username>
  <db:password>secret</db:password>
  <db:pool minFree="1" maxFree="5"/>
  <db:transaction retry="3"/>
</db:connection>