+INSERT INTO table_comments VALUES ('src_associations','Source <-> suite associations');
+INSERT INTO column_comments VALUES ('src_associations','id','Source <-> suite association id');
+INSERT INTO column_comments VALUES ('src_associations','suite','Suite id (matches suite)');
+INSERT INTO column_comments VALUES ('src_associations','source','Source version id (matches src_ver)');
+INSERT INTO column_comments VALUES ('src_associations','created','Time this source package entered this suite');
+INSERT INTO column_comments VALUES ('src_associations','modified','Time this entry was modified');
+CREATE UNIQUE INDEX src_associations_source_suite ON src_associations(source,suite);
+
+
+CREATE TYPE bug_status_type AS ENUM ('pending','forwarded','pending-fixed','fixed','absent','done');
+CREATE TABLE bug_status_cache (
+ bug INT NOT NULL REFERENCES bug ON DELETE CASCADE ON UPDATE CASCADE,
+ suite INT REFERENCES suite ON DELETE CASCADE ON UPDATE CASCADE,
+ arch INT REFERENCES arch ON DELETE CASCADE ON UPDATE CASCADE,
+ status bug_status_type NOT NULL,
+ modified TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
+ asof TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
+);
+CREATE UNIQUE INDEX bug_status_cache_bug_suite_arch_idx ON bug_status_cache(bug,suite,arch);
+CREATE INDEX bug_status_cache_idx_bug ON bug_status_cache(bug);
+CREATE INDEX bug_status_cache_idx_status ON bug_status_cache(status);
+CREATE INDEX bug_status_cache_idx_arch ON bug_status_cache(arch);
+CREATE INDEX bug_status_cache_idx_suite ON bug_status_cache(suite);
+INSERT INTO table_comments VALUES ('bug_status_cache','Bug Status Cache');
+INSERT INTO column_comments VALUES ('bug_status_cache','id','Bug status cache entry id');
+INSERT INTO column_comments VALUES ('bug_status_cache','bug','Bug number (matches bug)');
+INSERT INTO column_comments VALUES ('bug_status_cache','suite','Suite id (matches suite)');
+INSERT INTO column_comments VALUES ('bug_status_cache','arch','Architecture id (matches arch)');
+INSERT INTO column_comments VALUES ('bug_status_cache','status','Status (bug status)');
+INSERT INTO column_comments VALUES ('bug_status_cache','modified','Time that this status was last modified');
+INSERT INTO column_comments VALUES ('bug_status_cache','asof','Time that this status was last calculated');
+
+
+
+CREATE TABLE message (
+ id SERIAL PRIMARY KEY,
+ msgid TEXT NOT NULL DEFAULT '',
+ from_complete TEXT NOT NULL DEFAULT '',
+ to_complete TEXT NOT NULL DEFAULT '',
+ subject TEXT NOT NULL DEFAULT '',
+ sent_date TIMESTAMP WITH TIME ZONE,
+ refs TEXT NOT NULL DEFAULT '',
+ spam_score FLOAT NOT NULL DEFAULT 0,
+ is_spam BOOLEAN NOT NULL DEFAULT FALSE
+);
+INSERT INTO table_comments VALUES ('message','Messages sent to bugs');
+INSERT INTO column_comments VALUES ('message','id','Message id');
+INSERT INTO column_comments VALUES ('message','msgid','Message id header');
+INSERT INTO column_comments VALUES ('message','from_complete','Complete from header of message');
+INSERT INTO column_comments VALUES ('message','to_complete','Complete to header of message');
+INSERT INTO column_comments VALUES ('message','subject','Subject of the message');
+INSERT INTO column_comments VALUES ('message','sent_date','Time/date message was sent (from Date header)');
+INSERT INTO column_comments VALUES ('message','refs','Contents of References: header');
+INSERT INTO column_comments VALUES ('message','spam_score','Spam score from spamassassin');
+INSERT INTO column_comments VALUES ('message','is_spam','True if this message was spam and should not be shown');
+CREATE INDEX message_msgid_idx ON message(msgid);
+CREATE UNIQUE INDEX message_msgid_from_complete_to_complete_subject_idx
+ ON message(msgid,from_complete,to_complete,subject);
+CREATE INDEX message_subject_idx ON message(subject);
+
+CREATE TABLE message_refs (
+ message INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE,
+ refs INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE,
+ inferred BOOLEAN DEFAULT FALSE,
+ primary_ref BOOLEAN DEFAULT FALSE,
+ CONSTRAINT message_doesnt_reference_itself CHECK (message <> refs)
+);
+CREATE UNIQUE INDEX message_refs_message_refs_idx ON message_refs(message,refs);
+CREATE INDEX message_refs_idx_refs ON message_refs(refs);
+CREATE INDEX message_refs_idx_message ON message_refs(message);
+INSERT INTO table_comments VALUES ('message_refs','Message references');
+INSERT INTO column_comments VALUES ('message_refs','message','Message id (matches message)');
+INSERT INTO column_comments VALUES ('message_refs','refs','Reference id (matches message)');
+INSERT INTO column_comments VALUES ('message_refs','inferred','TRUE if this message reference was reconstructed; primarily of use for messages which lack In-Reply-To: or References: headers');
+INSERT INTO column_comments VALUES ('message_refs','primary_ref','TRUE if this message->ref came from In-Reply-To: or similar.');
+
+
+
+CREATE TABLE correspondent_full_name(
+ correspondent INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE,
+ full_name TEXT NOT NULL,
+ last_seen TIMESTAMP NOT NULL DEFAULT NOW()
+);
+CREATE UNIQUE INDEX correspondent_full_name_correspondent_full_name_idx
+ ON correspondent_full_name(correspondent,full_name);
+CREATE INDEX correspondent_full_name_idx_full_name ON correspondent_full_name(full_name);
+CREATE INDEX correspondent_full_name_idx_last_seen ON correspondent_full_name(last_seen);
+INSERT INTO table_comments VALUES ('correspondent_full_name','Full names of BTS correspondents');
+INSERT INTO column_comments VALUES ('correspondent_full_name','correspondent','Correspondent ID (matches correspondent)');
+INSERT INTO column_comments VALUES ('correspondent_full_name','full_name','Correspondent full name (includes e-mail address)');
+
+CREATE TYPE message_correspondent_type AS ENUM ('to','from','envfrom','cc');
+
+CREATE TABLE message_correspondent (
+ message INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE,
+ correspondent INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE,
+ correspondent_type message_correspondent_type NOT NULL DEFAULT 'to'
+);
+INSERT INTO table_comments VALUES ('message_correspondent','Linkage between correspondent and message');
+INSERT INTO column_comments VALUES ('message_correspondent','message','Message id (matches message)');
+INSERT INTO column_comments VALUES ('message_correspondent','correspondent','Correspondent (matches correspondent)');
+INSERT INTO column_comments VALUES ('message_correspondent','correspondent_type','Type of correspondent (to, from, envfrom, cc, etc.)');
+
+CREATE UNIQUE INDEX message_correspondent_message_correspondent_correspondent_t_idx
+ ON message_correspondent(message,correspondent,correspondent_type);
+CREATE INDEX message_correspondent_idx_correspondent ON message_correspondent(correspondent);
+CREATE INDEX message_correspondent_idx_message ON message_correspondent(message);
+
+CREATE TABLE bug_message (
+ bug INT NOT NULL REFERENCES bug ON DELETE CASCADE ON UPDATE CASCADE,
+ message INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE,
+ message_number INT NOT NULL,
+ bug_log_offset INT,
+ offset_valid TIMESTAMP WITH TIME ZONE
+);
+CREATE UNIQUE INDEX bug_message_bug_message_idx ON bug_message(bug,message);
+CREATE INDEX bug_message_idx_bug_message_number ON bug_message(bug,message_number);
+INSERT INTO table_comments VALUES ('bug_mesage','Mapping between a bug and a message');
+INSERT INTO column_comments VALUES ('bug_message','bug','Bug id (matches bug)');
+INSERT INTO column_comments VALUES ('bug_message','message','Message id (matches message)');
+INSERT INTO column_comments VALUES ('bug_message','message_number','Message number in the bug log');
+INSERT INTO column_comments VALUES ('bug_message','bug_log_offset','Byte offset in the bug log');
+INSERT INTO column_comments VALUES ('bug_message','offset_valid','Time offset was valid');