table.sql 31.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13
CREATE TABLE `allele` (
  `allele_id` int(11) NOT NULL AUTO_INCREMENT,
  `variation_id` int(11) unsigned NOT NULL,
  `subsnp_id` int(11) unsigned DEFAULT NULL,
  `allele_code_id` int(11) unsigned NOT NULL,
  `population_id` int(11) unsigned DEFAULT NULL,
  `frequency` float unsigned DEFAULT NULL,
  `count` int(11) unsigned DEFAULT NULL,
  `frequency_submitter_handle` int(10) DEFAULT NULL,
  PRIMARY KEY (`allele_id`),
  KEY `variation_idx` (`variation_id`),
  KEY `subsnp_idx` (`subsnp_id`),
  KEY `population_idx` (`population_id`)
14
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
15 16 17

CREATE TABLE `allele_code` (
  `allele_code_id` int(11) NOT NULL AUTO_INCREMENT,
18
  `allele` varchar(60000) DEFAULT NULL,
19
  PRIMARY KEY (`allele_code_id`),
20
  UNIQUE KEY `allele_idx` (`allele`(1000))
Anja Thormann's avatar
Anja Thormann committed
21
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
22

23 24 25 26 27 28 29 30 31 32
CREATE TABLE `allele_synonym` (
  `allele_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `variation_id` int(10) unsigned NOT NULL,
  `hgvs_genomic` varchar(600) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`allele_synonym_id`),
  UNIQUE KEY `variation_name_idx` (`variation_id`,`name`),
  KEY `name_idx` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

33 34 35 36
CREATE TABLE `associate_study` (
  `study1_id` int(10) unsigned NOT NULL,
  `study2_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`study1_id`,`study2_id`)
Anja Thormann's avatar
Anja Thormann committed
37
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
38

39
CREATE TABLE `attrib` (
Magali Ruffier's avatar
Magali Ruffier committed
40
  `attrib_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
41
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT 0,
42 43 44
  `value` text NOT NULL,
  PRIMARY KEY (`attrib_id`),
  UNIQUE KEY `type_val_idx` (`attrib_type_id`,`value`(80))
45
) ENGINE=MyISAM AUTO_INCREMENT=422 DEFAULT CHARSET=latin1;
46

47
CREATE TABLE `attrib_set` (
48 49
  `attrib_set_id` int(11) unsigned NOT NULL DEFAULT 0,
  `attrib_id` int(11) unsigned NOT NULL DEFAULT 0,
50 51
  UNIQUE KEY `set_idx` (`attrib_set_id`,`attrib_id`),
  KEY `attrib_idx` (`attrib_id`)
Anja Thormann's avatar
Anja Thormann committed
52
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
53

54
CREATE TABLE `attrib_type` (
55
  `attrib_type_id` smallint(5) unsigned NOT NULL DEFAULT 0,
56 57
  `code` varchar(20) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL DEFAULT '',
58
  `description` text DEFAULT NULL,
59 60
  PRIMARY KEY (`attrib_type_id`),
  UNIQUE KEY `code_idx` (`code`)
Anja Thormann's avatar
Anja Thormann committed
61
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
62

63 64 65 66 67 68
CREATE TABLE `compressed_genotype_region` (
  `sample_id` int(10) unsigned NOT NULL,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(11) NOT NULL,
  `seq_region_end` int(11) NOT NULL,
  `seq_region_strand` tinyint(4) NOT NULL,
69
  `genotypes` blob DEFAULT NULL,
70 71
  KEY `pos_idx` (`seq_region_id`,`seq_region_start`),
  KEY `sample_idx` (`sample_id`)
Anja Thormann's avatar
Anja Thormann committed
72
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
73

74 75 76
CREATE TABLE `compressed_genotype_var` (
  `variation_id` int(11) unsigned NOT NULL,
  `subsnp_id` int(11) unsigned DEFAULT NULL,
77
  `genotypes` blob DEFAULT NULL,
78 79
  KEY `variation_idx` (`variation_id`),
  KEY `subsnp_idx` (`subsnp_id`)
Anja Thormann's avatar
Anja Thormann committed
80
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
81

82 83
CREATE TABLE `coord_system` (
  `coord_system_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
84
  `species_id` int(10) unsigned NOT NULL DEFAULT 1,
85 86 87 88 89 90 91 92
  `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`)
Anja Thormann's avatar
Anja Thormann committed
93
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
94

95 96 97 98 99 100 101
CREATE TABLE `display_group` (
  `display_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `display_priority` int(10) unsigned NOT NULL,
  `display_name` varchar(255) NOT NULL,
  PRIMARY KEY (`display_group_id`),
  UNIQUE KEY `display_name` (`display_name`),
  UNIQUE KEY `display_priority` (`display_priority`)
Anja Thormann's avatar
Anja Thormann committed
102
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
103

104 105 106 107 108 109
CREATE TABLE `failed_allele` (
  `failed_allele_id` int(11) NOT NULL AUTO_INCREMENT,
  `allele_id` int(10) unsigned NOT NULL,
  `failed_description_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`failed_allele_id`),
  UNIQUE KEY `allele_idx` (`allele_id`,`failed_description_id`)
Anja Thormann's avatar
Anja Thormann committed
110
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
111

112 113 114 115
CREATE TABLE `failed_description` (
  `failed_description_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` text NOT NULL,
  PRIMARY KEY (`failed_description_id`)
116
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;
117

118 119 120 121 122 123
CREATE TABLE `failed_structural_variation` (
  `failed_structural_variation_id` int(11) NOT NULL AUTO_INCREMENT,
  `structural_variation_id` int(10) unsigned NOT NULL,
  `failed_description_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`failed_structural_variation_id`),
  UNIQUE KEY `structural_variation_idx` (`structural_variation_id`,`failed_description_id`)
Anja Thormann's avatar
Anja Thormann committed
124
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
125

126 127 128 129 130 131
CREATE TABLE `failed_variation` (
  `failed_variation_id` int(11) NOT NULL AUTO_INCREMENT,
  `variation_id` int(10) unsigned NOT NULL,
  `failed_description_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`failed_variation_id`),
  UNIQUE KEY `variation_idx` (`variation_id`,`failed_description_id`)
Anja Thormann's avatar
Anja Thormann committed
132
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
133

134 135 136 137 138 139 140 141
CREATE TABLE `failed_variation_feature` (
  `failed_variation_feature_id` int(11) NOT NULL AUTO_INCREMENT,
  `variation_feature_id` int(10) unsigned NOT NULL,
  `failed_description_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`failed_variation_feature_id`),
  UNIQUE KEY `variation_feature_idx` (`variation_feature_id`,`failed_description_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

142 143 144 145 146 147 148
CREATE TABLE `genotype_code` (
  `genotype_code_id` int(11) unsigned NOT NULL,
  `allele_code_id` int(11) unsigned NOT NULL,
  `haplotype_id` tinyint(2) unsigned NOT NULL,
  `phased` tinyint(2) unsigned DEFAULT NULL,
  KEY `genotype_code_id` (`genotype_code_id`),
  KEY `allele_code_id` (`allele_code_id`)
Anja Thormann's avatar
Anja Thormann committed
149
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
150

151 152 153
CREATE TABLE `individual` (
  `individual_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
154
  `description` text DEFAULT NULL,
155 156 157
  `gender` enum('Male','Female','Unknown') NOT NULL DEFAULT 'Unknown',
  `father_individual_id` int(10) unsigned DEFAULT NULL,
  `mother_individual_id` int(10) unsigned DEFAULT NULL,
158
  `individual_type_id` int(10) unsigned NOT NULL DEFAULT 0,
159 160 161
  PRIMARY KEY (`individual_id`),
  KEY `father_individual_idx` (`father_individual_id`),
  KEY `mother_individual_idx` (`mother_individual_id`)
Anja Thormann's avatar
Anja Thormann committed
162
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
163

164 165 166 167 168 169 170 171
CREATE TABLE `individual_synonym` (
  `synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `individual_id` int(10) unsigned NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`synonym_id`),
  KEY `individual_idx` (`individual_id`),
  KEY `name` (`name`,`source_id`)
Anja Thormann's avatar
Anja Thormann committed
172
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
173

174 175 176
CREATE TABLE `individual_type` (
  `individual_type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
177
  `description` text DEFAULT NULL,
178
  PRIMARY KEY (`individual_type_id`)
Magali Ruffier's avatar
Magali Ruffier committed
179
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
180

181 182
CREATE TABLE `meta` (
  `meta_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
183
  `species_id` int(10) unsigned DEFAULT 1,
184 185 186 187 188
  `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`)
189
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;
190

191 192 193 194 195
CREATE TABLE `meta_coord` (
  `table_name` varchar(40) NOT NULL,
  `coord_system_id` int(10) unsigned NOT NULL,
  `max_length` int(11) DEFAULT NULL,
  UNIQUE KEY `table_name` (`table_name`,`coord_system_id`)
Anja Thormann's avatar
Anja Thormann committed
196
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
197 198 199 200 201 202

CREATE TABLE `motif_feature_variation` (
  `motif_feature_variation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `variation_feature_id` int(11) unsigned NOT NULL,
  `feature_stable_id` varchar(128) DEFAULT NULL,
  `motif_feature_id` int(11) unsigned NOT NULL,
203 204
  `allele_string` text DEFAULT NULL,
  `somatic` tinyint(1) NOT NULL DEFAULT 0,
205
  `consequence_types` set('TF_binding_site_variant','TFBS_ablation','TFBS_fusion','TFBS_amplification','TFBS_translocation') DEFAULT NULL,
206
  `binding_matrix_stable_id` varchar(60) DEFAULT NULL,
207 208 209
  `motif_start` int(11) unsigned DEFAULT NULL,
  `motif_end` int(11) unsigned DEFAULT NULL,
  `motif_score_delta` float DEFAULT NULL,
210
  `in_informative_position` tinyint(1) NOT NULL DEFAULT 0,
211 212 213
  PRIMARY KEY (`motif_feature_variation_id`),
  KEY `variation_feature_idx` (`variation_feature_id`),
  KEY `consequence_type_idx` (`consequence_types`),
214 215
  KEY `somatic_feature_idx` (`feature_stable_id`,`somatic`),
  KEY `feature_stable_idx` (`feature_stable_id`)
Anja Thormann's avatar
Anja Thormann committed
216
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
217

218 219 220 221 222 223 224
CREATE TABLE `phenotype` (
  `phenotype_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stable_id` varchar(255) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`phenotype_id`),
  UNIQUE KEY `desc_idx` (`description`),
225 226
  KEY `name_idx` (`name`),
  KEY `stable_idx` (`stable_id`)
227
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
228

229 230 231 232 233 234 235
CREATE TABLE `phenotype_feature` (
  `phenotype_feature_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `phenotype_id` int(11) unsigned DEFAULT NULL,
  `source_id` int(11) unsigned DEFAULT NULL,
  `study_id` int(11) unsigned DEFAULT NULL,
  `type` enum('Gene','Variation','StructuralVariation','SupportingStructuralVariation','QTL','RegulatoryFeature') DEFAULT NULL,
  `object_id` varchar(255) DEFAULT NULL,
236
  `is_significant` tinyint(1) unsigned DEFAULT 1,
237 238 239 240 241 242 243 244 245 246
  `seq_region_id` int(11) unsigned DEFAULT NULL,
  `seq_region_start` int(11) unsigned DEFAULT NULL,
  `seq_region_end` int(11) unsigned DEFAULT NULL,
  `seq_region_strand` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`phenotype_feature_id`),
  KEY `phenotype_idx` (`phenotype_id`),
  KEY `object_idx` (`object_id`,`type`),
  KEY `type_idx` (`type`),
  KEY `pos_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`),
  KEY `source_idx` (`source_id`)
247
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
248

249 250 251 252 253 254
CREATE TABLE `phenotype_feature_attrib` (
  `phenotype_feature_id` int(11) unsigned NOT NULL,
  `attrib_type_id` int(11) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  KEY `phenotype_feature_idx` (`phenotype_feature_id`),
  KEY `type_value_idx` (`attrib_type_id`,`value`)
Anja Thormann's avatar
Anja Thormann committed
255
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
256

Alessandro Vullo's avatar
Alessandro Vullo committed
257 258 259
CREATE TABLE `phenotype_ontology_accession` (
  `phenotype_id` int(11) unsigned NOT NULL,
  `accession` varchar(255) NOT NULL,
260
  `mapped_by_attrib` set('437','438','439','440','441','442','443','444','588','589','590','591','592','593','594') DEFAULT NULL,
261 262 263
  `mapping_type` enum('is','involves') DEFAULT NULL,
  PRIMARY KEY (`phenotype_id`,`accession`),
  KEY `accession_idx` (`accession`)
Alessandro Vullo's avatar
Alessandro Vullo committed
264
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
265

266 267 268 269
CREATE TABLE `population` (
  `population_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `size` int(10) DEFAULT NULL,
270 271
  `description` text DEFAULT NULL,
  `collection` tinyint(1) DEFAULT 0,
272 273 274 275 276
  `freqs_from_gts` tinyint(1) DEFAULT NULL,
  `display` enum('LD','MARTDISPLAYABLE','UNDISPLAYABLE') DEFAULT 'UNDISPLAYABLE',
  `display_group_id` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`population_id`),
  KEY `name_idx` (`name`)
277
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
278

279 280 281 282 283 284 285 286 287 288 289 290
CREATE TABLE `population_genotype` (
  `population_genotype_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `variation_id` int(11) unsigned NOT NULL,
  `subsnp_id` int(11) unsigned DEFAULT NULL,
  `genotype_code_id` int(11) DEFAULT NULL,
  `frequency` float DEFAULT NULL,
  `population_id` int(10) unsigned DEFAULT NULL,
  `count` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`population_genotype_id`),
  KEY `population_idx` (`population_id`),
  KEY `variation_idx` (`variation_id`),
  KEY `subsnp_idx` (`subsnp_id`)
Anja Thormann's avatar
Anja Thormann committed
291
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
292

293 294 295 296 297
CREATE TABLE `population_structure` (
  `super_population_id` int(10) unsigned NOT NULL,
  `sub_population_id` int(10) unsigned NOT NULL,
  UNIQUE KEY `super_population_idx` (`super_population_id`,`sub_population_id`),
  KEY `sub_population_idx` (`sub_population_id`)
Anja Thormann's avatar
Anja Thormann committed
298
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
299 300 301 302 303 304 305 306 307

CREATE TABLE `population_synonym` (
  `synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `population_id` int(10) unsigned NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`synonym_id`),
  KEY `population_idx` (`population_id`),
  KEY `name` (`name`,`source_id`)
Anja Thormann's avatar
Anja Thormann committed
308
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
309 310 311 312

CREATE TABLE `protein_function_predictions` (
  `translation_md5_id` int(11) unsigned NOT NULL,
  `analysis_attrib_id` int(11) unsigned NOT NULL,
313
  `prediction_matrix` mediumblob DEFAULT NULL,
314
  PRIMARY KEY (`translation_md5_id`,`analysis_attrib_id`)
Anja Thormann's avatar
Anja Thormann committed
315
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
316

317 318 319 320
CREATE TABLE `protein_function_predictions_attrib` (
  `translation_md5_id` int(11) unsigned NOT NULL,
  `analysis_attrib_id` int(11) unsigned NOT NULL,
  `attrib_type_id` int(11) unsigned NOT NULL,
321
  `position_values` blob DEFAULT NULL,
322
  PRIMARY KEY (`translation_md5_id`,`analysis_attrib_id`,`attrib_type_id`)
Anja Thormann's avatar
Anja Thormann committed
323
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
324

325 326 327
CREATE TABLE `publication` (
  `publication_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
328
  `authors` varchar(255) CHARACTER SET latin2 DEFAULT NULL,
329 330 331 332 333 334 335 336
  `pmid` int(10) DEFAULT NULL,
  `pmcid` varchar(255) DEFAULT NULL,
  `year` int(10) unsigned DEFAULT NULL,
  `doi` varchar(50) DEFAULT NULL,
  `ucsc_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`publication_id`),
  KEY `pmid_idx` (`pmid`),
  KEY `doi_idx` (`doi`)
Anja Thormann's avatar
Anja Thormann committed
337
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
338

339 340 341 342 343 344
CREATE TABLE `read_coverage` (
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(11) NOT NULL,
  `seq_region_end` int(11) NOT NULL,
  `level` tinyint(4) NOT NULL,
  `sample_id` int(10) unsigned NOT NULL,
345 346
  KEY `seq_region_idx` (`seq_region_id`,`seq_region_start`),
  KEY `sample_idx` (`sample_id`)
Anja Thormann's avatar
Anja Thormann committed
347
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
348

349 350 351 352
CREATE TABLE `regulatory_feature_variation` (
  `regulatory_feature_variation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `variation_feature_id` int(11) unsigned NOT NULL,
  `feature_stable_id` varchar(128) DEFAULT NULL,
353 354 355
  `feature_type` text DEFAULT NULL,
  `allele_string` text DEFAULT NULL,
  `somatic` tinyint(1) NOT NULL DEFAULT 0,
356
  `consequence_types` set('regulatory_region_variant','regulatory_region_ablation','regulatory_region_fusion','regulatory_region_amplification','regulatory_region_translocation') DEFAULT NULL,
357 358 359
  PRIMARY KEY (`regulatory_feature_variation_id`),
  KEY `variation_feature_idx` (`variation_feature_id`),
  KEY `consequence_type_idx` (`consequence_types`),
360 361
  KEY `somatic_feature_idx` (`feature_stable_id`,`somatic`),
  KEY `feature_stable_idx` (`feature_stable_id`)
Anja Thormann's avatar
Anja Thormann committed
362
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
363 364 365 366 367

CREATE TABLE `sample` (
  `sample_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `individual_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
368
  `description` text DEFAULT NULL,
369 370
  `study_id` int(10) unsigned DEFAULT NULL,
  `display` enum('REFERENCE','DEFAULT','DISPLAYABLE','UNDISPLAYABLE','LD','MARTDISPLAYABLE') DEFAULT 'UNDISPLAYABLE',
371
  `has_coverage` tinyint(1) unsigned NOT NULL DEFAULT 0,
372 373
  `variation_set_id` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64') DEFAULT NULL,
  PRIMARY KEY (`sample_id`),
374 375
  KEY `individual_idx` (`individual_id`),
  KEY `study_idx` (`study_id`)
Anja Thormann's avatar
Anja Thormann committed
376
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
377

378 379 380
CREATE TABLE `sample_genotype_multiple_bp` (
  `variation_id` int(10) unsigned NOT NULL,
  `subsnp_id` int(15) unsigned DEFAULT NULL,
381 382
  `allele_1` varchar(25000) DEFAULT NULL,
  `allele_2` varchar(25000) DEFAULT NULL,
383 384 385 386
  `sample_id` int(10) unsigned DEFAULT NULL,
  KEY `variation_idx` (`variation_id`),
  KEY `subsnp_idx` (`subsnp_id`),
  KEY `sample_idx` (`sample_id`)
Anja Thormann's avatar
Anja Thormann committed
387
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
388 389 390 391

CREATE TABLE `sample_population` (
  `sample_id` int(10) unsigned NOT NULL,
  `population_id` int(10) unsigned NOT NULL,
392 393
  KEY `sample_idx` (`sample_id`),
  KEY `population_idx` (`population_id`)
Anja Thormann's avatar
Anja Thormann committed
394
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
395

Magali Ruffier's avatar
Magali Ruffier committed
396 397 398 399 400 401 402 403 404
CREATE TABLE `sample_synonym` (
  `synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sample_id` int(10) unsigned NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`synonym_id`),
  KEY `sample_idx` (`sample_id`),
  KEY `name` (`name`,`source_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
405

406 407
CREATE TABLE `seq_region` (
  `seq_region_id` int(10) unsigned NOT NULL,
408
  `name` varchar(255) NOT NULL,
409 410 411 412
  `coord_system_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`seq_region_id`),
  UNIQUE KEY `name_cs_idx` (`name`,`coord_system_id`),
  KEY `cs_idx` (`coord_system_id`)
Anja Thormann's avatar
Anja Thormann committed
413
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
414

415 416 417 418
CREATE TABLE `source` (
  `source_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL,
  `version` int(11) DEFAULT NULL,
Alessandro Vullo's avatar
Alessandro Vullo committed
419
  `description` varchar(400) DEFAULT NULL,
420 421 422 423
  `url` varchar(255) DEFAULT NULL,
  `type` enum('chip','lsdb') DEFAULT NULL,
  `somatic_status` enum('germline','somatic','mixed') DEFAULT 'germline',
  `data_types` set('variation','variation_synonym','structural_variation','phenotype_feature','study') DEFAULT NULL,
424 425
  PRIMARY KEY (`source_id`),
  UNIQUE KEY `name_idx` (`name`)
426
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
427

428 429 430 431 432 433
CREATE TABLE `structural_variation` (
  `structural_variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `variation_name` varchar(255) DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `study_id` int(10) unsigned DEFAULT NULL,
434
  `class_attrib_id` int(10) unsigned NOT NULL DEFAULT 0,
435 436
  `clinical_significance` set('uncertain significance','not provided','benign','likely benign','likely pathogenic','pathogenic','drug response','histocompatibility','other','confers sensitivity','risk factor','association','protective') DEFAULT NULL,
  `validation_status` enum('validated','not validated','high quality') DEFAULT NULL,
437 438
  `is_evidence` tinyint(4) DEFAULT 0,
  `somatic` tinyint(1) NOT NULL DEFAULT 0,
439 440 441 442 443 444
  `copy_number` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`structural_variation_id`),
  UNIQUE KEY `variation_name` (`variation_name`),
  KEY `source_idx` (`source_id`),
  KEY `study_idx` (`study_id`),
  KEY `attrib_idx` (`class_attrib_id`)
445
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
446

447 448 449 450 451 452
CREATE TABLE `structural_variation_association` (
  `structural_variation_id` int(10) unsigned NOT NULL,
  `supporting_structural_variation_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`structural_variation_id`,`supporting_structural_variation_id`),
  KEY `structural_variation_idx` (`structural_variation_id`),
  KEY `supporting_structural_variation_idx` (`supporting_structural_variation_id`)
Anja Thormann's avatar
Anja Thormann committed
453
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
454

455 456 457 458 459 460 461 462 463 464 465 466 467 468
CREATE TABLE `structural_variation_feature` (
  `structural_variation_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
  `outer_start` int(11) DEFAULT NULL,
  `seq_region_start` int(11) NOT NULL,
  `inner_start` int(11) DEFAULT NULL,
  `inner_end` int(11) DEFAULT NULL,
  `seq_region_end` int(11) NOT NULL,
  `outer_end` int(11) DEFAULT NULL,
  `seq_region_strand` tinyint(4) NOT NULL,
  `structural_variation_id` int(10) unsigned NOT NULL,
  `variation_name` varchar(255) DEFAULT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `study_id` int(10) unsigned DEFAULT NULL,
469 470 471 472
  `class_attrib_id` int(10) unsigned NOT NULL DEFAULT 0,
  `allele_string` longtext DEFAULT NULL,
  `is_evidence` tinyint(1) NOT NULL DEFAULT 0,
  `somatic` tinyint(1) NOT NULL DEFAULT 0,
473 474 475 476 477 478 479 480 481 482
  `breakpoint_order` tinyint(4) DEFAULT NULL,
  `length` int(10) DEFAULT NULL,
  `variation_set_id` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64') NOT NULL DEFAULT '',
  PRIMARY KEY (`structural_variation_feature_id`),
  KEY `pos_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`),
  KEY `structural_variation_idx` (`structural_variation_id`),
  KEY `source_idx` (`source_id`),
  KEY `study_idx` (`study_id`),
  KEY `attrib_idx` (`class_attrib_id`),
  KEY `variation_set_idx` (`variation_set_id`)
483
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
484

485 486 487 488
CREATE TABLE `structural_variation_sample` (
  `structural_variation_sample_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `structural_variation_id` int(10) unsigned NOT NULL,
  `sample_id` int(10) unsigned DEFAULT NULL,
489
  `zygosity` tinyint(1) DEFAULT NULL,
490 491 492
  PRIMARY KEY (`structural_variation_sample_id`),
  KEY `structural_variation_idx` (`structural_variation_id`),
  KEY `sample_idx` (`sample_id`)
Anja Thormann's avatar
Anja Thormann committed
493
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
494 495 496 497 498

CREATE TABLE `study` (
  `study_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
499
  `description` text DEFAULT NULL,
500 501 502 503
  `url` varchar(255) DEFAULT NULL,
  `external_reference` varchar(255) DEFAULT NULL,
  `study_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`study_id`),
504 505 506
  KEY `source_idx` (`source_id`),
  KEY `external_reference_idx` (`external_reference`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
507

508 509 510 511 512 513
CREATE TABLE `submitter` (
  `submitter_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`submitter_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

514 515 516 517 518
CREATE TABLE `submitter_handle` (
  `handle_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `handle` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`handle_id`),
  UNIQUE KEY `handle` (`handle`)
519 520
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

521 522 523 524
CREATE TABLE `subsnp_handle` (
  `subsnp_id` int(11) unsigned NOT NULL,
  `handle` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`subsnp_id`)
Anja Thormann's avatar
Anja Thormann committed
525
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
526 527

CREATE TABLE `tmp_sample_genotype_single_bp` (
528 529 530 531
  `variation_id` int(10) NOT NULL,
  `subsnp_id` int(15) unsigned DEFAULT NULL,
  `allele_1` char(1) DEFAULT NULL,
  `allele_2` char(1) DEFAULT NULL,
532
  `sample_id` int(10) unsigned NOT NULL,
533 534
  KEY `variation_idx` (`variation_id`),
  KEY `subsnp_idx` (`subsnp_id`),
535 536 537
  KEY `sample_idx` (`sample_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100000000;

538 539 540 541
CREATE TABLE `transcript_variation` (
  `transcript_variation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `variation_feature_id` int(11) unsigned NOT NULL,
  `feature_stable_id` varchar(128) DEFAULT NULL,
542 543
  `allele_string` text DEFAULT NULL,
  `somatic` tinyint(1) NOT NULL DEFAULT 0,
544
  `consequence_types` set('splice_acceptor_variant','splice_donor_variant','stop_lost','coding_sequence_variant','missense_variant','stop_gained','synonymous_variant','frameshift_variant','non_coding_transcript_variant','non_coding_transcript_exon_variant','mature_miRNA_variant','NMD_transcript_variant','5_prime_UTR_variant','3_prime_UTR_variant','incomplete_terminal_codon_variant','intron_variant','splice_region_variant','downstream_gene_variant','upstream_gene_variant','start_lost','stop_retained_variant','inframe_insertion','inframe_deletion','transcript_ablation','transcript_fusion','transcript_amplification','transcript_translocation','feature_elongation','feature_truncation','protein_altering_variant','start_retained_variant') DEFAULT NULL,
545 546 547 548 549 550 551
  `cds_start` int(11) unsigned DEFAULT NULL,
  `cds_end` int(11) unsigned DEFAULT NULL,
  `cdna_start` int(11) unsigned DEFAULT NULL,
  `cdna_end` int(11) unsigned DEFAULT NULL,
  `translation_start` int(11) unsigned DEFAULT NULL,
  `translation_end` int(11) unsigned DEFAULT NULL,
  `distance_to_transcript` int(11) unsigned DEFAULT NULL,
552 553 554 555 556
  `codon_allele_string` text DEFAULT NULL,
  `pep_allele_string` text DEFAULT NULL,
  `hgvs_genomic` text DEFAULT NULL,
  `hgvs_transcript` text DEFAULT NULL,
  `hgvs_protein` text DEFAULT NULL,
557 558 559 560
  `polyphen_prediction` enum('unknown','benign','possibly damaging','probably damaging') DEFAULT NULL,
  `polyphen_score` float DEFAULT NULL,
  `sift_prediction` enum('tolerated','deleterious','tolerated - low confidence','deleterious - low confidence') DEFAULT NULL,
  `sift_score` float DEFAULT NULL,
561
  `display` int(1) DEFAULT 1,
562 563 564 565
  PRIMARY KEY (`transcript_variation_id`),
  KEY `variation_feature_idx` (`variation_feature_id`),
  KEY `consequence_type_idx` (`consequence_types`),
  KEY `somatic_feature_idx` (`feature_stable_id`,`somatic`)
Anja Thormann's avatar
Anja Thormann committed
566
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
567 568 569 570 571 572

CREATE TABLE `translation_md5` (
  `translation_md5_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_md5` char(32) NOT NULL,
  PRIMARY KEY (`translation_md5_id`),
  UNIQUE KEY `md5_idx` (`translation_md5`)
Anja Thormann's avatar
Anja Thormann committed
573
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
574 575 576 577 578 579

CREATE TABLE `variation` (
  `variation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `flipped` tinyint(1) unsigned DEFAULT NULL,
580 581
  `class_attrib_id` int(10) unsigned DEFAULT 0,
  `somatic` tinyint(1) NOT NULL DEFAULT 0,
582 583 584 585
  `minor_allele` varchar(50) DEFAULT NULL,
  `minor_allele_freq` float DEFAULT NULL,
  `minor_allele_count` int(10) unsigned DEFAULT NULL,
  `clinical_significance` set('uncertain significance','not provided','benign','likely benign','likely pathogenic','pathogenic','drug response','histocompatibility','other','confers sensitivity','risk factor','association','protective') DEFAULT NULL,
586
  `evidence_attribs` set('367','368','369','370','371','372','418','421','573','585') DEFAULT NULL,
587
  `display` int(1) DEFAULT 1,
588 589 590
  PRIMARY KEY (`variation_id`),
  UNIQUE KEY `name` (`name`),
  KEY `source_idx` (`source_id`)
591
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
592 593 594 595 596 597 598

CREATE TABLE `variation_attrib` (
  `variation_id` int(11) unsigned NOT NULL,
  `attrib_id` int(11) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  KEY `variation_idx` (`variation_id`),
  KEY `attrib_value_idx` (`attrib_id`,`value`)
Anja Thormann's avatar
Anja Thormann committed
599
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
600 601 602 603 604

CREATE TABLE `variation_citation` (
  `variation_id` int(10) unsigned NOT NULL,
  `publication_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`variation_id`,`publication_id`)
Anja Thormann's avatar
Anja Thormann committed
605
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
606 607 608 609 610 611 612 613 614

CREATE TABLE `variation_feature` (
  `variation_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_region_id` int(10) unsigned NOT NULL,
  `seq_region_start` int(11) NOT NULL,
  `seq_region_end` int(11) NOT NULL,
  `seq_region_strand` tinyint(4) NOT NULL,
  `variation_id` int(10) unsigned NOT NULL,
  `allele_string` varchar(50000) DEFAULT NULL,
615
  `ancestral_allele` varchar(50) DEFAULT NULL,
616 617 618 619
  `variation_name` varchar(255) DEFAULT NULL,
  `map_weight` int(11) NOT NULL,
  `flags` set('genotyped') DEFAULT NULL,
  `source_id` int(10) unsigned NOT NULL,
620
  `consequence_types` set('intergenic_variant','splice_acceptor_variant','splice_donor_variant','stop_lost','coding_sequence_variant','missense_variant','stop_gained','synonymous_variant','frameshift_variant','non_coding_transcript_variant','non_coding_transcript_exon_variant','mature_miRNA_variant','NMD_transcript_variant','5_prime_UTR_variant','3_prime_UTR_variant','incomplete_terminal_codon_variant','intron_variant','splice_region_variant','downstream_gene_variant','upstream_gene_variant','start_lost','stop_retained_variant','inframe_insertion','inframe_deletion','transcript_ablation','transcript_fusion','transcript_amplification','transcript_translocation','TFBS_ablation','TFBS_fusion','TFBS_amplification','TFBS_translocation','regulatory_region_ablation','regulatory_region_fusion','regulatory_region_amplification','regulatory_region_translocation','feature_elongation','feature_truncation','regulatory_region_variant','TF_binding_site_variant','protein_altering_variant','start_retained_variant') NOT NULL DEFAULT 'intergenic_variant',
621
  `variation_set_id` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64') NOT NULL DEFAULT '',
622 623
  `class_attrib_id` int(10) unsigned DEFAULT 0,
  `somatic` tinyint(1) NOT NULL DEFAULT 0,
624 625 626 627
  `minor_allele` varchar(50) DEFAULT NULL,
  `minor_allele_freq` float DEFAULT NULL,
  `minor_allele_count` int(10) unsigned DEFAULT NULL,
  `alignment_quality` double DEFAULT NULL,
628
  `evidence_attribs` set('367','368','369','370','371','372','418','421','573','585') DEFAULT NULL,
629
  `clinical_significance` set('uncertain significance','not provided','benign','likely benign','likely pathogenic','pathogenic','drug response','histocompatibility','other','confers sensitivity','risk factor','association','protective') DEFAULT NULL,
630
  `display` int(1) DEFAULT 1,
631 632 633 634 635 636
  PRIMARY KEY (`variation_feature_id`),
  KEY `pos_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`),
  KEY `variation_idx` (`variation_id`),
  KEY `variation_set_idx` (`variation_set_id`),
  KEY `consequence_type_idx` (`consequence_types`),
  KEY `source_idx` (`source_id`)
637
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
638

639 640 641 642
CREATE TABLE `variation_genename` (
  `variation_id` int(10) unsigned NOT NULL,
  `gene_name` varchar(255) NOT NULL,
  PRIMARY KEY (`variation_id`,`gene_name`)
Anja Thormann's avatar
Anja Thormann committed
643
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
644

645 646 647 648
CREATE TABLE `variation_hgvs` (
  `variation_id` int(10) unsigned NOT NULL,
  `hgvs_name` varchar(255) NOT NULL,
  PRIMARY KEY (`variation_id`,`hgvs_name`)
Anja Thormann's avatar
Anja Thormann committed
649
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
650

651 652 653
CREATE TABLE `variation_set` (
  `variation_set_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
654
  `description` text DEFAULT NULL,
655 656 657
  `short_name_attrib_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`variation_set_id`),
  KEY `name_idx` (`name`)
658
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
659

660 661 662 663
CREATE TABLE `variation_set_structural_variation` (
  `structural_variation_id` int(10) unsigned NOT NULL,
  `variation_set_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`structural_variation_id`,`variation_set_id`)
Anja Thormann's avatar
Anja Thormann committed
664
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
665

666 667 668 669 670
CREATE TABLE `variation_set_structure` (
  `variation_set_super` int(10) unsigned NOT NULL,
  `variation_set_sub` int(10) unsigned NOT NULL,
  PRIMARY KEY (`variation_set_super`,`variation_set_sub`),
  KEY `sub_idx` (`variation_set_sub`,`variation_set_super`)
Anja Thormann's avatar
Anja Thormann committed
671
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
672 673 674 675 676 677

CREATE TABLE `variation_set_variation` (
  `variation_id` int(10) unsigned NOT NULL,
  `variation_set_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`variation_id`,`variation_set_id`),
  KEY `variation_set_idx` (`variation_set_id`,`variation_id`)
Anja Thormann's avatar
Anja Thormann committed
678
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
679 680 681 682 683 684 685 686

CREATE TABLE `variation_synonym` (
  `variation_synonym_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `variation_id` int(10) unsigned NOT NULL,
  `subsnp_id` int(15) unsigned DEFAULT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`variation_synonym_id`),
687
  UNIQUE KEY `name_idx` (`name`,`source_id`,`variation_id`),
688 689 690
  KEY `variation_idx` (`variation_id`),
  KEY `subsnp_idx` (`subsnp_id`),
  KEY `source_idx` (`source_id`)
Anja Thormann's avatar
Anja Thormann committed
691
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
692