-
+-- -*- mode: sql; sql-product: postgres; -*-
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;
-- 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);
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');
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,
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);
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;