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