Database Configuration

James has the capacity to use a JDBC-compatible database for storage of both message and user data. This section explains how to configure James to utilize a database for storage.

Requirements

Using James with a database backend has certain requirements. Database configuration is extremely vendor-specific, so we can only state the requirements in general terms.

There must be a database instance accessible from the James server. An account with appropriate privileges (select, insert, delete into tables, and on initial startup creation of tables) and with sufficient quota for the data to be inserted into the database must be available. Also, since James will use JDBC to access the database, an appropriate JDBC driver must be available for installation.

It is important to verify the functionality of the database before attempting to configure James to use it as a repository. This will help ensure that configuration issues are properly identified.

Connection Configuration

Configuring the Phoenix container to work with JDBC is the first step in enabling James database support.

First, Phoenix must be able to load the JDBC classes. To make these classes available to Phoenix, place the jar/zip files for the JDBC driver in the lib subdirectory of the James installation directory. Any additional libraries upon which the JDBC library depends that are not part of the standard Java distribution should also be added to this directory.

Please note that a MySQL driver is included as part of the James distribution and so there is no need to add such a driver to the lib directory.

Second, the config.xml must be modified so that Phoenix initializes the database connections. The relevant configuration is in the database-connections block. The database-connections tag has only a single child tag, data-sources. This latter tag is a simple container tag for a number of child elements. It is these child elements, data-source elements, that define the database connections.

Each data-source tag has a required attribute, name. This value must be unique to each data-source element. It is this name that will be used to specify the database connection in other parts of the config.xml file.

The data-source element has five children, all of whom are required.

  • driver - The class name of the database driver to be used.
  • dburl - The JDBC connection URL for your database/driver.
  • user - The user id of the database account to be used by this connection.
  • password - The password of the database account to be used by this connection.
  • max - The maximum number of JDBC connections to be used concurrently by this data-source.
Generally, you simply configure these entries in the config.xml file, which are commented, in order to use a database with James. You would then use the db: or dbfile: prefix instead of the file: prefix for a particular repository. You are currently free to mix and match your use of these different storage types for different repositories. See Repository Configuration for more details. A sample configuration is described below.

SQL Statement Configuration

The precise SQL statements used by James to modify and view data stored in the database are specified in an external configuration file. The sqlResources.xml file (which can be found in the apps/james/conf directory) is a sample configuration file that contains the SQL statements used by James. The purpose of each of these statements, as well as the repository with which they are associated, is documented in situ. If you are using a SQL database with unusual SQL commands or data types, you may need to add special entries to this file. The James team does try to keep sqlResources.xml updated, so if you do run into a special case, please let us know. Also, if the database tables are not created a priori, but rather are to be created by James upon startup, special attention should be paid to the "create table" statements in this file. Such statements tend to be both very database and very database instance specific.

Sample James Configuration

The config.xml file has commented out examples for MySQL and MSSQL data sources, and for each of the standard repositories. For example, to use MySQL, you would uncomment and adjust the following data-source element.

You must create the database, in this case named mail, the user, and assign the user privileges. You may create the tables before running James or, if you so choose, James will automatically create the tables it needs. In the latter case the user must have table creation privileges.

<data-source name="maildb" class="org.apache.james.util.mordred.JdbcDataSource">
  <driver>org.gjt.mm.mysql.Driver</driver>
  <dburl>jdbc:mysql://127.0.0.1/mail</dburl>
  <user>username</user>
  <password>password</password>
  <max>20</max>
</data-source>

Once the data-source element has been created, it can be referenced elsewhere in the config.xml file. For example, the following element tells James to use the maildb data-source and dbfile storage mechanism for the message spool:

<spoolRepository>
   <repository destinationURL="dbfile://maildb/spool/spool" type="SPOOL"/>
</spoolRepository>

The following element tells James to store mailboxes in a the maildb data-source:

<inboxRepository>
   <repository destinationURL="db://maildb/inbox/" type="MAIL"/>
</inboxRepository>

The configuration file contains further examples.

Known Issues

There are some vendor-specific subtleties in using databases with James that have been observed by some users. These issues (and methods to resolve them) are recorded on the James FAQ as they are reported. Please consult the FAQ if you encounter any difficulties.