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

annotate flys-backend/doc/schema/oracle-minfo.sql @ 5358:59f9760e8b7f

Switch to 2D for fixpoints and river_axes This is needed so that the spatial index can work. The importer only imports them as 2D geometries

author	Andre Heinecke <aheinecke@intevation.de>
date	Thu, 21 Mar 2013 09:30:46 +0100
parents	6dd354e7abfc
children	e88d55d01cf5

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,
2805 a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	43 year 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	44 sounding_width NUMBER(38,0) NOT NULL,
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),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	49 range_id NUMBER(38,0) NOT NULL,
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),
2809 f283212966e8 Finished work on MINFO bed heights (single). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2807 diff changeset	53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
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),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
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),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	72 range_id NUMBER(38,0) NOT NULL,
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),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	79 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
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),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	95 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	96 );
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 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	100
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	101 CREATE TABLE bed_height_epoch_values (
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	102 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	103 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	104 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	105 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	106 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	107 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	108 );
a55b7b35cc3a First things for MINFO specific database schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	109
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	110
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 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	112
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 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	114 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	115 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	116 upper NUMBER(38,2) NOT NULL,
2823 13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2819 diff changeset	117 unit_id NUMBER(38,0) NOT NULL,
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 PRIMARY KEY(id),
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 CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
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
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 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	124
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 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	126 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 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	128 depth_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	129 unit_id NUMBER(38,0) NOT NULL,
2817 8979f2294af9 Finished parsing MINFO specific sediment density. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2816 diff changeset	130 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	131 PRIMARY KEY(id),
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 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
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	133 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
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	134 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(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	135 );
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
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 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	139
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	140 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	141 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	142 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	143 station NUMBER(38,2) NOT NULL,
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	144 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	145 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	146 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	147 PRIMARY KEY(id),
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	148 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(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	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
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 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	153
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	154 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	155 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 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	157 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	158 PRIMARY KEY(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 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	160 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	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
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 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	165
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	166 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	167 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	168 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	169 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	170 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	171 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	172 PRIMARY KEY(id),
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	173 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
25ed1f18fcc4 Improved the MIFNO DB schema for morphological width and added model classes. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2817 diff changeset	174 );
2824 85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	175
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 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	178
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	179 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	180 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	181 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	182 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	183 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	184 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	185 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	186 PRIMARY KEY(id),
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	187 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
2824 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
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 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	192
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	193 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	194 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	195 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	196 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	197 PRIMARY KEY (id),
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	198 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
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
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 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	203
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	204 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	205 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	206 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	207 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	208 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	209 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	210 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	211 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	212 PRIMARY KEY(id),
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	213 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
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
85b2b5e7377f Improved the oracle db schema for MINFO specific flow velocity values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2823 diff changeset	217
2829 4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	218 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	219
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	220 CREATE TABLE flow_velocity_measurements (
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	221 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 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	223 description VARCHAR(256),
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	224 PRIMARY KEY (id),
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	225 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
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
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	228 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	229
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	230 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	231 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 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	233 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	234 datetime TIMESTAMP,
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	235 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	236 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	237 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	238 description VARCHAR(256),
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	239 PRIMARY KEY (id),
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	240 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
4074777afcfa Added MINFO specific relations for flow velocity measurements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2828 diff changeset	241 );
2834 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
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	244 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	245
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	246 CREATE TABLE grain_fraction (
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	247 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	248 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	249 lower NUMBER(38,3),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	250 upper NUMBER(38,3),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	251 PRIMARY KEY (id),
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
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	255 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	256
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	257 CREATE TABLE sediment_yield (
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	258 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	259 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	260 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	261 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	262 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	263 description VARCHAR(256),
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	264 PRIMARY KEY (id),
2836 6b541d85c5cf Added missing river_id column to sediment_yield relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2834 diff changeset	265 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
2834 daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	266 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	267 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	268 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	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
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	272 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	273
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	274 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	275 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 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	277 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	278 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	279 PRIMARY KEY (id),
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
daba3f895eb3 Improved the db schema to store sediment yield values. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2833 diff changeset	281 );
2841 6be2bf2492f9 Added MINFO specific waterlevel relations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2840 diff changeset	282
6be2bf2492f9 Added MINFO specific waterlevel relations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2840 diff changeset	283
5206 8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	284 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	285 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	286 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	287 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	288 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	289 station NUMBER(38,3) NOT NULL,
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	290 range_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	291 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	292 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	293 reference_gauge_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	294 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	295 operator VARCHAR(64),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	296 description VARCHAR(512),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	297 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	298 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	299 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	300 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(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_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column Andre Heinecke <aheinecke@intevation.de> parents: 4523 diff changeset	302 UNIQUE (river_id, station)
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),
3329 cc8fc6b29649 Store sq relations into database after parsing. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3321 diff changeset	314 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
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 (
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	322 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	323 sq_relation_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	324 parameter VARCHAR(16) 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	325 fraction VARCHAR(32) 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	326 function VARCHAR(32) 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	327 km NUMBER(38,3) NOT NULL,
3793 8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3329 diff changeset	328 a NUMBER(38, 3) 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	329 b NUMBER(38,3) 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	330 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	331 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
a2a33eff93aa Improved minfo db schema and added model classes for s/q relation. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2849 diff changeset	332 );

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/oracle-minfo.sql @ 5358:59f9760e8b7f