Mercurial > dive4elements > river

comparison backend/doc/schema/postgresql.sql @ 8965:f89fb9e9abad

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