Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-minfo.sql @ 5540:25c2505df28f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged
author Christian Lins <christian.lins@intevation.de>
date Wed, 03 Apr 2013 16:00:21 +0200
parents db6c7268b08e
children 88cbe798cbab
comparison
equal deleted inserted replaced
5539:8d0af912351c 5540:25c2505df28f
38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; 38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
39 39
40 CREATE TABLE bed_height_single ( 40 CREATE TABLE bed_height_single (
41 id int NOT NULL, 41 id int NOT NULL,
42 river_id int NOT NULL, 42 river_id int NOT NULL,
43 year int NOT NULL, 43 year int,
44 sounding_width int NOT NULL, 44 sounding_width int,
45 type_id int NOT NULL, 45 type_id int NOT NULL,
46 location_system_id int NOT NULL, 46 location_system_id int NOT NULL,
47 cur_elevation_model_id int NOT NULL, 47 cur_elevation_model_id int NOT NULL,
48 old_elevation_model_id int, 48 old_elevation_model_id int,
49 range_id int NOT NULL, 49 range_id int,
50 evaluation_by VARCHAR(255), 50 evaluation_by VARCHAR(255),
51 description VARCHAR(255), 51 description VARCHAR(255),
52 PRIMARY KEY(id), 52 PRIMARY KEY(id),
53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), 54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
67 time_interval_id int NOT NULL, 67 time_interval_id int NOT NULL,
68 -- sounding_with int NOT NULL, 68 -- sounding_with int NOT NULL,
69 -- type_id int NOT NULL, 69 -- type_id int NOT NULL,
70 cur_elevation_model_id int NOT NULL, 70 cur_elevation_model_id int NOT NULL,
71 old_elevation_model_id int, 71 old_elevation_model_id int,
72 range_id int NOT NULL, 72 range_id int,
73 evaluation_by VARCHAR(255), 73 evaluation_by VARCHAR(255),
74 description VARCHAR(255), 74 description VARCHAR(255),
75 PRIMARY KEY(id), 75 PRIMARY KEY(id),
76 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), 76 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
77 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), 77 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
112 112
113 CREATE TABLE depths ( 113 CREATE TABLE depths (
114 id int NOT NULL, 114 id int NOT NULL,
115 lower NUMERIC NOT NULL, 115 lower NUMERIC NOT NULL,
116 upper NUMERIC NOT NULL, 116 upper NUMERIC NOT NULL,
117 unit_id int NOT NULL, 117 PRIMARY KEY(id)
118 PRIMARY KEY(id),
119 CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
120 ); 118 );
121 119
122 120
123 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; 121 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
124 122
125 CREATE TABLE sediment_density ( 123 CREATE TABLE sediment_density (
126 id int NOT NULL, 124 id int NOT NULL,
127 river_id int NOT NULL, 125 river_id int NOT NULL,
128 depth_id int NOT NULL, 126 depth_id int NOT NULL,
129 unit_id int NOT NULL,
130 description VARCHAR(256), 127 description VARCHAR(256),
131 PRIMARY KEY(id), 128 PRIMARY KEY(id),
132 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 129 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
133 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), 130 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
134 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
135 ); 131 );
136 132
137 133
138 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; 134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
139 135
140 CREATE TABLE sediment_density_values ( 136 CREATE TABLE sediment_density_values (
141 id int NOT NULL, 137 id int NOT NULL,
142 sediment_density_id int NOT NULL, 138 sediment_density_id int NOT NULL,
143 station NUMERIC NOT NULL, 139 station NUMERIC NOT NULL,
140 shore_offset NUMERIC,
144 density NUMERIC NOT NULL, 141 density NUMERIC NOT NULL,
145 description VARCHAR(256), 142 description VARCHAR(256),
146 year int, 143 year int,
147 PRIMARY KEY(id), 144 PRIMARY KEY(id),
148 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE 145 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
285 CREATE TABLE measurement_station ( 282 CREATE TABLE measurement_station (
286 id int NOT NULL, 283 id int NOT NULL,
287 name VARCHAR(256) NOT NULL, 284 name VARCHAR(256) NOT NULL,
288 river_id int NOT NULL, 285 river_id int NOT NULL,
289 station NUMERIC NOT NULL, 286 station NUMERIC NOT NULL,
290 range_id int NOT NULL, 287 range_id int,
291 measurement_type VARCHAR(64) NOT NULL, 288 measurement_type VARCHAR(64) NOT NULL,
292 riverside VARCHAR(16), 289 riverside VARCHAR(16),
293 reference_gauge_id int, 290 reference_gauge_id int,
291 -- store name of reference gauges here too, as not all are in gauges
292 reference_gauge_name VARCHAR(64),
294 observation_timerange_id int, 293 observation_timerange_id int,
295 operator VARCHAR(64), 294 operator VARCHAR(64),
296 description VARCHAR(512), 295 description VARCHAR(512),
297 PRIMARY KEY (id), 296 PRIMARY KEY (id),
298 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, 297 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
299 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE, 298 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE,
300 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, 299 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE,
301 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id), 300 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id)
302 UNIQUE (river_id, station)
303 ); 301 );
304 302
305 303
306 CREATE SEQUENCE SQ_RELATION_ID_SEQ; 304 CREATE SEQUENCE SQ_RELATION_ID_SEQ;
307 305
317 315
318 316
319 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; 317 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
320 318
321 CREATE TABLE sq_relation_value ( 319 CREATE TABLE sq_relation_value (
322 id int NOT NULL, 320 id int NOT NULL,
323 sq_relation_id int NOT NULL, 321 sq_relation_id int NOT NULL,
324 parameter VARCHAR(16) NOT NULL, 322 measurement_station_id int NOT NULL,
325 fraction VARCHAR(32) NOT NULL, 323 parameter VARCHAR(1) NOT NULL,
326 function VARCHAR(32) NOT NULL, 324 a NUMERIC NOT NULL,
327 km NUMERIC NOT NULL, 325 b NUMERIC NOT NULL,
328 a NUMERIC NOT NULL, 326 qmax NUMERIC NOT NULL,
329 b NUMERIC NOT NULL, 327 rsq NUMERIC,
330 PRIMARY KEY (id), 328 ntot int,
331 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE 329 noutl int,
330 cferguson NUMERIC,
331 cduan NUMERIC,
332 PRIMARY KEY (id),
333 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE,
334 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id)
332 ); 335 );
333 COMMIT; 336 COMMIT;
http://dive4elements.wald.intevation.org