Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-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 5f91881124ba
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 NUMBER(38,0) NOT NULL, 41 id NUMBER(38,0) NOT NULL,
42 river_id NUMBER(38,0) NOT NULL, 42 river_id NUMBER(38,0) NOT NULL,
43 year NUMBER(38,0) NOT NULL, 43 year NUMBER(38,0),
44 sounding_width NUMBER(38,0) NOT NULL, 44 sounding_width NUMBER(38,0),
45 type_id NUMBER(38,0) NOT NULL, 45 type_id NUMBER(38,0) NOT NULL,
46 location_system_id NUMBER(38,0) NOT NULL, 46 location_system_id NUMBER(38,0) NOT NULL,
47 cur_elevation_model_id NUMBER(38,0) NOT NULL, 47 cur_elevation_model_id NUMBER(38,0) NOT NULL,
48 old_elevation_model_id NUMBER(38,0), 48 old_elevation_model_id NUMBER(38,0),
49 range_id NUMBER(38,0) NOT NULL, 49 range_id NUMBER(38,0),
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), 53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
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 NUMBER(38,0) NOT NULL, 67 time_interval_id NUMBER(38,0) NOT NULL,
68 -- sounding_with NUMBER(38,0) NOT NULL, 68 -- sounding_with NUMBER(38,0) NOT NULL,
69 -- type_id NUMBER(38,0) NOT NULL, 69 -- type_id NUMBER(38,0) NOT NULL,
70 cur_elevation_model_id NUMBER(38,0) NOT NULL, 70 cur_elevation_model_id NUMBER(38,0) NOT NULL,
71 old_elevation_model_id NUMBER(38,0), 71 old_elevation_model_id NUMBER(38,0),
72 range_id NUMBER(38,0) NOT NULL, 72 range_id NUMBER(38,0),
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 NUMBER(38,0) NOT NULL, 114 id NUMBER(38,0) NOT NULL,
115 lower NUMBER(38,2) NOT NULL, 115 lower NUMBER(38,2) NOT NULL,
116 upper NUMBER(38,2) NOT NULL, 116 upper NUMBER(38,2) NOT NULL,
117 unit_id NUMBER(38,0) 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 NUMBER(38,0) NOT NULL, 124 id NUMBER(38,0) NOT NULL,
127 river_id NUMBER(38,0) NOT NULL, 125 river_id NUMBER(38,0) NOT NULL,
128 depth_id NUMBER(38,0) NOT NULL, 126 depth_id NUMBER(38,0) NOT NULL,
129 unit_id NUMBER(38,0) 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), 129 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
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 NUMBER(38,0) NOT NULL, 137 id NUMBER(38,0) NOT NULL,
142 sediment_density_id NUMBER(38,0) NOT NULL, 138 sediment_density_id NUMBER(38,0) NOT NULL,
143 station NUMBER(38,2) NOT NULL, 139 station NUMBER(38,2) NOT NULL,
140 shore_offset NUMBER(38,2),
144 density NUMBER(38,2) NOT NULL, 141 density NUMBER(38,2) NOT NULL,
145 description VARCHAR(256), 142 description VARCHAR(256),
146 year NUMBER(38,0), 143 year NUMBER(38,0),
147 PRIMARY KEY(id), 144 PRIMARY KEY(id),
148 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) 145 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
285 CREATE TABLE measurement_station ( 282 CREATE TABLE measurement_station (
286 id NUMBER(38) NOT NULL, 283 id NUMBER(38) NOT NULL,
287 name VARCHAR(256) NOT NULL, 284 name VARCHAR(256) NOT NULL,
288 river_id NUMBER(38) NOT NULL, 285 river_id NUMBER(38) NOT NULL,
289 station NUMBER(38,3) NOT NULL, 286 station NUMBER(38,3) NOT NULL,
290 range_id NUMBER(38) NOT NULL, 287 range_id NUMBER(38),
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 NUMBER(38), 290 reference_gauge_id NUMBER(38),
291 -- store name of reference gauges here too, as not all are in gauges
292 reference_gauge_name VARCHAR(64),
294 observation_timerange_id NUMBER(38), 293 observation_timerange_id NUMBER(38),
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 NUMBER(38,0) NOT NULL, 320 id NUMBER(38,0) NOT NULL,
323 sq_relation_id NUMBER(38,0) NOT NULL, 321 sq_relation_id NUMBER(38,0) NOT NULL,
324 parameter VARCHAR(16) NOT NULL, 322 measurement_station_id NUMBER(38,0) NOT NULL,
325 fraction VARCHAR(32) NOT NULL, 323 parameter VARCHAR(1) NOT NULL,
326 function VARCHAR(32) NOT NULL, 324 a NUMBER(38,20) NOT NULL,
327 km NUMBER(38,3) NOT NULL, 325 b NUMBER(38,20) NOT NULL,
328 a NUMBER(38, 3) NOT NULL, 326 qmax NUMBER(38,20) NOT NULL,
329 b NUMBER(38,3) NOT NULL, 327 rsq NUMBER(38,3),
330 PRIMARY KEY (id), 328 ntot NUMBER(38,0),
331 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) 329 noutl NUMBER(38,0),
332 ); 330 cferguson NUMBER(38,20),
331 cduan NUMBER(38,20),
332 PRIMARY KEY (id),
333 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id),
334 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id)
335 );
http://dive4elements.wald.intevation.org