Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-minfo.sql @ 9780:a517ed28f133 3.2.x

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do not add unused other river modules to the client image
author Tom Gottfried <tom@intevation.de>
date Fri, 31 Mar 2023 12:26:50 +0200
parents bfca77cbf353
children 71b17f731762
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 id int NOT NULL,
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 name VARCHAR(32) NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 unit_id int NOT NULL,
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
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 (
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 river_id int NOT NULL,
5448
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
43 year int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 type_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45 location_system_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 cur_elevation_model_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47 old_elevation_model_id int,
5448
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
48 range_id 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 (
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 id int NOT NULL,
8559
6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete).
"Tom Gottfried <tom@intevation.de>"
parents: 8554
diff changeset
70 bed_height_id int NOT NULL,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 height NUMERIC,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
73 uncertainty NUMERIC,
3953
fc32a4955087 Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3793
diff changeset
74 data_gap NUMERIC,
fc32a4955087 Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3793
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
87 lower NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
97 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
98 depth_id int NOT NULL,
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110 sediment_density_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111 station NUMERIC NOT NULL,
5507
db6c7268b08e Schema change: add shore offset to sediment density values and be more rigid with input files in parser
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5498
diff changeset
112 shore_offset NUMERIC,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
113 density NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114 description VARCHAR(256),
4523
504cd5801785 Added new column 'year' to sediment density values in postgresql and oracle
Raimund Renkert <rrenkert@intevation.de>
parents: 4193
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 (
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
124 id int NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
125 river_id int NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
126 depth_id int NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
127 description VARCHAR(256),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
128 time_interval_id int NOT NULL,
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 (
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
139 id int NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
140 porosity_id int NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
141 station NUMERIC NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
142 shore_offset NUMERIC,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
143 porosity NUMERIC NOT NULL,
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
153 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
154 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
155 unit_id int NOT NULL,
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 (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
165 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
166 morphologic_width_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
167 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
168 width NUMERIC NOT NULL,
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 (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
178 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
179 river_id int NOT NULL,
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
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
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 (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
192 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
193 discharge_zone_id int NOT NULL,
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 (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
203 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
204 flow_velocity_model_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
205 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
206 q NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
207 total_channel NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
208 main_channel NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
209 shear_stress NUMERIC NOT NULL,
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 (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
220 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
221 river_id int NOT NULL,
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 (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
230 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
231 measurements_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
232 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
233 datetime TIMESTAMP,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
234 w NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
235 q NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
236 v NUMERIC NOT NULL,
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 (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
246 id int NOT NULL,
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,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
248 lower NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
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
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
265
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
266 --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
267 CREATE TABLE sediment_load_kinds (
7461
5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents: 6817
diff changeset
268 id int PRIMARY KEY NOT NULL,
5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents: 6817
diff changeset
269 kind VARCHAR(64) NOT NULL
5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented.
Tom Gottfried <tom@intevation.de>
parents: 6817
diff changeset
270 );
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
271 INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official');
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
272 INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official');
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
273
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
274 CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ;
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
275
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
276 CREATE TABLE sediment_load_ls (
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
277 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
278 river_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
279 grain_fraction_id int,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
280 unit_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
281 time_interval_id int NOT NULL,
8059
bde5f5ec7c72 SCHEMA CHANGE and Importer: get time intervals of SQ-relations from metalines in sediment load files.
Tom Gottfried <tom@intevation.de>
parents: 8032
diff changeset
282 sq_time_interval_id int,
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
283 description VARCHAR(256),
6721
df402b3e0281 minfo-schema-change/issue1393: add kind column to sediment_yield table.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 6045
diff changeset
284 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
285 PRIMARY KEY (id),
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
286 CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
287 REFERENCES rivers(id) ON DELETE CASCADE,
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
288 CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
289 REFERENCES sediment_load_kinds(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
290 CONSTRAINT fk_slls_grain_fraction_id FOREIGN KEY (grain_fraction_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
291 REFERENCES grain_fraction(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
292 CONSTRAINT fk_slls_unit_id FOREIGN KEY (unit_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
293 REFERENCES units(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
294 CONSTRAINT fk_slls_time_interval_id FOREIGN KEY (time_interval_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
295 REFERENCES time_intervals(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
296 CONSTRAINT fk_slls_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
8059
bde5f5ec7c72 SCHEMA CHANGE and Importer: get time intervals of SQ-relations from metalines in sediment load files.
Tom Gottfried <tom@intevation.de>
parents: 8032
diff changeset
297 REFERENCES time_intervals(id)
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
298 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
299
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
300
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
301 CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ;
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
302
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
303 CREATE TABLE sediment_load_ls_values (
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
304 id int NOT NULL,
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
305 sediment_load_ls_id 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
306 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
307 value NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
308 PRIMARY KEY (id),
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
309 CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
310 REFERENCES sediment_load_ls(id) ON DELETE CASCADE
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
311 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
312
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
313
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
314 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
315 CREATE TABLE measurement_station (
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
316 id int NOT NULL,
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
317 range_id int NOT NULL,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
318 reference_gauge_id int,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
319 time_interval_id int,
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
320 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
321 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
322 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
323 -- 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
324 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
325 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
326 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
327 PRIMARY KEY (id),
8642
9db1f48bfea9 Enforce strings we rely on.
Tom Gottfried <tom@intevation.de>
parents: 8641
diff changeset
328 CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff')),
8412
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
329 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id)
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
330 REFERENCES ranges(id) ON DELETE CASCADE,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
331 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id)
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
332 REFERENCES gauges(id) ON DELETE CASCADE,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
333 CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id)
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly.
Tom Gottfried <tom@intevation.de>
parents: 8072
diff changeset
334 REFERENCES time_intervals(id)
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
335 );
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
336
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
337
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
338 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ;
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
339
8018
ad614eff7a9b SCHEMA CHANGE: consistent naming scheme and drop script for sediment load at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 8017
diff changeset
340 CREATE TABLE sediment_load (
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
341 id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
342 grain_fraction_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
343 time_interval_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
344 sq_time_interval_id int,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
345 description VARCHAR(256),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
346 kind int,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
347 PRIMARY KEY (id),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
348 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind)
8072
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes.
Tom Gottfried <tom@intevation.de>
parents: 8059
diff changeset
349 REFERENCES sediment_load_kinds(id),
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
350 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
351 REFERENCES grain_fraction(id),
8641
c851d1ea543a SCHEMA CHANGE: Remove unnecessary direct relation of sq_relation with rivers.
Tom Gottfried <tom@intevation.de>
parents: 8560
diff changeset
352 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id)
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
353 REFERENCES time_intervals(id),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
354 CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
355 REFERENCES time_intervals(id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
356 );
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
357
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
358
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
359 CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ;
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
360
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
361 CREATE TABLE sediment_load_values (
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
362 id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
363 sediment_load_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
364 measurement_station_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
365 value DOUBLE PRECISION NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
366 PRIMARY KEY (id),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
367 CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id)
8019
aa8dcf7331a8 Repair last commit.
Tom Gottfried <tom@intevation.de>
parents: 8018
diff changeset
368 REFERENCES sediment_load(id) ON DELETE CASCADE,
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
369 CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
370 REFERENCES measurement_station(id) ON DELETE CASCADE
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
371 );
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
372
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
373
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
374 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
375
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
376 CREATE TABLE sq_relation (
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 id int NOT NULL,
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 time_interval_id int NOT NULL,
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 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
380 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
381 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
382 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
383 );
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
384
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
385
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 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
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 CREATE TABLE sq_relation_value (
5426
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
389 id int NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
390 sq_relation_id int NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
391 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
392 parameter VARCHAR(1) NOT NULL,
5426
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
393 a NUMERIC NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
394 b NUMERIC NOT NULL,
5498
d459a885c51f Schema change: make sq_relation_value.qmax NOT NULL and more informative messages in parser
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5478
diff changeset
395 qmax NUMERIC NOT NULL,
5426
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
396 rsq NUMERIC,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
397 ntot int,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
398 noutl int,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
399 cferguson NUMERIC,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
400 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
401 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
402 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
403 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
404 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
405 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
406 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
407 );
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
408 COMMIT;
http://dive4elements.wald.intevation.org