Merge remote-tracking branch 'local/master' into database

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index ea8446453cf4e861b4a30c4e9c99d79144679772..f2a420d3a2f027b733980171894935af9878f8a0 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -305,7 +305,6 @@ INSERT INTO column_comments VALUES ('bug_tag','tag','Tag id (matches tag)');
 
 CREATE UNIQUE INDEX bug_tag_bug_tag ON bug_tag (bug,tag);
 CREATE INDEX bug_tag_tag ON bug_tag (tag);
-CREATE INDEX bug_tag_bug ON bug_tag (bug);
 
 CREATE TABLE user_tag (
        id SERIAL PRIMARY KEY,
@@ -330,7 +329,6 @@ INSERT INTO column_comments VALUES ('bug_user_tag','tag','User tag id (matches u
 
 CREATE UNIQUE INDEX bug_user_tag_bug_tag ON bug_user_tag (bug,user_tag);
 CREATE INDEX bug_user_tag_tag ON bug_user_tag (user_tag);
-CREATE INDEX bug_user_tag_bug ON bug_user_tag (bug);
 
 CREATE TABLE bug_binpackage (
        bug INT NOT NULL REFERENCES bug,
@@ -346,17 +344,12 @@ CREATE TABLE bug_srcpackage (
        src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE
 );
 CREATE UNIQUE INDEX bug_srcpackage_id_pkg ON bug_srcpackage(bug,src_pkg);
-CREATE INDEX bug_srcpackage_idx_bug ON bug_srcpackage(bug);
 CREATE INDEX bug_srcpackage_idx_src_pkg ON bug_srcpackage(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 TABLE bug_affects_binpackage (
        bug INT NOT NULL REFERENCES bug,
        bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE
@@ -375,6 +368,12 @@ INSERT INTO table_comments VALUES ('bug_affects_srcpackage','Bug <-> source pack
 INSERT INTO column_comments VALUES ('bug_affects_srcpackage','bug','Bug id (matches bug)');
 INSERT INTO column_comments VALUES ('bug_affects_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 UNION
+       SELECT b.bug,b.bin_pkg,'binary_affects',bp.pkg FROM bug_affects_binpackage b JOIN bin_pkg bp ON bp.id=b.bin_pkg UNION
+              SELECT s.bug,s.src_pkg,'source_affects',sp.pkg FROM bug_affects_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
@@ -432,7 +431,7 @@ INSERT INTO column_comments VALUES ('src_associations','modified','Time this ent
 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 TYPE bug_status_type AS ENUM ('absent','found','fixed','undef');
 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,
@@ -446,8 +445,8 @@ 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);
+CREATE INDEX bug_status_cache_idx_asof ON bug_status_cache(asof);
 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)');
@@ -461,9 +460,7 @@ CREATE TABLE message (
        id SERIAL PRIMARY KEY,
        msgid TEXT NOT NULL DEFAULT '',
        from_complete TEXT NOT NULL DEFAULT '',
-       from_addr TEXT NOT NULL DEFAULT '',
        to_complete TEXT NOT NULL DEFAULT '',
-       to_addr TEXT NOT NULL DEFAULT '',
        subject TEXT NOT NULL DEFAULT '',
        sent_date TIMESTAMP WITH TIME ZONE,
        refs TEXT NOT NULL DEFAULT '',
@@ -474,9 +471,7 @@ 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');
@@ -506,7 +501,6 @@ INSERT INTO column_comments VALUES ('message_refs','primary_ref','TRUE if this m
 
 
 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,
        last_seen TIMESTAMP NOT NULL DEFAULT NOW()
@@ -516,11 +510,10 @@ CREATE UNIQUE INDEX correspondent_full_name_correspondent_full_name_idx
 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','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','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 TYPE message_correspondent_type AS ENUM ('to','from','envfrom','cc','recv');
 
 CREATE TABLE message_correspondent (
        message INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE,