Mercurial > gemma
changeset 2849:b8972e4671fa
Preserve old predictions on raw gauge measurement import.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 28 Mar 2019 16:40:02 +0100 |
parents | b6886706b325 |
children | 6d4f361c36e8 |
files | pkg/imports/gm.go schema/gemma.sql |
diffstat | 2 files changed, 28 insertions(+), 22 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/imports/gm.go Thu Mar 28 16:38:15 2019 +0100 +++ b/pkg/imports/gm.go Thu Mar 28 16:40:02 2019 +0100 @@ -50,9 +50,9 @@ FROM waterway.gauges WHERE (location).country_code = users.current_user_country()` - // TODO: Currently this statement updates existing data sets. In case we want - // 'historization' we need to develop an other mechanism to keep existing - // data. + // Note: we do not expect corrections of data through this service. So + // any constraint conflicts are triggered by actual redundat data which + // can be dropped. insertGMSQL = ` INSERT INTO waterway.gauge_measurements ( fk_gauge_id, @@ -87,20 +87,7 @@ $18, $19 ) -ON CONFLICT ON CONSTRAINT gauge_measurements_fk_gauge_id_measure_date_staging_done_key -DO UPDATE SET -country_code = EXCLUDED.country_code, -sender = EXCLUDED.sender, -language_code = EXCLUDED.language_code, -date_issue = EXCLUDED.date_issue, -reference_code= EXCLUDED.reference_code, -water_level = EXCLUDED.water_level, -predicted = EXCLUDED.predicted, -is_waterlevel = EXCLUDED.is_waterlevel, -value_min = EXCLUDED.value_min, -value_max = EXCLUDED.value_max, -date_info = EXCLUDED.date_info, -source_organization = EXCLUDED.source_organization +ON CONFLICT DO NOTHING RETURNING id ` ) @@ -335,6 +322,8 @@ } else { referenceCode = string(*wrm.Reference_code) } + + var newCnt int = 0 for _, measure := range wrm.Measure { var unit string if measure.Unit == nil { @@ -370,12 +359,17 @@ msg.Identification.Originator, true, // staging_done ).Scan(&gid) - if err != nil { + switch { + case err == sql.ErrNoRows: + // thats expected, nothing to do + case err != nil: return nil, err + default: + newCnt++ } } feedback.Info("Inserted %d measurements for %s", - len(wrm.Measure), curr) + newCnt, curr) gids = append(gids, curr) } }
--- a/schema/gemma.sql Thu Mar 28 16:38:15 2019 +0100 +++ b/schema/gemma.sql Thu Mar 28 16:40:02 2019 +0100 @@ -338,10 +338,22 @@ date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, -- "originator" staging_done boolean NOT NULL DEFAULT false, - -- So we can have a staged and - -- a non-staged fk_gauge_id/measure_date pair. - UNIQUE (fk_gauge_id, measure_date, staging_done) ) + -- Constraints are conditional for gauge_measurements, as they + -- differ between predicted values and measured ones. PG does not + -- have real conditional unique constraints, but we can use unique + -- indeces for that. + -- + -- So we can have a staged and a non-staged + -- fk_gauge_id/measure_date pairs in measured values. + CREATE UNIQUE INDEX gm_measured_unique_constraint + ON gauge_measurements (fk_gauge_id, measure_date, staging_done) + WHERE NOT predicted; + -- And we can have multiple predictions for one point in time + -- (but they are never staged). + CREATE UNIQUE INDEX gm_predicted_unique_constraint + ON gauge_measurements (fk_gauge_id, measure_date, date_issue) + WHERE predicted; CREATE TABLE waterway_axis ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,