2 -- Created by SQL::Translator::Producer::PostgreSQL
3 -- Created on Wed Aug 6 14:09:26 2014
13 CONSTRAINT "arch_arch_key" UNIQUE ("arch")
20 CREATE TABLE "bin_pkg" (
24 CONSTRAINT "bin_pkg_pkg_key" UNIQUE ("pkg")
29 -- Table: binary_versions.
31 CREATE TABLE "binary_versions" (
37 "src_ver_based_on" debversion,
38 "src_pkg_based_on" text
43 -- Table: bug_package.
45 CREATE TABLE "bug_package" (
54 -- Table: column_comments.
56 CREATE TABLE "column_comments" (
57 "table_name" text NOT NULL,
58 "column_name" text NOT NULL,
59 "comment_text" text NOT NULL,
60 CONSTRAINT "column_comments_table_name_column_name_idx" UNIQUE ("table_name", "column_name")
65 -- Table: correspondent.
67 CREATE TABLE "correspondent" (
71 CONSTRAINT "correspondent_addr_idx" UNIQUE ("addr")
78 CREATE TABLE "message" (
85 "subject" text DEFAULT '' NOT NULL,
86 "sent_date" timestamp with time zone,
87 "refs" text DEFAULT '' NOT NULL,
88 "spam_score" double precision,
89 "is_spam" boolean DEFAULT false,
97 CREATE TABLE "severity" (
99 "severity" text NOT NULL,
100 "ordering" integer DEFAULT 5 NOT NULL,
101 "strong" boolean DEFAULT false,
102 "obsolete" boolean DEFAULT false,
104 CONSTRAINT "severity_severity_idx" UNIQUE ("severity")
111 CREATE TABLE "src_pkg" (
112 "id" serial NOT NULL,
114 "pseduopkg" boolean DEFAULT false,
116 "creation" timestamp with time zone DEFAULT current_timestamp,
117 "disabled" timestamp with time zone,
118 "last_modified" timestamp with time zone DEFAULT current_timestamp,
119 "obsolete" boolean DEFAULT false,
121 CONSTRAINT "src_pkg_pkg_disabled" UNIQUE ("pkg", "disabled")
123 CREATE INDEX "src_pkg_idx_alias_of" on "src_pkg" ("alias_of");
129 CREATE TABLE "suite" (
130 "id" serial NOT NULL,
131 "suite_name" text NOT NULL,
134 "active" boolean DEFAULT true,
136 CONSTRAINT "suite_suite_name_key" UNIQUE ("suite_name")
141 -- Table: table_comments.
143 CREATE TABLE "table_comments" (
144 "table_name" text NOT NULL,
145 "comment_text" text NOT NULL,
146 CONSTRAINT "table_comments_table_name_key" UNIQUE ("table_name")
154 "id" serial NOT NULL,
156 "obsolete" boolean DEFAULT false,
158 CONSTRAINT "tag_tag_key" UNIQUE ("tag")
163 -- Table: correspondent_full_name.
165 CREATE TABLE "correspondent_full_name" (
166 "id" serial NOT NULL,
167 "correspondent" integer NOT NULL,
168 "full_name" text NOT NULL,
169 "last_seen" timestamp DEFAULT current_timestamp NOT NULL,
171 CONSTRAINT "correspondent_full_name_correspondent_full_name_idx" UNIQUE ("correspondent", "full_name")
173 CREATE INDEX "correspondent_full_name_idx_correspondent" on "correspondent_full_name" ("correspondent");
177 -- Table: maintainer.
179 CREATE TABLE "maintainer" (
180 "id" serial NOT NULL,
181 "name" text NOT NULL,
182 "correspondent" integer NOT NULL,
183 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
184 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
186 CONSTRAINT "maintainer_name_idx" UNIQUE ("name")
188 CREATE INDEX "maintainer_idx_correspondent" on "maintainer" ("correspondent");
192 -- Table: message_refs.
194 CREATE TABLE "message_refs" (
195 "id" serial NOT NULL,
196 "message" integer NOT NULL,
197 "refs" integer NOT NULL,
198 "inferred" boolean DEFAULT false,
199 "primary_ref" boolean DEFAULT false,
201 CONSTRAINT "message_refs_message_refs_idx" UNIQUE ("message", "refs")
203 CREATE INDEX "message_refs_idx_message" on "message_refs" ("message");
204 CREATE INDEX "message_refs_idx_refs" on "message_refs" ("refs");
211 "id" integer NOT NULL,
212 "creation" timestamp with time zone DEFAULT current_timestamp NOT NULL,
213 "log_modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
214 "last_modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
215 "archived" boolean DEFAULT false NOT NULL,
216 "unarchived" timestamp with time zone,
217 "forwarded" text DEFAULT '' NOT NULL,
218 "summary" text DEFAULT '' NOT NULL,
219 "outlook" text DEFAULT '' NOT NULL,
220 "subject" text NOT NULL,
221 "severity" integer NOT NULL,
223 "done_full" text DEFAULT '' NOT NULL,
225 "owner_full" text DEFAULT '' NOT NULL,
227 "submitter_full" text DEFAULT '' NOT NULL,
228 "unknown_packages" text DEFAULT '' NOT NULL,
231 CREATE INDEX "bug_idx_done" on "bug" ("done");
232 CREATE INDEX "bug_idx_owner" on "bug" ("owner");
233 CREATE INDEX "bug_idx_severity" on "bug" ("severity");
234 CREATE INDEX "bug_idx_submitter" on "bug" ("submitter");
238 -- Table: message_correspondent.
240 CREATE TABLE "message_correspondent" (
241 "id" serial NOT NULL,
242 "message" integer NOT NULL,
243 "correspondent" integer NOT NULL,
244 "correspondent_type" character varying DEFAULT 'to' NOT NULL,
246 CONSTRAINT "message_correspondent_message_correspondent_correspondent_t_idx" UNIQUE ("message", "correspondent", "correspondent_type")
248 CREATE INDEX "message_correspondent_idx_correspondent" on "message_correspondent" ("correspondent");
249 CREATE INDEX "message_correspondent_idx_message" on "message_correspondent" ("message");
253 -- Table: bug_blocks.
255 CREATE TABLE "bug_blocks" (
256 "id" serial NOT NULL,
257 "bug" integer NOT NULL,
258 "blocks" integer NOT NULL,
260 CONSTRAINT "bug_blocks_bug_id_blocks_idx" UNIQUE ("bug", "blocks")
262 CREATE INDEX "bug_blocks_idx_blocks" on "bug_blocks" ("blocks");
263 CREATE INDEX "bug_blocks_idx_bug" on "bug_blocks" ("bug");
267 -- Table: bug_merged.
269 CREATE TABLE "bug_merged" (
270 "id" serial NOT NULL,
271 "bug" integer NOT NULL,
272 "merged" integer NOT NULL,
274 CONSTRAINT "bug_merged_bug_id_merged_idx" UNIQUE ("bug", "merged")
276 CREATE INDEX "bug_merged_idx_bug" on "bug_merged" ("bug");
277 CREATE INDEX "bug_merged_idx_merged" on "bug_merged" ("merged");
283 CREATE TABLE "src_ver" (
284 "id" serial NOT NULL,
285 "src_pkg" integer NOT NULL,
286 "ver" debversion NOT NULL,
287 "maintainer" integer,
288 "upload_date" timestamp with time zone DEFAULT current_timestamp NOT NULL,
291 CONSTRAINT "src_ver_src_pkg_id_ver" UNIQUE ("src_pkg", "ver")
293 CREATE INDEX "src_ver_idx_based_on" on "src_ver" ("based_on");
294 CREATE INDEX "src_ver_idx_maintainer" on "src_ver" ("maintainer");
295 CREATE INDEX "src_ver_idx_src_pkg" on "src_ver" ("src_pkg");
299 -- Table: bug_binpackage.
301 CREATE TABLE "bug_binpackage" (
302 "id" serial NOT NULL,
303 "bug" integer NOT NULL,
304 "bin_pkg" integer NOT NULL,
306 CONSTRAINT "bug_binpackage_id_pkg" UNIQUE ("bug", "bin_pkg")
308 CREATE INDEX "bug_binpackage_idx_bin_pkg" on "bug_binpackage" ("bin_pkg");
309 CREATE INDEX "bug_binpackage_idx_bug" on "bug_binpackage" ("bug");
313 -- Table: bug_message.
315 CREATE TABLE "bug_message" (
316 "id" serial NOT NULL,
317 "bug" integer NOT NULL,
318 "message" integer NOT NULL,
319 "message_number" integer NOT NULL,
320 "bug_log_offset" integer,
321 "offset_valid" timestamp with time zone,
323 CONSTRAINT "bug_message_bug_message_idx" UNIQUE ("bug", "message")
325 CREATE INDEX "bug_message_idx_bug" on "bug_message" ("bug");
326 CREATE INDEX "bug_message_idx_message" on "bug_message" ("message");
330 -- Table: bug_srcpackage.
332 CREATE TABLE "bug_srcpackage" (
333 "id" serial NOT NULL,
334 "bug" integer NOT NULL,
335 "src_pkg" integer NOT NULL,
337 CONSTRAINT "bug_srcpackage_id_pkg" UNIQUE ("bug", "src_pkg")
339 CREATE INDEX "bug_srcpackage_idx_bug" on "bug_srcpackage" ("bug");
340 CREATE INDEX "bug_srcpackage_idx_src_pkg" on "bug_srcpackage" ("src_pkg");
346 CREATE TABLE "bug_tag" (
347 "id" serial NOT NULL,
348 "bug" integer NOT NULL,
349 "tag" integer NOT NULL,
351 CONSTRAINT "bug_tag_bug_tag" UNIQUE ("bug", "tag")
353 CREATE INDEX "bug_tag_idx_bug" on "bug_tag" ("bug");
354 CREATE INDEX "bug_tag_idx_tag" on "bug_tag" ("tag");
358 -- Table: bug_status_cache.
360 CREATE TABLE "bug_status_cache" (
361 "id" serial NOT NULL,
362 "bug" integer NOT NULL,
365 "status" character varying NOT NULL,
366 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
367 "asof" timestamp with time zone DEFAULT current_timestamp NOT NULL,
369 CONSTRAINT "bug_status_cache_bug_suite_arch_idx" UNIQUE ("bug", "suite", "arch")
371 CREATE INDEX "bug_status_cache_idx_arch" on "bug_status_cache" ("arch");
372 CREATE INDEX "bug_status_cache_idx_bug" on "bug_status_cache" ("bug");
373 CREATE INDEX "bug_status_cache_idx_suite" on "bug_status_cache" ("suite");
377 -- Table: src_associations.
379 CREATE TABLE "src_associations" (
380 "id" serial NOT NULL,
381 "suite" integer NOT NULL,
382 "source" integer NOT NULL,
383 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
384 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
387 CREATE INDEX "src_associations_idx_source" on "src_associations" ("source");
388 CREATE INDEX "src_associations_idx_suite" on "src_associations" ("suite");
394 CREATE TABLE "bin_ver" (
395 "id" serial NOT NULL,
396 "bin_pkg" integer NOT NULL,
397 "src_ver" integer NOT NULL,
398 "arch" integer NOT NULL,
399 "ver" debversion NOT NULL,
402 CREATE INDEX "bin_ver_idx_arch" on "bin_ver" ("arch");
403 CREATE INDEX "bin_ver_idx_bin_pkg" on "bin_ver" ("bin_pkg");
404 CREATE INDEX "bin_ver_idx_src_ver" on "bin_ver" ("src_ver");
410 CREATE TABLE "bug_ver" (
411 "id" serial NOT NULL,
412 "bug" integer NOT NULL,
416 "found" boolean DEFAULT true NOT NULL,
417 "creation" timestamp with time zone DEFAULT current_timestamp NOT NULL,
418 "last_modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
420 CONSTRAINT "bug_ver_bug_ver_string_found_idx" UNIQUE ("bug", "ver_string", "found")
422 CREATE INDEX "bug_ver_idx_bug" on "bug_ver" ("bug");
423 CREATE INDEX "bug_ver_idx_src_pkg" on "bug_ver" ("src_pkg");
424 CREATE INDEX "bug_ver_idx_src_ver" on "bug_ver" ("src_ver");
428 -- Table: bin_associations.
430 CREATE TABLE "bin_associations" (
431 "id" serial NOT NULL,
432 "suite" integer NOT NULL,
433 "bin" integer NOT NULL,
434 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
435 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
438 CREATE INDEX "bin_associations_idx_bin" on "bin_associations" ("bin");
439 CREATE INDEX "bin_associations_idx_suite" on "bin_associations" ("suite");
443 -- Foreign Key Definitions
447 ALTER TABLE "src_pkg" ADD CONSTRAINT "src_pkg_fk_alias_of" FOREIGN KEY ("alias_of")
448 REFERENCES "src_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
451 ALTER TABLE "correspondent_full_name" ADD CONSTRAINT "correspondent_full_name_fk_correspondent" FOREIGN KEY ("correspondent")
452 REFERENCES "correspondent" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
455 ALTER TABLE "maintainer" ADD CONSTRAINT "maintainer_fk_correspondent" FOREIGN KEY ("correspondent")
456 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
459 ALTER TABLE "message_refs" ADD CONSTRAINT "message_refs_fk_message" FOREIGN KEY ("message")
460 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
463 ALTER TABLE "message_refs" ADD CONSTRAINT "message_refs_fk_refs" FOREIGN KEY ("refs")
464 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
467 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_done" FOREIGN KEY ("done")
468 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
471 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_owner" FOREIGN KEY ("owner")
472 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
475 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_severity" FOREIGN KEY ("severity")
476 REFERENCES "severity" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
479 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_submitter" FOREIGN KEY ("submitter")
480 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
483 ALTER TABLE "message_correspondent" ADD CONSTRAINT "message_correspondent_fk_correspondent" FOREIGN KEY ("correspondent")
484 REFERENCES "correspondent" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
487 ALTER TABLE "message_correspondent" ADD CONSTRAINT "message_correspondent_fk_message" FOREIGN KEY ("message")
488 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
491 ALTER TABLE "bug_blocks" ADD CONSTRAINT "bug_blocks_fk_blocks" FOREIGN KEY ("blocks")
492 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
495 ALTER TABLE "bug_blocks" ADD CONSTRAINT "bug_blocks_fk_bug" FOREIGN KEY ("bug")
496 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
499 ALTER TABLE "bug_merged" ADD CONSTRAINT "bug_merged_fk_bug" FOREIGN KEY ("bug")
500 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
503 ALTER TABLE "bug_merged" ADD CONSTRAINT "bug_merged_fk_merged" FOREIGN KEY ("merged")
504 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
507 ALTER TABLE "src_ver" ADD CONSTRAINT "src_ver_fk_based_on" FOREIGN KEY ("based_on")
508 REFERENCES "src_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
511 ALTER TABLE "src_ver" ADD CONSTRAINT "src_ver_fk_maintainer" FOREIGN KEY ("maintainer")
512 REFERENCES "maintainer" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
515 ALTER TABLE "src_ver" ADD CONSTRAINT "src_ver_fk_src_pkg" FOREIGN KEY ("src_pkg")
516 REFERENCES "src_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
519 ALTER TABLE "bug_binpackage" ADD CONSTRAINT "bug_binpackage_fk_bin_pkg" FOREIGN KEY ("bin_pkg")
520 REFERENCES "bin_pkg" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
523 ALTER TABLE "bug_binpackage" ADD CONSTRAINT "bug_binpackage_fk_bug" FOREIGN KEY ("bug")
524 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
527 ALTER TABLE "bug_message" ADD CONSTRAINT "bug_message_fk_bug" FOREIGN KEY ("bug")
528 REFERENCES "bug" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
531 ALTER TABLE "bug_message" ADD CONSTRAINT "bug_message_fk_message" FOREIGN KEY ("message")
532 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
535 ALTER TABLE "bug_srcpackage" ADD CONSTRAINT "bug_srcpackage_fk_bug" FOREIGN KEY ("bug")
536 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
539 ALTER TABLE "bug_srcpackage" ADD CONSTRAINT "bug_srcpackage_fk_src_pkg" FOREIGN KEY ("src_pkg")
540 REFERENCES "src_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
543 ALTER TABLE "bug_tag" ADD CONSTRAINT "bug_tag_fk_bug" FOREIGN KEY ("bug")
544 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
547 ALTER TABLE "bug_tag" ADD CONSTRAINT "bug_tag_fk_tag" FOREIGN KEY ("tag")
548 REFERENCES "tag" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
551 ALTER TABLE "bug_status_cache" ADD CONSTRAINT "bug_status_cache_fk_arch" FOREIGN KEY ("arch")
552 REFERENCES "arch" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
555 ALTER TABLE "bug_status_cache" ADD CONSTRAINT "bug_status_cache_fk_bug" FOREIGN KEY ("bug")
556 REFERENCES "bug" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
559 ALTER TABLE "bug_status_cache" ADD CONSTRAINT "bug_status_cache_fk_suite" FOREIGN KEY ("suite")
560 REFERENCES "suite" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
563 ALTER TABLE "src_associations" ADD CONSTRAINT "src_associations_fk_source" FOREIGN KEY ("source")
564 REFERENCES "src_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
567 ALTER TABLE "src_associations" ADD CONSTRAINT "src_associations_fk_suite" FOREIGN KEY ("suite")
568 REFERENCES "suite" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
571 ALTER TABLE "bin_ver" ADD CONSTRAINT "bin_ver_fk_arch" FOREIGN KEY ("arch")
572 REFERENCES "arch" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
575 ALTER TABLE "bin_ver" ADD CONSTRAINT "bin_ver_fk_bin_pkg" FOREIGN KEY ("bin_pkg")
576 REFERENCES "bin_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
579 ALTER TABLE "bin_ver" ADD CONSTRAINT "bin_ver_fk_src_ver" FOREIGN KEY ("src_ver")
580 REFERENCES "src_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
583 ALTER TABLE "bug_ver" ADD CONSTRAINT "bug_ver_fk_bug" FOREIGN KEY ("bug")
584 REFERENCES "bug" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
587 ALTER TABLE "bug_ver" ADD CONSTRAINT "bug_ver_fk_src_pkg" FOREIGN KEY ("src_pkg")
588 REFERENCES "src_pkg" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
591 ALTER TABLE "bug_ver" ADD CONSTRAINT "bug_ver_fk_src_ver" FOREIGN KEY ("src_ver")
592 REFERENCES "src_ver" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
595 ALTER TABLE "bin_associations" ADD CONSTRAINT "bin_associations_fk_bin" FOREIGN KEY ("bin")
596 REFERENCES "bin_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
599 ALTER TABLE "bin_associations" ADD CONSTRAINT "bin_associations_fk_suite" FOREIGN KEY ("suite")
600 REFERENCES "suite" ("id") ON DELETE CASCADE ON UPDATE CASCADE;