X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=sql%2Fdebbugs_schema.sql;h=c3e313ca532ced6adc1d42fe3630647a5d6ee747;hb=refs%2Fheads%2Fbugwalker_abstraction;hp=71a470d105b772e72aed1aa0c9c28bcd957a3a7d;hpb=9286c3a0d38dc797564368a32c9ba8d2c2afe7e4;p=debbugs.git diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index 71a470d..c3e313c 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -47,19 +47,22 @@ CREATE UNIQUE INDEX ON column_comments(table_name,column_name); CREATE TABLE correspondent ( id SERIAL PRIMARY KEY, - addr TEXT NOT NULL UNIQUE + addr TEXT NOT NULL ); +CREATE UNIQUE INDEX 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 ON maintainer(name); +CREATE INDEX 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'); @@ -70,11 +73,13 @@ INSERT INTO column_comments VALUES ('maintainer','modified','Time maintainer rec CREATE TABLE severity ( id SERIAL PRIMARY KEY, - severity TEXT NOT NULL UNIQUE, + severity TEXT NOT NULL, ordering INT NOT NULL DEFAULT 5, strong BOOLEAN DEFAULT FALSE, obsolete BOOLEAN DEFAULT FALSE ); +CREATE UNIQUE INDEX ON severity(severity); +CREATE INDEX 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'); @@ -104,6 +109,12 @@ CREATE TABLE bug ( submitter_full TEXT NOT NULL DEFAULT '', unknown_packages TEXT NOT NULL DEfAULT '' ); +CREATE INDEX ON bug(owner); +CREATE INDEX ON bug(submitter); +CREATE INDEX ON bug(done); +CREATE INDEX ON bug(forwarded); +CREATE INDEX ON bug(last_modified); +CREATE INDEX ON bug(severity); 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'); @@ -165,9 +176,9 @@ CREATE TABLE src_pkg ( disabled TIMESTAMP WITH TIME ZONE DEFAULT NULL, last_modified TIMESTAMP WITH TIME ZONE DEFAULT NOW(), obsolete BOOLEAN DEFAULT FALSE, - CONSTRAINT src_pkg_doesnt_alias_itself CHECK (id <> alias_of) + CONSTRAINT src_pkg_doesnt_alias_itself CHECK (id <> alias_of), + CONSTRAINT src_pkg_is_obsolete_if_disabled CHECK ((obsolete IS FALSE AND disabled IS NULL) OR (obsolete IS TRUE AND disabled IS NOT NULL)) ); -CREATE UNIQUE INDEX src_pkg_pkg_alias ON src_pkg(pkg,alias_of,obsolete); CREATE INDEX src_pkg_pkg ON src_pkg(pkg); CREATE UNIQUE INDEX src_pkg_pkg_disabled ON src_pkg(pkg,disabled); INSERT INTO table_comments VALUES ('src_pkg','Source packages'); @@ -183,7 +194,7 @@ CREATE TABLE src_ver ( src_pkg INT NOT NULL REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE, ver public.debversion NOT NULL, - maintainer_id INT REFERENCES maintainer + 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 @@ -194,33 +205,35 @@ 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','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 ( + id SERIAL PRIMARY KEY, bug INT NOT NULL REFERENCES bug ON UPDATE CASCADE ON DELETE RESTRICT, ver_string TEXT, 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); -CREATE INDEX bug_ver_src_pkg_id_src_ver_id_idx ON bug_ver(src_pkg,src_ver_id); -CREATE INDEX bug_ver_src_ver_id_idx ON bug_ver(src_ver_id); +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 ON bug_ver(bug,ver_string,found); INSERT INTO table_comments VALUES ('bug_ver','Bug versions'); +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','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_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'); @@ -248,22 +261,22 @@ CREATE TABLE bin_ver( id SERIAL PRIMARY KEY, 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 ); 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,arch_id,ver); -CREATE UNIQUE INDEX bin_ver_src_ver_id_arch_idx ON bin_ver(src_ver_id,arch_id); +CREATE UNIQUE INDEX bin_ver_src_ver_id_arch_idx ON bin_ver(src_ver_id,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_id); +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','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','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 tag ( @@ -285,18 +298,18 @@ INSERT INTO table_comments VALUES ('bug_tag','Bug <-> tag mapping'); 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_id ON bug_tag (bug,tag); -CREATE INDEX bug_tag_tag_id ON bug_tag (tag); -CREATE INDEX bug_tag_bug_id ON bug_tag (bug); +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 bug_binpackage ( id SERIAL PRIMARY KEY, bug INT NOT NULL REFERENCES bug, - bin_pkg INT NOT NULL REFERENCES bin_pkg + 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,bin_pkg); +CREATE UNIQUE INDEX bug_binpackage_id_pkg ON bug_binpackage(bug,bin_pkg); 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)'); @@ -306,7 +319,7 @@ CREATE TABLE bug_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_srcpackage_id_pkg_id ON bug_srcpackage(bug,src_pkg); +CREATE UNIQUE INDEX bug_srcpackage_id_pkg ON bug_srcpackage(bug,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)'); @@ -315,12 +328,32 @@ 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 ( + id SERIAL PRIMARY KEY, + 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 ( + id SERIAL PRIMARY KEY, + 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 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 + 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_id = sv.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; @@ -419,7 +452,6 @@ INSERT INTO column_comments VALUES ('message','sent_date','Time/date message was 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 TABLE message_refs ( @@ -444,10 +476,12 @@ 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 + full_name TEXT NOT NULL, + last_seen TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE UNIQUE INDEX ON correspondent_full_name(correspondent,full_name); - +CREATE INDEX ON correspondent_full_name(full_name); +CREATE INDEX 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)'); @@ -478,6 +512,8 @@ CREATE TABLE bug_message ( bug_log_offset INT, offset_valid TIMESTAMP WITH TIME ZONE ); +CREATE UNIQUE INDEX ON bug_message(bug,message); +CREATE INDEX 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)');