Configuring James to Store Messages in a Database

In this post, I will demonstrate how to configure James to use a database instead of the filesystem to handle and store email messages. For the time being, I will still use the filesystem to store information about users.

First off: create a database. I am using MySQL. You should just run a few statements to create the database, as well as a user and give that user permissions. James will create the tables when it is started and configured to use a database. Put in the database information in the config.xml file in the config/database-connections section, which is from lines 1188 to 1270 in the default config file. There are commented out blocks for many databases: Apache Derby, MySQL, Microsoft MSSQL, and hsqldb (formerly HypersonicSQL).

There are quite a few places in which we must change the configuration file in order for the database to process messages. There is an inbox repository, there is a spool repository, and several processors. There is also an nntp repository, but I have no desire to run a news server. Also, it appears that the news server can only use the filesystem. To disable the news server, you can set the “enabled” attribute to “false” for the “nntpserver” element. This is at config/nntpserver, which is at line 929 in the default configuration file. As stated, the user repository will still use the filesystem for the time being.

There are three ways that James stores data. The default is to use the filesystem. This is done in the configuration file with XML attributes like destinationURL=”file://var/mail/inboxes/”. To use a database, this attribute value is replaced with “db://<data-source>/<table>”. The <data-source> should match the “name” attribute in the config/database-connections/data-source element. By default it is “maildb” throughout the configuration file. The third option is to store message delivery and headers in the DB, and the body to the filesystem. This has a value of “dbfile://maildb/inbox/”. All we need to do is comment out the elements with the attribute destinationURL=”file://var/some/path”, and uncomment the corresponging elements with the attribute  destinationURL=”db://maildb/someTable”. Usually these elements are one right after the other.

Inbox repository: config/James/inboxRepository, line 66,
“ToRepository” mailet: config/spoolmanager/processor@name=”error”/mailet@class=”ToRepository”, line 435
“RemoteDelivery” mailet: config/spoolmanager/processor@name=”transport”/mailet@class=”RemoteDelivery”, line 543
“ToRepository” mailet: config/spoolmanager/processor@name=”spam”/mailet@class=”ToRepository”, line 627
“ToRepository” mailet: config/spoolmanager/processor@name=”local-address-error”/mailet@class=”ToRepository”, line 680
“ToRepository” mailet: config/spoolmanager/processor@name=”relay-denied”/mailet@class=”ToRepository”, line 706
Spool repository: config/spoolrepository, line 994

There are several elements with filesystem URLs in the config/mailstore/repositories section, but I think these can be left as-is.

Next the new configuration file and the proper JDBC JAR file should be uploaded. The configuration file should go to $JAMES_HOME/apps/james/SAR-INF/config.xml. The JDBC JAR file should go to $JAMES_HOME/lib.


Updated:

2009-02-16 10:30:57 Monday CST -0600

Image from Collection of Texts on Mathematical Astronomy and the Natural Sciences, a 9th century manuscript housed at Bavarian State Library, webpage information here, image from World Document Library, image assumed allowed under Fair Use.