Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql.sql @ 8349:7341bebc79d0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Let injectors be strictly distinct from each other and give more meaningful names (they know what they do, not where they will be used. The latter is configuration).
author Tom Gottfried <tom@intevation.de>
date Wed, 01 Oct 2014 12:23:39 +0200
parents a67bd7744827
children b8c6cb36607e
rev   line source
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 BEGIN;
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
2347
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2346
diff changeset
3 CREATE SEQUENCE UNITS_ID_SEQ;
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2346
diff changeset
4
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2346
diff changeset
5 CREATE TABLE units (
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2346
diff changeset
6 id int PRIMARY KEY NOT NULL,
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2346
diff changeset
7 name VARCHAR(32) NOT NULL UNIQUE
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2346
diff changeset
8 );
0acf28a3d28a Removed the Unit from Wsts - added a WstUnit column to rivers.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2346
diff changeset
9
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 -- Gewaesser
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
11 CREATE SEQUENCE RIVERS_ID_SEQ;
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
12
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 CREATE TABLE rivers (
3946
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3796
diff changeset
14 id int PRIMARY KEY NOT NULL,
7743
a67bd7744827 Schema correction following yesterday's brain failure.
Tom Gottfried <tom@intevation.de>
parents: 7741
diff changeset
15 model_uuid CHAR(36) UNIQUE,
5893
c1abd48a03e2 Schema change: remove UNIQUE-constraint from rivers.official number as we have different representations of the same river (e.g. for sommer and winter)
Tom Gottfried <tom@intevation.de>
parents: 5883
diff changeset
16 official_number int8,
3946
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3796
diff changeset
17 name VARCHAR(256) NOT NULL UNIQUE,
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3796
diff changeset
18 km_up BOOLEAN NOT NULL DEFAULT true,
948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3796
diff changeset
19 wst_unit_id int NOT NULL REFERENCES units(id)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 -- Bruecke, Haefen, etc.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
23 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
24
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 CREATE TABLE attributes (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
27 value VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 -- segments from/to at a river
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
31 CREATE SEQUENCE RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
32
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 CREATE TABLE ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
35 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
36 a NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 b NUMERIC,
7005
dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6871
diff changeset
38 UNIQUE (river_id, a, b),
dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6871
diff changeset
39 CHECK (a < b)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 );
5297
4f3cc1aebcc0 trigger sequence of range IDs in DB as it is not used by hibernate only anymore
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
41
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 -- Lage 'links', 'rechts', etc.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
44 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
45
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 CREATE TABLE positions (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
48 value VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50
758
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
51 -- Kante 'oben', 'unten'
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
52 CREATE SEQUENCE EDGES_ID_SEQ;
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
53
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
54 CREATE TABLE edges (
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
55 id int PRIMARY KEY NOT NULL,
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
56 top NUMERIC,
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
57 bottom NUMERIC
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
58 );
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
59
763
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
60 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
61 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
62
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
63 CREATE TABLE annotation_types (
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
64 id int PRIMARY KEY NOT NULL,
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
65 name VARCHAR(256) NOT NULL UNIQUE
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
66 );
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
67
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 -- Some object (eg. Hafen) at a segment of river
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 -- plus its position.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
70 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
71
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 CREATE TABLE annotations (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
73 id int PRIMARY KEY NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
74 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
75 attribute_id int NOT NULL REFERENCES attributes(id),
758
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
76 position_id int REFERENCES positions(id),
763
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
77 edge_id int REFERENCES edges(id),
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
78 type_id int REFERENCES annotation_types(id)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81 -- Pegel
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
82 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
83
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84 CREATE TABLE gauges (
2371
fe89d6cf55fb Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2347
diff changeset
85 id int PRIMARY KEY NOT NULL,
fe89d6cf55fb Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2347
diff changeset
86 name VARCHAR(256) NOT NULL,
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5202
diff changeset
87 -- remove river id here because range_id references river already
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: 4776
diff changeset
88 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5202
diff changeset
89 station NUMERIC NOT NULL,
2371
fe89d6cf55fb Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2347
diff changeset
90 aeo NUMERIC NOT NULL,
5894
62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
Tom Gottfried <tom@intevation.de>
parents: 5893
diff changeset
91 official_number int8,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
93 -- Pegelnullpunkt
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
94 datum NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
95 -- Streckengueltigkeit
6871
5ce6a3d7d632 Undo misguided schema change from r5207
Tom Gottfried <tom@intevation.de>
parents: 6817
diff changeset
96 range_id int REFERENCES ranges (id) ON DELETE CASCADE,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
97
167
15d515fe15f5 Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 164
diff changeset
98 UNIQUE (name, river_id),
5894
62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
Tom Gottfried <tom@intevation.de>
parents: 5893
diff changeset
99 UNIQUE (official_number, river_id),
194
0f0b98ef9b04 Fixed wrong unique constraint.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 174
diff changeset
100 UNIQUE (river_id, station)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
101 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
102
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
103 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
104 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
105
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 CREATE TABLE main_value_types (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
107 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
108 name VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111 -- Named type of a Hauptwert (eg. HQ100)
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
112 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
113
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114 CREATE TABLE named_main_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
115 id int PRIMARY KEY NOT NULL,
5915
4fafe8d147b2 Schema change: named_main_values.name is not unique, as we can have the same name for different types
Tom Gottfried <tom@intevation.de>
parents: 5894
diff changeset
116 name VARCHAR(256) NOT NULL,
5684
88cbe798cbab Schema change: fixed constraints
Tom Gottfried <tom@intevation.de>
parents: 5321
diff changeset
117 type_id int NOT NULL REFERENCES main_value_types(id)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
119
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
120 -- Table for time intervals
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
121 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
122
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
123 CREATE TABLE time_intervals (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
124 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
125 start_time TIMESTAMP NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
126 stop_time TIMESTAMP,
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
127 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
128 );
5298
eaa630838705 trigger sequence of time_interval IDs in DB as it is not used by hibernate only anymore
Tom Gottfried <tom@intevation.de>
parents: 5297
diff changeset
129
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
130
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
131 -- Stammdaten
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
132 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
133
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
134 CREATE TABLE main_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
135 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
136 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
137 named_value_id int NOT NULL REFERENCES named_main_values(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
138 value NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
139
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
140 time_interval_id int REFERENCES time_intervals(id),
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
141
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
142 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
143 UNIQUE (gauge_id, named_value_id, time_interval_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
144 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
145
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
146 -- Abflusstafeln
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
147 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
148
167
15d515fe15f5 Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 164
diff changeset
149 CREATE TABLE discharge_tables (
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
150 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
151 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
493
b35c5dc0f8b7 Importer: Make import of historical discharge tables work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 476
diff changeset
152 description VARCHAR(256) NOT NULL,
4776
20b6ebf23916 !!! FLYS backend schema change !!! Add column bfg_id column to discharge_tables.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4651
diff changeset
153 bfg_id VARCHAR(50),
470
f4afea9b7537 Forget ',' in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 467
diff changeset
154 kind int NOT NULL DEFAULT 0,
5883
4f35b34f4efa Schema change: add constraint to discharge tables
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
155 time_interval_id int REFERENCES time_intervals(id),
5923
131f5f58ff7d Schema change: leave it up to AFT whether time intervals are unique per gauge but do not accept duplicate bfg_id per gauge
Tom Gottfried <tom@intevation.de>
parents: 5915
diff changeset
156 UNIQUE(gauge_id, bfg_id, kind)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
157 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
158
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
159 -- Values of the Abflusstafeln
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
160 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
161
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
162 CREATE TABLE discharge_table_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
163 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
164 table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
165 q NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
166 w NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
167
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
168 UNIQUE (table_id, q, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
169 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
170
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
171 -- WST files
5202
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
172 --lookup table for wst kinds
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
173 CREATE TABLE wst_kinds (
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
174 id int PRIMARY KEY NOT NULL,
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
175 kind VARCHAR(64) NOT NULL
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
176 );
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
177 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
178 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
179 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
180 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
181 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
182 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
183 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
184 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
185
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
186
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
187 CREATE SEQUENCE WSTS_ID_SEQ;
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
188
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
189 CREATE TABLE wsts (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
190 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
191 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
192 description VARCHAR(256) NOT NULL,
5202
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
193 kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
194 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
195 UNIQUE (river_id, description)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
196 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
197
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
198 -- columns of WST files
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
199 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
200
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
201 CREATE TABLE wst_columns (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
202 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
203 wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
204 name VARCHAR(256) NOT NULL,
174
249390dd24e7 Added foreign key constraint annotations to model classes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
205 description VARCHAR(256),
6502
3b93f439e954 Schema change: add new field 'source' to wst_columns, currently only for official lines
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6300
diff changeset
206 source VARCHAR(256),
471
3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 470
diff changeset
207 position int NOT NULL DEFAULT 0,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
208
6011
d9a1cf0438a5 backout 6009:6ea171b6d8d2 because this was only really valid for fixations
Tom Gottfried <tom@intevation.de>
parents: 6009
diff changeset
209 time_interval_id int REFERENCES time_intervals(id),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
210
471
3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 470
diff changeset
211 UNIQUE (wst_id, name),
3570e4af8cb2 Added 'position' column to wst_columns to allow order them by there column position in the original wst file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 470
diff changeset
212 UNIQUE (wst_id, position)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
213 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
214
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
215 -- w values in WST file column
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
216 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
217
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
218 CREATE TABLE wst_column_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
219 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
220 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
221 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
222 w NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
223
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
224 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
225 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
226 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
227
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
228 -- bind q values to range
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
229 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
230
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
231 CREATE TABLE wst_q_ranges (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
232 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
233 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
234 q NUMERIC NOT NULL
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
235 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
236
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
237 -- bind q ranges to wst columns
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
238 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
239
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
240 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
241 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
242 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
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: 4776
diff changeset
243 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
244
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
245 UNIQUE (wst_column_id, wst_q_range_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
246 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
247
6300
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
248 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
249
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
250 CREATE TABLE official_lines (
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
251 id int PRIMARY KEY NOT NULL,
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
252 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
253 named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
254
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
255 UNIQUE (wst_column_id, named_main_value_id)
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
256 );
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
257
476
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 471
diff changeset
258 CREATE VIEW wst_value_table AS
6078
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
259 SELECT
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
260 wcv.position AS position,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
261 w,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
262 q,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
263 wc.position AS column_pos,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
264 w.id AS wst_id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
265 FROM wsts w
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
266 JOIN wst_columns wc
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
267 ON wc.wst_id=w.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
268 JOIN wst_column_q_ranges wcqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
269 ON wcqr.wst_column_id=wc.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
270 JOIN wst_q_ranges wqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
271 ON wcqr.wst_q_range_id=wqr.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
272 JOIN ranges r
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
273 ON wqr.range_id=r.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
274 JOIN wst_column_values wcv
6177
573112007ec7 Remove stray semicolon from sql schema.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 6143
diff changeset
275 ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b
476
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 471
diff changeset
276 ORDER BY wcv.position ASC,
6078
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
277 wc.position DESC;
476
77f337650211 Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 471
diff changeset
278
754
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
279 -- view to select the w values of a WST
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
280 CREATE VIEW wst_w_values AS
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
281 SELECT wcv."position" AS km,
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
282 wcv.w AS w,
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
283 wc."position" AS column_pos,
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
284 w.id AS wst_id
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
285 FROM wst_column_values wcv
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
286 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
287 JOIN wsts w ON wc.wst_id = w.id
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
288 ORDER BY wcv."position", wc."position";
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
289
755
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
290 -- view to select the q values of a WST
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
291 CREATE VIEW wst_q_values AS
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
292 SELECT wc.position AS column_pos,
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
293 wqr.q AS q,
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
294 r.a AS a,
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
295 r.b AS b,
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
296 wc.wst_id AS wst_id
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
297 FROM wst_column_q_ranges wcqr
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
298 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
299 JOIN ranges r ON wqr.range_id = r.id
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
300 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
301 ORDER BY wc.position, wcqr.wst_column_id, r.a;
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
302
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
303 -- data for the cross-sections
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
304
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
305 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
306
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
307 CREATE TABLE cross_sections (
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
308 id int PRIMARY KEY NOT NULL,
6030
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
309 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
310 time_interval_id int REFERENCES time_intervals(id),
1202
44581b40b968 Schema: Added description column to the cross section table
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1201
diff changeset
311 description VARCHAR(256)
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
312 );
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
313
1205
5f1506fc7636 Made import of cross sections work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1203
diff changeset
314 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
1203
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
315
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
316 CREATE TABLE cross_section_lines (
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
317 id int PRIMARY KEY NOT NULL,
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
318 km NUMERIC NOT NULL,
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: 4776
diff changeset
319 cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
1203
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
320 UNIQUE (km, cross_section_id)
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
321 );
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
322
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
323 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
324
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
325 CREATE TABLE cross_section_points (
1203
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
326 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
327 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
1203
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
328 col_pos int NOT NULL,
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
329 x NUMERIC NOT NULL,
6817
63a24b4fbebf fixed syntax errors in Postgres-schema.
Tom Gottfried <tom@intevation.de>
parents: 6502
diff changeset
330 y NUMERIC NOT NULL
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
331 );
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
332
3796
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
333 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
334 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
335 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
336 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
337 ON cross_section_points(cross_section_line_id);
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
338
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
339 -- Hydraulische Kenngroessen
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
340
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
341 CREATE SEQUENCE HYKS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
342
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
343 CREATE TABLE hyks (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
344 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
345 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
346 description VARCHAR(256) NOT NULL
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
347 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
348
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
349 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
350
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
351 CREATE TABLE hyk_entries (
1212
dd948530fd57 Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1209
diff changeset
352 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
353 hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
1212
dd948530fd57 Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1209
diff changeset
354 km NUMERIC NOT NULL,
1217
d5e39c16fd2a Schema: Fixed location of 'Peilungsjahre' in HYKs
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1214
diff changeset
355 measure TIMESTAMP,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
356 UNIQUE (hyk_id, km)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
357 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
358
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
359 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
360
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
361 CREATE TABLE hyk_formations (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
362 id int PRIMARY KEY NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
363 formation_num int NOT NULL DEFAULT 0,
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: 4776
diff changeset
364 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
365 top NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
366 bottom NUMERIC NOT NULL,
1214
32ee9babe42c Schema: Argh! Added distance_{vl|hf|vr} to
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1212
diff changeset
367 distance_vl NUMERIC NOT NULL,
32ee9babe42c Schema: Argh! Added distance_{vl|hf|vr} to
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1212
diff changeset
368 distance_hf NUMERIC NOT NULL,
32ee9babe42c Schema: Argh! Added distance_{vl|hf|vr} to
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1212
diff changeset
369 distance_vr NUMERIC NOT NULL,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
370 UNIQUE (hyk_entry_id, formation_num)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
371 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
372
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
373 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
374
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
375 CREATE TABLE hyk_flow_zone_types (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
376 id int PRIMARY KEY NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
377 name VARCHAR(50) NOT NULL UNIQUE,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
378 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
379 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
380
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
381 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
382
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
383 CREATE TABLE hyk_flow_zones (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
384 id int PRIMARY KEY NOT NULL,
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: 4776
diff changeset
385 formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
386 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
387 a NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
388 b NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
389 CHECK (a <= b)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
390 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
391
4651
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
392 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
393 AS
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
394 SELECT wc.id AS wst_column_id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
395 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
396 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
397 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
398 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
399 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
400 ON wc.id = wcv.wst_column_id
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
401 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
402 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
403
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
404 COMMIT;
http://dive4elements.wald.intevation.org