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

annotate backend/doc/schema/oracle-minfo.sql @ 6061:e9a76ffa0f9a

Use maxOverlap to get the correct gauge for the MainValues Previously just the first matching gauge was taken even if it's range ended with the minimum value. This code is clearly intended to get one gauge for one range so the best match should be taken.

author	Andre Heinecke <aheinecke@intevation.de>
date	Wed, 22 May 2013 18:10:48 +0200
parents	2d16f4a0bdcc
children	53fca3392c9f

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
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	39
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	40 CREATE TABLE bed_height_single (
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),
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column Tom Gottfried <tom.gottfried@intevation.de> parents: 5441 diff changeset	44 sounding_width NUMBER(38,0),
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	45 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	46 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	47 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	48 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	49 range_id NUMBER(38,0),
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	50 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	51 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	52 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
2807 5ac1db5156be Some oracle db schema adaptions specific to bed heights in MINFO. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2805 diff changeset	55 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	56 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	57 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	59 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	60
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	61
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	62 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
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 CREATE TABLE bed_height_epoch (
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	65 id NUMBER(38,0) NOT NULL,
2810 04eeb45df27b Implemented model classes and importer classes for bed height epochs. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2809 diff changeset	66 river_id NUMBER(38,0) NOT NULL,
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	67 time_interval_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	68 -- sounding_with NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	69 -- type_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	70 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	71 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	72 range_id NUMBER(38,0),
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	73 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	74 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	75 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	76 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	77 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	78 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	79 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(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
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	83 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	84
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	85 CREATE TABLE bed_height_single_values (
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	86 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	87 bed_height_single_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	88 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	89 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	90 uncertainty NUMBER(38,2),
3953 fc32a4955087 Towards fix for issue863 (gaps in bedheight data). Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3793 diff changeset	91 data_gap NUMBER(38,2),
fc32a4955087 Towards fix for issue863 (gaps in bedheight data). Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3793 diff changeset	92 sounding_width NUMBER(38,2),
fc32a4955087 Towards fix for issue863 (gaps in bedheight data). Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3793 diff changeset	93 width NUMBER(38,2),
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	94 PRIMARY KEY(id),
6045 2d16f4a0bdcc Schema change: there is only one bed height per station at a given point in time Tom Gottfried <tom@intevation.de> parents: 5838 diff changeset	95 UNIQUE (station, bed_height_single_id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	96 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	97 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	98
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	99
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	100 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	101
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	102 CREATE TABLE bed_height_epoch_values (
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	103 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	104 bed_height_epoch_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	105 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	106 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	107 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	108 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	109 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	110
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	111
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	112 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	113
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 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	115 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	116 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	117 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	118 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	119 );
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	120
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	121
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	122 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	123
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	124 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	125 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	126 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	127 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	128 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	129 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	130 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	131 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	132 );
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	133
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	134
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	135 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	136
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	137 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	138 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	139 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	140 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	141 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	142 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	143 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	144 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	145 PRIMARY KEY(id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	146 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	147 );
2818 25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	148
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	149
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,
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	182 lower_discharge VARCHAR(16) NOT NULL,
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	183 upper_discharge VARCHAR(16),
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),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	250 PRIMARY KEY (id),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	251 );
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	252
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	253
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	254 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	255
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	256 CREATE TABLE sediment_yield (
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	257 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	258 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	259 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	260 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	261 time_interval_id NUMBER(38,0) NOT NULL,
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	262 description VARCHAR(256),
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	263 PRIMARY KEY (id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	264 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(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	265 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	266 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	267 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	268 );
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	269
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	270
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	271 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	272
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	273 CREATE TABLE sediment_yield_values (
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	274 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	275 sediment_yield_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	276 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	277 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	278 PRIMARY KEY (id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	279 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(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	280 );
2841 6be2bf2492f9 Added MINFO specific waterlevel relations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2840 diff changeset	281
6be2bf2492f9 Added MINFO specific waterlevel relations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2840 diff changeset	282
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	283 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	284 CREATE TABLE measurement_station (
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	285 id NUMBER(38) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	286 name VARCHAR(256) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	287 river_id NUMBER(38) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	288 station NUMBER(38,3) NOT NULL,
5448 b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column Tom Gottfried <tom.gottfried@intevation.de> parents: 5441 diff changeset	289 range_id NUMBER(38),
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	290 measurement_type VARCHAR(64) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	291 riverside VARCHAR(16),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	292 reference_gauge_id NUMBER(38),
5456 e47c4e9b050e Schema corrected: measurement_station.reference_gauge_station is not needed Tom Gottfried <tom.gottfried@intevation.de> parents: 5455 diff changeset	293 -- store name of reference gauges here too, as not all are in gauges
5453 303701ab73c6 store name and km of reference gauges in measurement_stations too, as not all are in gauges Tom Gottfried <tom.gottfried@intevation.de> parents: 5448 diff changeset	294 reference_gauge_name VARCHAR(64),
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	295 observation_timerange_id NUMBER(38),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	296 operator VARCHAR(64),
5475 b6a6d13eec41 backout r5460 to be compatible with r5471 and Oracle database Tom Gottfried <tom@intevation.de> parents: 5460 diff changeset	297 description VARCHAR(512),
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	298 PRIMARY KEY (id),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	299 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	300 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	301 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE,
5478 5bb3610b94f7 Schema Change: removed constraint that did not allow to have more than one measurement_type per station Tom Gottfried <tom.gottfried@intevation.de> parents: 5475 diff changeset	302 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) 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	303 );
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	304
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	305
3321 a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	306 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	307
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	308 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	309 id NUMBER(38,0) NOT NULL,
3329 cc8fc6b29649 Store sq relations into database after parsing. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3321 diff changeset	310 river_id NUMBER(38,0) NOT NULL,
3321 a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	311 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	312 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	313 PRIMARY KEY (id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	314 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(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	315 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	316 );
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	317
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	318
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	319 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	320
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	321 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	322 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	323 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	324 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	325 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	326 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	327 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	328 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	329 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	330 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	331 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	332 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	333 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	334 PRIMARY KEY (id),
5577 5f91881124ba added missing ON DELETE CASCADE to Oracle schema Tom Gottfried <tom@intevation.de> parents: 5507 diff changeset	335 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	336 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	337 );

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-minfo.sql @ 6061:e9a76ffa0f9a