Mercurial > gemma

changeset 5134:590ccab9ab70 queued-stage-done

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge default into queued-stage-done
author Tom Gottfried <tom@intevation.de>
date Fri, 27 Mar 2020 15:34:16 +0100
parents 38491057807b (current diff) a6e5bb85f0a7 (diff)
children 53618d18e387
files
diffstat 4 files changed, 79 insertions(+), 8 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/search.go	Fri Mar 27 15:31:03 2020 +0100
+++ b/pkg/controllers/search.go	Fri Mar 27 15:34:16 2020 +0100
@@ -4,7 +4,7 @@
 // SPDX-License-Identifier: AGPL-3.0-or-later
 // License-Filename: LICENSES/AGPL-3.0.txt
 //
-// Copyright (C) 2018 by via donau
+// Copyright (C) 2018, 2020 by via donau
 //   – Österreichische Wasserstraßen-Gesellschaft mbH
 // Software engineering by Intevation GmbH
 //
@@ -24,7 +24,7 @@
 )
 
 const (
-	searchMostSQL = `SELECT search_most($1)::text`
+	searchMostSQL = `SELECT search_most($1,$2)::text`
 )
 
 func searchFeature(req *http.Request) (jr mw.JSONResult, err error) {
@@ -44,6 +44,7 @@
 		req.Context(),
 		searchMostSQL,
 		s.SearchString,
+		s.SearchTime,
 	).Scan(&result)
 
 	if err != nil {
--- a/pkg/models/search.go	Fri Mar 27 15:31:03 2020 +0100
+++ b/pkg/models/search.go	Fri Mar 27 15:34:16 2020 +0100
@@ -4,7 +4,7 @@
 // SPDX-License-Identifier: AGPL-3.0-or-later
 // License-Filename: LICENSES/AGPL-3.0.txt
 //
-// Copyright (C) 2018 by via donau
+// Copyright (C) 2018, 2020 by via donau
 //   – Österreichische Wasserstraßen-Gesellschaft mbH
 // Software engineering by Intevation GmbH
 //
@@ -13,8 +13,13 @@
 
 package models
 
+import (
+	"time"
+)
+
 type (
 	SearchRequest struct {
 		SearchString string `json:"string"`
+		SearchTime *time.Time `json:"time"`
 	}
 )
--- a/schema/search_functions.sql	Fri Mar 27 15:31:03 2020 +0100
+++ b/schema/search_functions.sql	Fri Mar 27 15:34:16 2020 +0100
@@ -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
 
@@ -42,7 +42,10 @@
 END;
 $$;
 
-CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
+CREATE OR REPLACE FUNCTION search_bottlenecks(
+  search_string text,
+  search_time timestamp with time zone)
+RETURNS jsonb
   LANGUAGE plpgsql STABLE PARALLEL SAFE
   AS $$
 DECLARE
@@ -57,7 +60,7 @@
             FROM waterway.bottlenecks
             WHERE (objnam ILIKE '%' || search_string || '%'
                    OR bottleneck_id ILIKE '%' || search_string || '%')
-              AND validity @> now()
+              AND validity @> search_time
           ORDER BY name) r;
   RETURN _result;
 END;
@@ -159,12 +162,16 @@
 END;
 $$;
 
-CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
+CREATE OR REPLACE FUNCTION search_most(
+  search_string text,
+  search_time timestamp with time zone)
+RETURNS jsonb
   LANGUAGE plpgsql STABLE PARALLEL SAFE
   AS $$
 BEGIN
   RETURN search_hectometre(search_string)
-         || search_bottlenecks(search_string)
+         || search_bottlenecks(search_string,
+                               COALESCE(search_time, current_timestamp))
          || search_gauges(search_string)
          || search_sections(search_string)
          || search_stretches(search_string)
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1432/01.search_functions-time.sql	Fri Mar 27 15:34:16 2020 +0100
@@ -0,0 +1,58 @@
+-- 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) 2018,2019,2020 by via donau
+--   – Österreichische Wasserstraßen-Gesellschaft mbH
+-- Software engineering by Intevation GmbH
+
+-- Author(s):
+--  * Sascha Wilde <wilde@intevation.de>
+
+-- This update adds the new argument "search_time" to
+-- search_bottlenecks() and the meta function search_most().
+
+DROP FUNCTION search_bottlenecks(text);
+CREATE OR REPLACE FUNCTION search_bottlenecks(
+  search_string text,
+  search_time timestamp with time zone)
+RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+DECLARE
+  _result jsonb;
+BEGIN
+  SELECT COALESCE(json_agg(r),'[]')
+    INTO _result
+    FROM (SELECT objnam AS name,
+                 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
+                 'bottleneck' AS type,
+                 bottleneck_id AS location
+            FROM waterway.bottlenecks
+            WHERE (objnam ILIKE '%' || search_string || '%'
+                   OR bottleneck_id ILIKE '%' || search_string || '%')
+              AND validity @> search_time
+          ORDER BY name) r;
+  RETURN _result;
+END;
+$$;
+
+DROP FUNCTION search_most(text);
+CREATE OR REPLACE FUNCTION search_most(
+  search_string text,
+  search_time timestamp with time zone)
+RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+BEGIN
+  RETURN search_hectometre(search_string)
+         || search_bottlenecks(search_string,
+                               COALESCE(search_time, current_timestamp))
+         || search_gauges(search_string)
+         || search_sections(search_string)
+         || search_stretches(search_string)
+         || search_cities(search_string);
+END;
+$$;