table.sql 38.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
  PRIMARY KEY (`alt_allele_id`),
6
  UNIQUE KEY `gene_idx` (`gene_id`),
7
  KEY `gene_id` (`gene_id`,`alt_allele_group_id`)
8
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
9 10 11 12 13

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`)
14
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
15 16 17 18

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

21
CREATE TABLE `analysis` (
22
  `analysis_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
23
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
24
  `logic_name` varchar(40) NOT NULL DEFAULT '',
25 26 27 28 29 30
  `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,
31
  `parameters` text COLLATE latin1_bin,
32 33 34 35
  `module` varchar(80) DEFAULT NULL,
  `module_version` varchar(40) DEFAULT NULL,
  `gff_source` varchar(40) DEFAULT NULL,
  `gff_feature` varchar(40) DEFAULT NULL,
36
  PRIMARY KEY (`analysis_id`),
37 38 39
  UNIQUE KEY `logic_name` (`logic_name`),
  KEY `logic_name_idx` (`logic_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
40 41

CREATE TABLE `analysis_description` (
42 43 44
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `description` text COLLATE latin1_bin,
  `display_label` varchar(255) DEFAULT NULL,
45
  `displayable` tinyint(1) NOT NULL DEFAULT '1',
46 47 48
  `web_data` text COLLATE latin1_bin,
  KEY `analysis_idx` (`analysis_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
49 50

CREATE TABLE `assembly` (
51 52 53 54 55 56 57
  `asm_seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `cmp_seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `asm_start` int(10) NOT NULL DEFAULT '0',
  `asm_end` int(10) NOT NULL DEFAULT '0',
  `cmp_start` int(10) NOT NULL DEFAULT '0',
  `cmp_end` int(10) NOT NULL DEFAULT '0',
  `ori` tinyint(4) NOT NULL DEFAULT '0',
58
  UNIQUE KEY `all_idx` (`asm_seq_region_id`,`cmp_seq_region_id`,`asm_start`,`asm_end`,`cmp_start`,`cmp_end`,`ori`),
59 60 61
  KEY `cmp_seq_region_id` (`cmp_seq_region_id`),
  KEY `asm_seq_region_id` (`asm_seq_region_id`,`asm_start`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
62

63 64
CREATE TABLE `assembly_exception` (
  `assembly_exception_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
65 66 67 68 69 70 71 72
  `seq_region_id` int(11) NOT NULL DEFAULT '0',
  `seq_region_start` int(11) NOT NULL DEFAULT '0',
  `seq_region_end` int(11) NOT NULL DEFAULT '0',
  `exc_type` enum('HAP','PAR','PATCH_NOVEL','PATCH_FIX') NOT NULL DEFAULT 'HAP',
  `exc_seq_region_id` int(11) NOT NULL DEFAULT '0',
  `exc_seq_region_start` int(11) NOT NULL DEFAULT '0',
  `exc_seq_region_end` int(11) NOT NULL DEFAULT '0',
  `ori` int(11) NOT NULL DEFAULT '0',
73 74 75
  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`)
76
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
77

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

84 85 86 87 88 89 90 91 92 93 94 95 96 97
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`)
98
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
99

100 101
CREATE TABLE `attrib_type` (
  `attrib_type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
102
  `code` varchar(20) NOT NULL DEFAULT '',
103
  `name` varchar(255) NOT NULL DEFAULT '',
104
  `description` text COLLATE latin1_bin,
105
  PRIMARY KEY (`attrib_type_id`),
106 107
  UNIQUE KEY `c` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
108

109 110 111 112 113 114 115 116 117 118 119
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`)
120
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
121

122
CREATE TABLE `data_file` (
123 124 125
  `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,
126 127 128 129
  `name` varchar(100) NOT NULL,
  `version_lock` tinyint(1) NOT NULL DEFAULT '0',
  `absolute` tinyint(1) NOT NULL DEFAULT '0',
  `url` text,
130
  `file_type` enum('BAM','BAMCOV','BIGBED','BIGWIG','VCF') DEFAULT NULL,
131 132 133 134
  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`)
135
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
136

137
CREATE TABLE `density_feature` (
138 139 140 141 142 143
  `density_feature_id` int(11) NOT NULL AUTO_INCREMENT,
  `density_type_id` int(11) NOT NULL DEFAULT '0',
  `seq_region_id` int(11) NOT NULL DEFAULT '0',
  `seq_region_start` int(11) NOT NULL DEFAULT '0',
  `seq_region_end` int(11) NOT NULL DEFAULT '0',
  `density_value` float NOT NULL DEFAULT '0',
144 145 146
  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`)
147
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
148

149
CREATE TABLE `density_type` (
150 151 152 153 154
  `density_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `analysis_id` int(11) NOT NULL DEFAULT '0',
  `block_size` int(11) NOT NULL DEFAULT '0',
  `region_features` int(11) NOT NULL DEFAULT '0',
  `value_type` enum('sum','ratio') NOT NULL DEFAULT 'sum',
155
  PRIMARY KEY (`density_type_id`),
156 157
  UNIQUE KEY `analysis_id` (`analysis_id`,`block_size`,`region_features`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
158

159 160 161 162 163 164 165
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`)
166
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
167 168

CREATE TABLE `ditag` (
169 170 171 172 173
  `ditag_id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `type` varchar(30) DEFAULT NULL,
  `tag_count` smallint(6) DEFAULT '1',
  `sequence` text,
174
  PRIMARY KEY (`ditag_id`)
175
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
176

177 178 179 180 181 182 183 184
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',
185
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
186 187 188
  `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',
189 190
  `cigar_line` text,
  `ditag_side` char(1) DEFAULT '',
191
  PRIMARY KEY (`ditag_feature_id`),
192 193 194
  KEY `ditag_id` (`ditag_id`),
  KEY `ditag_pair_id` (`ditag_pair_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
195 196

CREATE TABLE `dna` (
197 198
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sequence` mediumtext NOT NULL,
199
  PRIMARY KEY (`seq_region_id`)
200
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=750000 AVG_ROW_LENGTH=19000;
201

202 203
CREATE TABLE `dna_align_feature` (
  `dna_align_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
204 205 206 207 208 209 210 211 212
  `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',
  `hit_start` int(11) NOT NULL DEFAULT '0',
  `hit_end` int(11) NOT NULL DEFAULT '0',
  `hit_strand` tinyint(1) NOT NULL DEFAULT '0',
  `hit_name` varchar(40) NOT NULL DEFAULT '',
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
213 214 215
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
216 217
  `cigar_line` text COLLATE latin1_bin,
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
218
  `hcoverage` double DEFAULT NULL,
219
  `external_data` text COLLATE latin1_bin,
220 221 222 223 224
  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`),
225
  KEY `external_db_idx` (`external_db_id`)
226
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
227

228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244
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`)
245
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
246

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

256
CREATE TABLE `external_db` (
257 258 259 260 261
  `external_db_id` int(11) NOT NULL DEFAULT '0',
  `db_name` varchar(27) NOT NULL DEFAULT '',
  `db_release` varchar(40) NOT NULL DEFAULT '',
  `status` enum('KNOWNXREF','KNOWN','XREF','PRED','ORTH','PSEUDO') NOT NULL DEFAULT 'KNOWNXREF',
  `priority` int(11) NOT NULL DEFAULT '0',
262
  `db_display_name` varchar(255) DEFAULT NULL,
263
  `type` enum('ARRAY','ALT_TRANS','ALT_GENE','MISC','LIT','PRIMARY_DB_SYNONYM','ENSEMBL') DEFAULT NULL,
264 265
  `secondary_db_name` varchar(255) DEFAULT NULL,
  `secondary_db_table` varchar(255) DEFAULT NULL,
266 267 268
  `description` text COLLATE latin1_bin,
  PRIMARY KEY (`external_db_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
269

270
CREATE TABLE `external_synonym` (
271 272
  `xref_id` int(10) unsigned NOT NULL DEFAULT '0',
  `synonym` varchar(40) NOT NULL DEFAULT '',
273 274
  PRIMARY KEY (`xref_id`,`synonym`),
  KEY `name_index` (`synonym`)
275
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
276

277 278 279 280 281 282 283 284 285
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,
286
  `source` varchar(40) NOT NULL,
287
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN') DEFAULT NULL,
288 289 290 291 292 293 294 295 296 297 298
  `description` text,
  `is_current` tinyint(1) NOT NULL DEFAULT '1',
  `canonical_transcript_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 (`gene_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `xref_id_index` (`display_xref_id`),
  KEY `analysis_idx` (`analysis_id`),
299 300
  KEY `stable_id_idx` (`stable_id`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
301 302

CREATE TABLE `gene_archive` (
303 304 305 306 307 308 309 310
  `gene_stable_id` varchar(128) NOT NULL DEFAULT '',
  `gene_version` smallint(6) NOT NULL DEFAULT '0',
  `transcript_stable_id` varchar(128) NOT NULL DEFAULT '',
  `transcript_version` smallint(6) NOT NULL DEFAULT '0',
  `translation_stable_id` varchar(128) NOT NULL DEFAULT '',
  `translation_version` smallint(6) NOT NULL DEFAULT '0',
  `peptide_archive_id` int(11) NOT NULL DEFAULT '0',
  `mapping_session_id` int(11) NOT NULL DEFAULT '0',
311 312
  KEY `gene_idx` (`gene_stable_id`,`gene_version`),
  KEY `transcript_idx` (`transcript_stable_id`,`transcript_version`),
313 314
  KEY `translation_idx` (`translation_stable_id`,`translation_version`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
315 316 317 318 319

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,
320
  UNIQUE KEY `gene_attribx` (`gene_id`,`attrib_type_id`,`value`(500)),
321 322
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `gene_idx` (`gene_id`)
323
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
324 325 326 327

CREATE TABLE `genome_statistics` (
  `genome_statistics_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `statistic` varchar(128) NOT NULL,
328
  `value` bigint(11) unsigned NOT NULL DEFAULT '0',
329 330 331 332 333 334 335
  `species_id` int(10) unsigned DEFAULT '1',
  `attrib_type_id` int(10) unsigned DEFAULT NULL,
  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`genome_statistics_id`),
  UNIQUE KEY `stats_uniq` (`statistic`,`attrib_type_id`,`species_id`),
  KEY `stats_idx` (`statistic`,`attrib_type_id`,`species_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
336

337
CREATE TABLE `identity_xref` (
338
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
339 340 341 342 343 344
  `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,
345
  `cigar_line` text COLLATE latin1_bin,
346 347 348
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  PRIMARY KEY (`object_xref_id`)
349
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
350

351
CREATE TABLE `interpro` (
352 353 354 355 356
  `interpro_ac` varchar(40) NOT NULL DEFAULT '',
  `id` varchar(40) NOT NULL DEFAULT '',
  UNIQUE KEY `interpro_ac` (`interpro_ac`,`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
357

358 359 360 361 362 363 364 365 366 367 368 369
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`),
370 371
  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`)
372
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
373

374 375
CREATE TABLE `karyotype` (
  `karyotype_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
376 377 378
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `seq_region_start` int(10) NOT NULL DEFAULT '0',
  `seq_region_end` int(10) NOT NULL DEFAULT '0',
379 380
  `band` varchar(40) DEFAULT NULL,
  `stain` varchar(40) DEFAULT NULL,
381 382
  PRIMARY KEY (`karyotype_id`),
  KEY `region_band_idx` (`seq_region_id`,`band`)
383
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
384 385 386

CREATE TABLE `map` (
  `map_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
387
  `map_name` varchar(30) NOT NULL DEFAULT '',
388
  PRIMARY KEY (`map_id`)
389
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
390

391
CREATE TABLE `mapping_session` (
392
  `mapping_session_id` int(11) NOT NULL AUTO_INCREMENT,
393 394 395 396 397 398
  `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 '',
399 400
  `created` datetime NOT NULL,
  PRIMARY KEY (`mapping_session_id`)
401
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
402

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

CREATE TABLE `marker` (
  `marker_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `display_marker_synonym_id` int(10) unsigned DEFAULT NULL,
414 415 416 417
  `left_primer` varchar(100) NOT NULL DEFAULT '',
  `right_primer` varchar(100) NOT NULL DEFAULT '',
  `min_primer_dist` int(10) unsigned NOT NULL DEFAULT '0',
  `max_primer_dist` int(10) unsigned NOT NULL DEFAULT '0',
418 419 420
  `priority` int(11) DEFAULT NULL,
  `type` enum('est','microsatellite') DEFAULT NULL,
  PRIMARY KEY (`marker_id`),
421 422
  KEY `marker_idx` (`marker_id`,`priority`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
423

424 425
CREATE TABLE `marker_feature` (
  `marker_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
426 427 428 429 430
  `marker_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',
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
431 432 433 434
  `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`)
435
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
436

437
CREATE TABLE `marker_map_location` (
438 439 440 441 442
  `marker_id` int(10) unsigned NOT NULL DEFAULT '0',
  `map_id` int(10) unsigned NOT NULL DEFAULT '0',
  `chromosome_name` varchar(15) NOT NULL DEFAULT '',
  `marker_synonym_id` int(10) unsigned NOT NULL DEFAULT '0',
  `position` varchar(15) NOT NULL DEFAULT '',
443 444 445
  `lod_score` double DEFAULT NULL,
  PRIMARY KEY (`marker_id`,`map_id`),
  KEY `map_idx` (`map_id`,`chromosome_name`,`position`)
446
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
447

448 449
CREATE TABLE `marker_synonym` (
  `marker_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
450
  `marker_id` int(10) unsigned NOT NULL DEFAULT '0',
451
  `source` varchar(20) DEFAULT NULL,
452
  `name` varchar(30) DEFAULT NULL,
453 454 455
  PRIMARY KEY (`marker_synonym_id`),
  KEY `marker_synonym_idx` (`marker_synonym_id`,`name`),
  KEY `marker_idx` (`marker_id`)
456
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
457

458 459 460 461
CREATE TABLE `meta` (
  `meta_id` int(11) NOT NULL AUTO_INCREMENT,
  `species_id` int(10) unsigned DEFAULT '1',
  `meta_key` varchar(40) NOT NULL,
462
  `meta_value` varchar(255) NOT NULL,
463 464 465
  PRIMARY KEY (`meta_id`),
  UNIQUE KEY `species_key_value_idx` (`species_id`,`meta_key`,`meta_value`),
  KEY `species_value_idx` (`species_id`,`meta_value`)
466
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
467 468

CREATE TABLE `meta_coord` (
469 470
  `table_name` varchar(40) NOT NULL DEFAULT '',
  `coord_system_id` int(11) NOT NULL DEFAULT '0',
471
  `max_length` int(11) DEFAULT NULL,
472 473
  UNIQUE KEY `table_name` (`table_name`,`coord_system_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
474

475 476 477
CREATE TABLE `misc_attrib` (
  `misc_feature_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
478
  `value` text NOT NULL,
479
  UNIQUE KEY `misc_attribx` (`misc_feature_id`,`attrib_type_id`,`value`(500)),
480
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
481
  KEY `misc_feature_idx` (`misc_feature_id`)
482
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
483

484 485 486 487 488 489 490 491
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`)
492
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
493 494 495 496 497 498

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`)
499
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
500

501 502
CREATE TABLE `misc_set` (
  `misc_set_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
503 504 505
  `code` varchar(25) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
506
  `max_length` int(10) unsigned NOT NULL DEFAULT '0',
507
  PRIMARY KEY (`misc_set_id`),
508 509
  UNIQUE KEY `c` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
510

511
CREATE TABLE `object_xref` (
512 513 514
  `object_xref_id` int(11) NOT NULL AUTO_INCREMENT,
  `ensembl_id` int(10) unsigned NOT NULL DEFAULT '0',
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation','regulatory_factor','regulatory_feature','Marker') NOT NULL DEFAULT 'RawContig',
515
  `xref_id` int(10) unsigned NOT NULL,
516
  `linkage_annotation` varchar(255) DEFAULT NULL,
517 518 519 520 521
  `analysis_id` smallint(5) unsigned NOT NULL,
  UNIQUE KEY `ensembl_object_type` (`ensembl_object_type`,`ensembl_id`,`xref_id`),
  KEY `oxref_idx` (`object_xref_id`,`xref_id`,`ensembl_object_type`,`ensembl_id`),
  KEY `xref_idx` (`xref_id`,`ensembl_object_type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
522

523 524
CREATE TABLE `ontology_xref` (
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
525
  `linkage_type` varchar(3) DEFAULT NULL,
526 527 528 529 530
  `source_xref_id` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `object_xref_id_2` (`object_xref_id`,`source_xref_id`,`linkage_type`),
  KEY `object_xref_id` (`object_xref_id`),
  KEY `source_xref_id` (`source_xref_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
531

532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547
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`)
548
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
549

550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566
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`)
567
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
568 569 570 571 572

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`)
573
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
574

575
CREATE TABLE `peptide_archive` (
576
  `peptide_archive_id` int(11) NOT NULL AUTO_INCREMENT,
577 578
  `md5_checksum` varchar(32) DEFAULT NULL,
  `peptide_seq` mediumtext NOT NULL,
579 580
  PRIMARY KEY (`peptide_archive_id`),
  KEY `checksum` (`md5_checksum`)
581
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
582

583 584
CREATE TABLE `prediction_exon` (
  `prediction_exon_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
585 586 587 588 589 590 591
  `prediction_transcript_id` int(10) unsigned NOT NULL DEFAULT '0',
  `exon_rank` smallint(5) 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(4) NOT NULL DEFAULT '0',
  `start_phase` tinyint(4) NOT NULL DEFAULT '0',
592 593 594
  `score` double DEFAULT NULL,
  `p_value` double DEFAULT NULL,
  PRIMARY KEY (`prediction_exon_id`),
595 596 597
  KEY `prediction_transcript_id` (`prediction_transcript_id`),
  KEY `seq_region_id` (`seq_region_id`,`seq_region_start`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
598

599 600
CREATE TABLE `prediction_transcript` (
  `prediction_transcript_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
601 602 603 604 605
  `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',
  `analysis_id` int(11) DEFAULT NULL,
606
  `display_label` varchar(255) DEFAULT NULL,
607
  PRIMARY KEY (`prediction_transcript_id`),
608
  KEY `seq_region_id` (`seq_region_id`,`seq_region_start`),
609
  KEY `analysis_idx` (`analysis_id`)
610
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
611

612 613
CREATE TABLE `protein_align_feature` (
  `protein_align_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
614 615 616
  `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',
617
  `seq_region_strand` tinyint(1) NOT NULL DEFAULT '1',
618 619 620 621
  `hit_start` int(10) NOT NULL DEFAULT '0',
  `hit_end` int(10) NOT NULL DEFAULT '0',
  `hit_name` varchar(40) NOT NULL DEFAULT '',
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
622 623 624
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
625 626
  `cigar_line` text COLLATE latin1_bin,
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
627 628 629 630 631 632 633
  `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`)
634
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
635

636 637
CREATE TABLE `protein_feature` (
  `protein_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
638 639 640 641 642 643 644 645
  `translation_id` int(11) NOT NULL DEFAULT '0',
  `seq_start` int(10) NOT NULL DEFAULT '0',
  `seq_end` int(10) NOT NULL DEFAULT '0',
  `hit_start` int(10) NOT NULL DEFAULT '0',
  `hit_end` int(10) NOT NULL DEFAULT '0',
  `hit_name` varchar(40) NOT NULL DEFAULT '',
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double NOT NULL DEFAULT '0',
646 647
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
648 649
  `external_data` text COLLATE latin1_bin,
  `hit_description` text COLLATE latin1_bin,
650
  PRIMARY KEY (`protein_feature_id`),
Magali Ruffier's avatar
Magali Ruffier committed
651
  UNIQUE KEY `aln_idx` (`translation_id`,`hit_name`,`seq_start`,`seq_end`,`hit_start`,`hit_end`),
652 653
  KEY `translation_id` (`translation_id`),
  KEY `hitname_index` (`hit_name`),
654
  KEY `analysis_idx` (`analysis_id`)
655
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
656

657 658
CREATE TABLE `repeat_consensus` (
  `repeat_consensus_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
659 660 661 662
  `repeat_name` varchar(255) NOT NULL DEFAULT '',
  `repeat_class` varchar(100) NOT NULL DEFAULT '',
  `repeat_type` varchar(40) NOT NULL DEFAULT '',
  `repeat_consensus` text COLLATE latin1_bin,
663 664 665 666 667
  PRIMARY KEY (`repeat_consensus_id`),
  KEY `name` (`repeat_name`),
  KEY `class` (`repeat_class`),
  KEY `consensus` (`repeat_consensus`(10)),
  KEY `type` (`repeat_type`)
668
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
669

670 671
CREATE TABLE `repeat_feature` (
  `repeat_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
672 673 674
  `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',
675
  `seq_region_strand` tinyint(1) NOT NULL DEFAULT '1',
676 677 678 679
  `repeat_start` int(10) NOT NULL DEFAULT '0',
  `repeat_end` int(10) NOT NULL DEFAULT '0',
  `repeat_consensus_id` int(10) unsigned NOT NULL DEFAULT '0',
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
680 681 682 683 684
  `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`)
685
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
686 687 688

CREATE TABLE `seq_region` (
  `seq_region_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
689 690 691
  `name` varchar(40) NOT NULL DEFAULT '',
  `coord_system_id` int(10) NOT NULL DEFAULT '0',
  `length` int(10) NOT NULL DEFAULT '0',
692
  PRIMARY KEY (`seq_region_id`),
693 694 695
  UNIQUE KEY `coord_system_id` (`coord_system_id`,`name`),
  KEY `name_idx` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
696

697 698 699
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',
700
  `value` text NOT NULL,
701
  UNIQUE KEY `region_attribx` (`seq_region_id`,`attrib_type_id`,`value`(500)),
702
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
703
  KEY `seq_region_idx` (`seq_region_id`)
704
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
705

706 707 708 709
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,
710 711
  KEY `mapping_set_id` (`mapping_set_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
712

713 714 715
CREATE TABLE `seq_region_synonym` (
  `seq_region_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
716
  `synonym` varchar(250) NOT NULL,
717
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
718
  PRIMARY KEY (`seq_region_synonym_id`),
719 720
  UNIQUE KEY `syn_idx` (`synonym`,`seq_region_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
721

722 723
CREATE TABLE `simple_feature` (
  `simple_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
724 725 726 727 728 729
  `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',
  `display_label` varchar(40) NOT NULL DEFAULT '',
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
730 731 732 733 734
  `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`)
735
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
736

737
CREATE TABLE `stable_id_event` (
738
  `old_stable_id` varchar(128) DEFAULT NULL,
739
  `old_version` smallint(6) DEFAULT NULL,
740
  `new_stable_id` varchar(128) DEFAULT NULL,
741
  `new_version` smallint(6) DEFAULT NULL,
742 743
  `mapping_session_id` int(10) NOT NULL DEFAULT '0',
  `type` enum('gene','transcript','translation') NOT NULL DEFAULT 'gene',
744
  `score` float NOT NULL DEFAULT '0',
745
  UNIQUE KEY `uni_idx` (`mapping_session_id`,`old_stable_id`,`old_version`,`new_stable_id`,`new_version`,`type`),
746 747
  KEY `new_idx` (`new_stable_id`),
  KEY `old_idx` (`old_stable_id`)
748
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
749

750
CREATE TABLE `supporting_feature` (
751
  `exon_id` int(11) NOT NULL DEFAULT '0',
752
  `feature_type` enum('dna_align_feature','protein_align_feature') DEFAULT NULL,
753
  `feature_id` int(11) NOT NULL DEFAULT '0',
754 755
  UNIQUE KEY `all_idx` (`exon_id`,`feature_type`,`feature_id`),
  KEY `feature_idx` (`feature_type`,`feature_id`)
756
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
757

758 759 760 761 762 763 764 765 766
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,
767
  `source` varchar(40) NOT NULL DEFAULT 'ensembl',
768
  `biotype` varchar(40) NOT NULL,
769
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN') DEFAULT NULL,
770 771 772 773 774 775 776 777 778 779 780 781 782 783
  `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`)
784
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
785 786 787 788 789

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,
790
  UNIQUE KEY `transcript_attribx` (`transcript_id`,`attrib_type_id`,`value`(500)),
791 792
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `transcript_idx` (`transcript_id`)
793
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;