Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql.sql @ 8943:71b17f731762

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Same data types, precision and scale as in Oracle (as far as possible)
author mschaefer
date Tue, 13 Mar 2018 09:42:41 +0100
parents 3912da70736b
children f89fb9e9abad
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 (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
6 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
2347
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
8683
cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table.
Andre Heinecke <andre.heinecke@intevation.de>
parents: 8658
diff changeset
10 -- SEDDB_NAME
8687
3912da70736b (issue1796) We don't necessarily need the river Rhine. SedDB river names are purely optional.
Tom Gottfried <tom@intevation.de>
parents: 8683
diff changeset
11 -- Lookup table for optional matching with differing river names in SedDB
3912da70736b (issue1796) We don't necessarily need the river Rhine. SedDB river names are purely optional.
Tom Gottfried <tom@intevation.de>
parents: 8683
diff changeset
12 -- Add name here and set rivers.seddb_name_id to id
8683
cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table.
Andre Heinecke <andre.heinecke@intevation.de>
parents: 8658
diff changeset
13 CREATE TABLE seddb_name (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
14 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
8683
cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table.
Andre Heinecke <andre.heinecke@intevation.de>
parents: 8658
diff changeset
15 name VARCHAR(256) NOT NULL
cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table.
Andre Heinecke <andre.heinecke@intevation.de>
parents: 8658
diff changeset
16 );
cfafe5764509 (issue 1796) Scheme change! Add model for seddb_name lookup table.
Andre Heinecke <andre.heinecke@intevation.de>
parents: 8658
diff changeset
17
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 -- Gewaesser
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
19 CREATE SEQUENCE RIVERS_ID_SEQ;
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
20
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 CREATE TABLE rivers (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
22 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
7743
a67bd7744827 Schema correction following yesterday's brain failure.
Tom Gottfried <tom@intevation.de>
parents: 7741
diff changeset
23 model_uuid CHAR(36) UNIQUE,
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
24 official_number NUMERIC(12,0),
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
25 name VARCHAR(256) NOT NULL UNIQUE,
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
26 km_up NUMERIC(1,0) DEFAULT 0 NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
27 wst_unit_id NUMERIC(9,0) NOT NULL REFERENCES units(id),
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
28 seddb_name_id NUMERIC(9,0) REFERENCES seddb_name(id),
8411
b8c6cb36607e SCHEMA CHANGE: There is no boolean data type in oracle, and therefore it's no good idea to use it in postgresql.
Tom Gottfried <tom@intevation.de>
parents: 7743
diff changeset
29 CHECK(km_up IN(0,1))
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32 -- Bruecke, Haefen, etc.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
33 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
34
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 CREATE TABLE attributes (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
36 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
37 value VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 -- 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
41 CREATE SEQUENCE RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
42
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
43 --FIXME: make precision and scale of a and b columns equal with the km columns of the other tables
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 CREATE TABLE ranges (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
45 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
46 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
47 a NUMERIC(14,10) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
48 b NUMERIC(14,10),
7005
dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6871
diff changeset
49 UNIQUE (river_id, a, b),
dddf633fec05 SCHEMA CHANGE: a < b now enforced on ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6871
diff changeset
50 CHECK (a < b)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51 );
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
52
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 -- Lage 'links', 'rechts', etc.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
55 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
56
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 CREATE TABLE positions (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
58 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
59 value VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61
758
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
62 -- Kante 'oben', 'unten'
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
63 CREATE SEQUENCE EDGES_ID_SEQ;
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
64
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
65 CREATE TABLE edges (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
66 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
67 top NUMERIC(6,2),
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
68 bottom NUMERIC(6,2)
758
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
69 );
bf16268629d9 Added 'Kanten' model
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 755
diff changeset
70
763
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
71 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
72 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
73
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
74 CREATE TABLE annotation_types (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
75 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
763
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
76 name VARCHAR(256) NOT NULL UNIQUE
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
77 );
8076f6a689d0 First part of flys/issue18
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 758
diff changeset
78
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79 -- 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
80 -- plus its position.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
81 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
82
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 CREATE TABLE annotations (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
84 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
85 range_id NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
86 attribute_id NUMERIC(9,0) NOT NULL REFERENCES attributes(id),
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
87 position_id NUMERIC(9,0) REFERENCES positions(id),
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
88 edge_id NUMERIC(9,0) REFERENCES edges(id),
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
89 type_id NUMERIC(9,0) REFERENCES annotation_types(id)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92 -- Pegel
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
93 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
94
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
95 --FIXME: make precision and scale of station column equal with the km columns of the other tables
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96 CREATE TABLE gauges (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
97 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
2371
fe89d6cf55fb Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2347
diff changeset
98 name VARCHAR(256) NOT NULL,
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5202
diff changeset
99 -- remove river id here because range_id references river already
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
100 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
101 station NUMERIC(8,4) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
102 aeo NUMERIC(9,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
103 official_number NUMERIC(12,0),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
104
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
105 -- Pegelnullpunkt
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
106 datum NUMERIC(6,2) NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
107 -- Streckengueltigkeit
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
108 range_id NUMERIC(9,0) REFERENCES ranges (id) ON DELETE CASCADE,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109
167
15d515fe15f5 Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 164
diff changeset
110 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
111 UNIQUE (official_number, river_id),
194
0f0b98ef9b04 Fixed wrong unique constraint.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 174
diff changeset
112 UNIQUE (river_id, station)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
113 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
115 -- 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
116 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
117
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 CREATE TABLE main_value_types (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
119 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
120 name VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
121 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
122
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
123 -- 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
124 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
125
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
126 CREATE TABLE named_main_values (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
127 id NUMERIC(9,0) 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
128 name VARCHAR(256) NOT NULL,
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
129 type_id NUMERIC(9,0) 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
130 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
131
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
132 -- Table for time intervals
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
133 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
134
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
135 CREATE TABLE time_intervals (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
136 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
137 start_time TIMESTAMP(0) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
138 stop_time TIMESTAMP(0),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
139 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
140 );
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
141
164
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 -- Stammdaten
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
144 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
145
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
146 CREATE TABLE main_values (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
147 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
148 gauge_id NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
149 named_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id),
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
150 value NUMERIC(12,2) NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
151
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
152 time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
153
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
154 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
155 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
156 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
157
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
158 -- Abflusstafeln
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
159 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
160
167
15d515fe15f5 Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 164
diff changeset
161 CREATE TABLE discharge_tables (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
162 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
163 gauge_id NUMERIC(9,0) 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
164 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
165 bfg_id VARCHAR(50),
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
166 kind NUMERIC(9,0) NOT NULL DEFAULT 0,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
167 time_interval_id NUMERIC(9,0) 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
168 UNIQUE(gauge_id, bfg_id, kind)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
169 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
170
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
171 -- Values of the Abflusstafeln
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
172 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
173
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
174 CREATE TABLE discharge_table_values (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
175 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
176 table_id NUMERIC(9,0) NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
177 q NUMERIC(9,4) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
178 w NUMERIC(6,2) NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
179
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
180 UNIQUE (table_id, q, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
181 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
182
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
183 -- WST files
5202
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
184 --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
185 CREATE TABLE wst_kinds (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
186 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
5202
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
187 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
188 );
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
189 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
190 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
191 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
192 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
193 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
194 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
195 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
196 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
197
646c154477fe SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents: 4991
diff changeset
198
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
199 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
200
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
201 CREATE TABLE wsts (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
202 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
203 river_id NUMERIC(9,0) 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
204 description VARCHAR(256) NOT NULL,
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
205 kind NUMERIC(9,0) 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
206 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
207 UNIQUE (river_id, description)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
208 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
209
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
210 -- columns of WST files
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
211 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
212
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
213 CREATE TABLE wst_columns (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
214 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
215 wst_id NUMERIC(9,0) 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
216 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
217 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
218 source VARCHAR(256),
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
219 position NUMERIC(9,0) NOT NULL DEFAULT 0,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
220
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
221 time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
222
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
223 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
224 UNIQUE (wst_id, position)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
225 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
226
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
227 -- 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
228 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
229
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
230 CREATE TABLE wst_column_values (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
231 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
232 wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
233 position NUMERIC(9,5) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
234 w NUMERIC(9,5) NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
235
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
236 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
237 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
238 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
239
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
240 -- 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
241 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
242
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
243 CREATE TABLE wst_q_ranges (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
244 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
245 range_id NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
246 q NUMERIC(10,5) NOT NULL
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
247 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
248
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
249 -- 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
250 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
251
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
252 CREATE TABLE wst_column_q_ranges (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
253 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
254 wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
255 wst_q_range_id NUMERIC(9,0) 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
256
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
257 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
258 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
259
6300
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
260 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
261
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
262 CREATE TABLE official_lines (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
263 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
264 wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
265 named_main_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
6300
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
266
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
267 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
268 );
0711ce5ca701 Backend: Modified schema for a better model of official lines.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 6177
diff changeset
269
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
270 CREATE VIEW wst_value_table AS
8658
3531f0cee5e1 Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents: 8411
diff changeset
271 SELECT
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
272 wcv.position AS position,
8658
3531f0cee5e1 Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents: 8411
diff changeset
273 w,
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
274 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
275 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
276 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
277 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
278 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
279 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
280 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
281 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
282 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
283 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
284 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
285 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
286 JOIN wst_column_values wcv
6177
573112007ec7 Remove stray semicolon from sql schema.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 6143
diff changeset
287 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
288 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
289 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
290
754
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
291 -- 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
292 CREATE VIEW wst_w_values AS
8658
3531f0cee5e1 Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents: 8411
diff changeset
293 SELECT wcv."position" AS km,
3531f0cee5e1 Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents: 8411
diff changeset
294 wcv.w AS w,
3531f0cee5e1 Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents: 8411
diff changeset
295 wc."position" AS column_pos,
754
5bcf338eadb9 Added a new view to select ws of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 505
diff changeset
296 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
297 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
298 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
299 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
300 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
301
755
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
302 -- 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
303 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
304 SELECT wc.position AS column_pos,
8658
3531f0cee5e1 Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents: 8411
diff changeset
305 wqr.q AS q,
3531f0cee5e1 Whitespace-cleanup schema files.
"Tom Gottfried <tom@intevation.de>"
parents: 8411
diff changeset
306 r.a AS a,
755
a29fd0916803 Added a new view to select qs of a WST.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 754
diff changeset
307 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
308 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
309 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
310 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
311 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
312 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
313 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
314
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
315 -- data for the cross-sections
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
316
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
317 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
318
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
319 CREATE TABLE cross_sections (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
320 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
321 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
322 time_interval_id NUMERIC(9,0) 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
323 description VARCHAR(256)
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
324 );
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
325
1205
5f1506fc7636 Made import of cross sections work.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1203
diff changeset
326 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
327
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 CREATE TABLE cross_section_lines (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
329 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
330 km NUMERIC(9,5) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
331 cross_section_id NUMERIC(9,0) 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
332 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
333 );
3c01bef43a98 Querprofile: Added a table to map the points to a given km.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1202
diff changeset
334
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
335 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
336
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
337 CREATE TABLE cross_section_points (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
338 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
339 cross_section_line_id NUMERIC(9,0) NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
340 col_pos NUMERIC(9,0) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
341 x NUMERIC(7,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
342 y NUMERIC(7,2) NOT NULL
1192
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
343 );
5f8444df19e4 Add relations for cross sections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 763
diff changeset
344
3796
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
345 -- Indices for faster access of the points
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
346 CREATE INDEX cross_section_lines_km_idx
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
347 ON cross_section_lines(km);
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
348 CREATE INDEX cross_section_points_line_idx
61195d14b844 FLYS backend: Fixed postgresql scheme.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3470
diff changeset
349 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
350
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
351 -- Hydraulische Kenngroessen
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
352
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
353 CREATE SEQUENCE HYKS_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
354
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
355 CREATE TABLE hyks (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
356 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
357 river_id NUMERIC(9,0) 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
358 description VARCHAR(256) NOT NULL
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_ENTRIES_ID_SEQ;
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
362
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
363 --FIXME: make precision and scale of km column equal with the km columns of the other tables
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
364 CREATE TABLE hyk_entries (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
365 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
366 hyk_id NUMERIC(9,0) NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
367 km NUMERIC(7,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
368 measure TIMESTAMP(0),
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
369 UNIQUE (hyk_id, km)
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
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
372 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
373
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
374 CREATE TABLE hyk_formations (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
375 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
376 formation_num NUMERIC(9,0) NOT NULL DEFAULT 0,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
377 hyk_entry_id NUMERIC(9,0) NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
378 top NUMERIC(6,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
379 bottom NUMERIC(6,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
380 distance_vl NUMERIC(8,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
381 distance_hf NUMERIC(8,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
382 distance_vr NUMERIC(8,2) NOT NULL,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
383 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
384 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
385
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
386 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
387
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
388 CREATE TABLE hyk_flow_zone_types (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
389 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
390 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
391 description VARCHAR(256)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
392 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
393
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
394 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
395
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
396 CREATE TABLE hyk_flow_zones (
8943
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
397 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
398 formation_id NUMERIC(9,0) NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
399 type_id NUMERIC(9,0) NOT NULL REFERENCES hyk_flow_zone_types(id),
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
400 a NUMERIC(7,2) NOT NULL,
71b17f731762 Same data types, precision and scale as in Oracle (as far as possible)
mschaefer
parents: 8687
diff changeset
401 b NUMERIC(7,2) NOT NULL,
1209
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
402 CHECK (a <= b)
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
403 );
c12b5fbd33e8 schema: Added structures for HYKs "Hydraulische Kenngroessen"
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1205
diff changeset
404
4651
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
405 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
406 AS
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
407 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
408 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
409 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
410 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
411 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
412 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
413 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
414 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
415 wc.wst_id;
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
416
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
417 COMMIT;
http://dive4elements.wald.intevation.org