This document describes how to install and setup ejabberd to use an ODBCrelational database (in this case MySQL) along with the internal distributed database (Mnesia).

Most relational databases support ODBC as a standard interface. ODBC stands for Open Database Connectivity. It is a standard software API specification for using database management systems (DBMS). ODBC is designed to be independent of programming language, database system and operating system.

ODBC configuration

ODBC on Linux, *BSD, *Nix

This document describes the basic steps to get ejabberdworking with ODBC on Linux, *BSD and *Nix systems. A general tutorial for configuring ODBC with Erlang under *nix systems is available from Trapexit Erlang community website.

ejabberd Linux installer

If you are using ejabberd Linux installer, you can skip the Erlang and ejabberdcompilation steps. Latest version of the installer include Erlang ODBC modules and no special steps are required. You can directly jump to the UnixODBC configuration paragraph.

Erlang/OTP compilation

You need to enable ODBC when compiling Erlang. This can be done with the configure flag --with-odbc. Here are the needed steps to compile Erlang with ODBC support:

./configure --enable-threads --with-odbc
make
make install

You can also activate the epoll patch for Erlang for performance improvments of the ejabberd server.

ejabberd compilation

You need to enable ODBC when compiling Erlang. This can be done with the configure flag --enable-odbc:

./configure --enable-odbc
make

UnixODBC

For Linux or Unix systems, you have to set-up the UnixODBC service on your system. A quick start tutorial is available from http://www.unixodbc.org/unixODBCsetup.html.

Download unixODBC source archive (i.e. unixODBC-2.2.11.tar.gz) and extract it in an appropriate directory:

tar zxvf unixODBC-2.2.11.tar.gz

Then, you can compile UnixODBC:

cd unixODBC-2.2.11/
./configure --prefix=/home/mremond/messenger/unixodbc/
make
make install

Download MyODBC Mysql binary archive from MySQL web site (i.e. mysql-connector-odbc-3.51.12-linux-i686.tar.gz) and extract the content of the bin and lib directories to unixODBC install dir. This will install the ODBC driver for MySQL in the UnixODBC context.

Start the UnixODBC configuration graphical interface:

./bin/ODBCConfig

and set-up a new driver:

You can then create a user configuration (DSN).

Keep in mind that, if MySQL is not running from a standard location, you have to define the following socket file: /tmp/mysql.sock

You have to select an existing MySQL database. For trials, you can use the default "test" database.

Before running Erlang, you have to ensure that the path to your odbc configuration files is correctly exported as environment variable:

export ODBCINI=/home/mremond/messenger/unixODBC/etc/odbc.ini
export ODBCSYSINI=/home/mremond/messenger/unixODBC/etc/

Reminder: You can grant remote priviledges on the database to the user 'mremond' with the following command:

GRANT ALL PRIVILEGES ON *.* TO mremond@% IDENTIFIED BY "password";

ejabberd configuration

The ejabberd.cfg file has to be changed for ODBC support:

• Disable internal authentication by commenting the line:

{auth_method, internal}.

• Enable ODBC authentication by adding the following lines:

{auth_method, odbc}.

{odbc_server, "DSN=Messenger;UID=root;PWD=nimda"}.

• Change the module name to use the ODBC ones (mod_vcard_odbc, mod_roster_odbc, mod_offline_odbc).

Creation of the database structure

The mysql.sql file is provided with the standard ejabberd distribution. The creation of the database structure can be done with the following command:

mysql -D test -S /tmp/mysql.sock < mysql.sql

Database schema is also provided for PostgreSQL in the pg.sql file.

Debugging the ODBC configuration

Several commands can be typed from the Erlang command-line to debug the ODBC configuration:

1. To test the database connection, type the following command:

   odbc:connect("DSN=ejabberd;UID=ejabberd;PWD=ejabberd", [{scrollable_cursors, off}]).

    

1. Creating a user can be done with the following Erlang command (test@localhost, with password 'test'):

   ejabberd_odbc:sql_query("localhost", "insert into users(username, password) values ('test', 'test')").

Optional: Migration from a existing Mnesia database

The simplest approach is to use first normal Mnesia configuration and then to use the program ejd2odbc.erl to migrate main tables to your relational database.

ODBC on Microsoft Windows

[TODO]