X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=sql%2Fdebbugs_schema.sql;h=3a75bac8b3b7af8767e3349461fd45c347535954;hb=b1252b6797aa6a79d00a32165fb2fa8fb1bd9318;hp=cf1e950919b4de6f19521a2d731c733ea84a817d;hpb=d274b1d3102ade0366beab97aff4b3ee9b94c4e0;p=debbugs.git diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index cf1e950..3a75bac 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -1,64 +1,98 @@ - +-- -*- 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; +DROP TABLE user_tag CASCADE; +DROP TABLE severity CASCADE; DROP TABLE bug CASCADE; -DROP TYPE bug_severity CASCADE; DROP TABLE src_pkg CASCADE; DROP TABLE bug_ver CASCADE; -DROP TABLE src_pkg_alias CASCADE; DROP TABLE src_ver CASCADE; DROP TABLE arch CASCADE; DROP TABLE bin_ver CASCADE; DROP TABLE bin_pkg CASCADE; DROP TABLE bug_blocks CASCADE; DROP TABLE bug_merged CASCADE; -DROP VIEW bug_package CASCADE; DROP TABLE bug_srcpackage CASCADE; DROP TABLE bug_binpackage CASCADE; -DROP VIEW bug_package CASCADE; -DROP VIEW binary_versions CASCADE; +DROP TABLE bug_affects_binpackage CASCADE; +DROP TABLE bug_affects_srcpackage CASCADE; DROP TABLE suite CASCADE; DROP TABLE bin_associations CASCADE; DROP TABLE src_associations CASCADE; DROP TABLE maintainer CASCADE; DROP TABLE bug_message CASCADE; DROP TABLE message_correspondent CASCADE; +DROP TABLE correspondent_full_name CASCADE; DROP TABLE correspondent CASCADE; DROP TABLE message_refs CASCADE; DROP TABLE message CASCADE; DROP TYPE message_correspondent_type CASCADE; DROP TABLE table_comments CASCADE; DROP TABLE column_comments CASCADE; +DROP TYPE bug_status_type CASCADE; -- the following two tables are used to provide documentation about -- the tables and columns for DBIx::Class::Schema::Loader CREATE TABLE table_comments ( - table_name TEXT UNIQUE NOT NULL, + table_name TEXT NOT NULL, comment_text TEXT NOT NULL ); +CREATE UNIQUE INDEX table_comments_table_name_idx ON table_comments(table_name); CREATE TABLE column_comments ( table_name TEXT NOT NULL, column_name TEXT NOT NULL, comment_text TEXT NOT NULL ); -CREATE UNIQUE INDEX ON column_comments(table_name,column_name); +CREATE UNIQUE INDEX column_comments_table_name_column_name_idx ON column_comments(table_name,column_name); --- severities -CREATE TYPE bug_severity AS ENUM ('wishlist','minor','normal', - 'important','serious','grave','critical'); + +CREATE TABLE correspondent ( + id SERIAL PRIMARY KEY, + addr TEXT NOT NULL +); +CREATE UNIQUE INDEX correspondent_addr_idx ON correspondent(addr); +INSERT INTO table_comments VALUES ('correspondent','Individual who has corresponded with the BTS'); +INSERT INTO column_comments VALUES ('correspondent','id','Correspondent ID'); +INSERT INTO column_comments VALUES ('correspondent','addr','Correspondent address'); CREATE TABLE maintainer ( id SERIAL PRIMARY KEY, - name TEXT NOT NULL UNIQUE, + name TEXT NOT NULL, + correspondent INT NOT NULL REFERENCES correspondent(id), created TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL, modified TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL ); +CREATE UNIQUE INDEX maintainer_name_idx ON maintainer(name); +CREATE INDEX maintainer_idx_correspondent ON maintainer(correspondent); INSERT INTO table_comments VALUES ('maintainer','Package maintainer names'); INSERT INTO column_comments VALUES ('maintainer','id','Package maintainer id'); INSERT INTO column_comments VALUES ('maintainer','name','Name of package maintainer'); +INSERT INTO column_comments VALUES ('maintainer','correspondent','Correspondent ID'); INSERT INTO column_comments VALUES ('maintainer','created','Time maintainer record created'); INSERT INTO column_comments VALUES ('maintainer','modified','Time maintainer record modified'); + +CREATE TABLE severity ( + id SERIAL PRIMARY KEY, + severity TEXT NOT NULL, + ordering INT NOT NULL DEFAULT 5, + strong BOOLEAN DEFAULT FALSE, + obsolete BOOLEAN DEFAULT FALSE +); +CREATE UNIQUE INDEX severity_severity_idx ON severity(severity); +CREATE INDEX severity_ordering_idx ON severity(ordering); +INSERT INTO table_comments VALUES ('severity','Bug severity'); +INSERT INTO column_comments VALUES ('severity','id','Severity id'); +INSERT INTO column_comments VALUES ('severity','severity','Severity name'); +INSERT INTO column_comments VALUES ('severity','ordering','Severity ordering (more severe severities have higher numbers)'); +INSERT INTO column_comments VALUES ('severity','strong','True if severity is a strong severity'); +INSERT INTO column_comments VALUES ('severity','obsolete','Whether a severity level is obsolete (should not be set on new bugs)'); + -- bugs table CREATE TABLE bug ( id INTEGER NOT NULL PRIMARY KEY, @@ -71,11 +105,26 @@ CREATE TABLE bug ( summary TEXT NOT NULL DEFAULT '', outlook TEXT NOT NULL DEFAULT '', subject TEXT NOT NULL, - done TEXT NOT NULL DEFAULT '', - owner TEXT NOT NULL DEFAULT '', - unknown_packages TEXT NOT NULL DEfAULT '', - severity bug_severity DEFAULT 'normal'::bug_severity + severity INT NOT NULL REFERENCES severity(id), + done INT REFERENCES correspondent(id), + done_full TEXT NOT NULL DEFAULT '', + owner INT REFERENCES correspondent(id), + owner_full TEXT NOT NULL DEFAULT '', + -- 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_affects TEXT NOT NULL DEFAULT '' ); +CREATE INDEX bug_idx_owner ON bug(owner); +CREATE INDEX bug_idx_submitter ON bug(submitter); +CREATE INDEX bug_idx_done ON bug(done); +CREATE INDEX bug_idx_forwarded ON bug(forwarded); +CREATE INDEX bug_idx_last_modified ON bug(last_modified); +CREATE INDEX bug_idx_severity ON bug(severity); +CREATE INDEX bug_idx_creation ON bug(creation); +CREATE INDEX bug_idx_log_modified ON bug(log_modified); + INSERT INTO table_comments VALUES ('bug','Bugs'); INSERT INTO column_comments VALUES ('bug','id','Bug number'); INSERT INTO column_comments VALUES ('bug','creation','Time bug created'); @@ -88,44 +137,57 @@ INSERT INTO column_comments VALUES ('bug','summary','Summary of the bug; empty i INSERT INTO column_comments VALUES ('bug','outlook','Outlook of the bug; empty if it has no outlook'); INSERT INTO column_comments VALUES ('bug','subject','Subject of the bug'); INSERT INTO column_comments VALUES ('bug','done','Individual who did the -done; empty if it has never been -done'); -INSERT INTO column_comments VALUES ('bug','owner','Individual who did the -done; empty if it has never been -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','severity','Bug severity'); +INSERT INTO column_comments VALUES ('bug','unknown_affects','Package name if the affected package is not known'); CREATE TABLE bug_blocks ( - bug_id INT NOT NULL REFERENCES bug, + id SERIAL PRIMARY KEY, + bug INT NOT NULL REFERENCES bug, blocks INT NOT NULL REFERENCES bug, - CONSTRAINT bug_doesnt_block_itself CHECK (bug_id <> blocks) + CONSTRAINT bug_doesnt_block_itself CHECK (bug <> blocks) ); -CREATE UNIQUE INDEX bug_blocks_bug_id_blocks_idx ON bug_blocks(bug_id,blocks); -CREATE INDEX bug_blocks_bug_id_idx ON bug_blocks(bug_id); +CREATE UNIQUE INDEX bug_blocks_bug_id_blocks_idx ON bug_blocks(bug,blocks); +CREATE INDEX bug_blocks_bug_id_idx ON bug_blocks(bug); CREATE INDEX bug_blocks_blocks_idx ON bug_blocks(blocks); INSERT INTO table_comments VALUES ('bug_blocks','Bugs which block other bugs'); -INSERT INTO column_comments VALUES ('bug_blocks','bug_id','Bug number'); -INSERT INTO column_comments VALUES ('bug_blocks','blocks','Bug number which is blocked by bug_id'); +INSERT INTO column_comments VALUES ('bug_blocks','bug','Bug number'); +INSERT INTO column_comments VALUES ('bug_blocks','blocks','Bug number which is blocked by bug'); CREATE TABLE bug_merged ( - bug_id INT NOT NULL REFERENCES bug, + id SERIAL PRIMARY KEY, + bug INT NOT NULL REFERENCES bug, merged INT NOT NULL REFERENCES bug, - CONSTRAINT bug_doesnt_merged_itself CHECK (bug_id <> merged) + CONSTRAINT bug_doesnt_merged_itself CHECK (bug <> merged) ); -CREATE UNIQUE INDEX bug_merged_bug_id_merged_idx ON bug_merged(bug_id,merged); -CREATE INDEX bug_merged_bug_id_idx ON bug_merged(bug_id); +CREATE UNIQUE INDEX bug_merged_bug_id_merged_idx ON bug_merged(bug,merged); +CREATE INDEX bug_merged_bug_id_idx ON bug_merged(bug); CREATE INDEX bug_merged_merged_idx ON bug_merged(merged); INSERT INTO table_comments VALUES ('bug_merged','Bugs which are merged with other bugs'); -INSERT INTO column_comments VALUES ('bug_merged','bug_id','Bug number'); -INSERT INTO column_comments VALUES ('bug_merged','merged','Bug number which is merged with bug_id'); +INSERT INTO column_comments VALUES ('bug_merged','bug','Bug number'); +INSERT INTO column_comments VALUES ('bug_merged','merged','Bug number which is merged with bug'); CREATE TABLE src_pkg ( id SERIAL PRIMARY KEY, - pkg TEXT NOT NULL UNIQUE, - pseduopkg BOOLEAN DEFAULT FALSE, - alias_of INT REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE - CONSTRAINT src_pkg_doesnt_alias_itself CHECK (id <> alias_of) + pkg TEXT NOT NULL, + pseduopkg BOOLEAN NOT NULL DEFAULT FALSE, + alias_of INT REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE, + creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + disabled TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'infinity'::timestamp with time zone, + last_modified TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + obsolete BOOLEAN NOT NULL DEFAULT FALSE, + CONSTRAINT src_pkg_doesnt_alias_itself CHECK (id <> alias_of), + CONSTRAINT src_pkg_is_obsolete_if_disabled CHECK ( + (obsolete IS FALSE AND disabled='infinity'::timestamp with time zone) OR + (obsolete IS TRUE AND disabled < 'infinity'::timestamp with time zone)) ); +CREATE INDEX src_pkg_pkg ON src_pkg(pkg); +CREATE UNIQUE INDEX src_pkg_pkg_null ON src_pkg(pkg) WHERE disabled='infinity'::timestamp with time zone; +CREATE UNIQUE INDEX src_pkg_pkg_disabled ON src_pkg(pkg,disabled); INSERT INTO table_comments VALUES ('src_pkg','Source packages'); INSERT INTO column_comments VALUES ('src_pkg','id','Source package id'); INSERT INTO column_comments VALUES ('src_pkg','pkg','Source package name'); @@ -136,47 +198,49 @@ INSERT INTO column_comments VALUES ('src_pkg','alias_of','Source package id whic CREATE TABLE src_ver ( id SERIAL PRIMARY KEY, - src_pkg_id INT NOT NULL REFERENCES src_pkg + src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE, - ver public.debversion NOT NULL, - maintainer_id INT REFERENCES maintainer + ver debversion NOT NULL, + maintainer INT REFERENCES maintainer ON UPDATE CASCADE ON DELETE SET NULL, upload_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), based_on INT REFERENCES src_ver ON UPDATE CASCADE ON DELETE CASCADE ); -CREATE UNIQUE INDEX src_ver_src_pkg_id_ver ON src_ver(src_pkg_id,ver); +CREATE UNIQUE INDEX src_ver_src_pkg_id_ver ON src_ver(src_pkg,ver); INSERT INTO table_comments VALUES ('src_ver','Source Package versions'); INSERT INTO column_comments VALUES ('src_ver','id','Source package version id'); -INSERT INTO column_comments VALUES ('src_ver','src_pkg_id','Source package id (matches src_pkg table)'); +INSERT INTO column_comments VALUES ('src_ver','src_pkg','Source package id (matches src_pkg table)'); INSERT INTO column_comments VALUES ('src_ver','ver','Version of the source package'); -INSERT INTO column_comments VALUES ('src_ver','maintainer_id','Maintainer id (matches maintainer table)'); +INSERT INTO column_comments VALUES ('src_ver','maintainer','Maintainer id (matches maintainer table)'); INSERT INTO column_comments VALUES ('src_ver','upload_date','Date this version of the source package was uploaded'); INSERT INTO column_comments VALUES ('src_ver','based_on','Source package version this version is based on'); CREATE TABLE bug_ver ( - bug_id INT NOT NULL REFERENCES bug + id SERIAL PRIMARY KEY, + bug INT NOT NULL REFERENCES bug ON UPDATE CASCADE ON DELETE RESTRICT, ver_string TEXT, - src_pkg_id INT REFERENCES src_pkg + src_pkg INT REFERENCES src_pkg ON UPDATE CASCADE ON DELETE SET NULL, - src_ver_id INT REFERENCES src_ver + src_ver INT REFERENCES src_ver ON UPDATE CASCADE ON DELETE SET NULL, found BOOLEAN NOT NULL DEFAULT TRUE, creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), last_modified TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -CREATE INDEX bug_ver_src_pkg_id_idx ON bug_ver(src_pkg_id); -CREATE INDEX bug_ver_src_pkg_id_src_ver_id_idx ON bug_ver(src_pkg_id,src_ver_id); -CREATE INDEX bug_ver_src_ver_id_idx ON bug_ver(src_ver_id); -CREATE UNIQUE INDEX ON bug_ver(bug_id,ver_string,found); +CREATE INDEX bug_ver_src_pkg_id_idx ON bug_ver(src_pkg); +CREATE INDEX bug_ver_src_pkg_id_src_ver_id_idx ON bug_ver(src_pkg,src_ver); +CREATE INDEX bug_ver_src_ver_id_idx ON bug_ver(src_ver); +CREATE UNIQUE INDEX bug_ver_bug_ver_string_found_idx ON bug_ver(bug,ver_string,found); INSERT INTO table_comments VALUES ('bug_ver','Bug versions'); -INSERT INTO column_comments VALUES ('bug_ver','bug_id','Bug number'); +INSERT INTO column_comments VALUES ('bug_ver','id','Bug version id'); +INSERT INTO column_comments VALUES ('bug_ver','bug','Bug number'); INSERT INTO column_comments VALUES ('bug_ver','ver_string','Version string'); -INSERT INTO column_comments VALUES ('bug_ver','src_pkg_id','Source package id (matches src_pkg table)'); -INSERT INTO column_comments VALUES ('bug_ver','src_ver_id','Source package version id (matches src_ver table)'); +INSERT INTO column_comments VALUES ('bug_ver','src_pkg','Source package id (matches src_pkg table)'); +INSERT INTO column_comments VALUES ('bug_ver','src_ver','Source package version id (matches src_ver table)'); INSERT INTO column_comments VALUES ('bug_ver','found','True if this is a found version; false if this is a fixed version'); INSERT INTO column_comments VALUES ('bug_ver','creation','Time that this entry was created'); INSERT INTO column_comments VALUES ('bug_ver','last_modified','Time that this entry was modified'); @@ -184,8 +248,9 @@ INSERT INTO column_comments VALUES ('bug_ver','last_modified','Time that this en CREATE TABLE arch ( id SERIAL PRIMARY KEY, - arch TEXT NOT NULL UNIQUE + arch TEXT NOT NULL ); +CREATE UNIQUE INDEX arch_arch_key ON arch(arch); INSERT INTO table_comments VALUES ('arch','Architectures'); INSERT INTO column_comments VALUES ('arch','id','Architecture id'); INSERT INTO column_comments VALUES ('arch','arch','Architecture name'); @@ -193,8 +258,9 @@ INSERT INTO column_comments VALUES ('arch','arch','Architecture name'); CREATE TABLE bin_pkg ( id SERIAL PRIMARY KEY, - pkg TEXT NOT NULL UNIQUE + pkg TEXT NOT NULL ); +CREATE UNIQUE INDEX bin_pkg_pkg_key ON bin_pkg(pkg); INSERT INTO table_comments VALUES ('bin_pkg','Binary packages'); INSERT INTO column_comments VALUES ('bin_pkg','id','Binary package id'); INSERT INTO column_comments VALUES ('bin_pkg','pkg','Binary package name'); @@ -202,26 +268,94 @@ INSERT INTO column_comments VALUES ('bin_pkg','pkg','Binary package name'); CREATE TABLE bin_ver( id SERIAL PRIMARY KEY, - bin_pkg_id INT NOT NULL REFERENCES bin_pkg + bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE, - src_ver_id INT NOT NULL REFERENCES src_ver + src_ver INT NOT NULL REFERENCES src_ver ON UPDATE CASCADE ON DELETE CASCADE, - arch_id INT NOT NULL REFERENCES arch + arch INT NOT NULL REFERENCES arch ON UPDATE CASCADE ON DELETE CASCADE, - ver public.debversion NOT NULL + ver debversion NOT NULL ); CREATE INDEX bin_ver_ver_idx ON bin_ver(ver); -CREATE UNIQUE INDEX bin_ver_bin_pkg_id_arch_idx ON bin_ver(bin_pkg_id,arch_id,ver); -CREATE UNIQUE INDEX bin_ver_src_ver_id_arch_idx ON bin_ver(src_ver_id,arch_id); -CREATE INDEX bin_ver_bin_pkg_id_idx ON bin_ver(bin_pkg_id); -CREATE INDEX bin_ver_src_ver_id_idx ON bin_ver(src_ver_id); +CREATE UNIQUE INDEX bin_ver_bin_pkg_id_arch_idx ON bin_ver(bin_pkg,arch,ver); +CREATE INDEX bin_ver_src_ver_id_arch_idx ON bin_ver(src_ver,arch); +CREATE INDEX bin_ver_bin_pkg_id_idx ON bin_ver(bin_pkg); +CREATE INDEX bin_ver_src_ver_id_idx ON bin_ver(src_ver); INSERT INTO table_comments VALUES ('bin_ver','Binary versions'); INSERT INTO column_comments VALUES ('bin_ver','id','Binary version id'); -INSERT INTO column_comments VALUES ('bin_ver','bin_pkg_id','Binary package id (matches bin_pkg)'); -INSERT INTO column_comments VALUES ('bin_ver','src_ver_id','Source version (matchines src_ver)'); -INSERT INTO column_comments VALUES ('bin_ver','arch_id','Architecture id (matches arch)'); +INSERT INTO column_comments VALUES ('bin_ver','bin_pkg','Binary package id (matches bin_pkg)'); +INSERT INTO column_comments VALUES ('bin_ver','src_ver','Source version (matchines src_ver)'); +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, @@ -232,64 +366,110 @@ INSERT INTO column_comments VALUES ('tag','id','Tag id'); INSERT INTO column_comments VALUES ('tag','tag','Tag name'); INSERT INTO column_comments VALUES ('tag','obsolete','Whether a tag is obsolete (should not be set on new bugs)'); - CREATE TABLE bug_tag ( - bug_id INT NOT NULL REFERENCES bug, - tag_id INT NOT NULL REFERENCES tag + bug INT NOT NULL REFERENCES bug, + tag INT NOT NULL REFERENCES tag ); INSERT INTO table_comments VALUES ('bug_tag','Bug <-> tag mapping'); -INSERT INTO column_comments VALUES ('bug_tag','bug_id','Bug id (matches bug)'); -INSERT INTO column_comments VALUES ('bug_tag','tag_id','Tag id (matches tag)'); +INSERT INTO column_comments VALUES ('bug_tag','bug','Bug id (matches bug)'); +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 UNIQUE INDEX bug_tag_bug_tag_id ON bug_tag (bug_id,tag_id); -CREATE INDEX bug_tag_tag_id ON bug_tag (tag_id); -CREATE INDEX bug_tag_bug_id ON bug_tag (bug_id); +CREATE TABLE user_tag ( + id SERIAL PRIMARY KEY, + tag TEXT NOT NULL, + correspondent INT NOT NULL REFERENCES correspondent(id) +); +INSERT INTO table_comments VALUES ('user_tag','User bug tags'); +INSERT INTO column_comments VALUES ('user_tag','id','User bug tag id'); +INSERT INTO column_comments VALUES ('user_tag','tag','User bug tag name'); +INSERT INTO column_comments VALUES ('user_tag','correspondent','User bug tag correspondent'); + +CREATE UNIQUE INDEX user_tag_tag_correspondent ON user_tag(tag,correspondent); +CREATE INDEX user_tag_correspondent ON user_tag(correspondent); + +CREATE TABLE bug_user_tag ( + bug INT NOT NULL REFERENCES bug, + user_tag INT NOT NULL REFERENCES user_tag +); +INSERT INTO table_comments VALUES ('bug_user_tag','Bug <-> user tag mapping'); +INSERT INTO column_comments VALUES ('bug_user_tag','bug','Bug id (matches bug)'); +INSERT INTO column_comments VALUES ('bug_user_tag','tag','User tag id (matches user_tag)'); + +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 TABLE bug_binpackage ( - bug_id INT NOT NULL REFERENCES bug, - bin_pkg_id INT NOT NULL REFERENCES bin_pkg + bug INT NOT NULL REFERENCES bug, + bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE ); -CREATE UNIQUE INDEX bug_binpackage_id_pkg_id ON bug_binpackage(bug_id,bin_pkg_id); +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_id','Bug id (matches bug)'); -INSERT INTO column_comments VALUES ('bug_binpackage','bin_pkg_id','Binary package id (matches bin_pkg)'); +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)'); CREATE TABLE bug_srcpackage ( - bug_id INT NOT NULL REFERENCES bug, - src_pkg_id INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE + bug INT NOT NULL REFERENCES bug, + src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE ); -CREATE UNIQUE INDEX bug_srcpackage_id_pkg_id ON bug_srcpackage(bug_id,src_pkg_id); +CREATE UNIQUE INDEX bug_srcpackage_id_pkg ON bug_srcpackage(bug,src_pkg); +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_id','Bug id (matches bug)'); -INSERT INTO column_comments VALUES ('bug_srcpackage','src_pkg_id','Source package id (matches src_pkg)'); +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 TABLE bug_affects_binpackage ( + bug INT NOT NULL REFERENCES bug, + bin_pkg INT NOT NULL REFERENCES bin_pkg ON UPDATE CASCADE ON DELETE CASCADE +); +CREATE UNIQUE INDEX bug_affects_binpackage_id_pkg ON bug_affects_binpackage(bug,bin_pkg); +INSERT INTO table_comments VALUES ('bug_affects_binpackage','Bug <-> binary package mapping'); +INSERT INTO column_comments VALUES ('bug_affects_binpackage','bug','Bug id (matches bug)'); +INSERT INTO column_comments VALUES ('bug_affects_binpackage','bin_pkg','Binary package id (matches bin_pkg)'); + +CREATE TABLE bug_affects_srcpackage ( + bug INT NOT NULL REFERENCES bug, + src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE +); +CREATE UNIQUE INDEX bug_affects_srcpackage_id_pkg ON bug_affects_srcpackage(bug,src_pkg); +INSERT INTO table_comments VALUES ('bug_affects_srcpackage','Bug <-> source package mapping'); +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_id,pkg_id,pkg_type,package) AS - SELECT b.bug_id,b.bin_pkg_id,'binary',bp.pkg FROM bug_binpackage b JOIN bin_pkg bp ON bp.id=b.bin_pkg_id UNION - SELECT s.bug_id,s.src_pkg_id,'source',sp.pkg FROM bug_srcpackage s JOIN src_pkg sp ON sp.id=s.src_pkg_id; +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 - FROM bin_ver b JOIN arch a ON b.arch_id = a.id - JOIN bin_pkg bp ON b.bin_pkg_id = bp.id - JOIN src_ver sv ON b.src_ver_id = sv.id - JOIN src_pkg sp ON sv.src_pkg_id = sp.id + FROM bin_ver b JOIN arch a ON b.arch = a.id + JOIN bin_pkg bp ON b.bin_pkg = bp.id + JOIN src_ver sv ON b.src_ver = sv.id + JOIN src_pkg sp ON sv.src_pkg = sp.id LEFT OUTER JOIN src_ver svb ON sv.based_on = svb.id - LEFT OUTER JOIN src_pkg spb ON spb.id = svb.src_pkg_id; + LEFT OUTER JOIN src_pkg spb ON spb.id = svb.src_pkg; CREATE TABLE suite ( id SERIAL PRIMARY KEY, - suite_name TEXT NOT NULL UNIQUE, + codename TEXT NOT NULL, + suite_name TEXT, version TEXT, - codename TEXT, active BOOLEAN DEFAULT TRUE); -CREATE INDEX ON suite(codename); -CREATE INDEX ON suite(version); +CREATE UNIQUE INDEX suite_idx_codename ON suite(codename); +CREATE UNIQUE INDEX suite_suite_name_key ON suite(suite_name); +CREATE UNIQUE INDEX suite_idx_version ON suite(version); INSERT INTO table_comments VALUES ('suite','Debian Release Suite (stable, testing, etc.)'); INSERT INTO column_comments VALUES ('suite','id','Suite id'); -INSERT INTO column_comments VALUES ('suite','suite_name','Suite name'); +INSERT INTO column_comments VALUES ('suite','suite_name','Suite name (testing, stable, etc.)'); INSERT INTO column_comments VALUES ('suite','version','Suite version; NULL if there is no appropriate version'); -INSERT INTO column_comments VALUES ('suite','codename','Suite codename'); +INSERT INTO column_comments VALUES ('suite','codename','Suite codename (sid, squeeze, etc.)'); INSERT INTO column_comments VALUES ('suite','active','TRUE if the suite is still accepting uploads'); CREATE TABLE bin_associations ( @@ -305,6 +485,7 @@ INSERT INTO column_comments VALUES ('bin_associations','suite','Suite id (matche INSERT INTO column_comments VALUES ('bin_associations','bin','Binary version id (matches bin_ver)'); INSERT INTO column_comments VALUES ('bin_associations','created','Time this binary package entered this suite'); INSERT INTO column_comments VALUES ('bin_associations','modified','Time this entry was modified'); +CREATE UNIQUE INDEX bin_associations_bin_suite ON bin_associations(bin,suite); CREATE TABLE src_associations ( id SERIAL PRIMARY KEY, @@ -319,34 +500,62 @@ INSERT INTO column_comments VALUES ('src_associations','suite','Suite id (matche INSERT INTO column_comments VALUES ('src_associations','source','Source version id (matches src_ver)'); INSERT INTO column_comments VALUES ('src_associations','created','Time this source package entered this suite'); INSERT INTO column_comments VALUES ('src_associations','modified','Time this entry was modified'); +CREATE UNIQUE INDEX src_associations_source_suite ON src_associations(source,suite); + + +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, + arch INT REFERENCES arch ON DELETE CASCADE ON UPDATE CASCADE, + status bug_status_type NOT NULL, + 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_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','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)'); +INSERT INTO column_comments VALUES ('bug_status_cache','status','Status (bug status)'); +INSERT INTO column_comments VALUES ('bug_status_cache','modified','Time that this status was last modified'); +INSERT INTO column_comments VALUES ('bug_status_cache','asof','Time that this status was last calculated'); + + CREATE TABLE message ( id SERIAL PRIMARY KEY, - msgid TEXT, - from_complete TEXT, - from_addr TEXT, - to_complete TEXT, - to_addr TEXT, + msgid TEXT NOT NULL DEFAULT '', + from_complete TEXT NOT NULL DEFAULT '', + to_complete TEXT NOT NULL DEFAULT '', subject TEXT NOT NULL DEFAULT '', sent_date TIMESTAMP WITH TIME ZONE, refs TEXT NOT NULL DEFAULT '', - spam_score FLOAT, - is_spam BOOLEAN DEFAULT FALSE + spam_score FLOAT NOT NULL DEFAULT 0, + is_spam BOOLEAN NOT NULL DEFAULT FALSE ); 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'); INSERT INTO column_comments VALUES ('message','spam_score','Spam score from spamassassin'); INSERT INTO column_comments VALUES ('message','is_spam','True if this message was spam and should not be shown'); - -CREATE INDEX ON message(msgid); +CREATE INDEX message_msgid_idx ON message(msgid); +CREATE UNIQUE INDEX message_msgid_from_complete_to_complete_subject_idx + ON message(msgid,from_complete,to_complete,subject); +CREATE INDEX message_subject_idx ON message(subject); CREATE TABLE message_refs ( message INT NOT NULL REFERENCES message ON DELETE CASCADE ON UPDATE CASCADE, @@ -355,9 +564,9 @@ CREATE TABLE message_refs ( primary_ref BOOLEAN DEFAULT FALSE, CONSTRAINT message_doesnt_reference_itself CHECK (message <> refs) ); -CREATE UNIQUE INDEX ON message_refs(message,refs); -CREATE INDEX ON message_refs(refs); -CREATE INDEX ON message_refs(message); +CREATE UNIQUE INDEX message_refs_message_refs_idx ON message_refs(message,refs); +CREATE INDEX message_refs_idx_refs ON message_refs(refs); +CREATE INDEX message_refs_idx_message ON message_refs(message); INSERT INTO table_comments VALUES ('message_refs','Message references'); INSERT INTO column_comments VALUES ('message_refs','message','Message id (matches message)'); INSERT INTO column_comments VALUES ('message_refs','refs','Reference id (matches message)'); @@ -365,15 +574,21 @@ INSERT INTO column_comments VALUES ('message_refs','inferred','TRUE if this mess INSERT INTO column_comments VALUES ('message_refs','primary_ref','TRUE if this message->ref came from In-Reply-To: or similar.'); -CREATE TABLE correspondent ( - id SERIAL PRIMARY KEY, - addr TEXT NOT NULL UNIQUE + +CREATE TABLE correspondent_full_name( + correspondent INT NOT NULL REFERENCES correspondent ON DELETE CASCADE ON UPDATE CASCADE, + full_name TEXT NOT NULL, + last_seen TIMESTAMP NOT NULL DEFAULT NOW() ); -INSERT INTO table_comments VALUES ('correspondent','Individual who has corresponded with the BTS'); -INSERT INTO column_comments VALUES ('correspondent','id','Correspondent ID'); -INSERT INTO column_comments VALUES ('correspondent','addr','Correspondent address'); +CREATE UNIQUE INDEX correspondent_full_name_correspondent_full_name_idx + ON correspondent_full_name(correspondent,full_name); +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','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, @@ -385,9 +600,10 @@ INSERT INTO column_comments VALUES ('message_correspondent','message','Message i INSERT INTO column_comments VALUES ('message_correspondent','correspondent','Correspondent (matches correspondent)'); INSERT INTO column_comments VALUES ('message_correspondent','correspondent_type','Type of correspondent (to, from, envfrom, cc, etc.)'); -CREATE UNIQUE INDEX ON message_correspondent(message,correspondent,correspondent_type); -CREATE INDEX ON message_correspondent(correspondent); -CREATE INDEX ON message_correspondent(message); +CREATE UNIQUE INDEX message_correspondent_message_correspondent_correspondent_t_idx + ON message_correspondent(message,correspondent,correspondent_type); +CREATE INDEX message_correspondent_idx_correspondent ON message_correspondent(correspondent); +CREATE INDEX message_correspondent_idx_message ON message_correspondent(message); CREATE TABLE bug_message ( bug INT NOT NULL REFERENCES bug ON DELETE CASCADE ON UPDATE CASCADE, @@ -396,6 +612,8 @@ CREATE TABLE bug_message ( bug_log_offset INT, offset_valid TIMESTAMP WITH TIME ZONE ); +CREATE UNIQUE INDEX bug_message_bug_message_idx ON bug_message(bug,message); +CREATE INDEX bug_message_idx_bug_message_number ON bug_message(bug,message_number); INSERT INTO table_comments VALUES ('bug_mesage','Mapping between a bug and a message'); INSERT INTO column_comments VALUES ('bug_message','bug','Bug id (matches bug)'); INSERT INTO column_comments VALUES ('bug_message','message','Message id (matches message)'); @@ -403,3 +621,58 @@ 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, + (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, + 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;