Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql.sql @ 5923:131f5f58ff7d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Schema change: leave it up to AFT whether time intervals are unique per gauge but do not accept duplicate bfg_id per gauge
author Tom Gottfried <tom@intevation.de>
date Wed, 08 May 2013 13:16:29 +0200
parents 4fafe8d147b2
children 6ea171b6d8d2
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),
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
204 position int NOT NULL DEFAULT 0,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
205
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
206 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
207
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
208 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
209 UNIQUE (wst_id, position)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
210 );
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 -- 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
213 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
214
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
215 CREATE TABLE wst_column_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
216 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
217 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
218 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
219 w NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
220
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
221 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
222 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
223 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
224
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
225 -- 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
226 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
227
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
228 CREATE TABLE wst_q_ranges (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
229 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
230 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
231 q NUMERIC NOT NULL
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
232 );
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 -- 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
235 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
236
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
237 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
238 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
239 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
240 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
241
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
242 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
243 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
244
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
245 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
246 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
247 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
248 (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
249 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
250 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
251 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
252 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
253 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
254 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
255 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
256 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
257 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
258 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
259 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
260 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
261 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
262 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
263 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
264 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
265
754
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
266 -- 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
267 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
268 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
269 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
270 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
271 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
272 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
273 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
274 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
275 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
276
755
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
277 -- 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
278 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
279 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
280 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
281 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
282 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
283 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
284 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
285 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
286 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
287 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
288 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
289
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
290 -- data for the cross-sections
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
291
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
292 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
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 TABLE cross_sections (
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
295 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
296 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
297 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
298 description VARCHAR(256)
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
299 );
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
300
1205
5f1506fc7636 Made import of cross sections work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1203
diff changeset
301 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
302
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
303 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
304 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
305 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
306 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
307 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
308 );
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
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
310 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
311
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
312 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
313 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
314 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
315 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
316 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
317 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
318 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
319 );
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
320
3796
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
321 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
322 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
323 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
324 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
325 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
326
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
327 -- Hydraulische Kenngroessen
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
328
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
329 CREATE SEQUENCE HYKS_ID_SEQ;
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 TABLE hyks (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
332 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
333 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
334 description VARCHAR(256) NOT NULL
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
335 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
336
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
337 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
338
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
339 CREATE TABLE hyk_entries (
1212
dd948530fd57 Schema: Added missing columns in HYK tables.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1209
diff changeset
340 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
341 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
342 km NUMERIC NOT NULL,
1217
d5e39c16fd2a Schema: Fixed location of 'Peilungsjahre' in HYKs
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1214
diff changeset
343 measure TIMESTAMP,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
344 UNIQUE (hyk_id, km)
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_FORMATIONS_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_formations (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
350 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
351 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
352 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
353 top NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
354 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
355 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
356 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
357 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
358 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
359 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
360
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
361 CREATE 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
362
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
363 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
364 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
365 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
366 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
367 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
368
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
369 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
370
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
371 CREATE TABLE hyk_flow_zones (
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
372 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
373 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
374 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
375 a NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
376 b NUMERIC NOT NULL,
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
377 CHECK (a <= b)
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
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
380 CREATE VIEW official_lines
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
381 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
382 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
383 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
384 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
385 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
386 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
387 FROM wsts w
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
388 JOIN wst_columns wc
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
389 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
390 WHERE w.kind = 3;
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
391
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
392 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
393 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
394 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
395 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
396 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
397 r.a AS a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
398 r.b AS b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
399 REGEXP_REPLACE(
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
400 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
401 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
402 FROM main_values mv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
403 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
404 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
405 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
406 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
407 JOIN gauges g
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
408 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
409 JOIN ranges r
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
410 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
411 JOIN rivers riv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
412 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
413 WHERE mvt.name = 'Q'
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
414 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
415
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
416 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
417 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
418 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
419 wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
420 wst_column_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
421 gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
422 gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
423 a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
424 b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
425 ol.name,
3470
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
426 value,
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
427 wst_column_pos
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
428 FROM official_lines ol
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2373
diff changeset
429 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
430 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
431 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
432
4651
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
433 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
434 AS
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
435 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
436 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
437 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
438 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
439 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
440 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
441 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
442 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
443 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
444
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
445 COMMIT;
http://dive4elements.wald.intevation.org