Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-minfo.sql @ 9465:be9bd06a1e3f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
bezugswst sounding select for miss'vol only (calc crashes)
author gernotbelger
date Thu, 30 Aug 2018 10:54:40 +0200
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
aa8dcf7331a8 Repair last commit.
Tom Gottfried <tom@intevation.de>
parents: 8018
diff changeset
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 );
http://dive4elements.wald.intevation.org