Mercurial > dive4elements > river

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