table.sql 40.9 KB
Newer Older
1
CREATE TABLE `alt_allele` (
2
3
4
5
  `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`),
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
22

CREATE TABLE `analysis` (
  `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=1504 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
39
40
41

CREATE TABLE `analysis_description` (
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
42
  `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
50
51
52
53
54
55
56
57
58
59

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`)
60
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
61
62
63
64
65
66

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',
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
72
73
74
  `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`)
Magali Ruffier's avatar
Magali Ruffier committed
75
) ENGINE=MyISAM AUTO_INCREMENT=3 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
106
  UNIQUE KEY `code_idx` (`code`)
) ENGINE=MyISAM AUTO_INCREMENT=391 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
107
108
109
110
111
112
113
114
115
116
117
118

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
119
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
120
121

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

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`)
146
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
147
148
149
150
151
152

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',
Magali Ruffier's avatar
Magali Ruffier committed
153
  `value_type` enum('sum','ratio') COLLATE latin1_bin NOT NULL DEFAULT 'sum',
154
155
  PRIMARY KEY (`density_type_id`),
  UNIQUE KEY `analysis_id` (`analysis_id`,`block_size`,`region_features`)
156
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
157
158
159
160
161
162
163
164

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`)
165
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
166
167
168
169
170
171
172
173

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`)
174
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191

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`),
Magali Ruffier's avatar
Magali Ruffier committed
192
  KEY `ditag_pair_id` (`ditag_pair_id`),
Magali Ruffier's avatar
Magali Ruffier committed
193
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`)
194
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
195
196
197

CREATE TABLE `dna` (
  `seq_region_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
198
  `sequence` mediumtext COLLATE latin1_bin 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
204
205
206
207
208
209
210

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',
Magali Ruffier's avatar
Magali Ruffier committed
211
  `hit_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
212
213
214
215
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
216
  `cigar_line` text COLLATE latin1_bin,
217
218
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
  `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
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;

238
239
240
241
242
243
244
245
246
247
248
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
249
  `version` smallint(5) unsigned DEFAULT NULL,
250
251
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
252
253
254
  PRIMARY KEY (`exon_id`),
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `stable_id_idx` (`stable_id`,`version`)
Matthew Laird's avatar
Matthew Laird committed
255
) ENGINE=MyISAM AUTO_INCREMENT=6894 DEFAULT CHARSET=latin1;
256
257
258
259
260
261
262
263

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`)
264
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
265
266
267

