Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql-minfo.sql @ 8965:f89fb9e9abad

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