table.sql 41.9 KB
Newer Older
1 2 3 4 5 6
CREATE TABLE `alt_allele` (
  `alt_allele_id` int(11) NOT NULL AUTO_INCREMENT,
  `gene_id` int(11) NOT NULL DEFAULT '0',
  `is_ref` tinyint(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `gene_idx` (`gene_id`),
  UNIQUE KEY `allele_idx` (`alt_allele_id`,`gene_id`)
7
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
8 9 10 11

CREATE TABLE `analysis` (
  `analysis_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
12 13 14 15 16 17 18 19 20 21 22 23
  `logic_name` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `db` varchar(120) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `db_version` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `db_file` varchar(120) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `program` varchar(80) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `program_version` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `program_file` varchar(80) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `parameters` text CHARACTER SET latin1 COLLATE latin1_bin,
  `module` varchar(80) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `module_version` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `gff_source` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `gff_feature` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
24 25 26
  PRIMARY KEY (`analysis_id`),
  UNIQUE KEY `logic_name` (`logic_name`),
  KEY `logic_name_idx` (`logic_name`)
27
) ENGINE=MyISAM AUTO_INCREMENT=1504 DEFAULT CHARSET=latin1;
28 29 30

CREATE TABLE `analysis_description` (
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
31 32
  `description` text CHARACTER SET latin1 COLLATE latin1_bin,
  `display_label` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
33
  `displayable` tinyint(1) NOT NULL DEFAULT '1',
34
  `web_data` text CHARACTER SET latin1 COLLATE latin1_bin,
35
  KEY `analysis_idx` (`analysis_id`)
36
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
37 38 39 40 41 42 43 44 45 46 47 48

CREATE TABLE `assembly` (
  `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',
  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_id` (`cmp_seq_region_id`),
  KEY `asm_seq_region_id` (`asm_seq_region_id`,`asm_start`)
49
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
50 51 52 53 54 55

CREATE TABLE `assembly_exception` (
  `assembly_exception_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `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',
56
  `exc_type` enum('HAP','PAR','PATCH_NOVEL','PATCH_FIX') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'HAP',
57 58 59 60 61 62 63
  `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',
  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`)
64
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
65

66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
CREATE TABLE `associated_group` (
  `associated_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`associated_group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

88 89
CREATE TABLE `attrib_type` (
  `attrib_type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
90 91 92
  `code` varchar(15) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `description` text CHARACTER SET latin1 COLLATE latin1_bin,
93 94
  PRIMARY KEY (`attrib_type_id`),
  UNIQUE KEY `c` (`code`)
95
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
96 97 98 99 100 101 102 103 104 105 106 107

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`)
108
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
109 110

CREATE TABLE `data_file` (
111 112 113
  `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,
114 115 116 117 118 119 120 121 122
  `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`)
123
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
124 125 126 127 128 129 130 131 132 133 134

CREATE TABLE `density_feature` (
  `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',
  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`)
135
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
136 137 138 139 140 141

CREATE TABLE `density_type` (
  `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',
142
  `value_type` enum('sum','ratio') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'sum',
143 144
  PRIMARY KEY (`density_type_id`),
  UNIQUE KEY `analysis_id` (`analysis_id`,`block_size`,`region_features`)
145 146 147 148 149 150 151 152 153 154
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
155 156 157 158 159 160 161 162

CREATE TABLE `ditag` (
  `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,
  PRIMARY KEY (`ditag_id`)
163
) ENGINE=MyISAM AUTO_INCREMENT=3278357 DEFAULT CHARSET=latin1;
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181

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',
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `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',
  `cigar_line` text,
  `ditag_side` char(1) DEFAULT '',
  PRIMARY KEY (`ditag_feature_id`),
  KEY `ditag_id` (`ditag_id`),
  KEY `ditag_pair_id` (`ditag_pair_id`)
182
) ENGINE=MyISAM AUTO_INCREMENT=4828763 DEFAULT CHARSET=latin1;
183 184 185

CREATE TABLE `dna` (
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
186
  `sequence` mediumtext CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
187
  PRIMARY KEY (`seq_region_id`)
188
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=750000 AVG_ROW_LENGTH=19000;
189 190 191 192 193 194 195 196 197 198

CREATE TABLE `dna_align_feature` (
  `dna_align_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(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',
199
  `hit_name` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
200 201 202 203
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
204
  `cigar_line` text CHARACTER SET latin1 COLLATE latin1_bin,
205 206
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
  `hcoverage` double DEFAULT NULL,
207
  `external_data` text CHARACTER SET latin1 COLLATE latin1_bin,
208 209 210 211 212 213 214 215
  `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`)
216
) ENGINE=MyISAM AUTO_INCREMENT=29797140 DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
217 218 219 220

CREATE TABLE `dnac` (
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sequence` mediumblob NOT NULL,
221
  `n_line` text CHARACTER SET latin1 COLLATE latin1_bin,
222
  PRIMARY KEY (`seq_region_id`)
223
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=750000 AVG_ROW_LENGTH=19000;
224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241

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`)
242
) ENGINE=MyISAM AUTO_INCREMENT=162034 DEFAULT CHARSET=latin1;
243 244 245 246 247 248 249 250

