Mercurial > dive4elements > river
annotate backend/doc/schema/postgresql.sql @ 6814:e41059fb4073 double-precision
Importer Doc: Update title page automatically.
author | Tom Gottfried <tom.gottfried@intevation.de> |
---|---|
date | Sat, 10 Aug 2013 12:25:58 +0200 |
parents | 3b93f439e954 |
children | 63a24b4fbebf |
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, |
5893
c1abd48a03e2
Schema change: remove UNIQUE-constraint from rivers.official number as we have different representations of the same river (e.g. for sommer and winter)
Tom Gottfried <tom@intevation.de>
parents:
5883
diff
changeset
|
15 official_number int8, |
3946
948c7289fc42
Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3796
diff
changeset
|
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, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
72 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
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, |
5894
62e6598a2c4b
Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
Tom Gottfried <tom@intevation.de>
parents:
5893
diff
changeset
|
89 official_number int8, |
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), |
5894
62e6598a2c4b
Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
Tom Gottfried <tom@intevation.de>
parents:
5893
diff
changeset
|
97 UNIQUE (official_number, river_id), |
194
0f0b98ef9b04
Fixed wrong unique constraint.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
174
diff
changeset
|
98 UNIQUE (river_id, station) |
164
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 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
101 -- 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
|
102 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
|
103 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
104 CREATE TABLE main_value_types ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
105 id int PRIMARY KEY NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
106 name VARCHAR(256) NOT NULL UNIQUE |
164
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 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
109 -- 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
|
110 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
|
111 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
112 CREATE TABLE named_main_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
113 id int PRIMARY KEY NOT NULL, |
5915
4fafe8d147b2
Schema change: named_main_values.name is not unique, as we can have the same name for different types
Tom Gottfried <tom@intevation.de>
parents:
5894
diff
changeset
|
114 name VARCHAR(256) NOT NULL, |
5684
88cbe798cbab
Schema change: fixed constraints
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
115 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
|
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, |
5883
4f35b34f4efa
Schema change: add constraint to discharge tables
Tom Gottfried <tom.gottfried@intevation.de>
parents:
5838
diff
changeset
|
153 time_interval_id int REFERENCES time_intervals(id), |
5923
131f5f58ff7d
Schema change: leave it up to AFT whether time intervals are unique per gauge but do not accept duplicate bfg_id per gauge
Tom Gottfried <tom@intevation.de>
parents:
5915
diff
changeset
|
154 UNIQUE(gauge_id, bfg_id, kind) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
155 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
156 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
157 -- Values of the Abflusstafeln |
170
88c14d5d45be
Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
168
diff
changeset
|
158 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
|
159 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
160 CREATE TABLE discharge_table_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
161 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
|
162 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
|
163 q NUMERIC NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
164 w NUMERIC NOT NULL, |
164
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 UNIQUE (table_id, q, w) |
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 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
169 -- WST files |
5202
646c154477fe
SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents:
4991
diff
changeset
|
170 --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
|
171 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
|
172 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
|
173 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
|
174 ); |
646c154477fe
SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents:
4991
diff
changeset
|
175 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
|
176 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
|
177 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
|
178 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
|
179 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
|
180 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
|
181 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
|
182 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
|
183 |
646c154477fe
SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents:
4991
diff
changeset
|
184 |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
185 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
|
186 |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
187 CREATE TABLE wsts ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
188 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
|
189 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
|
190 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
|
191 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
|
192 -- TODO: more meta infos |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
193 UNIQUE (river_id, description) |
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 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
196 -- columns of WST files |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
197 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
|
198 |
171
4a83e14f40f9
Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
170
diff
changeset
|
199 CREATE TABLE wst_columns ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
200 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
|
201 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
|
202 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
|
203 description VARCHAR(256), |
6502
3b93f439e954
Schema change: add new field 'source' to wst_columns, currently only for official lines
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6300
diff
changeset
|
204 source VARCHAR(256), |
471
3570e4af8cb2
Added 'position' column to wst_columns to allow order them by there column position in the original wst file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
470
diff
changeset
|
205 position int NOT NULL DEFAULT 0, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
206 |
6011
d9a1cf0438a5
backout 6009:6ea171b6d8d2 because this was only really valid for fixations
Tom Gottfried <tom@intevation.de>
parents:
6009
diff
changeset
|
207 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
|
208 |
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
|
209 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
|
210 UNIQUE (wst_id, position) |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
211 ); |
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 -- 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
|
214 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
|
215 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
216 CREATE TABLE wst_column_values ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
217 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
|
218 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
|
219 position NUMERIC NOT NULL, |
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
220 w NUMERIC NOT NULL, |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
221 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
222 UNIQUE (position, wst_column_id), |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
223 UNIQUE (position, wst_column_id, w) |
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 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
226 -- 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
|
227 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
|
228 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
229 CREATE TABLE wst_q_ranges ( |
168
86a1bd9cc50e
More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
167
diff
changeset
|
230 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
|
231 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
|
232 q NUMERIC NOT NULL |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
233 ); |
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 -- 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
|
236 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
|
237 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
238 CREATE TABLE wst_column_q_ranges ( |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
239 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
|
240 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
|
241 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
|
242 |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
243 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
|
244 ); |
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
245 |
6300
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
246 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
247 |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
248 CREATE TABLE official_lines ( |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
249 id int PRIMARY KEY NOT NULL, |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
250 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
251 named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE, |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
252 |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
253 UNIQUE (wst_column_id, named_main_value_id) |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
254 ); |
0711ce5ca701
Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6177
diff
changeset
|
255 |
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
|
256 CREATE VIEW wst_value_table AS |
6078
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
257 SELECT |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
258 wcv.position AS position, |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
259 w, |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
260 q, |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
261 wc.position AS column_pos, |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
262 w.id AS wst_id |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
263 FROM wsts w |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
264 JOIN wst_columns wc |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
265 ON wc.wst_id=w.id |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
266 JOIN wst_column_q_ranges wcqr |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
267 ON wcqr.wst_column_id=wc.id |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
268 JOIN wst_q_ranges wqr |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
269 ON wcqr.wst_q_range_id=wqr.id |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
270 JOIN ranges r |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
271 ON wqr.range_id=r.id |
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
272 JOIN wst_column_values wcv |
6177
573112007ec7
Remove stray semicolon from sql schema.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
6143
diff
changeset
|
273 ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b |
476
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
274 ORDER BY wcv.position ASC, |
6078
176664f84d86
Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
6030
diff
changeset
|
275 wc.position DESC; |
476
77f337650211
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
471
diff
changeset
|
276 |
754
5bcf338eadb9
Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
505
diff
changeset
|
277 -- 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
|
278 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
|
279 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
|
280 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
|
281 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
|
282 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
|
283 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
|
284 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
|
285 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
|
286 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
|
287 |
755
a29fd0916803
Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
754
diff
changeset
|
288 -- 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
|
289 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
|
290 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
|
291 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
|
292 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
|
293 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
|
294 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
|
295 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
|
296 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
|
297 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
|
298 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
|
299 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
|
300 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
301 -- data for the cross-sections |
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 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
304 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
305 CREATE TABLE cross_sections ( |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
306 id int PRIMARY KEY NOT NULL, |
6030
d12f920bbf00
Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents:
6011
diff
changeset
|
307 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, |
d12f920bbf00
Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents:
6011
diff
changeset
|
308 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
|
309 description VARCHAR(256) |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
310 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
311 |
1205
5f1506fc7636
Made import of cross sections work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1203
diff
changeset
|
312 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
|
313 |
3c01bef43a98
Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1202
diff
changeset
|
314 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
|
315 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
|
316 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
|
317 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
|
318 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
|
319 ); |
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 |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
321 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
322 |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
323 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
|
324 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
|
325 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
|
326 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
|
327 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
|
328 y NUMERIC NOT NULL, |
1192
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
329 ); |
5f8444df19e4
Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
763
diff
changeset
|
330 |
3796
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
331 -- Indices for faster access of the points |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
332 CREATE INDEX cross_section_lines_km_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
333 ON cross_section_lines(km); |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
334 CREATE INDEX cross_section_points_line_idx |
61195d14b844
FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
335 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
|
336 |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
337 -- Hydraulische Kenngroessen |
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 HYKS_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 hyks ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
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 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
|
344 description VARCHAR(256) NOT NULL |
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 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
347 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
|
348 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
349 CREATE TABLE hyk_entries ( |
1212
dd948530fd57
Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1209
diff
changeset
|
350 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
|
351 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
|
352 km NUMERIC NOT NULL, |
1217
d5e39c16fd2a
Schema: Fixed location of 'Peilungsjahre' in HYKs
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1214
diff
changeset
|
353 measure TIMESTAMP, |
1209
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
354 UNIQUE (hyk_id, km) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
355 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
356 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
357 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
|
358 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
359 CREATE TABLE hyk_formations ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
360 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
|
361 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
|
362 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
|
363 top NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
364 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
|
365 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
|
366 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
|
367 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
|
368 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
|
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_ZONE_TYPES_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_zone_types ( |
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, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
375 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
|
376 description VARCHAR(256) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
377 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
378 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
379 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
|
380 |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
381 CREATE TABLE hyk_flow_zones ( |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
382 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
|
383 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
|
384 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
|
385 a NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
386 b NUMERIC NOT NULL, |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
387 CHECK (a <= b) |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
388 ); |
c12b5fbd33e8
schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1205
diff
changeset
|
389 |
4651
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
390 CREATE VIEW wst_ranges |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
391 AS |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
392 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
|
393 wc.wst_id AS wst_id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
394 Min(wcv.position) AS a, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
395 Max(wcv.position) AS b |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
396 FROM wst_columns wc |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
397 JOIN wst_column_values wcv |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
398 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
|
399 GROUP BY wc.id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
400 wc.wst_id; |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
401 |
164
324e12ab7a05
Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
402 COMMIT; |