table.sql 27.5 KB
Newer Older
1 2
-- 
-- Created by SQL::Translator::Producer::SQLite
3
-- Created on Tue Feb 12 15:00:22 2019
4 5 6 7 8
-- 

BEGIN TRANSACTION;

--
9
-- Table: "alt_allele"
10
--
11 12 13 14
CREATE TABLE "alt_allele" (
  "alt_allele_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "alt_allele_group_id" integer NOT NULL,
  "gene_id" integer NOT NULL
15 16
);

17
CREATE UNIQUE INDEX "gene_idx" ON "alt_allele" ("gene_id");
18 19

--
20
-- Table: "alt_allele_attrib"
21
--
22 23 24
CREATE TABLE "alt_allele_attrib" (
  "alt_allele_id" integer,
  "attrib" enum
25 26 27
);

--
28
-- Table: "alt_allele_group"
29
--
30 31
CREATE TABLE "alt_allele_group" (
  "alt_allele_group_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
32 33 34
);

--
35
-- Table: "analysis"
36
--
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
CREATE TABLE "analysis" (
  "analysis_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "created" datetime,
  "logic_name" varchar(40) NOT NULL DEFAULT '',
  "db" varchar(120),
  "db_version" varchar(40),
  "db_file" varchar(120),
  "program" varchar(80),
  "program_version" varchar(40),
  "program_file" varchar(80),
  "parameters" text,
  "module" varchar(80),
  "module_version" varchar(40),
  "gff_source" varchar(40),
  "gff_feature" varchar(40)
52 53
);

54
CREATE UNIQUE INDEX "logic_name_idx" ON "analysis" ("logic_name");
55 56

--
57
-- Table: "analysis_description"
58
--
59 60 61 62 63 64
CREATE TABLE "analysis_description" (
  "analysis_id" integer NOT NULL DEFAULT 0,
  "description" text,
  "display_label" varchar(255),
  "displayable" tinyint NOT NULL DEFAULT 1,
  "web_data" text
65 66
);

67
CREATE UNIQUE INDEX "analysis_idx" ON "analysis_description" ("analysis_id");
Magali Ruffier's avatar
Magali Ruffier committed
68

69
--
70
-- Table: "assembly"
71
--
72 73 74 75 76 77 78 79
CREATE TABLE "assembly" (
  "asm_seq_region_id" integer NOT NULL DEFAULT 0,
  "cmp_seq_region_id" integer NOT NULL DEFAULT 0,
  "asm_start" integer NOT NULL DEFAULT 0,
  "asm_end" integer NOT NULL DEFAULT 0,
  "cmp_start" integer NOT NULL DEFAULT 0,
  "cmp_end" integer NOT NULL DEFAULT 0,
  "ori" tinyint NOT NULL DEFAULT 0
80 81
);

82
CREATE UNIQUE INDEX "all_idx" ON "assembly" ("asm_seq_region_id", "cmp_seq_region_id", "asm_start", "asm_end", "cmp_start", "cmp_end", "ori");
83 84

--
85
-- Table: "assembly_exception"
86
--
87 88 89 90 91 92 93 94 95 96
CREATE TABLE "assembly_exception" (
  "assembly_exception_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "exc_type" enum NOT NULL DEFAULT 'HAP',
  "exc_seq_region_id" integer NOT NULL DEFAULT 0,
  "exc_seq_region_start" integer NOT NULL DEFAULT 0,
  "exc_seq_region_end" integer NOT NULL DEFAULT 0,
  "ori" integer NOT NULL DEFAULT 0
97 98 99
);

--
100
-- Table: "associated_group"
101
--
102 103 104
CREATE TABLE "associated_group" (
  "associated_group_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "description" varchar(128)
105 106 107
);

--
108
-- Table: "associated_xref"
109
--
110 111 112 113 114 115 116 117
CREATE TABLE "associated_xref" (
  "associated_xref_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "object_xref_id" integer NOT NULL DEFAULT 0,
  "xref_id" integer NOT NULL DEFAULT 0,
  "source_xref_id" integer,
  "condition_type" varchar(128),
  "associated_group_id" integer,
  "rank" integer DEFAULT 0
118 119
);

