table.sql 28.4 KB
Newer Older
1 2
-- 
-- Created by SQL::Translator::Producer::SQLite
Marek Szuba's avatar
Marek Szuba committed
3
-- Created on Mon Jun 17 16:51:37 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
CREATE TABLE "object_xref" (
  "object_xref_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "ensembl_id" integer NOT NULL DEFAULT 0,
618
  "ensembl_object_type" enum NOT NULL,
619 620 621
  "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 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829
--
-- Table: "rnaproduct"
--
CREATE TABLE "rnaproduct" (
  "rnaproduct_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "rnaproduct_type_id" smallint NOT NULL,
  "transcript_id" integer NOT NULL,
  "seq_start" integer NOT NULL,
  "start_exon_id" integer,
  "seq_end" integer NOT NULL,
  "end_exon_id" integer,
  "stable_id" varchar(128),
  "version" smallint,
  "created_date" datetime,
  "modified_date" datetime
);

--
-- Table: "rnaproduct_attrib"
--
CREATE TABLE "rnaproduct_attrib" (
  "rnaproduct_id" integer NOT NULL DEFAULT 0,
  "attrib_type_id" smallint NOT NULL DEFAULT 0,
  "value" text NOT NULL
);

CREATE UNIQUE INDEX "rnaproduct_attribx" ON "rnaproduct_attrib" ("rnaproduct_id", "attrib_type_id", "value");

--
-- Table: "rnaproduct_type"
--
CREATE TABLE "rnaproduct_type" (
  "rnaproduct_type_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "code" varchar(20) NOT NULL DEFAULT '',
  "name" varchar(255) NOT NULL DEFAULT '',
  "description" text
);

CREATE UNIQUE INDEX "code_idx03" ON "rnaproduct_type" ("code");

830
--
831
-- Table: "seq_region"
832
--
833 834 835 836 837
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
838 839
);

840
CREATE UNIQUE INDEX "name_cs_idx" ON "seq_region" ("name", "coord_system_id");
841 842

--
843
-- Table: "seq_region_attrib"
844
--
845 846 847 848
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
849 850
);

851
CREATE UNIQUE INDEX "region_attribx" ON "seq_region_attrib" ("seq_region_id", "attrib_type_id", "value");
852 853

--
854
-- Table: "seq_region_mapping"
855
--
856 857 858 859
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
860 861 862
);

--
863
-- Table: "seq_region_synonym"
864
--
865 866 867 868 869
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
870 871
);

872
CREATE UNIQUE INDEX "syn_idx" ON "seq_region_synonym" ("synonym", "seq_region_id");
873 874

--
875
-- Table: "simple_feature"
876
--
877 878 879 880 881 882 883 884 885
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
886 887 888
);

--
889
-- Table: "stable_id_event"
890
--
891 892 893 894 895 896
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,
897
  "type" enum NOT NULL,
898
  "score" float NOT NULL DEFAULT 0
899 900
);

901
CREATE UNIQUE INDEX "uni_idx" ON "stable_id_event" ("mapping_session_id", "old_stable_id", "old_version", "new_stable_id", "new_version", "type");
902 903

--
904
-- Table: "supporting_feature"
905
--
906 907 908 909
CREATE TABLE "supporting_feature" (
  "exon_id" integer NOT NULL DEFAULT 0,
  "feature_type" enum,
  "feature_id" integer NOT NULL DEFAULT 0
910 911
);

912
CREATE UNIQUE INDEX "all_idx02" ON "supporting_feature" ("exon_id", "feature_type", "feature_id");
913 914

--
915
-- Table: "transcript"
916
--
917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934
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
935 936
);

937
CREATE UNIQUE INDEX "canonical_translation_idx" ON "transcript" ("canonical_translation_id");
938 939

--
940
-- Table: "transcript_attrib"
941
--
942 943 944 945
CREATE TABLE "transcript_attrib" (
  "transcript_id" integer NOT NULL DEFAULT 0,
  "attrib_type_id" smallint NOT NULL DEFAULT 0,
  "value" text NOT NULL
946 947
);

948
CREATE UNIQUE INDEX "transcript_attribx" ON "transcript_attrib" ("transcript_id", "attrib_type_id", "value");
949 950

--
951
-- Table: "transcript_intron_supporting_evidence"
952
--
953 954 955 956 957 958
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")
959 960 961
);

--
962
-- Table: "transcript_supporting_feature"
963
--