X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=sql%2Fdebbugs_schema.sql;h=4b154786878e4d036fbc8c76360b0f27e5575b33;hb=1fbaca61f2f551b5b2645ce2233a1d964bc93b30;hp=ea8446453cf4e861b4a30c4e9c99d79144679772;hpb=c869958af885ab6b3eabb8749bf1b6b3fcf238fb;p=debbugs.git diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index ea84464..4b15478 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -1,7 +1,8 @@ - +-- -*- 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; @@ -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'); @@ -305,7 +308,6 @@ INSERT INTO column_comments VALUES ('bug_tag','tag','Tag id (matches tag)'); CREATE UNIQUE INDEX bug_tag_bug_tag ON bug_tag (bug,tag); CREATE INDEX bug_tag_tag ON bug_tag (tag); -CREATE INDEX bug_tag_bug ON bug_tag (bug); CREATE TABLE user_tag ( id SERIAL PRIMARY KEY, @@ -330,7 +332,6 @@ INSERT INTO column_comments VALUES ('bug_user_tag','tag','User tag id (matches u CREATE UNIQUE INDEX bug_user_tag_bug_tag ON bug_user_tag (bug,user_tag); CREATE INDEX bug_user_tag_tag ON bug_user_tag (user_tag); -CREATE INDEX bug_user_tag_bug ON bug_user_tag (bug); CREATE TABLE bug_binpackage ( bug INT NOT NULL REFERENCES bug, @@ -346,17 +347,12 @@ CREATE TABLE bug_srcpackage ( src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE ); CREATE UNIQUE INDEX bug_srcpackage_id_pkg ON bug_srcpackage(bug,src_pkg); -CREATE INDEX bug_srcpackage_idx_bug ON bug_srcpackage(bug); CREATE INDEX bug_srcpackage_idx_src_pkg ON bug_srcpackage(src_pkg); INSERT INTO table_comments VALUES ('bug_srcpackage','Bug <-> source package mapping'); 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 @@ -375,6 +371,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 @@ -432,7 +434,7 @@ INSERT INTO column_comments VALUES ('src_associations','modified','Time this ent 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, @@ -441,13 +443,16 @@ 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); 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)'); @@ -461,9 +466,7 @@ CREATE TABLE message ( 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 '', @@ -474,9 +477,7 @@ INSERT INTO table_comments VALUES ('message','Messages sent to bugs'); 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'); @@ -506,7 +507,6 @@ INSERT INTO column_comments VALUES ('message_refs','primary_ref','TRUE if this m 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() @@ -516,11 +516,10 @@ CREATE UNIQUE INDEX correspondent_full_name_correspondent_full_name_idx 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, @@ -553,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;