Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle.sql @ 5797:cce12c06466f
RelativePointFacet: Through minor refactoring prepared to used StaticWQKmsArtifact.
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Wed, 24 Apr 2013 08:17:18 +0200 |
parents | 153456f84602 |
children |
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, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
6 name VARCHAR2(255) NOT NULL UNIQUE, |
2351
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 ( |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
15 id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
16 attribute_id NUMBER(38,0) NOT NULL, |
2351
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, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
30 value VARCHAR2(255) NOT NULL UNIQUE, |
2351
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, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
81 q NUMBER(38,2) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
82 w NUMBER(38,2) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
83 table_id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
84 UNIQUE (table_id, q, w), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
85 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
89 -- DISCHARGE_TABLES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
90 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
91 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
92 CREATE TABLE discharge_tables ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
93 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
94 description VARCHAR2(255) NOT NULL, |
4776
20b6ebf23916
!!! FLYS backend schema change !!! Add column bfg_id column to discharge_tables.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4651
diff
changeset
|
95 bfg_id VARCHAR2(50), |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
96 kind NUMBER(38,0) NOT NULL DEFAULT 0, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
97 gauge_id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
98 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
99 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
103 -- EDGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
104 CREATE SEQUENCE EDGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
105 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
106 CREATE TABLE edges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
107 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
108 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
109 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
110 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
114 -- GAUGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
115 CREATE SEQUENCE GAUGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
116 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
117 CREATE TABLE gauges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
118 id NUMBER(38,0) NOT NULL, |
5207
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
119 aeo NUMBER(38,2) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
120 datum NUMBER(38,2) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
121 name VARCHAR2(255) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
122 station NUMBER(38,2) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
123 official_number NUMBER(38,0) UNIQUE, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
124 range_id NUMBER(38,0) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
125 -- remove river id here because range_id references river already |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
126 river_id NUMBER(38,0) NOT NULL, |
5207
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
127 PRIMARY KEY (id), |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
128 UNIQUE (name, river_id), |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
129 UNIQUE (river_id, station) |
2351
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
132 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
133 -- HYK_ENTRIES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
134 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
135 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
136 CREATE TABLE hyk_entries ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
137 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
138 km NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
139 measure TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
140 hyk_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
141 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
144 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
145 -- HYK_FLOW_ZONE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
146 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
147 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
148 CREATE TABLE hyk_flow_zone_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
149 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
150 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
151 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
152 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
155 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
156 -- HYK_FLOW_ZONES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
157 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
158 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
159 CREATE TABLE hyk_flow_zones ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
160 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
161 a NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
162 b NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
163 formation_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
164 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
165 primary key (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
168 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
169 -- HYK_FORMATIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
170 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
171 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
172 CREATE TABLE hyk_formations ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
173 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
174 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
175 distance_hf NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
176 distance_vl NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
177 distance_vr NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
178 formation_num NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
179 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
180 hyk_entry_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
181 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
184 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
185 -- HYKS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
186 CREATE SEQUENCE HYKS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
187 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
188 CREATE TABLE hyks ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
189 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
190 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
191 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
192 primary key (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
195 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
196 -- MAIN_VALUE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
197 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; |
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 CREATE TABLE main_value_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
200 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
201 name VARCHAR2(255) NOT NULL UNIQUE, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
202 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
205 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
206 -- MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
207 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
208 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
209 CREATE TABLE main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
210 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
211 value NUMBER(38,2) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
212 gauge_id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
213 named_value_id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
214 time_interval_id NUMBER(38,0), |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
215 |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
216 -- TODO: better checks |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
217 UNIQUE (gauge_id, named_value_id, time_interval_id), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
218 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
219 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
220 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
221 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
222 -- NAMED_MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
223 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
224 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
225 CREATE TABLE named_main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
226 id NUMBER(38,0) NOT NULL, |
5684
88cbe798cbab
Schema change: fixed constraints
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
227 name VARCHAR2(256) NOT NULL UNIQUE, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
228 type_id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
229 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
230 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
231 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
232 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
233 -- POSITIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
234 CREATE SEQUENCE POSITIONS_ID_SEQ; |
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 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
|
237 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
238 value VARCHAR2(255 char) NOT NULL UNIQUE, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
239 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
240 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
241 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
242 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
243 --- RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
244 CREATE SEQUENCE RANGES_ID_SEQ; |
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 CREATE TABLE ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
247 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
|
248 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
|
249 b NUMBER(38,10), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
250 river_id NUMBER(38,0), |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
251 UNIQUE (river_id, a, b), |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
252 PRIMARY KEY (id) |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
253 ); |
5321
6dd354e7abfc
SCHEMA CHANGE: removed bad triggers and foreign keys from schema
Tom Gottfried <tom@intevation.de>
parents:
5298
diff
changeset
|
254 |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
255 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
256 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
257 -- RIVERS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
258 CREATE SEQUENCE RIVERS_ID_SEQ; |
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 CREATE TABLE rivers ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
261 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
262 official_number NUMBER(38,0) UNIQUE, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
263 km_up NUMBER(38,0) NOT NULL DEFAULT 0, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
264 name VARCHAR2(255) NOT NULL UNIQUE, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
265 wst_unit_id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
266 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
267 ); |
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 -- TIME_INTERVALS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
271 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
272 |
5206
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5158
diff
changeset
|
273 CREATE TABLE time_intervals ( |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
274 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
|
275 start_time TIMESTAMP NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
276 stop_time TIMESTAMP, |
5158
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
277 PRIMARY KEY (id), |
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
278 CHECK (start_time <= stop_time) |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
279 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
280 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
281 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
282 --- UNITS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
283 CREATE SEQUENCE UNITS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
284 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
285 CREATE TABLE units ( |
2353
67e0371f6f40
Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2352
diff
changeset
|
286 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
287 name VARCHAR2(255) NOT NULL UNIQUE, |
2351
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_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
293 CREATE SEQUENCE WST_COLUMN_Q_RANGES_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_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
296 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
297 wst_column_id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
298 wst_q_range_id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
299 UNIQUE (wst_column_id, wst_q_range_id), |
2351
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_COLUMN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
305 CREATE SEQUENCE WST_COLUMN_VALUES_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_column_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
308 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
309 position NUMBER(38,5) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
310 w NUMBER(38,5) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
311 wst_column_id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
312 UNIQUE (position, wst_column_id), |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
313 UNIQUE (position, wst_column_id, w), |
2351
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_COLUMNS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
319 CREATE SEQUENCE WST_COLUMNS_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_columns ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
322 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
323 description VARCHAR2(255), |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
324 name VARCHAR2(255) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
325 position NUMBER(38,0) NOT NULL DEFAULT 0, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
326 time_interval_id NUMBER(38,0), |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
327 wst_id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
328 UNIQUE (wst_id, name), |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
329 UNIQUE (wst_id, position), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
330 PRIMARY KEY (id) |
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 |
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 -- WST_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
335 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
336 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
337 CREATE TABLE wst_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
338 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
339 q NUMBER(38,5) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
340 range_id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
341 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
342 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
343 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
344 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
345 -- WSTS |
5224
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
346 --lookup table for wst kinds |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
347 CREATE TABLE wst_kinds ( |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
348 id NUMBER PRIMARY KEY NOT NULL, |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
349 kind VARCHAR(64) NOT NULL |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
350 ); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
351 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
352 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
353 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
354 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
355 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
356 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
357 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
358 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels'); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
359 |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
360 |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
361 CREATE SEQUENCE WSTS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
362 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
363 CREATE TABLE wsts ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
364 id NUMBER(38,0) NOT NULL, |
5783
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
365 description VARCHAR2(255) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
366 kind NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
367 river_id NUMBER(38,0) NOT NULL, |
153456f84602
add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents:
5684
diff
changeset
|
368 UNIQUE (river_id, description), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
369 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
370 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
371 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
372 |
2352 | 373 -- 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
|
374 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; |
2352 | 375 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; |
376 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; | |
377 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; | |
378 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
379 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
380 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
|
381 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; |
2352 | 382 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; |
383 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; | |
384 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; | |
385 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
|
386 |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
387 -- Cascading references |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
388 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
|
389 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
|
390 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
|
391 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
|
392 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
|
393 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
|
394 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
|
395 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
|
396 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
|
397 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
|
398 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
|
399 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
|
400 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
|
401 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
|
402 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
|
403 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
|
404 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
|
405 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
|
406 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
|
407 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE; |
5224
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
408 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; |
2355
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
409 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
410 -- VIEWS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
411 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
412 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
|
413 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
|
414 w, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
415 (SELECT q |
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 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
418 ON wcqr.wst_q_range_id = wqr.id |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
419 JOIN ranges r |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
420 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
|
421 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
|
422 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
|
423 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
|
424 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
|
425 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
|
426 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
|
427 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
|
428 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
|
429 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
|
430 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
|
431 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
|
432 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
433 -- 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
|
434 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
|
435 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
|
436 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
|
437 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
|
438 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
|
439 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
|
440 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
|
441 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
|
442 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
|
443 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
444 -- 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
|
445 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
|
446 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
|
447 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
|
448 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
|
449 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
|
450 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
|
451 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
|
452 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
|
453 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
|
454 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
|
455 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
|
456 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
457 -- 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
|
458 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
459 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
460 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
461 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
|
462 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
|
463 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
|
464 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
|
465 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
|
466 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
467 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
468 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
|
469 WHERE w.kind = 3; |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
470 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
471 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
|
472 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
473 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
|
474 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
|
475 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
|
476 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
477 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
478 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
479 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
|
480 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
|
481 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
482 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
|
483 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
|
484 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
|
485 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
|
486 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
487 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
|
488 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
489 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
|
490 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
491 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
|
492 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
|
493 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
|
494 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
495 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
|
496 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
497 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
|
498 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
499 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
500 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
501 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
502 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
503 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
504 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
505 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
506 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
507 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
508 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
|
509 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
|
510 AND ol.name = qmv.name; |
4651
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
511 |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
512 CREATE VIEW wst_ranges |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
513 AS |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
514 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
|
515 wc.wst_id AS wst_id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
516 Min(wcv.position) AS a, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
517 Max(wcv.position) AS b |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
518 FROM wst_columns wc |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
519 JOIN wst_column_values wcv |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
520 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
|
521 GROUP BY wc.id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
522 wc.wst_id; |