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

annotate flys-backend/doc/schema/oracle.sql @ 5251:8fa0c522bef3

Improve make_release script to optinally handle tagging and be more generic. It can now be used to prepare a fully configured version of flys. There are still some variables to set in the script or to be overwritten by environment variables.

author	Andre Heinecke <aheinecke@intevation.de>
date	Tue, 12 Mar 2013 16:52:55 +0100
parents	2594904640c3
children	4f3cc1aebcc0

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

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle.sql @ 5251:8fa0c522bef3