create new suite/version association indices

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index af98307b7d85959dba2131c894a941175c53a0cd..7856f69f4ba368cf65c57c9d52ec8463915af7e1 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -4,6 +4,8 @@ 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;
@@ -270,7 +272,7 @@ CREATE TABLE bin_ver(
 );
 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,ver);
-CREATE UNIQUE INDEX bin_ver_src_ver_id_arch_idx ON bin_ver(src_ver,arch);
+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');
@@ -303,7 +305,31 @@ 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,
@@ -364,18 +390,18 @@ CREATE VIEW binary_versions (src_pkg, src_ver, bin_pkg, arch, bin_ver) AS
 
 CREATE TABLE suite (
        id SERIAL PRIMARY KEY,
-       suite_name TEXT NOT NULL,
+       codename TEXT NOT NULL,
+       suite_name TEXT,
        version TEXT,
-       codename TEXT,
        active BOOLEAN DEFAULT TRUE);
+CREATE UNIQUE INDEX suite_idx_codename ON suite(codename);
 CREATE UNIQUE INDEX suite_suite_name_key ON suite(suite_name);
-CREATE INDEX suite_idx_codename ON suite(codename);
-CREATE INDEX suite_idx_version ON suite(version);
+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 (
@@ -391,6 +417,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,
@@ -405,7 +432,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');
@@ -423,14 +450,14 @@ 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','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');
+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');