The goal of cached headers is optimized access for sorting, threading, and searching.
The ideas presented here are loosely based on the sql schema design for generic email storage presented on http://yukatan.sourceforge.net
This document is for discussion purposes only. The actual design will take place in:
https://svn.ic-s.nl/svn/dbmail/trunk/dbmail/sql/mysql/add_header_tables.mysql
update: this code is finished as far as delivery/injection of messages is concerned.
Add a set of tables for holding all rfc2822 headers. This will be the starting point.
CREATE TABLE dbmail_headername ( id BIGINT NOT NULL AUTO_INCREMENT, headername VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (headername) ) type=InnoDB ;
CREATE TABLE dbmail_headervalue ( headername_id BIGINT NOT NULL REFERENCES dbmail_headername(id) ON UPDATE CASCADE ON DELETE CASCADE, physmessage_id BIGINT NOT NULL REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE, id BIGINT NOT NULL AUTO_INCREMENT, headervalue VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (physmessage_id, id) ) type=InnoDB ;
Support fast threading by breaking out the Subject (base-subject), Date, and In-Reply-To/References headers.
DROP TABLE IF EXISTS dbmail_subjectfield; CREATE TABLE dbmail_subjectfield ( physmessage_id BIGINT NOT NULL, id BIGINT NOT NULL AUTO_INCREMENT, subjectfield VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (physmessage_id, id), FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE ) type=InnoDB ; DROP TABLE IF EXISTS dbmail_datefield; CREATE TABLE dbmail_datefield ( physmessage_id BIGINT NOT NULL, id BIGINT NOT NULL AUTO_INCREMENT, datefield DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00', PRIMARY KEY (id), UNIQUE (physmessage_id, id), FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE ) type=InnoDB ; DROP TABLE IF EXISTS dbmail_referencesfield; CREATE TABLE dbmail_referencesfield ( physmessage_id BIGINT NOT NULL, id BIGINT NOT NULL AUTO_INCREMENT, referencesfield VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (physmessage_id, id), FOREIGN KEY (physmessage_id) REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE ) type=InnoDB ;
Support fast sorting by breaking out and preparsing the fields most commonly used in searching and sorting.
CREATE TABLE dbmail_fromfield ( physmessage_id BIGINT NOT NULL REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE, id BIGINT NOT NULL AUTO_INCREMENT, fromname VARCHAR(100) NOT NULL DEFAULT '', fromaddr VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (physmessage_id, id) ) type=InnoDB ; CREATE TABLE dbmail_tofield ( physmessage_id BIGINT NOT NULL REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE, id BIGINT NOT NULL AUTO_INCREMENT, toname VARCHAR(100) NOT NULL DEFAULT '', toaddr VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (physmessage_id, id) ) type=InnoDB ; CREATE TABLE dbmail_replytofield ( physmessage_id BIGINT NOT NULL REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE, id BIGINT NOT NULL AUTO_INCREMENT, replytoname VARCHAR(100) NOT NULL DEFAULT '', replytoaddr VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (physmessage_id, id) ) type=InnoDB ; CREATE TABLE dbmail_ccfield ( physmessage_id BIGINT NOT NULL REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE, id BIGINT NOT NULL AUTO_INCREMENT, ccname VARCHAR(100) NOT NULL DEFAULT '', ccaddr VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE (physmessage_id, id) ) type=InnoDB ;
Some other fields will also be commonly used for search/sort but do not warrant preparsing and/or separate tables.
ALTER TABLE dbmail_physmessage ADD sendername VARCHAR(100) NOT NULL DEFAULT ''; ALTER TABLE dbmail_physmessage ADD senderaddr VARCHAR(100) NOT NULL DEFAULT ''; ALTER TABLE dbmail_physmessage ADD subject VARCHAR(255) NOT NULL DEFAULT ''; ALTER TABLE dbmail_physmessage ADD messageid VARCHAR(100) NOT NULL DEFAULT '';