+ id SERIAL PRIMARY KEY,
+ bug INT NOT NULL REFERENCES bug,
+ src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE
+);
+CREATE UNIQUE INDEX bug_srcpackage_id_pkg_id ON bug_srcpackage(bug,src_pkg);
+INSERT INTO table_comments VALUES ('bug_srcpackage','Bug <-> source package mapping');
+INSERT INTO column_comments VALUES ('bug_srcpackage','bug','Bug id (matches bug)');
+INSERT INTO column_comments VALUES ('bug_srcpackage','src_pkg','Source package id (matches src_pkg)');
+
+CREATE VIEW bug_package (bug,pkg_id,pkg_type,package) AS
+ SELECT b.bug,b.bin_pkg,'binary',bp.pkg FROM bug_binpackage b JOIN bin_pkg bp ON bp.id=b.bin_pkg UNION
+ SELECT s.bug,s.src_pkg,'source',sp.pkg FROM bug_srcpackage s JOIN src_pkg sp ON sp.id=s.src_pkg;
+
+CREATE VIEW binary_versions (src_pkg, src_ver, bin_pkg, arch, bin_ver) AS
+ SELECT sp.pkg AS src_pkg, sv.ver AS src_ver, bp.pkg AS bin_pkg, a.arch AS arch, b.ver AS bin_ver,
+ svb.ver AS src_ver_based_on, spb.pkg AS src_pkg_based_on
+ FROM bin_ver b JOIN arch a ON b.arch_id = a.id
+ JOIN bin_pkg bp ON b.bin_pkg = bp.id
+ JOIN src_ver sv ON b.src_ver_id = sv.id
+ JOIN src_pkg sp ON sv.src_pkg = sp.id
+ LEFT OUTER JOIN src_ver svb ON sv.based_on = svb.id
+ LEFT OUTER JOIN src_pkg spb ON spb.id = svb.src_pkg;
+
+CREATE TABLE suite (
+ id SERIAL PRIMARY KEY,
+ suite_name TEXT NOT NULL UNIQUE,
+ version TEXT,
+ codename TEXT,
+ active BOOLEAN DEFAULT TRUE);
+CREATE INDEX ON suite(codename);
+CREATE INDEX ON suite(version);
+INSERT INTO table_comments VALUES ('suite','Debian Release Suite (stable, testing, etc.)');
+INSERT INTO column_comments VALUES ('suite','id','Suite id');
+INSERT INTO column_comments VALUES ('suite','suite_name','Suite name');
+INSERT INTO column_comments VALUES ('suite','version','Suite version; NULL if there is no appropriate version');
+INSERT INTO column_comments VALUES ('suite','codename','Suite codename');
+INSERT INTO column_comments VALUES ('suite','active','TRUE if the suite is still accepting uploads');
+
+CREATE TABLE bin_associations (
+ id SERIAL PRIMARY KEY,
+ suite INT NOT NULL REFERENCES suite ON DELETE CASCADE ON UPDATE CASCADE,
+ bin INT NOT NULL REFERENCES bin_ver ON DELETE CASCADE ON UPDATE CASCADE,
+ created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
+ modified TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
+);
+INSERT INTO table_comments VALUES ('bin_associations','Binary <-> suite associations');
+INSERT INTO column_comments VALUES ('bin_associations','id','Binary <-> suite association id');
+INSERT INTO column_comments VALUES ('bin_associations','suite','Suite id (matches suite)');
+INSERT INTO column_comments VALUES ('bin_associations','bin','Binary version id (matches bin_ver)');
+INSERT INTO column_comments VALUES ('bin_associations','created','Time this binary package entered this suite');
+INSERT INTO column_comments VALUES ('bin_associations','modified','Time this entry was modified');
+
+CREATE TABLE src_associations (
+ id SERIAL PRIMARY KEY,
+ suite INT NOT NULL REFERENCES suite ON DELETE CASCADE ON UPDATE CASCADE,
+ source INT NOT NULL REFERENCES src_ver ON DELETE CASCADE ON UPDATE CASCADE,
+ created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
+ modified TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
+);
+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 TABLE message (
+ id SERIAL PRIMARY KEY,
+ msgid TEXT,
+ from_complete TEXT,
+ from_addr TEXT,
+ to_complete TEXT,
+ to_addr TEXT,
+ subject TEXT NOT NULL DEFAULT '',
+ sent_date TIMESTAMP WITH TIME ZONE,
+ refs TEXT NOT NULL DEFAULT '',
+ spam_score FLOAT,
+ is_spam BOOLEAN 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','from_addr','Address(es) of From: headers');
+INSERT INTO column_comments VALUES ('message','to_complete','Complete to header of message');
+INSERT INTO column_comments VALUES ('message','to_addr','Address(es) of To: header');
+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 ON message(msgid);
+
+CREATE TABLE message_refs (
+ id SERIAL PRIMARY KEY,
+ 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 ON message_refs(message,refs);
+CREATE INDEX ON message_refs(refs);
+CREATE INDEX 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 (
+ id SERIAL PRIMARY KEY,
+ addr TEXT NOT NULL UNIQUE
+);
+INSERT INTO table_comments VALUES ('correspondent','Individual who has corresponded with the BTS');
+INSERT INTO column_comments VALUES ('correspondent','id','Correspondent ID');
+INSERT INTO column_comments VALUES ('correspondent','addr','Correspondent address');
+
+CREATE TABLE correspondent_full_name(
+ id SERIAL PRIMARY KEY,
+ correspondent INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE,
+ full_name TEXT NOT NULL UNIQUE
+);
+
+INSERT INTO table_comments VALUES ('correspondent_full_name','Full names of BTS correspondents');
+INSERT INTO column_comments VALUES ('correspondent_full_name','id','Correspondent full name id');
+INSERT INTO column_comments VALUES ('correspondent_full_name','correpsondent','Correspondent ID (matches correspondent)');
+INSERT INTO column_comments VALUES ('correspondent_full_name','full_name','Correspondent full name (includes e-mail address)');
+
+CREATE TABLE bug_submitter (
+ id SERIAL PRIMARY KEY,
+ bug INT NOT NULL REFERENCES bug ON DELETE CASCADE ON UPDATE CASCADE,
+ submitter INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX on bug_submitter(submitter);
+CREATE UNIQUE INDEX on bug_submitter(bug,submitter);
+
+INSERT INTO table_comments VALUES ('bug_submitter','Submitter of a bug (connects to correspondent)');
+INSERT INTO column_comments VALUES ('bug_submitter','id','Bug Submitter ID');
+INSERT INTO column_comments VALUES ('bug_submitter','bug','Bug which was submitted by this submitter');
+INSERT INTO column_comments VALUES ('bug_submitter','submitter','Bug submitter (connects to correspondent)');
+
+CREATE TABLE bug_done_by (
+ id SERIAL PRIMARY KEY,
+ bug INT NOT NULL REFERENCES bug ON DELETE CASCADE ON UPDATE CASCADE,
+ done_by INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE
+);
+CREATE INDEX on bug_done_by(done_by);
+CREATE UNIQUE INDEX on bug_done_by(bug,done_by);
+
+INSERT INTO table_comments VALUES ('bug_done_by','Correspondent who finished this bug (emailed -done)');
+INSERT INTO column_comments VALUES ('bug_done_by','id','Bug Done By ID');
+INSERT INTO column_comments VALUES ('bug_done_by','bug','Bug number which was done');
+INSERT INTO column_comments VALUES ('bug_done_by','done_by','Bug finisher (connects to correspondent)');
+
+CREATE TYPE message_correspondent_type AS ENUM ('to','from','envfrom','cc');
+
+CREATE TABLE message_correspondent (
+ id SERIAL PRIMARY KEY,
+ 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 ON message_correspondent(message,correspondent,correspondent_type);
+CREATE INDEX ON message_correspondent(correspondent);
+CREATE INDEX ON message_correspondent(message);
+
+CREATE TABLE bug_message (
+ id SERIAL PRIMARY KEY,
+ 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
projects / debbugs.git / blobdiff