Mercurial > dive4elements > river

annotate backend/doc/schema/oracle.sql @ 6112:439809f4d2b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged theme config files default.xml and second.xml.
author Raimund Renkert <rrenkert@intevation.de>
date Mon, 27 May 2013 15:43:34 +0200
parents 176664f84d86
children 6cdb3ee687a9
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,
6030
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
40 km NUMBER(38,2) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
41 cross_section_id NUMBER(38,0) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
42 PRIMARY KEY (id),
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
43 UNIQUE (km, cross_section_id)
2351
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
47 -- CROSS_SECTION_POINTS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
48 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
49
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
50 CREATE TABLE cross_section_points (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
51 id NUMBER(38,0) NOT NULL,
6030
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
52 col_pos NUMBER(38,0) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
53 x NUMBER(38,2) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
54 y NUMBER(38,2) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
55 cross_section_line_id NUMBER(38,0) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
56 PRIMARY KEY (id),
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
57 UNIQUE (cross_section_line_id, col_pos)
2351
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
60
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
61 -- CROSS_SECTIONS
2855
9c2424073be0 Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2371
diff changeset
62 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
63
2855
9c2424073be0 Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2371
diff changeset
64 CREATE TABLE cross_sections (
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
65 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
66 description VARCHAR2(255),
6030
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics
Tom Gottfried <tom@intevation.de>
parents: 6011
diff changeset
67 river_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
68 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
69 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
70 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
71
3339
790c12c55abb Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3338
diff changeset
72 -- 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
73 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
74 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
75 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
76 ON cross_section_points(cross_section_line_id);
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
77
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
78 -- DISCHARGE_TABLE_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
79 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
80
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
81 CREATE TABLE discharge_table_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
82 id NUMBER(38,0) NOT NULL,
5897
39cf5d4acf5d Schema change: increased precision of discharge_table_values.q to allow unique import of higher precision values
Tom Gottfried <tom@intevation.de>
parents: 5894
diff changeset
83 q NUMBER(38,4) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
84 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
85 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
86 UNIQUE (table_id, q, w),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
87 PRIMARY KEY (id)
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
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 -- DISCHARGE_TABLES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
92 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
93
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
94 CREATE TABLE discharge_tables (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
95 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
96 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
97 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
98 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
99 gauge_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
100 time_interval_id NUMBER(38,0),
5883
4f35b34f4efa Schema change: add constraint to discharge tables
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
101 PRIMARY KEY (id),
5923
131f5f58ff7d Schema change: leave it up to AFT whether time intervals are unique per gauge but do not accept duplicate bfg_id per gauge
Tom Gottfried <tom@intevation.de>
parents: 5915
diff changeset
102 UNIQUE(gauge_id, bfg_id, kind)
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
103 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
104
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 -- EDGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
107 CREATE SEQUENCE EDGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
108
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
109 CREATE TABLE edges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
110 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
111 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
112 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
113 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
114 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
115
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 -- GAUGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
118 CREATE SEQUENCE GAUGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
119
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
120 CREATE TABLE gauges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
121 id NUMBER(38,0) NOT NULL,
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
122 aeo NUMBER(38,2) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
123 datum NUMBER(38,2) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
124 name VARCHAR2(255) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
125 station NUMBER(38,2) NOT NULL,
5894
62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
Tom Gottfried <tom@intevation.de>
parents: 5893
diff changeset
126 official_number NUMBER(38,0),
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
127 range_id NUMBER(38,0) NOT NULL,
5894
62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
Tom Gottfried <tom@intevation.de>
parents: 5893
diff changeset
128 -- TODO: 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
129 river_id NUMBER(38,0) NOT NULL,
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
130 PRIMARY KEY (id),
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
131 UNIQUE (name, river_id),
5894
62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
Tom Gottfried <tom@intevation.de>
parents: 5893
diff changeset
132 UNIQUE (official_number, river_id),
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
133 UNIQUE (river_id, station)
2351
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
136
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
137 -- HYK_ENTRIES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
138 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
139
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
140 CREATE TABLE hyk_entries (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
141 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
142 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
143 measure TIMESTAMP,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
144 hyk_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
145 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
148
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
149 -- HYK_FLOW_ZONE_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
150 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
151
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
152 CREATE TABLE hyk_flow_zone_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
153 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
154 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
155 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
156 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
159
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
160 -- HYK_FLOW_ZONES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
161 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
162
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
163 CREATE TABLE hyk_flow_zones (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
164 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
165 a NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
166 b NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
167 formation_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
168 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
169 primary key (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
172
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
173 -- HYK_FORMATIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
174 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
175
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
176 CREATE TABLE hyk_formations (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
177 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
178 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
179 distance_hf NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
180 distance_vl NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
181 distance_vr NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
182 formation_num NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
183 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
184 hyk_entry_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
185 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
188
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
189 -- HYKS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
190 CREATE SEQUENCE HYKS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
191
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
192 CREATE TABLE hyks (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
193 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
194 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
195 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
196 primary key (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
199
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
200 -- MAIN_VALUE_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
201 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
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 CREATE TABLE main_value_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
204 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
205 name VARCHAR2(255) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
206 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
209
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
210 -- MAIN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
211 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
212
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
213 CREATE TABLE main_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
214 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
215 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
216 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
217 named_value_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
218 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
219
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
220 -- TODO: better checks
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
221 UNIQUE (gauge_id, named_value_id, time_interval_id),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
222 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
223 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
224
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
225
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
226 -- NAMED_MAIN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
227 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
228
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
229 CREATE TABLE named_main_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
230 id NUMBER(38,0) NOT NULL,
5915
4fafe8d147b2 Schema change: named_main_values.name is not unique, as we can have the same name for different types
Tom Gottfried <tom@intevation.de>
parents: 5897
diff changeset
231 name VARCHAR2(256) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
232 type_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
233 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
234 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
235
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
236
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
237 -- POSITIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
238 CREATE SEQUENCE POSITIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
239
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
240 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
241 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
242 value VARCHAR2(255 char) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
243 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
244 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
245
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
246
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
247 --- RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
248 CREATE SEQUENCE RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
249
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
250 CREATE TABLE ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
251 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
252 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
253 b NUMBER(38,10),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
254 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
255 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
256 PRIMARY KEY (id)
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
257 );
5321
6dd354e7abfc SCHEMA CHANGE: removed bad triggers and foreign keys from schema
Tom Gottfried <tom@intevation.de>
parents: 5298
diff changeset
258
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
259
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
260
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
261 -- RIVERS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
262 CREATE SEQUENCE RIVERS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
263
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
264 CREATE TABLE rivers (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
265 id NUMBER(38,0) NOT NULL,
5893
c1abd48a03e2 Schema change: remove UNIQUE-constraint from rivers.official number as we have different representations of the same river (e.g. for sommer and winter)
Tom Gottfried <tom@intevation.de>
parents: 5883
diff changeset
266 official_number NUMBER(38,0),
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
267 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
268 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
269 wst_unit_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
270 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
271 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
272
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
273
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
274 -- TIME_INTERVALS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
275 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
276
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5158
diff changeset
277 CREATE TABLE time_intervals (
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
278 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
279 start_time TIMESTAMP NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
280 stop_time TIMESTAMP,
5158
a9658d43b621 added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents: 5113
diff changeset
281 PRIMARY KEY (id),
a9658d43b621 added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents: 5113
diff changeset
282 CHECK (start_time <= stop_time)
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
283 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
284
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 --- UNITS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
287 CREATE SEQUENCE UNITS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
288
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
289 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
290 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
291 name VARCHAR2(255) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
292 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
293 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
294
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 -- WST_COLUMN_Q_RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
297 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
298
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
299 CREATE TABLE wst_column_q_ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
300 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
301 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
302 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
303 UNIQUE (wst_column_id, wst_q_range_id),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
304 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
305 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
306
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 -- WST_COLUMN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
309 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
310
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
311 CREATE TABLE wst_column_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
312 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
313 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
314 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
315 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
316 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
317 UNIQUE (position, wst_column_id, w),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
318 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
319 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
320
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 -- WST_COLUMNS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
323 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
324
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
325 CREATE TABLE wst_columns (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
326 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
327 description VARCHAR2(255),
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
328 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
329 position NUMBER(38,0) NOT NULL DEFAULT 0,
6011
d9a1cf0438a5 backout 6009:6ea171b6d8d2 because this was only really valid for fixations
Tom Gottfried <tom@intevation.de>
parents: 6009
diff changeset
330 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
331 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
332 UNIQUE (wst_id, name),
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
333 UNIQUE (wst_id, position),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
334 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
335 );
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
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
338 -- WST_Q_RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
339 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
340
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
341 CREATE TABLE wst_q_ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
342 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
343 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
344 range_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
345 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
346 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
347
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
348
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
349 -- WSTS
5224
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
350 --lookup table for wst kinds
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
351 CREATE TABLE wst_kinds (
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
352 id NUMBER PRIMARY KEY NOT NULL,
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
353 kind VARCHAR(64) NOT NULL
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
354 );
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 (0, 'basedata');
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 (1, 'basedata_additionals_marks');
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 (2, 'basedata_fixations_wst');
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 (3, 'basedata_officials');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
359 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
360 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
361 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
362 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
363
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
364
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
365 CREATE SEQUENCE WSTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
366
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
367 CREATE TABLE wsts (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
368 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
369 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
370 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
371 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
372 UNIQUE (river_id, description),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
373 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
374 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
375
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
376
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
377 -- 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
378 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
379 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
380 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
381 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
382 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
383 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
384 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
385 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
386 ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
387 ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
388 ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
389 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
390
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
391 -- Cascading references
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 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
393 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
394 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
395 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
396 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
397 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
398 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
399 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
400 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
401 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
402 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
403 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
404 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
405 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
406 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
407 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
408 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
409 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
410 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
411 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
412 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
413
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
414 -- VIEWS
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
415
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
416 CREATE VIEW wst_value_table AS
6078
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
417 SELECT
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
418 wcv.position AS position,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
419 w,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
420 q,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
421 wc.position AS column_pos,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
422 w.id AS wst_id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
423 FROM wsts w
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
424 JOIN wst_columns wc
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
425 ON wc.wst_id=w.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
426 JOIN wst_column_q_ranges wcqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
427 ON wcqr.wst_column_id=wc.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
428 JOIN wst_q_ranges wqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
429 ON wcqr.wst_q_range_id=wqr.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
430 JOIN ranges r
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
431 ON wqr.range_id=r.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
432 JOIN wst_column_values wcv
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
433 ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b;
2355
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
434 ORDER BY wcv.position ASC,
6078
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 6030
diff changeset
435 wc.position DESC;
2355
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
436
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
437 -- 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
438 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
439 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
440 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
441 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
442 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
443 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
444 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
445 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
446 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
447
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
448 -- 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
449 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
450 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
451 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
452 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
453 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
454 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
455 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
456 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
457 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
458 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
459 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
460
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
461 -- 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
462
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
463 CREATE VIEW official_lines
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
464 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
465 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
466 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
467 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
468 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
469 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
470 FROM wsts w
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
471 JOIN wst_columns wc
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
472 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
473 WHERE w.kind = 3;
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
474
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
475 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
476 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
477 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
478 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
479 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
480 r.a AS a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
481 r.b AS b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
482 REGEXP_REPLACE(
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
483 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
484 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
485 FROM main_values mv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
486 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
487 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
488 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
489 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
490 JOIN gauges g
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
491 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
492 JOIN ranges r
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
493 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
494 JOIN rivers riv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
495 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
496 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
497 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
498
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
499 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
500 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
501 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
502 wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
503 wst_column_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
504 gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
505 gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
506 a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
507 b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
508 ol.name,
3470
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
509 value,
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
510 wst_column_pos
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
511 FROM official_lines ol
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
512 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
513 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
514 AND ol.name = qmv.name;
4651
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
515
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
516 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
517 AS
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
518 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
519 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
520 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
521 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
522 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
523 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
524 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
525 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
526 wc.wst_id;
http://dive4elements.wald.intevation.org