Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle.sql @ 2352:8126ccb58f28

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed errors. flys-backend/trunk@2932 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Tue, 11 Oct 2011 07:29:53 +0000
parents f993b735db71
children 67e0371f6f40
comparison
equal deleted inserted replaced
2351:f993b735db71 2352:8126ccb58f28
1 BEGIN;
2 -- ANNOTATION_TYPES 1 -- ANNOTATION_TYPES
3 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; 2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
4 3
5 CREATE TABLE annotation_types ( 4 CREATE TABLE annotation_types (
6 id NUMBER(38,0) NOT NULL, 5 id NUMBER(38,0) NOT NULL,
31 BEGIN 30 BEGIN
32 SELECT ANNOTATIONS_ID_SEQ.nextval INTO :new.id FROM dual; 31 SELECT ANNOTATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
33 END; 32 END;
34 / 33 /
35 34
36 ALTER TABLE annotations ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
37 ALTER TABLE annotations ADD CONSTRAINT constraint_edges FOREIGN KEY (edge_id) REFERENCES edges;
38 ALTER TABLE annotations ADD CONSTRAINT constraint_positions FOREIGN KEY (position_id) REFERENCES positions;
39 ALTER TABLE annotations ADD CONSTRAINT constraint_attributes FOREIGN KEY (attribute_id) REFERENCES attributes;
40 ALTER TABLE annotations ADD CONSTRAINT constraint_types FOREIGN KEY (type_id) REFERENCES annotation_types;
41 35
42 -- ATTRIBUTES 36 -- ATTRIBUTES
43 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; 37 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
44 38
45 CREATE TABLE attributes ( 39 CREATE TABLE attributes (
68 BEGIN 62 BEGIN
69 SELECT CROSS_SECTION_LINES_ID_SEQ.nextval INTO :new.id FROM dual; 63 SELECT CROSS_SECTION_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
70 END; 64 END;
71 / 65 /
72 66
73 ALTER TABLE cross_section_lines ADD CONSTRAINT constraint_cross_sections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
74 67
75 -- CROSS_SECTION_POINTS 68 -- CROSS_SECTION_POINTS
76 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; 69 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
77 70
78 CREATE TABLE cross_section_points ( 71 CREATE TABLE cross_section_points (
88 BEGIN 81 BEGIN
89 SELECT CROSS_SECTION_POINTS_ID_SEQ.nextval INTO :new.id FROM dual; 82 SELECT CROSS_SECTION_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
90 END; 83 END;
91 / 84 /
92 85
93 ALTER TABLE cross_section_points ADD CONSTRAINT constraint_cross_section_lines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
94 86
95 -- CROSS_SECTIONS 87 -- CROSS_SECTIONS
96 create sequence CROSS_SECTIONS_ID_SEQ; 88 create sequence CROSS_SECTIONS_ID_SEQ;
97 89
98 create table cross_sections ( 90 create table cross_sections (
107 BEGIN 99 BEGIN
108 SELECT CROSS_SECTIONS_ID_SEQ.nextval INTO :new.id FROM dual; 100 SELECT CROSS_SECTIONS_ID_SEQ.nextval INTO :new.id FROM dual;
109 END; 101 END;
110 / 102 /
111 103
112 ALTER TABLE cross_sections ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
113 ALTER TABLE cross_sections ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
114 104
115 -- DISCHARGE_TABLE_VALUES 105 -- DISCHARGE_TABLE_VALUES
116 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; 106 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
117 107
118 CREATE TABLE discharge_table_values ( 108 CREATE TABLE discharge_table_values (
123 PRIMARY KEY (id) 113 PRIMARY KEY (id)
124 ); 114 );
125 115
126 CREATE OR REPLACE TRIGGER discharge_table_values_trigger BEFORE INSERT ON discharge_table_values FOR each ROW 116 CREATE OR REPLACE TRIGGER discharge_table_values_trigger BEFORE INSERT ON discharge_table_values FOR each ROW
127 BEGIN 117 BEGIN
128 SELECT DISCHARGE_TABLES_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; 118 SELECT DISCHARGE_TABLE_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
129 END; 119 END;
130 / 120 /
131 121
132 ALTER TABLE discharge_table_values ADD CONSTRAINT constraint_discharge_tables foreign key (table_id) REFERENCES discharge_tables;
133 122
134 -- DISCHARGE_TABLES 123 -- DISCHARGE_TABLES
135 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; 124 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
136 125
137 CREATE TABLE discharge_tables ( 126 CREATE TABLE discharge_tables (
147 BEGIN 136 BEGIN
148 SELECT DISCHARGE_TABLES_ID_SEQ.nextval INTO :new.id FROM dual; 137 SELECT DISCHARGE_TABLES_ID_SEQ.nextval INTO :new.id FROM dual;
149 END; 138 END;
150 / 139 /
151 140
152 ALTER TABLE discharge_tables ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
153 ALTER TABLE discharge_tables ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;
154 141
155 -- EDGES 142 -- EDGES
156 CREATE SEQUENCE EDGES_ID_SEQ; 143 CREATE SEQUENCE EDGES_ID_SEQ;
157 144
158 CREATE TABLE edges ( 145 CREATE TABLE edges (
186 BEGIN 173 BEGIN
187 SELECT GAUGES_ID_SEQ.nextval INTO :new.id FROM dual; 174 SELECT GAUGES_ID_SEQ.nextval INTO :new.id FROM dual;
188 END; 175 END;
189 / 176 /
190 177
191 ALTER TABLE gauges add CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
192 ALTER TABLE gauges add CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
193 178
194 -- HYK_ENTRIES 179 -- HYK_ENTRIES
195 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; 180 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
196 181
197 CREATE TABLE hyk_entries ( 182 CREATE TABLE hyk_entries (
206 BEGIN 191 BEGIN
207 SELECT HYK_ENTRIES_ID_SEQ.nextval INTO :new.id FROM dual; 192 SELECT HYK_ENTRIES_ID_SEQ.nextval INTO :new.id FROM dual;
208 END; 193 END;
209 / 194 /
210 195
211 ALTER TABLE hyk_entries ADD CONSTRAINT constraint_hyks FOREIGN KEY (hyk_id) REFERENCES hyks;
212 196
213 -- HYK_FLOW_ZONE_TYPES 197 -- HYK_FLOW_ZONE_TYPES
214 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; 198 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
215 199
216 CREATE TABLE hyk_flow_zone_types ( 200 CREATE TABLE hyk_flow_zone_types (
242 BEGIN 226 BEGIN
243 SELECT HYK_FLOW_ZONES_ID_SEQ.nextval INTO :new.id FROM dual; 227 SELECT HYK_FLOW_ZONES_ID_SEQ.nextval INTO :new.id FROM dual;
244 END; 228 END;
245 / 229 /
246 230
247 ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_formations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
248 ALTER TABLE hyk_flow_zones ADD CONSTRAINT constraint_hyk_flow_zone_types FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
249 231
250 -- HYK_FORMATIONS 232 -- HYK_FORMATIONS
251 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; 233 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
252 234
253 CREATE TABLE hyk_formations ( 235 CREATE TABLE hyk_formations (
266 BEGIN 248 BEGIN
267 SELECT HYK_FORMATIONS_ID_SEQ.nextval INTO :new.id FROM dual; 249 SELECT HYK_FORMATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
268 END; 250 END;
269 / 251 /
270 252
271 ALTER TABLE hyk_formations ADD CONSTRAINT constraint_hyk_entries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
272 253
273 -- HYKS 254 -- HYKS
274 CREATE SEQUENCE HYKS_ID_SEQ; 255 CREATE SEQUENCE HYKS_ID_SEQ;
275 256
276 CREATE TABLE hyks ( 257 CREATE TABLE hyks (
284 BEGIN 265 BEGIN
285 SELECT HYKS_ID_SEQ.nextval INTO :new.id FROM dual; 266 SELECT HYKS_ID_SEQ.nextval INTO :new.id FROM dual;
286 END; 267 END;
287 / 268 /
288 269
289 ALTER TABLE hyks ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
290 270
291 -- MAIN_VALUE_TYPES 271 -- MAIN_VALUE_TYPES
292 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; 272 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
293 273
294 CREATE TABLE main_value_types ( 274 CREATE TABLE main_value_types (
319 BEGIN 299 BEGIN
320 SELECT MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; 300 SELECT MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
321 END; 301 END;
322 / 302 /
323 303
324 ALTER TABLE main_values ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
325 ALTER TABLE main_values ADD CONSTRAINT constraint_gauges FOREIGN KEY (gauge_id) REFERENCES gauges;
326 ALTER TABLE main_values ADD CONSTRAINT constraint_named_main_values FOREIGN KEY (named_value_id) REFERENCES named_main_values;
327 304
328 -- NAMED_MAIN_VALUES 305 -- NAMED_MAIN_VALUES
329 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; 306 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
330 307
331 CREATE TABLE named_main_values ( 308 CREATE TABLE named_main_values (
339 BEGIN 316 BEGIN
340 SELECT NAMED_MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; 317 SELECT NAMED_MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
341 END; 318 END;
342 / 319 /
343 320
344 ALTER TABLE named_main_values ADD CONSTRAINT constraint_main_value_types FOREIGN KEY (type_id) REFERENCES main_value_types;
345 321
346 -- POSITIONS 322 -- POSITIONS
347 CREATE SEQUENCE POSITIONS_ID_SEQ; 323 CREATE SEQUENCE POSITIONS_ID_SEQ;
348 324
349 CREATE TABLE positions ( 325 CREATE TABLE positions (
373 BEGIN 349 BEGIN
374 SELECT RANGES_ID_SEQ.nextval INTO :new.id FROM dual; 350 SELECT RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
375 END; 351 END;
376 / 352 /
377 353
378 ALTER TABLE ranges ADD CONSTRAINT contraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
379 354
380 -- RIVERS 355 -- RIVERS
381 CREATE SEQUENCE RIVERS_ID_SEQ; 356 CREATE SEQUENCE RIVERS_ID_SEQ;
382 357
383 CREATE TABLE rivers ( 358 CREATE TABLE rivers (
392 BEGIN 367 BEGIN
393 SELECT RIVERS_ID_SEQ.nextval INTO :new.id FROM dual; 368 SELECT RIVERS_ID_SEQ.nextval INTO :new.id FROM dual;
394 END; 369 END;
395 / 370 /
396 371
397 ALTER TABLE rivers ADD CONSTRAINT contraint_units FOREIGN KEY (wst_unit_id) REFERENCES units;
398 372
399 -- TIME_INTERVALS 373 -- TIME_INTERVALS
400 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; 374 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
401 375
402 CREATE TABLE time_intervals ( 376 CREATE TABLE time_intervals (
441 BEGIN 415 BEGIN
442 SELECT WST_COLUMN_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual; 416 SELECT WST_COLUMN_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
443 END; 417 END;
444 / 418 /
445 419
446 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_colums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
447 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT constraint_wst_q_ranges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
448 420
449 -- WST_COLUMN_VALUES 421 -- WST_COLUMN_VALUES
450 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; 422 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
451 423
452 CREATE TABLE wst_column_values ( 424 CREATE TABLE wst_column_values (
461 BEGIN 433 BEGIN
462 SELECT WST_COLUMN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual; 434 SELECT WST_COLUMN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
463 END; 435 END;
464 / 436 /
465 437
466 ALTER TABLE wst_column_values ADD CONSTRAINT constraint_wst_columns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
467 438
468 -- WST_COLUMNS 439 -- WST_COLUMNS
469 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; 440 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
470 441
471 CREATE TABLE wst_columns ( 442 CREATE TABLE wst_columns (
482 BEGIN 453 BEGIN
483 SELECT WST_COLUMNS_ID_SEQ.nextval INTO :new.id FROM dual; 454 SELECT WST_COLUMNS_ID_SEQ.nextval INTO :new.id FROM dual;
484 END; 455 END;
485 / 456 /
486 457
487 ALTER TABLE wst_columns ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
488 ALTER TABLE wst_columns ADD CONSTRAINT constraint_wsts FOREIGN KEY (wst_id) REFERENCES wsts;
489 458
490 -- WST_Q_RANGES 459 -- WST_Q_RANGES
491 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; 460 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
492 461
493 CREATE TABLE wst_q_ranges ( 462 CREATE TABLE wst_q_ranges (
501 BEGIN 470 BEGIN
502 SELECT WST_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual; 471 SELECT WST_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
503 END; 472 END;
504 / 473 /
505 474
506 ALTER TABLE wst_q_ranges ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES RANGES;
507 475
508 -- WSTS 476 -- WSTS
509 CREATE SEQUENCE WSTS_ID_SEQ; 477 CREATE SEQUENCE WSTS_ID_SEQ;
510 478
511 CREATE TABLE wsts ( 479 CREATE TABLE wsts (
521 BEGIN 489 BEGIN
522 SELECT WSTS_ID_SEQ.nextval INTO :new.id FROM dual; 490 SELECT WSTS_ID_SEQ.nextval INTO :new.id FROM dual;
523 END; 491 END;
524 / 492 /
525 493
526 ALTER TABLE wsts ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers; 494
527 495 -- ADD CONSTRAINTs
528 COMMIT; 496 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges;
497 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
498 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
499 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
500 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
501 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
502 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
503 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
504 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
505 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
506 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
507 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
508 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
509 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
510 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
511 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
512 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
513 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers;
514 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
515 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
516 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
517 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
518 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
519 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
520 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
521 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
522 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
523 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
524 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
525 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
526 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
527 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;
http://dive4elements.wald.intevation.org