X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;ds=sidebyside;f=sql%2Fdebbugs_schema.sql;h=2556bac6ae181ed64bf12f833968058dc18a6a40;hb=0d8767b1126aa1dc30cfd8599ecc8fa52c2f2cc8;hp=151d434110e56e99027f937c96b843891ed216b3;hpb=44c0f6eeca5d4204a0f27f383be1731968b6deec;p=debbugs.git diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql index 151d434..2556bac 100644 --- a/sql/debbugs_schema.sql +++ b/sql/debbugs_schema.sql @@ -1,9 +1,11 @@ DROP TABLE bug_status_cache CASCADE; -DROP VIEW bug_package CASCADE; +DROP VIEW bug_package CASCADE; DROP VIEW binary_versions 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 TABLE src_pkg CASCADE; @@ -16,6 +18,8 @@ DROP TABLE bug_blocks CASCADE; DROP TABLE bug_merged CASCADE; DROP TABLE bug_srcpackage CASCADE; DROP TABLE bug_binpackage 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; @@ -34,32 +38,36 @@ 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); CREATE TABLE correspondent ( id SERIAL PRIMARY KEY, - addr TEXT NOT NULL UNIQUE + 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'); @@ -70,11 +78,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 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'); @@ -104,6 +114,15 @@ CREATE TABLE bug ( submitter_full TEXT NOT NULL DEFAULT '', unknown_packages 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'); @@ -120,13 +139,6 @@ INSERT INTO column_comments VALUES ('bug','owner','Individual who owns this bug; 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'); -CREATE INDEX ON bug(creation); -CREATE INDEX ON bug(log_modified); -CREATE INDEX ON bug(done); -CREATE INDEX ON bug(owner); -CREATE INDEX ON bug(submitter); -CREATE INDEX ON bug(forwarded); - CREATE TABLE bug_blocks ( @@ -159,16 +171,19 @@ INSERT INTO column_comments VALUES ('bug_merged','merged','Bug number which is m CREATE TABLE src_pkg ( id SERIAL PRIMARY KEY, pkg TEXT NOT NULL, - pseduopkg BOOLEAN DEFAULT FALSE, + pseduopkg BOOLEAN NOT NULL DEFAULT FALSE, alias_of INT REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE, - creation TIMESTAMP WITH TIME ZONE DEFAULT NOW(), - disabled TIMESTAMP WITH TIME ZONE DEFAULT NULL, - last_modified TIMESTAMP WITH TIME ZONE DEFAULT NOW(), - obsolete BOOLEAN DEFAULT FALSE, + 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 IS NULL) OR (obsolete IS TRUE AND disabled IS NOT NULL)) + 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'); @@ -182,8 +197,8 @@ CREATE TABLE src_ver ( id SERIAL PRIMARY KEY, 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 @@ -194,33 +209,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 UNIQUE INDEX ON bug_ver(bug,ver_string,found); +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','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'); @@ -228,8 +245,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'); @@ -237,8 +255,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'); @@ -248,22 +267,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 + 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,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_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_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 +304,42 @@ 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 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 ( + id SERIAL PRIMARY KEY, + 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 INDEX bug_user_tag_bug ON bug_user_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 +349,10 @@ 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); +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)'); @@ -315,29 +361,50 @@ 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; 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 ( @@ -353,6 +420,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, @@ -367,7 +435,7 @@ 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 ('pending','forwarded','pending-fixed','fixed','absent','done'); @@ -380,32 +448,34 @@ 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 ON bug_status_cache(bug,suite,arch); -CREATE INDEX ON bug_status_cache(bug); -CREATE INDEX ON bug_status_cache(status); -INSERT INTO table_comments VALUES ('bug_status_cache','Source <-> suite associations'); -INSERT INTO column_comments VALUES ('bug_status_cache','id','Source <-> suite association id'); -INSERT INTO column_comments VALUES ('bug_status_cache','bug','Source <-> suite association id'); -INSERT INTO column_comments VALUES ('bug_status_cache','suite','Source <-> suite association id'); -INSERT INTO column_comments VALUES ('bug_status_cache','arch','Source <-> suite association id'); -INSERT INTO column_comments VALUES ('bug_status_cache','status','Source <-> suite association id'); -INSERT INTO column_comments VALUES ('bug_status_cache','modified','Source <-> suite association id'); -INSERT INTO column_comments VALUES ('bug_status_cache','asof','Source <-> suite association id'); +CREATE UNIQUE INDEX bug_status_cache_bug_suite_arch_idx ON bug_status_cache(bug,suite,arch); +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); +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)'); +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 '', + 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 '', - 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'); @@ -419,8 +489,10 @@ 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 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 ( id SERIAL PRIMARY KEY, @@ -430,9 +502,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)'); @@ -444,10 +516,13 @@ 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 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','id','Correspondent full name id'); INSERT INTO column_comments VALUES ('correspondent_full_name','correpsondent','Correspondent ID (matches correspondent)'); @@ -466,9 +541,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 ( id SERIAL PRIMARY KEY, @@ -478,6 +554,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)');