Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle.sql @ 4055:bbce9e1ef807
Dummy state model for extreme value analysis.
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Mon, 08 Oct 2012 10:32:03 +0200 |
parents | 948c7289fc42 |
children | 83a42e6a562d |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
11 -- ANNOTATIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
13 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
14 CREATE TABLE annotations ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
15 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
16 attribute_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
17 edge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
18 position_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
19 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
20 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
21 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
22 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
23 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
24 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
25 -- ATTRIBUTES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; |
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 CREATE TABLE attributes ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
29 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
30 value VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
31 primary key (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
32 ); |
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 -- CROSS_SECTION_LINES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
36 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
37 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
38 CREATE TABLE cross_section_lines ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
39 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
40 km NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
41 cross_section_id NUMBER(38,0), |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
46 -- CROSS_SECTION_POINTS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
47 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
48 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
49 CREATE TABLE cross_section_points ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
50 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
51 col_pos NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
52 x NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
53 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
|
54 cross_section_line_id NUMBER(38,0), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
55 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
58 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
59 -- CROSS_SECTIONS |
2855
9c2424073be0
Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2371
diff
changeset
|
60 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
61 |
2855
9c2424073be0
Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2371
diff
changeset
|
62 CREATE TABLE cross_sections ( |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
63 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
64 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
65 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
66 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
67 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
68 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
69 |
3339
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
70 -- Indices for faster access of the points |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
71 CREATE INDEX cross_section_lines_km_idx |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
72 ON cross_section_lines(km); |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
73 CREATE INDEX cross_section_points_line_idx |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
74 ON cross_section_points(cross_section_line_id); |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
75 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
76 -- DISCHARGE_TABLE_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; |
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 CREATE TABLE discharge_table_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
80 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
81 q NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
82 w NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
83 table_id NUMBER(38,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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
88 -- DISCHARGE_TABLES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
89 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
90 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
91 CREATE TABLE discharge_tables ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
92 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
93 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
94 kind NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
95 gauge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
96 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
97 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
98 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
99 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
100 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
101 -- EDGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
102 CREATE SEQUENCE EDGES_ID_SEQ; |
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 CREATE TABLE edges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
105 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
106 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
107 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
108 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
109 ); |
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 -- GAUGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
113 CREATE SEQUENCE GAUGES_ID_SEQ; |
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 CREATE TABLE gauges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
116 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
117 aeo NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
118 datum NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
119 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
120 station NUMBER(38,2), |
2371
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2367
diff
changeset
|
121 official_number NUMBER(38,0), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
122 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
123 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
124 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
127 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
128 -- HYK_ENTRIES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
129 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; |
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 CREATE TABLE hyk_entries ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
132 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
133 km NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
134 measure TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
135 hyk_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
136 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
137 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
138 |
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 -- HYK_FLOW_ZONE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
141 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
142 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
143 CREATE TABLE hyk_flow_zone_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
144 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
145 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
146 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
147 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
148 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
149 |
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 -- HYK_FLOW_ZONES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
152 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
153 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
154 CREATE TABLE hyk_flow_zones ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
155 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
156 a NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
157 b NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
158 formation_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
159 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
160 primary key (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
161 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
162 |
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 -- HYK_FORMATIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
165 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
166 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
167 CREATE TABLE hyk_formations ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
168 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
169 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
170 distance_hf NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
171 distance_vl NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
172 distance_vr NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
173 formation_num NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
174 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
175 hyk_entry_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
176 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
180 -- HYKS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
181 CREATE SEQUENCE HYKS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
182 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
183 CREATE TABLE hyks ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
184 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
185 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
186 river_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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
191 -- MAIN_VALUE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
192 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; |
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 CREATE TABLE main_value_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
195 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
196 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
197 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
198 ); |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
201 -- MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
202 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; |
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 CREATE TABLE main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
205 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
206 value NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
207 gauge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
208 named_value_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
209 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
210 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
214 -- NAMED_MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
215 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
216 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
217 CREATE TABLE named_main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
218 id NUMBER(38,0) NOT NULL, |
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 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
221 PRIMARY KEY (id) |
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 |
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 -- POSITIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
226 CREATE SEQUENCE POSITIONS_ID_SEQ; |
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 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
|
229 id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
230 value VARCHAR2(255 char), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
231 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
232 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
233 |
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 --- RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
236 CREATE SEQUENCE RANGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
237 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
238 CREATE TABLE ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
239 id NUMBER(38,0) NOT NULL, |
2367
418a0918863b
Fixed flys/issue415: Increased precision of a and b in ranges.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2355
diff
changeset
|
240 a NUMBER(38,10), |
418a0918863b
Fixed flys/issue415: Increased precision of a and b in ranges.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2355
diff
changeset
|
241 b NUMBER(38,10), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
242 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
243 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
244 ); |
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 -- RIVERS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
248 CREATE SEQUENCE RIVERS_ID_SEQ; |
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 CREATE TABLE rivers ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
251 id NUMBER(38,0) NOT NULL, |
3946
948c7289fc42
Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3470
diff
changeset
|
252 official_number NUMBER(38,0), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
253 km_up NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
254 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
255 wst_unit_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
256 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
257 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
258 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
259 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
260 -- TIME_INTERVALS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
261 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; |
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 CREATE TABLE time_intervals ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
264 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
265 start_time TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
266 stop_time TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
267 PRIMARY KEY (id) |
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 --- UNITS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
272 CREATE SEQUENCE UNITS_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 units ( |
2353
67e0371f6f40
Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2352
diff
changeset
|
275 id NUMBER(38,0) NOT NULL, |
2351
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
281 -- WST_COLUMN_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
282 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
283 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
284 CREATE TABLE wst_column_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
285 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
286 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
287 wst_q_range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
288 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
289 ); |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
292 -- WST_COLUMN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
293 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
294 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
295 CREATE TABLE wst_column_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
296 id NUMBER(38,0) NOT NULL, |
3338
e19a503e4150
Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3335
diff
changeset
|
297 position NUMBER(38,5), |
e19a503e4150
Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3335
diff
changeset
|
298 w NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
299 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
300 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
301 ); |
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 -- WST_COLUMNS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
305 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
306 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
307 CREATE TABLE wst_columns ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
308 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
309 description VARCHAR2(255), |
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 position NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
312 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
313 wst_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
314 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
315 ); |
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 -- WST_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
319 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
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 CREATE TABLE wst_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
322 id NUMBER(38,0) NOT NULL, |
3338
e19a503e4150
Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3335
diff
changeset
|
323 q NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
324 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
325 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
326 ); |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
329 -- WSTS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
330 CREATE SEQUENCE WSTS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
331 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
332 CREATE TABLE wsts ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
333 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
334 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
335 kind NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
336 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
337 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
338 ); |
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 |
2352 | 341 -- ADD CONSTRAINTs |
342 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges; | |
343 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; | |
344 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; | |
345 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; | |
346 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; | |
347 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections; | |
348 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines; | |
349 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
350 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
351 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables; | |
352 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
353 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges; | |
354 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
355 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges; | |
356 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks; | |
357 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations; | |
358 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; | |
359 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
360 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries; | |
361 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
362 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges; | |
363 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; | |
364 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; | |
365 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
366 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; | |
367 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns; | |
368 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges; | |
369 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns; | |
370 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
371 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts; | |
372 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES; | |
373 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
2355
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
374 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
375 -- VIEWS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
376 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
377 CREATE VIEW wst_value_table AS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
378 SELECT wcv.position AS position, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
379 w, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
380 (SELECT q |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
381 FROM wst_column_q_ranges wcqr |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
382 JOIN wst_q_ranges wqr |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
383 ON wcqr.wst_q_range_id = wqr.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
384 JOIN ranges r |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
385 ON r.id = wqr.range_id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
386 WHERE wcqr.wst_column_id = wc.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
387 AND wcv.position BETWEEN r.a AND r.b) AS q, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
388 wc.position AS column_pos, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
389 w.id AS wst_id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
390 FROM wst_column_values wcv |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
391 JOIN wst_columns wc |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
392 ON wcv.wst_column_id = wc.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
393 JOIN wsts w |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
394 ON wc.wst_id = w.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
395 ORDER BY wcv.position ASC, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
396 wc.position DESC; |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
397 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
398 -- view to select the w values of a WST |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
399 CREATE VIEW wst_w_values AS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
400 SELECT wcv.position AS km, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
401 wcv.w AS w, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
402 wc.position AS column_pos, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
403 w.id AS wst_id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
404 FROM wst_column_values wcv |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
405 JOIN wst_columns wc ON wcv.wst_column_id = wc.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
406 JOIN wsts w ON wc.wst_id = w.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
407 ORDER BY wcv.position, wc.position; |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
408 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
409 -- view to select the q values of a WST |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
410 CREATE VIEW wst_q_values AS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
411 SELECT wc.position AS column_pos, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
412 wqr.q AS q, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
413 r.a AS a, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
414 r.b AS b, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
415 wc.wst_id AS wst_id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
416 FROM wst_column_q_ranges wcqr |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
417 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
418 JOIN ranges r ON wqr.range_id = r.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
419 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
420 ORDER BY wc.position, wcqr.wst_column_id, r.a; |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
421 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
422 -- Views to make the 'Amtlichen Linien' easier to access. |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
423 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
424 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
425 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
426 SELECT w.river_id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
427 w.id AS wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
428 wc.id AS wst_column_id, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
429 wc.name AS name, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
430 wc.position AS wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
431 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
432 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
433 ON wc.wst_id = w.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
434 WHERE w.kind = 3; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
435 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
436 CREATE VIEW q_main_values |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
437 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
438 SELECT riv.id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
439 g.id AS gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
440 g.name AS gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
441 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
442 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
443 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
444 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name, |
3338
e19a503e4150
Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3335
diff
changeset
|
445 CAST(mv.value AS NUMERIC(38, 5)) AS value |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
446 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
447 JOIN named_main_values nmv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
448 ON mv.named_value_id = nmv.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
449 JOIN main_value_types mvt |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
450 ON nmv.type_id = mvt.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
451 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
452 ON mv.gauge_id = g.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
453 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
454 ON g.range_id = r.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
455 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
456 ON g.river_id = riv.id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
457 WHERE mvt.name = 'Q' |
3338
e19a503e4150
Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3335
diff
changeset
|
458 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5)); |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
459 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
460 CREATE VIEW official_q_values |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
461 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
462 SELECT ol.river_id AS river_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
463 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
464 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
465 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
466 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
467 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
468 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
469 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
470 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
471 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
472 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
473 JOIN q_main_values qmv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
474 ON ol.river_id = qmv.river_id |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
475 AND ol.name = qmv.name; |