);
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,
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
+ 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)
);
+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');
INSERT INTO column_comments VALUES ('src_pkg','id','Source package id');
INSERT INTO column_comments VALUES ('src_pkg','pkg','Source package name');