Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql.sql @ 6652:765944ca6df0 double-precision

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Purge tables when droping in Oracle to free space and avoid duplicates in views such as dba_constraints
author Tom Gottfried <tom@intevation.de>
date Wed, 24 Jul 2013 18:41:22 +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;
http://dive4elements.wald.intevation.org