DROP DATABASE projectb;
CREATE DATABASE projectb WITH ENCODING = 'SQL_ASCII';

\c projectb

CREATE TABLE archive (
       id SERIAL PRIMARY KEY,
       name TEXT UNIQUE NOT NULL,
       origin_server TEXT,
       description TEXT
);

CREATE TABLE component (
       id SERIAL PRIMARY KEY,
       name TEXT UNIQUE NOT NULL,
       description TEXT,
       meets_dfsg BOOLEAN
);

CREATE TABLE architecture (
       id SERIAL PRIMARY KEY,
       arch_string TEXT UNIQUE NOT NULL,
       description TEXT
);

CREATE TABLE maintainer (
       id SERIAL PRIMARY KEY,
       name TEXT UNIQUE NOT NULL
);

CREATE TABLE uid (
       id SERIAL PRIMARY KEY,
       uid TEXT UNIQUE NOT NULL
);

CREATE TABLE fingerprint (
       id SERIAL PRIMARY KEY,
       fingerprint TEXT UNIQUE NOT NULL,
       uid INT4 REFERENCES uid
);

CREATE TABLE location (
       id SERIAL PRIMARY KEY,
       path TEXT NOT NULL,
       component INT4 REFERENCES component,
       archive INT4 REFERENCES archive,
       type TEXT NOT NULL
);

-- No references below here to allow sane population; added post-population

CREATE TABLE files (
       id SERIAL PRIMARY KEY,
       filename TEXT NOT NULL,
       size INT8 NOT NULL,
       md5sum TEXT NOT NULL,
       location INT4 NOT NULL, -- REFERENCES location
       last_used TIMESTAMP,
       unique (filename, location)
);

CREATE TABLE source (
        id SERIAL PRIMARY KEY,
        source TEXT NOT NULL,
        version TEXT NOT NULL,
        maintainer INT4 NOT NULL, -- REFERENCES maintainer
        file INT4 UNIQUE NOT NULL, -- REFERENCES files
	install_date TIMESTAMP NOT NULL,
	sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
	unique (source, version)
);

CREATE TABLE dsc_files (
       id SERIAL PRIMARY KEY,
       source INT4 NOT NULL, -- REFERENCES source,
       file INT4 NOT NULL, -- RERENCES files
       unique (source, file)
);

CREATE TABLE binaries (
       id SERIAL PRIMARY KEY,
       package TEXT NOT NULL,
       version TEXT NOT NULL,
       maintainer INT4 NOT NULL, -- REFERENCES maintainer
       source INT4, -- REFERENCES source,
       architecture INT4 NOT NULL, -- REFERENCES architecture
       file INT4 UNIQUE NOT NULL, -- REFERENCES files,
       type TEXT NOT NULL,
-- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
       sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
       unique (package, version, architecture)
);

CREATE TABLE suite (
       id SERIAL PRIMARY KEY,
       suite_name TEXT NOT NULL,
       version TEXT,
       origin TEXT,
       label TEXT,
       policy_engine TEXT,
       description TEXT
);

CREATE TABLE queue (
       id SERIAL PRIMARY KEY,
       queue_name TEXT NOT NULL
);

CREATE TABLE suite_architectures (
       suite INT4 NOT NULL, -- REFERENCES suite
       architecture INT4 NOT NULL, -- REFERENCES architecture
       unique (suite, architecture)
);

CREATE TABLE bin_associations (
       id SERIAL PRIMARY KEY,
       suite INT4 NOT NULL, -- REFERENCES suite
       bin INT4 NOT NULL, -- REFERENCES binaries
       unique (suite, bin)
);

CREATE TABLE src_associations (
       id SERIAL PRIMARY KEY,
       suite INT4 NOT NULL, -- REFERENCES suite
       source INT4 NOT NULL, -- REFERENCES source
       unique (suite, source)
);

CREATE TABLE section (
       id SERIAL PRIMARY KEY,
       section TEXT UNIQUE NOT NULL
);

CREATE TABLE priority (
       id SERIAL PRIMARY KEY,
       priority TEXT UNIQUE NOT NULL,
       level INT4 UNIQUE NOT NULL
);

CREATE TABLE override_type (
       id SERIAL PRIMARY KEY,
       type TEXT UNIQUE NOT NULL
);

CREATE TABLE override (
       package TEXT NOT NULL,
       suite INT4 NOT NULL, -- references suite
       component INT4 NOT NULL, -- references component
       priority INT4, -- references priority
       section INT4 NOT NULL, -- references section
       type INT4 NOT NULL, -- references override_type
       maintainer TEXT,
       unique (suite, component, package, type)
);

CREATE TABLE queue_build (
       suite INT4 NOT NULL, -- references suite
       queue INT4 NOT NULL, -- references queue
       filename TEXT NOT NULL,
       in_queue BOOLEAN NOT NULL,
       last_used TIMESTAMP
);

-- Critical indexes

CREATE INDEX bin_associations_bin ON bin_associations (bin);
CREATE INDEX src_associations_source ON src_associations (source);
CREATE INDEX source_maintainer ON source (maintainer);
CREATE INDEX binaries_maintainer ON binaries (maintainer);
CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
CREATE INDEX source_fingerprint on source (sig_fpr);
CREATE INDEX dsc_files_file ON dsc_files (file);