----------------------------------------------------------------------
-- Yukatan webmail
-- Copyright (c) 2004, Jukka Zitting <jukka@zitting.name>
----------------------------------------------------------------------
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License as
-- published by the Free Software Foundation; either version 2 of the
-- License, or (at your option) any later version.
-- 
-- This program is distributed in the hope that it will be useful, but
-- WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
-- 
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
-- USA
----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- DATABASE yukatan
-- 
-- This is the schema definition of the Yukatan webmail database.
-- The PostgreSQL database structures defined in this file can be
-- used as a backend store of an email message handling application.
-- The database should be created with the "UNICODE" encoding to
-- properly support messages in different languages.
-- 
--   CREATE DATABASE yukatan WITH ENCODING 'UNICODE';
-- 
-- All the text values in this database are stored after having
-- been fully decoded as specified in the SMTP and MIME standards.
-- 
-- The Yukatan webmail database contains the following structures:
--
--   SEQUENCE msgno_sequence
--   TABLE message
--   TABLE entity
--   TABLE headerfield
--   TABLE inreplytofield
--   TABLE referencesfield
--   TABLE fromfield
--   TABLE replytofield
--   TABLE tofield
--   TABLE ccfield
--   TABLE bccfield
-- 
----------------------------------------------------------------------


CREATE SEQUENCE msgno_sequence;

----------------------------------------------------------------------
-- 
-- TABLE message
-- 
-- The message relation holds information about email messages.
-- Each tuple represents a single message. Messages are identified
-- using an artificial serial number stored as the msgno attribute.
--
-- Selected message envelope information is stored as the env*
-- attributes. The entire original message is stored as-is as the
-- msgsource attribute to counter the loss of information associated
-- with decoding the header fields and the message body. All other
-- message data is stored in other relations using the message
-- serial number as reference.
-- 

CREATE TABLE message (
        msgno           INTEGER PRIMARY KEY
                        DEFAULT NEXTVAL('msgno_sequence'),

        envtype         CHARACTER VARYING NOT NULL
                        CHECK (envtype = 'received' OR
                               envtype = 'sent' OR
                               envtype = 'draft' OR
                               envtype = 'template' OR
                               envtype = 'saved')
                        DEFAULT 'saved',

        envtimestamp    TIMESTAMP WITH TIME ZONE NOT NULL
                        DEFAULT CURRENT_TIMESTAMP,

        envsender       CHARACTER VARYING,

        envrecipient    CHARACTER VARYING,

        msgsource       BYTEA
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE entity
-- 
-- The entity relation holds information about the MIME entities
-- of email messages. Each tuple represents a single MIME entity node
-- in the entity tree of a message.
-- 
-- Entities are identified by the serial number assigned to the
-- message containing the entity and the order number of the entity
-- within the message. The order number starts from zero and is
-- incremented by one for each entity.
-- 
-- Entity tree structures are encoded using the entparentno attribute.
-- The entity number of the root entity is always zero and its entparentno
-- attribute is NULL. All child entities have positive numbers and
-- non-NULL entparentno attributes. The entparentno attribute always
-- references a higher entity in the same message. These constraints
-- are encoded as a set of CHECK and FOREIGN KEY constraints.
-- 
-- Information from selected structured header fields is stored
-- in specific *field relations. All the header fields of the entity
-- are also stored in the headerfield relation.
-- 
-- The selected header values are stored after removing extra
-- whitespace and line foldings. The msgdate attribute is an
-- exception, it contains the data from the "Date:" header field
-- converted into a real timestamp.
-- 
-- All the line breaks in the original entity body are converted
-- to Unix-style LF line breaks before the body is saved in the
-- database. The entity body is only stored if the entity content
-- type is text/plain.
-- 

CREATE TABLE entity (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,

        entno           INTEGER NOT NULL
                        CHECK (entno >= 0),
        
        PRIMARY KEY (msgno, entno),
        
        entparentno     INTEGER,
        
        CHECK (entparentno IS NULL OR entparentno < entno),
        
        FOREIGN KEY (msgno, entparentno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        entdate         TIMESTAMP WITH TIME ZONE,
        entsendername   CHARACTER VARYING,
        entsenderaddress CHARACTER VARYING,
        entmessageid    CHARACTER VARYING,
        entsubject      CHARACTER VARYING,
        entbody         TEXT
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE headerfield
-- 
-- The headerfield relation holds information about the header fields
-- of email messages. Each tuple represents a single header field.
-- 
-- Header fields are identified by the serial number assigned to the
-- message containing the header field and the order number of the
-- header field within the message. The order number starts from
-- zero and is incremented by one for each header field.
-- 
-- Before storing in the database, the message header fields are 
-- first identified and parsed using standard RFC 822 rules.
-- The colon that separates the field name from the field body is
-- dropped and the field name is converted to lower case. Also
-- all extra white space and line foldings are removed.
-- 

CREATE TABLE headerfield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        fieldno         INTEGER NOT NULL
                        CHECK (fieldno >= 0),
        fieldname       CHARACTER VARYING NOT NULL
                        CHECK (LOWER(fieldname) = fieldname),
        fieldbody       CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, fieldno)
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE inreplytofield
-- 
-- The inreplytofield relation is used to store the message
-- identifiers included in the In-Reply-To: field of an email message. 
-- 
-- The message identifiers are stored as the inreplytoid attribute
-- without the leading "<" and trailing ">" characters.
-- 
-- Each identifier included in an In-Reply-To: field is given an order
-- number that uniquely identifies the identifier within the message.
-- Only one order number sequence is used for a message even if there
-- are multiple In-Reply-To: fields.
-- 

CREATE TABLE inreplytofield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        inreplytono     INTEGER NOT NULL
                        CHECK (inreplytono >= 0),
        inreplytoid     CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, inreplytono)
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE referencesfield
-- 
-- The referencesfield relation is used to store the message
-- identifiers included in the References: field of an email message. 
-- 
-- The message identifiers are stored as the referencesid attribute
-- without the leading "<" and trailing ">" characters.
-- 
-- Each identifier included in a References:  field is given an order
-- number that uniquely identifies the identifier within the message.
-- Only one order number sequence is used for a message even if there
-- are multiple References: fields.
-- 

CREATE TABLE referencesfield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        referencesno    INTEGER NOT NULL
                        CHECK (referencesno >= 0),
        referencesid    CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, referencesno)
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE fromfield
-- 
-- The fromfield relation is used to store the addresses included
-- in the From: field(s) of an email message.
-- 
-- Each address is given an order number that uniquely identifies the
-- address within the From: addresses of the message.
-- 

