[JDEV] xdb_mysql question

Sameer Verma sverma at sfsu.edu
Tue Aug 27 11:34:28 CDT 2002

PpDorApa wrote:

>i am trying to install jadc2s.. 4 that i have to change xdb_file to
>i install xdb_mysql but jabberd doesnt run it gave me this error:
>Error: invalid  tag type xdb_sql
>4 sure i am making something wrong.. if anyone can help me i will be
>thanks in advice..
>jdev mailing list
>jdev at jabber.org

Here's the Readme:


Sameer Verma, Ph.D.
Asst. Professor of Information Systems
San Francisco State University
San Francisco CA 94132 USA

$Id: README,v 1.5 2001/03/19 17:08:45 jpinon Exp $


Release 1.0

This an xdb implementation for Jabber 1.4 and later using an SQL database.
It is heavily based on xdb_mysql (by the Jabber Team) from Jabber 1.0.

xdb_sql is standalone, to keep MySQL/Postgresql/... details outside Jabber.

This code was developped by IDEALX (http://www.IDEALX.org), for
LibertySurf Télécom (http://www.libertysurftelecom.fr).

This program is Open Source software and is subject to specific
conditions of distribution and modification (see the COPYING file).


"Standard" installs of mysql and postgres should be found automatically.

Use xdb_sql.xml as config file for xdb_sql (it contains explanations
on how to use it from jabber.xml).  The database can be created with
the help of sample_database.sql for mysql or sample_database.pg.sql


In xdb_mysql, SQL requests were not hardcoded, but instead given in a
configuration file. Thus entire liberty is given for the tables layout.

The sample SQL tables and related XML config are from xdb_mysql,
with slight modifications for the new functionnalities.

Supported namespaces:
* jabber:iq:register
* jabber:iq:auth

* jabber:iq:roster
    Store user and group roster

* jabber:x:offline
    Store offline messages

* jabber:iq:filter
    Support the "from" condition, without action (blacklist)

General note on querydefs :

querydefs are given in the config file, and handle the SQL/XML mapping.
Usually, an xdb_get translate to a SELECT, and an xdb_set
translates to a DELETE or UPDATE, followed with an
INSERT or UPDATE. But it depends on your particular
database layout, and the concerned namespace.

In the <text> node, you have the SQL query, with special
words which will be substitued atr query time (these are
the $$foo$$ words).

<bindvar> substitutes a query parameter.
<bindcol> allows to retrieve an SQL result field (after a SELECT query)

Configuration and extensibility :

There's the old, powerful but painful way, and the new,
simple, generic way.

1. The old, powerful, painful way :

xdb_mysql, then xdb_sql, used both a config file and C code
to handle SQL/XML mapping.
To add a new namespace, go to xdb_sql_config.c :
* check if existing validators (validate_xxx)
  can fit your queries (validate_simple_user is often useful)
   * if yes :
      * good !
   * if not :
      * add new validator function
      * add validator declaration with others
* insert query name and validator ptr into s_query_table array
* create a new source file with the set/get functions
* insert the source name in Makefile.am
* insert functions declarations in xdb_sql.h
* insert functions ptrs and namespace into static_modules
* insert queries in the XML config file

2. The new, simple, generic way (dtd="2")

In xdb_sql, a new query config format has been added,
which allow to support moderatly complex processing on xml nodes 
without adding C code (before, a small part of code was needed to
fetch/store SQL params/results from/to XML nodes).
These are the <querydef> with attribute dtd="2".

On a xdb_set, it will try to execute N queries if N query datas
are supplied. On a xdb_set, the N tuples will be stored
in a given top node and optionnaly a per-tuple node.

Example 1:
xdb_set on a "jabber:iq:url" namespace to store an URL
associated with an user:

      <querydef name="url-set" dtd="2" type="set" namespace="jabber:iq:url">
          UPDATE user SET site = '$$domain$$',
                              page = '$$file$$'
          WHERE jid = '$$user$$'</text>
        <bindvar tag="domain">$$domain$$</bindvar>
        <bindvar tag="file">$$file$$</bindvar>

  name is mostly for humans and referencing;
  dtd will allow correct handling of the configuration;
  type is important for query dispatch, as is namespace;

text: SQL query template;
user: to substitute in text;
bindvar: will retrieve the cdata of the given tag in the
supplied xmlnode (this is used in an xdb_set), and substitute
this value in the text;
purge: name of a querydef executed prior to any set query.

Example 2:

      <querydef name="url-get" dtd="2" type="get" namespace="jabber:iq:url">
          SELECT site, page FROM user WHERE jid = '$$u$$'
           <x xmlns='jabber:iq:url'/>
        <bindcol tag="domain" offset="0"/>
        <bindcol tag="file" offset="1"/>

top-result: node created to contain query results
bindcol: will store the value found in field 0 of each tuple
as cdata for a node domain inserted in top-result.

It is possible to specify a node created for each tuple,
using tuple-node as child of the querydef.
Example: <tuple-node><item/></tuple-node>

Values will be inserted in the tuple-related node, and if
none exists, they will be inserted in the top-result node
(because the inner node is tried first).

More information about the JDev mailing list