CREATE TABLE `exon_transcript` (
  `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',
  PRIMARY KEY (`exon_id`,`transcript_id`,`rank`),
  KEY `transcript` (`transcript_id`),
  KEY `exon` (`exon_id`)
251
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
252 253 254

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

CREATE TABLE `external_synonym` (
  `xref_id` int(10) unsigned NOT NULL DEFAULT '0',
269
  `synonym` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
270 271
  PRIMARY KEY (`xref_id`,`synonym`),
  KEY `name_index` (`synonym`)
272
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297

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,
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN') DEFAULT NULL,
  `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`),
  KEY `stable_id_idx` (`stable_id`,`version`)
298
) ENGINE=MyISAM AUTO_INCREMENT=18276 DEFAULT CHARSET=latin1;
299 300

CREATE TABLE `gene_archive` (
301
  `gene_stable_id` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
302
  `gene_version` smallint(6) NOT NULL DEFAULT '0',
303
  `transcript_stable_id` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
304
  `transcript_version` smallint(6) NOT NULL DEFAULT '0',
305
  `translation_stable_id` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
306 307 308 309 310 311
  `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',
  KEY `gene_idx` (`gene_stable_id`,`gene_version`),
  KEY `transcript_idx` (`transcript_stable_id`,`transcript_version`),
  KEY `translation_idx` (`translation_stable_id`,`translation_version`)
312
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
313 314 315 316

CREATE TABLE `gene_attrib` (
  `gene_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
317
  `value` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
318 319
  KEY `type_val_idx` (`attrib_type_id`,`value`),
  KEY `gene_idx` (`gene_id`)
320
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
321 322 323 324 325 326 327 328 329

CREATE TABLE `identity_xref` (
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
  `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,
330
  `cigar_line` text CHARACTER SET latin1 COLLATE latin1_bin,
331 332 333
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  PRIMARY KEY (`object_xref_id`)
334
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
335 336

CREATE TABLE `interpro` (
337 338
  `interpro_ac` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `id` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
339 340
  UNIQUE KEY `interpro_ac` (`interpro_ac`,`id`),
  KEY `id` (`id`)
341
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
342 343 344 345 346 347 348 349 350 351 352 353 354 355

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`),
  UNIQUE KEY `analysis_id` (`analysis_id`,`seq_region_id`,`seq_region_start`,`seq_region_end`,`seq_region_strand`,`hit_name`)
356
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
357 358 359 360 361 362

CREATE TABLE `karyotype` (
  `karyotype_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `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',
363 364
  `band` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `stain` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
365 366
  PRIMARY KEY (`karyotype_id`),
  KEY `region_band_idx` (`seq_region_id`,`band`)
367
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
368 369 370

CREATE TABLE `map` (
  `map_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
371
  `map_name` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
372
  PRIMARY KEY (`map_id`)
373
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
374 375 376

CREATE TABLE `mapping_session` (
  `mapping_session_id` int(11) NOT NULL AUTO_INCREMENT,
377 378 379 380 381 382
  `old_db_name` varchar(80) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `new_db_name` varchar(80) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `old_release` varchar(5) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `new_release` varchar(5) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `old_assembly` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `new_assembly` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
383 384
  `created` datetime NOT NULL,
  PRIMARY KEY (`mapping_session_id`)
385
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
386 387 388

CREATE TABLE `mapping_set` (
  `mapping_set_id` int(10) unsigned NOT NULL,
389 390
  `internal_schema_build` varchar(20) NOT NULL,
  `external_schema_build` varchar(20) NOT NULL,
Andy Yates's avatar
Andy Yates committed
391
  PRIMARY KEY (`mapping_set_id`),
392 393
  UNIQUE KEY `mapping_idx` (`internal_schema_build`,`external_schema_build`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
394 395 396 397

CREATE TABLE `marker` (
  `marker_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `display_marker_synonym_id` int(10) unsigned DEFAULT NULL,
398 399
  `left_primer` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `right_primer` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
400 401 402
  `min_primer_dist` int(10) unsigned NOT NULL DEFAULT '0',
  `max_primer_dist` int(10) unsigned NOT NULL DEFAULT '0',
  `priority` int(11) DEFAULT NULL,
403
  `type` enum('est','microsatellite') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
404 405
  PRIMARY KEY (`marker_id`),
  KEY `marker_idx` (`marker_id`,`priority`)
406
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
407 408 409 410 411 412 413 414 415 416 417 418

CREATE TABLE `marker_feature` (
  `marker_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `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',
  `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`)
419
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
420 421 422 423

CREATE TABLE `marker_map_location` (
  `marker_id` int(10) unsigned NOT NULL DEFAULT '0',
  `map_id` int(10) unsigned NOT NULL DEFAULT '0',
424
  `chromosome_name` varchar(15) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
425
  `marker_synonym_id` int(10) unsigned NOT NULL DEFAULT '0',
426
  `position` varchar(15) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
427 428 429
  `lod_score` double DEFAULT NULL,
  PRIMARY KEY (`marker_id`,`map_id`),
  KEY `map_idx` (`map_id`,`chromosome_name`,`position`)
430
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
431 432 433 434

CREATE TABLE `marker_synonym` (
  `marker_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `marker_id` int(10) unsigned NOT NULL DEFAULT '0',
435 436
  `source` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `name` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
437 438 439
  PRIMARY KEY (`marker_synonym_id`),
  KEY `marker_synonym_idx` (`marker_synonym_id`,`name`),
  KEY `marker_idx` (`marker_id`)
440
) ENGINE=MyISAM AUTO_INCREMENT=1063 DEFAULT CHARSET=latin1;
441 442 443 444 445 446 447 448 449

CREATE TABLE `meta` (
  `meta_id` int(11) NOT NULL AUTO_INCREMENT,
  `species_id` int(10) unsigned DEFAULT '1',
  `meta_key` varchar(40) NOT NULL,
  `meta_value` varchar(255) NOT NULL,
  PRIMARY KEY (`meta_id`),
  UNIQUE KEY `species_key_value_idx` (`species_id`,`meta_key`,`meta_value`),
  KEY `species_value_idx` (`species_id`,`meta_value`)
450
) ENGINE=MyISAM AUTO_INCREMENT=86 DEFAULT CHARSET=latin1;
451 452

CREATE TABLE `meta_coord` (
453
  `table_name` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
454 455 456
  `coord_system_id` int(11) NOT NULL DEFAULT '0',
  `max_length` int(11) DEFAULT NULL,
  UNIQUE KEY `table_name` (`table_name`,`coord_system_id`)
457
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
458 459 460 461

CREATE TABLE `misc_attrib` (
  `misc_feature_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
462
  `value` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
463 464
  KEY `type_val_idx` (`attrib_type_id`,`value`),
  KEY `misc_feature_idx` (`misc_feature_id`)
465
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
466 467 468 469 470 471 472 473 474

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`)
475
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
476 477 478 479 480 481

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`)
482
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
483 484 485

CREATE TABLE `misc_set` (
  `misc_set_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
486 487 488
  `code` varchar(25) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `description` text CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
489 490 491
  `max_length` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`misc_set_id`),
  UNIQUE KEY `c` (`code`)
492
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
493 494 495 496

CREATE TABLE `object_xref` (
  `object_xref_id` int(11) NOT NULL AUTO_INCREMENT,
  `ensembl_id` int(10) unsigned NOT NULL DEFAULT '0',
497 498 499
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation','regulatory_factor','regulatory_feature') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'RawContig',
  `xref_id` int(10) unsigned NOT NULL,
  `linkage_annotation` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
500 501 502 503
  `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`)
504
) ENGINE=MyISAM AUTO_INCREMENT=253685 DEFAULT CHARSET=latin1;
505 506 507 508 509 510 511 512

CREATE TABLE `ontology_xref` (
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
  `linkage_type` varchar(3) DEFAULT NULL,
  `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`)
513
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530

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

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`)
550
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
551 552 553 554 555

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`)
556
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
557 558 559

CREATE TABLE `peptide_archive` (
  `peptide_archive_id` int(11) NOT NULL AUTO_INCREMENT,
560 561
  `md5_checksum` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `peptide_seq` mediumtext CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
562 563
  PRIMARY KEY (`peptide_archive_id`),
  KEY `checksum` (`md5_checksum`)
564
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
565 566 567 568 569 570 571 572 573 574 575 576 577 578 579

CREATE TABLE `prediction_exon` (
  `prediction_exon_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `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',
  `score` double DEFAULT NULL,
  `p_value` double DEFAULT NULL,
  PRIMARY KEY (`prediction_exon_id`),
  KEY `prediction_transcript_id` (`prediction_transcript_id`),
  KEY `seq_region_id` (`seq_region_id`,`seq_region_start`)
580
) ENGINE=MyISAM AUTO_INCREMENT=192 DEFAULT CHARSET=latin1;
581 582 583 584 585 586 587 588

CREATE TABLE `prediction_transcript` (
  `prediction_transcript_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',
  `analysis_id` int(11) DEFAULT NULL,
589
  `display_label` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
590 591 592
  PRIMARY KEY (`prediction_transcript_id`),
  KEY `seq_region_id` (`seq_region_id`,`seq_region_start`),
  KEY `analysis_idx` (`analysis_id`)
593
) ENGINE=MyISAM AUTO_INCREMENT=18084 DEFAULT CHARSET=latin1;
594 595 596 597 598 599 600 601 602

CREATE TABLE `protein_align_feature` (
  `protein_align_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(1) NOT NULL DEFAULT '1',
  `hit_start` int(10) NOT NULL DEFAULT '0',
  `hit_end` int(10) NOT NULL DEFAULT '0',
603
  `hit_name` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
604 605 606 607
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
608
  `cigar_line` text CHARACTER SET latin1 COLLATE latin1_bin,
609 610 611 612 613 614 615 616
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
  `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`)
617
) ENGINE=MyISAM AUTO_INCREMENT=11554505 DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
618 619 620 621 622 623 624 625

CREATE TABLE `protein_feature` (
  `protein_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `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',
626
  `hit_name` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
627 628 629 630
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double NOT NULL DEFAULT '0',
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
631 632
  `external_data` text,
  `hit_description` text,
633 634 635 636
  PRIMARY KEY (`protein_feature_id`),
  KEY `translation_id` (`translation_id`),
  KEY `hitname_index` (`hit_name`),
  KEY `analysis_idx` (`analysis_id`)
637
) ENGINE=MyISAM AUTO_INCREMENT=242847 DEFAULT CHARSET=latin1;
638 639 640

CREATE TABLE `qtl` (
  `qtl_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
641
  `trait` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
642 643 644 645 646 647
  `lod_score` float DEFAULT NULL,
  `flank_marker_id_1` int(11) DEFAULT NULL,
  `flank_marker_id_2` int(11) DEFAULT NULL,
  `peak_marker_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`qtl_id`),
  KEY `trait_idx` (`trait`)
648
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
649 650 651 652 653 654 655 656 657 658

CREATE TABLE `qtl_feature` (
  `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',
  `qtl_id` int(11) NOT NULL DEFAULT '0',
  `analysis_id` int(11) NOT NULL DEFAULT '0',
  KEY `qtl_id` (`qtl_id`),
  KEY `loc_idx` (`seq_region_id`,`seq_region_start`),
  KEY `analysis_idx` (`analysis_id`)
659
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
660 661 662 663

CREATE TABLE `qtl_synonym` (
  `qtl_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `qtl_id` int(10) unsigned NOT NULL DEFAULT '0',
664 665
  `source_database` enum('rat genome database','ratmap') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'rat genome database',
  `source_primary_id` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
666 667
  PRIMARY KEY (`qtl_synonym_id`),
  KEY `qtl_idx` (`qtl_id`)
668
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
669 670 671

CREATE TABLE `repeat_consensus` (
  `repeat_consensus_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
672 673 674 675
  `repeat_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `repeat_class` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `repeat_type` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `repeat_consensus` text CHARACTER SET latin1 COLLATE latin1_bin,
676 677 678 679 680
  PRIMARY KEY (`repeat_consensus_id`),
  KEY `name` (`repeat_name`),
  KEY `class` (`repeat_class`),
  KEY `consensus` (`repeat_consensus`(10)),
  KEY `type` (`repeat_type`)
681
) ENGINE=MyISAM AUTO_INCREMENT=1018 DEFAULT CHARSET=latin1;
682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697

CREATE TABLE `repeat_feature` (
  `repeat_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(1) NOT NULL DEFAULT '1',
  `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',
  `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`)
698
) ENGINE=MyISAM AUTO_INCREMENT=922515 DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
699 700 701

CREATE TABLE `seq_region` (
  `seq_region_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
702
  `name` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
703 704 705 706 707
  `coord_system_id` int(10) NOT NULL DEFAULT '0',
  `length` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seq_region_id`),
  UNIQUE KEY `coord_system_id` (`coord_system_id`,`name`),
  KEY `name_idx` (`name`)
708
) ENGINE=MyISAM AUTO_INCREMENT=965907 DEFAULT CHARSET=latin1;
709 710 711 712

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',
713
  `value` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
714 715
  KEY `type_val_idx` (`attrib_type_id`,`value`),
  KEY `seq_region_idx` (`seq_region_id`)
716
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
717 718 719 720 721 722

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,
  KEY `mapping_set_id` (`mapping_set_id`)
723
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
724 725 726 727 728 729 730 731

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,
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`seq_region_synonym_id`),
  UNIQUE KEY `syn_idx` (`synonym`)
732
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
733 734 735 736 737 738 739

CREATE TABLE `simple_feature` (
  `simple_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(1) NOT NULL DEFAULT '0',
740
  `display_label` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
741 742 743 744 745 746
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `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`)
747
) ENGINE=MyISAM AUTO_INCREMENT=95700 DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
748 749

CREATE TABLE `stable_id_event` (
750
  `old_stable_id` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
751
  `old_version` smallint(6) DEFAULT NULL,
752
  `new_stable_id` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
753 754
  `new_version` smallint(6) DEFAULT NULL,
  `mapping_session_id` int(10) NOT NULL DEFAULT '0',
755
  `type` enum('gene','transcript','translation') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'gene',
756 757 758 759
  `score` float NOT NULL DEFAULT '0',
  UNIQUE KEY `uni_idx` (`mapping_session_id`,`old_stable_id`,`old_version`,`new_stable_id`,`new_version`,`type`),
  KEY `new_idx` (`new_stable_id`),
  KEY `old_idx` (`old_stable_id`)
760
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
761 762 763

CREATE TABLE `supporting_feature` (
  `exon_id` int(11) NOT NULL DEFAULT '0',
764
  `feature_type` enum('dna_align_feature','protein_align_feature') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
765 766 767
  `feature_id` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `all_idx` (`exon_id`,`feature_type`,`feature_id`),
  KEY `feature_idx` (`feature_type`,`feature_id`)
768
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794

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,
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN') DEFAULT NULL,
  `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`)
795
) ENGINE=MyISAM AUTO_INCREMENT=21741 DEFAULT CHARSET=latin1;
796 797 798 799

CREATE TABLE `transcript_attrib` (
  `transcript_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
800
  `value` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
801 802
  KEY `type_val_idx` (`attrib_type_id`,`value`),
  KEY `transcript_idx` (`transcript_id`)
803
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
804 805 806 807 808 809 810

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`)
811
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
812 813 814

CREATE TABLE `transcript_supporting_feature` (
  `transcript_id` int(11) NOT NULL DEFAULT '0',
815
  `feature_type` enum('dna_align_feature','protein_align_feature') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
816 817 818
  `feature_id` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `all_idx` (`transcript_id`,`feature_type`,`feature_id`),
  KEY `feature_idx` (`feature_type`,`feature_id`)
819
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
820 821 822 823 824 825 826 827 828 829 830 831 832 833 834

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`)
835
) ENGINE=MyISAM AUTO_INCREMENT=21741 DEFAULT CHARSET=latin1;
836 837 838 839

CREATE TABLE `translation_attrib` (
  `translation_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
840
  `value` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
841 842
  KEY `type_val_idx` (`attrib_type_id`,`value`),
  KEY `translation_idx` (`translation_id`)
843
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
844 845 846 847 848 849 850

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,
  KEY `transcript_id` (`transcript_id`),
  KEY `gene_id` (`gene_id`)
851
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
852 853 854

CREATE TABLE `unmapped_object` (
  `unmapped_object_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
855
  `type` enum('xref','cDNA','Marker') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
856 857
  `analysis_id` int(10) unsigned NOT NULL,
  `external_db_id` int(11) DEFAULT NULL,
858
  `identifier` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
859 860 861 862
  `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',
863
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT 'RawContig',
864 865 866 867
  PRIMARY KEY (`unmapped_object_id`),
  KEY `id_idx` (`identifier`),
  KEY `anal_idx` (`analysis_id`),
  KEY `anal_exdb_idx` (`analysis_id`,`external_db_id`)
868
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
869 870 871

CREATE TABLE `unmapped_reason` (
  `unmapped_reason_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
872 873
  `summary_description` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `full_description` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
874
  PRIMARY KEY (`unmapped_reason_id`)
875
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
876 877 878 879

CREATE TABLE `xref` (
  `xref_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `external_db_id` int(11) NOT NULL DEFAULT '0',
880 881 882 883 884 885
  `dbprimary_acc` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `display_label` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `version` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `description` text CHARACTER SET latin1 COLLATE latin1_bin,
  `info_type` enum('NONE','PROJECTION','MISC','DEPENDENT','DIRECT','SEQUENCE_MATCH','INFERRED_PAIR','PROBE','UNMAPPED','CHECKSUM') CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT 'NONE',
  `info_text` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
886 887 888
  PRIMARY KEY (`xref_id`),
  UNIQUE KEY `id_index` (`dbprimary_acc`,`external_db_id`,`info_type`,`info_text`,`version`),
  KEY `display_index` (`display_label`)
889
) ENGINE=MyISAM AUTO_INCREMENT=1000000 DEFAULT CHARSET=latin1;
890