Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle.sql @ 2353:67e0371f6f40

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adjusted NUMBER format for units, positions and cross_section_points tables. flys-backend/trunk@2986 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Mon, 17 Oct 2011 12:10:12 +0000
parents 8126ccb58f28
children 40093215061b
rev   line source
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
1 -- ANNOTATION_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
3
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
4 CREATE TABLE annotation_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
5 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
6 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
7 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
8 );
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 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
11 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
12 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
13 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
14 /
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 -- ANNOTATIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
17 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
18
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
19 CREATE TABLE annotations (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
20 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
21 attribute_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
22 edge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
23 position_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
24 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
25 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
26 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
27 );
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 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
30 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
31 SELECT ANNOTATIONS_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
32 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
33 /
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 -- ATTRIBUTES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
37 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
38
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
39 CREATE TABLE attributes (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
40 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
41 value VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
42 primary key (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
43 );
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 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
46 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
47 SELECT ATTRIBUTES_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
48 END;
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 -- CROSS_SECTION_LINES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
52 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
53
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
54 CREATE TABLE cross_section_lines (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
55 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
56 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
57 cross_section_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
58 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
61 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
62 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
63 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
64 END;
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
68 -- CROSS_SECTION_POINTS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
69 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
70
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
71 CREATE TABLE cross_section_points (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
72 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
73 col_pos NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
74 x NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
75 y NUMBER(38,2),
2353
67e0371f6f40 Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2352
diff changeset
76 cross_section_line_id NUMBER(38,0),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
77 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
78 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
79
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
80 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
81 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
82 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
83 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
84 /
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 -- CROSS_SECTIONS
1255
0e63222d80bc Added inital oracle schema.
Bjoern Schilberg <bjoern@intevation.de>
parents:
diff changeset
88 create sequence CROSS_SECTIONS_ID_SEQ;
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
89
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
90 create table cross_sections (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
91 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
92 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
93 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
94 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
95 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
96 );
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 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
99 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
100 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
101 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
102 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
103
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 -- DISCHARGE_TABLE_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
106 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
107
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
108 CREATE TABLE discharge_table_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
109 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
110 q NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
111 w NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
112 table_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
113 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
116 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
117 BEGIN
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
118 SELECT DISCHARGE_TABLE_VALUES_ID_SEQ.nextval INTO :new.id FROM dual;
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
119 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
120 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
121
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
122
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
123 -- DISCHARGE_TABLES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
124 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
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 TABLE discharge_tables (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
127 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
128 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
129 kind NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
130 gauge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
131 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
132 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
135 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
136 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
137 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
138 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
139 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
140
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
141
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
142 -- EDGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
143 CREATE SEQUENCE EDGES_ID_SEQ;
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 CREATE TABLE edges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
146 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
147 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
148 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
149 PRIMARY KEY (id)
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 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
153 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
154 SELECT EDGES_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
155 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
156 /
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 -- GAUGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
159 CREATE SEQUENCE GAUGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
160
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
161 CREATE TABLE gauges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
162 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
163 aeo NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
164 datum NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
165 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
166 station NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
167 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
168 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
169 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
172 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
173 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
174 SELECT GAUGES_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
175 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
176 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
177
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
178
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
179 -- HYK_ENTRIES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
180 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
181
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
182 CREATE TABLE hyk_entries (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
183 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
184 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
185 measure TIMESTAMP,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
186 hyk_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
187 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
188 );
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 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
191 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
192 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
193 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
194 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
195
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 -- HYK_FLOW_ZONE_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
198 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
199
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
200 CREATE TABLE hyk_flow_zone_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
201 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
202 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
203 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
204 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
205 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
206
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
207 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
208 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
209 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
210 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
211 /
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_ZONES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
214 CREATE SEQUENCE HYK_FLOW_ZONES_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_zones (
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 a NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
219 b NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
220 formation_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
221 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
222 primary key (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
223 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
224
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
225 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
226 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
227 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
228 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
229 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
230
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 -- HYK_FORMATIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
233 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
234
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
235 CREATE TABLE hyk_formations (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
236 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
237 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
238 distance_hf NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
239 distance_vl NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
240 distance_vr NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
241 formation_num NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
242 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
243 hyk_entry_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
244 PRIMARY KEY (id)
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 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
248 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
249 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
250 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
251 /
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
254 -- HYKS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
255 CREATE SEQUENCE HYKS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
256
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
257 CREATE TABLE hyks (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
258 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
259 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
260 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
261 primary key (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
262 );
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 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
265 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
266 SELECT HYKS_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
267 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
268 /
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 -- MAIN_VALUE_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
272 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
273
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
274 CREATE TABLE main_value_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
275 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
276 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
277 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
278 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
279
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
280 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
281 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
282 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
283 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
284 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
285
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
286 -- MAIN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
287 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
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 CREATE TABLE main_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
290 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
291 value NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
292 gauge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
293 named_value_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
294 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
295 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
296 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
297
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
298 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
299 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
300 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
301 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
302 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
303
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 -- NAMED_MAIN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
306 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
307
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
308 CREATE TABLE named_main_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
309 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
310 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
311 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
312 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
313 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
314
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
315 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
316 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
317 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
318 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
319 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
320
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
321
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
322 -- POSITIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
323 CREATE SEQUENCE POSITIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
324
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
325 CREATE TABLE positions (
2353
67e0371f6f40 Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2352
diff changeset
326 id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
327 value VARCHAR2(255 char),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
328 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
329 );
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 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
332 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
333 SELECT POSITIONS_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
334 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
335 /
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 --- RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
338 CREATE SEQUENCE RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
339
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
340 CREATE TABLE ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
341 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
342 a NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
343 b NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
344 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
345 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
346 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
347
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
348 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
349 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
350 SELECT RANGES_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
351 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
352 /
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 -- RIVERS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
356 CREATE SEQUENCE RIVERS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
357
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
358 CREATE TABLE rivers (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
359 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
360 km_up NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
361 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
362 wst_unit_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
363 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
364 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
365
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
366 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
367 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
368 SELECT RIVERS_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
369 END;
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
373 -- TIME_INTERVALS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
374 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
375
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
376 CREATE TABLE time_intervals (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
377 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
378 start_time TIMESTAMP,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
379 stop_time TIMESTAMP,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
380 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
381 );
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 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
384 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
385 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
386 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
387 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
388
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
389 --- UNITS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
390 CREATE SEQUENCE UNITS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
391
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
392 CREATE TABLE units (
2353
67e0371f6f40 Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2352
diff changeset
393 id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
394 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
395 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
398 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
399 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
400 SELECT UNITS_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
401 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
402 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
403
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
404 -- WST_COLUMN_Q_RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
405 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
406
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
407 CREATE TABLE wst_column_q_ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
408 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
409 wst_column_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
410 wst_q_range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
411 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
412 );
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 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
415 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
416 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
417 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
418 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
419
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
420
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
421 -- WST_COLUMN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
422 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
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 TABLE wst_column_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
425 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
426 position NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
427 w NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
428 wst_column_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
429 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
430 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
431
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
432 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
433 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
434 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
435 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
436 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
437
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 -- WST_COLUMNS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
440 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
441
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
442 CREATE TABLE wst_columns (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
443 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
444 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
445 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
446 position NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
447 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
448 wst_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
449 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
450 );
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 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
453 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
454 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
455 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
456 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
457
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 -- WST_Q_RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
460 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
461
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
462 CREATE TABLE wst_q_ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
463 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
464 q NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
465 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
466 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
469 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
470 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
471 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
472 END;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
473 /
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
474
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
475
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
476 -- WSTS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
477 CREATE SEQUENCE WSTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
478
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
479 CREATE TABLE wsts (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
480 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
481 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
482 kind NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
483 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
484 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
488 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
489 BEGIN
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
490 SELECT WSTS_ID_SEQ.nextval INTO :new.id FROM dual;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
491 END;
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
494
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
495 -- ADD CONSTRAINTs
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
496 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
497 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
498 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
499 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
500 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
501 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
502 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
503 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
504 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
505 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
506 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
507 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
508 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
509 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
510 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
511 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
512 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
513 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
514 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
515 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
516 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
517 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
518 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
519 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
520 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
521 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
522 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
523 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
524 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
525 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
526 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
527 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;
http://dive4elements.wald.intevation.org