1 # SPDX-License-Identifier: GPL-2.0-only
3 # This file is part of Nominatim. (https://nominatim.org)
5 # Copyright (C) 2022 by the Nominatim developer community.
6 # For a full list of authors see the git log.
8 Functions for database migration to newer software versions.
10 from typing import List, Tuple, Callable, Any
13 from psycopg2 import sql as pysql
15 from nominatim.config import Configuration
16 from nominatim.db import properties
17 from nominatim.db.connection import connect, Connection
18 from nominatim.version import NOMINATIM_VERSION, version_str
19 from nominatim.tools import refresh
20 from nominatim.tokenizer import factory as tokenizer_factory
21 from nominatim.errors import UsageError
23 LOG = logging.getLogger()
25 VersionTuple = Tuple[int, int, int, int]
27 _MIGRATION_FUNCTIONS : List[Tuple[VersionTuple, Callable[..., None]]] = []
29 def migrate(config: Configuration, paths: Any) -> int:
30 """ Check for the current database version and execute migrations,
33 with connect(config.get_libpq_dsn()) as conn:
34 if conn.table_exists('nominatim_properties'):
35 db_version_str = properties.get_property(conn, 'database_version')
39 if db_version_str is not None:
40 parts = db_version_str.split('.')
41 db_version = tuple(int(x) for x in parts[:2] + parts[2].split('-'))
43 if db_version == NOMINATIM_VERSION:
44 LOG.warning("Database already at latest version (%s)", db_version_str)
47 LOG.info("Detected database version: %s", db_version_str)
49 db_version = _guess_version(conn)
52 has_run_migration = False
53 for version, func in _MIGRATION_FUNCTIONS:
54 if db_version <= version:
55 title = func.__doc__ or ''
56 LOG.warning("Running: %s (%s)", title.split('\n', 1)[0],
58 kwargs = dict(conn=conn, config=config, paths=paths)
61 has_run_migration = True
64 LOG.warning('Updating SQL functions.')
65 refresh.create_functions(conn, config)
66 tokenizer = tokenizer_factory.get_tokenizer_for_db(config)
67 tokenizer.update_sql_functions(config)
69 properties.set_property(conn, 'database_version', version_str())
76 def _guess_version(conn: Connection) -> VersionTuple:
77 """ Guess a database version when there is no property table yet.
78 Only migrations for 3.6 and later are supported, so bail out
79 when the version seems older.
81 with conn.cursor() as cur:
82 # In version 3.6, the country_name table was updated. Check for that.
83 cnt = cur.scalar("""SELECT count(*) FROM
84 (SELECT svals(name) FROM country_name
85 WHERE country_code = 'gb')x;
88 LOG.fatal('It looks like your database was imported with a version '
89 'prior to 3.6.0. Automatic migration not possible.')
90 raise UsageError('Migration not possible.')
96 def _migration(major: int, minor: int, patch: int = 0,
97 dbpatch: int = 0) -> Callable[[Callable[..., None]], Callable[..., None]]:
98 """ Decorator for a single migration step. The parameters describe the
99 version after which the migration is applicable, i.e before changing
100 from the given version to the next, the migration is required.
102 All migrations are run in the order in which they are defined in this
103 file. Do not run global SQL scripts for migrations as you cannot be sure
104 that these scripts do the same in later versions.
106 Functions will always be reimported in full at the end of the migration
107 process, so the migration functions may leave a temporary state behind
110 def decorator(func: Callable[..., None]) -> Callable[..., None]:
111 _MIGRATION_FUNCTIONS.append(((major, minor, patch, dbpatch), func))
117 @_migration(3, 5, 0, 99)
118 def import_status_timestamp_change(conn: Connection, **_: Any) -> None:
119 """ Add timezone to timestamp in status table.
121 The import_status table has been changed to include timezone information
124 with conn.cursor() as cur:
125 cur.execute("""ALTER TABLE import_status ALTER COLUMN lastimportdate
126 TYPE timestamp with time zone;""")
129 @_migration(3, 5, 0, 99)
130 def add_nominatim_property_table(conn: Connection, config: Configuration, **_: Any) -> None:
131 """ Add nominatim_property table.
133 if not conn.table_exists('nominatim_properties'):
134 with conn.cursor() as cur:
135 cur.execute(pysql.SQL("""CREATE TABLE nominatim_properties (
138 GRANT SELECT ON TABLE nominatim_properties TO {};
139 """).format(pysql.Identifier(config.DATABASE_WEBUSER)))
141 @_migration(3, 6, 0, 0)
142 def change_housenumber_transliteration(conn: Connection, **_: Any) -> None:
143 """ Transliterate housenumbers.
145 The database schema switched from saving raw housenumbers in
146 placex.housenumber to saving transliterated ones.
148 Note: the function create_housenumber_id() has been dropped in later
151 with conn.cursor() as cur:
152 cur.execute("""CREATE OR REPLACE FUNCTION create_housenumber_id(housenumber TEXT)
157 SELECT array_to_string(array_agg(trans), ';')
159 FROM (SELECT lookup_word as trans,
160 getorcreate_housenumber_id(lookup_word)
161 FROM (SELECT make_standard_name(h) as lookup_word
162 FROM regexp_split_to_table(housenumber, '[,;]') h) x) y;
165 $$ LANGUAGE plpgsql STABLE STRICT;""")
166 cur.execute("DELETE FROM word WHERE class = 'place' and type = 'house'")
167 cur.execute("""UPDATE placex
168 SET housenumber = create_housenumber_id(housenumber)
169 WHERE housenumber is not null""")
172 @_migration(3, 7, 0, 0)
173 def switch_placenode_geometry_index(conn: Connection, **_: Any) -> None:
174 """ Replace idx_placex_geometry_reverse_placeNode index.
176 Make the index slightly more permissive, so that it can also be used
177 when matching up boundaries and place nodes. It makes the index
178 idx_placex_adminname index unnecessary.
180 with conn.cursor() as cur:
181 cur.execute(""" CREATE INDEX IF NOT EXISTS idx_placex_geometry_placenode ON placex
182 USING GIST (geometry)
183 WHERE osm_type = 'N' and rank_search < 26
184 and class = 'place' and type != 'postcode'
185 and linked_place_id is null""")
186 cur.execute(""" DROP INDEX IF EXISTS idx_placex_adminname """)
189 @_migration(3, 7, 0, 1)
190 def install_legacy_tokenizer(conn: Connection, config: Configuration, **_: Any) -> None:
191 """ Setup legacy tokenizer.
193 If no other tokenizer has been configured yet, then create the
194 configuration for the backwards-compatible legacy tokenizer
196 if properties.get_property(conn, 'tokenizer') is None:
197 with conn.cursor() as cur:
198 for table in ('placex', 'location_property_osmline'):
199 has_column = cur.scalar("""SELECT count(*) FROM information_schema.columns
200 WHERE table_name = %s
201 and column_name = 'token_info'""",
204 cur.execute(pysql.SQL('ALTER TABLE {} ADD COLUMN token_info JSONB')
205 .format(pysql.Identifier(table)))
206 tokenizer = tokenizer_factory.create_tokenizer(config, init_db=False,
207 module_name='legacy')
209 tokenizer.migrate_database(config) # type: ignore[attr-defined]
212 @_migration(4, 0, 99, 0)
213 def create_tiger_housenumber_index(conn: Connection, **_: Any) -> None:
214 """ Create idx_location_property_tiger_parent_place_id with included
217 The inclusion is needed for efficient lookup of housenumbers in
218 full address searches.
220 if conn.server_version_tuple() >= (11, 0, 0):
221 with conn.cursor() as cur:
222 cur.execute(""" CREATE INDEX IF NOT EXISTS
223 idx_location_property_tiger_housenumber_migrated
224 ON location_property_tiger
225 USING btree(parent_place_id)
226 INCLUDE (startnumber, endnumber) """)
229 @_migration(4, 0, 99, 1)
230 def create_interpolation_index_on_place(conn: Connection, **_: Any) -> None:
231 """ Create idx_place_interpolations for lookup of interpolation lines
234 with conn.cursor() as cur:
235 cur.execute("""CREATE INDEX IF NOT EXISTS idx_place_interpolations
236 ON place USING gist(geometry)
237 WHERE osm_type = 'W' and address ? 'interpolation'""")
240 @_migration(4, 0, 99, 2)
241 def add_step_column_for_interpolation(conn: Connection, **_: Any) -> None:
242 """ Add a new column 'step' to the interpolations table.
244 Also converts the data into the stricter format which requires that
245 startnumbers comply with the odd/even requirements.
247 if conn.table_has_column('location_property_osmline', 'step'):
250 with conn.cursor() as cur:
251 # Mark invalid all interpolations with no intermediate numbers.
252 cur.execute("""UPDATE location_property_osmline SET startnumber = null
253 WHERE endnumber - startnumber <= 1 """)
254 # Align the start numbers where odd/even does not match.
255 cur.execute("""UPDATE location_property_osmline
256 SET startnumber = startnumber + 1,
257 linegeo = ST_LineSubString(linegeo,
258 1.0 / (endnumber - startnumber)::float,
260 WHERE (interpolationtype = 'odd' and startnumber % 2 = 0)
261 or (interpolationtype = 'even' and startnumber % 2 = 1)
263 # Mark invalid odd/even interpolations with no intermediate numbers.
264 cur.execute("""UPDATE location_property_osmline SET startnumber = null
265 WHERE interpolationtype in ('odd', 'even')
266 and endnumber - startnumber = 2""")
267 # Finally add the new column and populate it.
268 cur.execute("ALTER TABLE location_property_osmline ADD COLUMN step SMALLINT")
269 cur.execute("""UPDATE location_property_osmline
270 SET step = CASE WHEN interpolationtype = 'all'
275 @_migration(4, 0, 99, 3)
276 def add_step_column_for_tiger(conn: Connection, **_: Any) -> None:
277 """ Add a new column 'step' to the tiger data table.
279 if conn.table_has_column('location_property_tiger', 'step'):
282 with conn.cursor() as cur:
283 cur.execute("ALTER TABLE location_property_tiger ADD COLUMN step SMALLINT")
284 cur.execute("""UPDATE location_property_tiger
285 SET step = CASE WHEN interpolationtype = 'all'
290 @_migration(4, 0, 99, 4)
291 def add_derived_name_column_for_country_names(conn: Connection, **_: Any) -> None:
292 """ Add a new column 'derived_name' which in the future takes the
293 country names as imported from OSM data.
295 if not conn.table_has_column('country_name', 'derived_name'):
296 with conn.cursor() as cur:
297 cur.execute("ALTER TABLE country_name ADD COLUMN derived_name public.HSTORE")
300 @_migration(4, 0, 99, 5)
301 def mark_internal_country_names(conn: Connection, config: Configuration, **_: Any) -> None:
302 """ Names from the country table should be marked as internal to prevent
303 them from being deleted. Only necessary for ICU tokenizer.
305 import psycopg2.extras # pylint: disable=import-outside-toplevel
307 tokenizer = tokenizer_factory.get_tokenizer_for_db(config)
308 with tokenizer.name_analyzer() as analyzer:
309 with conn.cursor() as cur:
310 psycopg2.extras.register_hstore(cur)
311 cur.execute("SELECT country_code, name FROM country_name")
313 for country_code, names in cur:
316 names['countrycode'] = country_code
317 analyzer.add_country_names(country_code, names)
320 @_migration(4, 1, 99, 0)
321 def add_place_deletion_todo_table(conn: Connection, **_: Any) -> None:
322 """ Add helper table for deleting data on updates.
324 The table is only necessary when updates are possible, i.e.
325 the database is not in freeze mode.
327 if conn.table_exists('place'):
328 with conn.cursor() as cur:
329 cur.execute("""CREATE TABLE IF NOT EXISTS place_to_be_deleted (
334 deferred BOOLEAN)""")