table.sql 27.4 KB
Newer Older
1 2
-- 
-- Created by SQL::Translator::Producer::SQLite
3
-- Created on Thu Dec  6 13:49:43 2018
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 145
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,
  "so_acc" varchar(64)
146 147
);

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

150
--
151
-- Table: "coord_system"
152
--
153 154 155 156 157 158 159
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
160 161
);

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

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

--
167
-- Table: "data_file"
168
--
169 170 171 172 173 174 175 176 177
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
178 179
);

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

--
183
-- Table: "density_feature"
184
--
185 186 187 188 189 190 191
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
192 193 194
);

--
195
-- Table: "density_type"
196
--
197 198 199 200 201 202
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'
203 204
);

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

--
208
-- Table: "dependent_xref"
209
--
210 211 212 213
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
214 215 216
);

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

--
228
-- Table: "ditag_feature"
229
--
230 231 232 233 234 235 236 237 238 239 240 241 242 243
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 ''
244 245 246
);

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

--
255
-- Table: "dna_align_feature"
256
--
257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274
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'
275 276
);

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

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

288
--
289
-- Table: "exon"
290
--
291 292 293 294 295 296 297 298 299 300 301 302 303 304
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
305 306 307
);

--
308
-- Table: "exon_transcript"
309
--
310 311 312 313 314
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")
315 316 317
);

--
318
-- Table: "external_db"
319
--
320 321 322 323 324 325 326 327 328 329 330
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
331 332
);

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

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

--
345
-- Table: "gene"
346
--
347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363
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
364 365 366
);

--
367
-- Table: "gene_archive"
368
--
369 370 371 372 373 374 375 376 377
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
378 379 380
);

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

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

--
392
-- Table: "genome_statistics"
393
--
394 395 396 397 398 399 400
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
401 402
);

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

--
406
-- Table: "identity_xref"
407
--
408 409 410 411 412 413 414 415 416 417 418
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
419 420 421
);

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

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

--
432
-- Table: "intron_supporting_evidence"
433
--
434 435 436 437 438 439 440 441 442 443 444
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
445 446
);

447
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");
448 449

--
450
-- Table: "karyotype"
451
--
452 453 454 455 456 457 458
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)
459 460 461
);

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

--
470
-- Table: "mapping_session"
471
--
472 473 474 475 476 477 478 479 480
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
481 482 483
);

--
484
-- Table: "mapping_set"
485
--
486 487 488 489
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
490 491
);

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

--
495
-- Table: "marker"
496
--
497 498 499 500 501 502 503 504 505
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
506 507 508
);

--
509
-- Table: "marker_feature"
510
--
511 512 513 514 515 516 517 518
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
519 520 521
);

--
522
-- Table: "marker_map_location"
523
--
524 525 526 527 528 529 530 531
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")
532 533 534
);

--
535
-- Table: "marker_synonym"
536
--
537 538 539 540 541
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)
542 543 544
);

--
545
-- Table: "meta"
546
--
547 548 549 550 551
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
552 553
);

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

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

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

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

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

--
579
-- Table: "misc_feature"
580
--
581 582 583 584 585 586
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
587 588 589
);

--
590
-- Table: "misc_feature_misc_set"
591
--
592 593 594 595
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")
596 597 598
);

--
599
-- Table: "misc_set"
600
--
601 602 603 604 605 606
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
607 608
);

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

--
612
-- Table: "object_xref"
613
--
614 615 616 617 618 619 620
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
621 622
);

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

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

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

--
637
-- Table: "operon"
638
--
639 640 641 642 643 644 645 646 647 648 649 650
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
651 652 653
);

--
654
-- Table: "operon_transcript"
655
--
656 657 658 659 660 661 662 663 664 665 666 667 668
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
669 670 671
);

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

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

--
689
-- Table: "prediction_exon"
690
--
691 692 693 694 695 696 697 698 699 700 701
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
702 703 704
);

--
705
-- Table: "prediction_transcript"
706
--
707 708 709 710 711 712 713 714
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)
715 716 717
);

--
718
-- Table: "protein_align_feature"
719
--
720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736
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'
737 738 739
);

--
740
-- Table: "protein_feature"
741
--
742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757
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
758 759
);

760
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
761

762
--
763
-- Table: "repeat_consensus"
764
--
765 766 767 768 769 770
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
771 772 773
);

--
774
-- Table: "repeat_feature"
775
--
776 777 778 779 780 781 782 783 784 785 786
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
787 788 789
);

--
790
-- Table: "seq_region"
791
--
792 793 794 795 796
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
797 798
);

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

--
802
-- Table: "seq_region_attrib"
803
--
804 805 806 807
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
808 809
);

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

--
813
-- Table: "seq_region_mapping"
814
--
815 816 817 818
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
819 820 821
);

--
822
-- Table: "seq_region_synonym"
823
--
824 825 826 827 828
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
829 830
);

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

--
834
-- Table: "simple_feature"
835
--
836 837 838 839 840 841 842 843 844
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
845 846 847
);

--
848
-- Table: "stable_id_event"
849
--
850 851 852 853 854 855 856 857
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
858 859
);

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

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

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

--
874
-- Table: "transcript"
875
--
876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893
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
894 895
);

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

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

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

--
910
-- Table: "transcript_intron_supporting_evidence"
911
--
912 913 914 915 916 917
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")
918 919 920
);

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

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

--
932
-- Table: "translation"
933
--
934 935 936 937 938 939 940 941 942 943 944
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
945 946 947
);

--
948
-- Table: "translation_attrib"
949
--
950