Mercurial > dive4elements > river
comparison backend/doc/schema/postgresql.sql @ 8943:71b17f731762
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 |