120
CREATE UNIQUE INDEX "object_associated_source_type_idx" ON "associated_xref" ("object_xref_id", "xref_id", "source_xref_id", "condition_type", "associated_group_id");
121 122

--
123
-- Table: "attrib_type"
124
--
125 126 127 128 129
CREATE TABLE "attrib_type" (
  "attrib_type_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "code" varchar(20) NOT NULL DEFAULT '',
  "name" varchar(255) NOT NULL DEFAULT '',
  "description" text
130 131
);

132
CREATE UNIQUE INDEX "code_idx" ON "attrib_type" ("code");
133

134
--
135
-- Table: "biotype"
136
--
137 138 139 140 141 142 143 144
CREATE TABLE "biotype" (
  "biotype_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "name" varchar(64) NOT NULL,
  "object_type" enum NOT NULL DEFAULT 'gene',
  "db_type" varchar NOT NULL DEFAULT 'core',
  "attrib_type_id" integer,
  "description" text,
  "biotype_group" enum,
145 146
  "so_acc" varchar(64),
  "so_term" varchar(1023)
147 148
);

149
CREATE UNIQUE INDEX "name_type_idx" ON "biotype" ("name", "object_type");
150

151
--
152
-- Table: "coord_system"
153
--
154 155 156 157 158 159 160
CREATE TABLE "coord_system" (
  "coord_system_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "species_id" integer NOT NULL DEFAULT 1,
  "name" varchar(40) NOT NULL,
  "version" varchar(255),
  "rank" integer NOT NULL,
  "attrib" varchar
161 162
);

163
CREATE UNIQUE INDEX "name_idx" ON "coord_system" ("name", "version", "species_id");
164

165
CREATE UNIQUE INDEX "rank_idx" ON "coord_system" ("rank", "species_id");
166 167

--
168
-- Table: "data_file"
169
--
170 171 172 173 174 175 176 177 178
CREATE TABLE "data_file" (
  "data_file_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "coord_system_id" integer NOT NULL,
  "analysis_id" smallint NOT NULL,
  "name" varchar(100) NOT NULL,
  "version_lock" tinyint NOT NULL DEFAULT 0,
  "absolute" tinyint NOT NULL DEFAULT 0,
  "url" text,
  "file_type" enum
179 180
);

181
CREATE UNIQUE INDEX "df_unq_idx" ON "data_file" ("coord_system_id", "analysis_id", "name", "file_type");
182 183

--
184
-- Table: "density_feature"
185
--
186 187 188 189 190 191 192
CREATE TABLE "density_feature" (
  "density_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "density_type_id" integer NOT NULL DEFAULT 0,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "density_value" float NOT NULL DEFAULT 0
193 194 195
);

--
196
-- Table: "density_type"
197
--
198 199 200 201 202 203
CREATE TABLE "density_type" (
  "density_type_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "analysis_id" integer NOT NULL DEFAULT 0,
  "block_size" integer NOT NULL DEFAULT 0,
  "region_features" integer NOT NULL DEFAULT 0,
  "value_type" enum NOT NULL DEFAULT 'sum'
204 205
);

206
CREATE UNIQUE INDEX "analysis_id" ON "density_type" ("analysis_id", "block_size", "region_features");
207 208

--
209
-- Table: "dependent_xref"
210
--
211 212 213 214
CREATE TABLE "dependent_xref" (
  "object_xref_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "master_xref_id" integer NOT NULL,
  "dependent_xref_id" integer NOT NULL
215 216 217
);

--
218
-- Table: "ditag"
219
--
220 221 222 223 224 225
CREATE TABLE "ditag" (
  "ditag_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "name" varchar(30),
  "type" varchar(30),
  "tag_count" smallint DEFAULT 1,
  "sequence" text
226 227 228
);

--
229
-- Table: "ditag_feature"
230
--
231 232 233 234 235 236 237 238 239 240 241 242 243 244
CREATE TABLE "ditag_feature" (
  "ditag_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "ditag_id" integer NOT NULL DEFAULT 0,
  "ditag_pair_id" integer NOT NULL DEFAULT 0,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 0,
  "analysis_id" integer NOT NULL DEFAULT 0,
  "hit_start" integer NOT NULL DEFAULT 0,
  "hit_end" integer NOT NULL DEFAULT 0,
  "hit_strand" tinyint NOT NULL DEFAULT 0,
  "cigar_line" text,
  "ditag_side" char(1) DEFAULT ''
245 246 247
);

