optimize indexes for interpolation lines

[nominatim.git] / nominatim / tools / migration.py

diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py
index 5484834182d876f540eb919b605abff458bbabc0..94058f334e94ef9954e0f6ea15a0bbbafd875d9f 100644 (file)
--- a/nominatim/tools/migration.py
+++ b/nominatim/tools/migration.py
@@ -1,13 +1,20 @@
+# SPDX-License-Identifier: GPL-2.0-only
+#
+# This file is part of Nominatim. (https://nominatim.org)
+#
+# Copyright (C) 2022 by the Nominatim developer community.
+# For a full list of authors see the git log.

 """
 Functions for database migration to newer software versions.
 """
 import logging
 
 """
 Functions for database migration to newer software versions.
 """
 import logging
 

-from ..db import properties
-from ..db.connection import connect
-from ..version import NOMINATIM_VERSION
-from . import refresh, database_import
-from ..errors import UsageError
+from nominatim.db import properties
+from nominatim.db.connection import connect
+from nominatim.version import NOMINATIM_VERSION
+from nominatim.tools import refresh
+from nominatim.tokenizer import factory as tokenizer_factory
+from nominatim.errors import UsageError

 
 LOG = logging.getLogger()
 
 
 LOG = logging.getLogger()
 


@@ -25,7 +32,7 @@ def migrate(config, paths):
 
         if db_version_str is not None:
             parts = db_version_str.split('.')
 
         if db_version_str is not None:
             parts = db_version_str.split('.')

-            db_version = tuple([int(x) for x in parts[:2] + parts[2].split('-')])
+            db_version = tuple(int(x) for x in parts[:2] + parts[2].split('-'))

 
             if db_version == NOMINATIM_VERSION:
                 LOG.warning("Database already at latest version (%s)", db_version_str)
 
             if db_version == NOMINATIM_VERSION:
                 LOG.warning("Database already at latest version (%s)", db_version_str)


@@ -43,11 +50,14 @@ def migrate(config, paths):
                             '{0[0]}.{0[1]}.{0[2]}-{0[3]}'.format(version))
                 kwargs = dict(conn=conn, config=config, paths=paths)
                 func(**kwargs)
                             '{0[0]}.{0[1]}.{0[2]}-{0[3]}'.format(version))
                 kwargs = dict(conn=conn, config=config, paths=paths)
                 func(**kwargs)

+                conn.commit()

                 has_run_migration = True
 
         if has_run_migration:
             LOG.warning('Updating SQL functions.')
                 has_run_migration = True
 
         if has_run_migration:
             LOG.warning('Updating SQL functions.')

-            refresh.create_functions(conn, config, paths.sqllib_dir)
+            refresh.create_functions(conn, config)
+            tokenizer = tokenizer_factory.get_tokenizer_for_db(config)
+            tokenizer.update_sql_functions(config)

 
         properties.set_property(conn, 'database_version',
                                 '{0[0]}.{0[1]}.{0[2]}-{0[3]}'.format(NOMINATIM_VERSION))
 
         properties.set_property(conn, 'database_version',
                                 '{0[0]}.{0[1]}.{0[2]}-{0[3]}'.format(NOMINATIM_VERSION))


@@ -92,6 +102,7 @@ def _migration(major, minor, patch=0, dbpatch=0):
     """
     def decorator(func):
         _MIGRATION_FUNCTIONS.append(((major, minor, patch, dbpatch), func))
     """
     def decorator(func):
         _MIGRATION_FUNCTIONS.append(((major, minor, patch, dbpatch), func))

+        return func

 
     return decorator
 
 
     return decorator
 


@@ -108,17 +119,6 @@ def import_status_timestamp_change(conn, **_):
                        TYPE timestamp with time zone;""")
 
 
                        TYPE timestamp with time zone;""")
 
 

-@_migration(3, 5, 0, 99)
-def install_database_module_in_project_directory(conn, config, paths, **_):
-    """ Install database module in project directory.
-
-        The database module needs to be present in the project directory
-        since those were introduced.
-    """
-    database_import.install_module(paths.module_dir, paths.project_dir,
-                                   config.DATABASE_MODULE_PATH, conn=conn)
-
-

 @_migration(3, 5, 0, 99)
 def add_nominatim_property_table(conn, config, **_):
     """ Add nominatim_property table.
 @_migration(3, 5, 0, 99)
 def add_nominatim_property_table(conn, config, **_):
     """ Add nominatim_property table.


@@ -137,6 +137,9 @@ def change_housenumber_transliteration(conn, **_):
 
         The database schema switched from saving raw housenumbers in
         placex.housenumber to saving transliterated ones.
 
         The database schema switched from saving raw housenumbers in
         placex.housenumber to saving transliterated ones.

+
+        Note: the function create_housenumber_id() has been dropped in later
+              versions.

     """
     with conn.cursor() as cur:
         cur.execute("""CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT)
     """
     with conn.cursor() as cur:
         cur.execute("""CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT)


@@ -146,7 +149,8 @@ def change_housenumber_transliteration(conn, **_):
                        BEGIN
                          SELECT array_to_string(array_agg(trans), ';')
                            INTO normtext
                        BEGIN
                          SELECT array_to_string(array_agg(trans), ';')
                            INTO normtext

-                           FROM (SELECT lookup_word as trans, getorcreate_housenumber_id(lookup_word)
+                           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;
                                  FROM (SELECT make_standard_name(h) as lookup_word
                                        FROM regexp_split_to_table(housenumber, '[,;]') h) x) y;
                          return normtext;


@@ -173,3 +177,42 @@ def switch_placenode_geometry_index(conn, **_):
                               and class = 'place' and type != 'postcode'
                               and linked_place_id is null""")
         cur.execute(""" DROP INDEX IF EXISTS idx_placex_adminname """)
                               and class = 'place' and type != 'postcode'
                               and linked_place_id is null""")
         cur.execute(""" DROP INDEX IF EXISTS idx_placex_adminname """)

+
+
+@_migration(3, 7, 0, 1)
+def install_legacy_tokenizer(conn, config, **_):
+    """ Setup legacy tokenizer.
+
+        If no other tokenizer has been configured yet, then create the
+        configuration for the backwards-compatible legacy tokenizer
+    """
+    if properties.get_property(conn, 'tokenizer') is None:
+        with conn.cursor() as cur:
+            for table in ('placex', 'location_property_osmline'):
+                has_column = cur.scalar("""SELECT count(*) FROM information_schema.columns
+                                           WHERE table_name = %s
+                                           and column_name = 'token_info'""",
+                                        (table, ))
+                if has_column == 0:
+                    cur.execute('ALTER TABLE {} ADD COLUMN token_info JSONB'.format(table))
+        tokenizer = tokenizer_factory.create_tokenizer(config, init_db=False,
+                                                       module_name='legacy')
+
+        tokenizer.migrate_database(config)
+
+
+@_migration(4, 0, 99, 0)
+def create_tiger_housenumber_index(conn, **_):
+    """ Create idx_location_property_tiger_parent_place_id with included
+        house number.
+
+        The inclusion is needed for efficient lookup of housenumbers in
+        full address searches.
+    """
+    if conn.server_version_tuple() >= (11, 0, 0):
+        with conn.cursor() as cur:
+            cur.execute(""" CREATE INDEX IF NOT EXISTS
+                                idx_location_property_tiger_housenumber_migrated
+                            ON location_property_tiger
+                            USING btree(parent_place_id)
+                            INCLUDE (startnumber, endnumber) """)