Mercurial > dive4elements > river
annotate backend/doc/schema/oracle-minfo.sql @ 8856:5e38e2924c07 3.2.x
Fix code style.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 18 Jan 2018 20:12:01 +0100 |
parents | 94cddb00f934 |
children |
rev | line source |
---|---|
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
1 SET AUTOCOMMIT ON; |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
2 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ; |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
4 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
5 CREATE TABLE location_system ( |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
6 id NUMBER(38,0) NOT NULL, |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
7 name VARCHAR(32) NOT NULL, |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
8 description VARCHAR(255), |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
9 PRIMARY KEY(id) |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
10 ); |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
11 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
12 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ; |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
14 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
15 CREATE TABLE elevation_model ( |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
16 id NUMBER(38,0) NOT NULL, |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
17 name VARCHAR(32) NOT NULL, |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
18 unit_id NUMBER(38,0) NOT NULL, |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
19 PRIMARY KEY(id), |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
21 ); |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
22 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
23 |
5224
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
24 -- lookup table for bedheight types |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
25 CREATE TABLE bed_height_type ( |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
26 id NUMBER(38,0) NOT NULL, |
5273
db196abd16ca
Use lookup table from bedd_height from database in importer.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5224
diff
changeset
|
27 name VARCHAR(65) NOT NULL, |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
28 PRIMARY KEY(id) |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
29 ); |
5275
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
30 INSERT INTO bed_height_type VALUES (1, 'Querprofile'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
32 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
33 INSERT INTO bed_height_type VALUES (4, 'DGM'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
34 INSERT INTO bed_height_type VALUES (5, 'TIN'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
35 INSERT INTO bed_height_type VALUES (6, 'Modell'); |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
36 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
37 |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
38 CREATE SEQUENCE BED_HEIGHT_ID_SEQ; |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
39 |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
40 CREATE TABLE bed_height ( |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
41 id NUMBER(38,0) NOT NULL, |
2809
f283212966e8
Finished work on MINFO bed heights (single).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2807
diff
changeset
|
42 river_id NUMBER(38,0) NOT NULL, |
5448
b279f2d4bc78
SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5441
diff
changeset
|
43 year NUMBER(38,0), |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
44 type_id NUMBER(38,0) NOT NULL, |
2807
5ac1db5156be
Some oracle db schema adaptions specific to bed heights in MINFO.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2805
diff
changeset
|
45 location_system_id NUMBER(38,0) NOT NULL, |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
46 cur_elevation_model_id NUMBER(38,0) NOT NULL, |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
47 old_elevation_model_id NUMBER(38,0), |
5448
b279f2d4bc78
SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5441
diff
changeset
|
48 range_id NUMBER(38,0), |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
49 evaluation_by VARCHAR(255), |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
50 description VARCHAR(255), |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
51 PRIMARY KEY(id), |
8666
1a4ad0d4231a
(issue1763) Use more distinct constraint names and adapt drop-script.
Tom Gottfried <tom@intevation.de>
parents:
8658
diff
changeset
|
52 CONSTRAINT fk_bh_river_id FOREIGN KEY (river_id) |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
53 REFERENCES rivers(id) ON DELETE CASCADE, |
8666
1a4ad0d4231a
(issue1763) Use more distinct constraint names and adapt drop-script.
Tom Gottfried <tom@intevation.de>
parents:
8658
diff
changeset
|
54 CONSTRAINT fk_bh_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), |
1a4ad0d4231a
(issue1763) Use more distinct constraint names and adapt drop-script.
Tom Gottfried <tom@intevation.de>
parents:
8658
diff
changeset
|
55 CONSTRAINT fk_bh_location_system FOREIGN KEY (location_system_id) |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
56 REFERENCES location_system(id), |
8666
1a4ad0d4231a
(issue1763) Use more distinct constraint names and adapt drop-script.
Tom Gottfried <tom@intevation.de>
parents:
8658
diff
changeset
|
57 CONSTRAINT fk_bh_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
58 REFERENCES elevation_model(id), |
8666
1a4ad0d4231a
(issue1763) Use more distinct constraint names and adapt drop-script.
Tom Gottfried <tom@intevation.de>
parents:
8658
diff
changeset
|
59 CONSTRAINT fk_bh_old_elevation_model FOREIGN KEY (old_elevation_model_id) |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
60 REFERENCES elevation_model(id), |
8666
1a4ad0d4231a
(issue1763) Use more distinct constraint names and adapt drop-script.
Tom Gottfried <tom@intevation.de>
parents:
8658
diff
changeset
|
61 CONSTRAINT fk_bh_range FOREIGN KEY (range_id) |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
62 REFERENCES ranges(id) ON DELETE CASCADE |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
63 ); |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
64 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
65 |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
66 CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ; |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
67 |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
68 CREATE TABLE bed_height_values ( |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
69 id NUMBER(38,0) NOT NULL, |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
70 bed_height_id NUMBER(38,0) NOT NULL, |
6203
a3fb76cf79b6
backout backout as it is now safely in a branch
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6201
diff
changeset
|
71 station DOUBLE PRECISION NOT NULL, |
a3fb76cf79b6
backout backout as it is now safely in a branch
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6201
diff
changeset
|
72 height DOUBLE PRECISION, |
a3fb76cf79b6
backout backout as it is now safely in a branch
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6201
diff
changeset
|
73 uncertainty DOUBLE PRECISION, |
a3fb76cf79b6
backout backout as it is now safely in a branch
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6201
diff
changeset
|
74 data_gap DOUBLE PRECISION, |
a3fb76cf79b6
backout backout as it is now safely in a branch
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6201
diff
changeset
|
75 sounding_width DOUBLE PRECISION, |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
76 PRIMARY KEY(id), |
8559
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
77 UNIQUE (station, bed_height_id), |
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
78 CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id) |
6d8d7425a6b5
Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents:
8554
diff
changeset
|
79 REFERENCES bed_height(id) ON DELETE CASCADE |
2805
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
80 ); |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
81 |
a55b7b35cc3a
First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff
changeset
|
82 |
2812
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
83 CREATE SEQUENCE DEPTHS_ID_SEQ; |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
84 |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
85 CREATE TABLE depths ( |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
86 id NUMBER(38,0) NOT NULL, |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
87 lower NUMBER(38,2) NOT NULL, |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
88 upper NUMBER(38,2) NOT NULL, |
5441
7c1dd9c3f6bd
remove unit from sediment density and depths (always t/m3 respectively cm, otherwise a typo)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5426
diff
changeset
|
89 PRIMARY KEY(id) |
2812
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
90 ); |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
91 |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
92 |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
93 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
94 |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
95 CREATE TABLE sediment_density ( |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
96 id NUMBER(38,0) NOT NULL, |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
97 river_id NUMBER(38,0) NOT NULL, |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
98 depth_id NUMBER(38,0) NOT NULL, |
2817
8979f2294af9
Finished parsing MINFO specific sediment density.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2816
diff
changeset
|
99 description VARCHAR(256), |
2812
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
100 PRIMARY KEY(id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
101 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
5441
7c1dd9c3f6bd
remove unit from sediment density and depths (always t/m3 respectively cm, otherwise a typo)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5426
diff
changeset
|
102 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id) |
2812
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
103 ); |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
104 |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
105 |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
106 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
107 |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
108 CREATE TABLE sediment_density_values ( |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
109 id NUMBER(38,0) NOT NULL, |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
110 sediment_density_id NUMBER(38,0) NOT NULL, |
2816
70b4a31a3306
Implemented the method stubs of the parser for sediment density and made some db schema adaptions.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2812
diff
changeset
|
111 station NUMBER(38,2) NOT NULL, |
5507
db6c7268b08e
Schema change: add shore offset to sediment density values and be more rigid with input files in parser
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5498
diff
changeset
|
112 shore_offset NUMBER(38,2), |
2812
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
113 density NUMBER(38,2) NOT NULL, |
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
114 description VARCHAR(256), |
4523
504cd5801785
Added new column 'year' to sediment density values in postgresql and oracle
Raimund Renkert <rrenkert@intevation.de>
parents:
3953
diff
changeset
|
115 year NUMBER(38,0), |
2812
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
116 PRIMARY KEY(id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
117 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE |
2812
a36a5407acbf
Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2810
diff
changeset
|
118 ); |
2818
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
119 |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
120 |
7863
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
121 CREATE SEQUENCE POROSITY_ID_SEQ; |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
122 |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
123 CREATE TABLE porosity ( |
7866
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
124 id NUMBER(38,0) NOT NULL, |
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
125 river_id NUMBER(38,0) NOT NULL, |
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
126 depth_id NUMBER(38,0) NOT NULL, |
7863
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
127 description VARCHAR(256), |
7866
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
128 time_interval_id NUMBER(38,0) NOT NULL, |
7863
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
129 PRIMARY KEY(id), |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
130 CONSTRAINT fk_p_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
131 CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
132 CONSTRAINT fk_p_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
133 ); |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
134 |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
135 |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
136 CREATE SEQUENCE POROSITY_VALUES_ID_SEQ; |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
137 |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
138 CREATE TABLE porosity_values ( |
7866
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
139 id NUMBER(38,0) NOT NULL, |
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
140 porosity_id NUMBER(38,0) NOT NULL, |
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
141 station DOUBLE PRECISION NOT NULL, |
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
142 shore_offset DOUBLE PRECISION, |
0c74b5f8de17
Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304).
Tom Gottfried <tom@intevation.de>
parents:
7863
diff
changeset
|
143 porosity DOUBLE PRECISION NOT NULL, |
7863
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
144 description VARCHAR(256), |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
145 PRIMARY KEY(id), |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
146 CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
147 ); |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
148 |
082284c4e0ad
Updated oracle minfo schema (added porosity).
Raimund Renkert <rrenkert@intevation.de>
parents:
7461
diff
changeset
|
149 |
2818
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
150 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
151 |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
152 CREATE TABLE morphologic_width ( |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
153 id NUMBER(38,0) NOT NULL, |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
154 river_id NUMBER(38,0) NOT NULL, |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
155 unit_id NUMBER(38,0) NOT NULL, |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
156 PRIMARY KEY(id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
157 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
2818
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
158 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
159 ); |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
160 |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
161 |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
162 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
163 |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
164 CREATE TABLE morphologic_width_values ( |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
165 id NUMBER(38,0) NOT NULL, |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
166 morphologic_width_id NUMBER(38,0) NOT NULL, |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
167 station NUMBER(38,3) NOT NULL, |
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
168 width NUMBER(38,3) NOT NULL, |
2819
0c2567626754
Tiny schema modification specific to MINFO morphological width values; added importer classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2818
diff
changeset
|
169 description VARCHAR(256), |
2818
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
170 PRIMARY KEY(id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
171 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE |
2818
25ed1f18fcc4
Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2817
diff
changeset
|
172 ); |
2824
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
173 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
174 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
175 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
176 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
177 CREATE TABLE discharge_zone ( |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
178 id NUMBER(38,0) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
179 river_id NUMBER(38,0) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
180 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance |
2833
5b54a648f702
Finished flow velocity data import: finished parsing meta data of model files and repaired broken HQL statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2829
diff
changeset
|
181 value NUMBER(38,3) NOT NULL, |
6369
7fb0e755e4fa
Minimal schema change: allow longer names in discharge zones
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6203
diff
changeset
|
182 lower_discharge VARCHAR(64) NOT NULL, |
7fb0e755e4fa
Minimal schema change: allow longer names in discharge zones
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6203
diff
changeset
|
183 upper_discharge VARCHAR(64), |
2824
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
184 PRIMARY KEY(id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
185 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE |
2824
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
186 ); |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
187 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
188 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
189 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
190 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
191 CREATE TABLE flow_velocity_model ( |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
192 id NUMBER(38,0) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
193 discharge_zone_id NUMBER(38,0) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
194 description VARCHAR(256), |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
195 PRIMARY KEY (id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE |
2824
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
197 ); |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
198 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
199 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
201 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
202 CREATE TABLE flow_velocity_model_values ( |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
203 id NUMBER(38,0) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
204 flow_velocity_model_id NUMBER(38,0) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
205 station NUMBER(38,3) NOT NULL, |
2828
ac13e466a55e
Added a parser for flow velocity model data and adjusted the db relation schema (missing q column).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2824
diff
changeset
|
206 q NUMBER(38,3) NOT NULL, |
2824
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
207 total_channel NUMBER(38,3) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
208 main_channel NUMBER(38,3) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
209 shear_stress NUMBER(38,3) NOT NULL, |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
210 PRIMARY KEY(id), |
5709
e89d922df8ae
Schema change: add constraint to have unique stations per flow velocity model
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
211 UNIQUE (station, flow_velocity_model_id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
212 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE |
2824
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
213 ); |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
214 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
215 |
85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2823
diff
changeset
|
216 |
2829
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
217 CREATE SEQUENCE FV_MEASURE_ID_SEQ; |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
218 |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
219 CREATE TABLE flow_velocity_measurements ( |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
220 id NUMBER(38,0) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
221 river_id NUMBER(38,0) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
222 description VARCHAR(256), |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
223 PRIMARY KEY (id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
224 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE |
2829
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
225 ); |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
226 |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
227 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
228 |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
229 CREATE TABLE flow_velocity_measure_values ( |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
230 id NUMBER(38,0) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
231 measurements_id NUMBER(38,0) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
232 station NUMBER(38,3) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
233 datetime TIMESTAMP, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
234 w NUMBER(38,3) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
235 q NUMBER(38,3) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
236 v NUMBER(38,3) NOT NULL, |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
237 description VARCHAR(256), |
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
238 PRIMARY KEY (id), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
239 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE |
2829
4074777afcfa
Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2828
diff
changeset
|
240 ); |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
241 |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
242 |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
243 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
244 |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
245 CREATE TABLE grain_fraction ( |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
246 id NUMBER(38,0) NOT NULL, |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
247 name VARCHAR(64) NOT NULL, |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
248 lower NUMBER(38,3), |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
249 upper NUMBER(38,3), |
8032
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
250 PRIMARY KEY (id), |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
251 UNIQUE(name, lower, upper) |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
252 ); |
8032
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
253 -- single fractions |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
254 INSERT INTO grain_fraction VALUES (1, 'coarse', 16, 200); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
255 INSERT INTO grain_fraction VALUES (2, 'fine_middle', 2, 16); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
256 INSERT INTO grain_fraction VALUES (3, 'sand', 0.063, 2); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
257 INSERT INTO grain_fraction VALUES (4, 'susp_sand', 0.063, 2); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
258 INSERT INTO grain_fraction VALUES (5, 'susp_sand_bed', 0.063, 2); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
259 INSERT INTO grain_fraction VALUES (6, 'suspended_sediment', 0, 0.063); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
260 -- aggregations of fractions |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
261 INSERT INTO grain_fraction VALUES (7, 'total', 0, 200); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
262 INSERT INTO grain_fraction VALUES (8, 'bed_load', 0.063, 200); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
263 INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2); |
fd3a24336e6a
SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields.
"Tom Gottfried <tom@intevation.de>"
parents:
8019
diff
changeset
|
264 |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
265 |
7461
5b5a2cc8210f
Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents:
6730
diff
changeset
|
266 --lookup table for sediment yield kinds |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
267 CREATE TABLE sediment_load_kinds ( |
7461
5b5a2cc8210f
Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents:
6730
diff
changeset
|
268 id int PRIMARY KEY NOT NULL, |
5b5a2cc8210f
Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents:
6730
diff
changeset
|
269 kind VARCHAR(64) NOT NULL |
5b5a2cc8210f
Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents:
6730
diff
changeset
|
270 ); |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
271 INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official'); |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
272 INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official'); |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
273 |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
274 CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ; |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
275 |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
276 CREATE TABLE sediment_load_ls ( |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
277 id NUMBER(38,0) NOT NULL, |
2836
6b541d85c5cf
Added missing river_id column to sediment_yield relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2834
diff
changeset
|
278 river_id NUMBER(38,0) NOT NULL, |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
279 grain_fraction_id NUMBER(38,0), |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
280 unit_id NUMBER(38,0) NOT NULL, |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
281 time_interval_id NUMBER(38,0) NOT NULL, |
8059
bde5f5ec7c72
SCHEMA CHANGE and Importer: get time intervals of SQ-relations from metalines in sediment load files.
Tom Gottfried <tom@intevation.de>
parents:
8032
diff
changeset
|
282 sq_time_interval_id NUMBER(38,0), |
2840
71175502d868
Added a parser for sediment yield files; started parsing and store values in db.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2836
diff
changeset
|
283 description VARCHAR(256), |
6721
df402b3e0281
minfo-schema-change/issue1393: add kind column to sediment_yield table.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
6201
diff
changeset
|
284 kind NUMBER(38,0), |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
285 PRIMARY KEY (id), |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
286 CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id) |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
287 REFERENCES rivers(id) ON DELETE CASCADE, |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
288 CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind) |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
289 REFERENCES sediment_load_kinds(id), |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
290 CONSTRAINT fk_slls_grain_fraction_id FOREIGN KEY (grain_fraction_id) |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
291 REFERENCES grain_fraction(id), |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
292 CONSTRAINT fk_slls_unit_id FOREIGN KEY (unit_id) |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
293 REFERENCES units(id), |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
294 CONSTRAINT fk_slls_time_interval_id FOREIGN KEY (time_interval_id) |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
295 REFERENCES time_intervals(id), |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
296 CONSTRAINT fk_slls_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) |
8059
bde5f5ec7c72
SCHEMA CHANGE and Importer: get time intervals of SQ-relations from metalines in sediment load files.
Tom Gottfried <tom@intevation.de>
parents:
8032
diff
changeset
|
297 REFERENCES time_intervals(id) |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
298 ); |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
299 |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
300 |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
301 CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ; |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
302 |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
303 CREATE TABLE sediment_load_ls_values ( |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
304 id NUMBER(38,0) NOT NULL, |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
305 sediment_load_ls_id NUMBER(38,0) NOT NULL, |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
306 station NUMBER(38,3) NOT NULL, |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
307 value NUMBER(38,3) NOT NULL, |
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
308 PRIMARY KEY (id), |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
309 CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id) |
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
310 REFERENCES sediment_load_ls(id) ON DELETE CASCADE |
2834
daba3f895eb3
Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2833
diff
changeset
|
311 ); |
2841
6be2bf2492f9
Added MINFO specific waterlevel relations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2840
diff
changeset
|
312 |
6be2bf2492f9
Added MINFO specific waterlevel relations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2840
diff
changeset
|
313 |
5206
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
4523
diff
changeset
|
314 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
4523
diff
changeset
|
315 CREATE TABLE measurement_station ( |
8412
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
316 id int NOT NULL, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
317 range_id int NOT NULL, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
318 reference_gauge_id int, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
319 time_interval_id int, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
320 name VARCHAR2(256 CHAR) NOT NULL, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
321 measurement_type VARCHAR2(64 CHAR) NOT NULL, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
322 riverside VARCHAR2(16 CHAR), |
5456
e47c4e9b050e
Schema corrected: measurement_station.reference_gauge_station is not needed
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5455
diff
changeset
|
323 -- store name of reference gauges here too, as not all are in gauges |
8412
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
324 reference_gauge_name VARCHAR2(64 CHAR), |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
325 operator VARCHAR2(64 CHAR), |
8415
ffbeffe2d266
SCHEMA CHANGE: 'comment' is not acceptable as column name for Oracle.
Tom Gottfried <tom@intevation.de>
parents:
8412
diff
changeset
|
326 commentary VARCHAR2(512 CHAR), |
8412
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
327 PRIMARY KEY (id), |
8706
94cddb00f934
(issue1750) Apply schema changes to oracle, which are already done for PostgreSQL.
Tom Gottfried <tom@intevation.de>
parents:
8666
diff
changeset
|
328 CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff')), |
8412
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
329 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
330 REFERENCES ranges(id) ON DELETE CASCADE, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
331 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
332 REFERENCES gauges(id) ON DELETE CASCADE, |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
333 CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id) |
17db08570637
SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents:
8072
diff
changeset
|
334 REFERENCES time_intervals(id) |
5206
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
4523
diff
changeset
|
335 ); |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
4523
diff
changeset
|
336 |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
4523
diff
changeset
|
337 |
8017
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
338 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
339 |
8018
ad614eff7a9b
SCHEMA CHANGE: consistent naming scheme and drop script for sediment load at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
8017
diff
changeset
|
340 CREATE TABLE sediment_load ( |
8017
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
341 id int NOT NULL, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
342 grain_fraction_id int NOT NULL, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
343 time_interval_id int NOT NULL, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
344 sq_time_interval_id int, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
345 description VARCHAR(256), |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
346 kind int, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
347 PRIMARY KEY (id), |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
348 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) |
8072
5f28aa1be795
SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents:
8059
diff
changeset
|
349 REFERENCES sediment_load_kinds(id), |
8017
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
350 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
351 REFERENCES grain_fraction(id), |
8658
3531f0cee5e1
Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents:
8560
diff
changeset
|
352 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id) |
8017
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
353 REFERENCES time_intervals(id), |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
354 CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
355 REFERENCES time_intervals(id) |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
356 ); |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
357 |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
358 |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
359 CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
360 |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
361 CREATE TABLE sediment_load_values ( |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
362 id int NOT NULL, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
363 sediment_load_id int NOT NULL, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
364 measurement_station_id int NOT NULL, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
365 value DOUBLE PRECISION NOT NULL, |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
366 PRIMARY KEY (id), |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
367 CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) |
8019 | 368 REFERENCES sediment_load(id) ON DELETE CASCADE, |
8017
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
369 CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id) |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
370 REFERENCES measurement_station(id) ON DELETE CASCADE |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
371 ); |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
372 |
bcb05a98bb3f
SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents:
7924
diff
changeset
|
373 |
3321
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
374 CREATE SEQUENCE SQ_RELATION_ID_SEQ; |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
375 |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
376 CREATE TABLE sq_relation ( |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
377 id NUMBER(38,0) NOT NULL, |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
378 time_interval_id NUMBER(38,0) NOT NULL, |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
379 description VARCHAR(256), |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
380 PRIMARY KEY (id), |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
381 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
382 ); |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
383 |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
384 |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
385 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
386 |
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
387 CREATE TABLE sq_relation_value ( |
5426
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
388 id NUMBER(38,0) NOT NULL, |
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
389 sq_relation_id NUMBER(38,0) NOT NULL, |
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
390 measurement_station_id NUMBER(38,0) NOT NULL, |
5455
45c5b8b08ffd
Schema Change: use parameter instead of fraction in sq_relation_value
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5453
diff
changeset
|
391 parameter VARCHAR(1) NOT NULL, |
5426
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
392 a NUMBER(38,20) NOT NULL, |
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
393 b NUMBER(38,20) NOT NULL, |
5498
d459a885c51f
Schema change: make sq_relation_value.qmax NOT NULL and more informative messages in parser
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5478
diff
changeset
|
394 qmax NUMBER(38,20) NOT NULL, |
5426
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
395 rsq NUMBER(38,3), |
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
396 ntot NUMBER(38,0), |
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
397 noutl NUMBER(38,0), |
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
398 cferguson NUMBER(38,20), |
6875a9f02097
SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents:
5406
diff
changeset
|
399 cduan NUMBER(38,20), |
3321
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
400 PRIMARY KEY (id), |
8706
94cddb00f934
(issue1750) Apply schema changes to oracle, which are already done for PostgreSQL.
Tom Gottfried <tom@intevation.de>
parents:
8666
diff
changeset
|
401 UNIQUE(sq_relation_id, measurement_station_id, parameter), |
5577
5f91881124ba
added missing ON DELETE CASCADE to Oracle schema
Tom Gottfried <tom@intevation.de>
parents:
5507
diff
changeset
|
402 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE, |
5684
88cbe798cbab
Schema change: fixed constraints
Tom Gottfried <tom@intevation.de>
parents:
5577
diff
changeset
|
403 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE |
3321
a2a33eff93aa
Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2849
diff
changeset
|
404 ); |