Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-minfo.sql @ 8554:3a0522f1a532

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Morphological width imported and stored with heights is redundant to table morphologic_width.
author "Tom Gottfried <tom@intevation.de>"
date Mon, 16 Feb 2015 09:00:08 +0100
parents ffbeffe2d266
children 6d8d7425a6b5
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
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 CREATE TABLE bed_height_single (
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,
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
44 sounding_width int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45 type_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 location_system_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47 cur_elevation_model_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 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
49 range_id int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 evaluation_by VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51 description VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 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
53 CONSTRAINT fk_bed_single_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
54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
55 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_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
58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
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 CREATE TABLE bed_height_single_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66 bed_height_single_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 height NUMERIC,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 uncertainty NUMERIC,
3953
fc32a4955087 Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3793
diff changeset
70 data_gap NUMERIC,
fc32a4955087 Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3793
diff changeset
71 sounding_width NUMERIC,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 PRIMARY KEY(id),
6045
2d16f4a0bdcc Schema change: there is only one bed height per station at a given point in time
Tom Gottfried <tom@intevation.de>
parents: 5894
diff changeset
73 UNIQUE (station, bed_height_single_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
74 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
75 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
76
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
77
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 CREATE SEQUENCE DEPTHS_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80 CREATE TABLE depths (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82 lower NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 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
84 PRIMARY KEY(id)
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
87
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
88 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
89
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
90 CREATE TABLE sediment_density (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
91 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
93 depth_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
94 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
95 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
96 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
97 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
98 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
99
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
100
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
101 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
102
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
103 CREATE TABLE sediment_density_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
104 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
105 sediment_density_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 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
107 shore_offset NUMERIC,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
108 density NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 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
110 year int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111 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
112 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
113 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
115
7839
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
116 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
117
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
118 CREATE TABLE porosity (
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
119 id int NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
120 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
121 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
122 description VARCHAR(256),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
123 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
124 PRIMARY KEY(id),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
125 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
126 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
127 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
128 );
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
129
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
130
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
131 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
132
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
133 CREATE TABLE porosity_values (
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
134 id int NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
135 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
136 station NUMERIC NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
137 shore_offset NUMERIC,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
138 porosity NUMERIC NOT NULL,
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
139 description VARCHAR(256),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
140 PRIMARY KEY(id),
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
141 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
142 );
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
143
3f6b9fae1637 Added tables for porosity and porosity values to postgres schema.
Raimund Renkert <rrenkert@intevation.de>
parents: 7461
diff changeset
144
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
145 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
146
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
147 CREATE TABLE morphologic_width (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
148 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
149 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
150 unit_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
151 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
152 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
153 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
154 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
155
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
156
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
157 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
158
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
159 CREATE TABLE morphologic_width_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
160 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
161 morphologic_width_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
162 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
163 width NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
164 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
165 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
166 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
167 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
168
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
169
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
170 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
171
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
172 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
173 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
174 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
175 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
176 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
177 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
178 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
179 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
180 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
181 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
182
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
183
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
184 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
185
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
186 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
187 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
188 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
189 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
190 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
191 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
192 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
193
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
194
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
195 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
196
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
197 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
198 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
199 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
200 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
201 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
202 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
203 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
204 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
205 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
206 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
207 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
208 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
209
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
210
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
211
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
212 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
213
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
214 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
215 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
216 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
217 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
218 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
219 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
220 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
221
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
222 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
223
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
224 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
225 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
226 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
227 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
228 datetime TIMESTAMP,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
229 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
230 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
231 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
232 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
233 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
234 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
235 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
236
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
237
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
238 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
239
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
240 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
241 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
242 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
243 lower NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
244 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
245 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
246 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
247 );
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
248 -- 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
249 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
250 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
251 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
252 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
253 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
254 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
255 -- 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
256 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
257 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
258 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
259
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
260
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
261 --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
262 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
263 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
264 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
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 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
267 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
268
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
269 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
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 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
272 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
273 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
274 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
275 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
276 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
277 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
278 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
279 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
280 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
281 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
282 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
283 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
284 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
285 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
286 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
287 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
288 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
289 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
290 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
291 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
292 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
293 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
294
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
295
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
296 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
297
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
298 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
299 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
300 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
301 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
302 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
303 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
304 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
305 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
306 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
307
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
308
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
309 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
310 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
311 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
312 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
313 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
314 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
315 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
316 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
317 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
318 -- 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
319 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
320 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
321 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
322 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
323 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
324 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
325 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
326 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
327 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
328 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
329 );
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
330
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
331
8017
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
332 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
333
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
334 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
335 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
336 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
337 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
338 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
339 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
340 kind int,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
341 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
342 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
343 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
344 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
345 REFERENCES 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
346 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (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
347 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
348 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
349 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
350 );
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
351
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
352
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
353 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
354
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
355 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
356 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
357 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
358 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
359 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
360 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
361 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
362 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
363 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
364 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
365 );
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
366
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations.
Tom Gottfried <tom@intevation.de>
parents: 7924
diff changeset
367
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
368 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
369
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
370 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
371 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
372 river_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
373 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
374 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
375 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
376 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_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
377 REFERENCES rivers(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
378 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
379 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
380 );
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2858
diff changeset
381
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
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 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
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 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
386 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
387 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
388 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
389 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
390 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
391 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
392 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
393 rsq NUMERIC,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
394 ntot int,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
395 noutl int,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
396 cferguson NUMERIC,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
397 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
398 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
399 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
400 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
401 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
402 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
403 );
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
404 COMMIT;
http://dive4elements.wald.intevation.org