James 2.0 - Using JDBC

This document explains how to enable JAMES 2.0 to use database storage via JDBC. Based on ReadMe notes by Darrell DeBoer and ??.

Goals

Main Goals.

• use Avalon and Cornerstone DataSource components for connection serving and pooling (done)
• Remove hard-coded SQL statements from UsersJdbcRepository (done)
• 'SqlResources.java' - detect db product from jdbc connection and select appropriate SQL statements from SQL definition file for specific product (done)
• Simpler to create database-backed UserRepository implementations for different User implementations (done)
• Simplify UserRepository specification in config - make it URL:// based, like MailRepository. (done)
• Consolidate existing UserRepository implementations - refactor out common functionality (TODO)
• Have UserStore serve up repository implementations based on: storage, User implementation, and location. (TODO)

Other Goals (reuse development in JdbcMailRepository):

• use Avalon and Cornerstone DataSource components in JdbcMailRepository (done)
• Use SqlResources.java to provide db-specific SQL to JdbcMailRepository (done)
• Automatic table generation for JdbcMailRepository (done)
• Get rid of the separate database .properties files. (done)

USE INSTRUCTIONS

The main configuration is setting up the "database-connections" section of the config file. There's an example there using MySql - I haven't yet tested on other databases (although the SQL statements haven't changed much, so I imagine it will still work on other platforms).

The only config properties you should need to set are:

• <driver> Class name of database driver to use </driver>
• <dburl> the jdbc connection string for your database </dburl>
• <user> database user </user>
• <password> database password </password>

TEST INSTRUCTIONS (FOR USER REPOSITORIES)

• Telnet to the remote manager: "telnet localhost 4555".
• Do some user management - type "help" for options.
• type "use list-james", to switch to the repository for this list.
• list the users
• send an email to "james-on@localhost"
• list the users again

MAIL REPOSITORY

Mail repositories are now configured primarily by their "destinationURL" property. This has the format "db://datasource/table[/repository]". Other config such as the "sqlFile" (where to find sqlResources.xml, and the "filestore" for mixed storage, can also be included, or can be left to defaults (see below).

Each repository registered in the MailStore can now take a "config" section, which is the default configuration used by the MailStore when creating a repository of that class. This allows us to have a configurable JDBCMailRepository, without needing to specify config everywhere it's used. I've set up the SPOOL repository to use mixed storage (a filestore in addition to the database), but the MAIL repository to use pure db storage.

The new config has been tested with "inbox" and "spool" repositories, but it's not yet tested with the "error", "spam" and "outgoing" repositories.

The statements in the SqlResources.xml file have been tested on MySQL and M$SQL. Only M$ has the optimised "getMessageSize" SQL, but this is optional.

You no longer have to manually create the tables required - this is automatic. Create Table statements are included for M$SQL and MySQL; we'll need to add others for other db products.

USERS REPOSITORY

I've added an "AbstractJdbcUsersRepository", which takes care of most of the work of a JdbcUsersRepository, making it pretty easy to add new ones. The abstract implementation doesn't have knowledge of User implementations, this is restricted to overridden methods in concrete UsersRepository implementations.

The AbstractJdbcUsersRepository obtains SQL statements via an "SqlResources" object, which reads an sql definition file, finds the appropriate <sqlDefs> element, and provides the sql strings contained. In addition, the SqlResources class handles 2 other things:

• a) Parameter replacement in SQL (eg replace all occurances of ${table} within an sql statement with the parameter value for "table". Currently, all parameters are taken from the configuration <sqlParameters> element. It is also possible to define parameters (defaults, if you like) within the sql definition file itself (a <parameters> element).
• b) Examines the Jdbc Connection to determine what database product is being used. SQL statements specific to a db product (eg mysql) can then be used automatically. (Detection is done by regexp matches on Connection.getMetaData.getDatabaseProductName())

I've made a simple modification to "RemoteManagerHandler", to allow testing. The "use [userRepositoryName]" command will switch the Remote manager to manage the named repository. This isn't really intended for production, makes for easier testing. The "james-config.xml" included in the proposal sets up 4 JDBC repositories:

• "localUsers" - a JamesUsersJdbcRepository.
• "list-james" - a ListUsersJdbcRepository, used by the ListServ mailet.
• "list-test" - another ListUsersJdbcRepositor, for testing.
• "default-users" - a DefaultUsersJdbcRepository, for testing.

Note that in order for the Avalon DataSource components to work, I've included an upgraded "avalon-excalibur.jar" in the proposal.