How database inconsistencies lead to a hack which makes my chest swell with pride.

I came accross an interesting problem this morning when I was trying to get the rowcount of a Sqlite resultset. When using the MySQL engine, you can use the rowcount property to count the number of rows returned from a query, but strangely the same operation gives -1 when using a sqlite database.

At first, I thought the problem was due to SqlAlchemy which I was using, and quickly made a script to isolate and compare:

Which results in:

  Testing mysql & sqlite rowcount with SqlAlchemy v0.5.8
  Creating schemas
  Inserting some data
  all_mysql_rows.rowcount: 1
  len(all_mysql_rows.fetchall()): 1
  all_sqlite_rows.rowcount: -1
  len(all_sqlite_rows.fetchall()): 1

After some digging around, I discovered that the problem is infact due to pysqlite, and I quote:

Well this was a bummer, because I wanted to write code that I could switch between Sqlite and MySQL - using MySQL in production/development, and sqlite in-memory databases for unit-testing etc. Then it occurred to me: code for unit testing doesn't have to be extremely efficient, so perhaps I could monkey-patch SqlAlchemy's ResultProxy to make up for sqlite's deficiancy...

The hack

Making XMPP chat-bots as web applications

Over summer last year I'd been mucking around a bit with XMPP, Erlang and ejabberd. I wanted to quickly make some XMPP chat-bots and, being a web-developer, it occurred to me it would be cool to have a http backend to a chat server, so that I could treat incoming messages as simple HTTP requests. The idea was to:

• have a standard XMPP server handle everything related to federation, S2S communication, encryption and authentication;
• develop the bot as a traditional web app, that receives messages and spits out replies.

So I set about making an http-rpc module for ejabberd. Basically, the idea is that it would do the same as mod_rest but backwards: mod_rest allows you to POST stanzas to ejabberd via HTTP, but I wanted to post stanzas received by ejabberd to a restful webservice. I called this RPC gateway mod_motion (as in, the opposite of mod_rest :-).

So here's a little guide to how I made and how you can install mod_motion. First off, you will need to install the ejabberd server and some other packages:

I don't want to go too much into setting up an ejabberd XMPP server, but if you are running your chat server publicly, you will need to create some DNS SRV records to make it 'discoverable' by other chat servers, as defined in rfc3920. They should look something like this:

_xmpp-client._tcp  	SRV  	5 0 5222 yourdomain.com.
_xmpp-server._tcp 	SRV 	5 0 5269 yourdomain.com.

Now to the module. If you'd like to read a great introduction to making modules for ejabberd, have a look at these articles by Anders Conberes:

1. Compiling Erlang
2. Generic Modules
3. HTTP Modules
4. XMPP Bots

You'll also have to get hold of a copy of the ejabberd source in order to compile the module:

You can download the module from my public SVN repository:

Mod_motion works by simply posting all messages, presence and iq stanzas to a web server defined as BASE_URL in the module.

So presence stanzas will be posted to:

http://127.0.0.1:8080/presence/user@host

Likewise, messages will be posted to:

http://127.0.0.1:8080/message/user@host

And IQs get forwarded via HTTP POST to:

http://127.0.0.1:8080/iq/user@host

In each case, user@host is the JID of the remote user.

To install it you will just need to modify the modules section in /etc/ejabberd/ejabberd.cfg thus:

Now to compile and install the module with the script install.sh.

Here's a simple echobot, implemented with web.py; see also the examples folder.

Here's the source of mod_motion:

Credit: echo_bot.erl by Anders Conbere.