--
248
-- Table: "dna"
249
--
250 251 252
CREATE TABLE "dna" (
  "seq_region_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 0,
  "sequence" mediumtext NOT NULL
253 254 255
);

--
256
-- Table: "dna_align_feature"
257
--
258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275
CREATE TABLE "dna_align_feature" (
  "dna_align_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 0,
  "hit_start" integer NOT NULL DEFAULT 0,
  "hit_end" integer NOT NULL DEFAULT 0,
  "hit_strand" tinyint NOT NULL DEFAULT 0,
  "hit_name" varchar(40) NOT NULL DEFAULT '',
  "analysis_id" integer NOT NULL DEFAULT 0,
  "score" double precision,
  "evalue" double precision,
  "perc_ident" float,
  "cigar_line" text,
  "external_db_id" smallint,
  "hcoverage" double precision,
  "align_type" enum DEFAULT 'ensembl'
276 277
);

278
--
279
-- Table: "dna_align_feature_attrib"
280
--
281 282 283 284
CREATE TABLE "dna_align_feature_attrib" (
  "dna_align_feature_id" integer NOT NULL,
  "attrib_type_id" smallint NOT NULL,
  "value" text NOT NULL
285 286
);

287
CREATE UNIQUE INDEX "dna_align_feature_attribx" ON "dna_align_feature_attrib" ("dna_align_feature_id", "attrib_type_id", "value");
288

289
--
290
-- Table: "exon"
291
--
292 293 294 295 296 297 298 299 300 301 302 303 304 305
CREATE TABLE "exon" (
  "exon_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL,
  "seq_region_start" integer NOT NULL,
  "seq_region_end" integer NOT NULL,
  "seq_region_strand" tinyint NOT NULL,
  "phase" tinyint NOT NULL,
  "end_phase" tinyint NOT NULL,
  "is_current" tinyint NOT NULL DEFAULT 1,
  "is_constitutive" tinyint NOT NULL DEFAULT 0,
  "stable_id" varchar(128),
  "version" smallint,
  "created_date" datetime,
  "modified_date" datetime
306 307 308
);

--
309
-- Table: "exon_transcript"
310
--
311 312 313 314 315
CREATE TABLE "exon_transcript" (
  "exon_id" integer NOT NULL DEFAULT 0,
  "transcript_id" integer NOT NULL DEFAULT 0,
  "rank" integer NOT NULL DEFAULT 0,
  PRIMARY KEY ("exon_id", "transcript_id", "rank")
316 317 318
);

--
319
-- Table: "external_db"
320
--
321 322 323 324 325 326 327 328 329 330 331
CREATE TABLE "external_db" (
  "external_db_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 0,
  "db_name" varchar(27) NOT NULL DEFAULT '',
  "db_release" varchar(40) NOT NULL DEFAULT '',
  "status" enum NOT NULL DEFAULT 'KNOWNXREF',
  "priority" integer NOT NULL DEFAULT 0,
  "db_display_name" varchar(255),
  "type" enum,
  "secondary_db_name" varchar(255),
  "secondary_db_table" varchar(255),
  "description" text
332 333
);

334
CREATE UNIQUE INDEX "db_name_db_release_idx" ON "external_db" ("db_name", "db_release");
Magali Ruffier's avatar
Magali Ruffier committed
335

336
--
337
-- Table: "external_synonym"
338
--
339 340 341 342
CREATE TABLE "external_synonym" (
  "xref_id" integer NOT NULL DEFAULT 0,
  "synonym" varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY ("xref_id", "synonym")
343 344 345
);

--
346
-- Table: "gene"
347
--
348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364
CREATE TABLE "gene" (
  "gene_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "biotype" varchar(40) NOT NULL,
  "analysis_id" smallint NOT NULL,
  "seq_region_id" integer NOT NULL,
  "seq_region_start" integer NOT NULL,
  "seq_region_end" integer NOT NULL,
  "seq_region_strand" tinyint NOT NULL,
  "display_xref_id" integer,
  "source" varchar(40) NOT NULL,
  "description" text,
  "is_current" tinyint NOT NULL DEFAULT 1,
  "canonical_transcript_id" integer NOT NULL,
  "stable_id" varchar(128),
  "version" smallint,
  "created_date" datetime,
  "modified_date" datetime
365 366 367
);

