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;
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
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
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,
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)');
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 '',
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');
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()
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,
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,
+ 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 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;