[2.0]
 

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.

 
2.0.txt · Last modified: 2013/01/02 11:19 by rcsheets
 
DBMail is developed by Paul J Stevens together with developers world-wide