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

annotate backend/doc/schema/oracle-minfo.sql @ 9650:a2a42a6bac6b

Importer (s/u-info) extensions: outer try/catch for parse and log of line no, catching parsing exception if not enough value fields, parsing error and warning log messages with line number, detecting and rejecting duplicate data series, better differentiation between error and warning log messages

author	mschaefer
date	Mon, 23 Mar 2020 14:57:03 +0100
parents	94cddb00f934
children

rev	line source
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	1 SET AUTOCOMMIT ON;
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	2
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	4
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	5 CREATE TABLE location_system (
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	6 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	7 name VARCHAR(32) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	8 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	9 PRIMARY KEY(id)
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	10 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	11
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	12
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	14
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	15 CREATE TABLE elevation_model (
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	16 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	17 name VARCHAR(32) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	18 unit_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	19 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	21 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	22
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	23
5224 2594904640c3 SCHEMA CHANGE: analogue r5201. Here for Oracle Tom Gottfried <tom@intevation.de> parents: 5206 diff changeset	24 -- lookup table for bedheight types
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	25 CREATE TABLE bed_height_type (
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	26 id NUMBER(38,0) NOT NULL,
5273 db196abd16ca Use lookup table from bedd_height from database in importer. Sascha L. Teichmann <teichmann@intevation.de> parents: 5224 diff changeset	27 name VARCHAR(65) NOT NULL,
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	28 PRIMARY KEY(id)
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	29 );
5275 911f62917753 additional values in lookup table for bed height types Tom Gottfried <tom@intevation.de> parents: 5273 diff changeset	30 INSERT INTO bed_height_type VALUES (1, 'Querprofile');
911f62917753 additional values in lookup table for bed height types Tom Gottfried <tom@intevation.de> parents: 5273 diff changeset	31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung');
911f62917753 additional values in lookup table for bed height types Tom Gottfried <tom@intevation.de> parents: 5273 diff changeset	32 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen');
911f62917753 additional values in lookup table for bed height types Tom Gottfried <tom@intevation.de> parents: 5273 diff changeset	33 INSERT INTO bed_height_type VALUES (4, 'DGM');
911f62917753 additional values in lookup table for bed height types Tom Gottfried <tom@intevation.de> parents: 5273 diff changeset	34 INSERT INTO bed_height_type VALUES (5, 'TIN');
911f62917753 additional values in lookup table for bed height types Tom Gottfried <tom@intevation.de> parents: 5273 diff changeset	35 INSERT INTO bed_height_type VALUES (6, 'Modell');
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	36
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	37
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	38 CREATE SEQUENCE BED_HEIGHT_ID_SEQ;
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	39
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	40 CREATE TABLE bed_height (
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	41 id NUMBER(38,0) NOT NULL,
2809 f283212966e8 Finished work on MINFO bed heights (single). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2807 diff changeset	42 river_id NUMBER(38,0) NOT NULL,
5448 b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column Tom Gottfried <tom.gottfried@intevation.de> parents: 5441 diff changeset	43 year NUMBER(38,0),
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	44 type_id NUMBER(38,0) NOT NULL,
2807 5ac1db5156be Some oracle db schema adaptions specific to bed heights in MINFO. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2805 diff changeset	45 location_system_id NUMBER(38,0) NOT NULL,
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	46 cur_elevation_model_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	47 old_elevation_model_id NUMBER(38,0),
5448 b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column Tom Gottfried <tom.gottfried@intevation.de> parents: 5441 diff changeset	48 range_id NUMBER(38,0),
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	49 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	50 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	51 PRIMARY KEY(id),
8666 1a4ad0d4231a (issue1763) Use more distinct constraint names and adapt drop-script. Tom Gottfried <tom@intevation.de> parents: 8658 diff changeset	52 CONSTRAINT fk_bh_river_id FOREIGN KEY (river_id)
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	53 REFERENCES rivers(id) ON DELETE CASCADE,
8666 1a4ad0d4231a (issue1763) Use more distinct constraint names and adapt drop-script. Tom Gottfried <tom@intevation.de> parents: 8658 diff changeset	54 CONSTRAINT fk_bh_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
1a4ad0d4231a (issue1763) Use more distinct constraint names and adapt drop-script. Tom Gottfried <tom@intevation.de> parents: 8658 diff changeset	55 CONSTRAINT fk_bh_location_system FOREIGN KEY (location_system_id)
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	56 REFERENCES location_system(id),
8666 1a4ad0d4231a (issue1763) Use more distinct constraint names and adapt drop-script. Tom Gottfried <tom@intevation.de> parents: 8658 diff changeset	57 CONSTRAINT fk_bh_cur_elevation_model FOREIGN KEY (cur_elevation_model_id)
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	58 REFERENCES elevation_model(id),
8666 1a4ad0d4231a (issue1763) Use more distinct constraint names and adapt drop-script. Tom Gottfried <tom@intevation.de> parents: 8658 diff changeset	59 CONSTRAINT fk_bh_old_elevation_model FOREIGN KEY (old_elevation_model_id)
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	60 REFERENCES elevation_model(id),
8666 1a4ad0d4231a (issue1763) Use more distinct constraint names and adapt drop-script. Tom Gottfried <tom@intevation.de> parents: 8658 diff changeset	61 CONSTRAINT fk_bh_range FOREIGN KEY (range_id)
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	62 REFERENCES ranges(id) ON DELETE CASCADE
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	63 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	64
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	65
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	66 CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ;
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	67
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	68 CREATE TABLE bed_height_values (
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	69 id NUMBER(38,0) NOT NULL,
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	70 bed_height_id NUMBER(38,0) NOT NULL,
6203 a3fb76cf79b6 backout backout as it is now safely in a branch Tom Gottfried <tom.gottfried@intevation.de> parents: 6201 diff changeset	71 station DOUBLE PRECISION NOT NULL,
a3fb76cf79b6 backout backout as it is now safely in a branch Tom Gottfried <tom.gottfried@intevation.de> parents: 6201 diff changeset	72 height DOUBLE PRECISION,
a3fb76cf79b6 backout backout as it is now safely in a branch Tom Gottfried <tom.gottfried@intevation.de> parents: 6201 diff changeset	73 uncertainty DOUBLE PRECISION,
a3fb76cf79b6 backout backout as it is now safely in a branch Tom Gottfried <tom.gottfried@intevation.de> parents: 6201 diff changeset	74 data_gap DOUBLE PRECISION,
a3fb76cf79b6 backout backout as it is now safely in a branch Tom Gottfried <tom.gottfried@intevation.de> parents: 6201 diff changeset	75 sounding_width DOUBLE PRECISION,
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	76 PRIMARY KEY(id),
8559 6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	77 UNIQUE (station, bed_height_id),
6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	78 CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id)
6d8d7425a6b5 Bed heights are just bed heights since a while ('single' is obsolete). "Tom Gottfried <tom@intevation.de>" parents: 8554 diff changeset	79 REFERENCES bed_height(id) ON DELETE CASCADE
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	80 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	81
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	82
2812 a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	83 CREATE SEQUENCE DEPTHS_ID_SEQ;
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	84
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	85 CREATE TABLE depths (
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	86 id NUMBER(38,0) NOT NULL,
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	87 lower NUMBER(38,2) NOT NULL,
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	88 upper NUMBER(38,2) NOT NULL,
5441 7c1dd9c3f6bd remove unit from sediment density and depths (always t/m3 respectively cm, otherwise a typo) Tom Gottfried <tom.gottfried@intevation.de> parents: 5426 diff changeset	89 PRIMARY KEY(id)
2812 a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	90 );
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	91
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	92
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	93 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	94
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	95 CREATE TABLE sediment_density (
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	96 id NUMBER(38,0) NOT NULL,
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	97 river_id NUMBER(38,0) NOT NULL,
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	98 depth_id NUMBER(38,0) NOT NULL,
2817 8979f2294af9 Finished parsing MINFO specific sediment density. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2816 diff changeset	99 description VARCHAR(256),
2812 a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	100 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	101 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
5441 7c1dd9c3f6bd remove unit from sediment density and depths (always t/m3 respectively cm, otherwise a typo) Tom Gottfried <tom.gottfried@intevation.de> parents: 5426 diff changeset	102 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
2812 a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	103 );
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	104
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	105
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	106 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	107
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	108 CREATE TABLE sediment_density_values (
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	109 id NUMBER(38,0) NOT NULL,
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	110 sediment_density_id NUMBER(38,0) NOT NULL,
2816 70b4a31a3306 Implemented the method stubs of the parser for sediment density and made some db schema adaptions. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2812 diff changeset	111 station NUMBER(38,2) NOT NULL,
5507 db6c7268b08e Schema change: add shore offset to sediment density values and be more rigid with input files in parser Tom Gottfried <tom.gottfried@intevation.de> parents: 5498 diff changeset	112 shore_offset NUMBER(38,2),
2812 a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	113 density NUMBER(38,2) NOT NULL,
a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	114 description VARCHAR(256),
4523 504cd5801785 Added new column 'year' to sediment density values in postgresql and oracle Raimund Renkert <rrenkert@intevation.de> parents: 3953 diff changeset	115 year NUMBER(38,0),
2812 a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	116 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	117 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE
2812 a36a5407acbf Improved the MINFO db schema and added model classes for storing sediment density values specific to a river and depth. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2810 diff changeset	118 );
2818 25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	119
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	120
7863 082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	121 CREATE SEQUENCE POROSITY_ID_SEQ;
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	122
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	123 CREATE TABLE porosity (
7866 0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	124 id NUMBER(38,0) NOT NULL,
0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	125 river_id NUMBER(38,0) NOT NULL,
0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	126 depth_id NUMBER(38,0) NOT NULL,
7863 082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	127 description VARCHAR(256),
7866 0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	128 time_interval_id NUMBER(38,0) NOT NULL,
7863 082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	129 PRIMARY KEY(id),
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	130 CONSTRAINT fk_p_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	131 CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	132 CONSTRAINT fk_p_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	133 );
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	134
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	135
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	136 CREATE SEQUENCE POROSITY_VALUES_ID_SEQ;
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	137
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	138 CREATE TABLE porosity_values (
7866 0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	139 id NUMBER(38,0) NOT NULL,
0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	140 porosity_id NUMBER(38,0) NOT NULL,
0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	141 station DOUBLE PRECISION NOT NULL,
0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	142 shore_offset DOUBLE PRECISION,
0c74b5f8de17 Precision and scale have to be set to represent integers with NUMBER; DOUBLE PRECISION is preferable for floating point (see issue1304). Tom Gottfried <tom@intevation.de> parents: 7863 diff changeset	143 porosity DOUBLE PRECISION NOT NULL,
7863 082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	144 description VARCHAR(256),
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	145 PRIMARY KEY(id),
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	146 CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	147 );
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	148
082284c4e0ad Updated oracle minfo schema (added porosity). Raimund Renkert <rrenkert@intevation.de> parents: 7461 diff changeset	149
2818 25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	150 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	151
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	152 CREATE TABLE morphologic_width (
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	153 id NUMBER(38,0) NOT NULL,
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	154 river_id NUMBER(38,0) NOT NULL,
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	155 unit_id NUMBER(38,0) NOT NULL,
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	156 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	157 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE,
2818 25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	158 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	159 );
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	160
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	161
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	162 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	163
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	164 CREATE TABLE morphologic_width_values (
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	165 id NUMBER(38,0) NOT NULL,
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	166 morphologic_width_id NUMBER(38,0) NOT NULL,
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	167 station NUMBER(38,3) NOT NULL,
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	168 width NUMBER(38,3) NOT NULL,
2819 0c2567626754 Tiny schema modification specific to MINFO morphological width values; added importer classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2818 diff changeset	169 description VARCHAR(256),
2818 25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	170 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	171 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE
2818 25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	172 );
2824 85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	173
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	174
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	175 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	176
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	177 CREATE TABLE discharge_zone (
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	178 id NUMBER(38,0) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	179 river_id NUMBER(38,0) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	180 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
2833 5b54a648f702 Finished flow velocity data import: finished parsing meta data of model files and repaired broken HQL statements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2829 diff changeset	181 value NUMBER(38,3) NOT NULL,
6369 7fb0e755e4fa Minimal schema change: allow longer names in discharge zones Tom Gottfried <tom.gottfried@intevation.de> parents: 6203 diff changeset	182 lower_discharge VARCHAR(64) NOT NULL,
7fb0e755e4fa Minimal schema change: allow longer names in discharge zones Tom Gottfried <tom.gottfried@intevation.de> parents: 6203 diff changeset	183 upper_discharge VARCHAR(64),
2824 85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	184 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	185 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
2824 85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	186 );
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	187
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	188
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	189 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	190
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	191 CREATE TABLE flow_velocity_model (
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	192 id NUMBER(38,0) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	193 discharge_zone_id NUMBER(38,0) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	194 description VARCHAR(256),
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	195 PRIMARY KEY (id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE
2824 85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	197 );
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	198
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	199
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	201
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	202 CREATE TABLE flow_velocity_model_values (
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	203 id NUMBER(38,0) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	204 flow_velocity_model_id NUMBER(38,0) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	205 station NUMBER(38,3) NOT NULL,
2828 ac13e466a55e Added a parser for flow velocity model data and adjusted the db relation schema (missing q column). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2824 diff changeset	206 q NUMBER(38,3) NOT NULL,
2824 85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	207 total_channel NUMBER(38,3) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	208 main_channel NUMBER(38,3) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	209 shear_stress NUMBER(38,3) NOT NULL,
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	210 PRIMARY KEY(id),
5709 e89d922df8ae Schema change: add constraint to have unique stations per flow velocity model Tom Gottfried <tom@intevation.de> parents: 5684 diff changeset	211 UNIQUE (station, flow_velocity_model_id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	212 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE
2824 85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	213 );
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	214
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	215
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	216
2829 4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	217 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	218
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	219 CREATE TABLE flow_velocity_measurements (
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	220 id NUMBER(38,0) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	221 river_id NUMBER(38,0) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	222 description VARCHAR(256),
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	223 PRIMARY KEY (id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	224 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE
2829 4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	225 );
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	226
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	227 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	228
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	229 CREATE TABLE flow_velocity_measure_values (
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	230 id NUMBER(38,0) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	231 measurements_id NUMBER(38,0) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	232 station NUMBER(38,3) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	233 datetime TIMESTAMP,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	234 w NUMBER(38,3) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	235 q NUMBER(38,3) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	236 v NUMBER(38,3) NOT NULL,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	237 description VARCHAR(256),
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	238 PRIMARY KEY (id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	239 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE
2829 4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	240 );
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	241
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	242
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	243 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	244
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	245 CREATE TABLE grain_fraction (
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	246 id NUMBER(38,0) NOT NULL,
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	247 name VARCHAR(64) NOT NULL,
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	248 lower NUMBER(38,3),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	249 upper NUMBER(38,3),
8032 fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	250 PRIMARY KEY (id),
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	251 UNIQUE(name, lower, upper)
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	252 );
8032 fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	253 -- single fractions
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	254 INSERT INTO grain_fraction VALUES (1, 'coarse', 16, 200);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	255 INSERT INTO grain_fraction VALUES (2, 'fine_middle', 2, 16);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	256 INSERT INTO grain_fraction VALUES (3, 'sand', 0.063, 2);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	257 INSERT INTO grain_fraction VALUES (4, 'susp_sand', 0.063, 2);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	258 INSERT INTO grain_fraction VALUES (5, 'susp_sand_bed', 0.063, 2);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	259 INSERT INTO grain_fraction VALUES (6, 'suspended_sediment', 0, 0.063);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	260 -- aggregations of fractions
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	261 INSERT INTO grain_fraction VALUES (7, 'total', 0, 200);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	262 INSERT INTO grain_fraction VALUES (8, 'bed_load', 0.063, 200);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	263 INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2);
fd3a24336e6a SCHEMA CHANGE and Importer (only longitudinal section data so far): only grain fractions given now in schema are valid. We are handling sediment loads, not yields. "Tom Gottfried <tom@intevation.de>" parents: 8019 diff changeset	264
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	265
7461 5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented. Tom Gottfried <tom@intevation.de> parents: 6730 diff changeset	266 --lookup table for sediment yield kinds
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	267 CREATE TABLE sediment_load_kinds (
7461 5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented. Tom Gottfried <tom@intevation.de> parents: 6730 diff changeset	268 id int PRIMARY KEY NOT NULL,
5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented. Tom Gottfried <tom@intevation.de> parents: 6730 diff changeset	269 kind VARCHAR(64) NOT NULL
5b5a2cc8210f Schema change: add lookup table for sediment yield kinds to have its values documented. Tom Gottfried <tom@intevation.de> parents: 6730 diff changeset	270 );
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	271 INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official');
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	272 INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official');
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	273
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	274 CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ;
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	275
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	276 CREATE TABLE sediment_load_ls (
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	277 id NUMBER(38,0) NOT NULL,
2836 6b541d85c5cf Added missing river_id column to sediment_yield relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2834 diff changeset	278 river_id NUMBER(38,0) NOT NULL,
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	279 grain_fraction_id NUMBER(38,0),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	280 unit_id NUMBER(38,0) NOT NULL,
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	281 time_interval_id NUMBER(38,0) NOT NULL,
8059 bde5f5ec7c72 SCHEMA CHANGE and Importer: get time intervals of SQ-relations from metalines in sediment load files. Tom Gottfried <tom@intevation.de> parents: 8032 diff changeset	282 sq_time_interval_id NUMBER(38,0),
2840 71175502d868 Added a parser for sediment yield files; started parsing and store values in db. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2836 diff changeset	283 description VARCHAR(256),
6721 df402b3e0281 minfo-schema-change/issue1393: add kind column to sediment_yield table. Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 6201 diff changeset	284 kind NUMBER(38,0),
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	285 PRIMARY KEY (id),
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	286 CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	287 REFERENCES rivers(id) ON DELETE CASCADE,
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	288 CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	289 REFERENCES sediment_load_kinds(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	290 CONSTRAINT fk_slls_grain_fraction_id FOREIGN KEY (grain_fraction_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	291 REFERENCES grain_fraction(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	292 CONSTRAINT fk_slls_unit_id FOREIGN KEY (unit_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	293 REFERENCES units(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	294 CONSTRAINT fk_slls_time_interval_id FOREIGN KEY (time_interval_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	295 REFERENCES time_intervals(id),
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	296 CONSTRAINT fk_slls_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
8059 bde5f5ec7c72 SCHEMA CHANGE and Importer: get time intervals of SQ-relations from metalines in sediment load files. Tom Gottfried <tom@intevation.de> parents: 8032 diff changeset	297 REFERENCES time_intervals(id)
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	298 );
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	299
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	300
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	301 CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ;
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	302
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	303 CREATE TABLE sediment_load_ls_values (
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	304 id NUMBER(38,0) NOT NULL,
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	305 sediment_load_ls_id NUMBER(38,0) NOT NULL,
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	306 station NUMBER(38,3) NOT NULL,
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	307 value NUMBER(38,3) NOT NULL,
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	308 PRIMARY KEY (id),
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	309 CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id)
5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	310 REFERENCES sediment_load_ls(id) ON DELETE CASCADE
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	311 );
2841 6be2bf2492f9 Added MINFO specific waterlevel relations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2840 diff changeset	312
6be2bf2492f9 Added MINFO specific waterlevel relations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2840 diff changeset	313
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	314 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	315 CREATE TABLE measurement_station (
8412 17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	316 id int NOT NULL,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	317 range_id int NOT NULL,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	318 reference_gauge_id int,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	319 time_interval_id int,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	320 name VARCHAR2(256 CHAR) NOT NULL,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	321 measurement_type VARCHAR2(64 CHAR) NOT NULL,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	322 riverside VARCHAR2(16 CHAR),
5456 e47c4e9b050e Schema corrected: measurement_station.reference_gauge_station is not needed Tom Gottfried <tom.gottfried@intevation.de> parents: 5455 diff changeset	323 -- store name of reference gauges here too, as not all are in gauges
8412 17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	324 reference_gauge_name VARCHAR2(64 CHAR),
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	325 operator VARCHAR2(64 CHAR),
8415 ffbeffe2d266 SCHEMA CHANGE: 'comment' is not acceptable as column name for Oracle. Tom Gottfried <tom@intevation.de> parents: 8412 diff changeset	326 commentary VARCHAR2(512 CHAR),
8412 17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	327 PRIMARY KEY (id),
8706 94cddb00f934 (issue1750) Apply schema changes to oracle, which are already done for PostgreSQL. Tom Gottfried <tom@intevation.de> parents: 8666 diff changeset	328 CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff')),
8412 17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	329 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id)
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	330 REFERENCES ranges(id) ON DELETE CASCADE,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	331 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id)
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	332 REFERENCES gauges(id) ON DELETE CASCADE,
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	333 CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id)
17db08570637 SCHEMA CHANGE: removed superfluous columns station and river_id from measurement_stations and adapted other components accordingly. Tom Gottfried <tom@intevation.de> parents: 8072 diff changeset	334 REFERENCES time_intervals(id)
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	335 );
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	336
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	337
8017 bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	338 CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ;
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	339
8018 ad614eff7a9b SCHEMA CHANGE: consistent naming scheme and drop script for sediment load at measurement stations. Tom Gottfried <tom@intevation.de> parents: 8017 diff changeset	340 CREATE TABLE sediment_load (
8017 bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	341 id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	342 grain_fraction_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	343 time_interval_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	344 sq_time_interval_id int,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	345 description VARCHAR(256),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	346 kind int,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	347 PRIMARY KEY (id),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	348 CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind)
8072 5f28aa1be795 SCHEMA CHANGE: rename database tables from yield to load_ls according to respective classes. Tom Gottfried <tom@intevation.de> parents: 8059 diff changeset	349 REFERENCES sediment_load_kinds(id),
8017 bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	350 CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	351 REFERENCES grain_fraction(id),
8658 3531f0cee5e1 Whitespace-cleanup schema files. "Tom Gottfried <tom@intevation.de>" parents: 8560 diff changeset	352 CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id)
8017 bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	353 REFERENCES time_intervals(id),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	354 CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	355 REFERENCES time_intervals(id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	356 );
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	357
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	358
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	359 CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ;
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	360
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	361 CREATE TABLE sediment_load_values (
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	362 id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	363 sediment_load_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	364 measurement_station_id int NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	365 value DOUBLE PRECISION NOT NULL,
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	366 PRIMARY KEY (id),
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	367 CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id)
8019 aa8dcf7331a8 Repair last commit. Tom Gottfried <tom@intevation.de> parents: 8018 diff changeset	368 REFERENCES sediment_load(id) ON DELETE CASCADE,
8017 bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	369 CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id)
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	370 REFERENCES measurement_station(id) ON DELETE CASCADE
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	371 );
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	372
bcb05a98bb3f SCHEMA CHANGE: Added new structure for sediment loads measured at measurement stations. Tom Gottfried <tom@intevation.de> parents: 7924 diff changeset	373
3321 a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	374 CREATE SEQUENCE SQ_RELATION_ID_SEQ;
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	375
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	376 CREATE TABLE sq_relation (
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	377 id NUMBER(38,0) NOT NULL,
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	378 time_interval_id NUMBER(38,0) NOT NULL,
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	379 description VARCHAR(256),
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	380 PRIMARY KEY (id),
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	381 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	382 );
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	383
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	384
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	385 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	386
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	387 CREATE TABLE sq_relation_value (
5426 6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	388 id NUMBER(38,0) NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	389 sq_relation_id NUMBER(38,0) NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	390 measurement_station_id NUMBER(38,0) NOT NULL,
5455 45c5b8b08ffd Schema Change: use parameter instead of fraction in sq_relation_value Tom Gottfried <tom.gottfried@intevation.de> parents: 5453 diff changeset	391 parameter VARCHAR(1) NOT NULL,
5426 6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	392 a NUMBER(38,20) NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	393 b NUMBER(38,20) NOT NULL,
5498 d459a885c51f Schema change: make sq_relation_value.qmax NOT NULL and more informative messages in parser Tom Gottfried <tom.gottfried@intevation.de> parents: 5478 diff changeset	394 qmax NUMBER(38,20) NOT NULL,
5426 6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	395 rsq NUMBER(38,3),
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	396 ntot NUMBER(38,0),
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	397 noutl NUMBER(38,0),
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	398 cferguson NUMBER(38,20),
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value. Raimund Renkert <rrenkert@intevation.de> parents: 5406 diff changeset	399 cduan NUMBER(38,20),
3321 a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	400 PRIMARY KEY (id),
8706 94cddb00f934 (issue1750) Apply schema changes to oracle, which are already done for PostgreSQL. Tom Gottfried <tom@intevation.de> parents: 8666 diff changeset	401 UNIQUE(sq_relation_id, measurement_station_id, parameter),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	402 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE,
5684 88cbe798cbab Schema change: fixed constraints Tom Gottfried <tom@intevation.de> parents: 5577 diff changeset	403 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE
3321 a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	404 );

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-minfo.sql @ 9650:a2a42a6bac6b