Mercurial > dive4elements > river

comparison backend/doc/schema/oracle.sql @ 8658:3531f0cee5e1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Whitespace-cleanup schema files.
author "Tom Gottfried <tom@intevation.de>"
date Thu, 02 Apr 2015 17:17:51 +0200
parents b8c6cb36607e
children cfafe5764509
comparison
equal deleted inserted replaced
8657:4054c7fce43e 8658:3531f0cee5e1
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) NOT NULL UNIQUE, 6 name VARCHAR2(255) NOT NULL UNIQUE,
7 PRIMARY KEY (id) 7 PRIMARY KEY (id)
8 ); 8 );
9 9
10 10
20 type_id NUMBER(38,0), 20 type_id NUMBER(38,0),
21 PRIMARY KEY (id) 21 PRIMARY KEY (id)
22 ); 22 );
23 23
24 24
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) UNIQUE, 30 value VARCHAR2(255) UNIQUE,
31 primary key (id) 31 primary key (id)
32 ); 32 );
33 -- value can not be NOT NULL in Oracle: 33 -- value can not be NOT NULL in Oracle:
34 -- '' is needed here and silently converted to NULL in Oracle 34 -- '' is needed here and silently converted to NULL in Oracle
35 35
37 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; 37 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
38 38
39 CREATE TABLE cross_section_lines ( 39 CREATE TABLE cross_section_lines (
40 id NUMBER(38,0) NOT NULL, 40 id NUMBER(38,0) NOT NULL,
41 km NUMBER(38,5) NOT NULL, 41 km NUMBER(38,5) NOT NULL,
42 cross_section_id NUMBER(38,0) NOT NULL, 42 cross_section_id NUMBER(38,0) NOT NULL,
43 PRIMARY KEY (id), 43 PRIMARY KEY (id),
44 UNIQUE (km, cross_section_id) 44 UNIQUE (km, cross_section_id)
45 ); 45 );
46 46
47 47
118 CREATE SEQUENCE GAUGES_ID_SEQ; 118 CREATE SEQUENCE GAUGES_ID_SEQ;
119 119
120 CREATE TABLE gauges ( 120 CREATE TABLE gauges (
121 id NUMBER(38,0) NOT NULL, 121 id NUMBER(38,0) NOT NULL,
122 aeo NUMBER(38,2) NOT NULL, 122 aeo NUMBER(38,2) NOT NULL,
123 datum NUMBER(38,2) NOT NULL, 123 datum NUMBER(38,2) NOT NULL,
124 name VARCHAR2(255) NOT NULL, 124 name VARCHAR2(255) NOT NULL,
125 station NUMBER(38,4) NOT NULL, 125 station NUMBER(38,4) NOT NULL,
126 official_number NUMBER(38,0), 126 official_number NUMBER(38,0),
127 range_id NUMBER(38,0), 127 range_id NUMBER(38,0),
128 -- TODO: remove river id here because range_id references river already 128 -- TODO: remove river id here because range_id references river already
274 274
275 -- TIME_INTERVALS 275 -- TIME_INTERVALS
276 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; 276 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
277 277
278 CREATE TABLE time_intervals ( 278 CREATE TABLE time_intervals (
279 id NUMBER(38,0) NOT NULL, 279 id NUMBER(38,0) NOT NULL,
280 start_time TIMESTAMP NOT NULL, 280 start_time TIMESTAMP NOT NULL,
281 stop_time TIMESTAMP, 281 stop_time TIMESTAMP,
282 PRIMARY KEY (id), 282 PRIMARY KEY (id),
283 CHECK (start_time <= stop_time) 283 CHECK (start_time <= stop_time)
284 ); 284 );
427 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE; 427 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE;
428 428
429 -- VIEWS 429 -- VIEWS
430 430
431 CREATE VIEW wst_value_table AS 431 CREATE VIEW wst_value_table AS
432 SELECT 432 SELECT
433 wcv.position AS position, 433 wcv.position AS position,
434 w, 434 w,
435 q, 435 q,
436 wc.position AS column_pos, 436 wc.position AS column_pos,
437 w.id AS wst_id 437 w.id AS wst_id
438 FROM wsts w 438 FROM wsts w
439 JOIN wst_columns wc 439 JOIN wst_columns wc
449 ORDER BY wcv.position ASC, 449 ORDER BY wcv.position ASC,
450 wc.position DESC; 450 wc.position DESC;
451 451
452 -- view to select the w values of a WST 452 -- view to select the w values of a WST
453 CREATE VIEW wst_w_values AS 453 CREATE VIEW wst_w_values AS
454 SELECT wcv.position AS km, 454 SELECT wcv.position AS km,
455 wcv.w AS w, 455 wcv.w AS w,
456 wc.position AS column_pos, 456 wc.position AS column_pos,
457 w.id AS wst_id 457 w.id AS wst_id
458 FROM wst_column_values wcv 458 FROM wst_column_values wcv
459 JOIN wst_columns wc ON wcv.wst_column_id = wc.id 459 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
460 JOIN wsts w ON wc.wst_id = w.id 460 JOIN wsts w ON wc.wst_id = w.id
461 ORDER BY wcv.position, wc.position; 461 ORDER BY wcv.position, wc.position;
462 462
463 -- view to select the q values of a WST 463 -- view to select the q values of a WST
464 CREATE VIEW wst_q_values AS 464 CREATE VIEW wst_q_values AS
465 SELECT wc.position AS column_pos, 465 SELECT wc.position AS column_pos,
466 wqr.q AS q, 466 wqr.q AS q,
467 r.a AS a, 467 r.a AS a,
468 r.b AS b, 468 r.b AS b,
469 wc.wst_id AS wst_id 469 wc.wst_id AS wst_id
470 FROM wst_column_q_ranges wcqr 470 FROM wst_column_q_ranges wcqr
471 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id 471 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
472 JOIN ranges r ON wqr.range_id = r.id 472 JOIN ranges r ON wqr.range_id = r.id
http://dive4elements.wald.intevation.org