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

annotate backend/doc/schema/oracle.sql @ 7076:7f600001c807 generator-refactoring

Add LTR inversion code to diagram generator. This code is used in serveral diagrams and as it modifies a whole diagram it should be central. (This should also make maintenance easier). This function can be called by processors to make sure that their data is plotted with an LTR waterflow.

author	Andre Heinecke <aheinecke@intevation.de>
date	Fri, 20 Sep 2013 16:33:22 +0200
parents	a8b065038c0c
children	3b93f439e954

rev	line source
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	1 -- ANNOTATION_TYPES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	3
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	4 CREATE TABLE annotation_types (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	5 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	6 name VARCHAR2(255) NOT NULL UNIQUE,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	7 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	8 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	9
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	10
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	11 -- ANNOTATIONS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	13
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	14 CREATE TABLE annotations (
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	15 id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	16 attribute_id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	17 edge_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	18 position_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	19 range_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	20 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	21 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	22 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	23
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	24
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	25 -- ATTRIBUTES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	27
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	28 CREATE TABLE attributes (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	29 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	30 value VARCHAR2(255) NOT NULL UNIQUE,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	31 primary key (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	32 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	33
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	34
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	35 -- CROSS_SECTION_LINES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	36 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	37
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	38 CREATE TABLE cross_section_lines (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	39 id NUMBER(38,0) NOT NULL,
6030 d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	40 km NUMBER(38,2) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	41 cross_section_id NUMBER(38,0) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	42 PRIMARY KEY (id),
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	43 UNIQUE (km, cross_section_id)
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	44 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	45
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	46
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	47 -- CROSS_SECTION_POINTS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	48 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	49
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	50 CREATE TABLE cross_section_points (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	51 id NUMBER(38,0) NOT NULL,
6030 d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	52 col_pos NUMBER(38,0) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	53 x NUMBER(38,2) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	54 y NUMBER(38,2) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	55 cross_section_line_id NUMBER(38,0) NOT NULL,
d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	56 PRIMARY KEY (id),
2351 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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	60 -- CROSS_SECTIONS
2855 9c2424073be0 Made SQL instructions upper case. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2371 diff changeset	61 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	62
2855 9c2424073be0 Made SQL instructions upper case. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2371 diff changeset	63 CREATE TABLE cross_sections (
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	64 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	65 description VARCHAR2(255),
6030 d12f920bbf00 Schema change: Add missing constraints to cross sections in Oracle, cosmetics Tom Gottfried <tom@intevation.de> parents: 6011 diff changeset	66 river_id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	67 time_interval_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	68 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	69 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	70
3339 790c12c55abb Backend: Added DB indices for fasten access to cross section points. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3338 diff changeset	71 -- 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	72 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	73 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	74 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	75 ON cross_section_points(cross_section_line_id);
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	76
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	77 -- DISCHARGE_TABLE_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	78 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	79
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	80 CREATE TABLE discharge_table_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	81 id NUMBER(38,0) NOT NULL,
5897 39cf5d4acf5d Schema change: increased precision of discharge_table_values.q to allow unique import of higher precision values Tom Gottfried <tom@intevation.de> parents: 5894 diff changeset	82 q NUMBER(38,4) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	83 w NUMBER(38,2) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	84 table_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	85 UNIQUE (table_id, q, w),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	86 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	87 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	88
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	89
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	90 -- DISCHARGE_TABLES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	91 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	92
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	93 CREATE TABLE discharge_tables (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	94 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	95 description VARCHAR2(255) NOT NULL,
4776 20b6ebf23916 !!! FLYS backend schema change !!! Add column bfg_id column to discharge_tables. Sascha L. Teichmann <teichmann@intevation.de> parents: 4651 diff changeset	96 bfg_id VARCHAR2(50),
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	97 kind NUMBER(38,0) NOT NULL DEFAULT 0,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	98 gauge_id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	99 time_interval_id NUMBER(38,0),
5883 4f35b34f4efa Schema change: add constraint to discharge tables Tom Gottfried <tom.gottfried@intevation.de> parents: 5838 diff changeset	100 PRIMARY KEY (id),
5923 131f5f58ff7d Schema change: leave it up to AFT whether time intervals are unique per gauge but do not accept duplicate bfg_id per gauge Tom Gottfried <tom@intevation.de> parents: 5915 diff changeset	101 UNIQUE(gauge_id, bfg_id, kind)
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	102 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	103
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 -- EDGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	106 CREATE SEQUENCE EDGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	107
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	108 CREATE TABLE edges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	109 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	110 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	111 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	112 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	113 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	114
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 -- GAUGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	117 CREATE SEQUENCE GAUGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	118
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	119 CREATE TABLE gauges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	120 id NUMBER(38,0) NOT NULL,
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	121 aeo NUMBER(38,2) NOT NULL,
2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	122 datum NUMBER(38,2) NOT NULL,
2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	123 name VARCHAR2(255) NOT NULL,
2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	124 station NUMBER(38,2) NOT NULL,
5894 62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs Tom Gottfried <tom@intevation.de> parents: 5893 diff changeset	125 official_number NUMBER(38,0),
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	126 range_id NUMBER(38,0) NOT NULL,
5894 62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs Tom Gottfried <tom@intevation.de> parents: 5893 diff changeset	127 -- TODO: remove river id here because range_id references river already
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	128 river_id NUMBER(38,0) NOT NULL,
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	129 PRIMARY KEY (id),
2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	130 UNIQUE (name, river_id),
5894 62e6598a2c4b Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs Tom Gottfried <tom@intevation.de> parents: 5893 diff changeset	131 UNIQUE (official_number, river_id),
5207 2919cdc4e858 corrected constraints on table gauges Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	132 UNIQUE (river_id, station)
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	133 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	134
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	135
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	136 -- HYK_ENTRIES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	137 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
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 CREATE TABLE hyk_entries (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	140 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	141 km NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	142 measure TIMESTAMP,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	143 hyk_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	144 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	145 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	146
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	147
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	148 -- HYK_FLOW_ZONE_TYPES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	149 CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
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 CREATE TABLE hyk_flow_zone_types (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	152 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	153 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	154 name VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	155 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	156 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	157
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	158
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	159 -- HYK_FLOW_ZONES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	160 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
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 CREATE TABLE hyk_flow_zones (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	163 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	164 a NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	165 b NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	166 formation_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	167 type_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	168 primary key (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	169 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	170
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	171
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	172 -- HYK_FORMATIONS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	173 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	174
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	175 CREATE TABLE hyk_formations (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	176 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	177 bottom NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	178 distance_hf NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	179 distance_vl NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	180 distance_vr NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	181 formation_num NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	182 top NUMBER(38,2),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	183 hyk_entry_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	184 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	185 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	186
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	187
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	188 -- HYKS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	189 CREATE SEQUENCE HYKS_ID_SEQ;
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 CREATE TABLE hyks (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	192 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	193 description VARCHAR2(255),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	194 river_id NUMBER(38,0),
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	195 primary key (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	196 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	197
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	198
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	199 -- MAIN_VALUE_TYPES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	200 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	201
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	202 CREATE TABLE main_value_types (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	203 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	204 name VARCHAR2(255) NOT NULL UNIQUE,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	205 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	206 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	207
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	208
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	209 -- MAIN_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	210 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
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 CREATE TABLE main_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	213 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	214 value NUMBER(38,2) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	215 gauge_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	216 named_value_id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	217 time_interval_id NUMBER(38,0),
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	218
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	219 -- TODO: better checks
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	220 UNIQUE (gauge_id, named_value_id, time_interval_id),
2351 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 -- NAMED_MAIN_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	226 CREATE SEQUENCE NAMED_MAIN_VALUES_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 named_main_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	229 id NUMBER(38,0) NOT NULL,
5915 4fafe8d147b2 Schema change: named_main_values.name is not unique, as we can have the same name for different types Tom Gottfried <tom@intevation.de> parents: 5897 diff changeset	230 name VARCHAR2(256) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	231 type_id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	232 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	236 -- POSITIONS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	237 CREATE SEQUENCE POSITIONS_ID_SEQ;
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 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	240 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	241 value VARCHAR2(255 char) NOT NULL UNIQUE,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	242 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	243 );
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 --- RANGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	247 CREATE SEQUENCE RANGES_ID_SEQ;
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 CREATE TABLE ranges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	250 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	251 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	252 b NUMBER(38,10),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	253 river_id NUMBER(38,0),
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	254 UNIQUE (river_id, a, b),
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	255 PRIMARY KEY (id)
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	256 );
5321 6dd354e7abfc SCHEMA CHANGE: removed bad triggers and foreign keys from schema Tom Gottfried <tom@intevation.de> parents: 5298 diff changeset	257
2351 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 -- RIVERS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	261 CREATE SEQUENCE RIVERS_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 rivers (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	264 id NUMBER(38,0) NOT NULL,
5893 c1abd48a03e2 Schema change: remove UNIQUE-constraint from rivers.official number as we have different representations of the same river (e.g. for sommer and winter) Tom Gottfried <tom@intevation.de> parents: 5883 diff changeset	265 official_number NUMBER(38,0),
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	266 km_up NUMBER(38,0) NOT NULL DEFAULT 0,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	267 name VARCHAR2(255) NOT NULL UNIQUE,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	268 wst_unit_id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	269 PRIMARY KEY (id)
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
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	272
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	273 -- TIME_INTERVALS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	274 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	275
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 5158 diff changeset	276 CREATE TABLE time_intervals (
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	277 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	278 start_time TIMESTAMP NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	279 stop_time TIMESTAMP,
5158 a9658d43b621 added missing constraints in schema (mostly for Oracle) Tom Gottfried <tom@intevation.de> parents: 5113 diff changeset	280 PRIMARY KEY (id),
a9658d43b621 added missing constraints in schema (mostly for Oracle) Tom Gottfried <tom@intevation.de> parents: 5113 diff changeset	281 CHECK (start_time <= stop_time)
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	282 );
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 --- UNITS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	286 CREATE SEQUENCE UNITS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	287
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	288 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	289 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	290 name VARCHAR2(255) NOT NULL UNIQUE,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	291 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	292 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	293
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	294
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	295 -- WST_COLUMN_Q_RANGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	296 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	297
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	298 CREATE TABLE wst_column_q_ranges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	299 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	300 wst_column_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	301 wst_q_range_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	302 UNIQUE (wst_column_id, wst_q_range_id),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	303 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	304 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	305
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	306
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	307 -- WST_COLUMN_VALUES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	308 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	309
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	310 CREATE TABLE wst_column_values (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	311 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	312 position NUMBER(38,5) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	313 w NUMBER(38,5) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	314 wst_column_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	315 UNIQUE (position, wst_column_id),
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	316 UNIQUE (position, wst_column_id, w),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	317 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	318 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	319
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	320
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	321 -- WST_COLUMNS
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	322 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	323
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	324 CREATE TABLE wst_columns (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	325 id NUMBER(38,0) NOT NULL,
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	326 description VARCHAR2(255),
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	327 name VARCHAR2(255) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	328 position NUMBER(38,0) NOT NULL DEFAULT 0,
6011 d9a1cf0438a5 backout 6009:6ea171b6d8d2 because this was only really valid for fixations Tom Gottfried <tom@intevation.de> parents: 6009 diff changeset	329 time_interval_id NUMBER(38,0),
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	330 wst_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	331 UNIQUE (wst_id, name),
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	332 UNIQUE (wst_id, position),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	333 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	334 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	335
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	336
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	337 -- WST_Q_RANGES
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	338 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
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 CREATE TABLE wst_q_ranges (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	341 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	342 q NUMBER(38,5) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	343 range_id NUMBER(38,0) NOT NULL,
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	344 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	345 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	346
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	347 -- OFFICIAL_LINES
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	348 CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	349
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	350 CREATE TABLE official_lines (
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	351 id NUMBER(38,0) NOT NULL,
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	352 wst_column_id NUMBER(38,0) NOT NULL,
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	353 named_main_value_id NUMBER(38,0) NOT NULL,
6367 a8b065038c0c Backend: Fixed Oracle scheme for official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6300 diff changeset	354 UNIQUE (wst_column_id, named_main_value_id),
a8b065038c0c Backend: Fixed Oracle scheme for official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6300 diff changeset	355 PRIMARY KEY (id)
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	356 );
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	357
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	358 -- WSTS
5224 2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	359 --lookup table for wst kinds
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	360 CREATE TABLE wst_kinds (
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	361 id NUMBER PRIMARY KEY NOT NULL,
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	362 kind VARCHAR(64) NOT NULL
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	363 );
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	364 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	365 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	366 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	367 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	368 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	369 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	370 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	371 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	372
2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5207 diff changeset	373
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	374 CREATE SEQUENCE WSTS_ID_SEQ;
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	375
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	376 CREATE TABLE wsts (
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	377 id NUMBER(38,0) NOT NULL,
5783 153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	378 description VARCHAR2(255) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	379 kind NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	380 river_id NUMBER(38,0) NOT NULL,
153456f84602 add missing constraints to Oracle Schema (issue426) and some cosmetics Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	381 UNIQUE (river_id, description),
2351 f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	382 PRIMARY KEY (id)
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	383 );
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	384
f993b735db71 Adjust oracle schema [I382] Bjoern Schilberg <bjoern@intevation.de> parents: 1255 diff changeset	385
2352 8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	386 -- 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	387 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	388 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	389 ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
8126ccb58f28 Fixed errors. Bjoern Schilberg <bjoern@intevation.de> parents: 2351 diff changeset	390 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	391 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	392 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	393 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	394 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	395 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	396 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	397 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	398 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	399
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres Andre Heinecke <aheinecke@intevation.de> parents: 4776 diff changeset	400 -- Cascading references
5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres Andre Heinecke <aheinecke@intevation.de> parents: 4776 diff changeset	401 ALTER TABLE 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	402 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	403 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	404 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	405 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	406 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	407 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	408 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	409 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	410 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	411 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	412 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	413 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	414 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	415 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	416 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	417 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	418 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	419 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON LETE CASCADE;
5113 5c07ec3e9e25 Add on delete cascade to the constraints that also cascade on postgres Andre Heinecke <aheinecke@intevation.de> parents: 4776 diff changeset	420 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	421 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	422
6300 0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	423 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	424 ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE;
0711ce5ca701 Backend: Modified schema for a better model of official lines. Sascha L. Teichmann <teichmann@intevation.de> parents: 6177 diff changeset	425
2355 cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	426 -- VIEWS
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	427
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	428 CREATE VIEW wst_value_table AS
6078 176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	429 SELECT
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	430 wcv.position AS position,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	431 w,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	432 q,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	433 wc.position AS column_pos,
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	434 w.id AS wst_id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	435 FROM wsts w
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	436 JOIN wst_columns wc
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	437 ON wc.wst_id=w.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	438 JOIN wst_column_q_ranges wcqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	439 ON wcqr.wst_column_id=wc.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	440 JOIN wst_q_ranges wqr
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	441 ON wcqr.wst_q_range_id=wqr.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	442 JOIN ranges r
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	443 ON wqr.range_id=r.id
176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	444 JOIN wst_column_values wcv
6177 573112007ec7 Remove stray semicolon from sql schema. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 6143 diff changeset	445 ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b
2355 cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	446 ORDER BY wcv.position ASC,
6078 176664f84d86 Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296) Tom Gottfried <tom.gottfried@intevation.de> parents: 6030 diff changeset	447 wc.position DESC;
2355 cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	448
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	449 -- 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	450 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	451 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	452 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	453 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	454 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	455 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	456 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	457 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	458 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	459
cf8dcfaeb756 Added missing views (wst_value_table,wst_w_values,wst_q_values). Bjoern Schilberg <bjoern@intevation.de> parents: 2354 diff changeset	460 -- 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	461 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	462 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	463 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	464 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	465 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	466 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	467 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	468 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	469 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	470 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	471 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	472
4651 83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	473 CREATE VIEW wst_ranges
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	474 AS
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	475 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	476 wc.wst_id AS wst_id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	477 Min(wcv.position) AS a,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	478 Max(wcv.position) AS b
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	479 FROM wst_columns wc
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	480 JOIN wst_column_values wcv
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	481 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	482 GROUP BY wc.id,
83a42e6a562d Added view for selecting ranges in wsts. Sascha L. Teichmann <teichmann@intevation.de> parents: 3946 diff changeset	483 wc.wst_id;

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle.sql @ 7076:7f600001c807 generator-refactoring