annotate flys-backend/doc/schema/oracle-minfo.sql @ 5358:59f9760e8b7f

Switch to 2D for fixpoints and river_axes This is needed so that the spatial index can work. The importer only imports them as 2D geometries
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 21 Mar 2013 09:30:46 +0100
parents 6dd354e7abfc
children e88d55d01cf5
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
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
39
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
40 CREATE TABLE bed_height_single (
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,
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43 year 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
44 sounding_width NUMBER(38,0) NOT NULL,
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
45 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
46 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
47 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
48 old_elevation_model_id NUMBER(38,0),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
49 range_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
50 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
51 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
52 PRIMARY KEY(id),
2809
f283212966e8 Finished work on MINFO bed heights (single).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2807
diff changeset
53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
2807
5ac1db5156be Some oracle db schema adaptions specific to bed heights in MINFO.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2805
diff changeset
55 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
57 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
60
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
61
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
62 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
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 CREATE TABLE bed_height_epoch (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
65 id NUMBER(38,0) NOT NULL,
2810
04eeb45df27b Implemented model classes and importer classes for bed height epochs.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2809
diff changeset
66 river_id NUMBER(38,0) NOT NULL,
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
67 time_interval_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
68 -- sounding_with NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
69 -- type_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
70 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
71 old_elevation_model_id NUMBER(38,0),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
72 range_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
73 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
74 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
75 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
76 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
77 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
78 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
79 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
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
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
83 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
84
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
85 CREATE TABLE bed_height_single_values (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
86 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
87 bed_height_single_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
88 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
89 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
90 uncertainty NUMBER(38,2),
3953
fc32a4955087 Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3793
diff changeset
91 data_gap NUMBER(38,2),
fc32a4955087 Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3793
diff changeset
92 sounding_width NUMBER(38,2),
fc32a4955087 Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3793
diff changeset
93 width NUMBER(38,2),
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
94 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
95 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
96 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
97
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
98
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
99 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
100
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
101 CREATE TABLE bed_height_epoch_values (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
102 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
103 bed_height_epoch_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
104 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
105 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
106 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
107 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
108 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
109
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
110
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
111 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
112
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 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
114 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
115 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
116 upper NUMBER(38,2) NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2819
diff changeset
117 unit_id NUMBER(38,0) NOT NULL,
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 PRIMARY KEY(id),
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
119 CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
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
120 );
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
121
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
122
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
123 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
124
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
125 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
126 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
127 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
128 depth_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
129 unit_id NUMBER(38,0) NOT NULL,
2817
8979f2294af9 Finished parsing MINFO specific sediment density.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2816
diff changeset
130 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
131 PRIMARY KEY(id),
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
132 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
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
133 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
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
134 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(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
135 );
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
136
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
137
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
138 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
139
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
140 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
141 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
142 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
143 station NUMBER(38,2) NOT NULL,
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
144 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
145 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
146 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
147 PRIMARY KEY(id),
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
148 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(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
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
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 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
153
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2817
diff changeset
154 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
155 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 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
157 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
158 PRIMARY KEY(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 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2817
diff changeset
160 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
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
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 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
165
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2817
diff changeset
166 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
167 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
168 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
169 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
170 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
171 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
172 PRIMARY KEY(id),
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2817
diff changeset
173 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2817
diff changeset
174 );
2824
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
175
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 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
178
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
179 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
180 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
181 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
182 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
183 value NUMBER(38,3) NOT NULL,
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
184 lower_discharge VARCHAR(16) NOT NULL,
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
185 upper_discharge VARCHAR(16),
2824
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
186 PRIMARY KEY(id),
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
187 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
2824
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
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 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
192
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
193 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
194 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
195 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
196 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
197 PRIMARY KEY (id),
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
198 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
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
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 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
203
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
204 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
205 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
206 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
207 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
208 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
209 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
210 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
211 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
212 PRIMARY KEY(id),
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
213 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
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
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2823
diff changeset
217
2829
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
218 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
219
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
220 CREATE TABLE flow_velocity_measurements (
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
221 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 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
223 description VARCHAR(256),
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
224 PRIMARY KEY (id),
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
225 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
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
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
228 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
229
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
230 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
231 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 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
233 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
234 datetime TIMESTAMP,
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
235 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
236 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
237 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
238 description VARCHAR(256),
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
239 PRIMARY KEY (id),
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
240 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
4074777afcfa Added MINFO specific relations for flow velocity measurements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2828
diff changeset
241 );
2834
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
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
244 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
245
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
246 CREATE TABLE grain_fraction (
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
247 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
248 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
249 lower NUMBER(38,3),
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
250 upper NUMBER(38,3),
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
251 PRIMARY KEY (id),
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
252 );
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
253
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
254
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
255 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
256
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
257 CREATE TABLE sediment_yield (
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
258 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
259 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
260 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
261 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
262 time_interval_id NUMBER(38,0) NOT NULL,
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
263 description VARCHAR(256),
2834
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
264 PRIMARY KEY (id),
2836
6b541d85c5cf Added missing river_id column to sediment_yield relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2834
diff changeset
265 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
2834
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
266 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
267 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
268 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
269 );
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
270
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
271
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
272 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
273
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
274 CREATE TABLE sediment_yield_values (
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
275 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
276 sediment_yield_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
277 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
278 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
279 PRIMARY KEY (id),
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
daba3f895eb3 Improved the db schema to store sediment yield values.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2833
diff changeset
281 );
2841
6be2bf2492f9 Added MINFO specific waterlevel relations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2840
diff changeset
282
6be2bf2492f9 Added MINFO specific waterlevel relations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2840
diff changeset
283
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
284 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
285 CREATE TABLE measurement_station (
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
286 id NUMBER(38) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
287 name VARCHAR(256) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
288 river_id NUMBER(38) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
289 station NUMBER(38,3) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
290 range_id NUMBER(38) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
291 measurement_type VARCHAR(64) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
292 riverside VARCHAR(16),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
293 reference_gauge_id NUMBER(38),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
294 observation_timerange_id NUMBER(38),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
295 operator VARCHAR(64),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
296 description VARCHAR(512),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
297 PRIMARY KEY (id),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
298 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
299 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
300 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
301 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
302 UNIQUE (river_id, station)
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
303 );
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
304
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 4523
diff changeset
305
3321
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
306 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
307
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
308 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
309 id NUMBER(38,0) NOT NULL,
3329
cc8fc6b29649 Store sq relations into database after parsing.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3321
diff changeset
310 river_id NUMBER(38,0) NOT NULL,
3321
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
311 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
312 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
313 PRIMARY KEY (id),
3329
cc8fc6b29649 Store sq relations into database after parsing.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3321
diff changeset
314 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
3321
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
315 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
316 );
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
317
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
318
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
319 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
320
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
321 CREATE TABLE sq_relation_value (
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
322 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
323 sq_relation_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
324 parameter VARCHAR(16) 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
325 fraction VARCHAR(32) 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
326 function VARCHAR(32) 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
327 km NUMBER(38,3) NOT NULL,
3793
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3329
diff changeset
328 a NUMBER(38, 3) NOT NULL,
3321
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
329 b NUMBER(38,3) 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
330 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
331 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2849
diff changeset
332 );

http://dive4elements.wald.intevation.org