Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-minfo.sql @ 9152:8d4e7e08dbc0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed query semicolon
author mschaefer
date Thu, 14 Jun 2018 16:58:35 +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