Mercurial > dive4elements > river

comparison backend/doc/schema/oracle.sql @ 6632:97145ec4e33c double-precision

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema
author Tom Gottfried <tom@intevation.de>
date Mon, 22 Jul 2013 18:06:23 +0200
parents 3b93f439e954
children b1fa193fa5db
comparison
equal deleted inserted replaced
6631:9aa1c946b87d 6632:97145ec4e33c
51 id NUMBER(38,0) NOT NULL, 51 id NUMBER(38,0) NOT NULL,
52 col_pos NUMBER(38,0) NOT NULL, 52 col_pos NUMBER(38,0) NOT NULL,
53 x NUMBER(38,2) NOT NULL, 53 x NUMBER(38,2) NOT NULL,
54 y NUMBER(38,2) NOT NULL, 54 y NUMBER(38,2) NOT NULL,
55 cross_section_line_id NUMBER(38,0) NOT NULL, 55 cross_section_line_id NUMBER(38,0) NOT NULL,
56 PRIMARY KEY (id), 56 PRIMARY KEY (id)
57 ); 57 );
58 58
59 59
60 -- CROSS_SECTIONS 60 -- CROSS_SECTIONS
61 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; 61 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
92 92
93 CREATE TABLE discharge_tables ( 93 CREATE TABLE discharge_tables (
94 id NUMBER(38,0) NOT NULL, 94 id NUMBER(38,0) NOT NULL,
95 description VARCHAR2(255) NOT NULL, 95 description VARCHAR2(255) NOT NULL,
96 bfg_id VARCHAR2(50), 96 bfg_id VARCHAR2(50),
97 kind NUMBER(38,0) NOT NULL DEFAULT 0, 97 kind NUMBER(38,0) DEFAULT 0 NOT NULL,
98 gauge_id NUMBER(38,0) NOT NULL, 98 gauge_id NUMBER(38,0) NOT NULL,
99 time_interval_id NUMBER(38,0), 99 time_interval_id NUMBER(38,0),
100 PRIMARY KEY (id), 100 PRIMARY KEY (id),
101 UNIQUE(gauge_id, bfg_id, kind) 101 UNIQUE(gauge_id, bfg_id, kind)
102 ); 102 );
213 id NUMBER(38,0) NOT NULL, 213 id NUMBER(38,0) NOT NULL,
214 value NUMBER(38,2) NOT NULL, 214 value NUMBER(38,2) NOT NULL,
215 gauge_id NUMBER(38,0) NOT NULL, 215 gauge_id NUMBER(38,0) NOT NULL,
216 named_value_id NUMBER(38,0) NOT NULL, 216 named_value_id NUMBER(38,0) NOT NULL,
217 time_interval_id NUMBER(38,0), 217 time_interval_id NUMBER(38,0),
218
219 -- TODO: better checks
220 UNIQUE (gauge_id, named_value_id, time_interval_id), 218 UNIQUE (gauge_id, named_value_id, time_interval_id),
221 PRIMARY KEY (id) 219 PRIMARY KEY (id)
222 ); 220 );
223 221
224 222
261 CREATE SEQUENCE RIVERS_ID_SEQ; 259 CREATE SEQUENCE RIVERS_ID_SEQ;
262 260
263 CREATE TABLE rivers ( 261 CREATE TABLE rivers (
264 id NUMBER(38,0) NOT NULL, 262 id NUMBER(38,0) NOT NULL,
265 official_number NUMBER(38,0), 263 official_number NUMBER(38,0),
266 km_up NUMBER(38,0) NOT NULL DEFAULT 0, 264 km_up NUMBER(38,0) DEFAULT 0 NOT NULL,
267 name VARCHAR2(255) NOT NULL UNIQUE, 265 name VARCHAR2(255) NOT NULL UNIQUE,
268 wst_unit_id NUMBER(38,0) NOT NULL, 266 wst_unit_id NUMBER(38,0) NOT NULL,
269 PRIMARY KEY (id) 267 PRIMARY KEY (id)
270 ); 268 );
271 269
324 CREATE TABLE wst_columns ( 322 CREATE TABLE wst_columns (
325 id NUMBER(38,0) NOT NULL, 323 id NUMBER(38,0) NOT NULL,
326 description VARCHAR2(255), 324 description VARCHAR2(255),
327 name VARCHAR2(255) NOT NULL, 325 name VARCHAR2(255) NOT NULL,
328 source VARCHAR(256), 326 source VARCHAR(256),
329 position NUMBER(38,0) NOT NULL DEFAULT 0, 327 position NUMBER(38,0) DEFAULT 0 NOT NULL,
330 time_interval_id NUMBER(38,0), 328 time_interval_id NUMBER(38,0),
331 wst_id NUMBER(38,0) NOT NULL, 329 wst_id NUMBER(38,0) NOT NULL,
332 UNIQUE (wst_id, name), 330 UNIQUE (wst_id, name),
333 UNIQUE (wst_id, position), 331 UNIQUE (wst_id, position),
334 PRIMARY KEY (id) 332 PRIMARY KEY (id)
415 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; 413 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
416 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 414 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns 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; 415 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
418 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE; 416 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
419 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 417 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
420 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON LETE CASCADE; 418 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE;
421 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; 419 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
422 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; 420 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
423 421
424 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; 422 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
425 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE; 423 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