Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle.sql @ 5818:a4ff4167be1e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Request feature info on all layers and show it as html if the server does not return valid gml. Non queryable layers produce an error message when the request fails. This is good enough
author Andre Heinecke <aheinecke@intevation.de>
date Wed, 24 Apr 2013 17:33:27 +0200
parents 153456f84602
children
rev   line source
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
1 -- ANNOTATION_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
3
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
4 CREATE TABLE annotation_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
5 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
6 name VARCHAR2(255) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
7 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
8 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
9
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
10
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
11 -- ANNOTATIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
13
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
14 CREATE TABLE annotations (
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
15 id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
16 attribute_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
17 edge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
18 position_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
19 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
20 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
21 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
22 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
23
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
24
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
25 -- ATTRIBUTES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
27
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
28 CREATE TABLE attributes (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
29 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
30 value VARCHAR2(255) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
31 primary key (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
32 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
33
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
34
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
35 -- CROSS_SECTION_LINES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
36 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
37
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
38 CREATE TABLE cross_section_lines (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
39 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
40 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
41 cross_section_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
42 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
43 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
44
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
45
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
46 -- CROSS_SECTION_POINTS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
47 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
48
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
49 CREATE TABLE cross_section_points (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
50 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
51 col_pos NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
52 x NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
53 y NUMBER(38,2),
2353
67e0371f6f40 Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2352
diff changeset
54 cross_section_line_id NUMBER(38,0),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
55 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
56 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
57
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
58
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
59 -- CROSS_SECTIONS
2855
9c2424073be0 Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2371
diff changeset
60 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
61
2855
9c2424073be0 Made SQL instructions upper case.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 2371
diff changeset
62 CREATE TABLE cross_sections (
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
63 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
64 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
65 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
66 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
67 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
68 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
69
3339
790c12c55abb Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3338
diff changeset
70 -- Indices for faster access of the points
790c12c55abb Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3338
diff changeset
71 CREATE INDEX cross_section_lines_km_idx
790c12c55abb Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3338
diff changeset
72 ON cross_section_lines(km);
790c12c55abb Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3338
diff changeset
73 CREATE INDEX cross_section_points_line_idx
790c12c55abb Backend: Added DB indices for fasten access to cross section points.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3338
diff changeset
74 ON cross_section_points(cross_section_line_id);
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
75
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
76 -- DISCHARGE_TABLE_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
78
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
79 CREATE TABLE discharge_table_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
80 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
81 q NUMBER(38,2) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
82 w NUMBER(38,2) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
83 table_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
84 UNIQUE (table_id, q, w),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
85 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
86 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
87
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
88
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
89 -- DISCHARGE_TABLES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
90 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
91
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
92 CREATE TABLE discharge_tables (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
93 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
94 description VARCHAR2(255) NOT NULL,
4776
20b6ebf23916 !!! FLYS backend schema change !!! Add column bfg_id column to discharge_tables.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4651
diff changeset
95 bfg_id VARCHAR2(50),
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
96 kind NUMBER(38,0) NOT NULL DEFAULT 0,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
97 gauge_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
98 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
99 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
100 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
101
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
102
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
103 -- EDGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
104 CREATE SEQUENCE EDGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
105
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
106 CREATE TABLE edges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
107 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
108 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
109 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
110 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
111 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
112
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
113
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
114 -- GAUGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
115 CREATE SEQUENCE GAUGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
116
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
117 CREATE TABLE gauges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
118 id NUMBER(38,0) NOT NULL,
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
119 aeo NUMBER(38,2) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
120 datum NUMBER(38,2) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
121 name VARCHAR2(255) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
122 station NUMBER(38,2) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
123 official_number NUMBER(38,0) UNIQUE,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
124 range_id NUMBER(38,0) NOT NULL,
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
125 -- remove river id here because range_id references river already
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
126 river_id NUMBER(38,0) NOT NULL,
5207
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
127 PRIMARY KEY (id),
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
128 UNIQUE (name, river_id),
2919cdc4e858 corrected constraints on table gauges
Tom Gottfried <tom@intevation.de>
parents: 5206
diff changeset
129 UNIQUE (river_id, station)
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
130 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
131
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
132
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
133 -- HYK_ENTRIES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
134 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
135
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
136 CREATE TABLE hyk_entries (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
137 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
138 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
139 measure TIMESTAMP,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
140 hyk_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
141 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
142 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
143
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
144
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
145 -- HYK_FLOW_ZONE_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
146 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
147
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
148 CREATE TABLE hyk_flow_zone_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
149 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
150 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
151 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
152 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
153 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
154
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
155
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
156 -- HYK_FLOW_ZONES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
157 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
158
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
159 CREATE TABLE hyk_flow_zones (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
160 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
161 a NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
162 b NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
163 formation_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
164 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
165 primary key (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
166 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
167
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
168
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
169 -- HYK_FORMATIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
170 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
171
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
172 CREATE TABLE hyk_formations (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
173 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
174 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
175 distance_hf NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
176 distance_vl NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
177 distance_vr NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
178 formation_num NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
179 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
180 hyk_entry_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
181 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
182 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
183
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
184
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
185 -- HYKS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
186 CREATE SEQUENCE HYKS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
187
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
188 CREATE TABLE hyks (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
189 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
190 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
191 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
192 primary key (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
193 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
194
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
195
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
196 -- MAIN_VALUE_TYPES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
197 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
198
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
199 CREATE TABLE main_value_types (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
200 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
201 name VARCHAR2(255) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
202 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
203 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
204
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
205
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
206 -- MAIN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
207 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
208
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
209 CREATE TABLE main_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
210 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
211 value NUMBER(38,2) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
212 gauge_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
213 named_value_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
214 time_interval_id NUMBER(38,0),
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
215
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
216 -- TODO: better checks
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
217 UNIQUE (gauge_id, named_value_id, time_interval_id),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
218 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
219 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
220
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
221
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
222 -- NAMED_MAIN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
223 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
224
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
225 CREATE TABLE named_main_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
226 id NUMBER(38,0) NOT NULL,
5684
88cbe798cbab Schema change: fixed constraints
Tom Gottfried <tom@intevation.de>
parents: 5321
diff changeset
227 name VARCHAR2(256) NOT NULL UNIQUE,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
228 type_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
229 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
230 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
231
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
232
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
233 -- POSITIONS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
234 CREATE SEQUENCE POSITIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
235
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
236 CREATE TABLE positions (
2353
67e0371f6f40 Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2352
diff changeset
237 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
238 value VARCHAR2(255 char) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
239 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
240 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
241
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
242
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
243 --- RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
244 CREATE SEQUENCE RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
245
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
246 CREATE TABLE ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
247 id NUMBER(38,0) NOT NULL,
5158
a9658d43b621 added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents: 5113
diff changeset
248 a NUMBER(38,10) NOT NULL,
2367
418a0918863b Fixed flys/issue415: Increased precision of a and b in ranges.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2355
diff changeset
249 b NUMBER(38,10),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
250 river_id NUMBER(38,0),
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
251 UNIQUE (river_id, a, b),
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
252 PRIMARY KEY (id)
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
253 );
5321
6dd354e7abfc SCHEMA CHANGE: removed bad triggers and foreign keys from schema
Tom Gottfried <tom@intevation.de>
parents: 5298
diff changeset
254
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
255
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
256
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
257 -- RIVERS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
258 CREATE SEQUENCE RIVERS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
259
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
260 CREATE TABLE rivers (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
261 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
262 official_number NUMBER(38,0) UNIQUE,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
263 km_up NUMBER(38,0) NOT NULL DEFAULT 0,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
264 name VARCHAR2(255) NOT NULL UNIQUE,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
265 wst_unit_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
266 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
267 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
268
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
269
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
270 -- TIME_INTERVALS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
271 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
272
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5158
diff changeset
273 CREATE TABLE time_intervals (
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
274 id NUMBER(38,0) NOT NULL,
5158
a9658d43b621 added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents: 5113
diff changeset
275 start_time TIMESTAMP NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
276 stop_time TIMESTAMP,
5158
a9658d43b621 added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents: 5113
diff changeset
277 PRIMARY KEY (id),
a9658d43b621 added missing constraints in schema (mostly for Oracle)
Tom Gottfried <tom@intevation.de>
parents: 5113
diff changeset
278 CHECK (start_time <= stop_time)
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
279 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
280
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
281
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
282 --- UNITS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
283 CREATE SEQUENCE UNITS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
284
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
285 CREATE TABLE units (
2353
67e0371f6f40 Adjusted NUMBER format for units, positions and cross_section_points tables.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2352
diff changeset
286 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
287 name VARCHAR2(255) NOT NULL UNIQUE,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
288 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
289 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
290
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
291
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
292 -- WST_COLUMN_Q_RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
293 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
294
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
295 CREATE TABLE wst_column_q_ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
296 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
297 wst_column_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
298 wst_q_range_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
299 UNIQUE (wst_column_id, wst_q_range_id),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
300 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
301 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
302
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
303
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
304 -- WST_COLUMN_VALUES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
305 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
306
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
307 CREATE TABLE wst_column_values (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
308 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
309 position NUMBER(38,5) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
310 w NUMBER(38,5) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
311 wst_column_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
312 UNIQUE (position, wst_column_id),
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
313 UNIQUE (position, wst_column_id, w),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
314 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
315 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
316
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
317
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
318 -- WST_COLUMNS
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
319 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
320
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
321 CREATE TABLE wst_columns (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
322 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
323 description VARCHAR2(255),
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
324 name VARCHAR2(255) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
325 position NUMBER(38,0) NOT NULL DEFAULT 0,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
326 time_interval_id NUMBER(38,0),
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
327 wst_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
328 UNIQUE (wst_id, name),
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
329 UNIQUE (wst_id, position),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
330 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
331 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
332
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
333
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
334 -- WST_Q_RANGES
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
335 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
336
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
337 CREATE TABLE wst_q_ranges (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
338 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
339 q NUMBER(38,5) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
340 range_id NUMBER(38,0) NOT NULL,
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
341 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
342 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
343
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
344
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
345 -- WSTS
5224
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
346 --lookup table for wst kinds
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
347 CREATE TABLE wst_kinds (
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
348 id NUMBER PRIMARY KEY NOT NULL,
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
349 kind VARCHAR(64) NOT NULL
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
350 );
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
351 INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
352 INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
353 INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
354 INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
355 INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
356 INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
357 INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
358 INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
359
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
360
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
361 CREATE SEQUENCE WSTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
362
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
363 CREATE TABLE wsts (
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
364 id NUMBER(38,0) NOT NULL,
5783
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
365 description VARCHAR2(255) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
366 kind NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
367 river_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics
Tom Gottfried <tom@intevation.de>
parents: 5684
diff changeset
368 UNIQUE (river_id, description),
2351
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
369 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
370 );
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
371
f993b735db71 Adjust oracle schema [I382]
Bjoern Schilberg <bjoern@intevation.de>
parents: 1255
diff changeset
372
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
373 -- ADD CONSTRAINTs
5113
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
374 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
375 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
376 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
377 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
378 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
379 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
380 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
5113
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
381 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
2352
8126ccb58f28 Fixed errors.
Bjoern Schilberg <bjoern@intevation.de>
parents: 2351
diff changeset
382 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
383 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
384 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
385 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
5113
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
386
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
387 -- Cascading references
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
388 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
389 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
390 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
391 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
392 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
393 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
394 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
395 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
396 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
397 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
398 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
399 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
400 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
401 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
402 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
403 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
404 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
405 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
406 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE;
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres
Andre Heinecke <aheinecke@intevation.de>
parents: 4776
diff changeset
407 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
5224
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle
Tom Gottfried <tom@intevation.de>
parents: 5207
diff changeset
408 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
2355
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
409
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
410 -- VIEWS
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
411
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
412 CREATE VIEW wst_value_table AS
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
413 SELECT wcv.position AS position,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
414 w,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
415 (SELECT q
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
416 FROM wst_column_q_ranges wcqr
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
417 JOIN wst_q_ranges wqr
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
418 ON wcqr.wst_q_range_id = wqr.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
419 JOIN ranges r
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
420 ON r.id = wqr.range_id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
421 WHERE wcqr.wst_column_id = wc.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
422 AND wcv.position BETWEEN r.a AND r.b) AS q,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
423 wc.position AS column_pos,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
424 w.id AS wst_id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
425 FROM wst_column_values wcv
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
426 JOIN wst_columns wc
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
427 ON wcv.wst_column_id = wc.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
428 JOIN wsts w
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
429 ON wc.wst_id = w.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
430 ORDER BY wcv.position ASC,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
431 wc.position DESC;
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
432
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
433 -- view to select the w values of a WST
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
434 CREATE VIEW wst_w_values AS
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
435 SELECT wcv.position AS km,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
436 wcv.w AS w,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
437 wc.position AS column_pos,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
438 w.id AS wst_id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
439 FROM wst_column_values wcv
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
440 JOIN wst_columns wc ON wcv.wst_column_id = wc.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
441 JOIN wsts w ON wc.wst_id = w.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
442 ORDER BY wcv.position, wc.position;
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
443
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
444 -- view to select the q values of a WST
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
445 CREATE VIEW wst_q_values AS
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
446 SELECT wc.position AS column_pos,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
447 wqr.q AS q,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
448 r.a AS a,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
449 r.b AS b,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
450 wc.wst_id AS wst_id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
451 FROM wst_column_q_ranges wcqr
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
452 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
453 JOIN ranges r ON wqr.range_id = r.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
454 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values).
Bjoern Schilberg <bjoern@intevation.de>
parents: 2354
diff changeset
455 ORDER BY wc.position, wcqr.wst_column_id, r.a;
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
456
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
457 -- Views to make the 'Amtlichen Linien' easier to access.
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
458
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
459 CREATE VIEW official_lines
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
460 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
461 SELECT w.river_id AS river_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
462 w.id AS wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
463 wc.id AS wst_column_id,
3470
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
464 wc.name AS name,
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
465 wc.position AS wst_column_pos
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
466 FROM wsts w
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
467 JOIN wst_columns wc
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
468 ON wc.wst_id = w.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
469 WHERE w.kind = 3;
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
470
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
471 CREATE VIEW q_main_values
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
472 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
473 SELECT riv.id AS river_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
474 g.id AS gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
475 g.name AS gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
476 r.a AS a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
477 r.b AS b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
478 REGEXP_REPLACE(
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
479 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name,
3338
e19a503e4150 Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3335
diff changeset
480 CAST(mv.value AS NUMERIC(38, 5)) AS value
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
481 FROM main_values mv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
482 JOIN named_main_values nmv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
483 ON mv.named_value_id = nmv.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
484 JOIN main_value_types mvt
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
485 ON nmv.type_id = mvt.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
486 JOIN gauges g
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
487 ON mv.gauge_id = g.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
488 JOIN ranges r
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
489 ON g.range_id = r.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
490 JOIN rivers riv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
491 ON g.river_id = riv.id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
492 WHERE mvt.name = 'Q'
3338
e19a503e4150 Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3335
diff changeset
493 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5));
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
494
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
495 CREATE VIEW official_q_values
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
496 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
497 SELECT ol.river_id AS river_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
498 wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
499 wst_column_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
500 gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
501 gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
502 a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
503 b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
504 ol.name,
3470
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
505 value,
3442304b430a Adjusted official lines views schemata to include wst column pos.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 3339
diff changeset
506 wst_column_pos
3335
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
507 FROM official_lines ol
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
508 JOIN q_main_values qmv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
509 ON ol.river_id = qmv.river_id
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2855
diff changeset
510 AND ol.name = qmv.name;
4651
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
511
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
512 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
513 AS
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
514 SELECT wc.id AS wst_column_id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
515 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
516 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
517 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
518 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
519 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
520 ON wc.id = wcv.wst_column_id
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
521 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 3946
diff changeset
522 wc.wst_id;
http://dive4elements.wald.intevation.org