X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=sql%2Fdebbugs_schema.sql;h=f015d452d8727aac43e67b4bce6f5653bfa91993;hb=004b1a2e2a548c2f35f2bcf64a0cb56355f7b506;hp=f2a420d3a2f027b733980171894935af9878f8a0;hpb=53c435119200ab9b1c2538a96b8374c51a078855;p=debbugs.git

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index f2a420d..f015d45 100644
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -2,6 +2,7 @@
 DROP TABLE bug_status_cache CASCADE;
 DROP VIEW bug_package CASCADE;
 DROP VIEW binary_versions CASCADE;
+DROP VIEW bug_status CASCADE;
 DROP TABLE bug_tag CASCADE;
 DROP TABLE tag CASCADE;
 DROP TABLE bug_user_tag CASCADE;
@@ -112,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);
@@ -138,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');
 
 
 
@@ -440,7 +443,10 @@ CREATE TABLE bug_status_cache (
        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 UNIQUE INDEX bug_status_cache_bug_suite_arch_idx ON
+       bug_status_cache(bug,suite,arch);
+CREATE UNIQUE INDEX bug_status_cache_bug_col_suite_col_arch_idx ON
+       bug_status_cache(bug,COALESCE(suite,0),COALESCE(arch,0));
 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);
@@ -546,3 +552,45 @@ INSERT INTO column_comments VALUES ('bug_message','message_number','Message numb
 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');
 
+CREATE VIEW bug_status --(id,bug_num,tags,subject,
+--        severity,package,originator,log_modified,date,
+--        last_modified, blocks, blockedby, mergedwith,
+--        fixed_versions,found_versions)
+       AS
+       SELECT b.id AS id,
+       b.id AS bug_num,
+       string_agg(t.tag,',') AS tags,
+       b.subject AS subject,
+       (SELECT s.severity FROM severity s WHERE s.id=b.severity) AS severity,
+       (SELECT string_agg(package.package,',' ORDER BY package)
+        FROM (SELECT bp.pkg AS package
+	             FROM bug_binpackage bbp
+                     JOIN bin_pkg bp ON bbp.bin_pkg=bp.id
+                     WHERE bbp.bug=b.id
+	      UNION
+	      SELECT CONCAT('src:',sp.pkg) AS package
+	      	     FROM bug_srcpackage bsp
+	      	     JOIN src_pkg sp ON bsp.src_pkg=sp.id
+                     WHERE bsp.bug=b.id) AS package
+        ) AS package,
+	b.submitter_full AS originator,
+	EXTRACT(EPOCH FROM b.log_modified) AS log_modified,
+	EXTRACT(EPOCH FROM b.creation) AS date,
+	EXTRACT(EPOCH FROM 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 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 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 FALSE)
+		AS fixed_versions
+	FROM bug b
+	LEFT JOIN bug_tag bt ON bt.bug=b.id
+	LEFT JOIN tag t ON bt.tag=t.id
+	LEFT JOIN bug_blocks bb ON bb.bug=b.id
+	LEFT JOIN bug_blocks bbb ON bbb.blocks=b.id
+	GROUP BY b.id;