---------------------------------------------------------------------- -- 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 ---------------------------------------------------------------------- -- 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'; ---------------------------------------------------------------------- CREATE SEQUENCE msgno_sequence; COMMENT ON SEQUENCE msgno_sequence IS 'Message number sequence. Used to generate unique message numbers' ' to the messages stored in the database.'; ---------------------------------------------------------------------- CREATE DOMAIN msgno_domain AS INTEGER; CREATE DOMAIN entno_domain AS INTEGER; CREATE DOMAIN norm_domain AS CHARACTER VARYING CHECK (TRIM(BOTH FROM LOWER(VALUE)) = VALUE); CREATE DOMAIN id_domain AS CHARACTER VARYING CHECK (TRIM(BOTH FROM VALUE) = VALUE); CREATE DOMAIN name_domain AS CHARACTER VARYING CHECK (TRIM(BOTH FROM VALUE) = VALUE); CREATE DOMAIN addr_domain AS CHARACTER VARYING CHECK (TRIM(BOTH FROM VALUE) = VALUE); CREATE DOMAIN mime_domain AS CHARACTER VARYING; COMMENT ON DOMAIN msgno_domain IS 'Message number domain. Message numbers are used to uniquely' ' identify the messages stored in the database. The numbers have' ' no meaning outside the database. The supported operator' ' on msgno_domain values is the equality comparison.'; COMMENT ON DOMAIN entno_domain IS 'Entity number domain. Entity numbers are used to identify and' ' order the entities of a message. The root entity of each message' ' has the entity number zero, and the other entities are given an' ' ascending sequence of entity numbers. Only the root entity value' ' zero and the relative ordering of the entity numbers have meaning' ' outside the database. The preferred method of assigning entity' ' numbers is to start from zero and increment the number by one for' ' each entity. The supported operations on entno_domain values' ' are the equality, less/greater than, and IS NULL comparisons.'; COMMENT ON DOMAIN norm_domain IS 'Normalized string domain. This domain is used for fields that' ' contain keywords and other case-insensitive strings that should' ' be normalized to an all lower-case, no extra whitespace format.'; COMMENT ON DOMAIN id_domain IS 'Message identifier domain. Message identifiers are defined in' ' section 3.6.4 of RFC 2822 as globally unique message identifiers' ' that follow the "msg-id" syntax. The message identifiers are stored' ' in id_domain fields as opaque strings without leading or' ' trailing angle brackets or whitespace. Each id_domain field' ' contains exactly one such message identifier, or NULL if the' ' identifier is not available. The supported operators on id_domain' ' values are the equality and IS NULL comparisons. Note that' ' message identifiers are not used in explicit database constraints' ' because database clients can not guarantee the existence and' ' true uniqueness of the message identifiers. Instead the message' ' identifiers can be used in database queries to identify discussion' ' threads and other related messages.'; COMMENT ON DOMAIN name_domain IS 'Display name domain. This domain is used for fields that contain' ' display names of email addresses. An addr_domain field that' ' contains an email address is usually accompanied by a name_domain' ' field that contains the optional display name of the address.' ' If the display name is not set, then the name_domain value is NULL.' ' Display names are stored in RFC 2047 decoded format without' ' any leading or trailing whitespace.'; COMMENT ON DOMAIN addr_domain IS 'Email address domain. This domain is used for fields that contain' ' email addresses. Addresses are stored in addr_domain fields as' ' opaque strings without leading or trailing angle brackets or' ' whitespace. Each addr_domain field contains exactly one address,' ' or NULL if the address is not available. Group addresses and other' ' multi-address constructs should be split to multiple fields or' ' rows. The actual format of the address is not constrained by the' ' database, and it is possible that the database will contain' ' newsgroup and other addresses that do not conform to the standard' ' email address formats. Clients should always validate addresses' ' before using them for purposes that require specific address' ' formats. Note that the addresses are stored in original case, but' ' that the address formats are usually case-insensitive. Address' ' comparisons should therefore usually be made case-insensitively.'; COMMENT ON DOMAIN mime_domain IS 'RFC 2047 decoded string domain. This domain is used for fields' ' that contain header field values decoded using the RFC 2047 rules.' ' The str_domain values can therefore contain non-ASCII characters.' ' All string operations are permitted on str_domain values. The' ' purpose of this domain is to clearly label the fields whose values' ' have already been decoded using RFC 2047.'; ---------------------------------------------------------------------- CREATE TABLE message ( msgno msgno_domain DEFAULT NEXTVAL('msgno_sequence'), CONSTRAINT message_key PRIMARY KEY (msgno), msgsource BYTEA ); COMMENT ON TABLE message IS 'The message table. This is the main table of the Yukatan database.' ' Each message stored in the Yukatan database is represented by a' ' single row in this table. The message records contain only the' ' unique message number and the original message source. All other' ' message information is broken into other tables that are linked' ' together using the unique message number.'; COMMENT ON COLUMN message.msgno IS 'The message number.'; COMMENT ON CONSTRAINT message_key ON message IS 'The primary key constraint to quarantee that each message is' ' uniquely identified by its message number.'; COMMENT ON COLUMN message.msgsource IS 'The original message source. This field contains a byte by byte' ' copy of the entire message as it existed when it was stored' ' in the database. The purpose of this field is to guarantee that' ' no information is lost when the message is stored in the database.' ' If this field is NULL then the original message source did not' ' exist or was not available when the message was stored.'; ---------------------------------------------------------------------- CREATE TABLE entity ( msgno msgno_domain NOT NULL, CONSTRAINT entity_message FOREIGN KEY (msgno) REFERENCES message(msgno) ON UPDATE CASCADE ON DELETE CASCADE, entno entno_domain NOT NULL, CONSTRAINT entity_key PRIMARY KEY (msgno, entno), entparentno entno_domain, CONSTRAINT entity_parent FOREIGN KEY (msgno, entparentno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT entity_order CHECK ((entparentno IS NULL AND entno = 0) OR (entparentno IS NOT NULL AND entparentno < entno)), entdate TIMESTAMP WITH TIME ZONE, entsendername name_domain, entsenderaddr addr_domain, entmessageid id_domain, entcontentid id_domain, entdisposition norm_domain, entfilename mime_domain, enttypemajor norm_domain DEFAULT 'text' NOT NULL, enttypeminor norm_domain DEFAULT 'plain' NOT NULL, entdescription mime_domain, entsubject mime_domain, enttext TEXT, entdata BYTEA, CONSTRAINT entity_body CHECK (enttext IS NULL OR entdata IS NULL) ); COMMENT ON TABLE entity IS 'The MIME entity table. This table is used to store information' ' about the MIME entities that make up email messages. An email' ' message consists of one or more entities arranged in an ordered' ' tree structure. The rows of this table represent individual MIME' ' entities.'; COMMENT ON COLUMN entity.msgno IS 'The number of the containing message.'; COMMENT ON CONSTRAINT entity_message ON entity IS 'The foreign key constraint used to guarantee that each entity is' ' linked to an existing message.'; COMMENT ON COLUMN entity.entno IS 'The entity number.'; COMMENT ON CONSTRAINT entity_key ON entity IS 'The primary key constraint used to quarantee that each entity is' ' uniquely identified by the combination of the message and' ' entity numbers.'; COMMENT ON COLUMN entity.entparentno IS 'The entity number of the parent entity.'; COMMENT ON CONSTRAINT entity_parent ON entity IS 'The foreign key constraint used to guarantee that each the' ' parent entity of each non-root entity exists.'; COMMENT ON CONSTRAINT entity_order ON entity IS 'The constraint used to guarantee that the entno value of the' ' root entities is zero and that all other entities are given' ' entity numbers that grow by distance from the root entity.' ' Together with the entity_parent constraint this guarantees' ' that all entity trees are rooted at the defined root entities.'; COMMENT ON COLUMN entity.entdate IS 'The time when the entity message was sent. This field contains the' ' parsed value of the Date header field defined in RFC 2822.' ' Normally only the root entity has a Date: field, but also other' ' entities can have a non-NULL entdate value. For example digest' ' and forward messages can contain entities with separate Date:' ' fields. Note that the Date value can be inaccurate or even' ' intentionally false.'; COMMENT ON COLUMN entity.entsendername IS 'The name part of the entity Sender address.'; COMMENT ON COLUMN entity.entsenderaddr IS 'The entity Sender address. If the Sender field is not available,' ' then this and the entsendername fields are set to NULL.'; COMMENT ON COLUMN entity.entmessageid IS 'The message identifier. This field contains the message identifier' ' from the Message-ID header field defined in RFC 2822. If the' ' Message-ID field is not available, then this field is set to NULL.' ' The message identifier of the containing message is attached to' ' the root entity.'; COMMENT ON COLUMN entity.entcontentid IS 'The entity identifier. This field contains the entity identifier' ' from the Content-ID header field defined in RFC 2045. If the' ' Content-ID field is not available, then this field is set to NULL.'; COMMENT ON COLUMN entity.entdisposition IS 'The content disposition type of this entity. Derived from the' ' Content-Disposition header field defined in RFC 2183. The standard' ' values are "inline" and "attachment", but other disposition types' ' might also be used. If the Content-Disposition value is not' ' available then this field is set to NULL. If the field value is' ' not recognized by a client, then the value "attachment" should be' ' assumed.'; COMMENT ON COLUMN entity.entfilename IS 'The filename of the attached entity content. Derived from the' ' Content-Disposition header field defined in RFC 2183. Clients' ' should note that the stored filename might not conform to local' ' filename conventions, and that the filename might be specially' ' constructred to invoke malicious behaviour. See the security' ' considerations in RFC 2183 for more information about the potential' ' problems in using the value of this field.'; COMMENT ON COLUMN entity.enttypemajor IS 'The top-level type identifier of the entity content. Derived from' ' the Content-Type header field defined in RFC 2045. If the' ' Content-Type field is not available, then the value "text" is' ' used as a part of the default media type "text/plain". Defines' ' the overall type of the entity content. The standard values are' ' "text", "image", "audio", "video", "application", "message",' ' and "multipart". Other type identifiers might also be used.'; COMMENT ON COLUMN entity.enttypeminor IS 'The subtype identifier of the entity content. Derived from' ' the Content-Type header field defined in RFC 2045. If the' ' Content-Type field is not available, then the value "plain" is' ' used as a part of the default media type "text/plain".' ' Combined with the enttypemajor value, the value of this field' ' defines the specific media type of the entity content. Typical' ' combined values are "text/plain", "image/jpeg",' ' "application/octet-stream", and "multipart/mixed", where the' ' respective enttypeminor values are "plain", "jpeg", "octet-stream",' ' and "mixed".'; COMMENT ON COLUMN entity.entdescription IS 'Description of the entity content. This field contains the value of' ' the Content-Description header field defined in RFC 2045. If the' ' Content-Description field is not available, then this field' ' is set to NULL.'; COMMENT ON COLUMN entity.entsubject IS 'Subject of the message. This field contains the value of the Subject' ' header field defined in RFC 2822. If the Subject field is not' ' available, then this field is set to NULL.'; COMMENT ON COLUMN entity.enttext IS 'Text content of the entity. This field contains the decoded content' ' of textual leaf entities. The entity content is decoded' ' according to the rules of RFC 2045. Clients should use the' ' media type information of the enttypemajor and enttypeminor fields' ' to determine the correct way to handle the value of this field.' ' If the entity content is not textual or the entity is not a leaf' ' entity then this field is set to NULL.'; COMMENT ON COLUMN entity.entdata IS 'The binary entity content. This field contains the decoded content' ' of non-textual leaf entities. The entity content is decoded' ' according to the rules of RFC 2045. Clients should use the' ' media type information of the enttypemajor and enttypeminor fields' ' to determine the correct way to handle the value of this field.' ' The security considerations of handling unknown binary content' ' be taken into account. If the entity content is textual or the' ' entity is not a leaf entity then this field is set to NULL.'; COMMENT ON CONSTRAINT entity_body ON entity IS 'Entity content type constraint. An entity can have either textual,' ' binary, or child entity content. These content categories can not' ' be combined. This constraint guarantees that an entity can not have' ' both textual and binary content. Note that child content is not' ' explicitly constrained. This constraint is not tied to the contents' ' of the media type field enttypemajor because although the media' ' types are fairly standardized, future extensions might obsolete' ' explicit media type constraints.'; ---------------------------------------------------------------------- CREATE TABLE headerfield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT headerfield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, fieldno INTEGER NOT NULL, CONSTRAINT headerfield_key PRIMARY KEY (msgno, entno, fieldno), fieldname norm_domain NOT NULL, fieldbody CHARACTER VARYING NOT NULL ); COMMENT ON TABLE headerfield IS 'The header field table. This table is used to store all the' ' header fields of the entities stored in the entity table.' ' An entity has zero or more ordered header fields that consist' ' of a field name and a field body. The rows of this table represent' ' individual header fields. The header fields are linked to the' ' containing entities using the combination of the msgno and entno' ' fields. A header field serial number is used to order and identify' ' the header fields within an entity. Note that the parsed contents' ' of the more important header fields are stored in other tables in' ' addition to being available in this table in unparsed format.' ' The purpose of this table is to give clients access to the unparsed' ' contents of all the header fields of an entity.'; COMMENT ON COLUMN headerfield.msgno IS 'The message serial number. Part of the foreign key link that' ' binds the header field to the containing entity.'; COMMENT ON COLUMN headerfield.entno IS 'The entity serial number. Part of the foreign key link that' ' binds the header field to the containing entity.'; COMMENT ON CONSTRAINT headerfield_entity ON headerfield IS 'The header field entity foreign key. Uses the combination of' ' the msgno and entno fields to bind the header field to the' ' containing entity.'; COMMENT ON COLUMN headerfield.fieldno IS 'The header field serial number. An artificial serial number that' ' identifies and positions this header fields within the' ' containing entity. Only the relative ordering of the fieldno' ' values are semantically meaningful. The preferred method of' ' assigning header field serial numbers is to start from zero' ' and increment the number by one for each header field.'; COMMENT ON CONSTRAINT headerfield_key ON headerfield IS 'The header field primary key. Header fields are uniquely identified' ' by the combinatiof the message, entity, and header field serial' ' numbers'; COMMENT ON COLUMN headerfield.fieldname IS 'The header field name. This field contains the name of this header' ' field. The value of this field is normalized to lower case. The' ' colon that separates the field name from the field body is not' ' included.'; COMMENT ON COLUMN headerfield.fieldbody IS 'The header field body. This field contains the body of this header' ' field. The field body is stored in undecoded format, before any' ' unfolding or parsing of the field body has been made.'; ---------------------------------------------------------------------- CREATE TABLE inreplytofield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT inreplytofield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, inreplytono INTEGER NOT NULL, CONSTRAINT inreplytofield_key PRIMARY KEY (msgno, entno, inreplytono), inreplytoid id_domain NOT NULL ); COMMENT ON TABLE inreplytofield IS 'The In-Reply-To identifier table. This table is used to store the' ' identifiers included in the In-Reply-To header field defined in' ' RFC 2822. An entity contains zero or more In-Reply-To identifiers' ' extracted from zero or more In-Reply-To header fields. The rows' ' in this table represent individual In-Reply-To identifiers.'; COMMENT ON COLUMN inreplytofield.msgno IS 'The message serial number. Part of the foreign key link that' ' binds this In-Reply-To identifier to the containing entity.'; COMMENT ON COLUMN inreplytofield.entno IS 'The entity serial number. Part of the foreign key link that' ' binds this In-Reply-To identifier to the containing entity.'; COMMENT ON CONSTRAINT inreplytofield_entity ON inreplytofield IS 'The In-Reply-To entity foreign key. Uses the combination of' ' the msgno and entno fields to bind this In-Reply-To identifier to' ' the containing entity.'; COMMENT ON COLUMN inreplytofield.inreplytono IS 'The In-Reply-To identifier number. An artificial serial number that' ' identifies and positions this In-Reply-To identifier within the' ' containing entity. Only the relative ordering of the inreplytono' ' values are semantically meaningful. The preferred method of' ' assigning In-Reply-To identifier numbers is to start from zero' ' and increment the number by one for each identifier.'; COMMENT ON CONSTRAINT inreplytofield_key ON inreplytofield IS 'The In-Reply-To identifier primary key. In-Reply-To identifiers are' ' uniquely identified by the combination of the message, entity, and' ' identifier serial numbers.'; COMMENT ON COLUMN inreplytofield.inreplytoid IS 'The In-Reply-To identifier.'; ---------------------------------------------------------------------- CREATE TABLE referencefield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT referencefield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, referenceno INTEGER NOT NULL, CONSTRAINT referencefield_key PRIMARY KEY (msgno, entno, referenceno), referenceid id_domain NOT NULL ); COMMENT ON TABLE referencefield IS 'The References identifier table. This table is used to store the' ' identifiers included in the References header field defined in' ' RFC 2822. An entity contains zero or more References identifiers' ' extracted from zero or more References header fields. The rows' ' in this table represent individual References identifiers.'; COMMENT ON COLUMN referencefield.msgno IS 'The message serial number. Part of the foreign key link that' ' binds this References identifier to the containing entity.'; COMMENT ON COLUMN referencefield.entno IS 'The entity serial number. Part of the foreign key link that' ' binds this References identifier to the containing entity.'; COMMENT ON CONSTRAINT referencefield_entity ON referencefield IS 'The References entity foreign key. Uses the combination of' ' the msgno and entno fields to bind this References identifier to' ' the containing entity.'; COMMENT ON COLUMN referencefield.referenceno IS 'The References identifier number. An artificial serial number that' ' identifies and positions this References identifier within the' ' containing entity. Only the relative ordering of the referenceno' ' values are semantically meaningful. The preferred method of' ' assigning References identifier numbers is to start from zero' ' and increment the number by one for each identifier.'; COMMENT ON CONSTRAINT referencefield_key ON referencefield IS 'The References identifier primary key. References identifiers are' ' uniquely identified by the combination of the message, entity, and' ' identifier serial numbers.'; COMMENT ON COLUMN referencefield.referenceid IS 'The References identifier.'; ---------------------------------------------------------------------- CREATE TABLE fromfield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT fromfield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, fromno INTEGER NOT NULL, CONSTRAINT fromfield_key PRIMARY KEY (msgno, entno, fromno), fromname name_domain, fromaddr addr_domain NOT NULL ); COMMENT ON TABLE fromfield IS 'The From address table. This table is used to store the addresses' ' included in the From header field defined in RFC 2822. An entity' ' contains zero or more From addresses extracted from zero or more' ' From header fields. The rows in this table represent individual' ' From addresses.'; COMMENT ON COLUMN fromfield.msgno IS 'The number of the containing message.'; COMMENT ON COLUMN fromfield.entno IS 'The number of the containing entity.'; COMMENT ON CONSTRAINT fromfield_entity ON fromfield IS 'The foreign key constraint used to guarantee that each From address' ' is linked to an existing entity.'; COMMENT ON COLUMN fromfield.fromno IS 'The From address number. Address numbers are used to order and' ' identify the From addresses of an entity. Only the relative ordering' ' of the address numbers has meaning outside the database.'; COMMENT ON CONSTRAINT fromfield_key ON fromfield IS 'The primary key constraint to quarantee that each From address is' ' uniquely identified by the combination of the message, entity and' ' address numbers.'; COMMENT ON COLUMN fromfield.fromname IS 'The optional name part of the address.'; COMMENT ON COLUMN fromfield.fromaddr IS 'The address.'; ---------------------------------------------------------------------- CREATE TABLE replytofield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT replytofield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, replytono INTEGER NOT NULL, CONSTRAINT replytofield_key PRIMARY KEY (msgno, entno, replytono), replytoname name_domain, replytoaddr addr_domain NOT NULL ); COMMENT ON TABLE replytofield IS 'The Reply-To address table. This table is used to store the addresses' ' included in the Reply-To header field defined in RFC 2822. An entity' ' contains zero or more Reply-To addresses extracted from zero or more' ' Reply-To header fields. The rows in this table represent individual' ' Reply-To addresses.'; COMMENT ON COLUMN replytofield.msgno IS 'The number of the containing message.'; COMMENT ON COLUMN replytofield.entno IS 'The number of the containing entity.'; COMMENT ON CONSTRAINT replytofield_entity ON replytofield IS 'The foreign key constraint used to guarantee that each Reply-To' ' address is linked to an existing entity.'; COMMENT ON COLUMN replytofield.replytono IS 'The Reply-To address number. Address numbers are used to order and' ' identify the Reply-To addresses of an entity. Only the relative' ' ordering of the address numbers has meaning outside the database.'; COMMENT ON CONSTRAINT replytofield_key ON replytofield IS 'The primary key constraint to quarantee that each Reply-To address is' ' uniquely identified by the combination of the message, entity and' ' address numbers.'; COMMENT ON COLUMN replytofield.replytoname IS 'The optional name part of the address.'; COMMENT ON COLUMN replytofield.replytoaddr IS 'The address.'; ---------------------------------------------------------------------- CREATE TABLE tofield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT tofield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, tono INTEGER NOT NULL, CONSTRAINT tofield_key PRIMARY KEY (msgno, entno, tono), toname name_domain, toaddr addr_domain NOT NULL ); COMMENT ON TABLE tofield IS 'The To address table. This table is used to store the addresses' ' included in the To header field defined in RFC 2822. An entity' ' contains zero or more To addresses extracted from zero or more' ' To header fields. The rows in this table represent individual' ' To addresses.'; COMMENT ON COLUMN tofield.msgno IS 'The number of the containing message.'; COMMENT ON COLUMN tofield.entno IS 'The number of the containing entity.'; COMMENT ON CONSTRAINT tofield_entity ON tofield IS 'The foreign key constraint used to guarantee that each To address' ' is linked to an existing entity.'; COMMENT ON COLUMN tofield.tono IS 'The To address number. Address numbers are used to order and' ' identify the To addresses of an entity. Only the relative ordering' ' of the address numbers has meaning outside the database.'; COMMENT ON CONSTRAINT tofield_key ON tofield IS 'The primary key constraint to quarantee that each To address is' ' uniquely identified by the combination of the message, entity and' ' address numbers.'; COMMENT ON COLUMN tofield.toname IS 'The optional name part of the address.'; COMMENT ON COLUMN tofield.toaddr IS 'The address.'; ---------------------------------------------------------------------- CREATE TABLE ccfield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT ccfield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, ccno INTEGER NOT NULL, CONSTRAINT ccfield_key PRIMARY KEY (msgno, entno, ccno), ccname name_domain, ccaddr addr_domain NOT NULL ); COMMENT ON TABLE ccfield IS 'The Cc address table. This table is used to store the addresses' ' included in the Cc header field defined in RFC 2822. An entity' ' contains zero or more Cc addresses extracted from zero or more' ' Cc header fields. The rows in this table represent individual' ' Cc addresses.'; COMMENT ON COLUMN ccfield.msgno IS 'The number of the containing message.'; COMMENT ON COLUMN ccfield.entno IS 'The number of the containing entity.'; COMMENT ON CONSTRAINT ccfield_entity ON ccfield IS 'The foreign key constraint used to guarantee that each Cc address' ' is linked to an existing entity.'; COMMENT ON COLUMN ccfield.ccno IS 'The Cc address number. Address numbers are used to order and' ' identify the Cc addresses of an entity. Only the relative ordering' ' of the address numbers has meaning outside the database.'; COMMENT ON CONSTRAINT ccfield_key ON ccfield IS 'The primary key constraint to quarantee that each Cc address is' ' uniquely identified by the combination of the message, entity and' ' address numbers.'; COMMENT ON COLUMN ccfield.ccname IS 'The optional name part of the address.'; COMMENT ON COLUMN ccfield.ccaddr IS 'The address.'; ---------------------------------------------------------------------- CREATE TABLE bccfield ( msgno msgno_domain NOT NULL, entno entno_domain NOT NULL, CONSTRAINT bccfield_entity FOREIGN KEY (msgno, entno) REFERENCES entity(msgno, entno) ON UPDATE CASCADE ON DELETE CASCADE, bccno INTEGER NOT NULL, CONSTRAINT bccfield_key PRIMARY KEY (msgno, entno, bccno), bccname name_domain, bccaddr addr_domain NOT NULL ); COMMENT ON TABLE bccfield IS 'The Bcc address table. This table is used to store the addresses' ' included in the Bcc header field defined in RFC 2822. An entity' ' contains zero or more Bcc addresses extracted from zero or more' ' Bcc header fields. The rows in this table represent individual' ' Bcc addresses.'; COMMENT ON COLUMN bccfield.msgno IS 'The number of the containing message.'; COMMENT ON COLUMN bccfield.entno IS 'The number of the containing entity.'; COMMENT ON CONSTRAINT bccfield_entity ON bccfield IS 'The foreign key constraint used to guarantee that each Bcc address' ' is linked to an existing entity.'; COMMENT ON COLUMN bccfield.bccno IS 'The Bcc address number. Address numbers are used to order and' ' identify the Bcc addresses of an entity. Only the relative ordering' ' of the address numbers has meaning outside the database.'; COMMENT ON CONSTRAINT bccfield_key ON bccfield IS 'The primary key constraint to quarantee that each Bcc address is' ' uniquely identified by the combination of the message, entity and' ' address numbers.'; COMMENT ON COLUMN bccfield.bccname IS 'The optional name part of the address.'; COMMENT ON COLUMN bccfield.bccaddr IS 'The address.'; ----------------------------------------------------------------------