--
368
-- Table: "gene_archive"
369
--
370 371 372 373 374 375 376 377 378
CREATE TABLE "gene_archive" (
  "gene_stable_id" varchar(128) NOT NULL DEFAULT '',
  "gene_version" smallint NOT NULL DEFAULT 0,
  "transcript_stable_id" varchar(128) NOT NULL DEFAULT '',
  "transcript_version" smallint NOT NULL DEFAULT 0,
  "translation_stable_id" varchar(128) NOT NULL DEFAULT '',
  "translation_version" smallint NOT NULL DEFAULT 0,
  "peptide_archive_id" integer NOT NULL DEFAULT 0,
  "mapping_session_id" integer NOT NULL DEFAULT 0
379 380 381
);

--
382
-- Table: "gene_attrib"
383
--
384 385 386 387
CREATE TABLE "gene_attrib" (
  "gene_id" integer NOT NULL DEFAULT 0,
  "attrib_type_id" smallint NOT NULL DEFAULT 0,
  "value" text NOT NULL
388 389
);

390
CREATE UNIQUE INDEX "gene_attribx" ON "gene_attrib" ("gene_id", "attrib_type_id", "value");
391 392

--
393
-- Table: "genome_statistics"
394
--
395 396 397 398 399 400 401
CREATE TABLE "genome_statistics" (
  "genome_statistics_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "statistic" varchar(128) NOT NULL,
  "value" bigint NOT NULL DEFAULT 0,
  "species_id" integer DEFAULT 1,
  "attrib_type_id" integer,
  "timestamp" datetime
402 403
);

404
CREATE UNIQUE INDEX "stats_uniq" ON "genome_statistics" ("statistic", "attrib_type_id", "species_id");
405 406

--
407
-- Table: "identity_xref"
408
--
409 410 411 412 413 414 415 416 417 418 419
CREATE TABLE "identity_xref" (
  "object_xref_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 0,
  "xref_identity" integer,
  "ensembl_identity" integer,
  "xref_start" integer,
  "xref_end" integer,
  "ensembl_start" integer,
  "ensembl_end" integer,
  "cigar_line" text,
  "score" double precision,
  "evalue" double precision
420 421 422
);

--
423
-- Table: "interpro"
424
--
425 426 427
CREATE TABLE "interpro" (
  "interpro_ac" varchar(40) NOT NULL DEFAULT '',
  "id" varchar(40) NOT NULL
428 429
);

430
CREATE UNIQUE INDEX "accession_idx" ON "interpro" ("interpro_ac", "id");
431 432

--
433
-- Table: "intron_supporting_evidence"
434
--
435 436 437 438 439 440 441 442 443 444 445
CREATE TABLE "intron_supporting_evidence" (
  "intron_supporting_evidence_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "analysis_id" smallint NOT NULL,
  "seq_region_id" integer NOT NULL,
  "seq_region_start" integer NOT NULL,
  "seq_region_end" integer NOT NULL,
  "seq_region_strand" tinyint NOT NULL,
  "hit_name" varchar(100) NOT NULL,
  "score" decimal(10,3),
  "score_type" enum DEFAULT 'NONE',
  "is_splice_canonical" tinyint NOT NULL DEFAULT 0
446 447
);

448
CREATE UNIQUE INDEX "analysis_id02" ON "intron_supporting_evidence" ("analysis_id", "seq_region_id", "seq_region_start", "seq_region_end", "seq_region_strand", "hit_name");
449 450

--
451
-- Table: "karyotype"
452
--
453 454 455 456 457 458 459
CREATE TABLE "karyotype" (
  "karyotype_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "band" varchar(40),
  "stain" varchar(40)
460 461 462
);

--
463
-- Table: "map"
464
--
465 466 467
CREATE TABLE "map" (
  "map_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "map_name" varchar(30) NOT NULL DEFAULT ''
468 469 470
);

