X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=sql%2Fdebbugs_schema.sql;h=3a75bac8b3b7af8767e3349461fd45c347535954;hb=b1252b6797aa6a79d00a32165fb2fa8fb1bd9318;hp=18b14c60388cc0fe2e4a7fb78733436c3d3903bd;hpb=9a13ca040a616ccf16fe6acd706de294b7945c10;p=debbugs.git diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index 18b14c6..3a75bac 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -1,4 +1,4 @@ - +-- -*- mode: sql; sql-product: postgres; -*- DROP TABLE bug_status_cache CASCADE; DROP VIEW bug_package CASCADE; DROP VIEW binary_versions CASCADE; @@ -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'); @@ -286,6 +288,74 @@ INSERT INTO column_comments VALUES ('bin_ver','src_ver','Source version (matchin INSERT INTO column_comments VALUES ('bin_ver','arch','Architecture id (matches arch)'); INSERT INTO column_comments VALUES ('bin_ver','ver','Binary version'); +CREATE TABLE bin_pkg_src_pkg ( + bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE, + src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE +); +CREATE UNIQUE INDEX bin_pkg_src_pkg_bin_pkg_src_pkg ON bin_pkg_src_pkg (bin_pkg,src_pkg); +CREATE UNIQUE INDEX bin_pkg_src_pkg_src_pkg_bin_pkg ON bin_pkg_src_pkg (src_pkg,bin_pkg); + + +INSERT INTO table_comments VALUES ('bin_pkg_src_pkg', + 'Binary package <-> source package mapping sumpmary table'); +INSERT INTO column_comments VALUES ('bin_pkg_src_pkg','bin_pkg','Binary package id (matches bin_pkg)'); +INSERT INTO column_comments VALUES ('bin_pkg_src_pkg','src_pkg','Source package id (matches src_pkg)'); + +CREATE OR REPLACE FUNCTION bin_ver_to_src_pkg(bin_ver INT) RETURNS INT + AS $src_pkg_from_bin_ver$ + DECLARE + src_pkg int; + BEGIN + SELECT sv.src_pkg INTO STRICT src_pkg + FROM bin_ver bv JOIN src_ver sv ON bv.src_ver=sv.id + WHERE bv.id=bin_ver; + RETURN src_pkg; + END + $src_pkg_from_bin_ver$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION src_ver_to_src_pkg(src_ver INT) RETURNS INT + AS $src_ver_to_src_pkg$ + DECLARE + src_pkg int; + BEGIN + SELECT sv.src_pkg INTO STRICT src_pkg + FROM src_ver sv WHERE sv.id=src_ver; + RETURN src_pkg; + END + $src_ver_to_src_pkg$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION update_bin_pkg_src_pkg_bin_ver () RETURNS TRIGGER + AS $update_bin_pkg_src_pkg_bin_ver$ + DECLARE + src_ver_rows integer; + BEGIN + IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE' ) THEN + -- if there is still a bin_ver with this src_pkg, then do nothing + PERFORM * FROM bin_ver bv JOIN src_ver sv ON bv.src_ver = sv.id + WHERE sv.id = OLD.src_ver LIMIT 2; + GET DIAGNOSTICS src_ver_rows = ROW_COUNT; + IF (src_ver_rows <= 1) THEN + DELETE FROM bin_pkg_src_pkg + WHERE bin_pkg=OLD.bin_pkg AND + src_pkg=src_ver_to_src_pkg(OLD.src_ver); + END IF; + END IF; + IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN + BEGIN + INSERT INTO bin_pkg_src_pkg (bin_pkg,src_pkg) + VALUES (NEW.bin_pkg,src_ver_to_src_pkg(NEW.src_ver)) + ON CONFLICT (bin_pkg,src_pkg) DO NOTHING; + END; + END IF; + RETURN NULL; + END + $update_bin_pkg_src_pkg_bin_ver$ LANGUAGE plpgsql; + +CREATE TRIGGER bin_ver_update_bin_pkg_src_pkg +AFTER INSERT OR UPDATE OR DELETE ON bin_ver +FOR EACH ROW EXECUTE PROCEDURE update_bin_pkg_src_pkg_bin_ver(); + + CREATE TABLE tag ( id SERIAL PRIMARY KEY, tag TEXT NOT NULL UNIQUE, @@ -336,6 +406,7 @@ CREATE TABLE bug_binpackage ( bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE ); CREATE UNIQUE INDEX bug_binpackage_id_pkg ON bug_binpackage(bug,bin_pkg); +CREATE UNIQUE INDEX bug_binpackage_bin_pkg_bug_idx ON bug_binpackage(bin_pkg,bug); INSERT INTO table_comments VALUES ('bug_binpackage','Bug <-> binary package mapping'); INSERT INTO column_comments VALUES ('bug_binpackage','bug','Bug id (matches bug)'); INSERT INTO column_comments VALUES ('bug_binpackage','bin_pkg','Binary package id (matches bin_pkg)'); @@ -441,7 +512,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); @@ -568,19 +642,30 @@ CREATE VIEW bug_status --(id,bug_num,tags,subject, JOIN src_pkg sp ON bsp.src_pkg=sp.id WHERE bsp.bug=b.id) AS package ) AS package, + (SELECT string_agg(affects.affects,',' ORDER BY affects) + FROM (SELECT bp.pkg AS affects + FROM bug_affects_binpackage bbp + JOIN bin_pkg bp ON bbp.bin_pkg=bp.id + WHERE bbp.bug=b.id + UNION + SELECT CONCAT('src:',sp.pkg) AS affects + FROM bug_affects_srcpackage bsp + JOIN src_pkg sp ON bsp.src_pkg=sp.id + WHERE bsp.bug=b.id) AS affects + ) AS affects, + (SELECT msgid FROM message m LEFT JOIN bug_message bm ON bm.message=m.id + WHERE bm.bug=b.id ORDER BY m.sent_date ASC limit 1) AS message_id, b.submitter_full AS originator, - b.log_modified AS log_modified, - b.creation AS date, - b.last_modified AS last_modified, + 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 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 FALSE)