---------------------------------------------------------------------- -- Yukatan webmail -- Copyright (c) 2004, Jukka Zitting ---------------------------------------------------------------------- -- 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, entcontentid CHARACTER VARYING, entdisposition CHARACTER VARYING CHECK (LOWER(entdisposition) = entdisposition), entfilename CHARACTER VARYING, enttypemajor CHARACTER VARYING DEFAULT 'text' NOT NULL CHECK (LOWER(enttypemajor) = enttypemajor), enttypeminor CHARACTER VARYING DEFAULT 'plain' NOT NULL CHECK (LOWER(enttypeminor) = enttypeminor), entdescription CHARACTER VARYING, entsubject CHARACTER VARYING, enttext TEXT, entdata BYTEA ); ---------------------------------------------------------------------- ---------------------------------------------------------------------- -- -- 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) ); ----------------------------------------------------------------------