Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle.sql @ 2351:f993b735db71

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adjust oracle schema [I382] flys-backend/trunk@2931 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Mon, 10 Oct 2011 16:20:17 +0000
parents 0e63222d80bc
children 8126ccb58f28
comparison
equal deleted inserted replaced
2350:3eb520b7a70e 2351:f993b735db71
1 BEGIN; 1 BEGIN;
2 create table annotation_types (id number(10,0) not null, name varchar2(255), primary key (id)); 2 -- ANNOTATION_TYPES
3 create table annotations (id number(10,0) not null, attribute_id number(10,0), edge_id number(10,0), position_id number(10,0), range_id number(10,0), type_id number(10,0), primary key (id)); 3 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
4 create table attributes (id number(10,0) not null, value varchar2(255), primary key (id)); 4
5 create table cross_section_lines (id number(10,0) not null, km number(19,2), cross_section_id number(10,0), primary key (id)); 5 CREATE TABLE annotation_types (
6 create table cross_section_points (id number(10,0) not null, col_pos number(10,0), x number(19,2), y number(19,2), cross_section_line_id number(10,0), primary key (id)); 6 id NUMBER(38,0) NOT NULL,
7 create table cross_sections (id number(10,0) not null, description varchar2(255), river_id number(10,0), time_interval_id number(10,0), primary key (id)); 7 name VARCHAR2(255),
8 create table discharge_table_values (id number(10,0) not null, q number(19,2), w number(19,2), table_id number(10,0), primary key (id)); 8 PRIMARY KEY (id)
9 create table discharge_tables (id number(10,0) not null, description varchar2(255), kind number(10,0), gauge_id number(10,0), time_interval_id number(10,0), primary key (id)); 9 );
10 create table edges (id number(10,0) not null, bottom number(19,2), top number(19,2), primary key (id)); 10
11 create table gauges (id number(10,0) not null, aeo number(19,2), datum number(19,2), name varchar2(255), station number(19,2), range_id number(10,0), river_id number(10,0), primary key (id)); 11 CREATE OR REPLACE TRIGGER annotation_types_trigger BEFORE INSERT ON annotation_types FOR each ROW
12 create table hyk_entries (id number(10,0) not null, km number(19,2), measure date, hyk_id number(10,0), primary key (id)); 12 BEGIN
13 create table hyk_flow_zone_types (id number(10,0) not null, description varchar2(255), name varchar2(255), primary key (id)); 13 SELECT ANNOTATION_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
14 create table hyk_flow_zones (id number(10,0) not null, a number(19,2), b number(19,2), formation_id number(10,0), type_id number(10,0), primary key (id)); 14 END;
15 create table hyk_formations (id number(10,0) not null, bottom number(19,2), distance_hf number(19,2), distance_vl number(19,2), distance_vr number(19,2), formation_num number(10,0), top number(19,2), hyk_entry_id number(10,0), primary key (id)); 15 /
16 create table hyks (id number(10,0) not null, description varchar2(255), river_id number(10,0), primary key (id)); 16
17 create table main_value_types (id number(10,0) not null, name varchar2(255), primary key (id)); 17 -- ANNOTATIONS
18 create table main_values (id number(10,0) not null, value number(19,2), gauge_id number(10,0), named_value_id number(10,0), time_interval_id number(10,0), primary key (id)); 18 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
19 create table named_main_values (id number(10,0) not null, name varchar2(255), type_id number(10,0), primary key (id)); 19
20 create table positions (id number(10,0) not null, value varchar2(255), primary key (id)); 20 CREATE TABLE annotations (
21 create table ranges (id number(10,0) not null, a number(19,2), b number(19,2), river_id number(10,0), primary key (id)); 21 id NUMBER(38,0) NOT NULL,
22 create table rivers (id number(10,0) not null, km_up number(1,0), name varchar2(255), primary key (id)); 22 attribute_id NUMBER(38,0),
23 create table time_intervals (id number(10,0) not null, start_time date, stop_time date, primary key (id)); 23 edge_id NUMBER(38,0),
24 create table wst_column_q_ranges (id number(10,0) not null, wst_column_id number(10,0), wst_q_range_id number(10,0), primary key (id)); 24 position_id NUMBER(38,0),
25 create table wst_column_values (id number(10,0) not null, position number(19,2), w number(19,2), wst_column_id number(10,0), primary key (id)); 25 range_id NUMBER(38,0),
26 create table wst_columns (id number(10,0) not null, description varchar2(255), name varchar2(255), position number(10,0), time_interval_id number(10,0), wst_id number(10,0), primary key (id)); 26 type_id NUMBER(38,0),
27 create table wst_q_ranges (id number(10,0) not null, q number(19,2), range_id number(10,0), primary key (id)); 27 PRIMARY KEY (id)
28 create table wsts (id number(10,0) not null, description varchar2(255), kind number(10,0), river_id number(10,0), primary key (id)); 28 );
29 alter table annotations add constraint FKC6AD7B2476703DB3 foreign key (range_id) references ranges; 29
30 alter table annotations add constraint FKC6AD7B24E838461 foreign key (edge_id) references edges; 30 CREATE OR REPLACE TRIGGER annotations_trigger BEFORE INSERT ON annotations FOR each ROW
31 alter table annotations add constraint FKC6AD7B24FF598161 foreign key (position_id) references positions; 31 BEGIN
32 alter table annotations add constraint FKC6AD7B24483998D3 foreign key (attribute_id) references attributes; 32 SELECT ANNOTATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
33 alter table annotations add constraint FKC6AD7B245AE11A50 foreign key (type_id) references annotation_types; 33 END;
34 alter table cross_section_lines add constraint FK4072AB66BF9BA960 foreign key (cross_section_id) references cross_sections; 34 /
35 alter table cross_section_points add constraint FKD5088D5CF337114D foreign key (cross_section_line_id) references cross_section_lines; 35
36 alter table cross_sections add constraint FKC2C43F4D2B365753 foreign key (river_id) references rivers; 36 ALTER TABLE annotations ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES ranges;
37 alter table cross_sections add constraint FKC2C43F4D452A8FDC foreign key (time_interval_id) references time_intervals; 37 ALTER TABLE annotations ADD CONSTRAINT constraint_edges FOREIGN KEY (edge_id) REFERENCES edges;
38 alter table discharge_table_values add constraint FK56DD4B10C1ABE65F foreign key (table_id) references discharge_tables; 38 ALTER TABLE annotations ADD CONSTRAINT constraint_positions FOREIGN KEY (position_id) REFERENCES positions;
39 alter table discharge_tables add constraint FKF143882452A8FDC foreign key (time_interval_id) references time_intervals; 39 ALTER TABLE annotations ADD CONSTRAINT constraint_attributes FOREIGN KEY (attribute_id) REFERENCES attributes;
40 alter table discharge_tables add constraint FKF143882B1A5433 foreign key (gauge_id) references gauges; 40 ALTER TABLE annotations ADD CONSTRAINT constraint_types FOREIGN KEY (type_id) REFERENCES annotation_types;
41 alter table gauges add constraint FKB550CE9A2B365753 foreign key (river_id) references rivers; 41
42 alter table gauges add constraint FKB550CE9A76703DB3 foreign key (range_id) references ranges; 42 -- ATTRIBUTES
43 alter table hyk_entries add constraint FK19EBA6CBEDF9E6D3 foreign key (hyk_id) references hyks; 43 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
44 alter table hyk_flow_zones add constraint FK22F80BDB3C831087 foreign key (formation_id) references hyk_formations; 44
45 alter table hyk_flow_zones add constraint FK22F80BDB697E0D07 foreign key (type_id) references hyk_flow_zone_types; 45 CREATE TABLE attributes (
46 alter table hyk_formations add constraint FK97CFDC71A08D08C foreign key (hyk_entry_id) references hyk_entries; 46 id NUMBER(38,0) NOT NULL,
47 alter table hyks add constraint FK311A392B365753 foreign key (river_id) references rivers; 47 value VARCHAR2(255),
48 alter table main_values add constraint FKD85C6268452A8FDC foreign key (time_interval_id) references time_intervals; 48 primary key (id)
49 alter table main_values add constraint FKD85C6268B1A5433 foreign key (gauge_id) references gauges; 49 );
50 alter table main_values add constraint FKD85C6268DE623F5 foreign key (named_value_id) references named_main_values; 50
51 alter table named_main_values add constraint FK5EA68A62CBC256EB foreign key (type_id) references main_value_types; 51 CREATE OR REPLACE TRIGGER attributes_trigger BEFORE INSERT ON attributes FOR each ROW
52 alter table ranges add constraint FKC812EED62B365753 foreign key (river_id) references rivers; 52 BEGIN
53 alter table wst_column_q_ranges add constraint FKA28D6C067DA2F764 foreign key (wst_column_id) references wst_columns; 53 SELECT ATTRIBUTES_ID_SEQ.nextval INTO :new.id FROM dual;
54 alter table wst_column_q_ranges add constraint FKA28D6C066A71480F foreign key (wst_q_range_id) references wst_q_ranges; 54 END;
55 alter table wst_column_values add constraint FK992F4F447DA2F764 foreign key (wst_column_id) references wst_columns; 55 /
56 alter table wst_columns add constraint FKE77A73B6452A8FDC foreign key (time_interval_id) references time_intervals; 56
57 alter table wst_columns add constraint FKE77A73B67425E13 foreign key (wst_id) references wsts; 57 -- CROSS_SECTION_LINES
58 alter table wst_q_ranges add constraint FK70E459AB76703DB3 foreign key (range_id) references ranges; 58 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
59 alter table wsts add constraint FK37D65B2B365753 foreign key (river_id) references rivers; 59
60 create sequence ANNOTATIONS_ID_SEQ; 60 CREATE TABLE cross_section_lines (
61 create sequence ANNOTATION_TYPES_ID_SEQ; 61 id NUMBER(38,0) NOT NULL,
62 create sequence ATTRIBUTES_ID_SEQ; 62 km NUMBER(38,2),
63 cross_section_id NUMBER(38,0),
64 PRIMARY KEY (id)
65 );
66
67 CREATE OR REPLACE TRIGGER cross_section_lines_trigger BEFORE INSERT ON cross_section_lines FOR each ROW
68 BEGIN
69 SELECT CROSS_SECTION_LINES_ID_SEQ.nextval INTO :new.id FROM dual;
70 END;
71 /
72
73 ALTER TABLE cross_section_lines ADD CONSTRAINT constraint_cross_sections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
74
75 -- CROSS_SECTION_POINTS
76 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
77
78 CREATE TABLE cross_section_points (
79 id NUMBER(38,0) NOT NULL,
80 col_pos NUMBER(38,0),
81 x NUMBER(38,2),
82 y NUMBER(38,2),
83 cross_section_line_id NUMBER(10,0),
84 PRIMARY KEY (id)
85 );
86
87 CREATE OR REPLACE TRIGGER cross_section_points_trigger BEFORE INSERT ON cross_section_points FOR each ROW
88 BEGIN
89 SELECT CROSS_SECTION_POINTS_ID_SEQ.nextval INTO :new.id FROM dual;
90 END;
91 /
92
93 ALTER TABLE cross_section_points ADD CONSTRAINT constraint_cross_section_lines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
94
95 -- CROSS_SECTIONS
63 create sequence CROSS_SECTIONS_ID_SEQ; 96 create sequence CROSS_SECTIONS_ID_SEQ;
64 create sequence CROSS_SECTION_LINES_ID_SEQ; 97
65 create sequence CROSS_SECTION_POINTS_ID_SEQ; 98 create table cross_sections (
66 create sequence DISCHARGE_TABLES_ID_SEQ; 99 id NUMBER(38,0) NOT NULL,
67 create sequence DISCHARGE_TABLE_VALUES_ID_SEQ; 100 description VARCHAR2(255),
68 create sequence EDGES_ID_SEQ; 101 river_id NUMBER(38,0),
69 create sequence GAUGES_ID_SEQ; 102 time_interval_id NUMBER(38,0),
70 create sequence HYKS_ID_SEQ; 103 PRIMARY KEY (id)
71 create sequence HYK_ENTRIES_ID_SEQ; 104 );
72 create sequence HYK_FLOW_ZONES_ID_SEQ; 105
73 create sequence HYK_FLOW_ZONE_TYPES_ID_SEQ; 106 CREATE OR REPLACE TRIGGER cross_sections_trigger BEFORE INSERT ON cross_sections FOR each ROW
74 create sequence HYK_FORMATIONS_ID_SEQ; 107 BEGIN
75 create sequence MAIN_VALUES_ID_SEQ; 108 SELECT CROSS_SECTIONS_ID_SEQ.nextval INTO :new.id FROM dual;
76 create sequence MAIN_VALUE_TYPES_ID_SEQ; 109 END;
77 create sequence NAMED_MAIN_VALUES_ID_SEQ; 110 /
78 create sequence POSITIONS_ID_SEQ; 111
79 create sequence RANGES_ID_SEQ; 112 ALTER TABLE cross_sections ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
80 create sequence RIVERS_ID_SEQ; 113 ALTER TABLE cross_sections ADD CONSTRAINT constraint_time_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
81 create sequence TIME_INTERVALS_ID_SEQ; 114
82 create sequence WSTS_ID_SEQ; 115 -- DISCHARGE_TABLE_VALUES
83 create sequence WST_COLUMNS_ID_SEQ; 116 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
84 create sequence WST_COLUMN_VALUES_ID_SEQ; 117
85 create sequence WST_Q_RANGES_ID_SEQ; 118 CREATE TABLE discharge_table_values (
119 id NUMBER(38,0) NOT NULL,
120 q NUMBER(38,2),
121 w NUMBER(38,2),
122 table_id NUMBER(38,0),
123 PRIMARY KEY (id)
124 );
125
126 CREATE OR REPLACE TRIGGER discharge_table_values_trigger BEFORE INSERT ON discharge_table_values FOR each ROW
127 BEGIN
128 SELECT DISCHARGE_TABLES_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
129 END;
130 /
131
132 ALTER TABLE discharge_table_values ADD CONSTRAINT constraint_discharge_tables foreign key (table_id) REFERENCES discharge_tables;
133
134 -- DISCHARGE_TABLES
135 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
136
137 CREATE TABLE discharge_tables (
138 id NUMBER(38,0) NOT NULL,
139 description VARCHAR2(255),
140 kind NUMBER(38,0),
141 gauge_id NUMBER(38,0),
142 time_interval_id NUMBER(38,0),
143 PRIMARY KEY (id)
144 );
145
146 CREATE OR REPLACE TRIGGER discharge_tables_trigger BEFORE INSERT ON discharge_tables FOR each ROW
147 BEGIN
148 SELECT DISCHARGE_TABLES_ID_SEQ.nextval INTO :new.id FROM dual;
149 END;
150 /
151
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
155 -- EDGES
156 CREATE SEQUENCE EDGES_ID_SEQ;
157
158 CREATE TABLE edges (
159 id NUMBER(38,0) NOT NULL,
160 bottom NUMBER(38,2),
161 top NUMBER(38,2),
162 PRIMARY KEY (id)
163 );
164
165 CREATE OR REPLACE TRIGGER edges_trigger BEFORE INSERT ON edges FOR each ROW
166 BEGIN
167 SELECT EDGES_ID_SEQ.nextval INTO :new.id FROM dual;
168 END;
169 /
170
171 -- GAUGES
172 CREATE SEQUENCE GAUGES_ID_SEQ;
173
174 CREATE TABLE gauges (
175 id NUMBER(38,0) NOT NULL,
176 aeo NUMBER(38,2),
177 datum NUMBER(38,2),
178 name VARCHAR2(255),
179 station NUMBER(38,2),
180 range_id NUMBER(38,0),
181 river_id NUMBER(38,0),
182 PRIMARY KEY (id)
183 );
184
185 CREATE OR REPLACE TRIGGER gauges_trigger BEFORE INSERT ON gauges FOR each ROW
186 BEGIN
187 SELECT GAUGES_ID_SEQ.nextval INTO :new.id FROM dual;
188 END;
189 /
190
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
194 -- HYK_ENTRIES
195 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
196
197 CREATE TABLE hyk_entries (
198 id NUMBER(38,0) NOT NULL,
199 km NUMBER(38,2),
200 measure TIMESTAMP,
201 hyk_id NUMBER(38,0),
202 PRIMARY KEY (id)
203 );
204
205 CREATE OR REPLACE TRIGGER hyk_entries_trigger BEFORE INSERT ON hyk_entries FOR each ROW
206 BEGIN
207 SELECT HYK_ENTRIES_ID_SEQ.nextval INTO :new.id FROM dual;
208 END;
209 /
210
211 ALTER TABLE hyk_entries ADD CONSTRAINT constraint_hyks FOREIGN KEY (hyk_id) REFERENCES hyks;
212
213 -- HYK_FLOW_ZONE_TYPES
214 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
215
216 CREATE TABLE hyk_flow_zone_types (
217 id NUMBER(38,0) NOT NULL,
218 description VARCHAR2(255),
219 name VARCHAR2(255),
220 PRIMARY KEY (id)
221 );
222
223 CREATE OR REPLACE TRIGGER hyk_flow_zone_types_trigger BEFORE INSERT ON hyk_flow_zone_types FOR each ROW
224 BEGIN
225 SELECT HYK_FLOW_ZONE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
226 END;
227 /
228
229 -- HYK_FLOW_ZONES
230 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
231
232 CREATE TABLE hyk_flow_zones (
233 id NUMBER(38,0) NOT NULL,
234 a NUMBER(38,2),
235 b NUMBER(38,2),
236 formation_id NUMBER(38,0),
237 type_id NUMBER(38,0),
238 primary key (id)
239 );
240
241 CREATE OR REPLACE TRIGGER hyk_flow_zones_trigger BEFORE INSERT ON hyk_flow_zones FOR each ROW
242 BEGIN
243 SELECT HYK_FLOW_ZONES_ID_SEQ.nextval INTO :new.id FROM dual;
244 END;
245 /
246
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
250 -- HYK_FORMATIONS
251 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
252
253 CREATE TABLE hyk_formations (
254 id NUMBER(38,0) NOT NULL,
255 bottom NUMBER(38,2),
256 distance_hf NUMBER(38,2),
257 distance_vl NUMBER(38,2),
258 distance_vr NUMBER(38,2),
259 formation_num NUMBER(38,0),
260 top NUMBER(38,2),
261 hyk_entry_id NUMBER(38,0),
262 PRIMARY KEY (id)
263 );
264
265 CREATE OR REPLACE TRIGGER hyk_formations_trigger BEFORE INSERT ON hyk_formations FOR each ROW
266 BEGIN
267 SELECT HYK_FORMATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
268 END;
269 /
270
271 ALTER TABLE hyk_formations ADD CONSTRAINT constraint_hyk_entries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
272
273 -- HYKS
274 CREATE SEQUENCE HYKS_ID_SEQ;
275
276 CREATE TABLE hyks (
277 id NUMBER(38,0) NOT NULL,
278 description VARCHAR2(255),
279 river_id NUMBER(38,0),
280 primary key (id)
281 );
282
283 CREATE OR REPLACE TRIGGER hyks_trigger BEFORE INSERT ON hyks FOR each ROW
284 BEGIN
285 SELECT HYKS_ID_SEQ.nextval INTO :new.id FROM dual;
286 END;
287 /
288
289 ALTER TABLE hyks ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
290
291 -- MAIN_VALUE_TYPES
292 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
293
294 CREATE TABLE main_value_types (
295 id NUMBER(38,0) NOT NULL,
296 name VARCHAR2(255),
297 PRIMARY KEY (id)
298 );
299
300 CREATE OR REPLACE TRIGGER main_value_types_trigger BEFORE INSERT ON main_value_types FOR each ROW
301 BEGIN
302 SELECT MAIN_VALUE_TYPES_ID_SEQ.nextval INTO :new.id FROM dual;
303 END;
304 /
305
306 -- MAIN_VALUES
307 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
308
309 CREATE TABLE main_values (
310 id NUMBER(38,0) NOT NULL,
311 value NUMBER(38,2),
312 gauge_id NUMBER(38,0),
313 named_value_id NUMBER(38,0),
314 time_interval_id NUMBER(38,0),
315 PRIMARY KEY (id)
316 );
317
318 CREATE OR REPLACE TRIGGER main_values_trigger BEFORE INSERT ON main_values FOR each ROW
319 BEGIN
320 SELECT MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
321 END;
322 /
323
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
328 -- NAMED_MAIN_VALUES
329 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
330
331 CREATE TABLE named_main_values (
332 id NUMBER(38,0) NOT NULL,
333 name VARCHAR2(255),
334 type_id NUMBER(38,0),
335 PRIMARY KEY (id)
336 );
337
338 CREATE OR REPLACE TRIGGER named_main_values_trigger BEFORE INSERT ON named_main_values FOR each ROW
339 BEGIN
340 SELECT NAMED_MAIN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
341 END;
342 /
343
344 ALTER TABLE named_main_values ADD CONSTRAINT constraint_main_value_types FOREIGN KEY (type_id) REFERENCES main_value_types;
345
346 -- POSITIONS
347 CREATE SEQUENCE POSITIONS_ID_SEQ;
348
349 CREATE TABLE positions (
350 id NUMBER(10,0) NOT NULL,
351 value VARCHAR2(255 char),
352 PRIMARY KEY (id)
353 );
354
355 CREATE OR REPLACE TRIGGER positions_trigger BEFORE INSERT ON positions FOR each ROW
356 BEGIN
357 SELECT POSITIONS_ID_SEQ.nextval INTO :new.id FROM dual;
358 END;
359 /
360
361 --- RANGES
362 CREATE SEQUENCE RANGES_ID_SEQ;
363
364 CREATE TABLE ranges (
365 id NUMBER(38,0) NOT NULL,
366 a NUMBER(38,2),
367 b NUMBER(38,2),
368 river_id NUMBER(38,0),
369 PRIMARY KEY (id)
370 );
371
372 CREATE OR REPLACE TRIGGER ranges_trigger BEFORE INSERT ON ranges FOR each ROW
373 BEGIN
374 SELECT RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
375 END;
376 /
377
378 ALTER TABLE ranges ADD CONSTRAINT contraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
379
380 -- RIVERS
381 CREATE SEQUENCE RIVERS_ID_SEQ;
382
383 CREATE TABLE rivers (
384 id NUMBER(38,0) NOT NULL,
385 km_up NUMBER(38,0),
386 name VARCHAR2(255),
387 wst_unit_id NUMBER(38,0),
388 PRIMARY KEY (id)
389 );
390
391 CREATE OR REPLACE TRIGGER rivers_trigger BEFORE INSERT ON rivers FOR each ROW
392 BEGIN
393 SELECT RIVERS_ID_SEQ.nextval INTO :new.id FROM dual;
394 END;
395 /
396
397 ALTER TABLE rivers ADD CONSTRAINT contraint_units FOREIGN KEY (wst_unit_id) REFERENCES units;
398
399 -- TIME_INTERVALS
400 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
401
402 CREATE TABLE time_intervals (
403 id NUMBER(38,0) NOT NULL,
404 start_time TIMESTAMP,
405 stop_time TIMESTAMP,
406 PRIMARY KEY (id)
407 );
408
409 CREATE OR REPLACE TRIGGER time_intervals_trigger BEFORE INSERT ON time_intervals FOR each ROW
410 BEGIN
411 SELECT TIME_INTERVALS_ID_SEQ.nextval INTO :new.id FROM dual;
412 END;
413 /
414
415 --- UNITS
416 CREATE SEQUENCE UNITS_ID_SEQ;
417
418 CREATE TABLE units (
419 id NUMBER(10,0) NOT NULL,
420 name VARCHAR2(255),
421 PRIMARY KEY (id)
422 );
423
424 CREATE OR REPLACE TRIGGER units_trigger BEFORE INSERT ON units FOR each ROW
425 BEGIN
426 SELECT UNITS_ID_SEQ.nextval INTO :new.id FROM dual;
427 END;
428 /
429
430 -- WST_COLUMN_Q_RANGES
431 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
432
433 CREATE TABLE wst_column_q_ranges (
434 id NUMBER(38,0) NOT NULL,
435 wst_column_id NUMBER(38,0),
436 wst_q_range_id NUMBER(38,0),
437 PRIMARY KEY (id)
438 );
439
440 CREATE OR REPLACE TRIGGER wst_column_q_ranges_trigger BEFORE INSERT ON wst_column_q_ranges FOR each ROW
441 BEGIN
442 SELECT WST_COLUMN_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
443 END;
444 /
445
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
449 -- WST_COLUMN_VALUES
450 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
451
452 CREATE TABLE wst_column_values (
453 id NUMBER(38,0) NOT NULL,
454 position NUMBER(38,2),
455 w NUMBER(38,2),
456 wst_column_id NUMBER(38,0),
457 PRIMARY KEY (id)
458 );
459
460 CREATE OR REPLACE TRIGGER wst_column_values_trigger BEFORE INSERT ON wst_column_values FOR each ROW
461 BEGIN
462 SELECT WST_COLUMN_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
463 END;
464 /
465
466 ALTER TABLE wst_column_values ADD CONSTRAINT constraint_wst_columns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
467
468 -- WST_COLUMNS
469 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
470
471 CREATE TABLE wst_columns (
472 id NUMBER(38,0) NOT NULL,
473 description VARCHAR2(255),
474 name VARCHAR2(255),
475 position NUMBER(38,0),
476 time_interval_id NUMBER(38,0),
477 wst_id NUMBER(38,0),
478 PRIMARY KEY (id)
479 );
480
481 CREATE OR REPLACE TRIGGER wst_columns_trigger BEFORE INSERT ON wst_columns FOR each ROW
482 BEGIN
483 SELECT WST_COLUMNS_ID_SEQ.nextval INTO :new.id FROM dual;
484 END;
485 /
486
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
490 -- WST_Q_RANGES
491 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
492
493 CREATE TABLE wst_q_ranges (
494 id NUMBER(38,0) NOT NULL,
495 q NUMBER(38,2),
496 range_id NUMBER(38,0),
497 PRIMARY KEY (id)
498 );
499
500 CREATE OR REPLACE TRIGGER wst_q_ranges_trigger BEFORE INSERT ON wst_q_ranges FOR each ROW
501 BEGIN
502 SELECT WST_Q_RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
503 END;
504 /
505
506 ALTER TABLE wst_q_ranges ADD CONSTRAINT constraint_ranges FOREIGN KEY (range_id) REFERENCES RANGES;
507
508 -- WSTS
509 CREATE SEQUENCE WSTS_ID_SEQ;
510
511 CREATE TABLE wsts (
512 id NUMBER(38,0) NOT NULL,
513 description VARCHAR2(255),
514 kind NUMBER(38,0),
515 river_id NUMBER(38,0),
516 PRIMARY KEY (id)
517 );
518
519
520 CREATE OR REPLACE TRIGGER wsts_trigger BEFORE INSERT ON wsts FOR each ROW
521 BEGIN
522 SELECT WSTS_ID_SEQ.nextval INTO :new.id FROM dual;
523 END;
524 /
525
526 ALTER TABLE wsts ADD CONSTRAINT constraint_rivers FOREIGN KEY (river_id) REFERENCES rivers;
527
86 COMMIT; 528 COMMIT;
http://dive4elements.wald.intevation.org