Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle.sql @ 5161:a8d8ea1f2a5c
flys/issue1145: Add missing Area styles.
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Fri, 01 Mar 2013 17:11:08 +0100 |
parents | a9658d43b621 |
children | 8667f629d238 |
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, |
5158
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
241 a NUMBER(38,10) 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
|
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 |
5158
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
264 CREATE TABLE time_intervals_ ( |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
265 id NUMBER(38,0) NOT NULL, |
5158
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
266 start_time TIMESTAMP NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
267 stop_time TIMESTAMP, |
5158
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
268 PRIMARY KEY (id), |
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
269 CHECK (start_time <= stop_time) |
2351
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
273 --- UNITS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
274 CREATE SEQUENCE UNITS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
275 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
276 CREATE TABLE units ( |
2353
67e0371f6f40
Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2352
diff
changeset
|
277 id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
278 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
279 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
283 -- WST_COLUMN_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
284 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
285 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
286 CREATE TABLE wst_column_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
287 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
288 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
289 wst_q_range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
290 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
294 -- WST_COLUMN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
295 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
296 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
297 CREATE TABLE wst_column_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
298 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
|
299 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
|
300 w NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
301 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
302 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
306 -- WST_COLUMNS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
307 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
308 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
309 CREATE TABLE wst_columns ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
310 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
311 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
312 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
313 position NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
314 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
315 wst_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
316 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
320 -- WST_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
321 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
322 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
323 CREATE TABLE wst_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
324 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
|
325 q NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
326 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
327 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
331 -- WSTS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
332 CREATE SEQUENCE WSTS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
333 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
334 CREATE TABLE wsts ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
335 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
336 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
337 kind NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
338 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
339 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
342 |
2352 | 343 -- ADD CONSTRAINTs |
5113
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
344 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; |
2352 | 345 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; |
346 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; | |
347 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; | |
348 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
349 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
350 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types; | |
5113
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
351 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; |
2352 | 352 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; |
353 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; | |
354 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; | |
355 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
5113
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
356 |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
357 -- Cascading references |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
358 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
359 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
360 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
361 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
362 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
363 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
364 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
365 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
366 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
367 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
368 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
369 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
370 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
371 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
372 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
373 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
374 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
375 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
376 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE; |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
377 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; |
2355
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
378 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
379 -- VIEWS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
380 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
381 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
|
382 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
|
383 w, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
384 (SELECT q |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
385 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
|
386 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
|
387 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
|
388 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
|
389 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
|
390 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
|
391 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
|
392 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
|
393 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
|
394 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
|
395 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
|
396 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
|
397 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
|
398 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
|
399 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
|
400 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
|
401 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
402 -- 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
|
403 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
|
404 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
|
405 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
|
406 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
|
407 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
|
408 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
|
409 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
|
410 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
|
411 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
|
412 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
413 -- 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
|
414 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
|
415 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
|
416 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
|
417 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
|
418 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
|
419 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
|
420 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
|
421 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
|
422 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
|
423 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
|
424 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
|
425 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
426 -- 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
|
427 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
428 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
429 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
430 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
|
431 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
|
432 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
|
433 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
|
434 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
|
435 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
436 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
437 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
|
438 WHERE w.kind = 3; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
439 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
440 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
|
441 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
442 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
|
443 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
|
444 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
|
445 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
446 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
447 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
448 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
|
449 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
|
450 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
451 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
|
452 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
|
453 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
|
454 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
|
455 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
456 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
|
457 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
458 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
|
459 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
460 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
|
461 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
|
462 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
|
463 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
464 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
|
465 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
466 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
|
467 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
468 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
469 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
470 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
471 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
472 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
473 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
474 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
475 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
476 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
477 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
|
478 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
|
479 AND ol.name = qmv.name; |
4651
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
480 |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
481 CREATE VIEW wst_ranges |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
482 AS |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
483 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
|
484 wc.wst_id AS wst_id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
485 Min(wcv.position) AS a, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
486 Max(wcv.position) AS b |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
487 FROM wst_columns wc |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
488 JOIN wst_column_values wcv |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
489 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
|
490 GROUP BY wc.id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
491 wc.wst_id; |