Of Jabber Transports and XDB_SQL

xdb_sql and transports, how to make them work together.

Here is a plain old hack to make xdb_sql work with transports other than yahoo. Most of it is inspired by the yahoo transport anyway and the example works with icqv7t (great work alex)

Let's start off by creating the table in mysql

		CREATE TABLE icq (
		  username VARCHAR(32) PRIMARY KEY,
		  icq_id    VARCHAR(32) NOT NULL,
		  icq_pass  VARCHAR(32) NOT NULL
		);
	

then you need to create the query defs in xdb_sql.xml try adding the following island. Since we are using v2 queries we don't need to code any validation functions

<!-- icq set query -->
	<querydef name="icq-set" dtd="2" type="set" namespace="icqtrans:data">
	<text>
	INSERT INTO icq VALUES ('$$user$$', '$$id$$', '$$pass$$')
	</text>
		
	<user>$$user$$</user>
	<bindvar attrib="id">$$id$$</bindvar>
	<bindvar attrib="pass">$$pass$$</bindvar>
	<purge>icq-remove</purge>
	</querydef>

<!-- icq removal query -->
	<querydef name="icq-remove" dtd="2" type="remove" namespace="icqtrans:data">
	<text>
	DELETE FROM icq where username='$$user$$'
	</text>
	<user>$$user$$</user>
	</querydef>

<!-- icq get query -->
	<querydef name="icq-get" dtd="2" type="get" namespace="icqtrans:data">
	<text>
	SELECT icq_id, icq_pass  FROM icq WHERE username = '$$user$$'
	</text>
		
	<user>$$user$$</user>
	<top-result>
	<logon/>
	</top-result>
	<bindcol attrib="username" offset="0"/>
	<bindcol attrib="password" offset="1"/>
	</querydef>

Then you need to make changes to the actual code. Since it would be nice if the transport continued to work with xdb_file i have used an #ifdef, so that means you will need to add a -Dsql to your makefile if you want to use it with xdb_sql. The actual changes are to jabberd/utils.c and jabbered/unknown.c the files are attached. Please compare them with the original. Couple of new functions have been added to utils c and unknown.c gets a few lines of code.

Rebuild the transport and restart it.

all the best