Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle.sql @ 5026:bdef4c74d675 dami
Update migrators for changes made in the dami branch and add
oracle migrator.
Both are currently untested!
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Tue, 19 Feb 2013 14:59:44 +0100 |
parents | 20b6ebf23916 |
children | 5c07ec3e9e25 |
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), |
4776
20b6ebf23916
!!! FLYS backend schema change !!! Add column bfg_id column to discharge_tables.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4651
diff
changeset
|
94 bfg_id VARCHAR2(50), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
95 kind NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
96 gauge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
97 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
98 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
102 -- EDGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
103 CREATE SEQUENCE EDGES_ID_SEQ; |
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 CREATE TABLE edges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
106 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
107 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
108 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
109 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
113 -- GAUGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
114 CREATE SEQUENCE GAUGES_ID_SEQ; |
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 TABLE gauges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
117 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
118 aeo NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
119 datum NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
120 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
121 station NUMBER(38,2), |
2371
fe89d6cf55fb
Added support for official gauge numbers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2367
diff
changeset
|
122 official_number NUMBER(38,0), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
123 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
124 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
125 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
129 -- HYK_ENTRIES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
130 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
131 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
132 CREATE TABLE hyk_entries ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
133 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
134 km NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
135 measure TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
136 hyk_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
137 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
141 -- HYK_FLOW_ZONE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
142 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
143 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
144 CREATE TABLE hyk_flow_zone_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
145 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
146 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
147 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
148 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
152 -- HYK_FLOW_ZONES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
153 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
154 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
155 CREATE TABLE hyk_flow_zones ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
156 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
157 a NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
158 b NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
159 formation_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
160 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
161 primary key (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
165 -- HYK_FORMATIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
166 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
167 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
168 CREATE TABLE hyk_formations ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
169 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
170 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
171 distance_hf NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
172 distance_vl NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
173 distance_vr NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
174 formation_num NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
175 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
176 hyk_entry_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
177 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
181 -- HYKS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
182 CREATE SEQUENCE HYKS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
183 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
184 CREATE TABLE hyks ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
185 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
186 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
187 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
188 primary key (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
192 -- MAIN_VALUE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
193 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; |
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 CREATE TABLE main_value_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
196 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
197 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
198 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
202 -- MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
203 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
204 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
205 CREATE TABLE main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
206 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
207 value NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
208 gauge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
209 named_value_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
210 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
211 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
215 -- NAMED_MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
216 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
217 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
218 CREATE TABLE named_main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
219 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
220 name VARCHAR2(255), |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
226 -- POSITIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
227 CREATE SEQUENCE POSITIONS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
228 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
229 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
|
230 id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
231 value VARCHAR2(255 char), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
232 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
236 --- RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
237 CREATE SEQUENCE RANGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
238 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
239 CREATE TABLE ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
240 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
|
241 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
|
242 b NUMBER(38,10), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
243 river_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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
248 -- RIVERS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
249 CREATE SEQUENCE RIVERS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
250 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
251 CREATE TABLE rivers ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
252 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
|
253 official_number NUMBER(38,0), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
254 km_up NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
255 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
256 wst_unit_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
257 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
261 -- TIME_INTERVALS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
262 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; |
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 TABLE time_intervals ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
265 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
266 start_time TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
267 stop_time TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
268 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
272 --- UNITS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
273 CREATE SEQUENCE UNITS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
274 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
275 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
|
276 id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
277 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
278 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
282 -- WST_COLUMN_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
283 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; |
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 CREATE TABLE wst_column_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
286 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
287 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
288 wst_q_range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
289 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
293 -- WST_COLUMN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
294 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
295 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
296 CREATE TABLE wst_column_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
297 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
|
298 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
|
299 w NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
300 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
301 PRIMARY KEY (id) |
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 -- WST_COLUMNS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
306 CREATE SEQUENCE WST_COLUMNS_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 wst_columns ( |
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 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
311 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
312 position NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
313 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
314 wst_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
315 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
316 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
317 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
318 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
319 -- WST_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
320 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
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 CREATE TABLE wst_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
323 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
|
324 q NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
325 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
326 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
330 -- WSTS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
331 CREATE SEQUENCE WSTS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
332 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
333 CREATE TABLE wsts ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
334 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
335 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
336 kind NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
337 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
338 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
341 |
2352 | 342 -- ADD CONSTRAINTs |
343 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges; | |
344 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; | |
345 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; | |
346 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; | |
347 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; | |
348 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections; | |
349 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines; | |
350 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
351 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
352 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables; | |
353 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
354 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges; | |
355 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
356 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges; | |
357 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks; | |
358 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations; | |
359 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; | |
360 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
361 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries; | |
362 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
363 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges; | |
364 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; | |
365 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; | |
366 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers; | |
367 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; | |
368 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns; | |
369 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges; | |
370 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns; | |
371 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
372 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts; | |
373 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES; | |
374 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
|
375 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
376 -- VIEWS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
377 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
378 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
|
379 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
|
380 w, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
381 (SELECT q |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
382 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
|
383 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
|
384 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
|
385 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
|
386 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
|
387 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
|
388 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
|
389 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
|
390 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
|
391 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
|
392 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
|
393 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
|
394 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
|
395 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
|
396 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
|
397 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
|
398 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
399 -- 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
|
400 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
|
401 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
|
402 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
|
403 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
|
404 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
|
405 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
|
406 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
|
407 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
|
408 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
|
409 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
410 -- 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
|
411 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
|
412 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
|
413 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
|
414 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
|
415 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
|
416 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
|
417 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
|
418 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
|
419 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
|
420 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
|
421 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
|
422 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
423 -- 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
|
424 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
425 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
426 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
427 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
|
428 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
|
429 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
|
430 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
|
431 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
|
432 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
433 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
434 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
|
435 WHERE w.kind = 3; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
436 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
437 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
|
438 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
439 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
|
440 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
|
441 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
|
442 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
443 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
444 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
445 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
|
446 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
|
447 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
448 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
|
449 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
|
450 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
|
451 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
|
452 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
453 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
|
454 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
455 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
|
456 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
457 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
|
458 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
|
459 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
|
460 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
461 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
|
462 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
463 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
|
464 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
465 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
466 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
467 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
468 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
469 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
470 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
471 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
472 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
473 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
474 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
|
475 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
|
476 AND ol.name = qmv.name; |
4651
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
477 |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
478 CREATE VIEW wst_ranges |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
479 AS |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
480 SELECT wc.id AS wst_column_id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
481 wc.wst_id AS wst_id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
482 Min(wcv.position) AS a, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
483 Max(wcv.position) AS b |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
484 FROM wst_columns wc |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
485 JOIN wst_column_values wcv |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
486 ON wc.id = wcv.wst_column_id |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
487 GROUP BY wc.id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
488 wc.wst_id; |