Mercurial > dive4elements > river

comparison backend/doc/schema/oracle.sql @ 7352:c6825778de1f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged double-precision branch.
author Sascha L. Teichmann <teichmann@intevation.de>
date Wed, 16 Oct 2013 13:03:13 +0200
parents 28748bb1b676
children 44a9233c62eb
comparison
equal deleted inserted replaced
7350:760ea7f08455 7352:c6825778de1f
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) NOT NULL UNIQUE, 30 value VARCHAR2(255) UNIQUE,
31 primary key (id) 31 primary key (id)
32 ); 32 );
33 33 -- value can not be NOT NULL in Oracle:
34 -- '' is needed here and silently converted to NULL in Oracle
34 35
35 -- CROSS_SECTION_LINES 36 -- CROSS_SECTION_LINES
36 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; 37 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
37 38
38 CREATE TABLE cross_section_lines ( 39 CREATE TABLE cross_section_lines (
39 id NUMBER(38,0) NOT NULL, 40 id NUMBER(38,0) NOT NULL,
40 km NUMBER(38,2) NOT NULL, 41 km NUMBER(38,5) NOT NULL,
41 cross_section_id NUMBER(38,0) NOT NULL, 42 cross_section_id NUMBER(38,0) NOT NULL,
42 PRIMARY KEY (id), 43 PRIMARY KEY (id),
43 UNIQUE (km, cross_section_id) 44 UNIQUE (km, cross_section_id)
44 ); 45 );
45 46
51 id NUMBER(38,0) NOT NULL, 52 id NUMBER(38,0) NOT NULL,
52 col_pos NUMBER(38,0) NOT NULL, 53 col_pos NUMBER(38,0) NOT NULL,
53 x NUMBER(38,2) NOT NULL, 54 x NUMBER(38,2) NOT NULL,
54 y NUMBER(38,2) NOT NULL, 55 y NUMBER(38,2) NOT NULL,
55 cross_section_line_id NUMBER(38,0) NOT NULL, 56 cross_section_line_id NUMBER(38,0) NOT NULL,
56 PRIMARY KEY (id), 57 PRIMARY KEY (id)
57 ); 58 );
58 59
59 60
60 -- CROSS_SECTIONS 61 -- CROSS_SECTIONS
61 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; 62 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
92 93
93 CREATE TABLE discharge_tables ( 94 CREATE TABLE discharge_tables (
94 id NUMBER(38,0) NOT NULL, 95 id NUMBER(38,0) NOT NULL,
95 description VARCHAR2(255) NOT NULL, 96 description VARCHAR2(255) NOT NULL,
96 bfg_id VARCHAR2(50), 97 bfg_id VARCHAR2(50),
97 kind NUMBER(38,0) NOT NULL DEFAULT 0, 98 kind NUMBER(38,0) DEFAULT 0 NOT NULL,
98 gauge_id NUMBER(38,0) NOT NULL, 99 gauge_id NUMBER(38,0) NOT NULL,
99 time_interval_id NUMBER(38,0), 100 time_interval_id NUMBER(38,0),
100 PRIMARY KEY (id), 101 PRIMARY KEY (id),
101 UNIQUE(gauge_id, bfg_id, kind) 102 UNIQUE(gauge_id, bfg_id, kind)
102 ); 103 );
119 CREATE TABLE gauges ( 120 CREATE TABLE gauges (
120 id NUMBER(38,0) NOT NULL, 121 id NUMBER(38,0) NOT NULL,
121 aeo NUMBER(38,2) NOT NULL, 122 aeo NUMBER(38,2) NOT NULL,
122 datum NUMBER(38,2) NOT NULL, 123 datum NUMBER(38,2) NOT NULL,
123 name VARCHAR2(255) NOT NULL, 124 name VARCHAR2(255) NOT NULL,
124 station NUMBER(38,2) NOT NULL, 125 station NUMBER(38,4) NOT NULL,
125 official_number NUMBER(38,0), 126 official_number NUMBER(38,0),
126 range_id NUMBER(38,0) NOT NULL, 127 range_id NUMBER(38,0),
127 -- TODO: remove river id here because range_id references river already 128 -- TODO: remove river id here because range_id references river already
128 river_id NUMBER(38,0) NOT NULL, 129 river_id NUMBER(38,0) NOT NULL,
129 PRIMARY KEY (id), 130 PRIMARY KEY (id),
130 UNIQUE (name, river_id), 131 UNIQUE (name, river_id),
131 UNIQUE (official_number, river_id), 132 UNIQUE (official_number, river_id),
213 id NUMBER(38,0) NOT NULL, 214 id NUMBER(38,0) NOT NULL,
214 value NUMBER(38,2) NOT NULL, 215 value NUMBER(38,2) NOT NULL,
215 gauge_id NUMBER(38,0) NOT NULL, 216 gauge_id NUMBER(38,0) NOT NULL,
216 named_value_id NUMBER(38,0) NOT NULL, 217 named_value_id NUMBER(38,0) NOT NULL,
217 time_interval_id NUMBER(38,0), 218 time_interval_id NUMBER(38,0),
218
219 -- TODO: better checks
220 UNIQUE (gauge_id, named_value_id, time_interval_id), 219 UNIQUE (gauge_id, named_value_id, time_interval_id),
221 PRIMARY KEY (id) 220 PRIMARY KEY (id)
222 ); 221 );
223 222
224 223
250 id NUMBER(38,0) NOT NULL, 249 id NUMBER(38,0) NOT NULL,
251 a NUMBER(38,10) NOT NULL, 250 a NUMBER(38,10) NOT NULL,
252 b NUMBER(38,10), 251 b NUMBER(38,10),
253 river_id NUMBER(38,0), 252 river_id NUMBER(38,0),
254 UNIQUE (river_id, a, b), 253 UNIQUE (river_id, a, b),
255 PRIMARY KEY (id) 254 PRIMARY KEY (id),
255 CHECK (a < b)
256 ); 256 );
257 257
258 258
259 259
260 -- RIVERS 260 -- RIVERS
261 CREATE SEQUENCE RIVERS_ID_SEQ; 261 CREATE SEQUENCE RIVERS_ID_SEQ;
262 262
263 CREATE TABLE rivers ( 263 CREATE TABLE rivers (
264 id NUMBER(38,0) NOT NULL, 264 id NUMBER(38,0) NOT NULL,
265 official_number NUMBER(38,0), 265 official_number NUMBER(38,0),
266 km_up NUMBER(38,0) NOT NULL DEFAULT 0, 266 km_up NUMBER(38,0) DEFAULT 0 NOT NULL,
267 name VARCHAR2(255) NOT NULL UNIQUE, 267 name VARCHAR2(255) NOT NULL UNIQUE,
268 wst_unit_id NUMBER(38,0) NOT NULL, 268 wst_unit_id NUMBER(38,0) NOT NULL,
269 PRIMARY KEY (id) 269 PRIMARY KEY (id)
270 ); 270 );
271 271
323 323
324 CREATE TABLE wst_columns ( 324 CREATE TABLE wst_columns (
325 id NUMBER(38,0) NOT NULL, 325 id NUMBER(38,0) NOT NULL,
326 description VARCHAR2(255), 326 description VARCHAR2(255),
327 name VARCHAR2(255) NOT NULL, 327 name VARCHAR2(255) NOT NULL,
328 position NUMBER(38,0) NOT NULL DEFAULT 0, 328 source VARCHAR(256),
329 position NUMBER(38,0) DEFAULT 0 NOT NULL,
329 time_interval_id NUMBER(38,0), 330 time_interval_id NUMBER(38,0),
330 wst_id NUMBER(38,0) NOT NULL, 331 wst_id NUMBER(38,0) NOT NULL,
331 UNIQUE (wst_id, name), 332 UNIQUE (wst_id, name),
332 UNIQUE (wst_id, position), 333 UNIQUE (wst_id, position),
333 PRIMARY KEY (id) 334 PRIMARY KEY (id)
414 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; 415 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
415 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 416 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
416 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE; 417 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
417 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE; 418 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
418 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 419 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
419 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON LETE CASCADE; 420 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE;
420 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; 421 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
421 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; 422 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
422 423
423 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 424 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
424 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE; 425 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE;
http://dive4elements.wald.intevation.org