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 file INT4 UNIQUE NOT NULL, -- REFERENCES files
76 install_date TIMESTAMP NOT NULL,
77 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
78 unique (source, version)
81 CREATE TABLE dsc_files (
82 id SERIAL PRIMARY KEY,
83 source INT4 NOT NULL, -- REFERENCES source,
84 file INT4 NOT NULL, -- RERENCES files
88 CREATE TABLE binaries (
89 id SERIAL PRIMARY KEY,
90 package TEXT NOT NULL,
91 version TEXT NOT NULL,
92 maintainer INT4 NOT NULL, -- REFERENCES maintainer
93 source INT4, -- REFERENCES source,
94 architecture INT4 NOT NULL, -- REFERENCES architecture
95 file INT4 UNIQUE NOT NULL, -- REFERENCES files,
97 -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
98 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
99 unique (package, version, architecture)
103 id SERIAL PRIMARY KEY,
104 suite_name TEXT NOT NULL,
113 id SERIAL PRIMARY KEY,
114 queue_name TEXT NOT NULL
117 CREATE TABLE suite_architectures (
118 suite INT4 NOT NULL, -- REFERENCES suite
119 architecture INT4 NOT NULL, -- REFERENCES architecture
120 unique (suite, architecture)
123 CREATE TABLE bin_associations (
124 id SERIAL PRIMARY KEY,
125 suite INT4 NOT NULL, -- REFERENCES suite
126 bin INT4 NOT NULL, -- REFERENCES binaries
130 CREATE TABLE src_associations (
131 id SERIAL PRIMARY KEY,
132 suite INT4 NOT NULL, -- REFERENCES suite
133 source INT4 NOT NULL, -- REFERENCES source
134 unique (suite, source)
137 CREATE TABLE section (
138 id SERIAL PRIMARY KEY,
139 section TEXT UNIQUE NOT NULL
142 CREATE TABLE priority (
143 id SERIAL PRIMARY KEY,
144 priority TEXT UNIQUE NOT NULL,
145 level INT4 UNIQUE NOT NULL
148 CREATE TABLE override_type (
149 id SERIAL PRIMARY KEY,
150 type TEXT UNIQUE NOT NULL
153 CREATE TABLE override (
154 package TEXT NOT NULL,
155 suite INT4 NOT NULL, -- references suite
156 component INT4 NOT NULL, -- references component
157 priority INT4, -- references priority
158 section INT4 NOT NULL, -- references section
159 type INT4 NOT NULL, -- references override_type
161 unique (suite, component, package, type)
164 CREATE TABLE queue_build (
165 suite INT4 NOT NULL, -- references suite
166 queue INT4 NOT NULL, -- references queue
167 filename TEXT NOT NULL,
168 in_queue BOOLEAN NOT NULL,
174 CREATE INDEX bin_associations_bin ON bin_associations (bin);
175 CREATE INDEX src_associations_source ON src_associations (source);
176 CREATE INDEX source_maintainer ON source (maintainer);
177 CREATE INDEX binaries_maintainer ON binaries (maintainer);
178 CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
179 CREATE INDEX source_fingerprint on source (sig_fpr);
180 CREATE INDEX dsc_files_file ON dsc_files (file);