2 -- Created by SQL::Translator::Producer::PostgreSQL
3 -- Created on Wed Aug 6 09:18:36 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")
76 -- Table: dbix_class_deploymenthandler_versions.
78 CREATE TABLE "dbix_class_deploymenthandler_versions" (
80 "version" character varying(50) NOT NULL,
84 CONSTRAINT "dbix_class_deploymenthandler_versions_version" UNIQUE ("version")
91 CREATE TABLE "message" (
98 "subject" text DEFAULT '' NOT NULL,
99 "sent_date" timestamp with time zone,
100 "refs" text DEFAULT '' NOT NULL,
101 "spam_score" double precision,
102 "is_spam" boolean DEFAULT false,
110 CREATE TABLE "severity" (
111 "id" serial NOT NULL,
112 "severity" text NOT NULL,
113 "ordering" integer DEFAULT 5 NOT NULL,
114 "strong" boolean DEFAULT false,
115 "obsolete" boolean DEFAULT false,
117 CONSTRAINT "severity_severity_idx" UNIQUE ("severity")
124 CREATE TABLE "src_pkg" (
125 "id" serial NOT NULL,
127 "pseduopkg" boolean DEFAULT false,
129 "creation" timestamp with time zone DEFAULT current_timestamp,
130 "disabled" timestamp with time zone,
131 "last_modified" timestamp with time zone DEFAULT current_timestamp,
132 "obsolete" boolean DEFAULT false,
134 CONSTRAINT "src_pkg_pkg_disabled" UNIQUE ("pkg", "disabled")
136 CREATE INDEX "src_pkg_idx_alias_of" on "src_pkg" ("alias_of");
142 CREATE TABLE "suite" (
143 "id" serial NOT NULL,
144 "suite_name" text NOT NULL,
147 "active" boolean DEFAULT true,
149 CONSTRAINT "suite_suite_name_key" UNIQUE ("suite_name")
154 -- Table: table_comments.
156 CREATE TABLE "table_comments" (
157 "table_name" text NOT NULL,
158 "comment_text" text NOT NULL,
159 CONSTRAINT "table_comments_table_name_key" UNIQUE ("table_name")
167 "id" serial NOT NULL,
169 "obsolete" boolean DEFAULT false,
171 CONSTRAINT "tag_tag_key" UNIQUE ("tag")
176 -- Table: correspondent_full_name.
178 CREATE TABLE "correspondent_full_name" (
179 "id" serial NOT NULL,
180 "correspondent" integer NOT NULL,
181 "full_name" text NOT NULL,
182 "last_seen" timestamp DEFAULT current_timestamp NOT NULL,
184 CONSTRAINT "correspondent_full_name_correspondent_full_name_idx" UNIQUE ("correspondent", "full_name")
186 CREATE INDEX "correspondent_full_name_idx_correspondent" on "correspondent_full_name" ("correspondent");
190 -- Table: maintainer.
192 CREATE TABLE "maintainer" (
193 "id" serial NOT NULL,
194 "name" text NOT NULL,
195 "correspondent" integer NOT NULL,
196 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
197 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
199 CONSTRAINT "maintainer_name_idx" UNIQUE ("name")
201 CREATE INDEX "maintainer_idx_correspondent" on "maintainer" ("correspondent");
205 -- Table: message_refs.
207 CREATE TABLE "message_refs" (
208 "id" serial NOT NULL,
209 "message" integer NOT NULL,
210 "refs" integer NOT NULL,
211 "inferred" boolean DEFAULT false,
212 "primary_ref" boolean DEFAULT false,
214 CONSTRAINT "message_refs_message_refs_idx" UNIQUE ("message", "refs")
216 CREATE INDEX "message_refs_idx_message" on "message_refs" ("message");
217 CREATE INDEX "message_refs_idx_refs" on "message_refs" ("refs");
224 "id" integer NOT NULL,
225 "creation" timestamp with time zone DEFAULT current_timestamp NOT NULL,
226 "log_modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
227 "last_modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
228 "archived" boolean DEFAULT false NOT NULL,
229 "unarchived" timestamp with time zone,
230 "forwarded" text DEFAULT '' NOT NULL,
231 "summary" text DEFAULT '' NOT NULL,
232 "outlook" text DEFAULT '' NOT NULL,
233 "subject" text NOT NULL,
234 "severity" integer NOT NULL,
236 "done_full" text DEFAULT '' NOT NULL,
238 "owner_full" text DEFAULT '' NOT NULL,
240 "submitter_full" text DEFAULT '' NOT NULL,
241 "unknown_packages" text DEFAULT '' NOT NULL,
244 CREATE INDEX "bug_idx_done" on "bug" ("done");
245 CREATE INDEX "bug_idx_owner" on "bug" ("owner");
246 CREATE INDEX "bug_idx_severity" on "bug" ("severity");
247 CREATE INDEX "bug_idx_submitter" on "bug" ("submitter");
251 -- Table: message_correspondent.
253 CREATE TABLE "message_correspondent" (
254 "id" serial NOT NULL,
255 "message" integer NOT NULL,
256 "correspondent" integer NOT NULL,
257 "correspondent_type" character varying DEFAULT 'to' NOT NULL,
259 CONSTRAINT "message_correspondent_message_correspondent_correspondent_t_idx" UNIQUE ("message", "correspondent", "correspondent_type")
261 CREATE INDEX "message_correspondent_idx_correspondent" on "message_correspondent" ("correspondent");
262 CREATE INDEX "message_correspondent_idx_message" on "message_correspondent" ("message");
266 -- Table: bug_blocks.
268 CREATE TABLE "bug_blocks" (
269 "id" serial NOT NULL,
270 "bug" integer NOT NULL,
271 "blocks" integer NOT NULL,
273 CONSTRAINT "bug_blocks_bug_id_blocks_idx" UNIQUE ("bug", "blocks")
275 CREATE INDEX "bug_blocks_idx_blocks" on "bug_blocks" ("blocks");
276 CREATE INDEX "bug_blocks_idx_bug" on "bug_blocks" ("bug");
280 -- Table: bug_merged.
282 CREATE TABLE "bug_merged" (
283 "id" serial NOT NULL,
284 "bug" integer NOT NULL,
285 "merged" integer NOT NULL,
287 CONSTRAINT "bug_merged_bug_id_merged_idx" UNIQUE ("bug", "merged")
289 CREATE INDEX "bug_merged_idx_bug" on "bug_merged" ("bug");
290 CREATE INDEX "bug_merged_idx_merged" on "bug_merged" ("merged");
296 CREATE TABLE "src_ver" (
297 "id" serial NOT NULL,
298 "src_pkg" integer NOT NULL,
299 "ver" debversion NOT NULL,
300 "maintainer" integer,
301 "upload_date" timestamp with time zone DEFAULT current_timestamp NOT NULL,
304 CONSTRAINT "src_ver_src_pkg_id_ver" UNIQUE ("src_pkg", "ver")
306 CREATE INDEX "src_ver_idx_based_on" on "src_ver" ("based_on");
307 CREATE INDEX "src_ver_idx_maintainer" on "src_ver" ("maintainer");
308 CREATE INDEX "src_ver_idx_src_pkg" on "src_ver" ("src_pkg");
312 -- Table: bug_binpackage.
314 CREATE TABLE "bug_binpackage" (
315 "id" serial NOT NULL,
316 "bug" integer NOT NULL,
317 "bin_pkg" integer NOT NULL,
319 CONSTRAINT "bug_binpackage_id_pkg" UNIQUE ("bug", "bin_pkg")
321 CREATE INDEX "bug_binpackage_idx_bin_pkg" on "bug_binpackage" ("bin_pkg");
322 CREATE INDEX "bug_binpackage_idx_bug" on "bug_binpackage" ("bug");
326 -- Table: bug_message.
328 CREATE TABLE "bug_message" (
329 "id" serial NOT NULL,
330 "bug" integer NOT NULL,
331 "message" integer NOT NULL,
332 "message_number" integer NOT NULL,
333 "bug_log_offset" integer,
334 "offset_valid" timestamp with time zone,
336 CONSTRAINT "bug_message_bug_message_idx" UNIQUE ("bug", "message")
338 CREATE INDEX "bug_message_idx_bug" on "bug_message" ("bug");
339 CREATE INDEX "bug_message_idx_message" on "bug_message" ("message");
343 -- Table: bug_srcpackage.
345 CREATE TABLE "bug_srcpackage" (
346 "id" serial NOT NULL,
347 "bug" integer NOT NULL,
348 "src_pkg" integer NOT NULL,
350 CONSTRAINT "bug_srcpackage_id_pkg" UNIQUE ("bug", "src_pkg")
352 CREATE INDEX "bug_srcpackage_idx_bug" on "bug_srcpackage" ("bug");
353 CREATE INDEX "bug_srcpackage_idx_src_pkg" on "bug_srcpackage" ("src_pkg");
359 CREATE TABLE "bug_tag" (
360 "id" serial NOT NULL,
361 "bug" integer NOT NULL,
362 "tag" integer NOT NULL,
364 CONSTRAINT "bug_tag_bug_tag" UNIQUE ("bug", "tag")
366 CREATE INDEX "bug_tag_idx_bug" on "bug_tag" ("bug");
367 CREATE INDEX "bug_tag_idx_tag" on "bug_tag" ("tag");
371 -- Table: bug_status_cache.
373 CREATE TABLE "bug_status_cache" (
374 "id" serial NOT NULL,
375 "bug" integer NOT NULL,
378 "status" character varying NOT NULL,
379 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
380 "asof" timestamp with time zone DEFAULT current_timestamp NOT NULL,
382 CONSTRAINT "bug_status_cache_bug_suite_arch_idx" UNIQUE ("bug", "suite", "arch")
384 CREATE INDEX "bug_status_cache_idx_arch" on "bug_status_cache" ("arch");
385 CREATE INDEX "bug_status_cache_idx_bug" on "bug_status_cache" ("bug");
386 CREATE INDEX "bug_status_cache_idx_suite" on "bug_status_cache" ("suite");
390 -- Table: src_associations.
392 CREATE TABLE "src_associations" (
393 "id" serial NOT NULL,
394 "suite" integer NOT NULL,
395 "source" integer NOT NULL,
396 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
397 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
400 CREATE INDEX "src_associations_idx_source" on "src_associations" ("source");
401 CREATE INDEX "src_associations_idx_suite" on "src_associations" ("suite");
407 CREATE TABLE "bin_ver" (
408 "id" serial NOT NULL,
409 "bin_pkg" integer NOT NULL,
410 "src_ver" integer NOT NULL,
411 "arch" integer NOT NULL,
412 "ver" debversion NOT NULL,
415 CREATE INDEX "bin_ver_idx_arch" on "bin_ver" ("arch");
416 CREATE INDEX "bin_ver_idx_bin_pkg" on "bin_ver" ("bin_pkg");
417 CREATE INDEX "bin_ver_idx_src_ver" on "bin_ver" ("src_ver");
423 CREATE TABLE "bug_ver" (
424 "id" serial NOT NULL,
425 "bug" integer NOT NULL,
429 "found" boolean DEFAULT true NOT NULL,
430 "creation" timestamp with time zone DEFAULT current_timestamp NOT NULL,
431 "last_modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
433 CONSTRAINT "bug_ver_bug_ver_string_found_idx" UNIQUE ("bug", "ver_string", "found")
435 CREATE INDEX "bug_ver_idx_bug" on "bug_ver" ("bug");
436 CREATE INDEX "bug_ver_idx_src_pkg" on "bug_ver" ("src_pkg");
437 CREATE INDEX "bug_ver_idx_src_ver" on "bug_ver" ("src_ver");
441 -- Table: bin_associations.
443 CREATE TABLE "bin_associations" (
444 "id" serial NOT NULL,
445 "suite" integer NOT NULL,
446 "bin" integer NOT NULL,
447 "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
448 "modified" timestamp with time zone DEFAULT current_timestamp NOT NULL,
451 CREATE INDEX "bin_associations_idx_bin" on "bin_associations" ("bin");
452 CREATE INDEX "bin_associations_idx_suite" on "bin_associations" ("suite");
456 -- Foreign Key Definitions
460 ALTER TABLE "src_pkg" ADD CONSTRAINT "src_pkg_fk_alias_of" FOREIGN KEY ("alias_of")
461 REFERENCES "src_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
464 ALTER TABLE "correspondent_full_name" ADD CONSTRAINT "correspondent_full_name_fk_correspondent" FOREIGN KEY ("correspondent")
465 REFERENCES "correspondent" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
468 ALTER TABLE "maintainer" ADD CONSTRAINT "maintainer_fk_correspondent" FOREIGN KEY ("correspondent")
469 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
472 ALTER TABLE "message_refs" ADD CONSTRAINT "message_refs_fk_message" FOREIGN KEY ("message")
473 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
476 ALTER TABLE "message_refs" ADD CONSTRAINT "message_refs_fk_refs" FOREIGN KEY ("refs")
477 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
480 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_done" FOREIGN KEY ("done")
481 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
484 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_owner" FOREIGN KEY ("owner")
485 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
488 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_severity" FOREIGN KEY ("severity")
489 REFERENCES "severity" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
492 ALTER TABLE "bug" ADD CONSTRAINT "bug_fk_submitter" FOREIGN KEY ("submitter")
493 REFERENCES "correspondent" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
496 ALTER TABLE "message_correspondent" ADD CONSTRAINT "message_correspondent_fk_correspondent" FOREIGN KEY ("correspondent")
497 REFERENCES "correspondent" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
500 ALTER TABLE "message_correspondent" ADD CONSTRAINT "message_correspondent_fk_message" FOREIGN KEY ("message")
501 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
504 ALTER TABLE "bug_blocks" ADD CONSTRAINT "bug_blocks_fk_blocks" FOREIGN KEY ("blocks")
505 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
508 ALTER TABLE "bug_blocks" ADD CONSTRAINT "bug_blocks_fk_bug" FOREIGN KEY ("bug")
509 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
512 ALTER TABLE "bug_merged" ADD CONSTRAINT "bug_merged_fk_bug" FOREIGN KEY ("bug")
513 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
516 ALTER TABLE "bug_merged" ADD CONSTRAINT "bug_merged_fk_merged" FOREIGN KEY ("merged")
517 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
520 ALTER TABLE "src_ver" ADD CONSTRAINT "src_ver_fk_based_on" FOREIGN KEY ("based_on")
521 REFERENCES "src_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
524 ALTER TABLE "src_ver" ADD CONSTRAINT "src_ver_fk_maintainer" FOREIGN KEY ("maintainer")
525 REFERENCES "maintainer" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
528 ALTER TABLE "src_ver" ADD CONSTRAINT "src_ver_fk_src_pkg" FOREIGN KEY ("src_pkg")
529 REFERENCES "src_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
532 ALTER TABLE "bug_binpackage" ADD CONSTRAINT "bug_binpackage_fk_bin_pkg" FOREIGN KEY ("bin_pkg")
533 REFERENCES "bin_pkg" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
536 ALTER TABLE "bug_binpackage" ADD CONSTRAINT "bug_binpackage_fk_bug" FOREIGN KEY ("bug")
537 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
540 ALTER TABLE "bug_message" ADD CONSTRAINT "bug_message_fk_bug" FOREIGN KEY ("bug")
541 REFERENCES "bug" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
544 ALTER TABLE "bug_message" ADD CONSTRAINT "bug_message_fk_message" FOREIGN KEY ("message")
545 REFERENCES "message" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
548 ALTER TABLE "bug_srcpackage" ADD CONSTRAINT "bug_srcpackage_fk_bug" FOREIGN KEY ("bug")
549 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
552 ALTER TABLE "bug_srcpackage" ADD CONSTRAINT "bug_srcpackage_fk_src_pkg" FOREIGN KEY ("src_pkg")
553 REFERENCES "src_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
556 ALTER TABLE "bug_tag" ADD CONSTRAINT "bug_tag_fk_bug" FOREIGN KEY ("bug")
557 REFERENCES "bug" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
560 ALTER TABLE "bug_tag" ADD CONSTRAINT "bug_tag_fk_tag" FOREIGN KEY ("tag")
561 REFERENCES "tag" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
564 ALTER TABLE "bug_status_cache" ADD CONSTRAINT "bug_status_cache_fk_arch" FOREIGN KEY ("arch")
565 REFERENCES "arch" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
568 ALTER TABLE "bug_status_cache" ADD CONSTRAINT "bug_status_cache_fk_bug" FOREIGN KEY ("bug")
569 REFERENCES "bug" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
572 ALTER TABLE "bug_status_cache" ADD CONSTRAINT "bug_status_cache_fk_suite" FOREIGN KEY ("suite")
573 REFERENCES "suite" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
576 ALTER TABLE "src_associations" ADD CONSTRAINT "src_associations_fk_source" FOREIGN KEY ("source")
577 REFERENCES "src_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
580 ALTER TABLE "src_associations" ADD CONSTRAINT "src_associations_fk_suite" FOREIGN KEY ("suite")
581 REFERENCES "suite" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
584 ALTER TABLE "bin_ver" ADD CONSTRAINT "bin_ver_fk_arch" FOREIGN KEY ("arch")
585 REFERENCES "arch" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
588 ALTER TABLE "bin_ver" ADD CONSTRAINT "bin_ver_fk_bin_pkg" FOREIGN KEY ("bin_pkg")
589 REFERENCES "bin_pkg" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
592 ALTER TABLE "bin_ver" ADD CONSTRAINT "bin_ver_fk_src_ver" FOREIGN KEY ("src_ver")
593 REFERENCES "src_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
596 ALTER TABLE "bug_ver" ADD CONSTRAINT "bug_ver_fk_bug" FOREIGN KEY ("bug")
597 REFERENCES "bug" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
600 ALTER TABLE "bug_ver" ADD CONSTRAINT "bug_ver_fk_src_pkg" FOREIGN KEY ("src_pkg")
601 REFERENCES "src_pkg" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
604 ALTER TABLE "bug_ver" ADD CONSTRAINT "bug_ver_fk_src_ver" FOREIGN KEY ("src_ver")
605 REFERENCES "src_ver" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
608 ALTER TABLE "bin_associations" ADD CONSTRAINT "bin_associations_fk_bin" FOREIGN KEY ("bin")
609 REFERENCES "bin_ver" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
612 ALTER TABLE "bin_associations" ADD CONSTRAINT "bin_associations_fk_suite" FOREIGN KEY ("suite")
613 REFERENCES "suite" ("id") ON DELETE CASCADE ON UPDATE CASCADE;