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
Caveats
Schema reference