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');
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');
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');
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');
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
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');
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 (
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)');
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)');
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;
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 (
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)');
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)');
projects / debbugs.git / blobdiff