--
471
-- Table: "mapping_session"
472
--
473 474 475 476 477 478 479 480 481
CREATE TABLE "mapping_session" (
  "mapping_session_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "old_db_name" varchar(80) NOT NULL DEFAULT '',
  "new_db_name" varchar(80) NOT NULL DEFAULT '',
  "old_release" varchar(5) NOT NULL DEFAULT '',
  "new_release" varchar(5) NOT NULL DEFAULT '',
  "old_assembly" varchar(20) NOT NULL DEFAULT '',
  "new_assembly" varchar(20) NOT NULL DEFAULT '',
  "created" datetime
482 483 484
);

--
485
-- Table: "mapping_set"
486
--
487 488 489 490
CREATE TABLE "mapping_set" (
  "mapping_set_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "internal_schema_build" varchar(20) NOT NULL,
  "external_schema_build" varchar(20) NOT NULL
491 492
);

493
CREATE UNIQUE INDEX "mapping_idx" ON "mapping_set" ("internal_schema_build", "external_schema_build");
494 495

--
496
-- Table: "marker"
497
--
498 499 500 501 502 503 504 505 506
CREATE TABLE "marker" (
  "marker_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "display_marker_synonym_id" integer,
  "left_primer" varchar(100) NOT NULL DEFAULT '',
  "right_primer" varchar(100) NOT NULL DEFAULT '',
  "min_primer_dist" integer NOT NULL DEFAULT 0,
  "max_primer_dist" integer NOT NULL DEFAULT 0,
  "priority" integer,
  "type" enum
507 508 509
);

--
510
-- Table: "marker_feature"
511
--
512 513 514 515 516 517 518 519
CREATE TABLE "marker_feature" (
  "marker_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "marker_id" integer NOT NULL DEFAULT 0,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "analysis_id" integer NOT NULL DEFAULT 0,
  "map_weight" integer
520 521 522
);

--
523
-- Table: "marker_map_location"
524
--
525 526 527 528 529 530 531 532
CREATE TABLE "marker_map_location" (
  "marker_id" integer NOT NULL DEFAULT 0,
  "map_id" integer NOT NULL DEFAULT 0,
  "chromosome_name" varchar(15) NOT NULL DEFAULT '',
  "marker_synonym_id" integer NOT NULL DEFAULT 0,
  "position" varchar(15) NOT NULL DEFAULT '',
  "lod_score" double precision,
  PRIMARY KEY ("marker_id", "map_id")
533 534 535
);

--
536
-- Table: "marker_synonym"
537
--
538 539 540 541 542
CREATE TABLE "marker_synonym" (
  "marker_synonym_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "marker_id" integer NOT NULL DEFAULT 0,
  "source" varchar(20),
  "name" varchar(30)
543 544 545
);

--
546
-- Table: "meta"
547
--
548 549 550 551 552
CREATE TABLE "meta" (
  "meta_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "species_id" integer DEFAULT 1,
  "meta_key" varchar(40) NOT NULL,
  "meta_value" varchar(255) NOT NULL
553 554
);

555
CREATE UNIQUE INDEX "species_key_value_idx" ON "meta" ("species_id", "meta_key", "meta_value");
556 557

--
558
-- Table: "meta_coord"
559
--
560 561 562 563
CREATE TABLE "meta_coord" (
  "table_name" varchar(40) NOT NULL DEFAULT '',
  "coord_system_id" integer NOT NULL DEFAULT 0,
  "max_length" integer
564 565
);

566
CREATE UNIQUE INDEX "cs_table_name_idx" ON "meta_coord" ("coord_system_id", "table_name");
567 568

--
569
-- Table: "misc_attrib"
570
--
571 572 573 574
CREATE TABLE "misc_attrib" (
  "misc_feature_id" integer NOT NULL DEFAULT 0,
  "attrib_type_id" smallint NOT NULL DEFAULT 0,
  "value" text NOT NULL
575 576
);

577
CREATE UNIQUE INDEX "misc_attribx" ON "misc_attrib" ("misc_feature_id", "attrib_type_id", "value");
578 579

--
580
-- Table: "misc_feature"
581
--
582 583 584 585 586 587
CREATE TABLE "misc_feature" (
  "misc_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 0
588 589 590
);

--
591
-- Table: "misc_feature_misc_set"
592
--
593 594 595 596
CREATE TABLE "misc_feature_misc_set" (
  "misc_feature_id" integer NOT NULL DEFAULT 0,
  "misc_set_id" smallint NOT NULL DEFAULT 0,
  PRIMARY KEY ("misc_feature_id", "misc_set_id")
597 598 599
);

