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,
41 debian_maintainer BOOLEAN NOT NULL,
44 CREATE TABLE keyrings (
45 id SERIAL PRIMARY KEY,
50 CREATE TABLE fingerprint (
51 id SERIAL PRIMARY KEY,
52 fingerprint TEXT UNIQUE NOT NULL,
53 uid INT4 REFERENCES uid,
54 keyring INT4 REFERENCES keyrings
57 CREATE TABLE location (
58 id SERIAL PRIMARY KEY,
60 component INT4 REFERENCES component,
61 archive INT4 REFERENCES archive,
65 -- No references below here to allow sane population; added post-population
68 id SERIAL PRIMARY KEY,
69 filename TEXT NOT NULL,
72 location INT4 NOT NULL, -- REFERENCES location
74 sha1sum TEXT NOT NULL,
75 sha256sum TEXT NOT NULL,
76 unique (filename, location)
80 id SERIAL PRIMARY KEY,
82 version TEXT NOT NULL,
83 maintainer INT4 NOT NULL, -- REFERENCES maintainer
84 changedby INT4 NOT NULL, -- REFERENCES maintainer
85 file INT4 UNIQUE NOT NULL, -- REFERENCES files
86 install_date TIMESTAMP NOT NULL,
87 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
88 dm-upload-allowed BOOLEAN NOT NULL,
89 unique (source, version)
92 CREATE TABLE src_uploaders (
93 id SERIAL PRIMARY KEY,
94 source INT4 NOT NULL REFERENCES source,
95 maintainer INT4 NOT NULL REFERENCES maintainer
98 CREATE TABLE dsc_files (
99 id SERIAL PRIMARY KEY,
100 source INT4 NOT NULL, -- REFERENCES source,
101 file INT4 NOT NULL, -- RERENCES files
102 unique (source, file)
105 CREATE TABLE binaries (
106 id SERIAL PRIMARY KEY,
107 package TEXT NOT NULL,
108 version TEXT NOT NULL,
109 maintainer INT4 NOT NULL, -- REFERENCES maintainer
110 source INT4, -- REFERENCES source,
111 architecture INT4 NOT NULL, -- REFERENCES architecture
112 file INT4 UNIQUE NOT NULL, -- REFERENCES files,
114 -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
115 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
116 unique (package, version, architecture)
120 id SERIAL PRIMARY KEY,
121 suite_name TEXT NOT NULL,
130 id SERIAL PRIMARY KEY,
131 queue_name TEXT NOT NULL
134 CREATE TABLE suite_architectures (
135 suite INT4 NOT NULL, -- REFERENCES suite
136 architecture INT4 NOT NULL, -- REFERENCES architecture
137 unique (suite, architecture)
140 CREATE TABLE bin_associations (
141 id SERIAL PRIMARY KEY,
142 suite INT4 NOT NULL, -- REFERENCES suite
143 bin INT4 NOT NULL, -- REFERENCES binaries
147 CREATE TABLE src_associations (
148 id SERIAL PRIMARY KEY,
149 suite INT4 NOT NULL, -- REFERENCES suite
150 source INT4 NOT NULL, -- REFERENCES source
151 unique (suite, source)
154 CREATE TABLE section (
155 id SERIAL PRIMARY KEY,
156 section TEXT UNIQUE NOT NULL
159 CREATE TABLE priority (
160 id SERIAL PRIMARY KEY,
161 priority TEXT UNIQUE NOT NULL,
162 level INT4 UNIQUE NOT NULL
165 CREATE TABLE override_type (
166 id SERIAL PRIMARY KEY,
167 type TEXT UNIQUE NOT NULL
170 CREATE TABLE override (
171 package TEXT NOT NULL,
172 suite INT4 NOT NULL, -- references suite
173 component INT4 NOT NULL, -- references component
174 priority INT4, -- references priority
175 section INT4 NOT NULL, -- references section
176 type INT4 NOT NULL, -- references override_type
178 unique (suite, component, package, type)
181 CREATE TABLE queue_build (
182 suite INT4 NOT NULL, -- references suite
183 queue INT4 NOT NULL, -- references queue
184 filename TEXT NOT NULL,
185 in_queue BOOLEAN NOT NULL,
191 CREATE INDEX bin_associations_bin ON bin_associations (bin);
192 CREATE INDEX src_associations_source ON src_associations (source);
193 CREATE INDEX source_maintainer ON source (maintainer);
194 CREATE INDEX binaries_maintainer ON binaries (maintainer);
195 CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
196 CREATE INDEX source_fingerprint on source (sig_fpr);
197 CREATE INDEX dsc_files_file ON dsc_files (file);
200 CREATE FUNCTION space_concat(text, text) RETURNS text
202 WHEN $2 is null or $2 = '' THEN $1
203 WHEN $1 is null or $1 = '' THEN $2
208 CREATE AGGREGATE space_separated_list (
210 SFUNC = space_concat,