Mercurial > gemma
changeset 4705:901a74d866ac
Added dummy db update to fill in missing surtyp for sr data.
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Wed, 16 Oct 2019 19:49:19 +0200 |
| parents | 9eb708176b43 |
| children | a38d846d9fd5 |
| files | schema/updates/1306/01.fillin_sr_surtyp.sql schema/version.sql |
| diffstat | 2 files changed, 50 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1306/01.fillin_sr_surtyp.sql Wed Oct 16 19:49:19 2019 +0200 @@ -0,0 +1,49 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2019 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde <sascha.wilde@intevation.de> + +-- +-- This is is an dummy update which does not change the data base +-- schema but does fill in missing data. The goal is to add missing +-- surtyp in sounding reults by extracting it from the import logs. +-- + +WITH loginfo AS ( + SELECT i.enqueued, + (i.data::json -> 'date')::text::date AS date_info, + array_agg(l.msg ORDER BY l.msg) AS msgs + FROM import.imports i, + import.import_logs l + WHERE i.kind='sr' AND i.state IN ('accepted','pending') + AND l.import_id = i.id + AND ( l.msg LIKE 'Bottleneck: %' OR l.msg LIKE 'Processing as %' ) + GROUP BY i.enqueued, i.data + ), + typeinfo AS ( + SELECT DISTINCT ON (date_info, msgs[1]) enqueued, date_info, + substring(msgs[1] from 13) AS bn_name, + CASE WHEN msgs[2] = 'Processing as multi beam scan.' + THEN 'multi' ELSE 'single' END AS surtype + FROM loginfo + ORDER BY date_info, msgs[1], enqueued DESC + ), + bnid_type AS ( + SELECT DISTINCT t.*, bn.bottleneck_id + FROM typeinfo t, waterway.bottlenecks bn + WHERE t.bn_name = bn.objnam + ) +UPDATE waterway.sounding_results AS sr + SET surtyp = bt.surtype + FROM bnid_type bt + WHERE sr.bottleneck_id = bt.bottleneck_id + AND sr.date_info = bt.date_info + AND surtyp IS NULL ;