Database Extensions

Introduction

The ObjectBox database extensions combine 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 version 1.1. The documentation for older versions of the database extensions can be found here.

Using the Database Extensions

The required extension classfiles are included in the ObjectBox distribution. In addition you will need to have the JDBC drivers for your specific database in your classpath. A list of drivers covering most common databases is maintained by SUN on this page.

If you are running the ObjectBox from the command line or as a servlet then the database extensions will be activated automatically. If you are running the ObjectBox Ant task you need to activate the extensions by specifying the extensions class: org.oXML.extras.db.DatabaseExtensions, for example:


<obox>
  <extension name="org.oXML.extras.db.DatabaseExtensions"/>
  ...
</obox>

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

In the following examples we use the MySQL Connector/J JDBC drivers, available from the MySQL web site.

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. You will also need to know the class name to use for your JDBC driver.

In the following 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/test</db:url>
    <db:driver>com.mysql.jdbc.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="99" name="'eisenstein'"/>

</o:program>

Here we pass parameters to the query in much the same way as we would to an o:XML procedure, the difference is that we're using the db:call element instead. In the query definition you can see the parameters appearing in the SQL statement - as expressions contained in curly braces. If a parameter is of type String, it is automatically 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

The result set from a query can be accessed as result parameters when the query is executed. These parameters are available in the same way as local variables within a result template, which is defined in a db:result element.

Let's define a SQL select statement with result template:


<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="99"/>

<-- could give as result: -->
<user userID="99">
  <name>eisenstein</name>
  <age>old</age>
</user>

What's new here is another element in the definition: db:result. It is 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 expressions in curly braces 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.

It is also possible to include o:XML instructions in the result template. A common use of this is to execute commands using the result parameters, or to instantiate used-defined types as in the following example:


<db:query name="getUser">
  <db:param name="userID" type="Number"/>
  <db:sql>select id, name, age from users where id = {$userID}</db:sql>
  <db:result>
    <o:eval select="User($id, $name, $age)"/>
  </db:result>
</db:query>

Calling getUser() will now return an instance of the User type (or one for each matching database row), which provides us with a nice way to achieve data type encapsulation.

The result parameters are set using simple type mappings for basic SQL types.

SQL Type Mappings

SQL Typeo:XML Type
BIT, BOOLEAN Boolean
BIGINT, INTEGER, SMALLINT, TINYINT Number
DOUBLE, DECIMAL, FLOAT, REAL, NUMERIC Number
CLOB, CHAR, VARCHAR, LONGVARCHAR String
all others String

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 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 we perform a nested query, from within a 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">FireFox</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 creating a db:query definition. This can be useful when you only want to use a query once in a program, and so don't really gain much from predefining it. Instead of passing parameters to the query, you simply embed o:Path expressions in curly braces:


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

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


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

Note the use of the db:quote(String) function - it serves to protect string values from SQL interpretation. It performs the same quoting of strings that is otherwise done automatically on String parameters to predefined queries.

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

Database Type Functions

The Database Extensions also provide for SQL queries to be defined and called as functions, which may be encapsulated within user defined type definitions. A database function uses the same syntax as db:query, with parameters, SQL and result elements. A notable difference is that type variables are available within the SQL query template.


  <o:type name="User">
    <o:variable name="id" type="Number"/>
    <o:variable name="name" type="String"/>

    <db:function name="Save">
      <db:sql>update users set name = {$name} where id = {$id}</db:sql>
    </db:function>

    <o:function name="SetName">
      <o:param name="name"/>
      <o:do>
        <o:do select="$this.Save()"/>
      </o:do>
    </o:function>
  </o:type>

String type variables are automatically quoted within the SQL statement, just like String parameters in a regular query definition.

Database functions can be defined independently, outside the scope of a type definition, though the most advantage is gained when they are used in conjunction with object oriented encapsulation.

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="..."/>

