table.sql 41.7 KB
Newer Older
1 2 3 4 5 6 7
CREATE TABLE `alt_allele` (
  `alt_allele_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `alt_allele_group_id` int(10) unsigned NOT NULL,
  `gene_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`alt_allele_id`),
  UNIQUE KEY `gene_idx` (`gene_id`),
  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 DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
24 25 26 27 28 29 30
  `logic_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
  `db` varchar(120) COLLATE latin1_bin DEFAULT NULL,
  `db_version` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `db_file` varchar(120) COLLATE latin1_bin DEFAULT NULL,
  `program` varchar(80) COLLATE latin1_bin DEFAULT NULL,
  `program_version` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `program_file` varchar(80) COLLATE latin1_bin DEFAULT NULL,
31
  `parameters` text COLLATE latin1_bin,
Magali Ruffier's avatar
Magali Ruffier committed
32 33 34 35
  `module` varchar(80) COLLATE latin1_bin DEFAULT NULL,
  `module_version` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `gff_source` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `gff_feature` varchar(40) COLLATE latin1_bin DEFAULT NULL,
36
  PRIMARY KEY (`analysis_id`),
Magali Ruffier's avatar
Magali Ruffier committed
37
  UNIQUE KEY `logic_name_idx` (`logic_name`)
Magali Ruffier's avatar
Magali Ruffier committed
38
) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
39 40

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

CREATE TABLE `assembly` (
50 51 52 53 54 55 56
  `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',
57
  UNIQUE KEY `all_idx` (`asm_seq_region_id`,`cmp_seq_region_id`,`asm_start`,`asm_end`,`cmp_start`,`cmp_end`,`ori`),
58 59 60
  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;
61 62 63

CREATE TABLE `assembly_exception` (
  `assembly_exception_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
64 65 66
  `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',
Magali Ruffier's avatar
Magali Ruffier committed
67
  `exc_type` enum('HAP','PAR','PATCH_NOVEL','PATCH_FIX') COLLATE latin1_bin NOT NULL DEFAULT 'HAP',
68 69 70 71
  `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',
72 73 74
  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`)
75
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
76 77 78 79 80

CREATE TABLE `associated_group` (
  `associated_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`associated_group_id`)
81
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96

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

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

108 109 110 111 112 113 114 115 116 117 118 119 120
CREATE TABLE `biotype` (
  `biotype_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `object_type` enum('gene','transcript') NOT NULL DEFAULT 'gene',
  `db_type` set('cdna','core','coreexpressionatlas','coreexpressionest','coreexpressiongnf','funcgen','otherfeatures','rnaseq','variation','vega','presite','sangervega') NOT NULL DEFAULT 'core',
  `attrib_type_id` int(11) DEFAULT NULL,
  `description` text,
  `biotype_group` enum('coding','pseudogene','snoncoding','lnoncoding','mnoncoding','LRG','undefined','no_group') DEFAULT NULL,
  `so_acc` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`biotype_id`),
  UNIQUE KEY `name_type_idx` (`name`,`object_type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

121 122 123 124 125 126 127 128 129 130 131
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`)
Magali Ruffier's avatar
Magali Ruffier committed
132
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
133 134 135 136 137 138 139 140 141

CREATE TABLE `data_file` (
  `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,
  `name` varchar(100) NOT NULL,
  `version_lock` tinyint(1) NOT NULL DEFAULT '0',
  `absolute` tinyint(1) NOT NULL DEFAULT '0',
  `url` text,
142
  `file_type` enum('BAM','BAMCOV','BIGBED','BIGWIG','VCF') DEFAULT NULL,
143 144 145 146
  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`)
147
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
148 149

CREATE TABLE `density_feature` (
150 151 152 153 154 155
  `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',
156 157 158
  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`)
159
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
160 161

