Back to the main page

The master database

Discussion and details of the central interface between cmail modules: The master database. This is where most of the run-time configuration, such as user information (names, password hashes, user database locations), addresses and mail routing information as well as outbound mail is stored.

Why SQLite

SQLite has a long track record as a stable file format (which qualifies it over most other binary (and especially homebrew) storage formats. It also presents a nice C API (which sets it apart from most other Mail storage formats), the database is present as a single file on the disk (which may become rather large, but makes it easy to switch between databases) and using a database leaves open the option of implementing other database backends later, which (potentially) allows for great scalability.

Using a database also allows us to maintain a single storage backend both for configuration and mail storage, though whether that is a good thing is of course a matter of opinion.

Creating an empty database

To create an empty database, the cmail repository provides a schema file, consisting of all SQL statements necessary to create an empty master database.

To interact with SQLite databases, you can either install the sqlite3 command-line interface or use a database management tool of your choice which supports it.

To create a new, empty master database in your current working directory, run

~# sqlite3 empty.db ".read /path/to/cmail-repo/sql-update/install_master.sql"

or load the respective file into your database editor and run the commands in sql-update/install_master.sql.

Note that for a master database to be used by cmail, the file must be passed as the database parameter in the daemon configuration, and the file itself as well as the directory containing it must be read- and writable by the user running the cmail daemons.

User databases

Since SQLite databases are file-based and cmail needs to access the same file from multiple daemons, locking may become a problem on high-volume installations. cmail counters this by allowing users to have their own user databases, which will only contain mail stored to their accounts. User databases do not contain system configuration data as the master database does. User databases can also be used to allow users direct SQL access to their mail. Note that, should a user database become unwritable for cmail, incoming mail will be stored to the master database even if a user database is configured. This helps prevent lost mail.

To create a user database, follow the steps above, substituting sql-update/install_master.sql with sql-update/install_user.sql.

Note that some daemons and protocols (namely, the IMAP implementations), may have notes and restrictions on the use of user databases, at least as far as inter-system portability of database files goes.

To activate a user database, pass the path to the newly installed user database to cmail-admin-user userdb and take care that the file itself as well as the directory containing it must be read- and writable by the user running the cmail daemons.

Populating the database

To interact with a master database, for example to configure users and mail addresses, use the cmail-admin set of tools: cmail-admin-user, cmail-admin-address, cmail-admin-smtpd and cmail-admin-popd or, if you prefer, install and use the web interface.

To learn more about the internal structure of the master database, skip to the "Schema reference" section of this document or refer to the comments in sql-update/install_master.sql.

Upgrading the database

As development progresses, the internal structure of master and user databases evolves. To prevent problems when using incompatible code and database versions, all databases track their "schema version" internally. When a newer software version encounters a database version it does not recognize as compatible, it will exit with a warning. When you notice this warning, you will need to upgrade your database as described in the upgrade guide.

Interfacing the database

If you're considering writing a tool that interfaces with the cmail database, please take the following into account


Schema reference