# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1563352368 -7200
# Node ID 32e9ae5210f90ddf1637d896a16d774ebe035605
# Parent  7015fb18d5365a8b92253c218ddfd025757337e8
Moved 3.0.1 db migration to 0301 to have a four digit pattern.

diff -r 7015fb18d536 -r 32e9ae5210f9 schema/updates/0301/01.dismar-wwname.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/0301/01.dismar-wwname.sql	Wed Jul 17 10:32:48 2019 +0200
@@ -0,0 +1,2 @@
+ALTER TABLE waterway.distance_marks_virtual
+  ADD COLUMN IF NOT EXISTS wwname varchar;
diff -r 7015fb18d536 -r 32e9ae5210f9 schema/updates/0301/02.search_functions.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/0301/02.search_functions.sql	Wed Jul 17 10:32:48 2019 +0200
@@ -0,0 +1,170 @@
+-- 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 by via donau
+--   – Österreichische Wasserstraßen-Gesellschaft mbH
+-- Software engineering by Intevation GmbH
+
+-- Author(s):
+--  * Sascha Wilde <wilde@intevation.de>
+
+CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+DECLARE
+  _result jsonb;
+BEGIN
+  IF search_string SIMILAR TO '[0-9]+' THEN
+    SELECT COALESCE(json_agg(r),'[]')
+      INTO _result
+      FROM (SELECT (location_code).hectometre || ' rhm' AS name,
+                        isrs_asText(location_code) AS locationcode,
+                        wwname AS location,
+               ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
+        FROM waterway.distance_marks_virtual
+        WHERE (location_code).hectometre = search_string::int) r;
+  ELSIF is_ISRSstring(search_string) THEN
+    SELECT COALESCE(json_agg(r),'[]')
+      INTO _result
+      FROM (SELECT (location_code).hectometre || ' rhm' AS name,
+                        isrs_asText(location_code) AS locationcode,
+                        wwname AS location,
+               ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
+        FROM waterway.distance_marks_virtual
+        WHERE location_code = isrs_fromText(search_string)) r;
+  ELSE
+    _result='[]';
+  END IF;
+  RETURN _result;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) 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
+            FROM waterway.bottlenecks
+            WHERE objnam ILIKE '%' || search_string || '%'
+          ORDER BY name) r;
+  RETURN _result;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+DECLARE
+  _result jsonb;
+BEGIN
+  SELECT COALESCE(json_agg(r),'[]')
+    INTO _result
+    FROM (SELECT name,
+                 country_code AS location,
+                 ST_AsGeoJSON(location)::json AS geom,
+                 'city' AS type
+            FROM waterway.geonames
+            WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC')
+                  AND (name ILIKE '%' || search_string || '%'
+                       OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
+            ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'],
+                                          feature_code::text),
+                     name) r;
+  RETURN _result;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+DECLARE
+  _result jsonb;
+BEGIN
+  IF is_ISRSstring(search_string) THEN
+    SELECT COALESCE(json_agg(r),'[]')
+      INTO _result
+      FROM (
+         SELECT objname AS name,
+                   ST_AsGeoJSON(geom)::json AS geom,
+                   'gauge' AS type,
+                   isrs_asText(location) AS locationcode
+              FROM waterway.gauges
+              WHERE NOT erased AND location = isrs_fromText(search_string)
+            ORDER BY name) r;
+  ELSE
+    SELECT COALESCE(json_agg(r),'[]')
+      INTO _result
+      FROM (
+         SELECT objname AS name,
+                   ST_AsGeoJSON(geom)::json AS geom,
+                   'gauge' AS type,
+                   isrs_asText(location) AS locationcode
+              FROM waterway.gauges
+              WHERE NOT erased AND objname ILIKE '%' || search_string || '%'
+            ORDER BY name) r;
+  END IF;
+  RETURN _result;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+DECLARE
+  _result jsonb;
+BEGIN
+  SELECT COALESCE(json_agg(r),'[]')
+    INTO _result
+    FROM (SELECT id,
+                 objnam AS name,
+                 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
+                 'section' AS type
+            FROM waterway.sections
+            WHERE objnam ILIKE '%' || search_string || '%'
+               OR nobjnam ILIKE '%' || search_string || '%'
+          ORDER BY name) r;
+  RETURN _result;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+DECLARE
+  _result jsonb;
+BEGIN
+  SELECT COALESCE(json_agg(r),'[]')
+    INTO _result
+    FROM (SELECT id,
+                 objnam AS name,
+                 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
+                 'stretch' AS type
+            FROM waterway.stretches
+            WHERE objnam ILIKE '%' || search_string || '%'
+               OR nobjnam ILIKE '%' || search_string || '%'
+          ORDER BY name) r;
+  RETURN _result;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
+  LANGUAGE plpgsql STABLE PARALLEL SAFE
+  AS $$
+BEGIN
+  RETURN search_hectometre(search_string)
+         || search_bottlenecks(search_string)
+         || search_gauges(search_string)
+         || search_sections(search_string)
+         || search_stretches(search_string)
+         || search_cities(search_string);
+END;
+$$;
diff -r 7015fb18d536 -r 32e9ae5210f9 schema/updates/3.0.1/01.dismar-wwname.sql
--- a/schema/updates/3.0.1/01.dismar-wwname.sql	Wed Jul 17 10:29:02 2019 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,2 +0,0 @@
-ALTER TABLE waterway.distance_marks_virtual
-  ADD COLUMN IF NOT EXISTS wwname varchar;
diff -r 7015fb18d536 -r 32e9ae5210f9 schema/updates/3.0.1/02.search_functions.sql
--- a/schema/updates/3.0.1/02.search_functions.sql	Wed Jul 17 10:29:02 2019 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,170 +0,0 @@
--- 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 by via donau
---   – Österreichische Wasserstraßen-Gesellschaft mbH
--- Software engineering by Intevation GmbH
-
--- Author(s):
---  * Sascha Wilde <wilde@intevation.de>
-
-CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb
-  LANGUAGE plpgsql STABLE PARALLEL SAFE
-  AS $$
-DECLARE
-  _result jsonb;
-BEGIN
-  IF search_string SIMILAR TO '[0-9]+' THEN
-    SELECT COALESCE(json_agg(r),'[]')
-      INTO _result
-      FROM (SELECT (location_code).hectometre || ' rhm' AS name,
-                        isrs_asText(location_code) AS locationcode,
-                        wwname AS location,
-               ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
-        FROM waterway.distance_marks_virtual
-        WHERE (location_code).hectometre = search_string::int) r;
-  ELSIF is_ISRSstring(search_string) THEN
-    SELECT COALESCE(json_agg(r),'[]')
-      INTO _result
-      FROM (SELECT (location_code).hectometre || ' rhm' AS name,
-                        isrs_asText(location_code) AS locationcode,
-                        wwname AS location,
-               ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
-        FROM waterway.distance_marks_virtual
-        WHERE location_code = isrs_fromText(search_string)) r;
-  ELSE
-    _result='[]';
-  END IF;
-  RETURN _result;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) 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
-            FROM waterway.bottlenecks
-            WHERE objnam ILIKE '%' || search_string || '%'
-          ORDER BY name) r;
-  RETURN _result;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb
-  LANGUAGE plpgsql STABLE PARALLEL SAFE
-  AS $$
-DECLARE
-  _result jsonb;
-BEGIN
-  SELECT COALESCE(json_agg(r),'[]')
-    INTO _result
-    FROM (SELECT name,
-                 country_code AS location,
-                 ST_AsGeoJSON(location)::json AS geom,
-                 'city' AS type
-            FROM waterway.geonames
-            WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC')
-                  AND (name ILIKE '%' || search_string || '%'
-                       OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
-            ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'],
-                                          feature_code::text),
-                     name) r;
-  RETURN _result;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb
-  LANGUAGE plpgsql STABLE PARALLEL SAFE
-  AS $$
-DECLARE
-  _result jsonb;
-BEGIN
-  IF is_ISRSstring(search_string) THEN
-    SELECT COALESCE(json_agg(r),'[]')
-      INTO _result
-      FROM (
-         SELECT objname AS name,
-                   ST_AsGeoJSON(geom)::json AS geom,
-                   'gauge' AS type,
-                   isrs_asText(location) AS locationcode
-              FROM waterway.gauges
-              WHERE NOT erased AND location = isrs_fromText(search_string)
-            ORDER BY name) r;
-  ELSE
-    SELECT COALESCE(json_agg(r),'[]')
-      INTO _result
-      FROM (
-         SELECT objname AS name,
-                   ST_AsGeoJSON(geom)::json AS geom,
-                   'gauge' AS type,
-                   isrs_asText(location) AS locationcode
-              FROM waterway.gauges
-              WHERE NOT erased AND objname ILIKE '%' || search_string || '%'
-            ORDER BY name) r;
-  END IF;
-  RETURN _result;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb
-  LANGUAGE plpgsql STABLE PARALLEL SAFE
-  AS $$
-DECLARE
-  _result jsonb;
-BEGIN
-  SELECT COALESCE(json_agg(r),'[]')
-    INTO _result
-    FROM (SELECT id,
-                 objnam AS name,
-                 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
-                 'section' AS type
-            FROM waterway.sections
-            WHERE objnam ILIKE '%' || search_string || '%'
-               OR nobjnam ILIKE '%' || search_string || '%'
-          ORDER BY name) r;
-  RETURN _result;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb
-  LANGUAGE plpgsql STABLE PARALLEL SAFE
-  AS $$
-DECLARE
-  _result jsonb;
-BEGIN
-  SELECT COALESCE(json_agg(r),'[]')
-    INTO _result
-    FROM (SELECT id,
-                 objnam AS name,
-                 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
-                 'stretch' AS type
-            FROM waterway.stretches
-            WHERE objnam ILIKE '%' || search_string || '%'
-               OR nobjnam ILIKE '%' || search_string || '%'
-          ORDER BY name) r;
-  RETURN _result;
-END;
-$$;
-
-CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
-  LANGUAGE plpgsql STABLE PARALLEL SAFE
-  AS $$
-BEGIN
-  RETURN search_hectometre(search_string)
-         || search_bottlenecks(search_string)
-         || search_gauges(search_string)
-         || search_sections(search_string)
-         || search_stretches(search_string)
-         || search_cities(search_string);
-END;
-$$;