Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql.sql @ 467:c8c09e31cdb8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added new column 'kind' in discharge tables and wst to distinguish between different types. flys-backend/trunk@1703 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 15 Apr 2011 10:31:15 +0000
parents 0f0b98ef9b04
children f4afea9b7537
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
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 -- Gewaesser
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
4 CREATE SEQUENCE RIVERS_ID_SEQ;
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
5
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 CREATE TABLE rivers (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
8 name VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 -- Bruecke, Haefen, etc.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
12 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
13
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14 CREATE TABLE attributes (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
16 value VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 -- 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
20 CREATE SEQUENCE RANGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
21
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 CREATE TABLE ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
24 river_id int NOT NULL REFERENCES rivers(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
25 a NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 b NUMERIC,
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27 UNIQUE (river_id, a, b)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 -- Lage 'links', 'rechts', etc.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
31 CREATE SEQUENCE POSITIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
32
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 CREATE TABLE positions (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
35 value VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 -- 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
39 -- plus its position.
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
40 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
41
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 CREATE TABLE annotations (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
44 range_id int NOT NULL REFERENCES ranges(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
45 attribute_id int NOT NULL REFERENCES attributes(id),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 position_id int REFERENCES positions(id)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 -- Pegel
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
50 CREATE SEQUENCE GAUGES_ID_SEQ;
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
51
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 CREATE TABLE gauges (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
53 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
54 name VARCHAR(256) NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
55 river_id int NOT NULL REFERENCES rivers(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
56 station NUMERIC NOT NULL UNIQUE,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
57 aeo NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 -- Pegelnullpunkt
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
60 datum NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 -- Streckengueltigkeit
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
62 range_id int NOT NULL REFERENCES ranges (id),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63
167
15d515fe15f5 Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 164
diff changeset
64 UNIQUE (name, river_id),
194
0f0b98ef9b04 Fixed wrong unique constraint.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 174
diff changeset
65 UNIQUE (river_id, station)
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 -- 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
69 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
70
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 CREATE TABLE main_value_types (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
72 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
73 name VARCHAR(256) NOT NULL UNIQUE
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
74 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
75
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
76 -- 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
77 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
78
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79 CREATE TABLE named_main_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
80 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
81 name VARCHAR(256) NOT NULL UNIQUE,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
82 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
83 UNIQUE (name, type_id)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86 -- Table for time intervals
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
87 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
88
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
89 CREATE TABLE time_intervals (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
90 id int PRIMARY KEY NOT NULL,
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
91 start_time TIMESTAMP NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92 stop_time TIMESTAMP,
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
93 CHECK (start_time <= stop_time)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
94 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
95
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96 -- Stammdaten
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
97 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
98
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
99 CREATE TABLE main_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
100 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
101 gauge_id int NOT NULL REFERENCES gauges(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
102 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
103 value NUMERIC NOT NULL,
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 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
106
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
107 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
108 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
109 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
111 -- Abflusstafeln
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
112 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
113
167
15d515fe15f5 Added POJOs to be mapped to schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 164
diff changeset
114 CREATE TABLE discharge_tables (
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
115 id int PRIMARY KEY NOT NULL,
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
116 gauge_id int NOT NULL REFERENCES gauges(id),
467
c8c09e31cdb8 Added new column 'kind' in discharge tables and wst to distinguish between different types.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 194
diff changeset
117 kind int NOT NULL DEFAULT 0
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 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
119
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
120 -- TODO: better checks
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
121 UNIQUE (gauge_id, time_interval_id)
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
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
124 -- Values of the Abflusstafeln
170
88c14d5d45be Added missing sequences. Deleted unsupported sqlite schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 168
diff changeset
125 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
126
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
127 CREATE TABLE discharge_table_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
128 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
129 table_id int NOT NULL REFERENCES discharge_tables(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
130 q NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
131 w NUMERIC NOT NULL,
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
132
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
133 UNIQUE (table_id, q, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
134 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
135
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
136 -- WST files
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
137 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
138
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
139 CREATE TABLE wsts (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
140 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
141 river_id int NOT NULL REFERENCES rivers(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
142 description VARCHAR(256) NOT NULL,
467
c8c09e31cdb8 Added new column 'kind' in discharge tables and wst to distinguish between different types.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 194
diff changeset
143 kind int NOT NULL DEFAULT 0
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
144 -- TODO: more meta infos
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
145 UNIQUE (river_id, description)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
146 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
147
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
148 -- columns of WST files
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
149 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
150
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
151 CREATE TABLE wst_columns (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
152 id int PRIMARY KEY NOT NULL,
173
8e2f2ddc507f Fixed wrong spelled column references in foreign keys introduces with last change.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 171
diff changeset
153 wst_id int NOT NULL REFERENCES wsts(id),
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
154 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
155 description VARCHAR(256),
164
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 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
158
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
159 UNIQUE (wst_id, name)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
160 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
161
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
162 -- 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
163 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
164
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
165 CREATE TABLE wst_column_values (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
166 id int PRIMARY KEY NOT NULL,
171
4a83e14f40f9 Added entity and id annotations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
167 wst_column_id int NOT NULL REFERENCES wst_columns(id),
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
168 position NUMERIC NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
169 w NUMERIC NOT NULL,
164
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 UNIQUE (position, wst_column_id),
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
172 UNIQUE (position, wst_column_id, w)
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
173 );
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
174
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
175 -- 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
176 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
177
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
178 CREATE TABLE wst_q_ranges (
168
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
179 id int PRIMARY KEY NOT NULL,
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
180 range_id int NOT NULL REFERENCES ranges(id),
86a1bd9cc50e More Hibernate/JPA stuff
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
181 q NUMERIC NOT NULL
164
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
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
184 -- 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
185 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
186
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
187 CREATE TABLE wst_column_q_ranges (
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
188 id int PRIMARY KEY NOT NULL,
173
8e2f2ddc507f Fixed wrong spelled column references in foreign keys introduces with last change.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 171
diff changeset
189 wst_column_id int NOT NULL REFERENCES wst_columns(id),
164
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
190 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id),
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
191
324e12ab7a05 Converted schema to be PostgreSQL compatible.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
192 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
193 );
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 COMMIT;
http://dive4elements.wald.intevation.org