Caching common headers

• prepared by: Paul Stevens
• date: dec 31 2004

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 '';

essay writing service