From 96b0699621c964f0f46d81c416278a6a1e1bbfd2 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Fri, 2 Apr 2021 17:28:52 +0200
Subject: [PATCH] add migration for transliterated housenumbers

---
 nominatim/tools/migration.py | 26 ++++++++++++++++++++++++++
 nominatim/version.py         |  2 +-
 2 files changed, 27 insertions(+), 1 deletion(-)

diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py
index 756a1d3a..b5f0b80e 100644
--- a/nominatim/tools/migration.py
+++ b/nominatim/tools/migration.py
@@ -130,3 +130,29 @@ def add_nominatim_property_table(conn, config, **_):
                                value TEXT);
                            GRANT SELECT ON TABLE nominatim_properties TO "{}";
                         """.format(config.DATABASE_WEBUSER))
+
+@_migration(3, 6, 0, 0)
+def change_housenumber_transliteration(conn, **_):
+    """ Transliterate housenumbers.
+
+        The database schema switched from saving raw housenumbers in
+        placex.housenumber to saving transliterated ones.
+    """
+    with conn.cursor() as cur:
+        cur.execute("""CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT)
+                       RETURNS TEXT AS $$
+                       DECLARE
+                         normtext TEXT;
+                       BEGIN
+                         SELECT array_to_string(array_agg(trans), ';')
+                           INTO normtext
+                           FROM (SELECT lookup_word as trans, getorcreate_housenumber_id(lookup_word)
+                                 FROM (SELECT make_standard_name(h) as lookup_word
+                                       FROM regexp_split_to_table(housenumber, '[,;]') h) x) y;
+                         return normtext;
+                       END;
+                       $$ LANGUAGE plpgsql STABLE STRICT;""")
+        cur.execute("DELETE FROM word WHERE class = 'place' and type = 'house'")
+        cur.execute("""UPDATE placex
+                       SET housenumber = create_housenumber_id(housenumber)
+                       WHERE housenumber is not null""")
diff --git a/nominatim/version.py b/nominatim/version.py
index e7f31a12..352e6e55 100644
--- a/nominatim/version.py
+++ b/nominatim/version.py
@@ -10,7 +10,7 @@ Version information for Nominatim.
 # and must always be increased when there is a change to the database or code
 # that requires a migration.
 # Released versions always have a database patch level of 0.
-NOMINATIM_VERSION = (3, 6, 0, 0)
+NOMINATIM_VERSION = (3, 6, 0, 1)
 
 POSTGRESQL_REQUIRED_VERSION = (9, 3)
 POSTGIS_REQUIRED_VERSION = (2, 2)
-- 
2.45.1