remove pkg/alias_of/obsolete index; add obsolete/disabled time constraint

[debbugs.git] / sql / debbugs_schema.sql

diff --git a/sql/debbugs_schema.sql b/sql/debbugs_schema.sql
index d2ecfa25f99d27bf7d021a3ed9063f8e2bb4f61e..151d434110e56e99027f937c96b843891ed216b3 100644 (file)
--- a/sql/debbugs_schema.sql
+++ b/sql/debbugs_schema.sql
@@ -44,25 +44,29 @@ CREATE TABLE column_comments (
 );
 CREATE UNIQUE INDEX ON column_comments(table_name,column_name);
 
+
+CREATE TABLE correspondent (
+       id SERIAL PRIMARY KEY,
+       addr TEXT NOT NULL UNIQUE
+);
+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,
+       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
 );
 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');
+INSERT INTO column_comments VALUES ('maintainer','correspondent','Correspondent ID');
 INSERT INTO column_comments VALUES ('maintainer','created','Time maintainer record created');
 INSERT INTO column_comments VALUES ('maintainer','modified','Time maintainer record modified');
 
-CREATE TABLE correspondent (
-       id SERIAL PRIMARY KEY,
-       addr TEXT NOT NULL UNIQUE
-);
-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 severity (
        id SERIAL PRIMARY KEY,
@@ -154,11 +158,18 @@ 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 UNIQUE,
+       pkg TEXT NOT NULL,
        pseduopkg BOOLEAN DEFAULT FALSE,
-       alias_of INT REFERENCES src_pkg ON UPDATE CASCADE ON DELETE CASCADE
-       CONSTRAINT src_pkg_doesnt_alias_itself CHECK (id <> alias_of)
+       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,
+       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 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');
 INSERT INTO column_comments VALUES ('src_pkg','id','Source package id');
 INSERT INTO column_comments VALUES ('src_pkg','pkg','Source package name');