add unknown_affects to the bug table

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index fc480d4bfa00a40d7d3f735cb1e8af31143c4e7b..c091a6e5396d971aa76d54dc0fe0038b4b1eda7f 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -113,7 +113,8 @@ CREATE TABLE bug (
        -- submitter would ideally be NOT NULL, but there are some ancient bugs which do not have submitters
        submitter INT REFERENCES correspondent(id),
        submitter_full TEXT NOT NULL DEFAULT '',
-       unknown_packages TEXT NOT NULL DEfAULT ''
+       unknown_packages TEXT NOT NULL DEFAULT '',
+       unknown_affects TEXT NOT NULL DEFAULT ''
 );
 CREATE INDEX bug_idx_owner ON bug(owner);
 CREATE INDEX bug_idx_submitter ON bug(submitter);
@@ -139,6 +140,7 @@ INSERT INTO column_comments VALUES ('bug','done','Individual who did the -done;
 INSERT INTO column_comments VALUES ('bug','owner','Individual who owns this bug; empty if no one owns it');
 INSERT INTO column_comments VALUES ('bug','submitter','Individual who submitted this bug; empty if there is no submitter');
 INSERT INTO column_comments VALUES ('bug','unknown_packages','Package name if the package is not known');
+INSERT INTO column_comments VALUES ('bug','unknown_affects','Package name if the affected package is not known');
 
 
 
@@ -572,17 +574,16 @@ CREATE VIEW bug_status --(id,bug_num,tags,subject,
        b.log_modified AS log_modified,
        b.creation AS date,
        b.last_modified AS last_modified,
+       b.done_full AS done,
        string_agg(bb.blocks::text,' ' ORDER BY bb.blocks) AS blocks,
        string_agg(bbb.bug::text,' ' ORDER BY bbb.bug) AS blockedby,
        (SELECT string_agg(bug.bug::text,' ' ORDER BY bug.bug)
-        FROM (SELECT b.id AS bug
-                         UNION
-                  SELECT bm.merged AS bug FROM bug_merged bm WHERE bm.bug=b.id
+        FROM (SELECT bm.merged AS bug FROM bug_merged bm WHERE bm.bug=b.id
                          UNION
-                  SELECT bm.bug AS bug FROM bug_merged bm WHERE bm.merged=b.id) AS bug) AS merged,
+        SELECT bm.bug AS bug FROM bug_merged bm WHERE bm.merged=b.id) AS bug) AS mergedwith,
        (SELECT string_agg(bv.ver_string,' ') FROM bug_ver bv WHERE bv.bug=b.id AND bv.found IS TRUE)
                AS found_versions,
-       (SELECT string_agg(bv.ver_string,' ') FROM bug_ver bv WHERE bv.bug=b.id AND bv.found IS TRUE)
+       (SELECT string_agg(bv.ver_string,' ') FROM bug_ver bv WHERE bv.bug=b.id AND bv.found IS FALSE)
                AS fixed_versions
        FROM bug b
        LEFT JOIN bug_tag bt ON bt.bug=b.id