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 fingerprint (
38 id SERIAL PRIMARY KEY,
39 fingerprint TEXT UNIQUE NOT NULL,
40 uid INT4 REFERENCES uid
43 CREATE TABLE location (
44 id SERIAL PRIMARY KEY,
46 component INT4 REFERENCES component,
47 archive INT4 REFERENCES archive,
51 -- No references below here to allow sane population; added post-population
54 id SERIAL PRIMARY KEY,
55 filename TEXT NOT NULL,
58 location INT4 NOT NULL, -- REFERENCES location
60 unique (filename, location)
64 id SERIAL PRIMARY KEY,
66 version TEXT NOT NULL,
67 maintainer INT4 NOT NULL, -- REFERENCES maintainer
68 file INT4 UNIQUE NOT NULL, -- REFERENCES files
69 install_date TIMESTAMP NOT NULL,
70 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
71 unique (source, version)
74 CREATE TABLE dsc_files (
75 id SERIAL PRIMARY KEY,
76 source INT4 NOT NULL, -- REFERENCES source,
77 file INT4 NOT NULL, -- RERENCES files
81 CREATE TABLE binaries (
82 id SERIAL PRIMARY KEY,
83 package TEXT NOT NULL,
84 version TEXT NOT NULL,
85 maintainer INT4 NOT NULL, -- REFERENCES maintainer
86 source INT4, -- REFERENCES source,
87 architecture INT4 NOT NULL, -- REFERENCES architecture
88 file INT4 UNIQUE NOT NULL, -- REFERENCES files,
90 -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
91 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
92 unique (package, version, architecture)
96 id SERIAL PRIMARY KEY,
97 suite_name TEXT NOT NULL,
106 id SERIAL PRIMARY KEY,
107 queue_name TEXT NOT NULL
110 CREATE TABLE suite_architectures (
111 suite INT4 NOT NULL, -- REFERENCES suite
112 architecture INT4 NOT NULL, -- REFERENCES architecture
113 unique (suite, architecture)
116 CREATE TABLE bin_associations (
117 id SERIAL PRIMARY KEY,
118 suite INT4 NOT NULL, -- REFERENCES suite
119 bin INT4 NOT NULL, -- REFERENCES binaries
123 CREATE TABLE src_associations (
124 id SERIAL PRIMARY KEY,
125 suite INT4 NOT NULL, -- REFERENCES suite
126 source INT4 NOT NULL, -- REFERENCES source
127 unique (suite, source)
130 CREATE TABLE section (
131 id SERIAL PRIMARY KEY,
132 section TEXT UNIQUE NOT NULL
135 CREATE TABLE priority (
136 id SERIAL PRIMARY KEY,
137 priority TEXT UNIQUE NOT NULL,
138 level INT4 UNIQUE NOT NULL
141 CREATE TABLE override_type (
142 id SERIAL PRIMARY KEY,
143 type TEXT UNIQUE NOT NULL
146 CREATE TABLE override (
147 package TEXT NOT NULL,
148 suite INT4 NOT NULL, -- references suite
149 component INT4 NOT NULL, -- references component
150 priority INT4, -- references priority
151 section INT4 NOT NULL, -- references section
152 type INT4 NOT NULL, -- references override_type
154 unique (suite, component, package, type)
157 CREATE TABLE queue_build (
158 suite INT4 NOT NULL, -- references suite
159 queue INT4 NOT NULL, -- references queue
160 filename TEXT NOT NULL,
161 in_queue BOOLEAN NOT NULL,
167 CREATE INDEX bin_associations_bin ON bin_associations (bin);
168 CREATE INDEX src_associations_source ON src_associations (source);
169 CREATE INDEX source_maintainer ON source (maintainer);
170 CREATE INDEX binaries_maintainer ON binaries (maintainer);
171 CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
172 CREATE INDEX source_fingerprint on source (sig_fpr);
173 CREATE INDEX dsc_files_file ON dsc_files (file);