Mercurial > dive4elements > river
annotate flys-backend/doc/schema/postgresql.sql @ 5502:566eea137330
Added i18n strings for static sq data.
author | Raimund Renkert <rrenkert@intevation.de> |
---|---|
date | Thu, 28 Mar 2013 15:17:52 +0100 |
parents | 6dd354e7abfc |
children | 88cbe798cbab |
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, |
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
|
34 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
|
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 ); |
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
|
39 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
40 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
41 -- Lage 'links', 'rechts', etc. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
42 CREATE SEQUENCE POSITIONS_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
43 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
44 CREATE TABLE positions ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
45 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
46 value VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
47 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
48 |
758
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
49 -- Kante 'oben', 'unten' |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
50 CREATE SEQUENCE EDGES_ID_SEQ; |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
51 |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
52 CREATE TABLE edges ( |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
53 id int PRIMARY KEY NOT NULL, |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
54 top NUMERIC, |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
55 bottom NUMERIC |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
56 ); |
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
57 |
763
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
58 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
59 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
60 |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
61 CREATE TABLE annotation_types ( |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
62 id int PRIMARY KEY NOT NULL, |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
63 name VARCHAR(256) NOT NULL UNIQUE |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
64 ); |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
65 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
66 -- 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
|
67 -- plus its position. |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
68 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
69 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
70 CREATE TABLE annotations ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
71 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
|
72 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
|
73 attribute_id int NOT NULL REFERENCES attributes(id), |
758
bf16268629d9
Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
755
diff
changeset
|
74 position_id int REFERENCES positions(id), |
763
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
75 edge_id int REFERENCES edges(id), |
8076f6a689d0
First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
758
diff
changeset
|
76 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
|
77 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
78 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
79 -- Pegel |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
80 CREATE SEQUENCE GAUGES_ID_SEQ; |
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
81 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
82 CREATE TABLE gauges ( |
2371
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
83 id int PRIMARY KEY NOT NULL, |
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
84 name VARCHAR(256) NOT NULL, |
5207
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5202
diff
changeset
|
85 -- 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
|
86 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
|
87 station NUMERIC NOT NULL, |
2371
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2347
diff
changeset
|
88 aeo NUMERIC NOT NULL, |
5207
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5202
diff
changeset
|
89 official_number int8 UNIQUE, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
90 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
91 -- Pegelnullpunkt |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
92 datum NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
93 -- Streckengueltigkeit |
5207
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5202
diff
changeset
|
94 range_id int NOT NULL REFERENCES ranges (id) ON DELETE CASCADE, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
95 |
167
15d515fe15f5
Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
164
diff
changeset
|
96 UNIQUE (name, river_id), |
194
0f0b98ef9b04
Fixed wrong unique constraint.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
174
diff
changeset
|
97 UNIQUE (river_id, station) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
98 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
99 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
100 -- 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
|
101 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
|
102 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
103 CREATE TABLE main_value_types ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
104 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
105 name VARCHAR(256) NOT NULL UNIQUE |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
106 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
107 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
108 -- 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
|
109 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
|
110 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
111 CREATE TABLE named_main_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
112 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
113 name VARCHAR(256) NOT NULL UNIQUE, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
114 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
|
115 UNIQUE (name, type_id) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
116 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
117 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
118 -- Table for time intervals |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
119 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
|
120 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
121 CREATE TABLE time_intervals ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
122 id int PRIMARY KEY NOT NULL, |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
123 start_time TIMESTAMP NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
124 stop_time TIMESTAMP, |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
125 CHECK (start_time <= stop_time) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
126 ); |
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
|
127 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
128 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
129 -- Stammdaten |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
130 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
|
131 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
132 CREATE TABLE main_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
133 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
|
134 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
|
135 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
|
136 value NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
137 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
138 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
|
139 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
140 -- TODO: better checks |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
141 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
|
142 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
143 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
144 -- Abflusstafeln |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
145 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
|
146 |
167
15d515fe15f5
Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
164
diff
changeset
|
147 CREATE TABLE discharge_tables ( |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
148 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
|
149 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
|
150 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
|
151 bfg_id VARCHAR(50), |
470
f4afea9b7537
Forget ',' in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
467
diff
changeset
|
152 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
|
153 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
|
154 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
155 -- 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
|
156 -- 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
|
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), |
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
|
206 position int NOT NULL DEFAULT 0, |
164
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 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
|
209 |
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
|
210 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
|
211 UNIQUE (wst_id, position) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
212 ); |
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 -- 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
|
215 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
|
216 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
217 CREATE TABLE wst_column_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
218 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
|
219 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
|
220 position NUMERIC NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
221 w NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
222 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
223 UNIQUE (position, wst_column_id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
224 UNIQUE (position, wst_column_id, w) |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
225 ); |
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 -- 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
|
228 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
|
229 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
230 CREATE TABLE wst_q_ranges ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
231 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
|
232 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
|
233 q NUMERIC NOT NULL |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
234 ); |
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 -- 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
|
237 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
|
238 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
239 CREATE TABLE wst_column_q_ranges ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
240 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
|
241 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
|
242 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
|
243 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
244 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
|
245 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
246 |
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
|
247 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
|
248 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
|
249 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
|
250 (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
|
251 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
|
252 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
|
253 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
|
254 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
|
255 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
|
256 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
|
257 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
|
258 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
|
259 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
|
260 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
|
261 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
|
262 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
|
263 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
|
264 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
|
265 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
|
266 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
|
267 |
754
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
268 -- 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
|
269 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
|
270 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
|
271 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
|
272 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
|
273 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
|
274 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
|
275 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
|
276 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
|
277 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
|
278 |
755
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
279 -- 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
|
280 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
|
281 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
|
282 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
|
283 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
|
284 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
|
285 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
|
286 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
|
287 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
|
288 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
|
289 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
|
290 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
|
291 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
292 -- data for the cross-sections |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
293 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
294 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
295 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
296 CREATE TABLE cross_sections ( |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
297 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
|
298 river_id int NOT NULL REFERENCES rivers(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
|
299 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
|
300 description VARCHAR(256) |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
301 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
302 |
1205
5f1506fc7636
Made import of cross sections work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1203
diff
changeset
|
303 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
|
304 |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
305 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
|
306 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
|
307 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
|
308 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
|
309 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
|
310 ); |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
311 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
312 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
313 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
314 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
|
315 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
|
316 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
|
317 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
|
318 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
|
319 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
|
320 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
|
321 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
322 |
3796
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
323 -- Indices for faster access of the points |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
324 CREATE INDEX cross_section_lines_km_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
325 ON cross_section_lines(km); |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
326 CREATE INDEX cross_section_points_line_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
327 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
|
328 |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
329 -- Hydraulische Kenngroessen |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
330 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
331 CREATE SEQUENCE HYKS_ID_SEQ; |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
332 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
333 CREATE TABLE hyks ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
334 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
|
335 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
|
336 description VARCHAR(256) NOT NULL |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
337 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
338 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
339 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
|
340 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
341 CREATE TABLE hyk_entries ( |
1212
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
342 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
|
343 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
|
344 km NUMERIC NOT NULL, |
1217
d5e39c16fd2a
Schema: Fixed location of 'Peilungsjahre' in HYKs
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1214
diff
changeset
|
345 measure TIMESTAMP, |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
346 UNIQUE (hyk_id, km) |
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_FORMATIONS_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_formations ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
352 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
|
353 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
|
354 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
|
355 top NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
356 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
|
357 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
|
358 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
|
359 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
|
360 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
|
361 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
362 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
363 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
|
364 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
365 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
|
366 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
|
367 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
|
368 description VARCHAR(256) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
369 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
370 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
371 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
|
372 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
373 CREATE TABLE hyk_flow_zones ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
374 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
|
375 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
|
376 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
|
377 a NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
378 b NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
379 CHECK (a <= b) |
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 |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
382 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
383 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
384 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
|
385 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
|
386 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
|
387 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
|
388 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
|
389 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
390 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
391 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
|
392 WHERE w.kind = 3; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
393 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
394 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
|
395 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
396 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
|
397 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
|
398 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
|
399 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
400 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
401 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
402 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
|
403 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
|
404 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
405 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
|
406 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
|
407 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
|
408 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
|
409 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
410 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
|
411 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
412 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
|
413 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
414 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
|
415 WHERE mvt.name = 'Q' |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
416 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
|
417 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
418 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
|
419 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
420 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
|
421 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
422 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
423 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
424 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
425 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
426 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
427 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
428 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
429 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
430 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2373
diff
changeset
|
431 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
|
432 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
|
433 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
|
434 |
4651
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
435 CREATE VIEW wst_ranges |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
436 AS |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
437 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
|
438 wc.wst_id AS wst_id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
439 Min(wcv.position) AS a, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
440 Max(wcv.position) AS b |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
441 FROM wst_columns wc |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
442 JOIN wst_column_values wcv |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
443 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
|
444 GROUP BY wc.id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
445 wc.wst_id; |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
446 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
447 COMMIT; |