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

annotate flys-backend/doc/schema/oracle.sql @ 4380:19754e5227c8

Add facets for fixanalysis data from the datacage Add facets for fixanalysis data from the datacage to the bed difference height year, bed difference year and bed difference epoch output modes.

author	Björn Ricks <bjoern.ricks@intevation.de>
date	Fri, 02 Nov 2012 15:54:41 +0100
parents	948c7289fc42
children	83a42e6a562d

rev	line source
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	1 -- ANNOTATION_TYPES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	3
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	4 CREATE TABLE annotation_types (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	5 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	6 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	7 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	8 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	9
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	10
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	11 -- ANNOTATIONS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	13
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	14 CREATE TABLE annotations (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	15 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	16 attribute_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	17 edge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	18 position_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	19 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	20 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	21 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	22 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	23
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	24
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	25 -- ATTRIBUTES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	27
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	28 CREATE TABLE attributes (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	29 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	30 value VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	31 primary key (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	32 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	33
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	34
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	35 -- CROSS_SECTION_LINES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	36 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	37
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	38 CREATE TABLE cross_section_lines (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	39 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	40 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	41 cross_section_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	42 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	43 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	44
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	45
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	46 -- CROSS_SECTION_POINTS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	47 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	48
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	49 CREATE TABLE cross_section_points (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	50 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	51 col_pos NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	52 x NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	53 y NUMBER(38,2),
2353 67e0371f6f40 Adjusted NUMBER format for units, positions and cross_section_points tables. Bjoern Schilberg <bjoern@intevation.de> parents: 2352 diff changeset	54 cross_section_line_id NUMBER(38,0),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	55 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	56 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	57
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	58
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	59 -- CROSS_SECTIONS
2855 9c2424073be0 Made SQL instructions upper case. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2371 diff changeset	60 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	61
2855 9c2424073be0 Made SQL instructions upper case. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2371 diff changeset	62 CREATE TABLE cross_sections (
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	63 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	64 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	65 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	66 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	67 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	68 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	69
3339 790c12c55abb Backend: Added DB indices for fasten access to cross section points. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3338 diff changeset	70 -- Indices for faster access of the points
790c12c55abb Backend: Added DB indices for fasten access to cross section points. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3338 diff changeset	71 CREATE INDEX cross_section_lines_km_idx
790c12c55abb Backend: Added DB indices for fasten access to cross section points. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3338 diff changeset	72 ON cross_section_lines(km);
790c12c55abb Backend: Added DB indices for fasten access to cross section points. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3338 diff changeset	73 CREATE INDEX cross_section_points_line_idx
790c12c55abb Backend: Added DB indices for fasten access to cross section points. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3338 diff changeset	74 ON cross_section_points(cross_section_line_id);
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	75
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	76 -- DISCHARGE_TABLE_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	78
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	79 CREATE TABLE discharge_table_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	80 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	81 q NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	82 w NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	83 table_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	84 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	85 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	86
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	87
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	88 -- DISCHARGE_TABLES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	89 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	90
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	91 CREATE TABLE discharge_tables (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	92 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	93 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	94 kind NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	95 gauge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	96 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	97 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	98 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	99
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	100
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	101 -- EDGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	102 CREATE SEQUENCE EDGES_ID_SEQ;
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 CREATE TABLE edges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	105 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	106 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	107 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	108 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	109 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	110
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	111
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	112 -- GAUGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	113 CREATE SEQUENCE GAUGES_ID_SEQ;
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 CREATE TABLE gauges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	116 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	117 aeo NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	118 datum NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	119 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	120 station NUMBER(38,2),
2371 fe89d6cf55fb Added support for official gauge numbers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2367 diff changeset	121 official_number NUMBER(38,0),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	122 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	123 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	124 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	125 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	126
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	127
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	128 -- HYK_ENTRIES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	129 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
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 CREATE TABLE hyk_entries (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	132 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	133 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	134 measure TIMESTAMP,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	135 hyk_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	136 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	137 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	138
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 -- HYK_FLOW_ZONE_TYPES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	141 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
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 CREATE TABLE hyk_flow_zone_types (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	144 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	145 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	146 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	147 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	150
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	151 -- HYK_FLOW_ZONES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	152 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
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 CREATE TABLE hyk_flow_zones (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	155 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	156 a NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	157 b NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	158 formation_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	159 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	160 primary key (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	161 );
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	164 -- HYK_FORMATIONS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	165 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
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 CREATE TABLE hyk_formations (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	168 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	169 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	170 distance_hf NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	171 distance_vl NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	172 distance_vr NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	173 formation_num NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	174 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	175 hyk_entry_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	176 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	177 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	178
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	179
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	180 -- HYKS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	181 CREATE SEQUENCE HYKS_ID_SEQ;
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 CREATE TABLE hyks (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	184 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	185 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	186 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	187 primary key (id)
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	190
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	191 -- MAIN_VALUE_TYPES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	192 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
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 CREATE TABLE main_value_types (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	195 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	196 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	197 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	201 -- MAIN_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	202 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
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 CREATE TABLE main_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	205 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	206 value NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	207 gauge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	208 named_value_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	209 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	210 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	211 );
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	214 -- NAMED_MAIN_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	215 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	216
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	217 CREATE TABLE named_main_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	218 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	219 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	220 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	221 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	222 );
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 -- POSITIONS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	226 CREATE SEQUENCE POSITIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	227
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	228 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	229 id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	230 value VARCHAR2(255 char),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	231 PRIMARY KEY (id)
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
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 --- RANGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	236 CREATE SEQUENCE RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	237
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	238 CREATE TABLE ranges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	239 id NUMBER(38,0) 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	240 a NUMBER(38,10),
418a0918863b Fixed flys/issue415: Increased precision of a and b in ranges. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2355 diff changeset	241 b NUMBER(38,10),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	242 river_id NUMBER(38,0),
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 -- RIVERS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	248 CREATE SEQUENCE RIVERS_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 rivers (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	251 id NUMBER(38,0) NOT NULL,
3946 948c7289fc42 Backend: Added 'official_number' column to rivers table to model the 'Bundeswasserstrassen Identnummer'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3470 diff changeset	252 official_number NUMBER(38,0),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	253 km_up NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	254 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	255 wst_unit_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	256 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	257 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	258
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 -- TIME_INTERVALS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	261 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	262
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	263 CREATE TABLE time_intervals (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	264 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	265 start_time TIMESTAMP,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	266 stop_time TIMESTAMP,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	267 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	271 --- UNITS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	272 CREATE SEQUENCE UNITS_ID_SEQ;
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 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	275 id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	276 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	277 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	278 );
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 -- WST_COLUMN_Q_RANGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	282 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
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 CREATE TABLE wst_column_q_ranges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	285 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	286 wst_column_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	287 wst_q_range_id NUMBER(38,0),
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_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	293 CREATE SEQUENCE WST_COLUMN_VALUES_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_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	296 id NUMBER(38,0) NOT NULL,
3338 e19a503e4150 Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3335 diff changeset	297 position NUMBER(38,5),
e19a503e4150 Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3335 diff changeset	298 w NUMBER(38,5),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	299 wst_column_id NUMBER(38,0),
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_COLUMNS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	305 CREATE SEQUENCE WST_COLUMNS_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_columns (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	308 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	309 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	310 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	311 position NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	312 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	313 wst_id NUMBER(38,0),
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_Q_RANGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	319 CREATE SEQUENCE WST_Q_RANGES_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_q_ranges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	322 id NUMBER(38,0) NOT NULL,
3338 e19a503e4150 Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3335 diff changeset	323 q NUMBER(38,5),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	324 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	325 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	326 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	327
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	328
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	329 -- WSTS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	330 CREATE SEQUENCE WSTS_ID_SEQ;
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 CREATE TABLE wsts (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	333 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	334 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	335 kind NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	336 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	337 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	338 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	339
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	340
2352 8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	341 -- ADD CONSTRAINTs
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	342 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	343 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	344 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	345 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	346 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	347 ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	348 ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	349 ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	350 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	351 ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	352 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	353 ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	354 ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	355 ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	356 ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	357 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	358 ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	359 ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	360 ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	361 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	362 ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	363 ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	364 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	365 ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	366 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	367 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	368 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	369 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	370 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	371 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	372 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	373 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;
2355 cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	374
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	375 -- VIEWS
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	376
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	377 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	378 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	379 w,
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	380 (SELECT q
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	381 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	382 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	383 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	384 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	385 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	386 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	387 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	388 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	389 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	390 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	391 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	392 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	393 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	394 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	395 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	396 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	397
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	398 -- 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	399 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	400 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	401 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	402 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	403 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	404 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	405 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	406 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	407 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	408
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	409 -- 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	410 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	411 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	412 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	413 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	414 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	415 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	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 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	418 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	419 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	420 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	421
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	422 -- 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	423
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	424 CREATE VIEW official_lines
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	425 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	426 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	427 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	428 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	429 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	430 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	431 FROM wsts w
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	432 JOIN wst_columns wc
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	433 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	434 WHERE w.kind = 3;
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	435
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	436 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	437 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	438 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	439 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	440 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	441 r.a AS a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	442 r.b AS b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	443 REGEXP_REPLACE(
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	444 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	445 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	446 FROM main_values mv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	447 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	448 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	449 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	450 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	451 JOIN gauges g
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	452 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	453 JOIN ranges r
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	454 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	455 JOIN rivers riv
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	456 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	457 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	458 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	459
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	460 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	461 AS
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	462 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	463 wst_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	464 wst_column_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	465 gauge_id,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	466 gauge_name,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	467 a,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	468 b,
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	469 ol.name,
3470 3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	470 value,
3442304b430a Adjusted official lines views schemata to include wst column pos. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3339 diff changeset	471 wst_column_pos
3335 c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	472 FROM official_lines ol
c3e049961685 Backend: Added views to access the 'Amtlichen Linien'. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2855 diff changeset	473 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	474 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	475 AND ol.name = qmv.name;

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle.sql @ 4380:19754e5227c8