CREATE TABLE `external_db` (
  `external_db_id` int(11) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
268
269
270
  `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',
271
  `priority` int(11) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
272
273
274
275
  `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,
276
  `description` text COLLATE latin1_bin,
Magali Ruffier's avatar
Magali Ruffier committed
277
278
  PRIMARY KEY (`external_db_id`),
  UNIQUE KEY `db_name_db_release_idx` (`db_name`,`db_release`)
279
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
280
281
282

CREATE TABLE `external_synonym` (
  `xref_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
283
  `synonym` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
284
285
  PRIMARY KEY (`xref_id`,`synonym`),
  KEY `name_index` (`synonym`)
286
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
287
288
289
290
291
292
293
294
295
296

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,
297
  `source` varchar(40) NOT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
298
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN','ANNOTATED') DEFAULT NULL,
299
300
301
302
  `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
303
  `version` smallint(5) unsigned DEFAULT NULL,
304
305
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
306
307
308
309
  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
310
311
  KEY `stable_id_idx` (`stable_id`,`version`),
  KEY `canonical_transcript_id_idx` (`canonical_transcript_id`)
Matthew Laird's avatar
Matthew Laird committed
312
) ENGINE=MyISAM AUTO_INCREMENT=6886 DEFAULT CHARSET=latin1;
313
314

CREATE TABLE `gene_archive` (
Magali Ruffier's avatar
Magali Ruffier committed
315
  `gene_stable_id` varchar(128) COLLATE latin1_bin NOT NULL DEFAULT '',
316
  `gene_version` smallint(6) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
317
  `transcript_stable_id` varchar(128) COLLATE latin1_bin NOT NULL DEFAULT '',
318
  `transcript_version` smallint(6) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
319
  `translation_stable_id` varchar(128) COLLATE latin1_bin NOT NULL DEFAULT '',
320
321
322
323
324
  `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`),
Magali Ruffier's avatar
Magali Ruffier committed
325
326
  KEY `translation_idx` (`translation_stable_id`,`translation_version`),
  KEY `peptide_archive_id_idx` (`peptide_archive_id`)
327
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
328
329
330
331

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
332
  `value` text COLLATE latin1_bin NOT NULL,
333
334
  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
335
  KEY `val_only_idx` (`value`(40)),
336
  KEY `gene_idx` (`gene_id`)
337
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
338
339
340
341

CREATE TABLE `genome_statistics` (
  `genome_statistics_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `statistic` varchar(128) NOT NULL,
342
  `value` bigint(11) unsigned NOT NULL DEFAULT '0',
343
344
  `species_id` int(10) unsigned DEFAULT '1',
  `attrib_type_id` int(10) unsigned DEFAULT NULL,
345
  `timestamp` datetime DEFAULT NULL,
346
  PRIMARY KEY (`genome_statistics_id`),
Magali Ruffier's avatar
Magali Ruffier committed
347
  UNIQUE KEY `stats_uniq` (`statistic`,`attrib_type_id`,`species_id`)
348
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
349
350
351
352
353
354
355
356
357

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,
358
  `cigar_line` text COLLATE latin1_bin,
359
360
361
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  PRIMARY KEY (`object_xref_id`)
362
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
363
364

CREATE TABLE `interpro` (
Magali Ruffier's avatar
Magali Ruffier committed
365
366
  `interpro_ac` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
  `id` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
Magali Ruffier's avatar
Magali Ruffier committed
367
368
  UNIQUE KEY `accession_idx` (`interpro_ac`,`id`),
  KEY `id_idx` (`id`)
369
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
370
371
372
373
374
375
376
377
378
379
380
381
382

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`),
383
384
  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`)
Matthew Laird's avatar
Matthew Laird committed
385
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
386
387
388
389
390
391

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',
Magali Ruffier's avatar
Magali Ruffier committed
392
393
  `band` varchar(40) COLLATE latin1_bin DEFAULT NULL,
  `stain` varchar(40) COLLATE latin1_bin DEFAULT NULL,
394
395
  PRIMARY KEY (`karyotype_id`),
  KEY `region_band_idx` (`seq_region_id`,`band`)
396
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
397
398
399

CREATE TABLE `map` (
  `map_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
400
  `map_name` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT '',
401
  PRIMARY KEY (`map_id`)
402
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
403
404
405

CREATE TABLE `mapping_session` (
  `mapping_session_id` int(11) NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
406
407
408
409
410
411
  `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 '',
412
  `created` datetime DEFAULT NULL,
413
  PRIMARY KEY (`mapping_session_id`)
414
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
415
416
417

CREATE TABLE `mapping_set` (
  `mapping_set_id` int(10) unsigned NOT NULL,
418
419
  `internal_schema_build` varchar(20) NOT NULL,
  `external_schema_build` varchar(20) NOT NULL,
Andy Yates's avatar
Andy Yates committed
420
  PRIMARY KEY (`mapping_set_id`),
421
  UNIQUE KEY `mapping_idx` (`internal_schema_build`,`external_schema_build`)
422
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
423
424
425
426

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
427
428
  `left_primer` varchar(100) COLLATE latin1_bin NOT NULL DEFAULT '',
  `right_primer` varchar(100) COLLATE latin1_bin NOT NULL DEFAULT '',
429
430
431
  `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,
Magali Ruffier's avatar
Magali Ruffier committed
432
  `type` enum('est','microsatellite') COLLATE latin1_bin DEFAULT NULL,
433
  PRIMARY KEY (`marker_id`),
Magali Ruffier's avatar
Magali Ruffier committed
434
435
  KEY `marker_idx` (`marker_id`,`priority`),
  KEY `display_idx` (`display_marker_synonym_id`)
436
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
437
438
439
440
441
442
443
444
445
446
447
448

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`)
449
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
450
451
452
453

CREATE TABLE `marker_map_location` (
  `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
454
  `chromosome_name` varchar(15) COLLATE latin1_bin NOT NULL DEFAULT '',
455
  `marker_synonym_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
456
  `position` varchar(15) COLLATE latin1_bin NOT NULL DEFAULT '',
457
458
459
  `lod_score` double DEFAULT NULL,
  PRIMARY KEY (`marker_id`,`map_id`),
  KEY `map_idx` (`map_id`,`chromosome_name`,`position`)
460
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
461
462
463
464

CREATE TABLE `marker_synonym` (
  `marker_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `marker_id` int(10) unsigned NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
465
466
  `source` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `name` varchar(30) COLLATE latin1_bin DEFAULT NULL,
467
468
469
  PRIMARY KEY (`marker_synonym_id`),
  KEY `marker_synonym_idx` (`marker_synonym_id`,`name`),
  KEY `marker_idx` (`marker_id`)
470
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
471
472
473
474
475
476
477
478
479

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`)
Magali Ruffier's avatar
Magali Ruffier committed
480
) ENGINE=MyISAM AUTO_INCREMENT=135 DEFAULT CHARSET=latin1;
481
482

CREATE TABLE `meta_coord` (
Magali Ruffier's avatar
Magali Ruffier committed
483
  `table_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
484
485
  `coord_system_id` int(11) NOT NULL DEFAULT '0',
  `max_length` int(11) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
486
  UNIQUE KEY `cs_table_name_idx` (`coord_system_id`,`table_name`)
487
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
488
489
490
491

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
492
  `value` text COLLATE latin1_bin NOT NULL,
493
494
  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
495
  KEY `val_only_idx` (`value`(40)),
496
  KEY `misc_feature_idx` (`misc_feature_id`)
497
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
498
499
500
501
502
503
504
505
506

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`)
507
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
508
509
510
511
512
513

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`)
514
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
515
516
517

CREATE TABLE `misc_set` (
  `misc_set_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
518
519
520
  `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,
521
522
  `max_length` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`misc_set_id`),
Magali Ruffier's avatar
Magali Ruffier committed
523
  UNIQUE KEY `code_idx` (`code`)
524
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
525
526
527
528

CREATE TABLE `object_xref` (
  `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
529
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation','regulatory_factor','regulatory_feature','Marker') COLLATE latin1_bin NOT NULL DEFAULT 'RawContig',
530
  `xref_id` int(10) unsigned NOT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
531
  `linkage_annotation` varchar(255) COLLATE latin1_bin DEFAULT NULL,
532
  `analysis_id` smallint(5) unsigned NOT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
533
  PRIMARY KEY (`object_xref_id`),
Magali Ruffier's avatar
Magali Ruffier committed
534
535
  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
536
537
  KEY `analysis_idx` (`analysis_id`)
) ENGINE=MyISAM AUTO_INCREMENT=81424 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
538
539
540
541
542

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,
Magali Ruffier's avatar
Magali Ruffier committed
543
544
545
  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`)
546
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
547
548
549
550
551
552
553
554
555
556

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
557
  `version` smallint(5) unsigned DEFAULT NULL,
558
559
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
560
561
562
563
  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`)
564
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
565
566
567
568
569
570
571
572
573
574
575

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
576
  `version` smallint(5) unsigned DEFAULT NULL,
577
578
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
579
580
581
582
  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`)