CREATE TABLE `density_type` (
162 163 164 165
  `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',
Magali Ruffier's avatar
Magali Ruffier committed
166
  `value_type` enum('sum','ratio') COLLATE latin1_bin NOT NULL DEFAULT 'sum',
167
  PRIMARY KEY (`density_type_id`),
168
  UNIQUE KEY `analysis_id` (`analysis_id`,`block_size`,`region_features`)
Magali Ruffier's avatar
Magali Ruffier committed
169
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
170 171 172 173 174 175 176 177

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

CREATE TABLE `ditag` (
181 182 183 184 185
  `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,
186
  PRIMARY KEY (`ditag_id`)
187
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
188 189 190 191 192 193 194 195 196

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',
197
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
198 199 200
  `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',
201 202
  `cigar_line` text,
  `ditag_side` char(1) DEFAULT '',
203
  PRIMARY KEY (`ditag_feature_id`),
204
  KEY `ditag_id` (`ditag_id`),
Magali Ruffier's avatar
Magali Ruffier committed
205
  KEY `ditag_pair_id` (`ditag_pair_id`),
Magali Ruffier's avatar
Magali Ruffier committed
206
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`)
207
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
208 209

CREATE TABLE `dna` (
210
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
211
  `sequence` mediumtext COLLATE latin1_bin NOT NULL,
212
  PRIMARY KEY (`seq_region_id`)
213
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=750000 AVG_ROW_LENGTH=19000;
214 215 216

CREATE TABLE `dna_align_feature` (
  `dna_align_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
217 218 219 220 221 222 223
  `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',
Magali Ruffier's avatar
Magali Ruffier committed
224
  `hit_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
225
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
226 227 228
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
229 230
  `cigar_line` text COLLATE latin1_bin,
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
231
  `hcoverage` double DEFAULT NULL,
232
  `align_type` enum('ensembl','cigar','vulgar','mdtag') COLLATE latin1_bin DEFAULT 'ensembl',
233 234 235 236 237 238
  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`)
239
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
240

241 242 243 244 245 246 247 248 249 250
CREATE TABLE `dna_align_feature_attrib` (
  `dna_align_feature_id` int(10) unsigned NOT NULL,
  `attrib_type_id` smallint(5) unsigned NOT NULL,
  `value` text NOT NULL,
  UNIQUE KEY `dna_align_feature_attribx` (`dna_align_feature_id`,`attrib_type_id`,`value`(500)),
  KEY `dna_align_feature_idx` (`dna_align_feature_id`),
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
  KEY `val_only_idx` (`value`(40))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

251 252 253 254 255 256 257 258 259 260 261
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,
Magali Ruffier's avatar
Magali Ruffier committed
262
  `version` smallint(5) unsigned DEFAULT NULL,
263 264
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
265 266 267
  PRIMARY KEY (`exon_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `stable_id_idx` (`stable_id`,`version`)
Magali Ruffier's avatar
Magali Ruffier committed
268
) ENGINE=MyISAM AUTO_INCREMENT=382467 DEFAULT CHARSET=latin1;
269 270

CREATE TABLE `exon_transcript` (
271 272 273
  `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',
274 275 276
  PRIMARY KEY (`exon_id`,`transcript_id`,`rank`),
  KEY `transcript` (`transcript_id`),
  KEY `exon` (`exon_id`)
277
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
278 279

CREATE TABLE `external_db` (
280
  `external_db_id` int(11) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
281 282 283
  `db_name` varchar(27) COLLATE latin1_bin NOT NULL DEFAULT '',
  `db_release` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
  `status` enum('KNOWNXREF','KNOWN','XREF','PRED','ORTH','PSEUDO') COLLATE latin1_bin NOT NULL DEFAULT 'KNOWNXREF',
284
  `priority` int(11) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
285 286 287 288
  `db_display_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `type` enum('ARRAY','ALT_TRANS','ALT_GENE','MISC','LIT','PRIMARY_DB_SYNONYM','ENSEMBL') COLLATE latin1_bin DEFAULT NULL,
  `secondary_db_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `secondary_db_table` varchar(255) COLLATE latin1_bin DEFAULT NULL,
289
  `description` text COLLATE latin1_bin,
Magali Ruffier's avatar
Magali Ruffier committed
290 291
  PRIMARY KEY (`external_db_id`),
  UNIQUE KEY `db_name_db_release_idx` (`db_name`,`db_release`)
292
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
293 294

CREATE TABLE `external_synonym` (
295
  `xref_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
296
  `synonym` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
297 298
  PRIMARY KEY (`xref_id`,`synonym`),
  KEY `name_index` (`synonym`)
299
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
300 301 302 303 304 305 306 307 308 309 310 311 312 313 314

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(40) NOT NULL,
  `description` text,
  `is_current` tinyint(1) NOT NULL DEFAULT '1',
  `canonical_transcript_id` int(10) unsigned NOT NULL,
  `stable_id` varchar(128) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
315
  `version` smallint(5) unsigned DEFAULT NULL,
316 317
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
318 319 320 321
  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`),
Magali Ruffier's avatar
Magali Ruffier committed
322 323
  KEY `stable_id_idx` (`stable_id`,`version`),
  KEY `canonical_transcript_id_idx` (`canonical_transcript_id`)
Magali Ruffier's avatar
Magali Ruffier committed
324
) ENGINE=MyISAM AUTO_INCREMENT=40234 DEFAULT CHARSET=latin1;
325 326

CREATE TABLE `gene_archive` (
Magali Ruffier's avatar
Magali Ruffier committed
327
  `gene_stable_id` varchar(128) COLLATE latin1_bin NOT NULL DEFAULT '',
328
  `gene_version` smallint(6) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
329
  `transcript_stable_id` varchar(128) COLLATE latin1_bin NOT NULL DEFAULT '',
330
  `transcript_version` smallint(6) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
331
  `translation_stable_id` varchar(128) COLLATE latin1_bin NOT NULL DEFAULT '',
332 333 334
  `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',
335 336
  KEY `gene_idx` (`gene_stable_id`,`gene_version`),
  KEY `transcript_idx` (`transcript_stable_id`,`transcript_version`),
Magali Ruffier's avatar
Magali Ruffier committed
337 338
  KEY `translation_idx` (`translation_stable_id`,`translation_version`),
  KEY `peptide_archive_id_idx` (`peptide_archive_id`)
339
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
340 341 342 343

CREATE TABLE `gene_attrib` (
  `gene_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
344
  `value` text COLLATE latin1_bin NOT NULL,
345 346
  UNIQUE KEY `gene_attribx` (`gene_id`,`attrib_type_id`,`value`(500)),
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
Magali Ruffier's avatar
Magali Ruffier committed
347
  KEY `val_only_idx` (`value`(40)),
348 349
  KEY `gene_idx` (`gene_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
350 351 352 353

CREATE TABLE `genome_statistics` (
  `genome_statistics_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `statistic` varchar(128) NOT NULL,
354
  `value` bigint(11) unsigned NOT NULL DEFAULT '0',
355 356
  `species_id` int(10) unsigned DEFAULT '1',
  `attrib_type_id` int(10) unsigned DEFAULT NULL,
357
  `timestamp` datetime DEFAULT NULL,
358
  PRIMARY KEY (`genome_statistics_id`),
Magali Ruffier's avatar
Magali Ruffier committed
359 360
  UNIQUE KEY `stats_uniq` (`statistic`,`attrib_type_id`,`species_id`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;
361 362

CREATE TABLE `identity_xref` (
363
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
364 365 366 367 368 369
  `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,
370
  `cigar_line` text COLLATE latin1_bin,
371 372 373
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  PRIMARY KEY (`object_xref_id`)
374
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
375 376

CREATE TABLE `interpro` (
Magali Ruffier's avatar
Magali Ruffier committed
377
  `interpro_ac` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
378
  `id` varchar(40) COLLATE latin1_bin NOT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
379 380
  UNIQUE KEY `accession_idx` (`interpro_ac`,`id`),
  KEY `id_idx` (`id`)
381
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
382 383 384 385 386 387 388 389 390 391 392 393 394 395 396

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`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`)
397
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
398 399 400

CREATE TABLE `karyotype` (
  `karyotype_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
401 402 403
  `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',
Magali Ruffier's avatar
Magali Ruffier committed
404 405
  `band` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `stain` varchar(40) COLLATE latin1_bin DEFAULT NULL,
406 407
  PRIMARY KEY (`karyotype_id`),
  KEY `region_band_idx` (`seq_region_id`,`band`)
408
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
409 410 411

CREATE TABLE `map` (
  `map_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
412
  `map_name` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT '',
413
  PRIMARY KEY (`map_id`)
414
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
415 416

CREATE TABLE `mapping_session` (
417
  `mapping_session_id` int(11) NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
418 419 420 421 422 423
  `old_db_name` varchar(80) COLLATE latin1_bin NOT NULL DEFAULT '',
  `new_db_name` varchar(80) COLLATE latin1_bin NOT NULL DEFAULT '',
  `old_release` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT '',
  `new_release` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT '',
  `old_assembly` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT '',
  `new_assembly` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT '',
424
  `created` datetime DEFAULT NULL,
425
  PRIMARY KEY (`mapping_session_id`)
426
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
427 428 429 430 431 432 433

CREATE TABLE `mapping_set` (
  `mapping_set_id` int(10) unsigned NOT NULL,
  `internal_schema_build` varchar(20) NOT NULL,
  `external_schema_build` varchar(20) NOT NULL,
  PRIMARY KEY (`mapping_set_id`),
  UNIQUE KEY `mapping_idx` (`internal_schema_build`,`external_schema_build`)
434
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
435 436 437 438

CREATE TABLE `marker` (
  `marker_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `display_marker_synonym_id` int(10) unsigned DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
439 440
  `left_primer` varchar(100) COLLATE latin1_bin NOT NULL DEFAULT '',
  `right_primer` varchar(100) COLLATE latin1_bin NOT NULL DEFAULT '',
441 442
  `min_primer_dist` int(10) unsigned NOT NULL DEFAULT '0',
  `max_primer_dist` int(10) unsigned NOT NULL DEFAULT '0',
443
  `priority` int(11) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
444
  `type` enum('est','microsatellite') COLLATE latin1_bin DEFAULT NULL,
445
  PRIMARY KEY (`marker_id`),
Magali Ruffier's avatar
Magali Ruffier committed
446 447
  KEY `marker_idx` (`marker_id`,`priority`),
  KEY `display_idx` (`display_marker_synonym_id`)
448
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
449 450 451

CREATE TABLE `marker_feature` (
  `marker_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
452 453 454 455 456
  `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',
457 458 459 460
  `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`)
461
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
462 463

CREATE TABLE `marker_map_location` (
464 465
  `marker_id` int(10) unsigned NOT NULL DEFAULT '0',
  `map_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
466
  `chromosome_name` varchar(15) COLLATE latin1_bin NOT NULL DEFAULT '',
467
  `marker_synonym_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
468
  `position` varchar(15) COLLATE latin1_bin NOT NULL DEFAULT '',
469 470 471
  `lod_score` double DEFAULT NULL,
  PRIMARY KEY (`marker_id`,`map_id`),
  KEY `map_idx` (`map_id`,`chromosome_name`,`position`)
472
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
473 474 475

CREATE TABLE `marker_synonym` (
  `marker_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
476
  `marker_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
477 478
  `source` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `name` varchar(30) COLLATE latin1_bin DEFAULT NULL,
479 480 481
  PRIMARY KEY (`marker_synonym_id`),
  KEY `marker_synonym_idx` (`marker_synonym_id`,`name`),
  KEY `marker_idx` (`marker_id`)
482
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
483 484 485 486 487

CREATE TABLE `meta` (
  `meta_id` int(11) NOT NULL AUTO_INCREMENT,
  `species_id` int(10) unsigned DEFAULT '1',
  `meta_key` varchar(40) NOT NULL,
488
  `meta_value` varchar(255) NOT NULL,
489 490 491
  PRIMARY KEY (`meta_id`),
  UNIQUE KEY `species_key_value_idx` (`species_id`,`meta_key`,`meta_value`),
  KEY `species_value_idx` (`species_id`,`meta_value`)
492
) ENGINE=MyISAM AUTO_INCREMENT=239 DEFAULT CHARSET=latin1;
493 494

CREATE TABLE `meta_coord` (
Magali Ruffier's avatar
Magali Ruffier committed
495
  `table_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
496
  `coord_system_id` int(11) NOT NULL DEFAULT '0',
497
  `max_length` int(11) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
498
  UNIQUE KEY `cs_table_name_idx` (`coord_system_id`,`table_name`)
499
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
500 501 502 503

CREATE TABLE `misc_attrib` (
  `misc_feature_id` int(10) unsigned NOT NULL DEFAULT '0',
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
504
  `value` text COLLATE latin1_bin NOT NULL,
505 506
  UNIQUE KEY `misc_attribx` (`misc_feature_id`,`attrib_type_id`,`value`(500)),
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
Magali Ruffier's avatar
Magali Ruffier committed
507
  KEY `val_only_idx` (`value`(40)),
508 509
  KEY `misc_feature_idx` (`misc_feature_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
510 511 512 513 514 515 516 517 518

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`)
519
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
520 521 522 523 524 525

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`)
526
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
527 528 529

CREATE TABLE `misc_set` (
  `misc_set_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
530 531 532
  `code` varchar(25) COLLATE latin1_bin NOT NULL DEFAULT '',
  `name` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
  `description` text COLLATE latin1_bin NOT NULL,
533
  `max_length` int(10) unsigned NOT NULL DEFAULT '0',
534
  PRIMARY KEY (`misc_set_id`),
Magali Ruffier's avatar
Magali Ruffier committed
535
  UNIQUE KEY `code_idx` (`code`)
Magali Ruffier's avatar
Magali Ruffier committed
536
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
537 538

CREATE TABLE `object_xref` (
539 540
  `object_xref_id` int(11) NOT NULL AUTO_INCREMENT,
  `ensembl_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
541
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation','regulatory_factor','regulatory_feature','Marker') COLLATE latin1_bin NOT NULL DEFAULT 'RawContig',
542
  `xref_id` int(10) unsigned NOT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
543
  `linkage_annotation` varchar(255) COLLATE latin1_bin DEFAULT NULL,
544
  `analysis_id` smallint(5) unsigned DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
545
  PRIMARY KEY (`object_xref_id`),
Magali Ruffier's avatar
Magali Ruffier committed
546 547
  UNIQUE KEY `xref_idx` (`xref_id`,`ensembl_object_type`,`ensembl_id`,`analysis_id`),
  KEY `ensembl_idx` (`ensembl_object_type`,`ensembl_id`),
Magali Ruffier's avatar
Magali Ruffier committed
548 549
  KEY `analysis_idx` (`analysis_id`)
) ENGINE=MyISAM AUTO_INCREMENT=81424 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
550 551 552 553

CREATE TABLE `ontology_xref` (
  `object_xref_id` int(10) unsigned NOT NULL DEFAULT '0',
  `linkage_type` varchar(3) DEFAULT NULL,
554
  `source_xref_id` int(10) unsigned DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
555 556 557
  UNIQUE KEY `object_source_type_idx` (`object_xref_id`,`source_xref_id`,`linkage_type`),
  KEY `object_idx` (`object_xref_id`),
  KEY `source_idx` (`source_xref_id`)
558
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
559 560 561 562 563 564 565 566 567 568

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,
Magali Ruffier's avatar
Magali Ruffier committed
569
  `version` smallint(5) unsigned DEFAULT NULL,
570 571
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
572 573 574 575
  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`)
576
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
577 578 579 580 581 582 583 584 585 586 587

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,
Magali Ruffier's avatar
Magali Ruffier committed
588
  `version` smallint(5) unsigned DEFAULT NULL,
589 590
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
591 592 593 594
  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`)
595
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
596 597 598 599 600

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

CREATE TABLE `peptide_archive` (
604
  `peptide_archive_id` int(11) NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
605 606
  `md5_checksum` varchar(32) COLLATE latin1_bin DEFAULT NULL,
  `peptide_seq` mediumtext COLLATE latin1_bin NOT NULL,
607 608
  PRIMARY KEY (`peptide_archive_id`),
  KEY `checksum` (`md5_checksum`)
609
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
610 611 612

CREATE TABLE `prediction_exon` (
  `prediction_exon_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
613 614 615 616 617 618 619
  `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',
620 621 622
  `score` double DEFAULT NULL,
  `p_value` double DEFAULT NULL,
  PRIMARY KEY (`prediction_exon_id`),
623 624 625
  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;
626 627 628

CREATE TABLE `prediction_transcript` (
  `prediction_transcript_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
629 630 631 632 633
  `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,
Magali Ruffier's avatar
Magali Ruffier committed
634
  `display_label` varchar(255) COLLATE latin1_bin DEFAULT NULL,
635
  PRIMARY KEY (`prediction_transcript_id`),
Magali Ruffier's avatar
Magali Ruffier committed
636
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
637 638
  KEY `analysis_idx` (`analysis_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
639 640 641

CREATE TABLE `protein_align_feature` (
  `protein_align_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
642 643 644
  `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',
645
  `seq_region_strand` tinyint(1) NOT NULL DEFAULT '1',
646 647
  `hit_start` int(10) NOT NULL DEFAULT '0',
  `hit_end` int(10) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
648
  `hit_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
649
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
650 651 652
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
653 654
  `cigar_line` text COLLATE latin1_bin,
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
655
  `hcoverage` double DEFAULT NULL,
656
  `align_type` enum('ensembl','cigar','vulgar','mdtag') COLLATE latin1_bin DEFAULT 'ensembl',
657 658 659 660 661 662
  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`)
663
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
664 665 666

CREATE TABLE `protein_feature` (
  `protein_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
667 668 669 670 671
  `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',
672
  `hit_name` varchar(40) COLLATE latin1_bin NOT NULL,
673 674
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double NOT NULL DEFAULT '0',
675 676
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
677 678
  `external_data` text COLLATE latin1_bin,
  `hit_description` text COLLATE latin1_bin,
679 680
  `cigar_line` text COLLATE latin1_bin,
  `align_type` enum('ensembl','cigar','cigarplus','vulgar','mdtag') COLLATE latin1_bin DEFAULT NULL,
681
  PRIMARY KEY (`protein_feature_id`),
682
  UNIQUE KEY `aln_idx` (`translation_id`,`hit_name`,`seq_start`,`seq_end`,`hit_start`,`hit_end`,`analysis_id`),
Magali Ruffier's avatar
Magali Ruffier committed
683 684
  KEY `translation_idx` (`translation_id`),
  KEY `hitname_idx` (`hit_name`),
685
  KEY `analysis_idx` (`analysis_id`)
Magali Ruffier's avatar
Magali Ruffier committed
686
) ENGINE=MyISAM AUTO_INCREMENT=3502933 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
687 688 689

CREATE TABLE `repeat_consensus` (
  `repeat_consensus_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
690 691 692
  `repeat_name` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
  `repeat_class` varchar(100) COLLATE latin1_bin NOT NULL DEFAULT '',
  `repeat_type` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
693
  `repeat_consensus` text COLLATE latin1_bin,
694 695 696 697 698
  PRIMARY KEY (`repeat_consensus_id`),
  KEY `name` (`repeat_name`),
  KEY `class` (`repeat_class`),
  KEY `consensus` (`repeat_consensus`(10)),
  KEY `type` (`repeat_type`)
Magali Ruffier's avatar
Magali Ruffier committed
699
) ENGINE=MyISAM AUTO_INCREMENT=1163919 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
700 701 702

CREATE TABLE `repeat_feature` (
  `repeat_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
703 704 705
  `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',
706
  `seq_region_strand` tinyint(1) NOT NULL DEFAULT '1',
707 708 709 710
  `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',
711 712 713 714 715
  `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`)
Magali Ruffier's avatar
Magali Ruffier committed
716
) ENGINE=MyISAM AUTO_INCREMENT=10446798 DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
717 718 719

CREATE TABLE `seq_region` (
  `seq_region_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
720
  `name` varchar(255) COLLATE latin1_bin NOT NULL,
721 722
  `coord_system_id` int(10) NOT NULL DEFAULT '0',
  `length` int(10) NOT NULL DEFAULT '0',
723
  PRIMARY KEY (`seq_region_id`),
Magali Ruffier's avatar
Magali Ruffier committed
724 725
  UNIQUE KEY `name_cs_idx` (`name`,`coord_system_id`),
  KEY `cs_idx` (`coord_system_id`)
Magali Ruffier's avatar
Magali Ruffier committed
726
) ENGINE=MyISAM AUTO_INCREMENT=3495795 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
727 728 729 730

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',
Magali Ruffier's avatar
Magali Ruffier committed
731
  `value` text COLLATE latin1_bin NOT NULL,
732 733
  UNIQUE KEY `region_attribx` (`seq_region_id`,`attrib_type_id`,`value`(500)),
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
Magali Ruffier's avatar
Magali Ruffier committed
734
  KEY `val_only_idx` (`value`(40)),
735 736
  KEY `seq_region_idx` (`seq_region_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
737 738 739 740 741

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,
Magali Ruffier's avatar
Magali Ruffier committed
742
  KEY `mapping_set_idx` (`mapping_set_id`)
743
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
744 745 746 747

CREATE TABLE `seq_region_synonym` (
  `seq_region_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
748
  `synonym` varchar(250) NOT NULL,
749
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
750
  PRIMARY KEY (`seq_region_synonym_id`),
Magali Ruffier's avatar
Magali Ruffier committed
751 752
  UNIQUE KEY `syn_idx` (`synonym`,`seq_region_id`),
  KEY `seq_region_idx` (`seq_region_id`)
753
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
754 755 756

CREATE TABLE `simple_feature` (
  `simple_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
757 758 759 760
  `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',
Magali Ruffier's avatar
Magali Ruffier committed
761
  `display_label` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
762
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
763 764 765 766 767
  `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`)
Magali Ruffier's avatar
Magali Ruffier committed
768
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
769 770

CREATE TABLE `stable_id_event` (
Magali Ruffier's avatar
Magali Ruffier committed
771
  `old_stable_id` varchar(128) COLLATE latin1_bin DEFAULT NULL,
772
  `old_version` smallint(6) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
773
  `new_stable_id` varchar(128) COLLATE latin1_bin DEFAULT NULL,
774
  `new_version` smallint(6) DEFAULT NULL,
775
  `mapping_session_id` int(10) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
776
  `type` enum('gene','transcript','translation') COLLATE latin1_bin NOT NULL DEFAULT 'gene',
777
  `score` float NOT NULL DEFAULT '0',
778
  UNIQUE KEY `uni_idx` (`mapping_session_id`,`old_stable_id`,`old_version`,`new_stable_id`,`new_version`,`type`),
779 780
  KEY `new_idx` (`new_stable_id`),
  KEY `old_idx` (`old_stable_id`)
781
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
782 783

CREATE TABLE `supporting_feature` (
784
  `exon_id` int(11) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
785
  `feature_type` enum('dna_align_feature','protein_align_feature') COLLATE latin1_bin DEFAULT NULL,
786
  `feature_id` int(11) NOT NULL DEFAULT '0',
787 788
  UNIQUE KEY `all_idx` (`exon_id`,`feature_type`,`feature_id`),
  KEY `feature_idx` (`feature_type`,`feature_id`)
789
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;