--
600
-- Table: "misc_set"
601
--
602 603 604 605 606 607
CREATE TABLE "misc_set" (
  "misc_set_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "code" varchar(25) NOT NULL DEFAULT '',
  "name" varchar(255) NOT NULL DEFAULT '',
  "description" text NOT NULL,
  "max_length" integer NOT NULL DEFAULT 0
608 609
);

610
CREATE UNIQUE INDEX "code_idx02" ON "misc_set" ("code");
611 612

--
613
-- Table: "object_xref"
614
--
615 616 617 618 619 620 621
CREATE TABLE "object_xref" (
  "object_xref_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "ensembl_id" integer NOT NULL DEFAULT 0,
  "ensembl_object_type" enum NOT NULL DEFAULT 'RawContig',
  "xref_id" integer NOT NULL,
  "linkage_annotation" varchar(255),
  "analysis_id" smallint
622 623
);

624
CREATE UNIQUE INDEX "xref_idx" ON "object_xref" ("xref_id", "ensembl_object_type", "ensembl_id");
625 626

--
627
-- Table: "ontology_xref"
628
--
629 630 631 632
CREATE TABLE "ontology_xref" (
  "object_xref_id" integer NOT NULL DEFAULT 0,
  "linkage_type" varchar(3),
  "source_xref_id" integer
633 634
);

635
CREATE UNIQUE INDEX "object_source_type_idx" ON "ontology_xref" ("object_xref_id", "source_xref_id", "linkage_type");
636 637

--
638
-- Table: "operon"
639
--
640 641 642 643 644 645 646 647 648 649 650 651
CREATE TABLE "operon" (
  "operon_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL,
  "seq_region_start" integer NOT NULL,
  "seq_region_end" integer NOT NULL,
  "seq_region_strand" tinyint NOT NULL,
  "display_label" varchar(255),
  "analysis_id" smallint NOT NULL,
  "stable_id" varchar(128),
  "version" smallint,
  "created_date" datetime,
  "modified_date" datetime
652 653 654
);

--
655
-- Table: "operon_transcript"
656
--
657 658 659 660 661 662 663 664 665 666 667 668 669
CREATE TABLE "operon_transcript" (
  "operon_transcript_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL,
  "seq_region_start" integer NOT NULL,
  "seq_region_end" integer NOT NULL,
  "seq_region_strand" tinyint NOT NULL,
  "operon_id" integer NOT NULL,
  "display_label" varchar(255),
  "analysis_id" smallint NOT NULL,
  "stable_id" varchar(128),
  "version" smallint,
  "created_date" datetime,
  "modified_date" datetime
670 671 672
);

--
673
-- Table: "operon_transcript_gene"
674
--
675 676 677
CREATE TABLE "operon_transcript_gene" (
  "operon_transcript_id" integer,
  "gene_id" integer
678 679 680
);

--
681
-- Table: "peptide_archive"
682
--
683 684 685 686
CREATE TABLE "peptide_archive" (
  "peptide_archive_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "md5_checksum" varchar(32),
  "peptide_seq" mediumtext NOT NULL
687 688 689
);

--
690
-- Table: "prediction_exon"
691
--
692 693 694 695 696 697 698 699 700 701 702
CREATE TABLE "prediction_exon" (
  "prediction_exon_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "prediction_transcript_id" integer NOT NULL DEFAULT 0,
  "exon_rank" smallint NOT NULL DEFAULT 0,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 0,
  "start_phase" tinyint NOT NULL DEFAULT 0,
  "score" double precision,
  "p_value" double precision
703 704 705
);

--
706
-- Table: "prediction_transcript"
707
--
708 709 710 711 712 713 714 715
CREATE TABLE "prediction_transcript" (
  "prediction_transcript_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 0,
  "analysis_id" integer,
  "display_label" varchar(255)
716 717 718
);

