Email store

Emails are core to DBMail, and make up the bulk of database storage.

Optimising storage of an email is a non trivial task, but the benefits are worth the additional complexity. This is the first of a two part article and focuses on storage of an email, the second companion piece is on caching for fast and efficient retrieval.

When DBMail receives an email, it's split up into its constituent parts and stored in the database. DBMail relies heavily on the gmime library to extract those parts for storage.

If SIEVE automation is enabled, then users can store automation scripts to filter and process incoming mail.

The following services receive emails:

An email user agent (MUA) such as Mozilla Thunderbird or K-9 Mail can also store emails using IMAP, for example when a draft email is saved or when an email is sent via SMTP the email is usually copied to a sent folder.

When DBMail is asked for a list of emails, an email, or one of its mimeparts, that information is sent to the MUA via one of the following services:

It's also possible to export an email into mbox format from a command terminal using dbmail-export.

Where is my email? You'll find dbmail_mimeparts contains almost everything, but see dbmail_header and the next section on caching on how best to find what you're looking for.

Database structure

Users have one or more folders and emails are associated with folders. As an email is deconstructed into its constituent parts for storage, the database reflects these aspects in its schema.

All emails are included in the dbmail_messages table. This table links to a user's mailbox (dbmail_mailboxes) and the actual message (dbmail_physmessage). The message is divided into parts (dbmail_mimeparts) and those parts are itemised (dbmail_partlists), linking back to the message (dbmail_physmessage).

Table.Column	Description
dbmail_mailboxes.mailbox_idnr	Primary key
dbmail_mailboxes.owner_idnr	Owner of the mailbox
dbmail_mailboxes.name	Mailbox name
dbmail_mailboxes.*flag	Various flags
dbmail_mailboxes.permission	Mailbox permission
dbmail_mailboxes.seq	Sequence used for Quick Mailbox Resynchronization

dbmail_messages.message_idnr	Primary key
dbmail_messages.mailbox_idnr	Mailbox
dbmail_messages.physmessage_id	Reference to the message
dbmail_messages.*flag	Various flags
dbmail_messages.unique_id	Internally generated unique id
dbmail_messages.status	0 (new), 1 (seen), 2 (deleted) or 3 (purge)
dbmail_messages.seq	Sequence used for Quick Flag Changes Resynchronization

dbmail_partlists.physmessage_id	Reference to the message
dbmail_partlists.is_header	Flag to identify mail headers
dbmail_partlists.part_key	Used to reconstruct the message
dbmail_partlists.part_depth	Used to reconstruct the message
dbmail_partlists.part_order	Used to reconstruct the message
dbmail_partlists.part_id	Reference to the part in dbmail_mimeparts

dbmail_mimeparts.id	Primary key
dbmail_mimeparts.hash	Cryptographic hash
dbmail_mimeparts.data	The contents of the part
dbmail_mimeparts.size	Size of this part

It's straightforward to reconstruct an email using one of the following queries to select the data in the right order:

PostgreSQL

SELECT
  list.part_key,
  list.part_depth,
  list.part_order,
  list.is_header,
  ph.internal_date,
  convert_from(parts.data, 'utf-8') AS data
FROM dbmail_mimeparts AS parts
INNER JOIN dbmail_partlists AS list
ON parts.id = list.part_id
INNER JOIN dbmail_physmessage ph
ON ph.id = list.physmessage_id
WHERE list.physmessage_id = ?
ORDER BY list.part_key, list.part_order ASC, list.part_depth DESC

Sqlite, MySQL

SELECT
  list.part_key,
  list.part_depth,
  list.part_order,
  list.is_header,
  ph.internal_date,
  parts.data AS data
FROM dbmail_mimeparts AS parts
INNER JOIN dbmail_partlists AS list
ON parts.id = list.part_id
INNER JOIN dbmail_physmessage ph
ON ph.id = list.physmessage_id
WHERE list.physmessage_id = ?
ORDER BY list.part_key, list.part_order ASC, list.part_depth DESC