Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle.sql @ 3946:948c7289fc42

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