Mercurial > dive4elements > river
annotate flys-backend/doc/schema/postgresql.sql @ 4284:7a94d5e7fc3d
Write the "hidden" attribute of a style into the collection's attribute when using the theme editor.
Otherwise, the next time using the theme editor, the hidden attributes that should not be changed
by the user are visible.
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Mon, 29 Oct 2012 07:18:42 +0100 |
parents | 948c7289fc42 |
children | 83a42e6a562d |
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, |
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
|
15 official_number int8 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
|
16 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
|
17 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
|
18 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
|
19 ); |
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 -- Bruecke, Haefen, etc. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
22 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
23 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
24 CREATE TABLE attributes ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
25 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
26 value VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
27 ); |
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 -- 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
|
30 CREATE SEQUENCE RANGES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
31 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
32 CREATE TABLE ranges ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
33 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
34 river_id int NOT NULL REFERENCES rivers(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
35 a NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
36 b NUMERIC, |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
37 UNIQUE (river_id, a, b) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
38 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
39 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
40 -- Lage 'links', 'rechts', etc. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
41 CREATE SEQUENCE POSITIONS_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
42 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
43 CREATE TABLE positions ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
44 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
45 value VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
46 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
47 |
758
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
48 -- Kante 'oben', 'unten' |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
49 CREATE SEQUENCE EDGES_ID_SEQ; |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
50 |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
51 CREATE TABLE edges ( |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
52 id int PRIMARY KEY NOT NULL, |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
53 top NUMERIC, |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
54 bottom NUMERIC |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
55 ); |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
56 |
763
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
57 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
58 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
59 |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
60 CREATE TABLE annotation_types ( |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
61 id int PRIMARY KEY NOT NULL, |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
62 name VARCHAR(256) NOT NULL UNIQUE |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
63 ); |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
64 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
65 -- 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
|
66 -- plus its position. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
67 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
68 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
69 CREATE TABLE annotations ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
70 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
71 range_id int NOT NULL REFERENCES ranges(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
72 attribute_id int NOT NULL REFERENCES attributes(id), |
758
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
73 position_id int REFERENCES positions(id), |
763
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
74 edge_id int REFERENCES edges(id), |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
75 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
|
76 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
77 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
78 -- Pegel |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
79 CREATE SEQUENCE GAUGES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
80 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
81 CREATE TABLE gauges ( |
2371
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
82 id int PRIMARY KEY NOT NULL, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
83 name VARCHAR(256) NOT NULL, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
84 river_id int NOT NULL REFERENCES rivers(id), |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
85 station NUMERIC NOT NULL UNIQUE, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
86 aeo NUMERIC NOT NULL, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
87 official_number int8 UNIQUE, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
88 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
89 -- Pegelnullpunkt |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
90 datum NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
91 -- Streckengueltigkeit |
2373
056b3a5aa181
Removed "NOT NULL" constraint from gauges.range_id because there are gauges which dont have a Gueltigkeitsbereich.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2371
diff
changeset
|
92 range_id int REFERENCES ranges (id), |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
93 |
167
15d515fe15f5
Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
164
diff
changeset
|
94 UNIQUE (name, river_id), |
194
0f0b98ef9b04
Fixed wrong unique constraint.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
174
diff
changeset
|
95 UNIQUE (river_id, station) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
96 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
97 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
98 -- 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
|
99 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
|
100 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
101 CREATE TABLE main_value_types ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
102 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
103 name VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
104 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
105 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
106 -- 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
|
107 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
|
108 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
109 CREATE TABLE named_main_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
110 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
111 name VARCHAR(256) NOT NULL UNIQUE, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
112 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
|
113 UNIQUE (name, type_id) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
114 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
115 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
116 -- Table for time intervals |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
117 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
|
118 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
119 CREATE TABLE time_intervals ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
120 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
121 start_time TIMESTAMP NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
122 stop_time TIMESTAMP, |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
123 CHECK (start_time <= stop_time) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
124 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
125 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
126 -- Stammdaten |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
127 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
|
128 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
129 CREATE TABLE main_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
130 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
131 gauge_id int NOT NULL REFERENCES gauges(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
132 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
|
133 value NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
134 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
135 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
|
136 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
137 -- TODO: better checks |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
138 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
|
139 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
140 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
141 -- Abflusstafeln |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
142 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
|
143 |
167
15d515fe15f5
Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
164
diff
changeset
|
144 CREATE TABLE discharge_tables ( |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
145 id int PRIMARY KEY NOT NULL, |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
146 gauge_id int NOT NULL REFERENCES gauges(id), |
493
b35c5dc0f8b7
Importer: Make import of historical discharge tables work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
476
diff
changeset
|
147 description VARCHAR(256) NOT NULL, |
470
f4afea9b7537
Forget ',' in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
467
diff
changeset
|
148 kind int NOT NULL DEFAULT 0, |
496
4f2d8980415f
Schema: Dropped constraint which forces discharge tables to have a unique time interval for a given gauge and kind.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
493
diff
changeset
|
149 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
|
150 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
151 -- TODO: better checks |
496
4f2d8980415f
Schema: Dropped constraint which forces discharge tables to have a unique time interval for a given gauge and kind.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
493
diff
changeset
|
152 -- UNIQUE (gauge_id, kind, time_interval_id) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
153 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
154 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
155 -- Values of the Abflusstafeln |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
156 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
|
157 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
158 CREATE TABLE discharge_table_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
159 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
160 table_id int NOT NULL REFERENCES discharge_tables(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
161 q NUMERIC NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
162 w NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
163 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
164 UNIQUE (table_id, q, w) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
165 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
166 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
167 -- WST files |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
168 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
|
169 |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
170 CREATE TABLE wsts ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
171 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
172 river_id int NOT NULL REFERENCES rivers(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
173 description VARCHAR(256) NOT NULL, |
470
f4afea9b7537
Forget ',' in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
467
diff
changeset
|
174 kind int NOT NULL DEFAULT 0, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
175 -- TODO: more meta infos |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
176 UNIQUE (river_id, description) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
177 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
178 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
179 -- columns of WST files |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
180 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
|
181 |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
182 CREATE TABLE wst_columns ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
183 id int PRIMARY KEY NOT NULL, |
173
8e2f2ddc507f
Fixed wrong spelled column references in foreign keys introduces with last change.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
171
diff
changeset
|
184 wst_id int NOT NULL REFERENCES wsts(id), |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
185 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
|
186 description 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
|
187 position int NOT NULL DEFAULT 0, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
188 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
189 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
|
190 |
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
|
191 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
|
192 UNIQUE (wst_id, position) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
193 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
194 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
195 -- 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
|
196 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
|
197 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
198 CREATE TABLE wst_column_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
199 id int PRIMARY KEY NOT NULL, |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
200 wst_column_id int NOT NULL REFERENCES wst_columns(id), |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
201 position NUMERIC NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
202 w NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
203 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
204 UNIQUE (position, wst_column_id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
205 UNIQUE (position, wst_column_id, w) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
206 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
207 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
208 -- 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
|
209 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
|
210 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
211 CREATE TABLE wst_q_ranges ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
212 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
213 range_id int NOT NULL REFERENCES ranges(id), |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
214 q NUMERIC NOT NULL |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
215 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
216 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
217 -- 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
|
218 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
|
219 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
220 CREATE TABLE wst_column_q_ranges ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
221 id int PRIMARY KEY NOT NULL, |
173
8e2f2ddc507f
Fixed wrong spelled column references in foreign keys introduces with last change.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
171
diff
changeset
|
222 wst_column_id int NOT NULL REFERENCES wst_columns(id), |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
223 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
224 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
225 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
|
226 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
227 |
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
|
228 CREATE VIEW wst_value_table AS |
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
|
229 SELECT wcv.position AS position, |
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
|
230 w, |
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
|
231 (SELECT q |
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
|
232 FROM wst_column_q_ranges wcqr |
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
|
233 JOIN wst_q_ranges wqr |
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
|
234 ON wcqr.wst_q_range_id = wqr.id |
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
|
235 JOIN ranges r |
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
|
236 ON r.id = wqr.range_id |
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
|
237 WHERE wcqr.wst_column_id = wc.id |
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
|
238 AND wcv.position BETWEEN r.a AND r.b) AS q, |
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
|
239 wc.position AS column_pos, |
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
|
240 w.id AS wst_id |
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
|
241 FROM wst_column_values wcv |
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
|
242 JOIN wst_columns wc |
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
|
243 ON wcv.wst_column_id = wc.id |
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
|
244 JOIN wsts w |
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
|
245 ON wc.wst_id = w.id |
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
|
246 ORDER BY wcv.position ASC, |
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
|
247 wc.position DESC; |
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
|
248 |
754
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
249 -- 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
|
250 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
|
251 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
|
252 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
|
253 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
|
254 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
|
255 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
|
256 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
|
257 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
|
258 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
|
259 |
755
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
260 -- 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
|
261 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
|
262 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
|
263 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
|
264 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
|
265 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
|
266 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
|
267 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
|
268 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
|
269 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
|
270 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
|
271 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
|
272 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
273 -- data for the cross-sections |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
274 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
275 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
276 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
277 CREATE TABLE cross_sections ( |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
278 id int PRIMARY KEY NOT NULL, |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
279 river_id int NOT NULL REFERENCES rivers(id), |
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
|
280 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
|
281 description VARCHAR(256) |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
282 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
283 |
1205
5f1506fc7636
Made import of cross sections work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1203
diff
changeset
|
284 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
|
285 |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
286 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
|
287 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
|
288 km NUMERIC 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
|
289 cross_section_id int NOT NULL REFERENCES cross_sections(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
|
290 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
|
291 ); |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
292 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
293 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
294 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
295 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
|
296 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
|
297 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(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
|
298 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
|
299 x NUMERIC 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
|
300 y NUMERIC 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
|
301 UNIQUE (cross_section_line_id, col_pos) |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
302 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
303 |
3796
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
304 -- Indices for faster access of the points |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
305 CREATE INDEX cross_section_lines_km_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
306 ON cross_section_lines(km); |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
307 CREATE INDEX cross_section_points_line_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
308 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
|
309 |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
310 -- Hydraulische Kenngroessen |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
311 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
312 CREATE SEQUENCE HYKS_ID_SEQ; |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
313 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
314 CREATE TABLE hyks ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
315 id int PRIMARY KEY NOT NULL, |
1226
1dbafbba50ea
Schema: added missing key contraint on hyks table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1217
diff
changeset
|
316 river_id int NOT NULL REFERENCES rivers(id), |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
317 description VARCHAR(256) NOT NULL |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
318 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
319 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
320 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
|
321 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
322 CREATE TABLE hyk_entries ( |
1212
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
323 id int PRIMARY KEY NOT NULL, |
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
324 hyk_id int NOT NULL REFERENCES hyks(id), |
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
325 km NUMERIC NOT NULL, |
1217
d5e39c16fd2a
Schema: Fixed location of 'Peilungsjahre' in HYKs
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1214
diff
changeset
|
326 measure TIMESTAMP, |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
327 UNIQUE (hyk_id, km) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
328 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
329 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
330 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
|
331 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
332 CREATE TABLE hyk_formations ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
333 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
|
334 formation_num int NOT NULL DEFAULT 0, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
335 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id), |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
336 top NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
337 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
|
338 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
|
339 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
|
340 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
|
341 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
|
342 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
343 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
344 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
|
345 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
346 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
|
347 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
|
348 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
|
349 description VARCHAR(256) |
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 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
352 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
|
353 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
354 CREATE TABLE hyk_flow_zones ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
355 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
|
356 formation_id int NOT NULL REFERENCES hyk_formations(id), |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
357 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
|
358 a NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
359 b NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
360 CHECK (a <= b) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
361 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
362 |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
363 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
364 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
365 SELECT w.river_id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
366 w.id AS wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
367 wc.id AS wst_column_id, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
368 wc.name AS name, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
369 wc.position AS wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
370 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
371 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
372 ON wc.wst_id = w.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
373 WHERE w.kind = 3; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
374 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
375 CREATE VIEW q_main_values |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
376 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
377 SELECT riv.id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
378 g.id AS gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
379 g.name AS gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
380 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
381 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
382 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
383 nmv.name, E'[:space:]*\\(.*\\)[:space:]*', '') AS name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
384 CAST(mv.value AS NUMERIC(38, 2)) AS value |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
385 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
386 JOIN named_main_values nmv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
387 ON mv.named_value_id = nmv.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
388 JOIN main_value_types mvt |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
389 ON nmv.type_id = mvt.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
390 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
391 ON mv.gauge_id = g.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
392 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
393 ON g.range_id = r.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
394 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
395 ON g.river_id = riv.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
396 WHERE mvt.name = 'Q' |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
397 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
398 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
399 CREATE VIEW official_q_values |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
400 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
401 SELECT ol.river_id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
402 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
403 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
404 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
405 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
406 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
407 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
408 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
409 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
410 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
411 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
412 JOIN q_main_values qmv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
413 ON ol.river_id = qmv.river_id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
414 AND ol.name = qmv.name; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
415 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
416 COMMIT; |