# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1571248159 -7200
# Node ID 901a74d866ac982908bb383fa4196aeb00eb24e0
# Parent  9eb708176b434648869397e24249b4e06f6ad031
Added dummy db update to fill in missing surtyp for sr data.

diff -r 9eb708176b43 -r 901a74d866ac schema/updates/1306/01.fillin_sr_surtyp.sql
--- /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 ;
diff -r 9eb708176b43 -r 901a74d866ac schema/version.sql
--- a/schema/version.sql	Wed Oct 16 18:22:33 2019 +0200
+++ b/schema/version.sql	Wed Oct 16 19:49:19 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1305);
+INSERT INTO gemma_schema_version(version) VALUES (1306);