583
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
584
585
586
587
588

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

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

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`)
613
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
614
615
616
617
618
619
620
621

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,
Magali Ruffier's avatar
Magali Ruffier committed
622
  `display_label` varchar(255) COLLATE latin1_bin DEFAULT NULL,
623
  PRIMARY KEY (`prediction_transcript_id`),
Magali Ruffier's avatar
Magali Ruffier committed
624
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
625
  KEY `analysis_idx` (`analysis_id`)
626
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
627
628
629
630
631
632
633
634
635

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',
Magali Ruffier's avatar
Magali Ruffier committed
636
  `hit_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
637
638
639
640
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double DEFAULT NULL,
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
641
  `cigar_line` text COLLATE latin1_bin,
642
643
644
645
646
647
648
649
  `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`)
650
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
651
652
653
654
655
656
657
658

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',
Magali Ruffier's avatar
Magali Ruffier committed
659
  `hit_name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
660
661
662
663
  `analysis_id` int(10) unsigned NOT NULL DEFAULT '0',
  `score` double NOT NULL DEFAULT '0',
  `evalue` double DEFAULT NULL,
  `perc_ident` float DEFAULT NULL,
664
665
  `external_data` text COLLATE latin1_bin,
  `hit_description` text COLLATE latin1_bin,
666
  PRIMARY KEY (`protein_feature_id`),
Magali Ruffier's avatar
Magali Ruffier committed
667
  UNIQUE KEY `aln_idx` (`translation_id`,`hit_name`,`seq_start`,`seq_end`,`hit_start`,`hit_end`),
Magali Ruffier's avatar
Magali Ruffier committed
668
669
  KEY `translation_idx` (`translation_id`),
  KEY `hitname_idx` (`hit_name`),
670
  KEY `analysis_idx` (`analysis_id`)
Magali Ruffier's avatar
Magali Ruffier committed
671
) ENGINE=MyISAM AUTO_INCREMENT=24117 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
672
673
674

CREATE TABLE `repeat_consensus` (
  `repeat_consensus_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
675
676
677
  `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 '',
678
  `repeat_consensus` text COLLATE latin1_bin,
679
680
681
682
683
  PRIMARY KEY (`repeat_consensus_id`),
  KEY `name` (`repeat_name`),
  KEY `class` (`repeat_class`),
  KEY `consensus` (`repeat_consensus`(10)),
  KEY `type` (`repeat_type`)
684
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700

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`)
701
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
702
703
704