CREATE TABLE fromfield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        fromno          INTEGER NOT NULL
                        CHECK (fromno >= 0),
        fromname        CHARACTER VARYING,
        fromaddress     CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, fromno)
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE replytofield
-- 
-- The replytofield relation is used to store the addresses included
-- in the Reply-To: field(s) of an email message.
-- 
-- Each address is given an order number that uniquely identifies the
-- adress within the Reply-To: addresses of the message.
-- 

CREATE TABLE replytofield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        replytono       INTEGER NOT NULL
                        CHECK (replytono >= 0),
        replytoname     CHARACTER VARYING,
        replytoaddress  CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, replytono)
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE tofield
-- 
-- The tofield relation is used to store the addresses included in
-- the To: field(s) of an email message.
-- 
-- Each address is given an order number that uniquely identifies the
-- address within the To: addresses of the message.
-- 

CREATE TABLE tofield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        tono            INTEGER NOT NULL
                        CHECK (tono >= 0),

        toname          CHARACTER VARYING,

        toaddress       CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, tono)
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE ccfield
-- 
-- The ccfield relation is used to store the addresses included in
-- the Cc: field(s) of an email message.
-- 
-- Each address is given an order number that uniquely identifies the
-- address within the Cc: addresses of the message.
-- 

CREATE TABLE ccfield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        ccno            INTEGER NOT NULL
                        CHECK (ccno >= 0),
        ccname          CHARACTER VARYING,
        ccaddress       CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, ccno)
);

----------------------------------------------------------------------


----------------------------------------------------------------------
-- 
-- TABLE bccfield
-- 
-- The bccfield relation is used to store the addresses included in
-- the Bcc: field(s) of an email message.
-- 
-- Each address is given an order number that uniquely identifies the
-- address within the Bcc: addresses of the message.
-- 

CREATE TABLE bccfield (
        msgno           INTEGER NOT NULL,
        entno           INTEGER NOT NULL,
        FOREIGN KEY (msgno, entno)
                        REFERENCES entity(msgno, entno)
                        ON UPDATE CASCADE ON DELETE CASCADE,

        bccno           INTEGER NOT NULL
                        CHECK (bccno >= 0),
        bccname         CHARACTER VARYING,
        bccaddress      CHARACTER VARYING NOT NULL,

        PRIMARY KEY (msgno, entno, bccno)
);

----------------------------------------------------------------------