After a connection is defined any subsequent definition with the same name will be ignored and a warning message issued. If instantiation of a connection fails, then a warning message will be issued, and the next definition with the same name will be tried instead. This means that you can have different, alternative connections configured in the same code, which is often useful to allow code to be written that runs (albeit with warnings) in several enviroments without manual reconfiguration.

SQL Dialects and Multiple Queries

A common problem when writing cross-platform applications is maintaining compatibility with different databases, each having its own dialect of SQL. The Database Extensions allow you to define alternative queries for different dialects. Which query will be used is determined by the actual database connection. This makes it easy to write portable code, and to develop against different types of databases.

In the following example two database connections are defined, and a database query supporting two SQL dialects:


<db:connection dialect="mysql">
  <db:url>jdbc:mysql://localhost:3306/test</db:url>
  <db:driver>com.mysql.jdbc.Driver</db:driver>
</db:connection>
<db:connection dialect="sqlserver">
  <db:url>jdbc:sqlserver://localhost;port=1433;databaseName=test</db:url>
  <db:driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</db:driver>
</db:connection>
<db:query name="getFirstUser">
  <db:sql dialect="mysql">select * from users limit 1</db:sql>
  <db:sql dialect="sqlserver">select top 1 * from users</db:sql>
  <db:result><user userID="{$id}"/></db:result>
</db:query>
<db:call query="getFirstUser"/>

The connection in use at runtime will determine which variation of the query will be used. If the MySQL database is not available, then the SQL Server connection will be used and the customised query will be issued. The two variations are encapsulated within a single, portable db:query definition, but query variations can also be used within db:function definitions.

A default query can also be defined, either by omitting the dialect attribute or by setting it to the value sql.


<db:function name="getFirstUser">
  <db:sql>select * from users limit 1</db:sql>
  <db:sql dialect="sqlserver">select top 1 * from users</db:sql>
  <db:result><user userID="{$id}"/></db:result>
</db:function>

The dialect attributes on the db:connection elements are generally not necessary with most common databases, as the connection pool can determine the SQL dialect in use by asking the connection implementation. The following identifiers are used automatically if the dialect is not explicitly specified:

hsql

HSQL Database Engine

mysql

MySQL

pgsql

PostgreSQL

oracle

Oracle

sqlserver

Microsoft SQL Server

db2

DB2

informix

Informix

Nevertheless, any identifier can be used as long as it appears the same on the connection and the queries.

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/test</db:url>
  <db:driver>com.mysql.jdbc.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>

Connection Logging and Dynamic Values

To debug a database application it is often useful to log the sql queries. The connection element takes an optional log attribute that may be either "yes" or "no".


<db:connection log="yes">
   ...
</db:connection>

Database parameters such as url, driver, username and password can be determined at runtime - for example by reading them from a configuration file, using normal o:Path expressions contained in curly braces.


<-- define a MySQL connection using dynamic resource parameters -->
<db:connection>
  <db:url>jdbc:mysql://localhost:3306/{$dbname}</db:url>
  <db:driver>com.mysql.jdbc.Driver</db:driver>
  <db:username>{$dbusername}</db:username>
  <db:password>{$dbpassword}</db:password>
</db:connection>

Upgrading From ObjectBox v0.9 and v1.0

As of ObjectBox v1.1 the database extensions have undergone some changes. Notably the old value substitutions contained in angle brackets have been replaced with full o:Path expressions contained in curly braces. Also the format of passing parameters in nested db:call elements has changed.

The above example db:query would previously have looked like this:


<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>

To upgrade this to v1.1 format, all angle bracket parameters have to be replaced with curly braces and variable references. This is mostly a case of replacing [foobar] references with {$foobar} expressions. The exception to this rule is nested db:call elements, whose parameters (in this case userID) must not be enclosed in braces - nested db:call elements now look and act exactly like top-level ones!

The above query definition would be upgrade to v1.1 like this:


<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>