CREATE TABLE `seq_region` (
  `seq_region_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
705
  `name` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
706
707
708
  `coord_system_id` int(10) NOT NULL DEFAULT '0',
  `length` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seq_region_id`),
Magali Ruffier's avatar
Magali Ruffier committed
709
710
  UNIQUE KEY `name_cs_idx` (`name`,`coord_system_id`),
  KEY `cs_idx` (`coord_system_id`)
Magali Ruffier's avatar
Magali Ruffier committed
711
) ENGINE=MyISAM AUTO_INCREMENT=965907 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
712
713
714
715

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
716
  `value` text COLLATE latin1_bin NOT NULL,
717
718
  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
719
  KEY `val_only_idx` (`value`(40)),
720
  KEY `seq_region_idx` (`seq_region_id`)
721
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
722
723
724
725
726

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
727
  KEY `mapping_set_idx` (`mapping_set_id`)
728
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
729
730
731
732

CREATE TABLE `seq_region_synonym` (
  `seq_region_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
733
  `synonym` varchar(250) NOT NULL,
734
735
  `external_db_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`seq_region_synonym_id`),
Magali Ruffier's avatar
Magali Ruffier committed
736
737
  UNIQUE KEY `syn_idx` (`synonym`,`seq_region_id`),
  KEY `seq_region_idx` (`seq_region_id`)
Magali Ruffier's avatar
Magali Ruffier committed
738
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
739
740
741
742
743
744
745

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',
Magali Ruffier's avatar
Magali Ruffier committed
746
  `display_label` varchar(40) COLLATE latin1_bin NOT NULL DEFAULT '',
747
748
749
750
751
752
  `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`)
Matthew Laird's avatar
Matthew Laird committed
753
) ENGINE=MyISAM AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
754
755

CREATE TABLE `stable_id_event` (
Magali Ruffier's avatar
Magali Ruffier committed
756
  `old_stable_id` varchar(128) COLLATE latin1_bin DEFAULT NULL,
757
  `old_version` smallint(6) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
758
  `new_stable_id` varchar(128) COLLATE latin1_bin DEFAULT NULL,
759
760
  `new_version` smallint(6) DEFAULT NULL,
  `mapping_session_id` int(10) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
761
  `type` enum('gene','transcript','translation') COLLATE latin1_bin NOT NULL DEFAULT 'gene',
762
763
764
765
  `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`)
766
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
767
768
769

CREATE TABLE `supporting_feature` (
  `exon_id` int(11) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
770
  `feature_type` enum('dna_align_feature','protein_align_feature') COLLATE latin1_bin DEFAULT NULL,
771
772
773
  `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`)
774
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
775
776
777
778
779
780
781
782
783
784

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,
785
  `source` varchar(40) NOT NULL DEFAULT 'ensembl',
786
  `biotype` varchar(40) NOT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
787
  `status` enum('KNOWN','NOVEL','PUTATIVE','PREDICTED','KNOWN_BY_PROJECTION','UNKNOWN','ANNOTATED') DEFAULT NULL,
788
789
790
791
  `description` text,
  `is_current` tinyint(1) NOT NULL DEFAULT '1',
  `canonical_translation_id` int(10) unsigned DEFAULT NULL,
  `stable_id` varchar(128) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
792
  `version` smallint(5) unsigned DEFAULT NULL,
793
794
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
795
796
797
798
799
800
801
  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`)
Matthew Laird's avatar
Matthew Laird committed
802
) ENGINE=MyISAM AUTO_INCREMENT=6887 DEFAULT CHARSET=latin1;
803
804
805
806

CREATE TABLE `transcript_attrib` (
  `transcript_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
807
  `value` text COLLATE latin1_bin NOT NULL,
808
809
  UNIQUE KEY `transcript_attribx` (`transcript_id`,`attrib_type_id`,`value`(500)),
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
Magali Ruffier's avatar
Magali Ruffier committed
810
  KEY `val_only_idx` (`value`(40)),
811
  KEY `transcript_idx` (`transcript_id`)
812
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
813
814
815
816
817
818

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,
819
820
  PRIMARY KEY (`intron_supporting_evidence_id`,`transcript_id`),
  KEY `transcript_idx` (`transcript_id`)
821
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
822
823
824

CREATE TABLE `transcript_supporting_feature` (
  `transcript_id` int(11) NOT NULL DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
825
  `feature_type` enum('dna_align_feature','protein_align_feature') COLLATE latin1_bin DEFAULT NULL,
826
827
828
  `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`)
