jabberd14 HOWTO: writing a handler definition for xdb_sql

The xdb_sql component of jabberd14 is used to use a PostgreSQL or MySQL database as storage backend. But while SQL uses tables to manage data, jabberd14's internal representation of data is XML. Therefore the xdb_sql component has to do some sort of mapping between the two representations.

To be able to adopt jabberd14 freely to your existing database layouts, the mapping is freely configurable from within the configuration file. The default configuration file already contains definitions for all data, that is used by the base server itself. But you might want to configure your additional definitions if you either want to adapt the mapping to your existing user-base or if you want to store data of some add-on component using the xdb_sql component. In this mini-HOWTO I want to explain how such handlers gets defined by taking the JIT component as an example.

Before you can define your own mapping, you have to find out how the internal XML representation of the data inside jabberd14 looks like. The easiest way to get this information is to use the xdb_file storage module, that stores data by just writing the internal XML data to XML files. So you have to just open the XML file of a user, that has stored data in this namespace, to see the XML representation of the data you want to write a handler for.

So let's have a look at the data, that is written for a user using the JIT component:

<?xml version="1.0"?>
<xdb>
  <query xmlns="jabber:iq:register" xdbns="jabber:iq:register">
    <username>47110815</username>
    <password>secret</password>
  </query>
  <query xmlns="jabber:iq:roster" xdbns="jabber:iq:roster">
    <item jid="12345678"/>
    <item jid="300112233"/>
    <item jid="211081599"/>
  </query>
</xdb>
	

The XML declaration (<?xml version="1.0"?>) and the root element (<xdb/>) is created by xdb_file as a wrapper for all stored data, so it is not of interest to us. What we have to look at are the second level elements. In our example we have two of them. The xdbns attributes on this element define the namespaces, that are used, and that we have to define handlers for. Therefore we see by this example, that JIT uses two namespaces to store data. The one is 'jabber:iq:roster' where we already have a compatible handler in jabberd14's default configuration. The other one is 'jabber:iq:register'.

So let's now start writing the handler for 'jabber:iq:register'. First let's think about the database layout we want to have. I will use a table called 'icqlogins' with the following four fields: user, realm (user and realm building a JID), uin (the ICQ number of the user), and password (containing the ICQ password related to the uin).

We start defining the handler used to get data from the SQL database. This handler is defined in the <get/> element and contains two parts. The first part wrapped by the <query/> element containing the SQL statement to select the data from the SQL database, and the <result/> element defining the created XML for the result.

The SQL statement to query the data from the database is not very complicated, what we basically have to do is the following: SELECT uin, password FROM icqlogins WHERE user='userPartOfJID' AND realm='domainPartOfJID'. So how do we get the user part and the realm part of a JID? Well both are separated by a '@' sign, so we just have to use some SQL string handling to separate them. (The functions used are a bit different on PostgreSQL, please have a look at the example PostgreSQL configuration to see how it can be done there, my examples will use MySQL syntax, as it seems more users are using MySQL servers.)

To get the part in front of the '@' sign we use: SUBSTRING('JID', 1, INSTR('JID', '@')-1) (we check the position of the '@' sign and then get the substring up to one character before this position). To get the part after the '@' sign we do something similar: SUBSTRING('JID', INSTR('JID', '@')+1). So inserting these two snippets into the above statement we get:

SELECT uin, password FROM icqlogins WHERE user=SUBSTRING('JID', 1, INSTR('JID', '@')-1) AND realm=SUBSTRING('JID', INSTR('JID', '@')+1).

So what we now still need to know is how to get the JID we want to access data for. To explain this, I have to tell a bit more about how the data is passed to the xdb_sql component inside the server. The magic is, that it gets wrapped into an <xdb/> element (in case of a store request, in case of a fetch request the component just gets the wrapper element having no content) and that this element holds the JID as the value of its to attribute. We can access this request the xdb_sql component gets with a subset of xpath with the wrapper element as the context node. So to access the value of to attribute we just have to use the xpath expression 'attribute::to' and mark this as an xpath expression by inserting it in curly brackets: '{attribute:to}'.

With this knowledge we can not complete the SQL get request:

SELECT uin, password FROM icqlogins WHERE user=SUBSTRING('{attribute:to}', 1, INSTR('{attribute:to}', '@')-1) AND realm=SUBSTRING('{attribute:to}', INSTR('{attribute:to}', '@')+1).

What we get with this SQL request is a record containig four fields. We now have to format this back to the XML the server expects. Let's do this step by step again. What we need to return is the following:

<query xmlns="jabber:iq:register" xdbns="jabber:iq:register">
<username>content of 1st field</username>
<password>content of 2nd field</password>
</query>

We just have to expant this template with expressions where to insert the data, that xdb_sql understands. This is done by the <value xmlns='http://jabberd.org/ns/xdbsql' value='field'/> element which will insert the value of the field number field (counting starts with 1).

Therefore the completed template is:

<query xmlns="jabber:iq:register" xdbns="jabber:iq:register">
<username><value xmlns='http://jabberd.org/ns/xdbsql' value='1'/></username>
<password><value xmlns='http://jabberd.org/ns/xdbsql' value='2'/></password>
</query>

Well we completed the mapping from SQL to XML. What is left to implement is the mapping in the different direction. This mapping is broken in two parts. The first part is used to delete the data that is already in the database (defined using the <delete/> element in the configuration), and the second part is to store the new data (defined using the <set/> element in the configuration).

