Newer
Older
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
-----------------------------------
-- Version 003
-----------------------------------
-- Create domain types section -------------------------------------------------
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN CODE AS VARCHAR(40);
CREATE DOMAIN FILE_NAME as VARCHAR(255);
CREATE DOMAIN FILE_PATH as VARCHAR(1000);
CREATE DOMAIN SHORT_LABEL as VARCHAR(20);
CREATE DOMAIN LONG_LABEL as VARCHAR(100);
CREATE DOMAIN CHAR as VARCHAR(1);
CREATE DOMAIN ms_quantification_software_kind AS character varying(40)
CONSTRAINT ms_quantification_software_kind_check CHECK (((VALUE)::text =
ANY ((ARRAY['msSoft'::character varying, 'Xcalibur'::character varying,
'Analyst'::character varying, 'MassHunter'::character varying])::text[])));
-- Create tables section -------------------------------------------------
-- Table EXPERIMENTS
CREATE TABLE EXPERIMENTS (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
NAME LONG_LABEL NOT NULL,
PRIMARY KEY (ID),
UNIQUE (PERM_ID)
);
-- Table SAMPLES
CREATE TABLE SAMPLES (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
NAME LONG_LABEL NOT NULL,
EXPE_ID TECH_ID NOT NULL,
PRIMARY KEY (ID),
UNIQUE (PERM_ID),
CONSTRAINT FK_SAMPLE_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Table DATA_SETS
CREATE TABLE DATA_SETS (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
EXPE_ID TECH_ID NOT NULL,
SAMP_ID TECH_ID,
PRIMARY KEY (ID),
UNIQUE (PERM_ID),
CONSTRAINT FK_DATA_SET_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_DATA_SET_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Table EIC_MS_RUNS
CREATE TABLE EIC_MS_RUNS (
ID BIGSERIAL NOT NULL,
EXPE_ID TECH_ID NOT NULL,
SAMP_ID TECH_ID,
DS_ID TECH_ID NOT NULL,
RAW_DATA_FILE_NAME FILE_NAME NOT NULL,
RAW_DATA_FILE_PATH FILE_PATH DEFAULT NULL,
ACQUISITION_DATE TIMESTAMP DEFAULT NULL,
INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,
INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,
INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,
METHOD_IONISATION SHORT_LABEL DEFAULT NULL,
METHOD_SEPARATION LONG_LABEL DEFAULT NULL,
OPERATOR SHORT_LABEL DEFAULT NULL,
MS_RUN_ID BIGINT DEFAULT NULL,
SET_ID TECH_ID DEFAULT NULL,
START_TIME REAL NOT NULL,
END_TIME REAL NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_EIC_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_EIC_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_EIC_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX EIC_MS_RUNS_FK_DS_ID_IDX ON EIC_MS_RUNS(DS_ID);
CREATE INDEX EIC_MS_RUNS_FK_SAMP_ID_IDX ON EIC_MS_RUNS(SAMP_ID);
CREATE INDEX EIC_MS_RUNS_FK_EXPE_ID_IDX ON EIC_MS_RUNS(EXPE_ID);
-- Table EIC_CHROMATOGRAMS
CREATE TABLE EIC_CHROMATOGRAMS (
ID BIGSERIAL NOT NULL,
EIC_MS_RUN_ID TECH_ID NOT NULL,
Q1_MZ REAL NOT NULL,
Q3_LOW_MZ REAL NOT NULL,
Q3_HIGH_MZ REAL NOT NULL,
LABEL LONG_LABEL DEFAULT NULL,
POLARITY CHAR DEFAULT NULL,
RUN_TIMES TEXT NOT NULL,
INTENSITIES TEXT NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_EIC_CHROMATOGRAM_1 FOREIGN KEY (EIC_MS_RUN_ID) REFERENCES EIC_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX EIC_CHROMATOGRAM_FK_IDX ON EIC_CHROMATOGRAMS(EIC_MS_RUN_ID);
-- Table FIA_MS_RUNS
CREATE TABLE FIA_MS_RUNS (
ID BIGSERIAL NOT NULL,
EXPE_ID TECH_ID NOT NULL,
SAMP_ID TECH_ID,
DS_ID TECH_ID NOT NULL,
RAW_DATA_FILE_NAME FILE_NAME NOT NULL,
RAW_DATA_FILE_PATH FILE_PATH DEFAULT NULL,
ACQUISITION_DATE TIMESTAMP DEFAULT NULL,
INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,
INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,
INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,
METHOD_IONISATION SHORT_LABEL DEFAULT NULL,
METHOD_SEPARATION LONG_LABEL DEFAULT NULL,
POLARITY VARCHAR(1) DEFAULT NULL,
LOW_MZ REAL NOT NULL,
HIGH_MZ REAL NOT NULL,
INTERNAL_STANDARD REAL NOT NULL,
-- Is this a good name?
OD REAL NOT NULL,
OPERATOR SHORT_LABEL DEFAULT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_FIA_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_EIA_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_EIA_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX FIA_MS_RUNS_FK_DS_ID_IDX ON FIA_MS_RUNS(DS_ID);
CREATE INDEX FIA_MS_RUNS_FK_SAMP_ID_IDX ON FIA_MS_RUNS(SAMP_ID);
CREATE INDEX FIA_MS_RUNS_FK_EXPE_ID_IDX ON FIA_MS_RUNS(EXPE_ID);
-- Table FIA_PROFILES
CREATE TABLE FIA_PROFILES (
ID BIGSERIAL NOT NULL,
FIA_MS_RUN_ID TECH_ID NOT NULL,
LOW_MZ REAL NOT NULL,
HIGH_MZ REAL NOT NULL,
MZ TEXT NOT NULL,
INTENSITIES TEXT NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_FIA_PROFILE_1 FOREIGN KEY (FIA_MS_RUN_ID) REFERENCES FIA_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX FIA_PROFILE_I_ID on FIA_PROFILES(FIA_MS_RUN_ID);
CREATE INDEX FIA_PROFILE_I_ID_MZ on FIA_PROFILES(FIA_MS_RUN_ID, LOW_MZ, HIGH_MZ);
-- Table FIA_CENTROIDS
CREATE TABLE FIA_CENTROIDS (
ID BIGSERIAL NOT NULL,
FIA_MS_RUN_ID TECH_ID NOT NULL,
MZ REAL NOT NULL,
INTENSITY REAL NOT NULL,
CORRELATION REAL NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_FIA_CENTROID_1 FOREIGN KEY (FIA_MS_RUN_ID) REFERENCES FIA_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX FIA_CENTROID_I_ID ON FIA_CENTROIDS(FIA_MS_RUN_ID);
CREATE INDEX FIA_CENTROID_I_ID_MZ ON FIA_CENTROIDS(FIA_MS_RUN_ID, MZ);
-- Table MS_QUANTIFICATIONS
CREATE TABLE MS_QUANTIFICATIONS (
ID BIGSERIAL NOT NULL,
DS_ID TECH_ID NOT NULL,
EXPE_ID TECH_ID NOT NULL,
SOURCE MS_QUANTIFICATION_SOFTWARE_KIND NOT NULL,
VALID BOOLEAN NOT NULL,
COMMENT TEXT,
REGISTRATOR SHORT_LABEL DEFAULT NULL,
REGISTRATION_DATE TIMESTAMP DEFAULT NULL,
PRIMARY KEY (ID),
CONSTRAINT MS_QUANTIFICATION_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT MS_QUANTIFICATION_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX MS_QUANTIFICATION_FK_DS_ID_IDX ON MS_QUANTIFICATIONS(DS_ID);
CREATE INDEX MS_QUANTIFICATION_FK_EXPE_ID_IDX ON MS_QUANTIFICATIONS(EXPE_ID);
-- Table MS_QUANT_CONCENTRATIONS
CREATE TABLE MS_QUANT_CONCENTRATIONS (
ID BIGSERIAL NOT NULL,
MS_QUANTIFICATION_ID TECH_ID NOT NULL,
PARENT_DS_PERM_ID CODE NOT NULL,
AMOUNT REAL NOT NULL,
UNIT SHORT_LABEL NOT NULL,
VALID BOOLEAN NOT NULL,
COMMENT TEXT,
RETENTION_TIME REAL NOT NULL,
Q1 REAL NOT NULL,
Q3 REAL NOT NULL,
INTERNAL_STANDARD VARCHAR(30) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_QUANT_CONCENTRATIONS_1 FOREIGN KEY (MS_QUANTIFICATION_ID) REFERENCES MS_QUANTIFICATIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Table MS_QUANT_COMPOUND
CREATE TABLE MS_QUANT_COMPOUNDS (
ID BIGSERIAL NOT NULL,
MS_QUANT_CONCENTRATION_ID TECH_ID NOT NULL,
COMPOUND_ID TECH_ID NOT NULL,
CONSTRAINT FK_QUANT_COMPOUND_1 FOREIGN KEY (MS_QUANT_CONCENTRATION_ID) REFERENCES MS_QUANT_CONCENTRATIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Table EVENTS
CREATE TABLE EVENTS (
LAST_SEEN_DELETION_EVENT_ID TECH_ID NOT NULL
);
GRANT SELECT ON TABLE EXPERIMENTS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE SAMPLES TO GROUP metabol_readonly;
GRANT SELECT ON TABLE DATA_SETS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE EIC_MS_RUNS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE EIC_CHROMATOGRAMS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE FIA_MS_RUNS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE FIA_PROFILES TO GROUP metabol_readonly;
GRANT SELECT ON TABLE FIA_CENTROIDS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MS_QUANTIFICATIONS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MS_QUANT_CONCENTRATIONS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MS_QUANT_COMPOUNDS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE EVENTS TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE EXPERIMENTS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE SAMPLES_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE DATA_SETS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE EIC_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE EIC_CHROMATOGRAMS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE FIA_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE FIA_PROFILES_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE FIA_CENTROIDS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE MS_QUANTIFICATIONS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE MS_QUANT_CONCENTRATIONS_ID_SEQ TO GROUP metabol_readonly;
GRANT ALL PRIVILEGES ON TABLE EXPERIMENTS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE SAMPLES TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE DATA_SETS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE EIC_MS_RUNS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE EIC_CHROMATOGRAMS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE FIA_MS_RUNS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE FIA_PROFILES TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE FIA_CENTROIDS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MS_QUANTIFICATIONS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MS_QUANT_CONCENTRATIONS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MS_QUANT_COMPOUNDS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE EVENTS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE EXPERIMENTS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE SAMPLES_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE DATA_SETS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE EIC_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE EIC_CHROMATOGRAMS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE FIA_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE FIA_PROFILES_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE FIA_CENTROIDS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE MS_QUANTIFICATIONS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE MS_QUANT_CONCENTRATIONS_ID_SEQ TO GROUP metabol_readwrite;