Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-minfo.sql @ 9661:9b8ba3b83a15

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Importer (s/u-info) vegetation zones: new database column in vegetation_type table for german type name, localized vegetation type names by querying the database instead of translating by resource property, detecting and cancelling the import of a second vegetation zone file for a river, detecting, logging, cancelling in case of wrong column titles, detecting, logging and ignoring lines with missing (color) values, comparing vegetation zone name and class with the database and logging+ignoring in case of inconsistencies, starting the most elevated zone with 0 instead of -1 overflow days
author mschaefer
date Mon, 23 Mar 2020 16:38:12 +0100
parents f89fb9e9abad
children
rev   line source
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 BEGIN;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 CREATE TABLE location_system (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
6 id int NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 name VARCHAR(32) NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 description VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 PRIMARY KEY(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 CREATE TABLE elevation_model (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
16 id int NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 name VARCHAR(32) NOT NULL,
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
18 unit_id int NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23
5202
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
24 -- lookup table for bedheight types
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 CREATE TABLE bed_height_type (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
26 id int NOT NULL,
5273
db196abd16ca Use lookup table from bedd_height from database in importer.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5206
diff changeset
27 name VARCHAR(64) NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 PRIMARY KEY(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29 );
5276
170f514a4f29 SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents: 5275
diff changeset
30 INSERT INTO bed_height_type VALUES (1, 'Querprofile');
5275
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');
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@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;
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@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 (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
41 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
42 river_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
43 year int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
44 type_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
45 location_system_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
46 cur_elevation_model_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
47 old_elevation_model_id int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
48 range_id int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 evaluation_by VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 description VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@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: 8642
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: 8642
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: 8642
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: 8642
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: 8642
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: 8642
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
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@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;
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@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 (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
69 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
70 bed_height_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
71 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
72 height NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
73 uncertainty NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
74 data_gap NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
75 sounding_width NUMERIC,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@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
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 CREATE SEQUENCE DEPTHS_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85 CREATE TABLE depths (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
86 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
87 lower NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
88 upper NUMERIC 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)
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
90 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
91
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
93 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
94
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
95 CREATE TABLE sediment_density (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
96 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
97 river_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
98 depth_id int NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
99 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
100 PRIMARY KEY(id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
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)
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
103 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
104
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
105
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
107
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
108 CREATE TABLE sediment_density_values (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
109 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
110 sediment_density_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
111 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
112 shore_offset NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
113 density NUMERIC NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114 description VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
115 year int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
116 PRIMARY KEY(id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
117 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
119
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
120
7839
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
121 CREATE SEQUENCE POROSITY_ID_SEQ;
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
122
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
123 CREATE TABLE porosity (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
124 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
125 river_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
126 depth_id int NOT NULL,
7839
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
127 description VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
128 time_interval_id int NOT NULL,
7839
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
129 PRIMARY KEY(id),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
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,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
131 CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
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)
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
133 );
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
134
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
135
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
136 CREATE SEQUENCE POROSITY_VALUES_ID_SEQ;
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
137
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
138 CREATE TABLE porosity_values (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
139 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
140 porosity_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
141 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
142 shore_offset NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
143 porosity NUMERIC NOT NULL,
7839
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
144 description VARCHAR(256),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
145 PRIMARY KEY(id),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
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
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
147 );
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
148
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
149
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
150 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
151
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
152 CREATE TABLE morphologic_width (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
153 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
154 river_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
155 unit_id int NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
156 PRIMARY KEY(id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
157 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
158 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
159 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
160
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
161
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
162 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
163
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
164 CREATE TABLE morphologic_width_values (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
165 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
166 morphologic_width_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
167 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
168 width NUMERIC NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
169 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
170 PRIMARY KEY(id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
171 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
172 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
173
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
174
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
175 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
176
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
177 CREATE TABLE discharge_zone (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
178 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
179 river_id int NOT NULL,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@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
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
181 value NUMERIC NOT NULL,
6369
7fb0e755e4fa Minimal schema change: allow longer names in discharge zones
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6045
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: 6045
diff changeset
183 upper_discharge VARCHAR(64),
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
184 PRIMARY KEY(id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
185 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
186 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
187
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
188
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
189 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
190
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
191 CREATE TABLE flow_velocity_model (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
192 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
193 discharge_zone_id int NOT NULL,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
194 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
195 PRIMARY KEY (id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
197 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
198
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
199
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
201
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
202 CREATE TABLE flow_velocity_model_values (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
203 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
204 flow_velocity_model_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
205 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
206 q NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
207 total_channel NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
208 main_channel NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
209 shear_stress NUMERIC NOT NULL,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@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),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
212 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
213 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
214
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
215
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
216
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
217 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
218
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
219 CREATE TABLE flow_velocity_measurements (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
220 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
221 river_id int NOT NULL,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
222 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
223 PRIMARY KEY (id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
224 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
225 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
226
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
227 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
228
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
229 CREATE TABLE flow_velocity_measure_values (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
230 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
231 measurements_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
232 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
233 datetime TIMESTAMP,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
234 w NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
235 q NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
236 v NUMERIC NOT NULL,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
237 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
238 PRIMARY KEY (id),
4991
b79eb203032d SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 4523
diff changeset
239 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
240 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
241
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
242
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
243 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
244
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
245 CREATE TABLE grain_fraction (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
246 id int NOT NULL,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
247 name VARCHAR(64) NOT NULL,
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
248 lower NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
249 upper NUMERIC,
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)
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
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);
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
264
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
265 ALTER SEQUENCE GRAIN_FRACTION_ID_SEQ RESTART WITH 10;
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
266
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
267
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
268 --lookup table for sediment load kinds
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
269 CREATE TABLE sediment_load_kinds (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
270 id int PRIMARY KEY NOT NULL,
7461
5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents: 6817
diff changeset
271 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: 6817
diff changeset
272 );
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
273 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
274 INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official');
7461
5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents: 6817
diff changeset
275
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
276
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
277 CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ;
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
278
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
279 CREATE TABLE sediment_load_ls (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
280 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
281 river_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
282 grain_fraction_id int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
283 unit_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
284 time_interval_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
285 sq_time_interval_id int,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
286 description VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
287 kind int,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
288 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
289 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
290 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
291 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
292 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
293 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
294 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
295 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
296 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
297 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
298 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
299 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
300 REFERENCES time_intervals(id)
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
301 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
302
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
303
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
304 CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ;
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
305
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
306 CREATE TABLE sediment_load_ls_values (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
307 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
308 sediment_load_ls_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
309 station NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
310 value NUMERIC NOT NULL,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
311 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
312 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
313 REFERENCES sediment_load_ls(id) ON DELETE CASCADE
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
314 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
315
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
316
4193
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3953
diff changeset
317 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3953
diff changeset
318 CREATE TABLE measurement_station (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
319 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
320 range_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
321 reference_gauge_id int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
322 time_interval_id int,
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
323 name VARCHAR(256) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
324 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: 5202
diff changeset
325 riverside VARCHAR(16),
5456
e47c4e9b050e Schema corrected: measurement_station.reference_gauge_station is not needed
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5455
diff changeset
326 -- store name of reference gauges here too, as not all are in gauges
5453
303701ab73c6 store name and km of reference gauges in measurement_stations too, as not all are in gauges
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5448
diff changeset
327 reference_gauge_name VARCHAR(64),
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
328 operator VARCHAR(64),
8415
ffbeffe2d266 SCHEMA CHANGE: 'comment' is not acceptable as column name for Oracle.
Tom Gottfried <tom@intevation.de>
parents: 8412
diff changeset
329 commentary VARCHAR(512),
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
330 PRIMARY KEY (id),
8642
9db1f48bfea9 Enforce strings we rely on.
Tom Gottfried <tom@intevation.de>
parents: 8641
diff changeset
331 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
332 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
333 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
334 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
335 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
336 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
337 REFERENCES time_intervals(id)
4193
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3953
diff changeset
338 );
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3953
diff changeset
339
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 3953
diff changeset
340
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
341 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
342
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
343 CREATE TABLE sediment_load (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
344 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
345 grain_fraction_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
346 time_interval_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
347 sq_time_interval_id int,
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
348 description VARCHAR(256),
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
349 kind int,
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
350 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
351 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
352 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
353 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
354 REFERENCES grain_fraction(id),
8641
c851d1ea543a SCHEMA CHANGE: Remove unnecessary direct relation of sq_relation with rivers.
Tom Gottfried <tom@intevation.de>
parents: 8560
diff changeset
355 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
356 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
357 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
358 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
359 );
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
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
362 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
363
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
364 CREATE TABLE sediment_load_values (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
365 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
366 sediment_load_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
367 measurement_station_id int NOT NULL,
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
368 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
369 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
370 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
371 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
372 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
373 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
374 );
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
375
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
376
3793
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
377 CREATE SEQUENCE SQ_RELATION_ID_SEQ;
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
378
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
379 CREATE TABLE sq_relation (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
380 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
381 time_interval_id int NOT NULL,
3793
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
382 description VARCHAR(256),
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
383 PRIMARY KEY (id),
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
384 CONSTRAINT fk_sqr_tinterval_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
385 REFERENCES time_intervals(id)
3793
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
386 );
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
387
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
388
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
389 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
390
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
391 CREATE TABLE sq_relation_value (
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
392 id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
393 sq_relation_id int NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
394 measurement_station_id int 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
395 parameter VARCHAR(1) NOT NULL,
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
396 a NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
397 b NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
398 qmax NUMERIC NOT NULL,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
399 rsq NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
400 ntot int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
401 noutl int,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
402 cferguson NUMERIC,
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8943
diff changeset
403 cduan NUMERIC,
3793
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
404 PRIMARY KEY (id),
8691
bfca77cbf353 (issue1750) There can be only one SQ-relation of a parameter for a time range (given by sq_relation_id) per station and some other attributes might be null.
Tom Gottfried <tom@intevation.de>
parents: 8666
diff changeset
405 UNIQUE(sq_relation_id, measurement_station_id, parameter),
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
406 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_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
407 REFERENCES sq_relation(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
408 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_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
409 REFERENCES measurement_station(id) ON DELETE CASCADE
3793
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
410 );
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
411 COMMIT;
http://dive4elements.wald.intevation.org