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
36 CREATE TABLE fingerprint (
37 id SERIAL PRIMARY KEY,
38 fingerprint TEXT UNIQUE NOT NULL,
39 uid INT4 REFERENCES uid
42 CREATE TABLE location (
43 id SERIAL PRIMARY KEY,
45 component INT4 REFERENCES component,
46 archive INT4 REFERENCES archive,
50 -- No references below here to allow sane population; added post-population
53 id SERIAL PRIMARY KEY,
54 filename TEXT NOT NULL,
57 location INT4 NOT NULL, -- REFERENCES location
59 unique (filename, location)
63 id SERIAL PRIMARY KEY,
65 version TEXT NOT NULL,
66 maintainer INT4 NOT NULL, -- REFERENCES maintainer
67 file INT4 UNIQUE NOT NULL, -- REFERENCES files
68 install_date TIMESTAMP NOT NULL,
69 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
70 unique (source, version)
73 CREATE TABLE dsc_files (
74 id SERIAL PRIMARY KEY,
75 source INT4 NOT NULL, -- REFERENCES source,
76 file INT4 NOT NULL, -- RERENCES files
80 CREATE TABLE binaries (
81 id SERIAL PRIMARY KEY,
82 package TEXT NOT NULL,
83 version TEXT NOT NULL,
84 maintainer INT4 NOT NULL, -- REFERENCES maintainer
85 source INT4, -- REFERENCES source,
86 architecture INT4 NOT NULL, -- REFERENCES architecture
87 file INT4 UNIQUE NOT NULL, -- REFERENCES files,
89 -- joeyh@ doesn't want .udebs and .debs with the same name, which is why the unique () doesn't mention type
90 sig_fpr INT4 NOT NULL, -- REFERENCES fingerprint
91 unique (package, version, architecture)
95 id SERIAL PRIMARY KEY,
96 suite_name TEXT NOT NULL,
104 CREATE TABLE suite_architectures (
105 suite INT4 NOT NULL, -- REFERENCES suite
106 architecture INT4 NOT NULL, -- REFERENCES architecture
107 unique (suite, architecture)
110 CREATE TABLE bin_associations (
111 id SERIAL PRIMARY KEY,
112 suite INT4 NOT NULL, -- REFERENCES suite
113 bin INT4 NOT NULL, -- REFERENCES binaries
117 CREATE TABLE src_associations (
118 id SERIAL PRIMARY KEY,
119 suite INT4 NOT NULL, -- REFERENCES suite
120 source INT4 NOT NULL, -- REFERENCES source
121 unique (suite, source)
124 CREATE TABLE section (
125 id SERIAL PRIMARY KEY,
126 section TEXT UNIQUE NOT NULL
129 CREATE TABLE priority (
130 id SERIAL PRIMARY KEY,
131 priority TEXT UNIQUE NOT NULL,
132 level INT4 UNIQUE NOT NULL
135 CREATE TABLE override_type (
136 id SERIAL PRIMARY KEY,
137 type TEXT UNIQUE NOT NULL
140 CREATE TABLE override (
141 package TEXT NOT NULL,
142 suite INT4 NOT NULL, -- references suite
143 component INT4 NOT NULL, -- references component
144 priority INT4, -- references priority
145 section INT4 NOT NULL, -- references section
146 type INT4 NOT NULL, -- references override_type
148 unique (suite, component, package, type)
151 CREATE TABLE accepted_autobuild (
152 suite INT4 NOT NULL, -- references suite
153 filename TEXT NOT NULL,
154 in_accepted BOOLEAN NOT NULL,
160 CREATE INDEX bin_associations_bin ON bin_associations (bin);
161 CREATE INDEX src_associations_source ON src_associations (source);
162 CREATE INDEX source_maintainer ON source (maintainer);
163 CREATE INDEX binaries_maintainer ON binaries (maintainer);
164 CREATE INDEX binaries_fingerprint on binaries (sig_fpr);
165 CREATE INDEX source_fingerprint on source (sig_fpr);
166 CREATE INDEX dsc_files_file ON dsc_files (file);