X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/2bd6c761b0e573189a03e8819c6db715eb711eb1..0278ab7f41bf9bc96a87084c04bfec263c6acc40:/nominatim/tools/migration.py diff --git a/nominatim/tools/migration.py b/nominatim/tools/migration.py index 7854154c..02b7d19a 100644 --- a/nominatim/tools/migration.py +++ b/nominatim/tools/migration.py @@ -15,16 +15,14 @@ from psycopg2 import sql as pysql from nominatim.config import Configuration from nominatim.db import properties from nominatim.db.connection import connect, Connection -from nominatim.version import NOMINATIM_VERSION, version_str +from nominatim.version import NominatimVersion, NOMINATIM_VERSION, parse_version from nominatim.tools import refresh from nominatim.tokenizer import factory as tokenizer_factory from nominatim.errors import UsageError LOG = logging.getLogger() -VersionTuple = Tuple[int, int, int, int] - -_MIGRATION_FUNCTIONS : List[Tuple[VersionTuple, Callable[..., None]]] = [] +_MIGRATION_FUNCTIONS : List[Tuple[NominatimVersion, Callable[..., None]]] = [] def migrate(config: Configuration, paths: Any) -> int: """ Check for the current database version and execute migrations, @@ -37,8 +35,7 @@ def migrate(config: Configuration, paths: Any) -> int: db_version_str = None 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 = parse_version(db_version_str) if db_version == NOMINATIM_VERSION: LOG.warning("Database already at latest version (%s)", db_version_str) @@ -49,31 +46,28 @@ def migrate(config: Configuration, paths: Any) -> int: db_version = _guess_version(conn) - has_run_migration = False for version, func in _MIGRATION_FUNCTIONS: - if db_version <= version: + if db_version < version or \ + (db_version == (3, 5, 0, 99) and version == (3, 5, 0, 99)): title = func.__doc__ or '' - LOG.warning("Running: %s (%s)", title.split('\n', 1)[0], - version_str(version)) + LOG.warning("Running: %s (%s)", title.split('\n', 1)[0], 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.') - refresh.create_functions(conn, config) - tokenizer = tokenizer_factory.get_tokenizer_for_db(config) - tokenizer.update_sql_functions(config) + LOG.warning('Updating SQL functions.') + refresh.create_functions(conn, config) + tokenizer = tokenizer_factory.get_tokenizer_for_db(config) + tokenizer.update_sql_functions(config) - properties.set_property(conn, 'database_version', version_str()) + properties.set_property(conn, 'database_version', str(NOMINATIM_VERSION)) conn.commit() return 0 -def _guess_version(conn: Connection) -> VersionTuple: +def _guess_version(conn: Connection) -> NominatimVersion: """ Guess a database version when there is no property table yet. Only migrations for 3.6 and later are supported, so bail out when the version seems older. @@ -89,7 +83,7 @@ def _guess_version(conn: Connection) -> VersionTuple: 'prior to 3.6.0. Automatic migration not possible.') raise UsageError('Migration not possible.') - return (3, 5, 0, 99) + return NominatimVersion(3, 5, 0, 99) @@ -108,7 +102,8 @@ def _migration(major: int, minor: int, patch: int = 0, there. """ def decorator(func: Callable[..., None]) -> Callable[..., None]: - _MIGRATION_FUNCTIONS.append(((major, minor, patch, dbpatch), func)) + version = NominatimVersion(major, minor, patch, dbpatch) + _MIGRATION_FUNCTIONS.append((version, func)) return func return decorator @@ -315,3 +310,96 @@ def mark_internal_country_names(conn: Connection, config: Configuration, **_: An names = {} names['countrycode'] = country_code analyzer.add_country_names(country_code, names) + + +@_migration(4, 1, 99, 0) +def add_place_deletion_todo_table(conn: Connection, **_: Any) -> None: + """ Add helper table for deleting data on updates. + + The table is only necessary when updates are possible, i.e. + the database is not in freeze mode. + """ + if conn.table_exists('place'): + with conn.cursor() as cur: + cur.execute("""CREATE TABLE IF NOT EXISTS place_to_be_deleted ( + osm_type CHAR(1), + osm_id BIGINT, + class TEXT, + type TEXT, + deferred BOOLEAN)""") + + +@_migration(4, 1, 99, 1) +def split_pending_index(conn: Connection, **_: Any) -> None: + """ Reorganise indexes for pending updates. + """ + if conn.table_exists('place'): + with conn.cursor() as cur: + cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_rank_address_sector + ON placex USING BTREE (rank_address, geometry_sector) + WHERE indexed_status > 0""") + cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_rank_boundaries_sector + ON placex USING BTREE (rank_search, geometry_sector) + WHERE class = 'boundary' and type = 'administrative' + and indexed_status > 0""") + cur.execute("DROP INDEX IF EXISTS idx_placex_pendingsector") + + +@_migration(4, 2, 99, 0) +def enable_forward_dependencies(conn: Connection, **_: Any) -> None: + """ Create indexes for updates with forward dependency tracking (long-running). + """ + if conn.table_exists('planet_osm_ways'): + with conn.cursor() as cur: + cur.execute("""SELECT * FROM pg_indexes + WHERE tablename = 'planet_osm_ways' + and indexdef LIKE '%nodes%'""") + if cur.rowcount == 0: + cur.execute("""CREATE OR REPLACE FUNCTION public.planet_osm_index_bucket(bigint[]) + RETURNS bigint[] + LANGUAGE sql IMMUTABLE + AS $function$ + SELECT ARRAY(SELECT DISTINCT unnest($1) >> 5) + $function$""") + cur.execute("""CREATE INDEX planet_osm_ways_nodes_bucket_idx + ON planet_osm_ways + USING gin (planet_osm_index_bucket(nodes)) + WITH (fastupdate=off)""") + cur.execute("""CREATE INDEX planet_osm_rels_parts_idx + ON planet_osm_rels USING gin (parts) + WITH (fastupdate=off)""") + cur.execute("ANALYZE planet_osm_ways") + + +@_migration(4, 2, 99, 1) +def add_improved_geometry_reverse_placenode_index(conn: Connection, **_: Any) -> None: + """ Create improved index for reverse lookup of place nodes. + """ + with conn.cursor() as cur: + cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPlaceNode + ON placex + USING gist (ST_Buffer(geometry, reverse_place_diameter(rank_search))) + WHERE rank_address between 4 and 25 AND type != 'postcode' + AND name is not null AND linked_place_id is null AND osm_type = 'N' + """) + +@_migration(4, 4, 99, 0) +def create_postcode_area_lookup_index(conn: Connection, **_: Any) -> None: + """ Create index needed for looking up postcode areas from postocde points. + """ + with conn.cursor() as cur: + cur.execute("""CREATE INDEX IF NOT EXISTS idx_placex_postcode_areas + ON placex USING BTREE (country_code, postcode) + WHERE osm_type = 'R' AND class = 'boundary' AND type = 'postal_code' + """) + + +@_migration(4, 4, 99, 1) +def create_postcode_parent_index(conn: Connection, **_: Any) -> None: + """ Create index needed for updating postcodes when a parent changes. + """ + if conn.table_exists('planet_osm_ways'): + with conn.cursor() as cur: + cur.execute("""CREATE INDEX IF NOT EXISTS + idx_location_postcode_parent_place_id + ON location_postcode USING BTREE (parent_place_id)""")