Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-minfo.sql @ 8945:4a6b6a3c279c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge
author mschaefer
date Tue, 13 Mar 2018 09:55:53 +0100
parents 71b17f731762
children f89fb9e9abad
comparison
equal deleted inserted replaced
8942:11bf13cf0463 8945:4a6b6a3c279c
1 BEGIN; 1 BEGIN;
2 2
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ; 3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
4 4
5 CREATE TABLE location_system ( 5 CREATE TABLE location_system (
6 id int NOT NULL, 6 id NUMERIC(9,0) NOT NULL,
7 name VARCHAR(32) NOT NULL, 7 name VARCHAR(32) NOT NULL,
8 description VARCHAR(255), 8 description VARCHAR(255),
9 PRIMARY KEY(id) 9 PRIMARY KEY(id)
10 ); 10 );
11 11
12 12
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ; 13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
14 14
15 CREATE TABLE elevation_model ( 15 CREATE TABLE elevation_model (
16 id int NOT NULL, 16 id NUMERIC(9,0) NOT NULL,
17 name VARCHAR(32) NOT NULL, 17 name VARCHAR(32) NOT NULL,
18 unit_id int NOT NULL, 18 unit_id NUMERIC(9,0) NOT NULL,
19 PRIMARY KEY(id), 19 PRIMARY KEY(id),
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) 20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
21 ); 21 );
22 22
23 23
24 -- lookup table for bedheight types 24 -- lookup table for bedheight types
25 CREATE TABLE bed_height_type ( 25 CREATE TABLE bed_height_type (
26 id int NOT NULL, 26 id NUMERIC(9,0) NOT NULL,
27 name VARCHAR(64) NOT NULL, 27 name VARCHAR(64) NOT NULL,
28 PRIMARY KEY(id) 28 PRIMARY KEY(id)
29 ); 29 );
30 INSERT INTO bed_height_type VALUES (1, 'Querprofile'); 30 INSERT INTO bed_height_type VALUES (1, 'Querprofile');
31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung'); 31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung');
36 36
37 37
38 CREATE SEQUENCE BED_HEIGHT_ID_SEQ; 38 CREATE SEQUENCE BED_HEIGHT_ID_SEQ;
39 39
40 CREATE TABLE bed_height ( 40 CREATE TABLE bed_height (
41 id int NOT NULL, 41 id NUMERIC(9,0) NOT NULL,
42 river_id int NOT NULL, 42 river_id NUMERIC(9,0) NOT NULL,
43 year int, 43 year NUMERIC(9,0),
44 type_id int NOT NULL, 44 type_id NUMERIC(9,0) NOT NULL,
45 location_system_id int NOT NULL, 45 location_system_id NUMERIC(9,0) NOT NULL,
46 cur_elevation_model_id int NOT NULL, 46 cur_elevation_model_id NUMERIC(9,0) NOT NULL,
47 old_elevation_model_id int, 47 old_elevation_model_id NUMERIC(9,0),
48 range_id int, 48 range_id NUMERIC(9,0),
49 evaluation_by VARCHAR(255), 49 evaluation_by VARCHAR(255),
50 description VARCHAR(255), 50 description VARCHAR(255),
51 PRIMARY KEY(id), 51 PRIMARY KEY(id),
52 CONSTRAINT fk_bh_river_id FOREIGN KEY (river_id) 52 CONSTRAINT fk_bh_river_id FOREIGN KEY (river_id)
53 REFERENCES rivers(id) ON DELETE CASCADE, 53 REFERENCES rivers(id) ON DELETE CASCADE,
63 ); 63 );
64 64
65 65
66 CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ; 66 CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ;
67 67
68 --FIXME: make precision and scale of station column equal with the km columns of the other tables
69 --FIXME: replace double precision with exact types
68 CREATE TABLE bed_height_values ( 70 CREATE TABLE bed_height_values (
69 id int NOT NULL, 71 id NUMERIC(9,0) NOT NULL,
70 bed_height_id int NOT NULL, 72 bed_height_id NUMERIC(9,0) NOT NULL,
71 station NUMERIC NOT NULL, 73 station DOUBLE PRECISION NOT NULL,
72 height NUMERIC, 74 height DOUBLE PRECISION,
73 uncertainty NUMERIC, 75 uncertainty DOUBLE PRECISION,
74 data_gap NUMERIC, 76 data_gap DOUBLE PRECISION,
75 sounding_width NUMERIC, 77 sounding_width DOUBLE PRECISION,
76 PRIMARY KEY(id), 78 PRIMARY KEY(id),
77 UNIQUE (station, bed_height_id), 79 UNIQUE (station, bed_height_id),
78 CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id) 80 CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id)
79 REFERENCES bed_height(id) ON DELETE CASCADE 81 REFERENCES bed_height(id) ON DELETE CASCADE
80 ); 82 );
81 83
82 84
83 CREATE SEQUENCE DEPTHS_ID_SEQ; 85 CREATE SEQUENCE DEPTHS_ID_SEQ;
84 86
85 CREATE TABLE depths ( 87 CREATE TABLE depths (
86 id int NOT NULL, 88 id NUMERIC(9,0) NOT NULL,
87 lower NUMERIC NOT NULL, 89 lower NUMERIC(6,2) NOT NULL,
88 upper NUMERIC NOT NULL, 90 upper NUMERIC(6,2) NOT NULL,
89 PRIMARY KEY(id) 91 PRIMARY KEY(id)
90 ); 92 );
91 93
92 94
93 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; 95 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
94 96
95 CREATE TABLE sediment_density ( 97 CREATE TABLE sediment_density (
96 id int NOT NULL, 98 id NUMERIC(9,0) NOT NULL,
97 river_id int NOT NULL, 99 river_id NUMERIC(9,0) NOT NULL,
98 depth_id int NOT NULL, 100 depth_id NUMERIC(9,0) NOT NULL,
99 description VARCHAR(256), 101 description VARCHAR(256),
100 PRIMARY KEY(id), 102 PRIMARY KEY(id),
101 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 103 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
102 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id) 104 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
103 ); 105 );
104 106
105 107
106 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; 108 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
107 109
110 --FIXME: make precision and scale of station column equal with the km columns of the other tables
108 CREATE TABLE sediment_density_values ( 111 CREATE TABLE sediment_density_values (
109 id int NOT NULL, 112 id NUMERIC(9,0) NOT NULL,
110 sediment_density_id int NOT NULL, 113 sediment_density_id NUMERIC(9,0) NOT NULL,
111 station NUMERIC NOT NULL, 114 station NUMERIC(6,2) NOT NULL,
112 shore_offset NUMERIC, 115 shore_offset NUMERIC(6,2),
113 density NUMERIC NOT NULL, 116 density NUMERIC(8,2) NOT NULL,
114 description VARCHAR(256), 117 description VARCHAR(256),
115 year int, 118 year NUMERIC(4,0),
116 PRIMARY KEY(id), 119 PRIMARY KEY(id),
117 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE 120 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
118 ); 121 );
119 122
120 123
121 CREATE SEQUENCE POROSITY_ID_SEQ; 124 CREATE SEQUENCE POROSITY_ID_SEQ;
122 125
123 CREATE TABLE porosity ( 126 CREATE TABLE porosity (
124 id int NOT NULL, 127 id NUMERIC(9,0) NOT NULL,
125 river_id int NOT NULL, 128 river_id NUMERIC(9,0) NOT NULL,
126 depth_id int NOT NULL, 129 depth_id NUMERIC(9,0) NOT NULL,
127 description VARCHAR(256), 130 description VARCHAR(256),
128 time_interval_id int NOT NULL, 131 time_interval_id NUMERIC(9,0) NOT NULL,
129 PRIMARY KEY(id), 132 PRIMARY KEY(id),
130 CONSTRAINT fk_p_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 133 CONSTRAINT fk_p_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
131 CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), 134 CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
132 CONSTRAINT fk_p_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) 135 CONSTRAINT fk_p_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
133 ); 136 );
134 137
135 138
136 CREATE SEQUENCE POROSITY_VALUES_ID_SEQ; 139 CREATE SEQUENCE POROSITY_VALUES_ID_SEQ;
137 140
141 --FIXME: make precision and scale of station column equal with the km columns of the other tables
138 CREATE TABLE porosity_values ( 142 CREATE TABLE porosity_values (
139 id int NOT NULL, 143 id NUMERIC(9,0) NOT NULL,
140 porosity_id int NOT NULL, 144 porosity_id NUMERIC(9,0) NOT NULL,
141 station NUMERIC NOT NULL, 145 station DOUBLE PRECISION NOT NULL,
142 shore_offset NUMERIC, 146 shore_offset DOUBLE PRECISION,
143 porosity NUMERIC NOT NULL, 147 porosity DOUBLE PRECISION NOT NULL,
144 description VARCHAR(256), 148 description VARCHAR(256),
145 PRIMARY KEY(id), 149 PRIMARY KEY(id),
146 CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE 150 CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE
147 ); 151 );
148 152
149 153
150 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; 154 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
151 155
152 CREATE TABLE morphologic_width ( 156 CREATE TABLE morphologic_width (
153 id int NOT NULL, 157 id NUMERIC(9,0) NOT NULL,
154 river_id int NOT NULL, 158 river_id NUMERIC(9,0) NOT NULL,
155 unit_id int NOT NULL, 159 unit_id NUMERIC(9,0) NOT NULL,
156 PRIMARY KEY(id), 160 PRIMARY KEY(id),
157 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, 161 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
158 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) 162 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
159 ); 163 );
160 164
161 165
162 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; 166 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
163 167
164 CREATE TABLE morphologic_width_values ( 168 CREATE TABLE morphologic_width_values (
165 id int NOT NULL, 169 id NUMERIC(9,0) NOT NULL,
166 morphologic_width_id int NOT NULL, 170 morphologic_width_id NUMERIC(9,0) NOT NULL,
167 station NUMERIC NOT NULL, 171 station NUMERIC(7,3) NOT NULL,
168 width NUMERIC NOT NULL, 172 width NUMERIC(7,3) NOT NULL,
169 description VARCHAR(256), 173 description VARCHAR(256),
170 PRIMARY KEY(id), 174 PRIMARY KEY(id),
171 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE 175 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
172 ); 176 );
173 177
174 178
175 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; 179 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
176 180
177 CREATE TABLE discharge_zone ( 181 CREATE TABLE discharge_zone (
178 id int NOT NULL, 182 id NUMERIC(9,0) NOT NULL,
179 river_id int NOT NULL, 183 river_id NUMERIC(9,0) NOT NULL,
180 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance 184 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
181 value NUMERIC NOT NULL, 185 value NUMERIC(8,3) NOT NULL,
182 lower_discharge VARCHAR(64) NOT NULL, 186 lower_discharge VARCHAR(64) NOT NULL,
183 upper_discharge VARCHAR(64), 187 upper_discharge VARCHAR(64),
184 PRIMARY KEY(id), 188 PRIMARY KEY(id),
185 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE 189 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
186 ); 190 );
187 191
188 192
189 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; 193 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
190 194
191 CREATE TABLE flow_velocity_model ( 195 CREATE TABLE flow_velocity_model (
192 id int NOT NULL, 196 id NUMERIC(9,0) NOT NULL,
193 discharge_zone_id int NOT NULL, 197 discharge_zone_id NUMERIC(9,0) NOT NULL,
194 description VARCHAR(256), 198 description VARCHAR(256),
195 PRIMARY KEY (id), 199 PRIMARY KEY (id),
196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE 200 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
197 ); 201 );
198 202
199 203
200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; 204 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
201 205
202 CREATE TABLE flow_velocity_model_values ( 206 CREATE TABLE flow_velocity_model_values (
203 id int NOT NULL, 207 id NUMERIC(9,0) NOT NULL,
204 flow_velocity_model_id int NOT NULL, 208 flow_velocity_model_id NUMERIC(9,0) NOT NULL,
205 station NUMERIC NOT NULL, 209 station NUMERIC(7,3) NOT NULL,
206 q NUMERIC NOT NULL, 210 q NUMERIC(8,3) NOT NULL,
207 total_channel NUMERIC NOT NULL, 211 total_channel NUMERIC(5,3) NOT NULL,
208 main_channel NUMERIC NOT NULL, 212 main_channel NUMERIC(5,3) NOT NULL,
209 shear_stress NUMERIC NOT NULL, 213 shear_stress NUMERIC(6,3) NOT NULL,
210 PRIMARY KEY(id), 214 PRIMARY KEY(id),
211 UNIQUE (station, flow_velocity_model_id), 215 UNIQUE (station, flow_velocity_model_id),
212 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE 216 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
213 ); 217 );
214 218
215 219
216 220
217 CREATE SEQUENCE FV_MEASURE_ID_SEQ; 221 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
218 222
219 CREATE TABLE flow_velocity_measurements ( 223 CREATE TABLE flow_velocity_measurements (
220 id int NOT NULL, 224 id NUMERIC(9,0) NOT NULL,
221 river_id int NOT NULL, 225 river_id NUMERIC(9,0) NOT NULL,
222 description VARCHAR(256), 226 description VARCHAR(256),
223 PRIMARY KEY (id), 227 PRIMARY KEY (id),
224 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE 228 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
225 ); 229 );
226 230
227 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; 231 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
228 232
229 CREATE TABLE flow_velocity_measure_values ( 233 CREATE TABLE flow_velocity_measure_values (
230 id int NOT NULL, 234 id NUMERIC(9,0) NOT NULL,
231 measurements_id int NOT NULL, 235 measurements_id NUMERIC(9,0) NOT NULL,
232 station NUMERIC NOT NULL, 236 station NUMERIC(7,3) NOT NULL,
233 datetime TIMESTAMP, 237 datetime TIMESTAMP(0),
234 w NUMERIC NOT NULL, 238 w NUMERIC(7,3) NOT NULL,
235 q NUMERIC NOT NULL, 239 q NUMERIC(8,3) NOT NULL,
236 v NUMERIC NOT NULL, 240 v NUMERIC(5,3) NOT NULL,
237 description VARCHAR(256), 241 description VARCHAR(256),
238 PRIMARY KEY (id), 242 PRIMARY KEY (id),
239 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE 243 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
240 ); 244 );
241 245
242 246
243 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; 247 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
244 248
245 CREATE TABLE grain_fraction ( 249 CREATE TABLE grain_fraction (
246 id int NOT NULL, 250 id NUMERIC(9,0) NOT NULL,
247 name VARCHAR(64) NOT NULL, 251 name VARCHAR(64) NOT NULL,
248 lower NUMERIC, 252 lower NUMERIC(6,3),
249 upper NUMERIC, 253 upper NUMERIC(6,3),
250 PRIMARY KEY (id), 254 PRIMARY KEY (id),
251 UNIQUE(name, lower, upper) 255 UNIQUE(name, lower, upper)
252 ); 256 );
253 -- single fractions 257 -- single fractions
254 INSERT INTO grain_fraction VALUES (1, 'coarse', 16, 200); 258 INSERT INTO grain_fraction VALUES (1, 'coarse', 16, 200);
263 INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2); 267 INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2);
264 268
265 269
266 --lookup table for sediment load kinds 270 --lookup table for sediment load kinds
267 CREATE TABLE sediment_load_kinds ( 271 CREATE TABLE sediment_load_kinds (
268 id int PRIMARY KEY NOT NULL, 272 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
269 kind VARCHAR(64) NOT NULL 273 kind VARCHAR(64) NOT NULL
270 ); 274 );
271 INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official'); 275 INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official');
272 INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official'); 276 INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official');
273 277
274 CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ; 278 CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ;
275 279
276 CREATE TABLE sediment_load_ls ( 280 CREATE TABLE sediment_load_ls (
277 id int NOT NULL, 281 id NUMERIC(9,0) NOT NULL,
278 river_id int NOT NULL, 282 river_id NUMERIC(9,0) NOT NULL,
279 grain_fraction_id int, 283 grain_fraction_id NUMERIC(9,0),
280 unit_id int NOT NULL, 284 unit_id NUMERIC(9,0) NOT NULL,
281 time_interval_id int NOT NULL, 285 time_interval_id NUMERIC(9,0) NOT NULL,
282 sq_time_interval_id int, 286 sq_time_interval_id NUMERIC(9,0),
283 description VARCHAR(256), 287 description VARCHAR(256),
284 kind int, 288 kind NUMERIC(9,0),
285 PRIMARY KEY (id), 289 PRIMARY KEY (id),
286 CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id) 290 CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id)
287 REFERENCES rivers(id) ON DELETE CASCADE, 291 REFERENCES rivers(id) ON DELETE CASCADE,
288 CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind) 292 CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind)
289 REFERENCES sediment_load_kinds(id), 293 REFERENCES sediment_load_kinds(id),
299 303
300 304
301 CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ; 305 CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ;
302 306
303 CREATE TABLE sediment_load_ls_values ( 307 CREATE TABLE sediment_load_ls_values (
304 id int NOT NULL, 308 id NUMERIC(9,0) NOT NULL,
305 sediment_load_ls_id int NOT NULL, 309 sediment_load_ls_id NUMERIC(9,0) NOT NULL,
306 station NUMERIC NOT NULL, 310 station NUMERIC(7,3) NOT NULL,
307 value NUMERIC NOT NULL, 311 value NUMERIC(10,3) NOT NULL,
308 PRIMARY KEY (id), 312 PRIMARY KEY (id),
309 CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id) 313 CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id)
310 REFERENCES sediment_load_ls(id) ON DELETE CASCADE 314 REFERENCES sediment_load_ls(id) ON DELETE CASCADE
311 ); 315 );
312 316
313 317
314 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; 318 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
315 CREATE TABLE measurement_station ( 319 CREATE TABLE measurement_station (
316 id int NOT NULL, 320 id NUMERIC(9,0) NOT NULL,
317 range_id int NOT NULL, 321 range_id NUMERIC(9,0) NOT NULL,
318 reference_gauge_id int, 322 reference_gauge_id NUMERIC(9,0),
319 time_interval_id int, 323 time_interval_id NUMERIC(9,0),
320 name VARCHAR(256) NOT NULL, 324 name VARCHAR(256) NOT NULL,
321 measurement_type VARCHAR(64) NOT NULL, 325 measurement_type VARCHAR(64) NOT NULL,
322 riverside VARCHAR(16), 326 riverside VARCHAR(16),
323 -- store name of reference gauges here too, as not all are in gauges 327 -- store name of reference gauges here too, as not all are in gauges
324 reference_gauge_name VARCHAR(64), 328 reference_gauge_name VARCHAR(64),
336 340
337 341
338 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; 342 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ;
339 343
340 CREATE TABLE sediment_load ( 344 CREATE TABLE sediment_load (
341 id int NOT NULL, 345 id NUMERIC(9,0) NOT NULL,
342 grain_fraction_id int NOT NULL, 346 grain_fraction_id NUMERIC(9,0) NOT NULL,
343 time_interval_id int NOT NULL, 347 time_interval_id NUMERIC(9,0) NOT NULL,
344 sq_time_interval_id int, 348 sq_time_interval_id NUMERIC(9,0),
345 description VARCHAR(256), 349 description VARCHAR(256),
346 kind int, 350 kind NUMERIC(9,0),
347 PRIMARY KEY (id), 351 PRIMARY KEY (id),
348 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) 352 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind)
349 REFERENCES sediment_load_kinds(id), 353 REFERENCES sediment_load_kinds(id),
350 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) 354 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id)
351 REFERENCES grain_fraction(id), 355 REFERENCES grain_fraction(id),
357 361
358 362
359 CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; 363 CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ;
360 364
361 CREATE TABLE sediment_load_values ( 365 CREATE TABLE sediment_load_values (
362 id int NOT NULL, 366 id NUMERIC(9,0) NOT NULL,
363 sediment_load_id int NOT NULL, 367 sediment_load_id NUMERIC(9,0) NOT NULL,
364 measurement_station_id int NOT NULL, 368 measurement_station_id NUMERIC(9,0) NOT NULL,
365 value DOUBLE PRECISION NOT NULL, 369 value DOUBLE PRECISION NOT NULL,
366 PRIMARY KEY (id), 370 PRIMARY KEY (id),
367 CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) 371 CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id)
368 REFERENCES sediment_load(id) ON DELETE CASCADE, 372 REFERENCES sediment_load(id) ON DELETE CASCADE,
369 CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id) 373 CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id)
372 376
373 377
374 CREATE SEQUENCE SQ_RELATION_ID_SEQ; 378 CREATE SEQUENCE SQ_RELATION_ID_SEQ;
375 379
376 CREATE TABLE sq_relation ( 380 CREATE TABLE sq_relation (
377 id int NOT NULL, 381 id NUMERIC(9,0) NOT NULL,
378 time_interval_id int NOT NULL, 382 time_interval_id NUMERIC(9,0) NOT NULL,
379 description VARCHAR(256), 383 description VARCHAR(256),
380 PRIMARY KEY (id), 384 PRIMARY KEY (id),
381 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) 385 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id)
382 REFERENCES time_intervals(id) 386 REFERENCES time_intervals(id)
383 ); 387 );
384 388
385 389
386 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; 390 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
387 391
392 --FIXME: adjust precision and scale of the numerics
388 CREATE TABLE sq_relation_value ( 393 CREATE TABLE sq_relation_value (
389 id int NOT NULL, 394 id NUMERIC(9,0) NOT NULL,
390 sq_relation_id int NOT NULL, 395 sq_relation_id NUMERIC(9,0) NOT NULL,
391 measurement_station_id int NOT NULL, 396 measurement_station_id NUMERIC(9,0) NOT NULL,
392 parameter VARCHAR(1) NOT NULL, 397 parameter VARCHAR(1) NOT NULL,
393 a NUMERIC NOT NULL, 398 a NUMERIC(38,20) NOT NULL,
394 b NUMERIC NOT NULL, 399 b NUMERIC(38,20) NOT NULL,
395 qmax NUMERIC NOT NULL, 400 qmax NUMERIC(38,20) NOT NULL,
396 rsq NUMERIC, 401 rsq NUMERIC(38,3),
397 ntot int, 402 ntot NUMERIC(9,0),
398 noutl int, 403 noutl NUMERIC(9,0),
399 cferguson NUMERIC, 404 cferguson NUMERIC(38,20),
400 cduan NUMERIC, 405 cduan NUMERIC(38,20),
401 PRIMARY KEY (id), 406 PRIMARY KEY (id),
402 UNIQUE(sq_relation_id, measurement_station_id, parameter), 407 UNIQUE(sq_relation_id, measurement_station_id, parameter),
403 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) 408 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id)
404 REFERENCES sq_relation(id) ON DELETE CASCADE, 409 REFERENCES sq_relation(id) ON DELETE CASCADE,
405 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) 410 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id)
http://dive4elements.wald.intevation.org