table.sql 39.3 KB
Newer Older
1
CREATE TABLE `alt_allele` (
2
  `alt_allele_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3
  `alt_allele_group_id` int(10) unsigned NOT NULL,
4
  `gene_id` int(10) unsigned NOT NULL,
5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
  PRIMARY KEY (`alt_allele_id`),
  KEY `gene_id` (`gene_id`,`alt_allele_group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `alt_allele_attrib` (
  `alt_allele_id` int(10) unsigned DEFAULT NULL,
  `attrib` enum('IS_REPRESENTATIVE','IS_MOST_COMMON_ALLELE','IN_CORRECTED_ASSEMBLY','HAS_CODING_POTENTIAL','IN_ARTIFICIALLY_DUPLICATED_ASSEMBLY','IN_SYNTENIC_REGION','HAS_SAME_UNDERLYING_DNA_SEQUENCE','IN_BROKEN_ASSEMBLY_REGION','IS_VALID_ALTERNATE','SAME_AS_REPRESENTATIVE','SAME_AS_ANOTHER_ALLELE','MANUALLY_ASSIGNED','AUTOMATICALLY_ASSIGNED') DEFAULT NULL,
  KEY `aa_idx` (`alt_allele_id`,`attrib`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `alt_allele_group` (
  `alt_allele_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`alt_allele_group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

20
CREATE TABLE `analysis` (
21
  `analysis_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
22
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
23 24 25 26 27 28 29 30 31 32 33 34
  `logic_name` varchar(128) NOT NULL,
  `db` varchar(120) DEFAULT NULL,
  `db_version` varchar(40) DEFAULT NULL,
  `db_file` varchar(120) DEFAULT NULL,
  `program` varchar(80) DEFAULT NULL,
  `program_version` varchar(40) DEFAULT NULL,
  `program_file` varchar(80) DEFAULT NULL,
  `parameters` text,
  `module` varchar(80) DEFAULT NULL,
  `module_version` varchar(40) DEFAULT NULL,
  `gff_source` varchar(40) DEFAULT NULL,
  `gff_feature` varchar(40) DEFAULT NULL,
35
  PRIMARY KEY (`analysis_id`),
36 37
  UNIQUE KEY `logic_name_idx` (`logic_name`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=latin1;
38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

CREATE TABLE `analysis_description` (
  `analysis_id` smallint(5) unsigned NOT NULL,
  `description` text,
  `display_label` varchar(255) NOT NULL,
  `displayable` tinyint(1) NOT NULL DEFAULT '1',
  `web_data` text,
  UNIQUE KEY `analysis_idx` (`analysis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `assembly` (
  `asm_seq_region_id` int(10) unsigned NOT NULL,
  `cmp_seq_region_id` int(10) unsigned NOT NULL,
  `asm_start` int(10) NOT NULL,
  `asm_end` int(10) NOT NULL,
  `cmp_start` int(10) NOT NULL,
  `cmp_end` int(10) NOT NULL,
  `ori` tinyint(4) NOT NULL,
  UNIQUE KEY `all_idx` (`asm_seq_region_id`,`cmp_seq_region_id`,`asm_start`,`asm_end`,`cmp_start`,`cmp_end`,`ori`),
  KEY `cmp_seq_region_idx` (`cmp_seq_region_id`),
  KEY `asm_seq_region_idx` (`asm_seq_region_id`,`asm_start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

61 62
CREATE TABLE `assembly_exception` (
  `assembly_exception_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
63 64 65 66 67 68 69 70
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `exc_type` enum('HAP','PAR','PATCH_FIX','PATCH_NOVEL') NOT NULL,
  `exc_seq_region_id` int(10) unsigned NOT NULL,
  `exc_seq_region_start` int(10) unsigned NOT NULL,
  `exc_seq_region_end` int(10) unsigned NOT NULL,
  `ori` int(11) NOT NULL,
71 72 73
  PRIMARY KEY (`assembly_exception_id`),
  KEY `sr_idx` (`seq_region_id`,`seq_region_start`),
  KEY `ex_idx` (`exc_seq_region_id`,`exc_seq_region_start`)
74
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
75

76 77 78 79
CREATE TABLE `associated_group` (
  `associated_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`associated_group_id`)
80
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
81

82 83 84 85 86 87 88 89 90 91 92 93 94 95
CREATE TABLE `associated_xref` (
  `associated_xref_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
  `xref_id` int(10) unsigned NOT NULL DEFAULT '0',
  `source_xref_id` int(10) unsigned DEFAULT NULL,
  `condition_type` varchar(128) DEFAULT NULL,
  `associated_group_id` int(10) unsigned DEFAULT NULL,
  `rank` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`associated_xref_id`),
  UNIQUE KEY `object_associated_source_type_idx` (`object_xref_id`,`xref_id`,`source_xref_id`,`condition_type`,`associated_group_id`),
  KEY `associated_source_idx` (`source_xref_id`),
  KEY `associated_object_idx` (`object_xref_id`),
  KEY `associated_idx` (`xref_id`),
  KEY `associated_group_idx` (`associated_group_id`)
96
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
97

98 99
CREATE TABLE `attrib_type` (
  `attrib_type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
100 101 102
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text,
103
  PRIMARY KEY (`attrib_type_id`),
104 105
  UNIQUE KEY `code_idx` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=14078 DEFAULT CHARSET=latin1;
106

107 108 109 110 111 112 113 114 115 116 117
CREATE TABLE `coord_system` (
  `coord_system_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `species_id` int(10) unsigned NOT NULL DEFAULT '1',
  `name` varchar(40) NOT NULL,
  `version` varchar(255) DEFAULT NULL,
  `rank` int(11) NOT NULL,
  `attrib` set('default_version','sequence_level') DEFAULT NULL,
  PRIMARY KEY (`coord_system_id`),
  UNIQUE KEY `rank_idx` (`rank`,`species_id`),
  UNIQUE KEY `name_idx` (`name`,`version`,`species_id`),
  KEY `species_idx` (`species_id`)
118
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
119

120
CREATE TABLE `data_file` (
121 122 123
  `data_file_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coord_system_id` int(10) unsigned NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
124 125 126 127 128 129 130 131 132
  `name` varchar(100) NOT NULL,
  `version_lock` tinyint(1) NOT NULL DEFAULT '0',
  `absolute` tinyint(1) NOT NULL DEFAULT '0',
  `url` text,
  `file_type` enum('BAM','BIGBED','BIGWIG','VCF') DEFAULT NULL,
  PRIMARY KEY (`data_file_id`),
  UNIQUE KEY `df_unq_idx` (`coord_system_id`,`analysis_id`,`name`,`file_type`),
  KEY `df_name_idx` (`name`),
  KEY `df_analysis_idx` (`analysis_id`)
133
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
134

135
CREATE TABLE `density_feature` (
136 137 138 139 140 141
  `density_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `density_type_id` int(10) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `density_value` float NOT NULL,
142 143 144
  PRIMARY KEY (`density_feature_id`),
  KEY `seq_region_idx` (`density_type_id`,`seq_region_id`,`seq_region_start`),
  KEY `seq_region_id_idx` (`seq_region_id`)
145
) ENGINE=InnoDB AUTO_INCREMENT=1051 DEFAULT CHARSET=latin1;
146

147
CREATE TABLE `density_type` (
148 149 150 151 152
  `density_type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `block_size` int(11) NOT NULL,
  `region_features` int(11) NOT NULL,
  `value_type` enum('sum','ratio') NOT NULL,
153
  PRIMARY KEY (`density_type_id`),
154 155
  UNIQUE KEY `analysis_idx` (`analysis_id`,`block_size`,`region_features`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
156

157 158 159 160 161 162 163
CREATE TABLE `dependent_xref` (
  `object_xref_id` int(10) unsigned NOT NULL,
  `master_xref_id` int(10) unsigned NOT NULL,
  `dependent_xref_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`object_xref_id`),
  KEY `dependent` (`dependent_xref_id`),
  KEY `master_idx` (`master_xref_id`)
164
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
165 166 167 168 169 170 171 172 173 174

CREATE TABLE `ditag` (
  `ditag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `type` varchar(30) NOT NULL,
  `tag_count` smallint(6) unsigned NOT NULL DEFAULT '1',
  `sequence` tinytext NOT NULL,
  PRIMARY KEY (`ditag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

175 176 177 178 179 180 181 182
CREATE TABLE `ditag_feature` (
  `ditag_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ditag_id` int(10) unsigned NOT NULL DEFAULT '0',
  `ditag_pair_id` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_start` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_end` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_strand` tinyint(1) NOT NULL DEFAULT '0',
183
  `analysis_id` smallint(5) unsigned NOT NULL DEFAULT '0',
184 185 186
  `hit_start` int(10) unsigned NOT NULL DEFAULT '0',
  `hit_end` int(10) unsigned NOT NULL DEFAULT '0',
  `hit_strand` tinyint(1) NOT NULL DEFAULT '0',
187 188
  `cigar_line` tinytext NOT NULL,
  `ditag_side` enum('F','L','R') NOT NULL,
189
  PRIMARY KEY (`ditag_feature_id`),
190 191 192 193
  KEY `ditag_idx` (`ditag_id`),
  KEY `ditag_pair_idx` (`ditag_pair_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
194 195 196 197 198 199 200

CREATE TABLE `dna` (
  `seq_region_id` int(10) unsigned NOT NULL,
  `sequence` longtext NOT NULL,
  PRIMARY KEY (`seq_region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=750000 AVG_ROW_LENGTH=19000;

201 202
CREATE TABLE `dna_align_feature` (
  `dna_align_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
203 204 205 206 207 208 209 210 211
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(1) NOT NULL,
  `hit_start` int(11) NOT NULL,
  `hit_end` int(11) NOT NULL,
  `hit_strand` tinyint(1) NOT NULL,
  `hit_name` varchar(40) NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
212 213 214
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
215 216
  `cigar_line` text,
  `external_db_id` int(10) unsigned DEFAULT NULL,
217
  `hcoverage` double DEFAULT NULL,
218
  `external_data` text,
219 220 221 222 223 224 225 226
  `pair_dna_align_feature_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`dna_align_feature_id`),
  KEY `seq_region_idx` (`seq_region_id`,`analysis_id`,`seq_region_start`,`score`),
  KEY `seq_region_idx_2` (`seq_region_id`,`seq_region_start`),
  KEY `hit_idx` (`hit_name`),
  KEY `analysis_idx` (`analysis_id`),
  KEY `external_db_idx` (`external_db_id`),
  KEY `pair_idx` (`pair_dna_align_feature_id`)
227
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
228

229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245
CREATE TABLE `exon` (
  `exon_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(2) NOT NULL,
  `phase` tinyint(2) NOT NULL,
  `end_phase` tinyint(2) NOT NULL,
  `is_current` tinyint(1) NOT NULL DEFAULT '1',
  `is_constitutive` tinyint(1) NOT NULL DEFAULT '0',
  `stable_id` varchar(128) DEFAULT NULL,
  `version` smallint(5) unsigned NOT NULL DEFAULT '1',
  `created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`exon_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `stable_id_idx` (`stable_id`,`version`)
246
) ENGINE=InnoDB AUTO_INCREMENT=6885 DEFAULT CHARSET=latin1;
247

248
CREATE TABLE `exon_transcript` (
249 250 251
  `exon_id` int(10) unsigned NOT NULL,
  `transcript_id` int(10) unsigned NOT NULL,
  `rank` int(10) NOT NULL,
252 253 254
  PRIMARY KEY (`exon_id`,`transcript_id`,`rank`),
  KEY `transcript` (`transcript_id`),
  KEY `exon` (`exon_id`)
255
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
256

257
CREATE TABLE `external_db` (
258 259 260 261 262 263 264 265 266 267 268 269 270
  `external_db_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `db_name` varchar(100) NOT NULL,
  `db_release` varchar(255) DEFAULT NULL,
  `status` enum('KNOWNXREF','KNOWN','XREF','PRED','ORTH','PSEUDO') NOT NULL,
  `priority` int(11) NOT NULL,
  `db_display_name` varchar(255) DEFAULT NULL,
  `type` enum('ARRAY','ALT_TRANS','MISC','LIT','PRIMARY_DB_SYNONYM','ENSEMBL','IGNORE','ALT_GENE') DEFAULT NULL,
  `secondary_db_name` varchar(255) DEFAULT NULL,
  `secondary_db_table` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`external_db_id`),
  UNIQUE KEY `db_name_idx` (`db_name`,`db_release`)
) ENGINE=InnoDB AUTO_INCREMENT=50745 DEFAULT CHARSET=latin1;
271

272
CREATE TABLE `external_synonym` (
273 274
  `xref_id` int(10) unsigned NOT NULL,
  `synonym` varchar(100) NOT NULL,
275 276
  PRIMARY KEY (`xref_id`,`synonym`),
  KEY `name_index` (`synonym`)
277
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
278

279 280 281 282 283 284 285 286 287 288
CREATE TABLE `gene` (
  `gene_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `biotype` varchar(40) NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(2) NOT NULL,
  `display_xref_id` int(10) unsigned DEFAULT NULL,
  `source` varchar(20) NOT NULL,
289
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN','ANNOTATED') DEFAULT NULL,
290 291 292 293 294 295 296 297 298 299 300 301
  `description` text,
  `is_current` tinyint(1) NOT NULL DEFAULT '1',
  `canonical_transcript_id` int(10) unsigned NOT NULL,
  `canonical_annotation` varchar(255) DEFAULT NULL,
  `stable_id` varchar(128) DEFAULT NULL,
  `version` smallint(5) unsigned NOT NULL DEFAULT '1',
  `created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`gene_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `xref_id_index` (`display_xref_id`),
  KEY `analysis_idx` (`analysis_id`),
302 303 304
  KEY `stable_id_idx` (`stable_id`,`version`),
  KEY `canonical_transcript_id_idx` (`canonical_transcript_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6885 DEFAULT CHARSET=latin1;
305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329

CREATE TABLE `gene_archive` (
  `gene_stable_id` varchar(128) NOT NULL,
  `gene_version` smallint(6) NOT NULL DEFAULT '1',
  `transcript_stable_id` varchar(128) NOT NULL,
  `transcript_version` smallint(6) NOT NULL DEFAULT '1',
  `translation_stable_id` varchar(128) DEFAULT NULL,
  `translation_version` smallint(6) NOT NULL DEFAULT '1',
  `peptide_archive_id` int(10) unsigned DEFAULT NULL,
  `mapping_session_id` int(10) unsigned NOT NULL,
  KEY `gene_idx` (`gene_stable_id`,`gene_version`),
  KEY `transcript_idx` (`transcript_stable_id`,`transcript_version`),
  KEY `translation_idx` (`translation_stable_id`,`translation_version`),
  KEY `peptide_archive_id_idx` (`peptide_archive_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `gene_attrib` (
  `gene_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `val_only_idx` (`value`(40)),
  KEY `gene_idx` (`gene_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

330
CREATE TABLE `identity_xref` (
331
  `object_xref_id` int(10) unsigned NOT NULL,
332 333 334 335 336 337
  `xref_identity` int(5) DEFAULT NULL,
  `ensembl_identity` int(5) DEFAULT NULL,
  `xref_start` int(11) DEFAULT NULL,
  `xref_end` int(11) DEFAULT NULL,
  `ensembl_start` int(11) DEFAULT NULL,
  `ensembl_end` int(11) DEFAULT NULL,
338
  `cigar_line` text,
339 340 341
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  PRIMARY KEY (`object_xref_id`)
342
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
343

344
CREATE TABLE `interpro` (
345 346 347 348 349
  `interpro_ac` varchar(40) NOT NULL,
  `id` varchar(40) NOT NULL,
  UNIQUE KEY `accession_idx` (`interpro_ac`,`id`),
  KEY `id_idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
350

351 352 353 354 355 356 357 358 359 360 361 362
CREATE TABLE `intron_supporting_evidence` (
  `intron_supporting_evidence_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(2) NOT NULL,
  `hit_name` varchar(100) NOT NULL,
  `score` decimal(10,3) DEFAULT NULL,
  `score_type` enum('NONE','DEPTH') DEFAULT 'NONE',
  `is_splice_canonical` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`intron_supporting_evidence_id`),
363 364 365
  UNIQUE KEY `analysis_id` (`analysis_id`,`seq_region_id`,`seq_region_start`,`seq_region_end`,`seq_region_strand`,`hit_name`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
366

367 368
CREATE TABLE `karyotype` (
  `karyotype_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
369 370 371 372 373
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `band` varchar(40) NOT NULL,
  `stain` varchar(40) NOT NULL,
374 375
  PRIMARY KEY (`karyotype_id`),
  KEY `region_band_idx` (`seq_region_id`,`band`)
376
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
377 378 379 380 381 382 383

CREATE TABLE `map` (
  `map_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `map_name` varchar(30) NOT NULL,
  PRIMARY KEY (`map_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

384
CREATE TABLE `mapping_session` (
385 386 387 388 389 390 391
  `mapping_session_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `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 '',
392
  `created` datetime NOT NULL,
393
  `species_id` int(10) NOT NULL,
394
  PRIMARY KEY (`mapping_session_id`)
395
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
396

397 398
CREATE TABLE `mapping_set` (
  `mapping_set_id` int(10) unsigned NOT NULL,
399 400
  `internal_schema_build` varchar(20) NOT NULL,
  `external_schema_build` varchar(20) NOT NULL,
Andy Yates's avatar
Andy Yates committed
401
  PRIMARY KEY (`mapping_set_id`),
402
  UNIQUE KEY `mapping_idx` (`internal_schema_build`,`external_schema_build`)
403
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
404 405 406 407 408 409 410 411 412 413 414 415 416

CREATE TABLE `marker` (
  `marker_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `display_marker_synonym_id` int(10) unsigned DEFAULT NULL,
  `left_primer` varchar(100) NOT NULL,
  `right_primer` varchar(100) NOT NULL,
  `min_primer_dist` int(10) unsigned NOT NULL,
  `max_primer_dist` int(10) unsigned NOT NULL,
  `priority` int(11) DEFAULT NULL,
  `type` enum('est','microsatellite') DEFAULT NULL,
  PRIMARY KEY (`marker_id`),
  KEY `marker_idx` (`marker_id`,`priority`),
  KEY `display_idx` (`display_marker_synonym_id`)
417
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
418

419 420
CREATE TABLE `marker_feature` (
  `marker_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
421 422 423 424 425
  `marker_id` int(10) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
426 427 428 429
  `map_weight` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`marker_feature_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `analysis_idx` (`analysis_id`)
430
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
431

432
CREATE TABLE `marker_map_location` (
433 434 435 436 437
  `marker_id` int(10) unsigned NOT NULL,
  `map_id` int(10) unsigned NOT NULL,
  `chromosome_name` varchar(15) NOT NULL,
  `marker_synonym_id` int(10) unsigned NOT NULL,
  `position` varchar(15) NOT NULL,
438 439 440
  `lod_score` double DEFAULT NULL,
  PRIMARY KEY (`marker_id`,`map_id`),
  KEY `map_idx` (`map_id`,`chromosome_name`,`position`)
441
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
442

443 444
CREATE TABLE `marker_synonym` (
  `marker_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
445 446 447
  `marker_id` int(10) unsigned NOT NULL,
  `source` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
448 449 450
  PRIMARY KEY (`marker_synonym_id`),
  KEY `marker_synonym_idx` (`marker_synonym_id`,`name`),
  KEY `marker_idx` (`marker_id`)
451
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
452

453 454 455 456
CREATE TABLE `meta` (
  `meta_id` int(11) NOT NULL AUTO_INCREMENT,
  `species_id` int(10) unsigned DEFAULT '1',
  `meta_key` varchar(40) NOT NULL,
457
  `meta_value` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
458 459 460
  PRIMARY KEY (`meta_id`),
  UNIQUE KEY `species_key_value_idx` (`species_id`,`meta_key`,`meta_value`),
  KEY `species_value_idx` (`species_id`,`meta_value`)
461 462 463 464 465 466 467 468 469
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=latin1;

CREATE TABLE `meta_coord` (
  `table_name` varchar(40) NOT NULL,
  `coord_system_id` int(10) unsigned NOT NULL,
  `max_length` int(11) DEFAULT NULL,
  UNIQUE KEY `cs_table_name_idx` (`coord_system_id`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

470 471 472
CREATE TABLE `misc_attrib` (
  `misc_feature_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
473 474 475
  `value` text NOT NULL,
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `val_only_idx` (`value`(40)),
476
  KEY `misc_feature_idx` (`misc_feature_id`)
477
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
478

479 480 481 482 483 484 485 486
CREATE TABLE `misc_feature` (
  `misc_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_start` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_end` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_strand` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`misc_feature_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`)
487
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
488 489 490 491 492 493 494 495

CREATE TABLE `misc_feature_misc_set` (
  `misc_feature_id` int(10) unsigned NOT NULL DEFAULT '0',
  `misc_set_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`misc_feature_id`,`misc_set_id`),
  KEY `reverse_idx` (`misc_set_id`,`misc_feature_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

496 497
CREATE TABLE `misc_set` (
  `misc_set_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
498 499 500 501
  `code` varchar(25) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `max_length` int(10) unsigned NOT NULL,
502
  PRIMARY KEY (`misc_set_id`),
503 504
  UNIQUE KEY `code_idx` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
505

506
CREATE TABLE `object_xref` (
507 508
  `object_xref_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ensembl_id` int(10) unsigned NOT NULL,
509
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation','Operon','OperonTranscript','Marker') NOT NULL,
510
  `xref_id` int(10) unsigned NOT NULL,
511 512 513 514 515 516 517
  `linkage_annotation` varchar(255) DEFAULT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`object_xref_id`),
  UNIQUE KEY `xref_idx` (`xref_id`,`ensembl_object_type`,`ensembl_id`,`analysis_id`),
  KEY `ensembl_idx` (`ensembl_object_type`,`ensembl_id`),
  KEY `analysis_idx` (`analysis_id`)
) ENGINE=InnoDB AUTO_INCREMENT=81424 DEFAULT CHARSET=latin1;
518

519 520 521
CREATE TABLE `ontology_xref` (
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
  `source_xref_id` int(10) unsigned DEFAULT NULL,
522 523 524 525 526
  `linkage_type` varchar(3) DEFAULT NULL,
  UNIQUE KEY `object_source_type_idx` (`object_xref_id`,`source_xref_id`,`linkage_type`),
  KEY `source_idx` (`source_xref_id`),
  KEY `object_idx` (`object_xref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
527

528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543
CREATE TABLE `operon` (
  `operon_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(2) NOT NULL,
  `display_label` varchar(255) DEFAULT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `stable_id` varchar(128) DEFAULT NULL,
  `version` smallint(5) unsigned NOT NULL DEFAULT '1',
  `created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`operon_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `name_idx` (`display_label`),
  KEY `stable_id_idx` (`stable_id`,`version`)
544
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
545

546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562
CREATE TABLE `operon_transcript` (
  `operon_transcript_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(2) NOT NULL,
  `operon_id` int(10) unsigned NOT NULL,
  `display_label` varchar(255) DEFAULT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `stable_id` varchar(128) DEFAULT NULL,
  `version` smallint(5) unsigned NOT NULL DEFAULT '1',
  `created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`operon_transcript_id`),
  KEY `operon_idx` (`operon_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `stable_id_idx` (`stable_id`,`version`)
563
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
564 565 566 567 568 569 570

CREATE TABLE `operon_transcript_gene` (
  `operon_transcript_id` int(10) unsigned DEFAULT NULL,
  `gene_id` int(10) unsigned DEFAULT NULL,
  KEY `operon_transcript_gene_idx` (`operon_transcript_id`,`gene_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

571
CREATE TABLE `peptide_archive` (
572 573 574
  `peptide_archive_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `md5_checksum` varchar(32) DEFAULT NULL,
  `peptide_seq` mediumtext NOT NULL,
575 576
  PRIMARY KEY (`peptide_archive_id`),
  KEY `checksum` (`md5_checksum`)
577
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
578

579 580
CREATE TABLE `prediction_exon` (
  `prediction_exon_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
581 582 583 584 585 586 587
  `prediction_transcript_id` int(10) unsigned NOT NULL,
  `exon_rank` smallint(5) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(4) NOT NULL,
  `start_phase` tinyint(4) NOT NULL,
588 589 590
  `score` double DEFAULT NULL,
  `p_value` double DEFAULT NULL,
  PRIMARY KEY (`prediction_exon_id`),
591 592 593
  KEY `transcript_idx` (`prediction_transcript_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
594

595 596
CREATE TABLE `prediction_transcript` (
  `prediction_transcript_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
597 598 599 600 601 602
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(4) NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `display_label` varchar(255) DEFAULT NULL,
603
  PRIMARY KEY (`prediction_transcript_id`),
604
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
605
  KEY `analysis_idx` (`analysis_id`)
606
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
607

608 609
CREATE TABLE `protein_align_feature` (
  `protein_align_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
610 611 612
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
613
  `seq_region_strand` tinyint(1) NOT NULL DEFAULT '1',
614 615 616 617
  `hit_start` int(10) NOT NULL,
  `hit_end` int(10) NOT NULL,
  `hit_name` varchar(40) NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
618 619 620
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
621 622
  `cigar_line` text,
  `external_db_id` int(10) unsigned DEFAULT NULL,
623 624 625 626 627 628 629
  `hcoverage` double DEFAULT NULL,
  PRIMARY KEY (`protein_align_feature_id`),
  KEY `seq_region_idx` (`seq_region_id`,`analysis_id`,`seq_region_start`,`score`),
  KEY `seq_region_idx_2` (`seq_region_id`,`seq_region_start`),
  KEY `hit_idx` (`hit_name`),
  KEY `analysis_idx` (`analysis_id`),
  KEY `external_db_idx` (`external_db_id`)
630
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
631

632 633
CREATE TABLE `protein_feature` (
  `protein_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
634 635 636 637 638 639 640 641
  `translation_id` int(10) unsigned NOT NULL,
  `seq_start` int(10) NOT NULL,
  `seq_end` int(10) NOT NULL,
  `hit_start` int(10) NOT NULL,
  `hit_end` int(10) NOT NULL,
  `hit_name` varchar(40) NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `score` double DEFAULT NULL,
642 643
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
644 645
  `external_data` text,
  `hit_description` text,
646
  PRIMARY KEY (`protein_feature_id`),
647 648
  KEY `translation_idx` (`translation_id`),
  KEY `hitname_idx` (`hit_name`),
649
  KEY `analysis_idx` (`analysis_id`)
650
) ENGINE=InnoDB AUTO_INCREMENT=24117 DEFAULT CHARSET=latin1;
651

652 653
CREATE TABLE `qtl` (
  `qtl_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
654
  `trait` varchar(255) NOT NULL,
655
  `lod_score` float DEFAULT NULL,
656 657 658
  `flank_marker_id_1` int(10) unsigned DEFAULT NULL,
  `flank_marker_id_2` int(10) unsigned DEFAULT NULL,
  `peak_marker_id` int(10) unsigned DEFAULT NULL,
659 660
  PRIMARY KEY (`qtl_id`),
  KEY `trait_idx` (`trait`)
661
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
662 663 664 665 666 667 668 669 670 671 672 673

CREATE TABLE `qtl_feature` (
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `qtl_id` int(10) unsigned NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  KEY `qtl_idx` (`qtl_id`),
  KEY `loc_idx` (`seq_region_id`,`seq_region_start`),
  KEY `analysis_idx` (`analysis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

674 675
CREATE TABLE `qtl_synonym` (
  `qtl_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
676 677 678
  `qtl_id` int(10) unsigned NOT NULL,
  `source_database` enum('rat genome database','ratmap') NOT NULL,
  `source_primary_id` varchar(255) NOT NULL,
679 680
  PRIMARY KEY (`qtl_synonym_id`),
  KEY `qtl_idx` (`qtl_id`)
681
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
682

683 684
CREATE TABLE `repeat_consensus` (
  `repeat_consensus_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
685 686 687 688
  `repeat_name` varchar(255) NOT NULL,
  `repeat_class` varchar(100) NOT NULL,
  `repeat_type` varchar(40) NOT NULL,
  `repeat_consensus` text,
689 690 691 692 693
  PRIMARY KEY (`repeat_consensus_id`),
  KEY `name` (`repeat_name`),
  KEY `class` (`repeat_class`),
  KEY `consensus` (`repeat_consensus`(10)),
  KEY `type` (`repeat_type`)
694
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
695

696 697
CREATE TABLE `repeat_feature` (
  `repeat_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
698 699 700
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
701
  `seq_region_strand` tinyint(1) NOT NULL DEFAULT '1',
702 703 704 705
  `repeat_start` int(10) NOT NULL,
  `repeat_end` int(10) NOT NULL,
  `repeat_consensus_id` int(10) unsigned NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
706 707 708 709 710
  `score` double DEFAULT NULL,
  PRIMARY KEY (`repeat_feature_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `repeat_idx` (`repeat_consensus_id`),
  KEY `analysis_idx` (`analysis_id`)
711
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
712 713 714 715 716 717 718 719 720 721 722

CREATE TABLE `seq_region` (
  `seq_region_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `coord_system_id` int(10) unsigned NOT NULL,
  `length` int(10) unsigned NOT NULL,
  PRIMARY KEY (`seq_region_id`),
  UNIQUE KEY `name_cs_idx` (`name`,`coord_system_id`),
  KEY `cs_idx` (`coord_system_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

723 724 725
CREATE TABLE `seq_region_attrib` (
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
726 727 728
  `value` text NOT NULL,
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `val_only_idx` (`value`(40)),
729
  KEY `seq_region_idx` (`seq_region_id`)
730
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
731

732 733 734 735
CREATE TABLE `seq_region_mapping` (
  `external_seq_region_id` int(10) unsigned NOT NULL,
  `internal_seq_region_id` int(10) unsigned NOT NULL,
  `mapping_set_id` int(10) unsigned NOT NULL,
736 737
  KEY `mapping_set_idx` (`mapping_set_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
738

739 740 741 742
CREATE TABLE `seq_region_synonym` (
  `seq_region_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
  `synonym` varchar(40) NOT NULL,
743
  `external_db_id` int(10) unsigned DEFAULT NULL,
744
  PRIMARY KEY (`seq_region_synonym_id`),
745 746 747
  UNIQUE KEY `syn_idx` (`synonym`),
  KEY `seq_region_idx` (`seq_region_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
748

749 750
CREATE TABLE `simple_feature` (
  `simple_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
751 752 753 754 755 756
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(1) NOT NULL,
  `display_label` varchar(255) NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
757 758 759 760 761
  `score` double DEFAULT NULL,
  PRIMARY KEY (`simple_feature_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `analysis_idx` (`analysis_id`),
  KEY `hit_idx` (`display_label`)
762
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;
763 764 765 766 767 768 769 770 771 772 773 774 775 776 777

CREATE TABLE `splicing_event` (
  `splicing_event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(134) DEFAULT NULL,
  `gene_id` int(10) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(2) NOT NULL,
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`splicing_event_id`),
  KEY `gene_idx` (`gene_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

778 779 780 781 782 783 784 785 786 787 788 789 790 791
CREATE TABLE `splicing_event_feature` (
  `splicing_event_feature_id` int(10) unsigned NOT NULL,
  `splicing_event_id` int(10) unsigned NOT NULL,
  `exon_id` int(10) unsigned NOT NULL,
  `transcript_id` int(10) unsigned NOT NULL,
  `feature_order` int(10) unsigned NOT NULL,
  `transcript_association` int(10) unsigned NOT NULL,
  `type` enum('constitutive_exon','exon','flanking_exon') DEFAULT NULL,
  `start` int(10) unsigned NOT NULL,
  `end` int(10) unsigned NOT NULL,
  PRIMARY KEY (`splicing_event_feature_id`,`exon_id`,`transcript_id`),
  KEY `se_idx` (`splicing_event_id`),
  KEY `transcript_idx` (`transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
792

793 794 795 796 797 798 799 800
CREATE TABLE `splicing_transcript_pair` (
  `splicing_transcript_pair_id` int(10) unsigned NOT NULL,
  `splicing_event_id` int(10) unsigned NOT NULL,
  `transcript_id_1` int(10) unsigned NOT NULL,
  `transcript_id_2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`splicing_transcript_pair_id`),
  KEY `se_idx` (`splicing_event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
801

802
CREATE TABLE `stable_id_event` (
803
  `old_stable_id` varchar(128) DEFAULT NULL,
804
  `old_version` smallint(6) DEFAULT NULL,
805
  `new_stable_id` varchar(128) DEFAULT NULL,
806
  `new_version` smallint(6) DEFAULT NULL,
807 808
  `mapping_session_id` int(10) unsigned NOT NULL DEFAULT '0',
  `type` enum('gene','transcript','translation') NOT NULL,
809
  `score` float NOT NULL DEFAULT '0',
810
  UNIQUE KEY `uni_idx` (`mapping_session_id`,`old_stable_id`,`new_stable_id`,`type`),
811 812
  KEY `new_idx` (`new_stable_id`),
  KEY `old_idx` (`old_stable_id`)
813
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
814

815
CREATE TABLE `supporting_feature` (
816 817 818
  `exon_id` int(10) unsigned NOT NULL DEFAULT '0',
  `feature_type` enum('dna_align_feature','protein_align_feature') DEFAULT NULL,
  `feature_id` int(10) unsigned NOT NULL DEFAULT '0',
819 820
  UNIQUE KEY `all_idx` (`exon_id`,`feature_type`,`feature_id`),
  KEY `feature_idx` (`feature_type`,`feature_id`)
821
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
822

823 824 825 826 827 828 829 830 831 832
CREATE TABLE `transcript` (
  `transcript_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `gene_id` int(10) unsigned DEFAULT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(10) unsigned NOT NULL,
  `seq_region_end` int(10) unsigned NOT NULL,
  `seq_region_strand` tinyint(2) NOT NULL,
  `display_xref_id` int(10) unsigned DEFAULT NULL,
  `biotype` varchar(40) NOT NULL,
833
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN','ANNOTATED') DEFAULT NULL,
834 835 836 837 838 839 840 841 842 843 844 845 846 847
  `description` text,
  `is_current` tinyint(1) NOT NULL DEFAULT '1',
  `canonical_translation_id` int(10) unsigned DEFAULT NULL,
  `stable_id` varchar(128) DEFAULT NULL,
  `version` smallint(5) unsigned NOT NULL DEFAULT '1',
  `created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`transcript_id`),
  UNIQUE KEY `canonical_translation_idx` (`canonical_translation_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `gene_index` (`gene_id`),
  KEY `xref_id_index` (`display_xref_id`),
  KEY `analysis_idx` (`analysis_id`),
  KEY `stable_id_idx` (`stable_id`,`version`)
848
) ENGINE=InnoDB AUTO_INCREMENT=6885 DEFAULT CHARSET=latin1;
849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866

CREATE TABLE `transcript_attrib` (
  `transcript_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `val_only_idx` (`value`(40)),
  KEY `transcript_idx` (`transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `transcript_intron_supporting_evidence` (
  `transcript_id` int(10) unsigned NOT NULL,
  `intron_supporting_evidence_id` int(10) unsigned NOT NULL,
  `previous_exon_id` int(10) unsigned NOT NULL,
  `next_exon_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`intron_supporting_evidence_id`,`transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

867
CREATE TABLE `transcript_supporting_feature` (
868 869 870
  `transcript_id` int(10) unsigned NOT NULL DEFAULT '0',
  `feature_type` enum('dna_align_feature','protein_align_feature') DEFAULT NULL,
  `feature_id` int(10) unsigned NOT NULL DEFAULT '0',
871 872
  UNIQUE KEY `all_idx` (`transcript_id`,`feature_type`,`feature_id`),
  KEY `feature_idx` (`feature_type`,`feature_id`)
873
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
874

875 876 877 878 879 880 881 882 883 884 885 886 887 888
CREATE TABLE `translation` (
  `translation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `transcript_id` int(10) unsigned NOT NULL,
  `seq_start` int(10) NOT NULL,
  `start_exon_id` int(10) unsigned NOT NULL,
  `seq_end` int(10) NOT NULL,
  `end_exon_id` int(10) unsigned NOT NULL,
  `stable_id` varchar(128) DEFAULT NULL,
  `version` smallint(5) unsigned NOT NULL DEFAULT '1',
  `created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`translation_id`),
  KEY `transcript_idx` (`transcript_id`),
  KEY `stable_id_idx` (`stable_id`,`version`)
889
) ENGINE=InnoDB AUTO_INCREMENT=6690 DEFAULT CHARSET=latin1;
890 891 892 893 894 895 896 897 898 899

CREATE TABLE `translation_attrib` (
  `translation_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `val_only_idx` (`value`(40)),
  KEY `translation_idx` (`translation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

900 901 902 903
CREATE TABLE `unconventional_transcript_association` (
  `transcript_id` int(10) unsigned NOT NULL,
  `gene_id` int(10) unsigned NOT NULL,
  `interaction_type` enum('antisense','sense_intronic','sense_overlaping_exonic','chimeric_sense_exonic') DEFAULT NULL,
904 905 906
  KEY `transcript_idx` (`transcript_id`),
  KEY `gene_idx` (`gene_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
907

908 909
CREATE TABLE `unmapped_object` (
  `unmapped_object_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
910 911 912 913
  `type` enum('xref','cDNA','Marker') NOT NULL,
  `analysis_id` smallint(5) unsigned NOT NULL,
  `external_db_id` int(10) unsigned DEFAULT NULL,
  `identifier` varchar(255) NOT NULL,
914 915 916 917
  `unmapped_reason_id` smallint(5) unsigned NOT NULL,
  `query_score` double DEFAULT NULL,
  `target_score` double DEFAULT NULL,
  `ensembl_id` int(10) unsigned DEFAULT '0',
918 919
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation') DEFAULT 'RawContig',
  `parent` varchar(255) DEFAULT NULL,
920
  PRIMARY KEY (`unmapped_object_id`),
921 922 923 924 925
  UNIQUE KEY `unique_unmapped_obj_idx` (`ensembl_id`,`ensembl_object_type`,`identifier`,`unmapped_reason_id`,`parent`,`external_db_id`),
  KEY `id_idx` (`identifier`(50)),
  KEY `anal_exdb_idx` (`analysis_id`,`external_db_id`),
  KEY `ext_db_identifier_idx` (`external_db_id`,`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
926

927 928
CREATE TABLE `unmapped_reason` (
  `unmapped_reason_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
929 930
  `summary_description` varchar(255) DEFAULT NULL,
  `full_description` varchar(255) DEFAULT NULL,
931
  PRIMARY KEY (`unmapped_reason_id`)
932
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
933