[JDEV] xdb_mysql question

[Sameer Verma]

PpDorApa wrote:

>i am trying to install jadc2s.. 4 that i have to change xdb_file to
>xdb_mysql..
>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
>appreciate..
>thanks in advice..
>Ppdorapa
>
>
>_______________________________________________
>jdev mailing list
>jdev at jabber.org
>http://mailman.jabber.org/listinfo/jdev
>  
>
http://download.jabber.org/contrib/

Here's the Readme:

Sameer

-- 
Sameer Verma, Ph.D.
Asst. Professor of Information Systems
San Francisco State University
San Francisco CA 94132 USA
http://verma.sfsu.edu/ 

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

xdb_sql 

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


***
Installation:

"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">
        <text>
          UPDATE user SET site = '$$domain$$',
                              page = '$$file$$'
          WHERE jid = '$$user$$'</text>
        <user>$$user$$</user>
        <bindvar tag="domain">$$domain$$</bindvar>
        <bindvar tag="file">$$file$$</bindvar>
	<purge>url-remove</purge>
      </querydef>

querydef:
  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:
xdb_get:

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

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