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
/* ---------------------------------------------------------------------- */
/* Domains */
/* ---------------------------------------------------------------------- */
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN CODE AS VARCHAR(40);
CREATE DOMAIN NAME AS VARCHAR(80);
CREATE DOMAIN DESCRIPTION AS VARCHAR(200);
CREATE DOMAIN FILE_PATH as VARCHAR(1000);
CREATE DOMAIN COLOR_COMPONENT AS VARCHAR(40) CHECK (VALUE IN ('RED', 'GREEN', 'BLUE'));
/* ---------------------------------------------------------------------- */
/* Tables */
/* ---------------------------------------------------------------------- */
CREATE TABLE EXPERIMENTS (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
PRIMARY KEY (ID),
UNIQUE (PERM_ID)
);
CREATE TABLE CONTAINERS (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
SPOTS_WIDTH INTEGER,
SPOTS_HEIGHT INTEGER,
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
);
CREATE INDEX CONTAINERS_EXPE_IDX ON CONTAINERS(EXPE_ID);
CREATE TABLE SPOTS (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
-- position in the container, one-based
X INTEGER,
Y INTEGER,
CONT_ID TECH_ID NOT NULL,
PRIMARY KEY (ID),
UNIQUE (PERM_ID),
CONSTRAINT FK_SPOT_1 FOREIGN KEY (CONT_ID) REFERENCES CONTAINERS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX SPOTS_CONT_IDX ON SPOTS(CONT_ID);
CREATE TABLE DATA_SETS (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
FIELDS_WIDTH INTEGER,
FIELDS_HEIGHT INTEGER,
CONT_ID TECH_ID NOT NULL,
PRIMARY KEY (ID),
UNIQUE (PERM_ID),
CONSTRAINT FK_DATA_SET_1 FOREIGN KEY (CONT_ID) REFERENCES CONTAINERS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX DATA_SETS_CONT_IDX ON DATA_SETS(CONT_ID);
CREATE TABLE CHANNELS (
ID BIGSERIAL NOT NULL,
NAME NAME NOT NULL,
DESCRIPTION DESCRIPTION,
WAVELENGTH INTEGER,
DS_ID TECH_ID,
EXP_ID TECH_ID,
PRIMARY KEY (ID),
CONSTRAINT FK_CHANNELS_1 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_CHANNELS_2 FOREIGN KEY (EXP_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT CHANNELS_DS_EXP_ARC_CK CHECK ((DS_ID IS NOT NULL AND EXP_ID IS NULL) OR (DS_ID IS NULL AND EXP_ID IS NOT NULL)),
CONSTRAINT CHANNELS_UK_1 UNIQUE(NAME, DS_ID),
CONSTRAINT CHANNELS_UK_2 UNIQUE(NAME, EXP_ID)
);
CREATE INDEX CHANNELS_DS_IDX ON CHANNELS(DS_ID);
CREATE TABLE CHANNEL_STACKS (
ID BIGSERIAL NOT NULL,
-- x and y are kind of a two dimensional sequence number, some use case may only use x and leave y alone
X INTEGER,
Y INTEGER,
-- we use the fixed dimension meters here
Z_in_M REAL,
-- we use the fixed dimension seconds here
T_in_SEC REAL,
DS_ID TECH_ID NOT NULL,
SPOT_ID TECH_ID NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_CHANNEL_STACKS_1 FOREIGN KEY (SPOT_ID) REFERENCES SPOTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_CHANNEL_STACKS_2 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX CHANNEL_STACKS_DS_IDX ON CHANNEL_STACKS(DS_ID);
CREATE INDEX CHANNEL_STACKS_SPOT_IDX ON CHANNEL_STACKS(SPOT_ID);
CREATE INDEX CHANNEL_STACKS_DIM_IDX ON CHANNEL_STACKS(X, Y, Z_in_M, T_in_SEC);
CREATE TABLE IMAGES (
ID BIGSERIAL NOT NULL,
PATH FILE_PATH NOT NULL,
PAGE INTEGER,
COLOR COLOR_COMPONENT,
PRIMARY KEY (ID)
);
CREATE TABLE ACQUIRED_IMAGES (
ID BIGSERIAL NOT NULL,
IMG_ID TECH_ID NOT NULL,
THUMBNAIL_ID TECH_ID,
CHANNEL_STACK_ID TECH_ID NOT NULL,
CHANNEL_ID TECH_ID NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_IMAGES_1 FOREIGN KEY (CHANNEL_STACK_ID) REFERENCES CHANNEL_STACKS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_IMAGES_2 FOREIGN KEY (CHANNEL_ID) REFERENCES CHANNELS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_IMAGES_3 FOREIGN KEY (IMG_ID) REFERENCES IMAGES (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_IMAGES_4 FOREIGN KEY (THUMBNAIL_ID) REFERENCES IMAGES (ID) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX IMAGES_CHANNEL_STACK_IDX ON ACQUIRED_IMAGES(CHANNEL_STACK_ID);
CREATE INDEX IMAGES_CHANNEL_IDX ON ACQUIRED_IMAGES(CHANNEL_ID);
CREATE INDEX IMAGES_IMG_IDX ON ACQUIRED_IMAGES(IMG_ID);
CREATE INDEX IMAGES_THUMBNAIL_IDX ON ACQUIRED_IMAGES(THUMBNAIL_ID);
/* ---------------------------------------------------------------------- */
/* FEATURE VECTORS */
/* ---------------------------------------------------------------------- */
CREATE TABLE FEATURE_DEFS (
ID BIGSERIAL NOT NULL,
NAME NAME NOT NULL,
DESCRIPTION DESCRIPTION,
DS_ID TECH_ID NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_FEATURE_DEFS_1 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FEATURE_DEFS_UK_1 UNIQUE(NAME, DS_ID)
);
CREATE INDEX FEATURE_DEFS_DS_IDX ON FEATURE_DEFS(DS_ID);
CREATE TABLE FEATURE_VALUES (
ID BIGSERIAL NOT NULL,
-- we use the fixed dimension meters here
Z_in_M REAL,
-- we use the fixed dimension seconds here
T_in_SEC REAL,
-- serialized 2D matrix with values for each spot
VALUES BYTEA NOT NULL,
FD_ID TECH_ID NOT NULL,
DS_ID TECH_ID NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT FK_FEATURE_VALUES_1 FOREIGN KEY (FD_ID) REFERENCES FEATURE_DEFS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_FEATURE_VALUES_2 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
-- This constaint does not make any sense. Leave it out for now.
-- CONSTRAINT FEATURE_VALUES_UK_1 UNIQUE(Z_in_M, T_in_SEC)