use infinity::timestamp for non-disabled packages

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index 02529ed2057b1dd756a9ab66b6eb10daaacda964..fc207c44a6128c4333801521218391a869fe9da2 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -171,16 +171,17 @@ 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');
@@ -272,7 +273,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');
@@ -390,18 +391,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 (
@@ -417,6 +418,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,
@@ -431,7 +433,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');