This article is about mod_archive_odbc: ejabberd implementation of server-side instant messaging history support, also known as XEP-136.

Update: development of this module has stopped, all further development is done in mod_archive2 project.

Development history

Original version of module for XEP-136 support in ejabberd, mod_archive was written by Olivier Goffart in 2006, see his post.

Without this original great work I do not think I would even start working on this subject at all, as I didn’t know Erlang and had no idea on where to start from.

When trying to experiment with this original mod_archive implementation I soon discovered some bugs in RSM handling, as well as missing features here and there.

I’ve performed some fixing, added replication support and submitted patch in ejabberd bugtracking system.

However, the patch was not applied as at this time there was already another implementation of mod_archive: mod_archive_sql, which is basically the port of original, mnesia-based mod_archive to PostgreSQL RDBMS, and ejabberd developers didn’t want these versions to differ.

To be true, I find the approach of maintaining two separate versions of the same module that differ only by storage engine pretty ridiculous, and for sure I wasn’t going to participate in that “road to hell” - I worked once for one big project that had very high rate of duplicate code (hello to those my ex co-workers who’re reading this! , and I know how bad it is …

Therefore, I’ve stopped at that time with further mod_archive fixes and switched to the other task - writing client library for XEP-136 support, to make it easy for clients to adopt this protocol. The library is subject for separate post, but what is important is that when I started to test it against server part, it quickly appeared that there were some severe limitations in mod_archive architecture, which were also inherited by mod_archive_sql version:

• RSM filtering, which is basically used to limit the number of queries and to page through results, was applied after fetching items satisfying the main request.
• When answering requests related to collections, they were fully fetched from the database, including all messages they contained.

These two points effectively mean that if client performs the request as simple as “show last 10 collections” the entire database is picked up in memory, and only after that filtering is performed to select those 10 collections the user asked for.

With about 3,000 collections recorded in mnesia database ejabberd easily took about 1Gb of RAM answering these queries, the speed was quite bad also - I do not think it would be useable at all with even larger database.

Just to put it to the right scale: currently I have the messaging archive with about 28,000 collections and around 290,000 messages, I cannot imagine processing these volumes of data with architecture explained above.

Therefore I had to return to the task of mod_archive development again.

First I just wanted to further patch original, mnesia-based mod_archive version to make it more efficient - but soon I discovered that mnesia just has not enough power to perform the tasks I needed. The most severe limiting factor was that I found no ways to efficiently limit queries size and page through results. Switching to SQL-based version seemd to be the only viable solution.

So I took mnesia-based mod_archive version, mod_archive_sql and tried to produce resulting version which would have the correct architecture & implementation to be efficient enough.

This appeared to be not that easy task - in the end it looks like I’ve rewritten most important parts of the module almost completely.

However, the current implementation I have seems to be quite effective, it easily proceeds the volumes of data I mentioned above and also much more conformant to XEP-136 standard.

I will submit this version to ejabberd bug-tracking system once more - I hope it can be included now into ejabberd trunk, replacing both mod_archive and mod_archive_sql, as I believe there’s no point in further development of mnesia-based mod_archive.

Current status

Current version is called mod_archive_odbc, as it’s designed (in theory) to work with any ODBC-compliant RDBMS. On practice there are quite some incompatibilities between RDBMS implementations, see below for details.

To the best of my knowledge and understanding of the standard, this version is fully conformant to XEP-136 except of the following:

• Encryption is not supported.
• Setting “auto” preference is persistent, not per-session as it should be according to XEP-136 (thanks to Olivier Goffart for spotting this!)
• When auto-archiving is enforced by module options, warning message, that is required according to XEP-136, is not sent to legacy clients.
• “Remove collection” command with attribute “open=true” is not supported - I do not think it’s very important to have at current stage, though.

All examples from XEP-136 standard, excluding those related to features mentioned above, seems to work correctly with current version.

Please note though that the standard is not yet finalized, so it’s still possible there will be some further changes in it.

Also there were revealed some problems in standard during implementation, I will submit the report to XMPP guys about them - hopefully, these still can be fixed before standard approval. mod_archive_odbc contains fixes to these problems - as these fixes do not break backward compatibility, it should be completely safe to have them there.

If you run mod_archive_odbc and encounter any standard-breaking behavior, or just any bug with it - I’d like to hear about it, please consider either contacting me or posting the comment to this article.

Please note that this module is still in experimental state! You’re strongly adviced to avoid using it on production servers, or at least make sure that frequent database backups are performed.

Change History

Revision 476

• Fixed error when requests without RSM tag were not processed, by Olivier Goffart.
• Added auto-archiving for groupchats.
• Fixes to support native MySQL driver.

Revision 465

Replaced string:to_lower by jlib:tolower everywhere in sources, as it appeated that string:to_lower was introduced as late as in erlang R11-B4 so not everybody have it.

Revision 434

Fixed bug with crashes on empty RSM responses.

Version 1.0.1

Fixed serious bug which might cause wiping out extra collections in the case user uploads preferences which specify expire interval for full or bare JID, but there is no prefs entry for domain of this JID - expire interval of this JID was applied on the whole domain, possibly causing removal of unwanted collections.

All users are adviced to upgrade or, if upgrade is not possible, the workaround for version 1.0.0 is to set wipeout_interval option to infinity.

Upgrading instructions: DB schemas were not changed, so just copy new version of mod_archive_odbc.erl to ejabberd sources and recompile.

Version 1.0.0

Initial release.

Downloading and Installation

The most up-to-date version is located at ejabberd-modules SVN repository. The files you need are with mod_archive_odbc* prefix.

Just put mod_archive_odbc to ejabberd source tree before ejabberd compilation, then compile ejabberd as usual. Do not forget to turn on ODBC support using --enable-odbc.

mod_archive_odbc was tested with ejabberd SVN trunk and ejabberd-1.1.4 versions and works with both of them. However, for ejabberd-1.x versions the following needs to be done:

1. Supporting module ejabberd_odbc_sup has to be started - if you use auth_odbc it is already started there, if not - you should start it explicitly from mod_archive_odbc, just uncomment lines “ejabberd-1.x compatibility code”. In ejabberd SVN this is done always automatically by ejabberd_rdbms, so you do not have to worry about it.
2. Connection to RDBMS can be closed by timeout if not used for a long time, causing the module to fail. In ejabberd SVN version there’s odbc_keepalive_interval parameter which can be used to periodically issue dummy query to keep connection up, but there’s no such thing in ejabberd-1.x, so you’ll have to deal with it on your own either switching timeout off in RDBMS configuration, issuing periodically the command to mod_archive_odbc or in any other way you find suitable for your configuration.

Please note that odbcserver, which is part of ODBC support package for erlang, has limit 8001 bytes on the maximal size of column data returned - this may become a problem if you have large messages in your collections, as they will be truncated on retrieval from database.

You can use this patch for erlang-11.2.5 to lift the restriction up to 65535 bytes. You’ll need to recompile odbcserver, either separately on its own - or just as part of whole erlang compilation.

Configuration

The following steps need to be done to configure mod_archive_odbc to use ODBC database connection:

1. Create database using either one of schema files provided with mod_archive_odbc or your own, making sure, of course, that it’s compatible to the schema mod_archive_odbc expects.
2. Create DSN to your database: for Windows use Administrative Tools → Data Sources (ODBC), for *nixes - configs for your unixODBC installation. It’s a good idea to check at this point DSN works correctly before proceeding further, preferrably - using erlang + odbc, see erlang odbc docs for an example, or even better - using ejabberd ODBC wrapper, see section “Debugging the ODBC configuration”, using ejabberd_odbc:sql_query. For example, smth like ejabberd_odbc:sql_query("localhost", "select * from archive_messages").
3. Specify {odbc_server, “Your ejabberd database DSN”} in ejabberd.cfg.
4. Add mod_archive_odbc to the list of modules ejabberd loads, parameters are described below.

For native connections, such as native MySQL driver, after creating database you should just add the appropriate option to ejabberd.cfg specifying database to connect and that’s should be it, see ejabberd site articles for general discussion, as well as native MySQL driver tutorial.

Parameters mod_archive_odbc understands:

• database_type - selects RDBMS type, currently “mysql”, “sqlite”, and “pgsql” are supported. This parameter should always be specified, all subsequent parameters are optional.
• default_auto_save (true or false, defaults to false) - whether to perform auto-saving by default or not; please note that XEP-136 requires it to be off by default.
• enforce_default_auto_save (true or false, defaults to false) - whether to enforce default_auto_save option by prohibiting users changing it individually in their preferences or not.
• session_duration (time in seconds) - the timeout interval the server uses to decide that conversation is over when performing automated archiving.
• default_expire (time in seconds or infinity atom, defaults to infinity) - all collections with their start time satisfying now() - start() >= default_expire will be removed, unless expire time is overridden by users in their preferences.
• enforce_min_expire (time in seconds, defaults to 0) - minimal expire time users are allowed to set in their preferences, useful if you’d like to enforce some message saving policy. NOTE: this parameter is not currently checked in modification commands, such as “upload collection” or “remove collection”, so you cannot enforce the policy completely yet!
• enforce_max_expire (time in seconds or infinity atom, defaults to infinity)
  • maximum expire time user is allowed to set, useful for limiting the amount of collections users may keep on server.
• replication_expire (time in seconds or infinity atom, defaults to 31536000, which is one year) - all collections with their removal time satisfying now() - removal_time() >= replication_expire will be removed completely from the database, thus the server completely forgets they were present once.
• wipeout_interval (time in seconds or infinity atom, defaults to 86400, which is one day) - interval between executions of expire routine.

Please note that according to XEP-136 only the following auto_save combinations are valid:

1. default_auto_save = true, enforce_default_auto_save = true
2. default_auto_save = false, enforce_default_auto_save = false

Implementation will happily work with any combination of these, though - for example, for personal ejabberd server, until all clients support XEP-136, combination default_auto_save = true, enforce_default_auto_save = false is quite logical, while for some public ejabberd server with lots of users and shortage of disk space default_auto_save = false, enforce_default_auto_save = true might be desirable, so that only manual uploads are supported.

Here’s the example configuration when using ODBC MySQL driver:

...

% Add to modules section:
{mod_archive_odbc, [{database_type, "mysql"},
                      {default_auto_save, true},
                      {enforce_default_auto_save, false},
                      {default_expire, infinity},
                      {enforce_min_expire, 0},
                      {enforce_max_expire, infinity},
                      {replication_expire, 31536000},
                      {session_duration, 1800},
                      {wipeout_interval, 86400}]},

...

% Add somewhere at top level, if you do not have it yet:
{odbc_server, "DSN=ejabberd;UID=ejabberd;PWD=passwd"}.

% Make sure the connection isn't closed,
% this option is supported in ejabberd-2.x only.
{odbc_keepalive_interval, 3600}.

...

For native MySQL driver replace “odbc_server” connection string by smth like this:

...

{odbc_server, {mysql, "localhost", "ejabberd", "ejabberd", "password"}}.

...

Migrating history

There’s no easy way to fully import history for all users from previous versions of mod_archive or other server-side storages to mod_archive_odbc - however, for XEP-136 servers implementations you can do it per user basis using wswutil by downloading history first to local file and then uploading it to server which runs mod_archive_odbc.

Please note, though, that previous versions of mod_archive had some of the features required by wswutil broken, so I cannot guarantee that downloading will succeed: I’ve tested this only with patched version of mnesia-based mod_archive. Also be warned that downloading can take large amounts of memory and be quite slow for the reasons explained above.

In wswutil distribution there are also some Perl scripts for history convertion to XEP-136 XML file format for the following clients:

• Miranda (msg_export and dbeditorpp plugins should be used first to export history and database to text files)
• Psi
• vicq

However, these scripts are quite hacky and may require some finishing of resulting XML files before uploading.

I strongly suggest to test obtained XML files on some test server running mod_archive_odbc before uploading them on production one, as if it’s determined in the middle of uploading that XML is broken you’ll stay with half-way imported history and will have to find the way to remove all uploaded collections to start from scratch, which may be problematic if you have already some other collections stored in database which you’d like to keep.

Client support

So far nothing encouraging can be written here - I know no Jabber clients supporting current version of XEP-136. There’s JWChat which claims to support XEP-136, but it uses old version of specs so I’m almost sure it will not work with mod_archive_odbc.

One of the possibilities to get access to the history stored by mod_archive_odbc is to use Olivier’s Jabber archive web reader - it provides web interface to history on Jabber server that supports XEP-136, such as ejabberd + mod_archive_odbc.

Other RDBMS support

mod_archive_odbc utilizes ejabberd wrappers over ODBC - this means that in theory it should work with all RDBMS’es which have ODBC driver, as well as through native drivers to MySQL and PostgreSQL, as they are included in ejabberd and supported through the same interface as ODBC queries.

On practice, though, due to SQL standard being … well … just like any other standard, i.e. missing important features and not fully supported by any of the RDBMS in any of standard / RDBMS versions, there are some either non-standard or incompatible features used.

My tests covered the following setups:

1. Windows XP + SQLite3 through ODBC driver.
2. Linux/MySQL through ODBC driver.
3. Linux/MySQL through native driver (only partially tested).

Therefore, for those configurations I can be pretty sure they’re working. There’s also support for PostgreSQL in sources, but it was never tested - so, most likely, it will not work out of the box, though should be easy to fix.

Update: it was reported that it works also with PostgreSQL, the appropriate schema file is now included in mod_archive_odbc, but I haven’t tried it myself.

Update2: it was reported that collections expiration is broken on PostgreSQL, see comments to this post for details.

For other RDBMS’es like MS SQL, Oracle, Firebird or whatever, you will need to read on to figure out what should be changed to support them.

This comparison of different SQL implementations, which lists incompatibilities between different RDBMS’es, helped me very much during development, if you’re thinking about adding support to mod_archive_odbc for other RDBMS’es - it can be useful for you also.

Below I list all the features I know in current implementation to behave not according to standard or that are likely to be a problem for new RDBMS support.

Almost all differences are localized to the appropriate case construction, so you just need to search the source for all database_type instances, and add your RDBMS into the appropriate case statement.

Triggers

Triggers are still new to some of RDBMS’es, so there may be some deviations here and there. In my tests they worked perfectly for SQLite3, but required quite some tuning for MySQL. Specifically, MySQL doesn’t support triggers updating the same table that invoked these triggers - therefore, for MySQL I had to put some actions directly in mod_archive_odbc code instead of triggers.

Depending on situation with your RDBMS you can consider either going SQLite3 direction, so just using triggers (preferred way) or use the same approach as for MySQL.

Paging through results and limiting result size

I have no clues how could that happen that this is not in the standard and implementations are incompatible with respect to this feature. To be more precise, there IS functionality in the standard that allows to implement this, albeit in quite awkward way, but it’s not supported by majority of RDBMS’es anyway, so there’s not that much sense in it.

Due to RDBMS’es selection used for my tests, I’ve used "OFFSET ... LIMIT ..." construction to acomplish the goal - it should be portable at least between SQLite, MySQL and PostgreSQL, and is quite easy to understand.

This one is important to fix for your RDBMS, as it provides the essential functionality, but it’s also one of the most difficult ones - if your RDBMS requires completely different way of query construction you may need to make quite some changes. They’re localized to generic query constructions functions, though, so at least you do not have to do this separately for collections, messages and changes queries.

Last Inserted Auto ID

When auto incremented column, used as primary key, is updated on new record insertion, I need in some places to know its value that was just inserted. Again, surprisingly there’s quite some deviation with that: almost every RDBMS has this function, but it’s called differently everywhere.

This one is easy - just find out the right way to do it for your RDBMS and insert it into the appropriate case.

In principle, you can even skip this completely, if this function is not available - implementation will just perform normal SELECT statement, that should also work in most cases, although being slightly slower and, theoretically, subject to problems if duplicate records are inserted - which should not be the case on practice, as last inserted id functionality is currently used for collections insertions only.

Strings escaping

The standard specifies that strings are specified as ‘string’, and one ’ symbol is escaped as ”, so the string can't should become 'can''t'. Besides that, \ symbol does not have special meaning according to the standard.

However, some RDBMS’es allow \ to be used for escaping, which creates the mess, as strings have to be escaped differently: for those that strictly follow the standard a\b'c should be escaped as 'a\b''c', while for others 'a\\\\b\'c'.

Again, this one is simple to handle: just add the case for your RDBMS in escape_str() function.

Multiple insert

For optimum performance it’s better to insert messages in database not one-by-one, but in bulk requests, this makes quite some difference - with single inserts uploading about 3000 collections into MySQL over local network takes more than an hour, while with bulk inserts - less than 10 minutes. The problem is that appropriate INSERT operation is specified as optional in SQL standard, and is not implemented by some RDBMS’es.

Just change case in store_messages() function to cover your RDBMS correctly.

Date/Time functions

For expiration support there’s a need to perform some calculations with date/time in query - again, everyone is different here.

Change case in get_expired_str() to cover your RDBMS, if you need expiration support.

Database creation and types

I tried to stick to very basic types in database schema to make it easier to port implementation to other RDBMS’es, as well as to work-around odbc problems in erlang implementation. Therefore, for example, BOOLEAN type is not used and is discouraged - use INTEGER or TINYINT instead. For timestamps everything that is able to store the date in format ‘YYYY-MM-DD HH:MM’ is OK, use CHAR(16) or anything similar if you cannot find any suitable type, provided that date/time functions of your RDBMS will work OK with that type. Everything other is just stored as VARCHAR.

I recommend taking types schema from mod_archive_odbc_mysql.sql and triggers creation code from mod_archive_odbc_sqlite3.sql and then adjusting it to suit your RDBMS.

Encodings

Erlang + ODBC knows nothing about encodings, so they just pass the text further to native driver without changes. As ejabberd and XMPP protocol in general use UTF-8, you should make sure that the native driver expects to receive UTF-8 from you. For most *nixes this is easy to accomplish: just configure unixODBC with charset=utf-8 or, if using native driver directly, specify encoding in your DBMS config.

For Windows, there’s one important complication: Windows treats erlang + ODBC as ANSI client, so when mod_archive_odbc connects to the database which has Unicode ODBC driver, Windows performs ANSI codepage→Unicode conversion, completely messing your UTF-8 input.

I’m not really sure what is the best & universal solution for it - at least I haven’t found the way to solve this problem for SQLite3 Unicode ODBC driver. What I’ve finally done for that was patching the driver to expose ANSI interface, so that input is passed to driver without conversion, and later treat it as UTF-8 - hacky and wrong, but works OK. Just in case, if anyone needs these, here is the patch for sqliteodbc-0.76 sources and here is the compiled binary.

For more complex / powerful RDBMS’es, such as MS SQL, as far as I understood there are the ways in ODBC driver itself to specify what encoding to assume for particular DSN when clients connect to it via ANSI interface.

Most likely, you will also want to specify UTF-8 as encoding of the database when creating it, or make sure that it’s created by default with UTF-8 encoding.

Closing thoughts

I hope that releasing mod_archive_odbc should improve server-side message archiving situation as it provides mostly standard-conformant and full-featured implementation, thus making it easier to develop clients supporting server-side message archiving.

My other project, libwsw, once brought up to fully useable state, should make it easier to add XEP-136 support to clients, once it is finished, and can already be used for simple maintenance operations like importing / exporting history to / from XEP-136 compliant servers.

Please, do not hesitate to comment!