Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-minfo.sql @ 2877:f0a67bc0e777 2.7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/2.7
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:31 +0200
parents c36106f15ac9
children a2a33eff93aa
comparison
equal deleted inserted replaced
2793:6310b1582f2d 2877:f0a67bc0e777
1 SET AUTOCOMMIT ON;
2
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
4
5 CREATE TABLE location_system (
6 id NUMBER(38,0) NOT NULL,
7 name VARCHAR(32) NOT NULL,
8 description VARCHAR(255),
9 PRIMARY KEY(id)
10 );
11
12
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
14
15 CREATE TABLE elevation_model (
16 id NUMBER(38,0) NOT NULL,
17 name VARCHAR(32) NOT NULL,
18 unit_id NUMBER(38,0) NOT NULL,
19 PRIMARY KEY(id),
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
21 );
22
23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
24
25 CREATE TABLE bed_height_type (
26 id NUMBER(38,0) NOT NULL,
27 name VARCHAR(16) NOT NULL,
28 description VARCHAR(255),
29 PRIMARY KEY(id)
30 );
31
32
33
34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
35
36 CREATE TABLE bed_height_single (
37 id NUMBER(38,0) NOT NULL,
38 river_id NUMBER(38,0) NOT NULL,
39 year NUMBER(38,0) NOT NULL,
40 sounding_width NUMBER(38,0) NOT NULL,
41 type_id NUMBER(38,0) NOT NULL,
42 location_system_id NUMBER(38,0) NOT NULL,
43 cur_elevation_model_id NUMBER(38,0) NOT NULL,
44 old_elevation_model_id NUMBER(38,0),
45 range_id NUMBER(38,0) NOT NULL,
46 evaluation_by VARCHAR(255),
47 description VARCHAR(255),
48 PRIMARY KEY(id),
49 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
50 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
51 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
52 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
53 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
54 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
55 );
56
57
58 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
59
60 CREATE TABLE bed_height_epoch (
61 id NUMBER(38,0) NOT NULL,
62 river_id NUMBER(38,0) NOT NULL,
63 time_interval_id NUMBER(38,0) NOT NULL,
64 -- sounding_with NUMBER(38,0) NOT NULL,
65 -- type_id NUMBER(38,0) NOT NULL,
66 cur_elevation_model_id NUMBER(38,0) NOT NULL,
67 old_elevation_model_id NUMBER(38,0),
68 range_id NUMBER(38,0) NOT NULL,
69 evaluation_by VARCHAR(255),
70 description VARCHAR(255),
71 PRIMARY KEY(id),
72 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
73 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
74 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
75 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
76 );
77
78
79 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
80
81 CREATE TABLE bed_height_single_values (
82 id NUMBER(38,0) NOT NULL,
83 bed_height_single_id NUMBER(38,0) NOT NULL,
84 station NUMBER(38,2) NOT NULL,
85 height NUMBER(38,2),
86 uncertainty NUMBER(38,2),
87 data_gap NUMBER(38,2) NOT NULL,
88 sounding_width NUMBER(38,2) NOT NULL,
89 width NUMBER(38,2) NOT NULL,
90 PRIMARY KEY(id),
91 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
92 );
93
94
95 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
96
97 CREATE TABLE bed_height_epoch_values (
98 id NUMBER(38,0) NOT NULL,
99 bed_height_epoch_id NUMBER(38,0) NOT NULL,
100 station NUMBER(38,2) NOT NULL,
101 height NUMBER(38,2),
102 PRIMARY KEY(id),
103 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
104 );
105
106
107 CREATE SEQUENCE DEPTHS_ID_SEQ;
108
109 CREATE TABLE depths (
110 id NUMBER(38,0) NOT NULL,
111 lower NUMBER(38,2) NOT NULL,
112 upper NUMBER(38,2) NOT NULL,
113 unit_id NUMBER(38,0) NOT NULL,
114 PRIMARY KEY(id),
115 CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
116 );
117
118
119 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
120
121 CREATE TABLE sediment_density (
122 id NUMBER(38,0) NOT NULL,
123 river_id NUMBER(38,0) NOT NULL,
124 depth_id NUMBER(38,0) NOT NULL,
125 unit_id NUMBER(38,0) NOT NULL,
126 description VARCHAR(256),
127 PRIMARY KEY(id),
128 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
129 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
130 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
131 );
132
133
134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
135
136 CREATE TABLE sediment_density_values (
137 id NUMBER(38,0) NOT NULL,
138 sediment_density_id NUMBER(38,0) NOT NULL,
139 station NUMBER(38,2) NOT NULL,
140 density NUMBER(38,2) NOT NULL,
141 description VARCHAR(256),
142 PRIMARY KEY(id),
143 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
144 );
145
146
147 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
148
149 CREATE TABLE morphologic_width (
150 id NUMBER(38,0) NOT NULL,
151 river_id NUMBER(38,0) NOT NULL,
152 unit_id NUMBER(38,0) NOT NULL,
153 PRIMARY KEY(id),
154 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
155 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
156 );
157
158
159 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
160
161 CREATE TABLE morphologic_width_values (
162 id NUMBER(38,0) NOT NULL,
163 morphologic_width_id NUMBER(38,0) NOT NULL,
164 station NUMBER(38,3) NOT NULL,
165 width NUMBER(38,3) NOT NULL,
166 description VARCHAR(256),
167 PRIMARY KEY(id),
168 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
169 );
170
171
172 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
173
174 CREATE TABLE discharge_zone (
175 id NUMBER(38,0) NOT NULL,
176 river_id NUMBER(38,0) NOT NULL,
177 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
178 value NUMBER(38,3) NOT NULL,
179 lower_discharge VARCHAR(16) NOT NULL,
180 upper_discharge VARCHAR(16),
181 PRIMARY KEY(id),
182 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
183 );
184
185
186 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
187
188 CREATE TABLE flow_velocity_model (
189 id NUMBER(38,0) NOT NULL,
190 river_id NUMBER(38,0) NOT NULL,
191 discharge_zone_id NUMBER(38,0) NOT NULL,
192 description VARCHAR(256),
193 PRIMARY KEY (id),
194 CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
195 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
196 );
197
198
199 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
200
201 CREATE TABLE flow_velocity_model_values (
202 id NUMBER(38,0) NOT NULL,
203 flow_velocity_model_id NUMBER(38,0) NOT NULL,
204 station NUMBER(38,3) NOT NULL,
205 q NUMBER(38,3) NOT NULL,
206 total_channel NUMBER(38,3) NOT NULL,
207 main_channel NUMBER(38,3) NOT NULL,
208 shear_stress NUMBER(38,3) NOT NULL,
209 PRIMARY KEY(id),
210 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
211 );
212
213
214
215 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
216
217 CREATE TABLE flow_velocity_measurements (
218 id NUMBER(38,0) NOT NULL,
219 river_id NUMBER(38,0) NOT NULL,
220 description VARCHAR(256),
221 PRIMARY KEY (id),
222 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
223 );
224
225 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
226
227 CREATE TABLE flow_velocity_measure_values (
228 id NUMBER(38,0) NOT NULL,
229 measurements_id NUMBER(38,0) NOT NULL,
230 station NUMBER(38,3) NOT NULL,
231 datetime TIMESTAMP,
232 w NUMBER(38,3) NOT NULL,
233 q NUMBER(38,3) NOT NULL,
234 v NUMBER(38,3) NOT NULL,
235 description VARCHAR(256),
236 PRIMARY KEY (id),
237 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
238 );
239
240
241 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
242
243 CREATE TABLE grain_fraction (
244 id NUMBER(38,0) NOT NULL,
245 name VARCHAR(64) NOT NULL,
246 lower NUMBER(38,3),
247 upper NUMBER(38,3),
248 unit_id NUMBER (38,0),
249 PRIMARY KEY (id),
250 CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
251 );
252
253
254 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
255
256 CREATE TABLE sediment_yield (
257 id NUMBER(38,0) NOT NULL,
258 river_id NUMBER(38,0) NOT NULL,
259 grain_fraction_id NUMBER(38,0),
260 unit_id NUMBER(38,0) NOT NULL,
261 time_interval_id NUMBER(38,0) NOT NULL,
262 description VARCHAR(256),
263 PRIMARY KEY (id),
264 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
265 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
266 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
267 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
268 );
269
270
271 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
272
273 CREATE TABLE sediment_yield_values (
274 id NUMBER(38,0) NOT NULL,
275 sediment_yield_id NUMBER(38,0) NOT NULL,
276 station NUMBER(38,3) NOT NULL,
277 value NUMBER(38,3) NOT NULL,
278 PRIMARY KEY (id),
279 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
280 );
281
282
283 CREATE SEQUENCE WATERLEVEL_ID_SEQ;
284
285 CREATE TABLE waterlevel (
286 id NUMBER(38,0) NOT NULL,
287 river_id NUMBER(38,0) NOT NULL,
288 unit_id NUMBER(38,0) NOT NULL,
289 description VARCHAR(256),
290 PRIMARY KEY (id),
291 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
292 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
293 );
294
295
296 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
297
298 CREATE TABLE waterlevel_q_range (
299 id NUMBER(38,0) NOT NULL,
300 waterlevel_id NUMBER(38,0) NOT NULL,
301 q NUMBER(38,2) NOT NULL,
302 PRIMARY KEY (id),
303 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
304 );
305
306
307 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
308
309 CREATE TABLE waterlevel_values(
310 id NUMBER(38,0) NOT NULL,
311 waterlevel_q_range_id NUMBER(38,0) NOT NULL,
312 station NUMBER(38,3) NOT NULL,
313 w NUMBER(38,2) NOT NULL,
314 PRIMARY KEY (id),
315 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
316 );
317
318
319 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;
320
321 CREATE TABLE waterlevel_difference (
322 id NUMBER(38,0) NOT NULL,
323 river_id NUMBER(38,0) NOT NULL,
324 unit_id NUMBER(38,0) NOT NULL,
325 description VARCHAR(256),
326 PRIMARY KEY (id),
327 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
328 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
329 );
330
331
332 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
333
334 CREATE TABLE waterlevel_difference_column (
335 id NUMBER(38,0) NOT NULL,
336 difference_id NUMBER(38,0) NOT NULL,
337 description VARCHAR(256),
338 PRIMARY KEY (id),
339 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
340 );
341
342
343 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;
344
345 CREATE TABLE waterlevel_difference_values (
346 id NUMBER(38,0) NOT NULL,
347 column_id NUMBER(38,0) NOT NULL,
348 station NUMBER(38,3) NOT NULL,
349 value NUMBER(38,2) NOT NULL,
350 PRIMARY KEY (id),
351 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
352 );
353
http://dive4elements.wald.intevation.org