X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/bb175cc95898de420781867973b38d033c187e81..1821f68ca0915c0b797efdbae8b2672f9c81985f:/nominatim/tools/migration.py?ds=inline diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py index bcf8f142..28a14455 100644 --- a/nominatim/tools/migration.py +++ b/nominatim/tools/migration.py @@ -1,11 +1,19 @@ +# 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 +from psycopg2 import sql as pysql + from nominatim.db import properties from nominatim.db.connection import connect -from nominatim.version import NOMINATIM_VERSION +from nominatim.version import NOMINATIM_VERSION, version_str from nominatim.tools import refresh from nominatim.tokenizer import factory as tokenizer_factory from nominatim.errors import UsageError @@ -41,7 +49,7 @@ def migrate(config, paths): for version, func in _MIGRATION_FUNCTIONS: if db_version <= version: LOG.warning("Runnning: %s (%s)", func.__doc__.split('\n', 1)[0], - '{0[0]}.{0[1]}.{0[2]}-{0[3]}'.format(version)) + version_str(version)) kwargs = dict(conn=conn, config=config, paths=paths) func(**kwargs) conn.commit() @@ -53,8 +61,7 @@ def migrate(config, paths): 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', version_str()) conn.commit() @@ -119,11 +126,11 @@ def add_nominatim_property_table(conn, config, **_): """ if not conn.table_exists('nominatim_properties'): with conn.cursor() as cur: - cur.execute("""CREATE TABLE nominatim_properties ( - property TEXT, - value TEXT); - GRANT SELECT ON TABLE nominatim_properties TO "{}"; - """.format(config.DATABASE_WEBUSER)) + cur.execute(pysql.SQL("""CREATE TABLE nominatim_properties ( + property TEXT, + value TEXT); + GRANT SELECT ON TABLE nominatim_properties TO {}; + """).format(pysql.Identifier(config.DATABASE_WEBUSER))) @_migration(3, 6, 0, 0) def change_housenumber_transliteration(conn, **_): @@ -188,7 +195,8 @@ def install_legacy_tokenizer(conn, config, **_): and column_name = 'token_info'""", (table, )) if has_column == 0: - cur.execute('ALTER TABLE {} ADD COLUMN token_info JSONB'.format(table)) + cur.execute(pysql.SQL('ALTER TABLE {} ADD COLUMN token_info JSONB') + .format(pysql.Identifier(table))) tokenizer = tokenizer_factory.create_tokenizer(config, init_db=False, module_name='legacy') @@ -210,3 +218,94 @@ def create_tiger_housenumber_index(conn, **_): ON location_property_tiger USING btree(parent_place_id) INCLUDE (startnumber, endnumber) """) + + +@_migration(4, 0, 99, 1) +def create_interpolation_index_on_place(conn, **_): + """ Create idx_place_interpolations for lookup of interpolation lines + on updates. + """ + with conn.cursor() as cur: + cur.execute("""CREATE INDEX IF NOT EXISTS idx_place_interpolations + ON place USING gist(geometry) + WHERE osm_type = 'W' and address ? 'interpolation'""") + + +@_migration(4, 0, 99, 2) +def add_step_column_for_interpolation(conn, **_): + """ Add a new column 'step' to the interpolations table. + + Also convers the data into the stricter format which requires that + startnumbers comply with the odd/even requirements. + """ + if conn.table_has_column('location_property_osmline', 'step'): + return + + with conn.cursor() as cur: + # Mark invalid all interpolations with no intermediate numbers. + cur.execute("""UPDATE location_property_osmline SET startnumber = null + WHERE endnumber - startnumber <= 1 """) + # Align the start numbers where odd/even does not match. + cur.execute("""UPDATE location_property_osmline + SET startnumber = startnumber + 1, + linegeo = ST_LineSubString(linegeo, + 1.0 / (endnumber - startnumber)::float, + 1) + WHERE (interpolationtype = 'odd' and startnumber % 2 = 0) + or (interpolationtype = 'even' and startnumber % 2 = 1) + """) + # Mark invalid odd/even interpolations with no intermediate numbers. + cur.execute("""UPDATE location_property_osmline SET startnumber = null + WHERE interpolationtype in ('odd', 'even') + and endnumber - startnumber = 2""") + # Finally add the new column and populate it. + cur.execute("ALTER TABLE location_property_osmline ADD COLUMN step SMALLINT") + cur.execute("""UPDATE location_property_osmline + SET step = CASE WHEN interpolationtype = 'all' + THEN 1 ELSE 2 END + """) + + +@_migration(4, 0, 99, 3) +def add_step_column_for_tiger(conn, **_): + """ Add a new column 'step' to the tiger data table. + """ + if conn.table_has_column('location_property_tiger', 'step'): + return + + with conn.cursor() as cur: + cur.execute("ALTER TABLE location_property_tiger ADD COLUMN step SMALLINT") + cur.execute("""UPDATE location_property_tiger + SET step = CASE WHEN interpolationtype = 'all' + THEN 1 ELSE 2 END + """) + + +@_migration(4, 0, 99, 4) +def add_derived_name_column_for_country_names(conn, **_): + """ Add a new column 'derived_name' which in the future takes the + country names as imported from OSM data. + """ + if not conn.table_has_column('country_name', 'derived_name'): + with conn.cursor() as cur: + cur.execute("ALTER TABLE country_name ADD COLUMN derived_name public.HSTORE") + + +@_migration(4, 0, 99, 5) +def mark_internal_country_names(conn, config, **_): + """ Names from the country table should be marked as internal to prevent + them from being deleted. Only necessary for ICU tokenizer. + """ + import psycopg2.extras # pylint: disable=import-outside-toplevel + + tokenizer = tokenizer_factory.get_tokenizer_for_db(config) + with tokenizer.name_analyzer() as analyzer: + with conn.cursor() as cur: + psycopg2.extras.register_hstore(cur) + cur.execute("SELECT country_code, name FROM country_name") + + for country_code, names in cur: + if not names: + names = {} + names['countrycode'] = country_code + analyzer.add_country_names(country_code, names)