First deletion: The statement we need is the following: DELETE FROM icqlogins WHERE realm=SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1) AND user=SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1). No explanations should be needed for this definition, all what is used in this statement is already explained above while defining the SELECT SQL statement.

More interesting is the part where the data is stored to the table. What we basically have to do is the following: INSERT INTO icqlogins (user, realm, uin, password) VALUES (SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1), SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1), 'uin', 'password'). The uin and password we again select from the data the xdb_sql component gets using xpath statements. To get the uin, we have to select the content of the <username/> element, the xpath expression for this is 'register:query/register:username/text()' (we select the <query/> element in the jabber:iq:register namespace which is bound to the register namespace prefix, then the <username/> element inside this query in the same namespace, and then the text node inside this element). And we do the same for the password 'register:query/register:password/text()'.

Therefore the completed statement now is: INSERT INTO icqlogins (user, realm, uin, password) VALUES (SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1), SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1), '{register:query/register:username/text()}', '{register:query/register:password/text()}').

We now have finished the handler:

<handler ns='jabber:iq:register'>
    <get>
        <query>SELECT uin, password FROM icqlogins WHERE user=SUBSTRING('{attribute:to}', 1, INSTR('{attribute:to}', '@')-1) AND realm=SUBSTRING('{attribute:to}', INSTR('{attribute:to}', '@')+1)</query>
        <result><query xmlns="jabber:iq:register" xdbns="jabber:iq:register"><username><value xmlns='http://jabberd.org/ns/xdbsql' value='1'/></username><password><value xmlns='http://jabberd.org/ns/xdbsql' value='2'/></password></query></result>
    </get>
    <set>INSERT INTO icqlogins (user, realm, uin, password) VALUES (SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1), SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1), '{register:query/register:username/text()}', '{register:query/register:password/text()}')</set>
    <delete>DELETE FROM icqlogins WHERE realm=SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1) AND user=SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1)</delete>
</handler>
	

Well above I just sait, that 'register' is the namespace prefix of the 'jabber:iq:register' namespace. I have to explain that a bit: This binding of the prefix to the namespace is defined inside the xdb_sql configuration using the following element:
<namespace prefix='register'>jabber:iq:register</namespace>.
As we already have expressions for the jabber:iq:register namespace in the default configuration we did not have to define the prefix again. But if you have to define statements for completely new namespaces, you would have to define your own prefixes at that place in the configuration.

If we would now just insert the new handler into the configuration, we would get a problem: There is already a handler for the jabber:iq:register namespace. The problem why we cannot use this existing handler is, that the existing handler is not compatible with the data the JIT transport stores. - While the handler we just defined cannot be used for anything else but the data JIT transport stores. So we have to tell the jabberd14 server to use the new definition only for JIT and continue using the old definition for anything else.

We do this by adding a new xdb_sql instance to the configuration file, that is only responsible for handling requests of the JIT component. This is done by adding the domain of the JIT transport as the content of the <host/> element of the xdb_sql instance. So our reconfiguration is done by adding the following section to the jabber.xml configuration file (assuming that the transport uses the domain 'icq.example.com':

<xdb id="xdbsql4jit.localhost">
  <host>icq.example.com</host>
  <load>
    <xdb_sql>@libdir@/libjabberdxdbsql.so</xdb_sql>
  </load>
  <xdb_sql xmlns="jabber:config:xdb_sql">
    <driver>mysql</driver>
    <mysql>
      <user>jabber</user>
      <password>secret</password>
      <host>localhost</host>
      <database>jabber</database>
    </mysql>
    <nsprefixes>
      <namespace prefix='register'>jabber:iq:register</namespace>
      <namespace prefix='roster'>jabber:iq:roster</namespace>
    </nsprefixes>
    <onconnect>SET NAMES utf8</onconnect>
    <handler ns='jabber:iq:register'>
	<get>
	    <query>SELECT uin, password FROM icqlogins WHERE user=SUBSTRING('{attribute:to}', 1, INSTR('{attribute:to}', '@')-1) AND realm=SUBSTRING('{attribute:to}', INSTR('{attribute:to}', '@')+1)</query>
	    <result><query xmlns="jabber:iq:register" xdbns="jabber:iq:register"><username><value xmlns='http://jabberd.org/ns/xdbsql' value='1'/></username><password><value xmlns='http://jabberd.org/ns/xdbsql' value='2'/></password></query></result>
	</get>
	<set>INSERT INTO icqlogins (user, realm, uin, password) VALUES (SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1), SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1), '{register:query/register:username/text()}', '{register:query/register:password/text()}')</set>
	<delete>DELETE FROM icqlogins WHERE realm=SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1) AND user=SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1)</delete>
    </handler>
    <handler ns="jabber:iq:roster">
      <get>
	<query>SELECT xml FROM roster WHERE realm=SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1) AND user=SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1)</query>
	<result><value xmlns='http://jabberd.org/ns/xdbsql' value='1' parsed='parsed'/></result>
      </get>
      <set>INSERT INTO roster (user, realm, xml) VALUES (SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1), SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1), '{roster:query}')</set>
      <delete>DELETE FROM roster WHERE realm=SUBSTRING('{attribute::to}', INSTR('{attribute::to}', '@')+1) AND user=SUBSTRING('{attribute::to}', 1, INSTR('{attribute::to}', '@')-1)</delete>
    </handler>
  </xdb_sql>
</xdb>