· 8 years ago · Apr 12, 2018, 08:06 AM
1CREATE DATABASE `ariel` DEFAULT CHARACTER SET ASCII COLLATE ascii_general_ci;
2CREATE DATABASE `caliban` DEFAULT CHARACTER SET ASCII COLLATE ascii_general_ci;
3CREATE DATABASE `dbsnp` DEFAULT CHARACTER SET ascii COLLATE ascii_general_ci;
4
5CREATE USER 'reader'@'localhost' IDENTIFIED BY 'shakespeare';
6GRANT SELECT ON *.* TO `reader`@'localhost';
7CREATE USER 'reader'@'%' IDENTIFIED BY 'shakespeare';
8GRANT SELECT ON *.* TO `reader`@'%';
9
10CREATE USER 'updater'@'localhost' IDENTIFIED BY 'shakespeare';
11GRANT SELECT,
12INSERT,
13UPDATE,
14DELETE,
15CREATE TEMPORARY TABLES ON *.* TO `updater`@'localhost';
16CREATE USER 'updater'@'%' IDENTIFIED BY 'shakespeare';
17GRANT SELECT,
18INSERT,
19UPDATE,
20DELETE,
21CREATE TEMPORARY TABLES ON *.* TO `updater`@'%';
22
23CREATE USER 'writer'@'localhost' IDENTIFIED BY 'shakespeare';
24GRANT SELECT,
25INSERT,
26UPDATE,
27CREATE TEMPORARY TABLES ON `ariel`.* TO `writer`@'localhost';
28CREATE USER 'writer'@'%' IDENTIFIED BY 'shakespeare';
29GRANT SELECT,
30INSERT,
31UPDATE,
32CREATE TEMPORARY TABLES ON `ariel`.* TO `writer`@'%';
33
34FLUSH PRIVILEGES;
35
36USE ariel;
37
38CREATE TABLE IF NOT EXISTS `evidence` (
39 `phenotype` VARCHAR(255) NOT NULL,
40 `chr` VARCHAR(12) NOT NULL,
41 `start` INT NOT NULL,
42 `end` INT NOT NULL,
43 `allele` VARCHAR(255) NOT NULL,
44 `inheritance` VARCHAR(12) NULL,
45 `references` TEXT NULL,
46 `tags` TEXT NULL,
47 `id` INT NOT NULL auto_increment,
48 PRIMARY KEY (`id`),
49 KEY (`phenotype`),
50 KEY (`chr`, `start`, `end`, `allele`)
51) ENGINE=MyISAM DEFAULT CHARSET=utf8;
52
53CREATE TABLE IF NOT EXISTS `files` (
54 `id` int(11) NOT NULL auto_increment,
55 `path` text NOT NULL,
56 `kind` varchar(16) NOT NULL,
57 `job` int(11) NOT NULL,
58 PRIMARY KEY (`id`),
59 KEY `kind` (`kind`,`job`)
60) ENGINE=MyISAM DEFAULT CHARSET=utf8;
61
62CREATE TABLE IF NOT EXISTS `jobs` (
63 `id` int(11) NOT NULL auto_increment,
64 `submitted` timestamp NULL default NULL,
65 `processed` timestamp NULL default NULL,
66 `retrieved` timestamp NULL default NULL,
67 `public` tinyint(1) NOT NULL default '0',
68 `user` int(11) default NULL,
69 PRIMARY KEY (`id`),
70 KEY `user` (`user`)
71) ENGINE=MyISAM DEFAULT CHARSET=utf8;
72
73CREATE TABLE IF NOT EXISTS `users` (
74 `id` int(11) NOT NULL auto_increment,
75 `username` varchar(64) NOT NULL,
76 `password_hash` varchar(64) NOT NULL,
77 `email` varchar(128) default NULL,
78 `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
79 PRIMARY KEY (`id`),
80 UNIQUE KEY `username` (`username`)
81) ENGINE=MyISAM DEFAULT CHARSET=utf8;
82
83USE caliban;
84
85CREATE OR REPLACE VIEW `evidence` AS SELECT * FROM `ariel`.`evidence`;
86
87CREATE TABLE IF NOT EXISTS `hapmap27` (
88 `rs_id` VARCHAR(16) NOT NULL,
89 `chr` VARCHAR(12) NOT NULL,
90 `start` INT UNSIGNED NOT NULL,
91 `end` INT UNSIGNED NOT NULL,
92 `strand` ENUM('+','-') NOT NULL,
93 `pop` VARCHAR(8) NOT NULL,
94 `ref_allele` CHAR(1) NOT NULL,
95 `ref_allele_freq` DECIMAL(6,4) NOT NULL,
96 `ref_allele_count` INT UNSIGNED NOT NULL,
97 `oth_allele` CHAR(1) NULL,
98 `oth_allele_freq` DECIMAL(6,4) NULL,
99 `oth_allele_count` INT UNSIGNED NULL,
100 `total_count` INT UNSIGNED NOT NULL,
101 UNIQUE KEY `i_rs_id_pop` (`rs_id`,`pop`),
102 KEY `i_chrom_start_end` (`chr`,`start`,`end`)
103) ENGINE=MyISAM DEFAULT CHARSET=latin1;
104
105CREATE TABLE IF NOT EXISTS `morbidmap` (
106 `disorder` varchar(255) NOT NULL,
107 `symbols` varchar(128) NOT NULL,
108 `omim` int(11) NOT NULL,
109 `location` varchar(24) NOT NULL,
110 KEY `omim` (`omim`)
111) ENGINE=MyISAM DEFAULT CHARSET=latin1;
112
113CREATE TABLE IF NOT EXISTS `omim` (
114 `phenotype` VARCHAR(255) NOT NULL,
115 `gene` VARCHAR(12) NOT NULL,
116 `amino_acid` VARCHAR(8) NOT NULL,
117 `codon` INT NOT NULL,
118 `word_count` INT,
119 `allelic_variant_id` VARCHAR(24),
120 KEY (`gene`,`codon`)
121) ENGINE=MyISAM DEFAULT CHARSET=latin1;
122
123CREATE TABLE IF NOT EXISTS `refflat` (
124 `geneName` varchar(255) NOT NULL,
125 `name` varchar(255) NOT NULL,
126 `chrom` varchar(255) NOT NULL,
127 `strand` char(1) NOT NULL,
128 `txStart` int(10) unsigned NOT NULL,
129 `txEnd` int(10) unsigned NOT NULL,
130 `cdsStart` int(10) unsigned NOT NULL,
131 `cdsEnd` int(10) unsigned NOT NULL,
132 `exonCount` int(10) unsigned NOT NULL,
133 `exonStarts` longblob,
134 `exonEnds` longblob,
135 KEY `geneName` (`geneName`),
136 KEY `i_chromtxstarttxend` (`chrom`,`txStart`,`txEnd`)
137) ENGINE=MyISAM DEFAULT CHARSET=latin1;
138
139CREATE OR REPLACE VIEW `refflat-complete` AS SELECT * FROM `refflat`;
140
141-- unchanged from UCSC schema
142CREATE TABLE IF NOT EXISTS `snp129` (
143 `bin` smallint(5) unsigned NOT NULL default '0',
144 `chrom` varchar(31) NOT NULL default '',
145 `chromStart` int(10) unsigned NOT NULL default '0',
146 `chromEnd` int(10) unsigned NOT NULL default '0',
147 `name` varchar(15) NOT NULL default '',
148 `score` smallint(5) unsigned NOT NULL default '0',
149 `strand` enum('+','-') default NULL,
150 `refNCBI` blob NOT NULL,
151 `refUCSC` blob NOT NULL,
152 `observed` varchar(255) NOT NULL default '',
153 `molType` enum('genomic','cDNA') default NULL,
154 `class` enum('unknown','single','in-del','het','microsatellite','named','mixed','mnp','insertion','deletion') NOT NULL default 'unknown',
155 `valid` set('unknown','by-cluster','by-frequency','by-submitter','by-2hit-2allele','by-hapmap') NOT NULL default 'unknown',
156 `avHet` float NOT NULL default '0',
157 `avHetSE` float NOT NULL default '0',
158 `func` set('unknown','coding-synon','intron','cds-reference','near-gene-3','near-gene-5','nonsense','missense','frameshift','untranslated-3','untranslated-5','splice-3','splice-5') NOT NULL default 'unknown',
159 `locType` enum('range','exact','between','rangeInsertion','rangeSubstitution','rangeDeletion') default NULL,
160 `weight` int(10) unsigned NOT NULL default '0',
161 KEY `name` (`name`),
162 KEY `chrom` (`chrom`,`bin`)
163) ENGINE=MyISAM DEFAULT CHARSET=latin1;
164
165CREATE TABLE IF NOT EXISTS `snpedia` (
166 `phenotype` VARCHAR(255) NOT NULL,
167 `chr` VARCHAR(12) NOT NULL,
168 `start` INT UNSIGNED NOT NULL,
169 `end` INT UNSIGNED NOT NULL,
170 `strand` enum('+','-') NOT NULL,
171 `genotype` VARCHAR(255) NOT NULL,
172 `pubmed_id` TEXT,
173 `rs_id` VARCHAR(255),
174 KEY (`rs_id`),
175 KEY `i_chrom_start_end` (`chr`,`start`,`end`)
176) ENGINE=MyISAM DEFAULT CHARSET=latin1;
177
178USE dbsnp;
179
180CREATE TABLE IF NOT EXISTS `OmimVarLocusIdSNP` (
181 `omim_id` INT NOT NULL,
182 `locus_id` INT NOT NULL,
183 `omimvar_id` CHAR(4) NOT NULL,
184 `locus_symbol` CHAR(10) NOT NULL,
185 `var1` CHAR(2) NOT NULL,
186 `aa_position` INT NOT NULL,
187 `var2` CHAR(2) NOT NULL,
188 `var_class` INT NOT NULL,
189 `snp_id` INT NOT NULL
190) ENGINE=MyISAM DEFAULT CHARSET=latin1;
191
192ALTER TABLE `OmimVarLocusIdSNP` ADD INDEX `i_snp_id` (`snp_id`);
193ALTER TABLE `OmimVarLocusIdSNP` ADD INDEX `i_omim_id` (`omim_id`);
194
195CREATE TABLE IF NOT EXISTS `b129_SNPChrPosOnRef_36_3` (
196 `snp_id` INT NOT NULL,
197 `chr` VARCHAR(32) NULL,
198 `pos` INT NULL,
199 `orien` INT NULL,
200 `neighbor_snp_list` INT NULL,
201 `is_par` VARCHAR(1) NOT NULL
202) ENGINE=MyISAM DEFAULT CHARSET=latin1;
203
204ALTER TABLE `b129_SNPChrPosOnRef_36_3` ADD UNIQUE `i_snp_id` (`snp_id`);
205ALTER TABLE `b129_SNPChrPosOnRef_36_3` ADD INDEX `i_chrpos` (`chr`,`pos`);
206
207CREATE OR REPLACE VIEW `SNPChrPosOnRef` AS SELECT * FROM `b129_SNPChrPosOnRef_36_3`;