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 the following postgres query to select the data in the right order
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
