Mercurial > dive4elements > river
annotate flys-backend/doc/schema/oracle.sql @ 5452:5287440b57b3
merged.
author | Raimund Renkert <rrenkert@intevation.de> |
---|---|
date | Wed, 27 Mar 2013 11:48:53 +0100 |
parents | 6dd354e7abfc |
children | 88cbe798cbab |
rev | line source |
---|---|
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
1 -- ANNOTATION_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
3 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
4 CREATE TABLE annotation_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
5 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
6 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
7 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
8 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
9 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
10 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
11 -- ANNOTATIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
13 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
14 CREATE TABLE annotations ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
15 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
16 attribute_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
17 edge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
18 position_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
19 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
20 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
21 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
22 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
23 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
24 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
25 -- ATTRIBUTES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
27 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
28 CREATE TABLE attributes ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
29 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
30 value VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
31 primary key (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
32 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
33 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
34 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
35 -- CROSS_SECTION_LINES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
36 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
37 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
38 CREATE TABLE cross_section_lines ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
39 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
40 km NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
41 cross_section_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
42 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
43 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
44 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
45 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
46 -- CROSS_SECTION_POINTS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
47 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
48 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
49 CREATE TABLE cross_section_points ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
50 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
51 col_pos NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
52 x NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
53 y NUMBER(38,2), |
2353
67e0371f6f40
Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents:
2352
diff
changeset
|
54 cross_section_line_id NUMBER(38,0), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
55 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
56 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
57 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
58 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
59 -- CROSS_SECTIONS |
2855
9c2424073be0
Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2371
diff
changeset
|
60 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
61 |
2855
9c2424073be0
Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
2371
diff
changeset
|
62 CREATE TABLE cross_sections ( |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
63 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
64 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
65 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
66 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
67 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
68 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
69 |
3339
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
70 -- Indices for faster access of the points |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
71 CREATE INDEX cross_section_lines_km_idx |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
72 ON cross_section_lines(km); |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
73 CREATE INDEX cross_section_points_line_idx |
790c12c55abb
Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3338
diff
changeset
|
74 ON cross_section_points(cross_section_line_id); |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
75 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
76 -- DISCHARGE_TABLE_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
78 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
79 CREATE TABLE discharge_table_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
80 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
81 q NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
82 w NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
83 table_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
84 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
85 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
86 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
87 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
88 -- DISCHARGE_TABLES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
89 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
90 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
91 CREATE TABLE discharge_tables ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
92 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
93 description VARCHAR2(255), |
4776
20b6ebf23916
!!! FLYS backend schema change !!! Add column bfg_id column to discharge_tables.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4651
diff
changeset
|
94 bfg_id VARCHAR2(50), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
95 kind NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
96 gauge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
97 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
98 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
99 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
100 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
101 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
102 -- EDGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
103 CREATE SEQUENCE EDGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
104 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
105 CREATE TABLE edges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
106 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
107 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
108 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
109 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
110 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
111 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
112 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
113 -- GAUGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
114 CREATE SEQUENCE GAUGES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
115 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
116 CREATE TABLE gauges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
117 id NUMBER(38,0) NOT NULL, |
5207
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
118 aeo NUMBER(38,2) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
119 datum NUMBER(38,2) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
120 name VARCHAR2(255) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
121 station NUMBER(38,2) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
122 official_number NUMBER(38,0) UNIQUE, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
123 range_id NUMBER(38,0) NOT NULL, |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
124 -- remove river id here because range_id references river already |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
125 river_id NUMBER(38,0), |
5207
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
126 PRIMARY KEY (id), |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
127 UNIQUE (name, river_id), |
2919cdc4e858
corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents:
5206
diff
changeset
|
128 UNIQUE (river_id, station) |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
129 ); |
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 -- HYK_ENTRIES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
133 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
134 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
135 CREATE TABLE hyk_entries ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
136 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
137 km NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
138 measure TIMESTAMP, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
139 hyk_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
140 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
141 ); |
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 -- HYK_FLOW_ZONE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
145 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
146 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
147 CREATE TABLE hyk_flow_zone_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
148 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
149 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
150 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
151 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
152 ); |
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 -- HYK_FLOW_ZONES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
156 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
157 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
158 CREATE TABLE hyk_flow_zones ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
159 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
160 a NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
161 b NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
162 formation_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
163 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
164 primary key (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
165 ); |
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 -- HYK_FORMATIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
169 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
170 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
171 CREATE TABLE hyk_formations ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
172 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
173 bottom NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
174 distance_hf NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
175 distance_vl NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
176 distance_vr NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
177 formation_num NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
178 top NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
179 hyk_entry_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
180 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
181 ); |
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 -- HYKS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
185 CREATE SEQUENCE HYKS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
186 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
187 CREATE TABLE hyks ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
188 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
189 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
190 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
191 primary key (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
192 ); |
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 -- MAIN_VALUE_TYPES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
196 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
197 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
198 CREATE TABLE main_value_types ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
199 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
200 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
201 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
202 ); |
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 -- MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
206 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
207 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
208 CREATE TABLE main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
209 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
210 value NUMBER(38,2), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
211 gauge_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
212 named_value_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
213 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
214 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
215 ); |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
218 -- NAMED_MAIN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
219 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; |
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 CREATE TABLE named_main_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
222 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
223 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
224 type_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
225 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
226 ); |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
229 -- POSITIONS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
230 CREATE SEQUENCE POSITIONS_ID_SEQ; |
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 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
|
233 id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
234 value VARCHAR2(255 char), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
235 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
236 ); |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
239 --- RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
240 CREATE SEQUENCE RANGES_ID_SEQ; |
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 CREATE TABLE ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
243 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
|
244 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
|
245 b NUMBER(38,10), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
246 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
247 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
248 ); |
5321
6dd354e7abfc
SCHEMA CHANGE: removed bad triggers and foreign keys from schema
Tom Gottfried <tom@intevation.de>
parents:
5298
diff
changeset
|
249 |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
250 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
251 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
252 -- RIVERS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
253 CREATE SEQUENCE RIVERS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
254 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
255 CREATE TABLE rivers ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
256 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
|
257 official_number NUMBER(38,0), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
258 km_up NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
259 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
260 wst_unit_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
261 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
264 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
265 -- TIME_INTERVALS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
266 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
267 |
5206
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5158
diff
changeset
|
268 CREATE TABLE time_intervals ( |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
269 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
|
270 start_time TIMESTAMP NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
271 stop_time TIMESTAMP, |
5158
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
272 PRIMARY KEY (id), |
a9658d43b621
added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents:
5113
diff
changeset
|
273 CHECK (start_time <= stop_time) |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
274 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
275 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
276 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
277 --- UNITS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
278 CREATE SEQUENCE UNITS_ID_SEQ; |
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 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
|
281 id NUMBER(38,0) NOT NULL, |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
282 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
283 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
286 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
287 -- WST_COLUMN_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
288 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; |
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 CREATE TABLE wst_column_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
291 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
292 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
293 wst_q_range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
294 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
295 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
296 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
297 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
298 -- WST_COLUMN_VALUES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
299 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
300 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
301 CREATE TABLE wst_column_values ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
302 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
|
303 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
|
304 w NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
305 wst_column_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
306 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
307 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
308 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
309 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
310 -- WST_COLUMNS |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
311 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
312 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
313 CREATE TABLE wst_columns ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
314 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
315 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
316 name VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
317 position NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
318 time_interval_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
319 wst_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
320 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
321 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
322 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
323 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
324 -- WST_Q_RANGES |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
325 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
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 CREATE TABLE wst_q_ranges ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
328 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
|
329 q NUMBER(38,5), |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
330 range_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
331 PRIMARY KEY (id) |
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 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
335 -- WSTS |
5224
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
336 --lookup table for wst kinds |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
337 CREATE TABLE wst_kinds ( |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
338 id NUMBER PRIMARY KEY NOT NULL, |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
339 kind VARCHAR(64) NOT NULL |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
340 ); |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
341 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
|
342 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
|
343 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
|
344 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
|
345 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
|
346 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
|
347 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
|
348 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
|
349 |
2594904640c3
SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents:
5207
diff
changeset
|
350 |
2351
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
351 CREATE SEQUENCE WSTS_ID_SEQ; |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
352 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
353 CREATE TABLE wsts ( |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
354 id NUMBER(38,0) NOT NULL, |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
355 description VARCHAR2(255), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
356 kind NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
357 river_id NUMBER(38,0), |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
358 PRIMARY KEY (id) |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
359 ); |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
360 |
f993b735db71
Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents:
1255
diff
changeset
|
361 |
2352 | 362 -- 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
|
363 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes; |
2352 | 364 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges; |
365 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions; | |
366 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types; | |
367 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
368 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | |
369 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
|
370 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values; |
2352 | 371 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; |
372 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types; | |
373 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units; | |
374 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
|
375 |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
376 -- Cascading references |
5c07ec3e9e25
Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents:
4776
diff
changeset
|
377 ALTER TABLE 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
|
378 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
|
379 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
|
380 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
|
381 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
|
382 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
|
383 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
|
384 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
|
385 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
|
386 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
|
387 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
|
388 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
|
389 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
|
390 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
|
391 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
|
392 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
|
393 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
|
394 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
|
395 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
|
396 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
|
397 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
|
398 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
399 -- VIEWS |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
400 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
401 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
|
402 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
|
403 w, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
404 (SELECT q |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
405 FROM wst_column_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
|
406 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
|
407 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
|
408 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
|
409 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
|
410 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
|
411 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
|
412 wc.position AS column_pos, |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
413 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
|
414 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
|
415 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
|
416 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
|
417 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
|
418 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
|
419 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
|
420 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
|
421 |
cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents:
2354
diff
changeset
|
422 -- 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
|
423 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
|
424 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
|
425 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
|
426 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
|
427 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
|
428 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
|
429 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
|
430 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
|
431 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
|
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 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
|
434 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
|
435 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
|
436 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
|
437 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
|
438 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
|
439 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
|
440 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
|
441 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
|
442 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
|
443 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
|
444 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
|
445 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
446 -- 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
|
447 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
448 CREATE VIEW official_lines |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
449 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
450 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
|
451 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
|
452 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
|
453 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
|
454 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
|
455 FROM wsts w |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
456 JOIN wst_columns wc |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
457 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
|
458 WHERE w.kind = 3; |
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 q_main_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 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
|
463 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
|
464 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
|
465 r.a AS a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
466 r.b AS b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
467 REGEXP_REPLACE( |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
468 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
|
469 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
|
470 FROM main_values mv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
471 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
|
472 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
|
473 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
|
474 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
|
475 JOIN gauges g |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
476 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
|
477 JOIN ranges r |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
478 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
|
479 JOIN rivers riv |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
480 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
|
481 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
|
482 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
|
483 |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
484 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
|
485 AS |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
486 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
|
487 wst_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
488 wst_column_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
489 gauge_id, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
490 gauge_name, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
491 a, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
492 b, |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
493 ol.name, |
3470
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
494 value, |
3442304b430a
Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3339
diff
changeset
|
495 wst_column_pos |
3335
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
496 FROM official_lines ol |
c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2855
diff
changeset
|
497 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
|
498 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
|
499 AND ol.name = qmv.name; |
4651
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
500 |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
501 CREATE VIEW wst_ranges |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
502 AS |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
503 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
|
504 wc.wst_id AS wst_id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
505 Min(wcv.position) AS a, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
506 Max(wcv.position) AS b |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
507 FROM wst_columns wc |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
508 JOIN wst_column_values wcv |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
509 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
|
510 GROUP BY wc.id, |
83a42e6a562d
Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
3946
diff
changeset
|
511 wc.wst_id; |