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

annotate flys-backend/doc/schema/postgresql-minfo.sql @ 4837:9e25c7523485

Fixed calculation of effective width in MINFO SQ relation. * Get all (including empty datasets) from db. * Filter empty datasets when processing data of the same date. * Added debug outputs.

author	Raimund Renkert <rrenkert@intevation.de>
date	Wed, 23 Jan 2013 11:14:41 +0100
parents	504cd5801785
children	b79eb203032d

rev	line source
2823 13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 BEGIN;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	4
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	5 CREATE TABLE location_system (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	7 name VARCHAR(32) NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 description VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	9 PRIMARY KEY(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	10 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	11
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	12
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	14
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	15 CREATE TABLE elevation_model (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	16 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	17 name VARCHAR(32) NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18 unit_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	21 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	22
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	24
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	25 CREATE TABLE bed_height_type (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	26 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	27 name VARCHAR(16) NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	28 description VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	29 PRIMARY KEY(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	30 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	31
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	32
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	33
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	35
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	36 CREATE TABLE bed_height_single (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	37 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	38 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	39 year int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	40 sounding_width int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	41 type_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	42 location_system_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	43 cur_elevation_model_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	44 old_elevation_model_id int,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	45 range_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	46 evaluation_by VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	47 description VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	48 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	49 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	50 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	51 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	52 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	53 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	54 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	55 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	56
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	57
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	58 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	59
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	60 CREATE TABLE bed_height_epoch (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	61 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	62 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	63 time_interval_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	64 -- sounding_with int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	65 -- type_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	66 cur_elevation_model_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	67 old_elevation_model_id int,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	68 range_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	69 evaluation_by VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	70 description VARCHAR(255),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	71 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	72 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	73 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	74 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	75 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	76 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	77
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	78
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	79 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	80
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	81 CREATE TABLE bed_height_single_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	82 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	83 bed_height_single_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	84 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	85 height NUMERIC,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	86 uncertainty NUMERIC,
3953 fc32a4955087 Towards fix for issue863 (gaps in bedheight data). Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3793 diff changeset	87 data_gap NUMERIC,
fc32a4955087 Towards fix for issue863 (gaps in bedheight data). Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3793 diff changeset	88 sounding_width NUMERIC,
fc32a4955087 Towards fix for issue863 (gaps in bedheight data). Felix Wolfsteller <felix.wolfsteller@intevation.de> parents: 3793 diff changeset	89 width NUMERIC,
2823 13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	90 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	91 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	92 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	93
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	94
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	95 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	96
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	97 CREATE TABLE bed_height_epoch_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	98 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	99 bed_height_epoch_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	100 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	101 height NUMERIC,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	102 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	103 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	104 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	105
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	106
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	107 CREATE SEQUENCE DEPTHS_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	108
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	109 CREATE TABLE depths (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	110 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	111 lower NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	112 upper NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	113 unit_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	114 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	115 CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	116 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	117
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	118
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	119 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	120
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	121 CREATE TABLE sediment_density (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	122 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	123 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	124 depth_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	125 unit_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	126 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	127 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	128 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	129 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	130 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	131 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	132
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	133
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	135
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	136 CREATE TABLE sediment_density_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	137 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	138 sediment_density_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	139 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	140 density NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	141 description VARCHAR(256),
4523 504cd5801785 Added new column 'year' to sediment density values in postgresql and oracle Raimund Renkert <rrenkert@intevation.de> parents: 4193 diff changeset	142 year int,
2823 13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	143 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	144 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	145 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	146
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	147
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	148 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	149
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	150 CREATE TABLE morphologic_width (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	151 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	152 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	153 unit_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	154 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	155 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	156 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	157 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	158
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	159
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	160 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	161
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	162 CREATE TABLE morphologic_width_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	163 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	164 morphologic_width_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	165 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	166 width NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	167 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	168 PRIMARY KEY(id),
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	169 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	170 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	171
2858 c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	172
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	173 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	174
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	175 CREATE TABLE discharge_zone (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	176 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	177 river_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	178 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	179 value NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	180 lower_discharge VARCHAR(16) NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	181 upper_discharge VARCHAR(16),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	182 PRIMARY KEY(id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	183 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	184 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	185
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	186
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	187 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	188
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	189 CREATE TABLE flow_velocity_model (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	190 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	191 river_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	192 discharge_zone_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	193 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	194 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	195 CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	196 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	197 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	198
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	199
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	200 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	201
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	202 CREATE TABLE flow_velocity_model_values (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	203 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	204 flow_velocity_model_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	205 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	206 q NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	207 total_channel NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	208 main_channel NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	209 shear_stress NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	210 PRIMARY KEY(id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	211 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	212 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	213
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	214
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	215
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	216 CREATE SEQUENCE FV_MEASURE_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	217
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	218 CREATE TABLE flow_velocity_measurements (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	219 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	220 river_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	221 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	222 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	223 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	224 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	225
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	226 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	227
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	228 CREATE TABLE flow_velocity_measure_values (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	229 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	230 measurements_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	231 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	232 datetime TIMESTAMP,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	233 w NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	234 q NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	235 v NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	236 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	237 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	238 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	239 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	240
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	241
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	242 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	243
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	244 CREATE TABLE grain_fraction (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	245 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	246 name VARCHAR(64) NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	247 lower NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	248 upper NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	249 unit_id int,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	250 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	251 CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	252 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	253
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	254
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	255 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	256
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	257 CREATE TABLE sediment_yield (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	258 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	259 river_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	260 grain_fraction_id int,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	261 unit_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	262 time_interval_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	263 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	264 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	265 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	266 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	267 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	268 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	269 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	270
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	271
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	272 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	273
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	274 CREATE TABLE sediment_yield_values (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	275 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	276 sediment_yield_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	277 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	278 value NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	279 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	281 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	282
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	283
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	284 CREATE SEQUENCE WATERLEVEL_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	285
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	286 CREATE TABLE waterlevel (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	287 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	288 river_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	289 unit_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	290 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	291 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	292 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	293 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	294 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	295
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	296
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	297 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	298
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	299 CREATE TABLE waterlevel_q_range (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	300 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	301 waterlevel_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	302 q NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	303 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	304 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	305 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	306
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	307
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	308 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	309
3793 8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	310 CREATE TABLE waterlevel_values (
2858 c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	311 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	312 waterlevel_q_range_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	313 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	314 w NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	315 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	316 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	317 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	318
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	319
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	320 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	321
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	322 CREATE TABLE waterlevel_difference (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	323 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	324 river_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	325 unit_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	326 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	327 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	328 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	329 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	330 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	331
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	332
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	333 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	334
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	335 CREATE TABLE waterlevel_difference_column (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	336 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	337 difference_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	338 description VARCHAR(256),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	339 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	340 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	341 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	342
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	343
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	344 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	345
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	346 CREATE TABLE waterlevel_difference_values (
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	347 id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	348 column_id int NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	349 station NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	350 value NUMERIC NOT NULL,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	351 PRIMARY KEY (id),
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	352 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	353 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2823 diff changeset	354
3793 8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	355
4193 f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	356 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ;
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	357 CREATE TABLE measurement_station (
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	358 id int NOT NULL,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	359 name VARCHAR(256) NOT NULL,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	360 river_id int NOT NULL,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	361 station NUMERIC NOT NULL,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	362 range_id int NOT NULL,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	363 measurement_type VARCHAR(64) NOT NULL,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	364 riverside VARCHAR(16),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	365 reference_gauge_id int,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	366 observation_timerange_id int,
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	367 operator VARCHAR(64),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	368 comment VARCHAR(512),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	369 PRIMARY KEY (id),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	370 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	371 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	372 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	373 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	374 UNIQUE (river_id, station)
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	375 );
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	376
f63b39799d2d Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3953 diff changeset	377
3793 8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	378 CREATE SEQUENCE SQ_RELATION_ID_SEQ;
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	379
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	380 CREATE TABLE sq_relation (
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	381 id int NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	382 river_id int NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	383 time_interval_id int NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	384 description VARCHAR(256),
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	385 PRIMARY KEY (id),
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	386 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	387 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	388 );
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	389
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	390
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	391 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	392
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	393 CREATE TABLE sq_relation_value (
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	394 id int NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	395 sq_relation_id int NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	396 parameter VARCHAR(16) NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	397 fraction VARCHAR(32) NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	398 function VARCHAR(32) NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	399 km NUMERIC NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	400 a NUMERIC NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	401 b NUMERIC NOT NULL,
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	402 PRIMARY KEY (id),
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	403 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
8f232119011e FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2858 diff changeset	404 );
2823 13b3fcaa1b34 Add MINFO schema for PostgreSQL Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	405 COMMIT;

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-minfo.sql @ 4837:9e25c7523485