829
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin MAX_ROWS=100000000 AVG_ROW_LENGTH=80;
830
831
832
833
834
835
836
837
838

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,
Magali Ruffier's avatar
Magali Ruffier committed
839
  `version` smallint(5) unsigned DEFAULT NULL,
840
841
  `created_date` datetime DEFAULT NULL,
  `modified_date` datetime DEFAULT NULL,
842
843
844
  PRIMARY KEY (`translation_id`),
  KEY `transcript_idx` (`transcript_id`),
  KEY `stable_id_idx` (`stable_id`,`version`)
Magali Ruffier's avatar
Magali Ruffier committed
845
) ENGINE=MyISAM AUTO_INCREMENT=6690 DEFAULT CHARSET=latin1;
846
847
848
849

CREATE TABLE `translation_attrib` (
  `translation_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
850
  `value` text COLLATE latin1_bin NOT NULL,
851
852
  UNIQUE KEY `translation_attribx` (`translation_id`,`attrib_type_id`,`value`(500)),
  KEY `type_val_idx` (`attrib_type_id`,`value`(40)),
Magali Ruffier's avatar
Magali Ruffier committed
853
  KEY `val_only_idx` (`value`(40)),
854
  KEY `translation_idx` (`translation_id`)
855
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
856
857
858

CREATE TABLE `unmapped_object` (
  `unmapped_object_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
859
  `type` enum('xref','cDNA','Marker') COLLATE latin1_bin NOT NULL,
860
861
  `analysis_id` int(10) unsigned NOT NULL,
  `external_db_id` int(11) DEFAULT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
862
  `identifier` varchar(255) COLLATE latin1_bin NOT NULL,
863
  `unmapped_reason_id` int(10) unsigned NOT NULL,
864
865
866
  `query_score` double DEFAULT NULL,
  `target_score` double DEFAULT NULL,
  `ensembl_id` int(10) unsigned DEFAULT '0',
Magali Ruffier's avatar
Magali Ruffier committed
867
868
  `ensembl_object_type` enum('RawContig','Transcript','Gene','Translation') COLLATE latin1_bin DEFAULT 'RawContig',
  `parent` varchar(255) COLLATE latin1_bin DEFAULT NULL,
869
  PRIMARY KEY (`unmapped_object_id`),
Magali Ruffier's avatar
Magali Ruffier committed
870
  UNIQUE KEY `unique_unmapped_obj_idx` (`ensembl_id`,`ensembl_object_type`,`identifier`,`unmapped_reason_id`,`parent`,`external_db_id`),
Magali Ruffier's avatar
Magali Ruffier committed
871
  KEY `id_idx` (`identifier`(50)),
Magali Ruffier's avatar
Magali Ruffier committed
872
  KEY `ext_db_identifier_idx` (`external_db_id`,`identifier`),
873
  KEY `anal_exdb_idx` (`analysis_id`,`external_db_id`)
874
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
875
876

CREATE TABLE `unmapped_reason` (
877
  `unmapped_reason_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
Magali Ruffier's avatar
Magali Ruffier committed
878
879
  `summary_description` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `full_description` varchar(255) COLLATE latin1_bin DEFAULT NULL,
880
  PRIMARY KEY (`unmapped_reason_id`)
Magali Ruffier's avatar
Magali Ruffier committed
881
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
882
883
884

CREATE TABLE `xref` (
  `xref_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
885
  `external_db_id` int(11) NOT NULL,
Magali Ruffier's avatar
Magali Ruffier committed
886
887
888
  `dbprimary_acc` varchar(512) COLLATE latin1_bin NOT NULL,
  `display_label` varchar(512) COLLATE latin1_bin NOT NULL,
  `version` varchar(10) COLLATE latin1_bin DEFAULT NULL,
889
  `description` text COLLATE latin1_bin,
Magali Ruffier's avatar
Magali Ruffier committed
890
891
  `info_type` enum('NONE','PROJECTION','MISC','DEPENDENT','DIRECT','SEQUENCE_MATCH','INFERRED_PAIR','PROBE','UNMAPPED','CHECKSUM') COLLATE latin1_bin NOT NULL DEFAULT 'NONE',
  `info_text` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
892
  PRIMARY KEY (`xref_id`),
893
  UNIQUE KEY `id_index` (`dbprimary_acc`,`external_db_id`,`info_type`,`info_text`,`version`),
894
895
  KEY `display_index` (`display_label`),
  KEY `info_type_idx` (`info_type`)
Magali Ruffier's avatar
Magali Ruffier committed
896
) ENGINE=MyISAM AUTO_INCREMENT=91703 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
897