Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle.sql @ 5379:61bf64b102bc mapgenfix

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge with default branch
author Christian Lins <christian.lins@intevation.de>
date Fri, 22 Mar 2013 11:25:54 +0100
parents 6dd354e7abfc
children 88cbe798cbab
comparison
equal deleted inserted replaced
5175:cfc5540a4eec 5379:61bf64b102bc
113 -- GAUGES 113 -- GAUGES
114 CREATE SEQUENCE GAUGES_ID_SEQ; 114 CREATE SEQUENCE GAUGES_ID_SEQ;
115 115
116 CREATE TABLE gauges ( 116 CREATE TABLE gauges (
117 id NUMBER(38,0) NOT NULL, 117 id NUMBER(38,0) NOT NULL,
118 aeo NUMBER(38,2), 118 aeo NUMBER(38,2) NOT NULL,
119 datum NUMBER(38,2), 119 datum NUMBER(38,2) NOT NULL,
120 name VARCHAR2(255), 120 name VARCHAR2(255) NOT NULL,
121 station NUMBER(38,2), 121 station NUMBER(38,2) NOT NULL,
122 official_number NUMBER(38,0), 122 official_number NUMBER(38,0) UNIQUE,
123 range_id NUMBER(38,0), 123 range_id NUMBER(38,0) NOT NULL,
124 -- remove river id here because range_id references river already
124 river_id NUMBER(38,0), 125 river_id NUMBER(38,0),
125 PRIMARY KEY (id) 126 PRIMARY KEY (id),
127 UNIQUE (name, river_id),
128 UNIQUE (river_id, station)
126 ); 129 );
127 130
128 131
129 -- HYK_ENTRIES 132 -- HYK_ENTRIES
130 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; 133 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
236 --- RANGES 239 --- RANGES
237 CREATE SEQUENCE RANGES_ID_SEQ; 240 CREATE SEQUENCE RANGES_ID_SEQ;
238 241
239 CREATE TABLE ranges ( 242 CREATE TABLE ranges (
240 id NUMBER(38,0) NOT NULL, 243 id NUMBER(38,0) NOT NULL,
241 a NUMBER(38,10), 244 a NUMBER(38,10) NOT NULL,
242 b NUMBER(38,10), 245 b NUMBER(38,10),
243 river_id NUMBER(38,0), 246 river_id NUMBER(38,0),
244 PRIMARY KEY (id) 247 PRIMARY KEY (id)
245 ); 248 );
249
246 250
247 251
248 -- RIVERS 252 -- RIVERS
249 CREATE SEQUENCE RIVERS_ID_SEQ; 253 CREATE SEQUENCE RIVERS_ID_SEQ;
250 254
261 -- TIME_INTERVALS 265 -- TIME_INTERVALS
262 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; 266 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
263 267
264 CREATE TABLE time_intervals ( 268 CREATE TABLE time_intervals (
265 id NUMBER(38,0) NOT NULL, 269 id NUMBER(38,0) NOT NULL,
266 start_time TIMESTAMP, 270 start_time TIMESTAMP NOT NULL,
267 stop_time TIMESTAMP, 271 stop_time TIMESTAMP,
268 PRIMARY KEY (id) 272 PRIMARY KEY (id),
273 CHECK (start_time <= stop_time)
269 ); 274 );
270 275
271 276
272 --- UNITS 277 --- UNITS
273 CREATE SEQUENCE UNITS_ID_SEQ; 278 CREATE SEQUENCE UNITS_ID_SEQ;
326 PRIMARY KEY (id) 331 PRIMARY KEY (id)
327 ); 332 );
328 333
329 334
330 -- WSTS 335 -- WSTS
336 --lookup table for wst kinds
337 CREATE TABLE wst_kinds (
338 id NUMBER PRIMARY KEY NOT NULL,
339 kind VARCHAR(64) NOT NULL
340 );
341 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
342 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
343 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
344 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
345 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
346 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
347 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
348 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
349
350
331 CREATE SEQUENCE WSTS_ID_SEQ; 351 CREATE SEQUENCE WSTS_ID_SEQ;
332 352
333 CREATE TABLE wsts ( 353 CREATE TABLE wsts (
334 id NUMBER(38,0) NOT NULL, 354 id NUMBER(38,0) NOT NULL,
335 description VARCHAR2(255), 355 description VARCHAR2(255),
338 PRIMARY KEY (id) 358 PRIMARY KEY (id)
339 ); 359 );
340 360
341 361
342 -- ADD CONSTRAINTs 362 -- ADD CONSTRAINTs
343 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges; 363 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
344 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; 364 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
345 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; 365 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
346 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
347 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; 366 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
348 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
349 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
350 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
351 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; 367 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
352 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
353 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; 368 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
354 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
355 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
356 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
357 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
358 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
359 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; 369 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
360 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers; 370 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
361 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
362 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; 371 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
363 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
364 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
365 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; 372 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
366 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
367 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; 373 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
368 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
369 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
370 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
371 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; 374 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
372 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts; 375
373 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES; 376 -- Cascading references
374 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers; 377 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
378 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE;
379 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE;
380 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
381 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
382 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE;
383 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
384 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
385 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE;
386 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE;
387 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE;
388 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
389 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
390 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
391 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
392 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
393 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
394 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
395 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE;
396 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
397 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
375 398
376 -- VIEWS 399 -- VIEWS
377 400
378 CREATE VIEW wst_value_table AS 401 CREATE VIEW wst_value_table AS
379 SELECT wcv.position AS position, 402 SELECT wcv.position AS position,
http://dive4elements.wald.intevation.org