Mercurial > dive4elements > river

comparison 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
comparison
equal deleted inserted replaced
8941:a9950a3a71e5 8943:71b17f731762
1 BEGIN; 1 BEGIN;
2 2
3 CREATE SEQUENCE UNITS_ID_SEQ; 3 CREATE SEQUENCE UNITS_ID_SEQ;
4 4
5 CREATE TABLE units ( 5 CREATE TABLE units (
6 id int PRIMARY KEY NOT NULL, 6 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
7 name VARCHAR(32) NOT NULL UNIQUE 7 name VARCHAR(32) NOT NULL UNIQUE
8 ); 8 );
9 9
10 -- SEDDB_NAME 10 -- SEDDB_NAME
11 -- Lookup table for optional matching with differing river names in SedDB 11 -- Lookup table for optional matching with differing river names in SedDB
12 -- Add name here and set rivers.seddb_name_id to id 12 -- Add name here and set rivers.seddb_name_id to id
13 CREATE TABLE seddb_name ( 13 CREATE TABLE seddb_name (
14 id int PRIMARY KEY NOT NULL, 14 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
15 name VARCHAR(256) NOT NULL 15 name VARCHAR(256) NOT NULL
16 ); 16 );
17 17
18 -- Gewaesser 18 -- Gewaesser
19 CREATE SEQUENCE RIVERS_ID_SEQ; 19 CREATE SEQUENCE RIVERS_ID_SEQ;
20 20
21 CREATE TABLE rivers ( 21 CREATE TABLE rivers (
22 id int PRIMARY KEY NOT NULL, 22 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
23 model_uuid CHAR(36) UNIQUE, 23 model_uuid CHAR(36) UNIQUE,
24 official_number int8, 24 official_number NUMERIC(12,0),
25 name VARCHAR(256) NOT NULL UNIQUE, 25 name VARCHAR(256) NOT NULL UNIQUE,
26 km_up int DEFAULT 0 NOT NULL, 26 km_up NUMERIC(1,0) DEFAULT 0 NOT NULL,
27 wst_unit_id int NOT NULL REFERENCES units(id), 27 wst_unit_id NUMERIC(9,0) NOT NULL REFERENCES units(id),
28 seddb_name_id int REFERENCES seddb_name(id), 28 seddb_name_id NUMERIC(9,0) REFERENCES seddb_name(id),
29 CHECK(km_up IN(0,1)) 29 CHECK(km_up IN(0,1))
30 ); 30 );
31 31
32 -- Bruecke, Haefen, etc. 32 -- Bruecke, Haefen, etc.
33 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; 33 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
34 34
35 CREATE TABLE attributes ( 35 CREATE TABLE attributes (
36 id int PRIMARY KEY NOT NULL, 36 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
37 value VARCHAR(256) NOT NULL UNIQUE 37 value VARCHAR(256) NOT NULL UNIQUE
38 ); 38 );
39 39
40 -- segments from/to at a river 40 -- segments from/to at a river
41 CREATE SEQUENCE RANGES_ID_SEQ; 41 CREATE SEQUENCE RANGES_ID_SEQ;
42 42
43 --FIXME: make precision and scale of a and b columns equal with the km columns of the other tables
43 CREATE TABLE ranges ( 44 CREATE TABLE ranges (
44 id int PRIMARY KEY NOT NULL, 45 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
45 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, 46 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
46 a NUMERIC NOT NULL, 47 a NUMERIC(14,10) NOT NULL,
47 b NUMERIC, 48 b NUMERIC(14,10),
48 UNIQUE (river_id, a, b), 49 UNIQUE (river_id, a, b),
49 CHECK (a < b) 50 CHECK (a < b)
50 ); 51 );
51 52
52 53
53 -- Lage 'links', 'rechts', etc. 54 -- Lage 'links', 'rechts', etc.
54 CREATE SEQUENCE POSITIONS_ID_SEQ; 55 CREATE SEQUENCE POSITIONS_ID_SEQ;
55 56
56 CREATE TABLE positions ( 57 CREATE TABLE positions (
57 id int PRIMARY KEY NOT NULL, 58 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
58 value VARCHAR(256) NOT NULL UNIQUE 59 value VARCHAR(256) NOT NULL UNIQUE
59 ); 60 );
60 61
61 -- Kante 'oben', 'unten' 62 -- Kante 'oben', 'unten'
62 CREATE SEQUENCE EDGES_ID_SEQ; 63 CREATE SEQUENCE EDGES_ID_SEQ;
63 64
64 CREATE TABLE edges ( 65 CREATE TABLE edges (
65 id int PRIMARY KEY NOT NULL, 66 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
66 top NUMERIC, 67 top NUMERIC(6,2),
67 bottom NUMERIC 68 bottom NUMERIC(6,2)
68 ); 69 );
69 70
70 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...) 71 -- Types of annotatations (Hafen, Bruecke, Zufluss, ...)
71 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; 72 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
72 73
73 CREATE TABLE annotation_types ( 74 CREATE TABLE annotation_types (
74 id int PRIMARY KEY NOT NULL, 75 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
75 name VARCHAR(256) NOT NULL UNIQUE 76 name VARCHAR(256) NOT NULL UNIQUE
76 ); 77 );
77 78
78 -- Some object (eg. Hafen) at a segment of river 79 -- Some object (eg. Hafen) at a segment of river
79 -- plus its position. 80 -- plus its position.
80 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; 81 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
81 82
82 CREATE TABLE annotations ( 83 CREATE TABLE annotations (
83 id int PRIMARY KEY NOT NULL, 84 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
84 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, 85 range_id NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
85 attribute_id int NOT NULL REFERENCES attributes(id), 86 attribute_id NUMERIC(9,0) NOT NULL REFERENCES attributes(id),
86 position_id int REFERENCES positions(id), 87 position_id NUMERIC(9,0) REFERENCES positions(id),
87 edge_id int REFERENCES edges(id), 88 edge_id NUMERIC(9,0) REFERENCES edges(id),
88 type_id int REFERENCES annotation_types(id) 89 type_id NUMERIC(9,0) REFERENCES annotation_types(id)
89 ); 90 );
90 91
91 -- Pegel 92 -- Pegel
92 CREATE SEQUENCE GAUGES_ID_SEQ; 93 CREATE SEQUENCE GAUGES_ID_SEQ;
93 94
95 --FIXME: make precision and scale of station column equal with the km columns of the other tables
94 CREATE TABLE gauges ( 96 CREATE TABLE gauges (
95 id int PRIMARY KEY NOT NULL, 97 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
96 name VARCHAR(256) NOT NULL, 98 name VARCHAR(256) NOT NULL,
97 -- remove river id here because range_id references river already 99 -- remove river id here because range_id references river already
98 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, 100 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
99 station NUMERIC NOT NULL, 101 station NUMERIC(8,4) NOT NULL,
100 aeo NUMERIC NOT NULL, 102 aeo NUMERIC(9,2) NOT NULL,
101 official_number int8, 103 official_number NUMERIC(12,0),
102 104
103 -- Pegelnullpunkt 105 -- Pegelnullpunkt
104 datum NUMERIC NOT NULL, 106 datum NUMERIC(6,2) NOT NULL,
105 -- Streckengueltigkeit 107 -- Streckengueltigkeit
106 range_id int REFERENCES ranges (id) ON DELETE CASCADE, 108 range_id NUMERIC(9,0) REFERENCES ranges (id) ON DELETE CASCADE,
107 109
108 UNIQUE (name, river_id), 110 UNIQUE (name, river_id),
109 UNIQUE (official_number, river_id), 111 UNIQUE (official_number, river_id),
110 UNIQUE (river_id, station) 112 UNIQUE (river_id, station)
111 ); 113 );
112 114
113 -- Type of a Hauptwert 'W', 'Q', 'D', etc. 115 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
114 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; 116 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
115 117
116 CREATE TABLE main_value_types ( 118 CREATE TABLE main_value_types (
117 id int PRIMARY KEY NOT NULL, 119 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
118 name VARCHAR(256) NOT NULL UNIQUE 120 name VARCHAR(256) NOT NULL UNIQUE
119 ); 121 );
120 122
121 -- Named type of a Hauptwert (eg. HQ100) 123 -- Named type of a Hauptwert (eg. HQ100)
122 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; 124 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
123 125
124 CREATE TABLE named_main_values ( 126 CREATE TABLE named_main_values (
125 id int PRIMARY KEY NOT NULL, 127 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
126 name VARCHAR(256) NOT NULL, 128 name VARCHAR(256) NOT NULL,
127 type_id int NOT NULL REFERENCES main_value_types(id) 129 type_id NUMERIC(9,0) NOT NULL REFERENCES main_value_types(id)
128 ); 130 );
129 131
130 -- Table for time intervals 132 -- Table for time intervals
131 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; 133 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
132 134
133 CREATE TABLE time_intervals ( 135 CREATE TABLE time_intervals (
134 id int PRIMARY KEY NOT NULL, 136 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
135 start_time TIMESTAMP NOT NULL, 137 start_time TIMESTAMP(0) NOT NULL,
136 stop_time TIMESTAMP, 138 stop_time TIMESTAMP(0),
137 CHECK (start_time <= stop_time) 139 CHECK (start_time <= stop_time)
138 ); 140 );
139 141
140 142
141 -- Stammdaten 143 -- Stammdaten
142 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; 144 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
143 145
144 CREATE TABLE main_values ( 146 CREATE TABLE main_values (
145 id int PRIMARY KEY NOT NULL, 147 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
146 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, 148 gauge_id NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
147 named_value_id int NOT NULL REFERENCES named_main_values(id), 149 named_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id),
148 value NUMERIC NOT NULL, 150 value NUMERIC(12,2) NOT NULL,
149 151
150 time_interval_id int REFERENCES time_intervals(id), 152 time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
151 153
152 -- TODO: better checks 154 -- TODO: better checks
153 UNIQUE (gauge_id, named_value_id, time_interval_id) 155 UNIQUE (gauge_id, named_value_id, time_interval_id)
154 ); 156 );
155 157
156 -- Abflusstafeln 158 -- Abflusstafeln
157 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; 159 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
158 160
159 CREATE TABLE discharge_tables ( 161 CREATE TABLE discharge_tables (
160 id int PRIMARY KEY NOT NULL, 162 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
161 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE, 163 gauge_id NUMERIC(9,0) NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
162 description VARCHAR(256) NOT NULL, 164 description VARCHAR(256) NOT NULL,
163 bfg_id VARCHAR(50), 165 bfg_id VARCHAR(50),
164 kind int NOT NULL DEFAULT 0, 166 kind NUMERIC(9,0) NOT NULL DEFAULT 0,
165 time_interval_id int REFERENCES time_intervals(id), 167 time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
166 UNIQUE(gauge_id, bfg_id, kind) 168 UNIQUE(gauge_id, bfg_id, kind)
167 ); 169 );
168 170
169 -- Values of the Abflusstafeln 171 -- Values of the Abflusstafeln
170 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; 172 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
171 173
172 CREATE TABLE discharge_table_values ( 174 CREATE TABLE discharge_table_values (
173 id int PRIMARY KEY NOT NULL, 175 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
174 table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE, 176 table_id NUMERIC(9,0) NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
175 q NUMERIC NOT NULL, 177 q NUMERIC(9,4) NOT NULL,
176 w NUMERIC NOT NULL, 178 w NUMERIC(6,2) NOT NULL,
177 179
178 UNIQUE (table_id, q, w) 180 UNIQUE (table_id, q, w)
179 ); 181 );
180 182
181 -- WST files 183 -- WST files
182 --lookup table for wst kinds 184 --lookup table for wst kinds
183 CREATE TABLE wst_kinds ( 185 CREATE TABLE wst_kinds (
184 id int PRIMARY KEY NOT NULL, 186 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
185 kind VARCHAR(64) NOT NULL 187 kind VARCHAR(64) NOT NULL
186 ); 188 );
187 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); 189 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
188 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks'); 190 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
189 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst'); 191 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
195 197
196 198
197 CREATE SEQUENCE WSTS_ID_SEQ; 199 CREATE SEQUENCE WSTS_ID_SEQ;
198 200
199 CREATE TABLE wsts ( 201 CREATE TABLE wsts (
200 id int PRIMARY KEY NOT NULL, 202 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
201 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, 203 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
202 description VARCHAR(256) NOT NULL, 204 description VARCHAR(256) NOT NULL,
203 kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0, 205 kind NUMERIC(9,0) NOT NULL REFERENCES wst_kinds(id) DEFAULT 0,
204 -- TODO: more meta infos 206 -- TODO: more meta infos
205 UNIQUE (river_id, description) 207 UNIQUE (river_id, description)
206 ); 208 );
207 209
208 -- columns of WST files 210 -- columns of WST files
209 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; 211 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
210 212
211 CREATE TABLE wst_columns ( 213 CREATE TABLE wst_columns (
212 id int PRIMARY KEY NOT NULL, 214 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
213 wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE, 215 wst_id NUMERIC(9,0) NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
214 name VARCHAR(256) NOT NULL, 216 name VARCHAR(256) NOT NULL,
215 description VARCHAR(256), 217 description VARCHAR(256),
216 source VARCHAR(256), 218 source VARCHAR(256),
217 position int NOT NULL DEFAULT 0, 219 position NUMERIC(9,0) NOT NULL DEFAULT 0,
218 220
219 time_interval_id int REFERENCES time_intervals(id), 221 time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
220 222
221 UNIQUE (wst_id, name), 223 UNIQUE (wst_id, name),
222 UNIQUE (wst_id, position) 224 UNIQUE (wst_id, position)
223 ); 225 );
224 226
225 -- w values in WST file column 227 -- w values in WST file column
226 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; 228 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
227 229
228 CREATE TABLE wst_column_values ( 230 CREATE TABLE wst_column_values (
229 id int PRIMARY KEY NOT NULL, 231 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
230 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, 232 wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
231 position NUMERIC NOT NULL, 233 position NUMERIC(9,5) NOT NULL,
232 w NUMERIC NOT NULL, 234 w NUMERIC(9,5) NOT NULL,
233 235
234 UNIQUE (position, wst_column_id), 236 UNIQUE (position, wst_column_id),
235 UNIQUE (position, wst_column_id, w) 237 UNIQUE (position, wst_column_id, w)
236 ); 238 );
237 239
238 -- bind q values to range 240 -- bind q values to range
239 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; 241 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
240 242
241 CREATE TABLE wst_q_ranges ( 243 CREATE TABLE wst_q_ranges (
242 id int PRIMARY KEY NOT NULL, 244 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
243 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE, 245 range_id NUMERIC(9,0) NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
244 q NUMERIC NOT NULL 246 q NUMERIC(10,5) NOT NULL
245 ); 247 );
246 248
247 -- bind q ranges to wst columns 249 -- bind q ranges to wst columns
248 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; 250 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
249 251
250 CREATE TABLE wst_column_q_ranges ( 252 CREATE TABLE wst_column_q_ranges (
251 id int PRIMARY KEY NOT NULL, 253 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
252 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, 254 wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
253 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE, 255 wst_q_range_id NUMERIC(9,0) NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
254 256
255 UNIQUE (wst_column_id, wst_q_range_id) 257 UNIQUE (wst_column_id, wst_q_range_id)
256 ); 258 );
257 259
258 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ; 260 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;
259 261
260 CREATE TABLE official_lines ( 262 CREATE TABLE official_lines (
261 id int PRIMARY KEY NOT NULL, 263 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
262 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE, 264 wst_column_id NUMERIC(9,0) NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
263 named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE, 265 named_main_value_id NUMERIC(9,0) NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
264 266
265 UNIQUE (wst_column_id, named_main_value_id) 267 UNIQUE (wst_column_id, named_main_value_id)
266 ); 268 );
267 269
268 CREATE VIEW wst_value_table AS 270 CREATE VIEW wst_value_table AS
313 -- data for the cross-sections 315 -- data for the cross-sections
314 316
315 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; 317 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
316 318
317 CREATE TABLE cross_sections ( 319 CREATE TABLE cross_sections (
318 id int PRIMARY KEY NOT NULL, 320 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
319 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, 321 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
320 time_interval_id int REFERENCES time_intervals(id), 322 time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
321 description VARCHAR(256) 323 description VARCHAR(256)
322 ); 324 );
323 325
324 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; 326 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
325 327
326 CREATE TABLE cross_section_lines ( 328 CREATE TABLE cross_section_lines (
327 id int PRIMARY KEY NOT NULL, 329 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
328 km NUMERIC NOT NULL, 330 km NUMERIC(9,5) NOT NULL,
329 cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE, 331 cross_section_id NUMERIC(9,0) NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
330 UNIQUE (km, cross_section_id) 332 UNIQUE (km, cross_section_id)
331 ); 333 );
332 334
333 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; 335 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
334 336
335 CREATE TABLE cross_section_points ( 337 CREATE TABLE cross_section_points (
336 id int PRIMARY KEY NOT NULL, 338 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
337 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE, 339 cross_section_line_id NUMERIC(9,0) NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
338 col_pos int NOT NULL, 340 col_pos NUMERIC(9,0) NOT NULL,
339 x NUMERIC NOT NULL, 341 x NUMERIC(7,2) NOT NULL,
340 y NUMERIC NOT NULL 342 y NUMERIC(7,2) NOT NULL
341 ); 343 );
342 344
343 -- Indices for faster access of the points 345 -- Indices for faster access of the points
344 CREATE INDEX cross_section_lines_km_idx 346 CREATE INDEX cross_section_lines_km_idx
345 ON cross_section_lines(km); 347 ON cross_section_lines(km);
349 -- Hydraulische Kenngroessen 351 -- Hydraulische Kenngroessen
350 352
351 CREATE SEQUENCE HYKS_ID_SEQ; 353 CREATE SEQUENCE HYKS_ID_SEQ;
352 354
353 CREATE TABLE hyks ( 355 CREATE TABLE hyks (
354 id int PRIMARY KEY NOT NULL, 356 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
355 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, 357 river_id NUMERIC(9,0) NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
356 description VARCHAR(256) NOT NULL 358 description VARCHAR(256) NOT NULL
357 ); 359 );
358 360
359 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; 361 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
360 362
363 --FIXME: make precision and scale of km column equal with the km columns of the other tables
361 CREATE TABLE hyk_entries ( 364 CREATE TABLE hyk_entries (
362 id int PRIMARY KEY NOT NULL, 365 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
363 hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE, 366 hyk_id NUMERIC(9,0) NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
364 km NUMERIC NOT NULL, 367 km NUMERIC(7,2) NOT NULL,
365 measure TIMESTAMP, 368 measure TIMESTAMP(0),
366 UNIQUE (hyk_id, km) 369 UNIQUE (hyk_id, km)
367 ); 370 );
368 371
369 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; 372 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
370 373
371 CREATE TABLE hyk_formations ( 374 CREATE TABLE hyk_formations (
372 id int PRIMARY KEY NOT NULL, 375 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
373 formation_num int NOT NULL DEFAULT 0, 376 formation_num NUMERIC(9,0) NOT NULL DEFAULT 0,
374 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE, 377 hyk_entry_id NUMERIC(9,0) NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
375 top NUMERIC NOT NULL, 378 top NUMERIC(6,2) NOT NULL,
376 bottom NUMERIC NOT NULL, 379 bottom NUMERIC(6,2) NOT NULL,
377 distance_vl NUMERIC NOT NULL, 380 distance_vl NUMERIC(8,2) NOT NULL,
378 distance_hf NUMERIC NOT NULL, 381 distance_hf NUMERIC(8,2) NOT NULL,
379 distance_vr NUMERIC NOT NULL, 382 distance_vr NUMERIC(8,2) NOT NULL,
380 UNIQUE (hyk_entry_id, formation_num) 383 UNIQUE (hyk_entry_id, formation_num)
381 ); 384 );
382 385
383 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; 386 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
384 387
385 CREATE TABLE hyk_flow_zone_types ( 388 CREATE TABLE hyk_flow_zone_types (
386 id int PRIMARY KEY NOT NULL, 389 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
387 name VARCHAR(50) NOT NULL UNIQUE, 390 name VARCHAR(50) NOT NULL UNIQUE,
388 description VARCHAR(256) 391 description VARCHAR(256)
389 ); 392 );
390 393
391 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; 394 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
392 395
393 CREATE TABLE hyk_flow_zones ( 396 CREATE TABLE hyk_flow_zones (
394 id int PRIMARY KEY NOT NULL, 397 id NUMERIC(9,0) PRIMARY KEY NOT NULL,
395 formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE, 398 formation_id NUMERIC(9,0) NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
396 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), 399 type_id NUMERIC(9,0) NOT NULL REFERENCES hyk_flow_zone_types(id),
397 a NUMERIC NOT NULL, 400 a NUMERIC(7,2) NOT NULL,
398 b NUMERIC NOT NULL, 401 b NUMERIC(7,2) NOT NULL,
399 CHECK (a <= b) 402 CHECK (a <= b)
400 ); 403 );
401 404
402 CREATE VIEW wst_ranges 405 CREATE VIEW wst_ranges
403 AS 406 AS
http://dive4elements.wald.intevation.org