# HG changeset patch
# User wilde@azure1.rgb.intevation.de
# Date 1589282055 -7200
# Node ID 0b051ee3f2383658d9eda89eb96432d21543fd89
# Parent  f3a88039d8228f27f1437f73d5fafde38df518f5
Added bottleneck_id column to bottleneck_overview view.

diff -r f3a88039d822 -r 0b051ee3f238 schema/default_sysconfig.sql
--- a/schema/default_sysconfig.sql	Tue May 12 12:43:31 2020 +0200
+++ b/schema/default_sysconfig.sql	Tue May 12 13:14:15 2020 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018, 2019 by via donau
+-- Copyright (C) 2018, 2019, 2020 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -220,6 +220,7 @@
     ('waterway', 'bottleneck_overview', 4326, NULL, $$
         SELECT
             objnam AS name,
+            bn.bottleneck_id,
             ST_Centroid(area) AS point,
             (lower(stretch)).hectometre AS from,
             (upper(stretch)).hectometre AS to,
diff -r f3a88039d822 -r 0b051ee3f238 schema/updates/1439/01.add_bn_id_to_bn_overview.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1439/01.add_bn_id_to_bn_overview.sql	Tue May 12 13:14:15 2020 +0200
@@ -0,0 +1,22 @@
+-- Add bottleneck_id column to bottleneck_overview view.
+
+UPDATE sys_admin.published_services
+    SET
+        view_def = $$
+            SELECT
+                objnam AS name,
+                bn.bottleneck_id,
+                ST_Centroid(area) AS point,
+                (lower(stretch)).hectometre AS from,
+                (upper(stretch)).hectometre AS to,
+                sr.current::text,
+                responsible_country
+            FROM waterway.bottlenecks bn LEFT JOIN (
+                SELECT bottleneck_id, max(date_info) AS current
+                    FROM waterway.sounding_results
+                    GROUP BY bottleneck_id) sr
+                    ON sr.bottleneck_id = bn.bottleneck_id
+            WHERE bn.validity @> current_timestamp
+            ORDER BY objnam
+            $$
+    WHERE schema = 'waterway' AND name = 'bottleneck_overview';
diff -r f3a88039d822 -r 0b051ee3f238 schema/version.sql
--- a/schema/version.sql	Tue May 12 12:43:31 2020 +0200
+++ b/schema/version.sql	Tue May 12 13:14:15 2020 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1438);
+INSERT INTO gemma_schema_version(version) VALUES (1439);