Update DB Schema version to 6

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index 95f742edf86b6f2d4d5c44a88ac35b10cec4ccc3..a9d2232563713615accd6fe68f4006fde8eb1883 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -350,10 +350,6 @@ INSERT INTO table_comments VALUES ('bug_srcpackage','Bug <-> source package mapp
 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
@@ -372,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
@@ -443,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)');