dive4elements/river: backend/doc/schema/oracle.sql annotate

annotate backend/doc/schema/oracle.sql @ 6048:caf9d989bd84

Fix waterlevel classes and change defaults for them. If to is 0 then we create a class that goes to 9999 If step is 0 then we only have a single step >= 0 The defaults are now from 0, to 2m with a step of 0.5

author	Andre Heinecke <aheinecke@intevation.de>
date	Fri, 17 May 2013 17:56:20 +0200
parents	d12f920bbf00
children	176664f84d86

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
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	417 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	418 w,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	419 (SELECT q
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	420 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	421 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	422 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	423 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	424 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	425 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	426 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	427 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	428 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	429 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	430 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	431 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	432 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	433 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	434 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	435 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	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;

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle.sql @ 6048:caf9d989bd84