· 8 years ago · Jul 18, 2018, 06:48 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2
3SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
4
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
6
7
8
9CREATE TABLE IF NOT EXISTS `rt`.`instrument` (
10
11 `instrument_id` INT(11) NOT NULL ,
12
13 `name` INT(11) NOT NULL ,
14
15 PRIMARY KEY (`instrument_id`) )
16
17ENGINE = InnoDB
18
19DEFAULT CHARACTER SET = latin1
20
21COLLATE = latin1_swedish_ci;
22
23
24
25CREATE TABLE IF NOT EXISTS `rt`.`lc_rune` (
26
27 `lc_run_id` INT(11) NOT NULL ,
28
29 `l_project_id` INT(11) NOT NULL ,
30
31 `l_instrument_id` INT(11) NOT NULL ,
32
33 PRIMARY KEY (`lc_run_id`) ,
34
35 INDEX `l_project_id_1` (`l_project_id` ASC) ,
36
37 INDEX `l_instrument_id_1` (`l_instrument_id` ASC) ,
38
39 CONSTRAINT `l_project_id_1`
40
41 FOREIGN KEY (`l_project_id` )
42
43 REFERENCES `rt`.`project` (`project_id` )
44
45 ON DELETE NO ACTION
46
47 ON UPDATE NO ACTION,
48
49 CONSTRAINT `l_instrument_id_1`
50
51 FOREIGN KEY (`l_instrument_id` )
52
53 REFERENCES `rt`.`instrument` (`instrument_id` )
54
55 ON DELETE NO ACTION
56
57 ON UPDATE NO ACTION)
58
59ENGINE = InnoDB
60
61DEFAULT CHARACTER SET = latin1
62
63COLLATE = latin1_swedish_ci;
64
65
66
67CREATE TABLE IF NOT EXISTS `rt`.`modification_sete_has_modification` (
68
69 `modification_sete_has_modificationcol_id` INT(11) NOT NULL ,
70
71 `l_modification_set_id` INT(10) UNSIGNED NOT NULL ,
72
73 `l_modification_id` INT(10) UNSIGNED NOT NULL ,
74
75 PRIMARY KEY (`modification_sete_has_modificationcol_id`) ,
76
77 INDEX `l_modification_set_id_1` (`l_modification_set_id` ASC) ,
78
79 INDEX `l_modification_id_2` (`l_modification_id` ASC) ,
80
81 CONSTRAINT `l_modification_set_id_1`
82
83 FOREIGN KEY (`l_modification_set_id` )
84
85 REFERENCES `rt`.`modification_sete` (`modification_set_id` )
86
87 ON DELETE NO ACTION
88
89 ON UPDATE NO ACTION,
90
91 CONSTRAINT `l_modification_id_2`
92
93 FOREIGN KEY (`l_modification_id` )
94
95 REFERENCES `rt`.`modification` (`modification_id` )
96
97 ON DELETE NO ACTION
98
99 ON UPDATE NO ACTION)
100
101ENGINE = InnoDB
102
103DEFAULT CHARACTER SET = latin1
104
105COLLATE = latin1_swedish_ci;
106
107
108
109CREATE TABLE IF NOT EXISTS `rt`.`modification_sete` (
110
111 `modification_set_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
112
113 `tag` VARCHAR(5) NOT NULL ,
114
115 PRIMARY KEY (`modification_set_id`) )
116
117ENGINE = InnoDB
118
119DEFAULT CHARACTER SET = latin1
120
121COLLATE = latin1_swedish_ci;
122
123
124
125CREATE TABLE IF NOT EXISTS `rt`.`modification` (
126
127 `modification_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
128
129 `name` VARCHAR(45) NOT NULL ,
130
131 PRIMARY KEY (`modification_id`) )
132
133ENGINE = InnoDB
134
135DEFAULT CHARACTER SET = latin1
136
137COLLATE = latin1_swedish_ci;
138
139
140
141CREATE TABLE IF NOT EXISTS `rt`.`peptide_has_modification` (
142
143 `peptide_has_modification_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
144
145 `l_lc_run_id` INT(11) NOT NULL ,
146
147 `l_peptide_id` INT(11) NOT NULL ,
148
149 `l_modification_id` INT(11) NOT NULL ,
150
151 `retention_time` DECIMAL(8,4) NOT NULL ,
152
153 `score` DECIMAL(3,1) NOT NULL ,
154
155 `threshold` DECIMAL(3,1) NOT NULL ,
156
157 `homology` DECIMAL(3,1) NOT NULL ,
158
159 `positions` VARCHAR(30) NOT NULL ,
160
161 `charge` INT(11) NOT NULL ,
162
163 `isAverage` TINYINT(1) NOT NULL ,
164
165 PRIMARY KEY (`peptide_has_modification_id`) ,
166
167 INDEX `l_peptide_id_` (`l_peptide_id` ASC) ,
168
169 INDEX `l_modification_id_1` (`l_modification_id` ASC) ,
170
171 INDEX `l_lc_run_id_1` (`l_lc_run_id` ASC) ,
172
173 CONSTRAINT `l_peptide_id_`
174
175 FOREIGN KEY (`l_peptide_id` )
176
177 REFERENCES `rt`.`peptide` (`peptide_id` )
178
179 ON DELETE NO ACTION
180
181 ON UPDATE NO ACTION,
182
183 CONSTRAINT `l_modification_id_1`
184
185 FOREIGN KEY (`l_modification_id` )
186
187 REFERENCES `rt`.`modification_sete` (`modification_set_id` )
188
189 ON DELETE NO ACTION
190
191 ON UPDATE NO ACTION,
192
193 CONSTRAINT `l_lc_run_id_1`
194
195 FOREIGN KEY (`l_lc_run_id` )
196
197 REFERENCES `rt`.`lc_rune` (`lc_run_id` )
198
199 ON DELETE NO ACTION
200
201 ON UPDATE NO ACTION)
202
203ENGINE = InnoDB
204
205DEFAULT CHARACTER SET = latin1
206
207COLLATE = latin1_swedish_ci;
208
209
210
211CREATE TABLE IF NOT EXISTS `rt`.`peptide` (
212
213 `peptide_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
214
215 `sequence` VARCHAR(45) NOT NULL ,
216
217 PRIMARY KEY (`peptide_id`) )
218
219ENGINE = InnoDB
220
221DEFAULT CHARACTER SET = latin1
222
223COLLATE = latin1_swedish_ci;
224
225
226
227CREATE TABLE IF NOT EXISTS `rt`.`project` (
228
229 `project_id` INT(11) NOT NULL ,
230
231 `protocol` INT(11) NOT NULL ,
232
233 PRIMARY KEY (`project_id`) )
234
235ENGINE = InnoDB
236
237DEFAULT CHARACTER SET = latin1
238
239COLLATE = latin1_swedish_ci;
240
241
242
243
244
245SET SQL_MODE=@OLD_SQL_MODE;
246
247SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
248
249SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;