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
32 id SERIAL PRIMARY KEY,
33 uid TEXT UNIQUE NOT NULL,
37 CREATE TABLE keyrings (
38 id SERIAL PRIMARY KEY,
43 CREATE TABLE fingerprint (
44 id SERIAL PRIMARY KEY,
45 fingerprint TEXT UNIQUE NOT NULL,
46 uid INT4 REFERENCES uid,
47 keyring INT4 REFERENCES keyrings
50 CREATE TABLE location (
51 id SERIAL PRIMARY KEY,
53 component INT4 REFERENCES component,
54 archive INT4 REFERENCES archive,
58 -- No references below here to allow sane population; added post-population
61 id SERIAL PRIMARY KEY,
62 filename TEXT NOT NULL,
65 location INT4 NOT NULL, -- REFERENCES location
67 unique (filename, location)
71 id SERIAL PRIMARY KEY,
73 version TEXT NOT NULL,
74 maintainer INT4 NOT NULL, -- REFERENCES maintainer
75 changedby INT4 NOT NULL, -- REFERENCES maintainer
76 file INT4 UNIQUE NOT NULL, -- REFERENCES files
77 install_date TIMESTAMP NOT NULL,
78 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
79 unique (source, version)
82 CREATE TABLE src_uploaders (
83 id SERIAL PRIMARY KEY,
84 source INT4 NOT NULL REFERENCES source,
85 maintainer INT4 NOT NULL REFERENCES maintainer
88 CREATE TABLE dsc_files (
89 id SERIAL PRIMARY KEY,
90 source INT4 NOT NULL, -- REFERENCES source,
91 file INT4 NOT NULL, -- RERENCES files
95 CREATE TABLE binaries (
96 id SERIAL PRIMARY KEY,
97 package TEXT NOT NULL,
98 version TEXT NOT NULL,
99 maintainer INT4 NOT NULL, -- REFERENCES maintainer
100 source INT4, -- REFERENCES source,
101 architecture INT4 NOT NULL, -- REFERENCES architecture
102 file INT4 UNIQUE NOT NULL, -- REFERENCES files,
104 -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
105 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
106 unique (package, version, architecture)
110 id SERIAL PRIMARY KEY,
111 suite_name TEXT NOT NULL,
120 id SERIAL PRIMARY KEY,
121 queue_name TEXT NOT NULL
124 CREATE TABLE suite_architectures (
125 suite INT4 NOT NULL, -- REFERENCES suite
126 architecture INT4 NOT NULL, -- REFERENCES architecture
127 unique (suite, architecture)
130 CREATE TABLE bin_associations (
131 id SERIAL PRIMARY KEY,
132 suite INT4 NOT NULL, -- REFERENCES suite
133 bin INT4 NOT NULL, -- REFERENCES binaries
137 CREATE TABLE src_associations (
138 id SERIAL PRIMARY KEY,
139 suite INT4 NOT NULL, -- REFERENCES suite
140 source INT4 NOT NULL, -- REFERENCES source
141 unique (suite, source)
144 CREATE TABLE section (
145 id SERIAL PRIMARY KEY,
146 section TEXT UNIQUE NOT NULL
149 CREATE TABLE priority (
150 id SERIAL PRIMARY KEY,
151 priority TEXT UNIQUE NOT NULL,
152 level INT4 UNIQUE NOT NULL
155 CREATE TABLE override_type (
156 id SERIAL PRIMARY KEY,
157 type TEXT UNIQUE NOT NULL
160 CREATE TABLE override (
161 package TEXT NOT NULL,
162 suite INT4 NOT NULL, -- references suite
163 component INT4 NOT NULL, -- references component
164 priority INT4, -- references priority
165 section INT4 NOT NULL, -- references section
166 type INT4 NOT NULL, -- references override_type
168 unique (suite, component, package, type)
171 CREATE TABLE queue_build (
172 suite INT4 NOT NULL, -- references suite
173 queue INT4 NOT NULL, -- references queue
174 filename TEXT NOT NULL,
175 in_queue BOOLEAN NOT NULL,
181 CREATE INDEX bin_associations_bin ON bin_associations (bin);
182 CREATE INDEX src_associations_source ON src_associations (source);
183 CREATE INDEX source_maintainer ON source (maintainer);
184 CREATE INDEX binaries_maintainer ON binaries (maintainer);
185 CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
186 CREATE INDEX source_fingerprint on source (sig_fpr);
187 CREATE INDEX dsc_files_file ON dsc_files (file);
190 CREATE FUNCTION space_concat(text, text) RETURNS text
192 WHEN $2 is null or $2 = '' THEN $1
193 WHEN $1 is null or $1 = '' THEN $2
198 CREATE AGGREGATE space_separated_list (
200 SFUNC = space_concat,