--
719
-- Table: "protein_align_feature"
720
--
721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737
CREATE TABLE "protein_align_feature" (
  "protein_align_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 1,
  "hit_start" integer NOT NULL DEFAULT 0,
  "hit_end" integer NOT NULL DEFAULT 0,
  "hit_name" varchar(40) NOT NULL DEFAULT '',
  "analysis_id" integer NOT NULL DEFAULT 0,
  "score" double precision,
  "evalue" double precision,
  "perc_ident" float,
  "cigar_line" text,
  "external_db_id" smallint,
  "hcoverage" double precision,
  "align_type" enum DEFAULT 'ensembl'
738 739 740
);

--
741
-- Table: "protein_feature"
742
--
743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758
CREATE TABLE "protein_feature" (
  "protein_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "translation_id" integer NOT NULL DEFAULT 0,
  "seq_start" integer NOT NULL DEFAULT 0,
  "seq_end" integer NOT NULL DEFAULT 0,
  "hit_start" integer NOT NULL DEFAULT 0,
  "hit_end" integer NOT NULL DEFAULT 0,
  "hit_name" varchar(40) NOT NULL,
  "analysis_id" integer NOT NULL DEFAULT 0,
  "score" double precision NOT NULL DEFAULT 0,
  "evalue" double precision,
  "perc_ident" float,
  "external_data" text,
  "hit_description" text,
  "cigar_line" text,
  "align_type" enum
759 760
);

761
CREATE UNIQUE INDEX "aln_idx" ON "protein_feature" ("translation_id", "hit_name", "seq_start", "seq_end", "hit_start", "hit_end", "analysis_id");
Magali Ruffier's avatar
Magali Ruffier committed
762

763
--
764
-- Table: "repeat_consensus"
765
--
766 767 768 769 770 771
CREATE TABLE "repeat_consensus" (
  "repeat_consensus_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "repeat_name" varchar(255) NOT NULL DEFAULT '',
  "repeat_class" varchar(100) NOT NULL DEFAULT '',
  "repeat_type" varchar(40) NOT NULL DEFAULT '',
  "repeat_consensus" text
772 773 774
);

--
775
-- Table: "repeat_feature"
776
--
777 778 779 780 781 782 783 784 785 786 787
CREATE TABLE "repeat_feature" (
  "repeat_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 1,
  "repeat_start" integer NOT NULL DEFAULT 0,
  "repeat_end" integer NOT NULL DEFAULT 0,
  "repeat_consensus_id" integer NOT NULL DEFAULT 0,
  "analysis_id" integer NOT NULL DEFAULT 0,
  "score" double precision
788 789 790
);

--
791
-- Table: "seq_region"
792
--
793 794 795 796 797
CREATE TABLE "seq_region" (
  "seq_region_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "name" varchar(255) NOT NULL,
  "coord_system_id" integer NOT NULL DEFAULT 0,
  "length" integer NOT NULL DEFAULT 0
798 799
);

800
CREATE UNIQUE INDEX "name_cs_idx" ON "seq_region" ("name", "coord_system_id");
801 802

--
803
-- Table: "seq_region_attrib"
804
--
805 806 807 808
CREATE TABLE "seq_region_attrib" (
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "attrib_type_id" smallint NOT NULL DEFAULT 0,
  "value" text NOT NULL
809 810
);

811
CREATE UNIQUE INDEX "region_attribx" ON "seq_region_attrib" ("seq_region_id", "attrib_type_id", "value");
812 813

--
814
-- Table: "seq_region_mapping"
815
--
816 817 818 819
CREATE TABLE "seq_region_mapping" (
  "external_seq_region_id" integer NOT NULL,
  "internal_seq_region_id" integer NOT NULL,
  "mapping_set_id" integer NOT NULL
820 821 822
);

--
823
-- Table: "seq_region_synonym"
824
--
825 826 827 828 829
CREATE TABLE "seq_region_synonym" (
  "seq_region_synonym_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL,
  "synonym" varchar(250) NOT NULL,
  "external_db_id" smallint
830 831
);

832
CREATE UNIQUE INDEX "syn_idx" ON "seq_region_synonym" ("synonym", "seq_region_id");
833 834

--
835
-- Table: "simple_feature"
836
--
837 838 839 840 841 842 843 844 845
CREATE TABLE "simple_feature" (
  "simple_feature_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "seq_region_id" integer NOT NULL DEFAULT 0,
  "seq_region_start" integer NOT NULL DEFAULT 0,
  "seq_region_end" integer NOT NULL DEFAULT 0,
  "seq_region_strand" tinyint NOT NULL DEFAULT 0,
  "display_label" varchar(40) NOT NULL DEFAULT '',
  "analysis_id" integer NOT NULL DEFAULT 0,
  "score" double precision
846 847 848
);

