1 DROP DATABASE projectb;
2 CREATE DATABASE projectb WITH ENCODING = 'SQL_ASCII';
8 name TEXT UNIQUE NOT NULL,
13 CREATE TABLE component (
14 id SERIAL PRIMARY KEY,
15 name TEXT UNIQUE NOT NULL,
20 CREATE TABLE architecture (
21 id SERIAL PRIMARY KEY,
22 arch_string TEXT UNIQUE NOT NULL,
26 CREATE TABLE maintainer (
27 id SERIAL PRIMARY KEY,
28 name TEXT UNIQUE NOT NULL
31 CREATE TABLE src_uploaders (
32 id SERIAL PRIMARY KEY,
33 source INT4 NOT NULL REFERENCES source,
34 maintainer INT4 NOT NULL REFERENCES maintainer
38 id SERIAL PRIMARY KEY,
39 uid TEXT UNIQUE NOT NULL,
43 CREATE TABLE keyrings (
44 id SERIAL PRIMARY KEY,
49 CREATE TABLE fingerprint (
50 id SERIAL PRIMARY KEY,
51 fingerprint TEXT UNIQUE NOT NULL,
52 uid INT4 REFERENCES uid,
53 keyring INT4 REFERENCES keyrings
56 CREATE TABLE location (
57 id SERIAL PRIMARY KEY,
59 component INT4 REFERENCES component,
60 archive INT4 REFERENCES archive,
64 -- No references below here to allow sane population; added post-population
67 id SERIAL PRIMARY KEY,
68 filename TEXT NOT NULL,
71 location INT4 NOT NULL, -- REFERENCES location
73 unique (filename, location)
77 id SERIAL PRIMARY KEY,
79 version TEXT NOT NULL,
80 maintainer INT4 NOT NULL, -- REFERENCES maintainer
81 changedby INT4 NOT NULL, -- REFERENCES maintainer
82 file INT4 UNIQUE NOT NULL, -- REFERENCES files
83 install_date TIMESTAMP NOT NULL,
84 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
85 unique (source, version)
88 CREATE TABLE src_uploaders (
89 id SERIAL PRIMARY KEY,
90 source INT4 NOT NULL REFERENCES source,
91 maintainer INT4 NOT NULL REFERENCES maintainer
94 CREATE TABLE dsc_files (
95 id SERIAL PRIMARY KEY,
96 source INT4 NOT NULL, -- REFERENCES source,
97 file INT4 NOT NULL, -- RERENCES files
101 CREATE TABLE binaries (
102 id SERIAL PRIMARY KEY,
103 package TEXT NOT NULL,
104 version TEXT NOT NULL,
105 maintainer INT4 NOT NULL, -- REFERENCES maintainer
106 source INT4, -- REFERENCES source,
107 architecture INT4 NOT NULL, -- REFERENCES architecture
108 file INT4 UNIQUE NOT NULL, -- REFERENCES files,
110 -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
111 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
112 unique (package, version, architecture)
116 id SERIAL PRIMARY KEY,
117 suite_name TEXT NOT NULL,
126 id SERIAL PRIMARY KEY,
127 queue_name TEXT NOT NULL
130 CREATE TABLE suite_architectures (
131 suite INT4 NOT NULL, -- REFERENCES suite
132 architecture INT4 NOT NULL, -- REFERENCES architecture
133 unique (suite, architecture)
136 CREATE TABLE bin_associations (
137 id SERIAL PRIMARY KEY,
138 suite INT4 NOT NULL, -- REFERENCES suite
139 bin INT4 NOT NULL, -- REFERENCES binaries
143 CREATE TABLE src_associations (
144 id SERIAL PRIMARY KEY,
145 suite INT4 NOT NULL, -- REFERENCES suite
146 source INT4 NOT NULL, -- REFERENCES source
147 unique (suite, source)
150 CREATE TABLE section (
151 id SERIAL PRIMARY KEY,
152 section TEXT UNIQUE NOT NULL
155 CREATE TABLE priority (
156 id SERIAL PRIMARY KEY,
157 priority TEXT UNIQUE NOT NULL,
158 level INT4 UNIQUE NOT NULL
161 CREATE TABLE override_type (
162 id SERIAL PRIMARY KEY,
163 type TEXT UNIQUE NOT NULL
166 CREATE TABLE override (
167 package TEXT NOT NULL,
168 suite INT4 NOT NULL, -- references suite
169 component INT4 NOT NULL, -- references component
170 priority INT4, -- references priority
171 section INT4 NOT NULL, -- references section
172 type INT4 NOT NULL, -- references override_type
174 unique (suite, component, package, type)
177 CREATE TABLE queue_build (
178 suite INT4 NOT NULL, -- references suite
179 queue INT4 NOT NULL, -- references queue
180 filename TEXT NOT NULL,
181 in_queue BOOLEAN NOT NULL,
187 CREATE INDEX bin_associations_bin ON bin_associations (bin);
188 CREATE INDEX src_associations_source ON src_associations (source);
189 CREATE INDEX source_maintainer ON source (maintainer);
190 CREATE INDEX binaries_maintainer ON binaries (maintainer);
191 CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
192 CREATE INDEX source_fingerprint on source (sig_fpr);
193 CREATE INDEX dsc_files_file ON dsc_files (file);
196 CREATE FUNCTION space_concat(text, text) RETURNS text
198 WHEN $2 is null or $2 = '' THEN $1
199 WHEN $1 is null or $1 = '' THEN $2
204 CREATE AGGREGATE space_separated_list (
206 SFUNC = space_concat,