DBMail 2.0
This page is being used to collect information from other pages that is only relevant to DBMail 2.0.x. Note that DBMail 2.2 was released in 2006 and 3.0 was released in 2012, so please use the information here only for historical purposes or if you really need to support a very old DBMail installation. The use of DBMail 2.0.x versions is no longer recommended. Please upgrade.
Database Schema
Graphical overview of tables in DBMail 2.0.x
Tables in DBMail 2.0.x
This is a more detailed description of the tables in DBMail 2.0.x in alphabetic order.
NOTE: this page may be inaccurate on some topics.
dbmail_acl
purpose
Implementing the IMAP4 ACL extension as defined in RFC 2086. Used to define Access Control Lists for shared mailboxes.
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| user_id | bigint(21) | INT8 | First part of the primary key and foreign key referencing dbmail_users.user_idnr |
| mailbox_id | bigint(21) | INT8 | Second part of the primary key and foreign key referencing dbmail_mailboxes.mailbox_idnr |
| lookup_flag | tinyint(1) | INT2 | RFC 2086: mailbox is visible to LIST/LSUB commands |
| read_flag | tinyint(1) | INT2 | RFC 2086: SELECT the mailbox, perform CHECK, FETCH, PARTIAL SEARCH, COPY from mailbox |
| seen_flag | tinyint(1) | INT2 | RFC 2086: keep seen/unseen information across session |
| write_flag | tinyint(1) | INT2 | RFC 2086: STORE flags other than SEEN and DELETED |
| insert_flag | tinyint(1) | INT2 | RFC 2086: perform APPEND, COPY into mailbox |
| post_flag | tinyint(1) | INT2 | RFC 2086: send mail to submission address for mailbox |
| create_flag | tinyint(1) | INT2 | RFC 2086: CREATE new sub-mailboxes in any implementation defined hierarchy |
| delete_flag | tinyint(1) | INT2 | RFC 2086: STORE DELETED flag perform EXPUNGE |
| administer_flag | tinyint(1) | INT2 | RFC 2086: perform SETACL |
dbmail_aliases
purpose
Defines the mailrouting for specific mail addresses. This table isn't tightly coupled to dbmail_users via constraint, because some features like forwarding to another address wouldn't work anymore.
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| alias_idnr | bigint(21) | INT8 | Primary key |
| alias | varchar(100) | VARCHAR(100) | Destination Mail-Address (RCPT TO Address at MTA-Level) |
| deliver_to | varchar(250) | VARCHAR(250) | Destination by the MDA, see notes |
| client_idnr | bigint(21) | INT8 | May be useful for billing at an ISP |
notes
The deliver_to attribute can be used for various purposes:
- using an existing user_idnr from dbmail_users: delivery to a mailbox in the database
- using a mail-address: forwarding to another mail-address
- using a | followed by a shell command: pipe the raw e-mail to the given command
- using a ! followed by a shell command: same as | but prepend a mbox header
The alias attribute isn't unique, so it may also be used for building distribution lists.
dbmail_auto_notifications
purpose
Send a notification to a mail-address when new mail arrives at the mailbox. Needs AUTO_NOTIFY=yes in the configuration file (usually /etc/dbmail.conf)
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| user_idnr | bigint(21) | INT8 | Primary key and foreign key referencing dbmail_users.user.idnr |
| notify_address | varchar(100) | VARCHAR(100) | Mail-Address, where the notification should go to |
dbmail_auto_replies
purpose
Send an auto reply to back to the sender when new mail arrives at the mailbox. Can be used for Out-of-office Replies, but see the notes. Needs AUTO_REPLY=yes in the configuration file (usually /etc/dbmail.conf)
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| user_idnr | bigint(21) | INT8 | Primary key and foreign key referencing dbmail_users.user.idnr |
| reply_body | mediumtext | TEXT | Message Body sent back to the originator of the recieved mail |
notes
Unlike normal out-of-office-replies, AUTO_REPLY in DBMail does NOT remember if it already responded to an e-mail. If the user is subscribed to a Mailing List one can easily build a mail-loop.
dbmail_mailboxes
purpose
The mailbox information is stored in this table.
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| mailbox_idnr | bigint(21) | INT8 | Primary key |
| owner_idnr | bigint(21) | INT8 | Foreign key referencing dbmail_users.user_idnr |
| name | varchar(100) | VARCHAR(100) | Name of this Mailbox including the hierarchy information |
| seen_flag | tinyint(1) | INT2 | RFC 3501, 6.3.1., 6.3.2., 7.1.: PERMANENTFLAGS |
| answered_flag | tinyint(1) | INT2 | RFC 3501, 6.3.1., 6.3.2., 7.1.: PERMANENTFLAGS |
| deleted_flag | tinyint(1) | INT2 | RFC 3501, 6.3.1., 6.3.2., 7.1.: PERMANENTFLAGS |
| flagged_flag | tinyint(1) | INT2 | RFC 3501, 6.3.1., 6.3.2., 7.1.: PERMANENTFLAGS |
| recent_flag | tinyint(1) | INT2 | RFC 3501, 6.3.1., 6.3.2., 7.1.: PERMANENTFLAGS |
| draft_flag | tinyint(1) | INT2 | RFC 3501, 6.3.1., 6.3.2., 7.1.: PERMANENTFLAGS |
| no_inferiors | tinyint(1) | INT2 | RFC 3501, 7.2.2.: No child levels under this mailbox possible |
| no_select | tinyint(1) | INT2 | RFC 3501, 7.2.2.: Not possible to use this mailbox as selectable mailbox |
| permission | tinyint(1) | INT2 | RFC 3501, 6.3.1. 6.3.2. 7.1.: READ-ONLY (value 1) READ-WRITE (value 2) |
dbmail_messageblks
purpose
Raw e-mails are stored in this table
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| messageblk_idnr | bigint(21) | INT8 | Primary key |
| physmessage_id | bigint(21) | INT8 | Foreign key referencing dbmail_physmqessage.id |
| messageblk | longtext | TEXT | Either the e-mail header (upto crlfcrlf) or another part of the raw e-mail (maximum part size is 512KB) |
| blocksize | bigint(21) | INT8 | Size of this raw e-mail block |
| is_header | tinyint(1) | INT2 | Indicator for being a header block |
notes
The is_header attribute isn't yet used in DBMail 2.0.0, but is intended to be for increasing message retrieval performance ?in the near future?. This table is probably getting very large depending on your scenario. Consider using an InnoDB Table or UNIONs of MyISAM Tables in MySQL.
Sample how E-Mails are addressed and stored here:
You may use this image under a Creative Commons Attribution License.
dbmail_messages
purpose
IMAP Flags for each message as defined in RFC 3501
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| message_idnr | bigint(21) | INT8 | Primary key |
| mailbox_idnr | bigint(21) | INT8 | Foreign key referencing dbmail_mailboxes.mailbox_idnr |
| physmessage_id | bigint(21) | INT8 | Foreign key referencing dbmail_physmessage.id |
| seen_flag | tinyint(1) | INT2 | RFC 3501, 2.3.2.: message has been read |
| answered_flag | tinyint(1) | INT2 | RFC 3501, 2.3.2.: message has been answered |
| deleted_flag | tinyint(1) | INT2 | RFC 3501, 2.3.2.: message is delete for removal by later EXPUNGE |
| flagged_flag | tinyint(1) | INT2 | RFC 3501, 2.3.2.: message is flagged for urgent/special attention |
| recent_flag | tinyint(1) | INT2 | RFC 3501, 2.3.2.: message recently arrived in this mailbox |
| draft_flag | tinyint(1) | INT2 | RFC 3501, 2.3.2.: message has not completed composition (marked as draft) |
| unique_id | varchar(70) | VARCHAR(70) | RFC 3501, 2.3.1.1.: unique identifier message attribute |
| status | tinyint(1) | INT2 | DBMail internal message status, see notes |
notes
The DBMail internal status attribute is defined as follows:
- MESSAGE_STATUS_NEW = 0
- MESSAGE_STATUS_SEEN = 1
- MESSAGE_STATUS_DELETE = 2
- MESSAGE_STATUS_PURGE = 3
- MESSAGE_STATUS_UNUSED = 4
- MESSAGE_STATUS_INSERT = 5
- MESSAGE_STATUS_ERROR = 6
dbmail_pbsp
purpose
Implementing POP/IMAP before SMTP Functionality
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| idnr | bigint(21) | INT8 | Primary key |
| since | datetime | TIMESTAMP | Login Time of IP-Address in the ipnumber attribute |
| ipnumber | varchar(40) | INET | IP Address of the POP3/IMAP Client, unique attribute |
notes
POP_BEFORE_SMTP and/or IMAP_BEFORE_SMTP has to be set to yes in the configuration file. This table has to be cleaned up frequently by dbmail-util -l.
dbmail_physmessage
purpose
“Abstraction Layer” between dbmail_messages and dbmail_messageblks, increasing speed on IMAP-copying/moving messages and retrieving size and date information.
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| id | bigint(21) | INT8 | Primary key |
| messagesize | bigint(21) | INT8 | Raw mail size: SELECT SUM(mb.blocksize) FROM dbmail_messageblocks mb, dbmail_physmessage pm WHERE mb.physmessage_id = pm.id |
| rfcsize | bigint(21) | INT8 | RFC 3501, 2.3.4: [RFC 2822] Size Message Attribute |
| internal_date | datetime | TIMESTAMP | RFC 3501, 2.3.3: Internal Date Message Attribute |
dbmail_subscription
purpose
Subscribed mailboxes of a user returned by IMAP4s LSUB command. RFC 3501, 6.3.6., 6.3.7., 6.3.9
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| user_id | bigint(21) | INT8 | First part of the primary key and foreign key referencing dbmail_users.user_idnr |
| mailbox_id | bigint(21) | INT8 | Second part of the primary key and foreign key referencing dbmail_mailboxes.mailbox_idnr |
dbmail_users
purpose
Users table.
attributes
| Attribute Name | MySQL Data Type | PostgreSQL Data Type | Description |
|---|---|---|---|
| user_idnr | bigint(21) | INT8 | Primary key |
| userid | varchar(100) | VARCHAR(100) | Login Name of the user |
| passwd | varchar(34) | VARCHAR(34) | Login Password of the user |
| client_idnr | bigint(21) | INT8 | May be useful for billing at an ISP |
| maxmail_size | bigint(21) | INT8 | RFC 2087 IMAP4 QUOTA extension |
| curmail_size | bigint(21) | INT8 | Currently used space by this users message |
| encryption_type | varchar(20) | VARCHAR(20) | man dbmail-users for valid encryption types, see notes |
| last_login | datetime | TIMESTAMP | Time User last logged in via IMAP/POP3 |
notes
If you want to use cyrus-sasl sql-interface with this table for implementing SMTP-Auth you shouldn't choose any encryption upon creation of users.