Saké Mail Database Configuration

Several of Saké Mail's extension components are available in versions that use an SQL database rather than the file system for storage. Specifically, you can configure a Saké Mail installation to support database storage for user options, addresses, and the message storage system itself. We recommend using IMAP servers for high-load installations rather than using an SQL back end, but the SQL configuration makes sense in some situations and we provide the option.

In order to connect Saké Mail components to any given database, you will need an appropriate JDBC driver for your database. Most database vendors supply JDBC drivers for their database systems, with Microsoft being the notable exception. Note that the JDBC/ODBC bridge is not an acceptable way to connect Saké Mail (or any high-performance web-based system) to an ODBC data source because it does not support connection pooling. Without connection pooling, database performance would be very poor.

In addition to the JDBC driver, you will need a database schema file in the particular flavor of SQL that your database system supports. Endymion Corporation currently provides database schemas for Microsoft SQL Server 7 and for MySQL. We implement schema files on-demand as customers need them, so if you need a schema file for a new database just let us know. The only requirement for the database system is that it has quality JDBC drivers available and the database must support some sort of BLOB data type.

To configure Saké Mail components to connect to your database, first replace the desired components in your "mail.ini" configuration file with the database components. The component that implements SQL connectivity for address books is "AddressesSQLBean", which replaces "AddressesXMLFileBean". The database version of the user options bean is "UserOptionsSQLBean", which replaces "UserOptionsXMLFileBean". The database version of the message store back end is "MessageStoreSQLBean", which replaces "MessageStoreXMLFileBean". Configuration of each component is illustrated below.

Common Settings
All of the database-enabled components require certain settings that control what database the component connects to, and how it connects. These parameters will be configured in each of the specific beans that follow:

DBDriver
The database driver class to use. This setting refers to the specific type of JDBC driver that you have installed for your database. This is a third-party component supplied either by your database vendor or by an independent driver vendor. A JDBC driver will be distributed at least partly as a Java class, which needs to be accessible via the classpath that you configured for Saké Mail.

 

DBSchema
The database schema file to use. This file describes how this servlet will interact with your database. You will need a schema file appropriate to your database vendor. This file will normally be supplied by Endymion. This file is stored in "/sake/config/mail".

 

DBURL
This is the JDBC URL that specifies to your JDBC driver what database to connect to. The specific format of this URL depends entirely on the specific JDBC driver that you are using. Consult your JDBC driver's documentation for information on exactly how to format this URL to connect to your database.

 

DBUsername (optional)
This is the username that will be used to connect to your database. This parameter is optional, as some database configurations do not require a login. Some JDBC vendors allow authentication information to be encoded in the JDBC URL, also. You can include your authentication information either here or in the URL if that is the case with your driver. Both should have the same effect.

 

DBPassword (optional)
The password corresponding to the above username.

 

DBMaxConnections
The maximum number of database connections to allow at once. Database connections are pooled between all users, so the more connections are allowed, the shorter any given user will have to wait before a connection can be used for that user's request. If you set this value to zero then the bean will allocate as many connections as are necessary. This is probably the best way to configure things, unless you have a strange setup.

Remember that you do not necessarily have to configure the same database settings for all of the beans. If you need to, you can store your address books on one database server, your user options on a different server, and your messages on a third server. You can mix database and file system components as well, perhaps storing your addresses and user options in a database and your messages on the file system. However, if you do wish to configure multiple database beans identically, you can use the "macro" feature of the Saké Mail configuration file. This feature will allow you to specify your configuration properties in one place and then invoke them in as many beans as you like. Simply define a macro using the "macro" XML tag. An example of use of this mechanism is available in "/sake/config/mail/default/mail_db_mysql.ini" in your Saké Mail distribution.

Addresses
To connect your Saké Mail installation's private address books to a database back end, locate the section in your "mail.ini" configuration file where the "AddressesXMLFileBean" is configured. The standard file system bean does not require any parameters, so the section should be relatively simple, like this:

Replace the name "AddressesXMLFileBean" with "AddressesSQLBean". This will invoke the database-enabled bean the next time that Saké Mail starts, rather than the standard file system bean. In addition, the database bean requires a few extra parameters, which will tell it what database to connect to and how to connect. Configure the parameters listed in the "Common Settings" section above. An example configuration follows:

If your JDBC driver and your database server are configured correctly and Saké Mail is able to access the database, then Saké Mail will automatically create the tables that it needs in your database. All you have to do is provide access to the database. If Saké Mail has problems connecting to your database, then the details will be listed in your output log. Most of the time Saké Mail will simply regurgitate the JDBC exception that has been raised by your JDBC driver.

User Options
Connecting your Saké Mail installation's user options storage system to a database back end is pretty much the same as the above directions for address books. Locate the section in your "mail.ini" configuration file where the "UserOptionsXMLFileBean" is configured. The standard file system bean does not require any parameters, so the section should be relatively simple, like this:

Replace the name "UserOptionsXMLFileBean" with "UserOptionsSQLBean". This will invoke the database-enabled bean the next time that Saké Mail starts, rather than the standard file system bean. In addition, the database bean requires a few extra parameters, which will tell it what database to connect to and how to connect. Configure the parameters listed in the "Common Settings" section above. The configuration is identical to the configuration for the addresses module, listed above.

Message Store
The message store mechanism can be replaced with a database-oriented system, although we do not recommend it. The most robust and efficient way to handle the message storage back-end for high-volume sites is to use a high-quality mail server that implements the IMAP protocol. Quality mail servers are optimized for efficient message listing and message retrieval, and can generally perform the task better and more economically than a generalized database server can. Good mail store servers also include sophisticated account management and monitoring systems that are invaluable to larger sites. For smaller sites, IMAP server still make more sense than generalized database servers, for simplicity cost effectiveness. Enormous amounts of progress have been made in the domain of message routing and storage, and using an off-the-shelf message server that is already optimized for the task is the best way to take advantage of the latest innovations.

That said, some sites still want to use a database back end for their message store. If this describes you, then the directions are virtually identical to the above configuration steps for address books and user options. Locate the section in your "mail.ini" configuration file where the "UserOptionsXMLFileBean" is configured. The standard file system bean does not require any parameters, so the section should be relatively simple, like this:

Replace the name "MessageStoreXMLFileBean" with "MessageStoreSQLBean". This will invoke the database-enabled bean the next time that Saké Mail starts, rather than the standard file system bean.

The message store bean uses the exact same parameters that the addresses and user options beans do, with the addition of one parameter, "DeleteMessages". When the database-enabled message store bean is in use, there are only two protocols that are valid, "pop3" and "imap". The protocol selection simply determines which protocol will be used to draw messages off of a store server and deposit them into the SQL database system. Message lists will be generated from the database system once the messages are drawn from the store server. In this system, the "pop3local" protocol no longer makes any sense, since everything that the database-enabled storage system does involves local storage. Likewise the "imapnofolders" protocol is irrelevant because "INBOX" folder is the only folder necessary on the IMAP server, since the database system is used for managing all other folders. The "DeleteMessages" parameter, set to either "true" or "false", specifies whether messages are to be deleted from the store server when they are inserted into the database server. Most production environments will want to set this value to "true" to prevent store servers from accumulating messages. An example of a valid configuration for the database-enabled message storage back end module is available in "/sake/config/mail/default/mail_dB_mysql.ini" in your Saké Mail distribution.