Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle.sql @ 5783:153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 22 Apr 2013 13:00:13 +0200 |
parents | 88cbe798cbab |
children |
comparison
equal
deleted
inserted
replaced
5782:5ad389bc20f0 | 5783:153456f84602 |
---|---|
1 -- ANNOTATION_TYPES | 1 -- ANNOTATION_TYPES |
2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; | 2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; |
3 | 3 |
4 CREATE TABLE annotation_types ( | 4 CREATE TABLE annotation_types ( |
5 id NUMBER(38,0) NOT NULL, | 5 id NUMBER(38,0) NOT NULL, |
6 name VARCHAR2(255), | 6 name VARCHAR2(255) NOT NULL UNIQUE, |
7 PRIMARY KEY (id) | 7 PRIMARY KEY (id) |
8 ); | 8 ); |
9 | 9 |
10 | 10 |
11 -- ANNOTATIONS | 11 -- ANNOTATIONS |
12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; | 12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; |
13 | 13 |
14 CREATE TABLE annotations ( | 14 CREATE TABLE annotations ( |
15 id NUMBER(38,0) NOT NULL, | 15 id NUMBER(38,0) NOT NULL, |
16 attribute_id NUMBER(38,0), | 16 attribute_id NUMBER(38,0) NOT NULL, |
17 edge_id NUMBER(38,0), | 17 edge_id NUMBER(38,0), |
18 position_id NUMBER(38,0), | 18 position_id NUMBER(38,0), |
19 range_id NUMBER(38,0), | 19 range_id NUMBER(38,0), |
20 type_id NUMBER(38,0), | 20 type_id NUMBER(38,0), |
21 PRIMARY KEY (id) | 21 PRIMARY KEY (id) |
25 -- ATTRIBUTES | 25 -- ATTRIBUTES |
26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; | 26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; |
27 | 27 |
28 CREATE TABLE attributes ( | 28 CREATE TABLE attributes ( |
29 id NUMBER(38,0) NOT NULL, | 29 id NUMBER(38,0) NOT NULL, |
30 value VARCHAR2(255), | 30 value VARCHAR2(255) NOT NULL UNIQUE, |
31 primary key (id) | 31 primary key (id) |
32 ); | 32 ); |
33 | 33 |
34 | 34 |
35 -- CROSS_SECTION_LINES | 35 -- CROSS_SECTION_LINES |
76 -- DISCHARGE_TABLE_VALUES | 76 -- DISCHARGE_TABLE_VALUES |
77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; | 77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; |
78 | 78 |
79 CREATE TABLE discharge_table_values ( | 79 CREATE TABLE discharge_table_values ( |
80 id NUMBER(38,0) NOT NULL, | 80 id NUMBER(38,0) NOT NULL, |
81 q NUMBER(38,2), | 81 q NUMBER(38,2) NOT NULL, |
82 w NUMBER(38,2), | 82 w NUMBER(38,2) NOT NULL, |
83 table_id NUMBER(38,0), | 83 table_id NUMBER(38,0) NOT NULL, |
84 UNIQUE (table_id, q, w), | |
84 PRIMARY KEY (id) | 85 PRIMARY KEY (id) |
85 ); | 86 ); |
86 | 87 |
87 | 88 |
88 -- DISCHARGE_TABLES | 89 -- DISCHARGE_TABLES |
89 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; | 90 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; |
90 | 91 |
91 CREATE TABLE discharge_tables ( | 92 CREATE TABLE discharge_tables ( |
92 id NUMBER(38,0) NOT NULL, | 93 id NUMBER(38,0) NOT NULL, |
93 description VARCHAR2(255), | 94 description VARCHAR2(255) NOT NULL, |
94 bfg_id VARCHAR2(50), | 95 bfg_id VARCHAR2(50), |
95 kind NUMBER(38,0), | 96 kind NUMBER(38,0) NOT NULL DEFAULT 0, |
96 gauge_id NUMBER(38,0), | 97 gauge_id NUMBER(38,0) NOT NULL, |
97 time_interval_id NUMBER(38,0), | 98 time_interval_id NUMBER(38,0), |
98 PRIMARY KEY (id) | 99 PRIMARY KEY (id) |
99 ); | 100 ); |
100 | 101 |
101 | 102 |
120 name VARCHAR2(255) NOT NULL, | 121 name VARCHAR2(255) NOT NULL, |
121 station NUMBER(38,2) NOT NULL, | 122 station NUMBER(38,2) NOT NULL, |
122 official_number NUMBER(38,0) UNIQUE, | 123 official_number NUMBER(38,0) UNIQUE, |
123 range_id NUMBER(38,0) NOT NULL, | 124 range_id NUMBER(38,0) NOT NULL, |
124 -- remove river id here because range_id references river already | 125 -- remove river id here because range_id references river already |
125 river_id NUMBER(38,0), | 126 river_id NUMBER(38,0) NOT NULL, |
126 PRIMARY KEY (id), | 127 PRIMARY KEY (id), |
127 UNIQUE (name, river_id), | 128 UNIQUE (name, river_id), |
128 UNIQUE (river_id, station) | 129 UNIQUE (river_id, station) |
129 ); | 130 ); |
130 | 131 |
195 -- MAIN_VALUE_TYPES | 196 -- MAIN_VALUE_TYPES |
196 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; | 197 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; |
197 | 198 |
198 CREATE TABLE main_value_types ( | 199 CREATE TABLE main_value_types ( |
199 id NUMBER(38,0) NOT NULL, | 200 id NUMBER(38,0) NOT NULL, |
200 name VARCHAR2(255), | 201 name VARCHAR2(255) NOT NULL UNIQUE, |
201 PRIMARY KEY (id) | 202 PRIMARY KEY (id) |
202 ); | 203 ); |
203 | 204 |
204 | 205 |
205 -- MAIN_VALUES | 206 -- MAIN_VALUES |
206 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; | 207 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; |
207 | 208 |
208 CREATE TABLE main_values ( | 209 CREATE TABLE main_values ( |
209 id NUMBER(38,0) NOT NULL, | 210 id NUMBER(38,0) NOT NULL, |
210 value NUMBER(38,2), | 211 value NUMBER(38,2) NOT NULL, |
211 gauge_id NUMBER(38,0), | 212 gauge_id NUMBER(38,0) NOT NULL, |
212 named_value_id NUMBER(38,0), | 213 named_value_id NUMBER(38,0) NOT NULL, |
213 time_interval_id NUMBER(38,0), | 214 time_interval_id NUMBER(38,0), |
215 | |
216 -- TODO: better checks | |
217 UNIQUE (gauge_id, named_value_id, time_interval_id), | |
214 PRIMARY KEY (id) | 218 PRIMARY KEY (id) |
215 ); | 219 ); |
216 | 220 |
217 | 221 |
218 -- NAMED_MAIN_VALUES | 222 -- NAMED_MAIN_VALUES |
219 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; | 223 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; |
220 | 224 |
221 CREATE TABLE named_main_values ( | 225 CREATE TABLE named_main_values ( |
222 id NUMBER(38,0) NOT NULL, | 226 id NUMBER(38,0) NOT NULL, |
223 name VARCHAR2(256) NOT NULL UNIQUE, | 227 name VARCHAR2(256) NOT NULL UNIQUE, |
224 type_id NUMBER(38,0), | 228 type_id NUMBER(38,0) NOT NULL, |
225 PRIMARY KEY (id) | 229 PRIMARY KEY (id) |
226 ); | 230 ); |
227 | 231 |
228 | 232 |
229 -- POSITIONS | 233 -- POSITIONS |
230 CREATE SEQUENCE POSITIONS_ID_SEQ; | 234 CREATE SEQUENCE POSITIONS_ID_SEQ; |
231 | 235 |
232 CREATE TABLE positions ( | 236 CREATE TABLE positions ( |
233 id NUMBER(38,0) NOT NULL, | 237 id NUMBER(38,0) NOT NULL, |
234 value VARCHAR2(255 char), | 238 value VARCHAR2(255 char) NOT NULL UNIQUE, |
235 PRIMARY KEY (id) | 239 PRIMARY KEY (id) |
236 ); | 240 ); |
237 | 241 |
238 | 242 |
239 --- RANGES | 243 --- RANGES |
242 CREATE TABLE ranges ( | 246 CREATE TABLE ranges ( |
243 id NUMBER(38,0) NOT NULL, | 247 id NUMBER(38,0) NOT NULL, |
244 a NUMBER(38,10) NOT NULL, | 248 a NUMBER(38,10) NOT NULL, |
245 b NUMBER(38,10), | 249 b NUMBER(38,10), |
246 river_id NUMBER(38,0), | 250 river_id NUMBER(38,0), |
247 PRIMARY KEY (id) | 251 UNIQUE (river_id, a, b), |
252 PRIMARY KEY (id) | |
248 ); | 253 ); |
249 | 254 |
250 | 255 |
251 | 256 |
252 -- RIVERS | 257 -- RIVERS |
253 CREATE SEQUENCE RIVERS_ID_SEQ; | 258 CREATE SEQUENCE RIVERS_ID_SEQ; |
254 | 259 |
255 CREATE TABLE rivers ( | 260 CREATE TABLE rivers ( |
256 id NUMBER(38,0) NOT NULL, | 261 id NUMBER(38,0) NOT NULL, |
257 official_number NUMBER(38,0), | 262 official_number NUMBER(38,0) UNIQUE, |
258 km_up NUMBER(38,0), | 263 km_up NUMBER(38,0) NOT NULL DEFAULT 0, |
259 name VARCHAR2(255), | 264 name VARCHAR2(255) NOT NULL UNIQUE, |
260 wst_unit_id NUMBER(38,0), | 265 wst_unit_id NUMBER(38,0) NOT NULL, |
261 PRIMARY KEY (id) | 266 PRIMARY KEY (id) |
262 ); | 267 ); |
263 | 268 |
264 | 269 |
265 -- TIME_INTERVALS | 270 -- TIME_INTERVALS |
277 --- UNITS | 282 --- UNITS |
278 CREATE SEQUENCE UNITS_ID_SEQ; | 283 CREATE SEQUENCE UNITS_ID_SEQ; |
279 | 284 |
280 CREATE TABLE units ( | 285 CREATE TABLE units ( |
281 id NUMBER(38,0) NOT NULL, | 286 id NUMBER(38,0) NOT NULL, |
282 name VARCHAR2(255), | 287 name VARCHAR2(255) NOT NULL UNIQUE, |
283 PRIMARY KEY (id) | 288 PRIMARY KEY (id) |
284 ); | 289 ); |
285 | 290 |
286 | 291 |
287 -- WST_COLUMN_Q_RANGES | 292 -- WST_COLUMN_Q_RANGES |
288 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; | 293 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; |
289 | 294 |
290 CREATE TABLE wst_column_q_ranges ( | 295 CREATE TABLE wst_column_q_ranges ( |
291 id NUMBER(38,0) NOT NULL, | 296 id NUMBER(38,0) NOT NULL, |
292 wst_column_id NUMBER(38,0), | 297 wst_column_id NUMBER(38,0) NOT NULL, |
293 wst_q_range_id NUMBER(38,0), | 298 wst_q_range_id NUMBER(38,0) NOT NULL, |
299 UNIQUE (wst_column_id, wst_q_range_id), | |
294 PRIMARY KEY (id) | 300 PRIMARY KEY (id) |
295 ); | 301 ); |
296 | 302 |
297 | 303 |
298 -- WST_COLUMN_VALUES | 304 -- WST_COLUMN_VALUES |
299 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; | 305 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; |
300 | 306 |
301 CREATE TABLE wst_column_values ( | 307 CREATE TABLE wst_column_values ( |
302 id NUMBER(38,0) NOT NULL, | 308 id NUMBER(38,0) NOT NULL, |
303 position NUMBER(38,5), | 309 position NUMBER(38,5) NOT NULL, |
304 w NUMBER(38,5), | 310 w NUMBER(38,5) NOT NULL, |
305 wst_column_id NUMBER(38,0), | 311 wst_column_id NUMBER(38,0) NOT NULL, |
312 UNIQUE (position, wst_column_id), | |
313 UNIQUE (position, wst_column_id, w), | |
306 PRIMARY KEY (id) | 314 PRIMARY KEY (id) |
307 ); | 315 ); |
308 | 316 |
309 | 317 |
310 -- WST_COLUMNS | 318 -- WST_COLUMNS |
311 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; | 319 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; |
312 | 320 |
313 CREATE TABLE wst_columns ( | 321 CREATE TABLE wst_columns ( |
314 id NUMBER(38,0) NOT NULL, | 322 id NUMBER(38,0) NOT NULL, |
315 description VARCHAR2(255), | 323 description VARCHAR2(255), |
316 name VARCHAR2(255), | 324 name VARCHAR2(255) NOT NULL, |
317 position NUMBER(38,0), | 325 position NUMBER(38,0) NOT NULL DEFAULT 0, |
318 time_interval_id NUMBER(38,0), | 326 time_interval_id NUMBER(38,0), |
319 wst_id NUMBER(38,0), | 327 wst_id NUMBER(38,0) NOT NULL, |
328 UNIQUE (wst_id, name), | |
329 UNIQUE (wst_id, position), | |
320 PRIMARY KEY (id) | 330 PRIMARY KEY (id) |
321 ); | 331 ); |
322 | 332 |
323 | 333 |
324 -- WST_Q_RANGES | 334 -- WST_Q_RANGES |
325 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; | 335 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
326 | 336 |
327 CREATE TABLE wst_q_ranges ( | 337 CREATE TABLE wst_q_ranges ( |
328 id NUMBER(38,0) NOT NULL, | 338 id NUMBER(38,0) NOT NULL, |
329 q NUMBER(38,5), | 339 q NUMBER(38,5) NOT NULL, |
330 range_id NUMBER(38,0), | 340 range_id NUMBER(38,0) NOT NULL, |
331 PRIMARY KEY (id) | 341 PRIMARY KEY (id) |
332 ); | 342 ); |
333 | 343 |
334 | 344 |
335 -- WSTS | 345 -- WSTS |
350 | 360 |
351 CREATE SEQUENCE WSTS_ID_SEQ; | 361 CREATE SEQUENCE WSTS_ID_SEQ; |
352 | 362 |
353 CREATE TABLE wsts ( | 363 CREATE TABLE wsts ( |
354 id NUMBER(38,0) NOT NULL, | 364 id NUMBER(38,0) NOT NULL, |
355 description VARCHAR2(255), | 365 description VARCHAR2(255) NOT NULL, |
356 kind NUMBER(38,0), | 366 kind NUMBER(38,0) NOT NULL, |
357 river_id NUMBER(38,0), | 367 river_id NUMBER(38,0) NOT NULL, |
368 UNIQUE (river_id, description), | |
358 PRIMARY KEY (id) | 369 PRIMARY KEY (id) |
359 ); | 370 ); |
360 | 371 |
361 | 372 |
362 -- ADD CONSTRAINTs | 373 -- ADD CONSTRAINTs |