--
849
-- Table: "stable_id_event"
850
--
851 852 853 854 855 856 857 858
CREATE TABLE "stable_id_event" (
  "old_stable_id" varchar(128),
  "old_version" smallint,
  "new_stable_id" varchar(128),
  "new_version" smallint,
  "mapping_session_id" integer NOT NULL DEFAULT 0,
  "type" enum NOT NULL DEFAULT 'gene',
  "score" float NOT NULL DEFAULT 0
859 860
);

861
CREATE UNIQUE INDEX "uni_idx" ON "stable_id_event" ("mapping_session_id", "old_stable_id", "old_version", "new_stable_id", "new_version", "type");
862 863

--
864
-- Table: "supporting_feature"
865
--
866 867 868 869
CREATE TABLE "supporting_feature" (
  "exon_id" integer NOT NULL DEFAULT 0,
  "feature_type" enum,
  "feature_id" integer NOT NULL DEFAULT 0
870 871
);

872
CREATE UNIQUE INDEX "all_idx02" ON "supporting_feature" ("exon_id", "feature_type", "feature_id");
873 874

--
875
-- Table: "transcript"
876
--
877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894
CREATE TABLE "transcript" (
  "transcript_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "gene_id" integer,
  "analysis_id" smallint NOT NULL,
  "seq_region_id" integer NOT NULL,
  "seq_region_start" integer NOT NULL,
  "seq_region_end" integer NOT NULL,
  "seq_region_strand" tinyint NOT NULL,
  "display_xref_id" integer,
  "source" varchar(40) NOT NULL DEFAULT 'ensembl',
  "biotype" varchar(40) NOT NULL,
  "description" text,
  "is_current" tinyint NOT NULL DEFAULT 1,
  "canonical_translation_id" integer,
  "stable_id" varchar(128),
  "version" smallint,
  "created_date" datetime,
  "modified_date" datetime
895 896
);

897
CREATE UNIQUE INDEX "canonical_translation_idx" ON "transcript" ("canonical_translation_id");
898 899

--
900
-- Table: "transcript_attrib"
901
--
902 903 904 905
CREATE TABLE "transcript_attrib" (
  "transcript_id" integer NOT NULL DEFAULT 0,
  "attrib_type_id" smallint NOT NULL DEFAULT 0,
  "value" text NOT NULL
906 907
);

908
CREATE UNIQUE INDEX "transcript_attribx" ON "transcript_attrib" ("transcript_id", "attrib_type_id", "value");
909 910

--
911
-- Table: "transcript_intron_supporting_evidence"
912
--
913 914 915 916 917 918
CREATE TABLE "transcript_intron_supporting_evidence" (
  "transcript_id" integer NOT NULL,
  "intron_supporting_evidence_id" integer NOT NULL,
  "previous_exon_id" integer NOT NULL,
  "next_exon_id" integer NOT NULL,
  PRIMARY KEY ("intron_supporting_evidence_id", "transcript_id")
919 920 921
);

--
922
-- Table: "transcript_supporting_feature"
923
--
924 925 926 927
CREATE TABLE "transcript_supporting_feature" (
  "transcript_id" integer NOT NULL DEFAULT 0,
  "feature_type" enum,
  "feature_id" integer NOT NULL DEFAULT 0
928 929
);

930
CREATE UNIQUE INDEX "all_idx03" ON "transcript_supporting_feature" ("transcript_id", "feature_type", "feature_id");
931 932

--
933
-- Table: "translation"
934
--
935 936 937 938 939 940 941 942 943 944 945
CREATE TABLE "translation" (
  "translation_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "transcript_id" integer NOT NULL,
  "seq_start" integer NOT NULL,
  "start_exon_id" integer NOT NULL,
  "seq_end" integer NOT NULL,
  "end_exon_id" integer NOT NULL,
  "stable_id" varchar(128),
  "version" smallint,
  "created_date" datetime,
  "modified_date" datetime
946 947 948
);

--
949
-- Table: "translation_attrib"