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

annotate backend/doc/schema/oracle-minfo.sql @ 8655:4b00ee858964

(issue1755) Fix Y Axis label in Bed Quality facets. It's used in the CSV Export.

author	Andre Heinecke <andre.heinecke@intevation.de>
date	Thu, 02 Apr 2015 14:09:27 +0200
parents	6fcf4717605f
children	3531f0cee5e1

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

Mercurial > dive4elements > river

annotate backend/doc/schema/oracle-minfo.sql @ 8655:4b00ee858964