From d0c91e4acf3ee445da77927834c1f576ec1f618b Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Thu, 12 Oct 2023 13:51:10 +0200 Subject: [PATCH] make lookup call work with sqlite Includes porting unit tests. --- nominatim/db/sqlalchemy_types.py | 37 ++++++++++++++++++++++- test/python/api/conftest.py | 20 ++++++++++++- test/python/api/test_api_lookup.py | 48 ++++++++++++++++++------------ 3 files changed, 84 insertions(+), 21 deletions(-) diff --git a/nominatim/db/sqlalchemy_types.py b/nominatim/db/sqlalchemy_types.py index e9687dff..9d1e48fa 100644 --- a/nominatim/db/sqlalchemy_types.py +++ b/nominatim/db/sqlalchemy_types.py @@ -11,12 +11,38 @@ from typing import Callable, Any, cast import sys import sqlalchemy as sa +from sqlalchemy.ext.compiler import compiles from sqlalchemy import types from nominatim.typing import SaColumn, SaBind #pylint: disable=all +SQLITE_FUNCTION_ALIAS = ( + ('ST_AsEWKB', sa.Text, 'AsEWKB'), + ('ST_AsGeoJSON', sa.Text, 'AsGeoJSON'), + ('ST_AsKML', sa.Text, 'AsKML'), + ('ST_AsSVG', sa.Text, 'AsSVG'), +) + +def _add_function_alias(func: str, ftype: type, alias: str) -> None: + _FuncDef = type(func, (sa.sql.functions.GenericFunction, ), { + "type": ftype, + "name": func, + "identifier": func, + "inherit_cache": True}) + + func_templ = f"{alias}(%s)" + + def _sqlite_impl(element: Any, compiler: Any, **kw: Any) -> Any: + return func_templ % compiler.process(element.clauses, **kw) + + compiles(_FuncDef, 'sqlite')(_sqlite_impl) # type: ignore[no-untyped-call] + +for alias in SQLITE_FUNCTION_ALIAS: + _add_function_alias(*alias) + + class Geometry(types.UserDefinedType): # type: ignore[type-arg] """ Simplified type decorator for PostGIS geometry. This type only supports geometries in 4326 projection. @@ -28,7 +54,7 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg] def get_col_spec(self) -> str: - return f'GEOMETRY' + return f'GEOMETRY({self.subtype}, 4326)' def bind_processor(self, dialect: 'sa.Dialect') -> Callable[[Any], str]: @@ -47,6 +73,10 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg] return process + def column_expression(self, col: SaColumn) -> SaColumn: + return sa.func.ST_AsEWKB(col) + + def bind_expression(self, bindvalue: SaBind) -> SaColumn: return sa.func.ST_GeomFromText(bindvalue, sa.text('4326'), type_=self) @@ -116,3 +146,8 @@ class Geometry(types.UserDefinedType): # type: ignore[type-arg] def ST_LineLocatePoint(self, other: SaColumn) -> SaColumn: return sa.func.ST_LineLocatePoint(self, other, type_=sa.Float) + + +@compiles(Geometry, 'sqlite') # type: ignore[no-untyped-call] +def get_col_spec(self, *args, **kwargs): # type: ignore[no-untyped-def] + return 'GEOMETRY' diff --git a/test/python/api/conftest.py b/test/python/api/conftest.py index 1b5b88ed..cb7f324a 100644 --- a/test/python/api/conftest.py +++ b/test/python/api/conftest.py @@ -16,6 +16,7 @@ import sqlalchemy as sa import nominatim.api as napi from nominatim.db.sql_preprocessor import SQLPreprocessor +from nominatim.tools import convert_sqlite import nominatim.api.logging as loglib class APITester: @@ -178,7 +179,6 @@ def apiobj(temp_db_with_extensions, temp_db_conn, monkeypatch): testapi.async_to_sync(testapi.create_tables()) proc = SQLPreprocessor(temp_db_conn, testapi.api.config) - proc.run_sql_file(temp_db_conn, 'functions/address_lookup.sql') proc.run_sql_file(temp_db_conn, 'functions/ranking.sql') loglib.set_log_output('text') @@ -186,3 +186,21 @@ def apiobj(temp_db_with_extensions, temp_db_conn, monkeypatch): print(loglib.get_and_disable()) testapi.api.close() + + +@pytest.fixture(params=['postgres_db', 'sqlite_db']) +def frontend(request, event_loop, tmp_path): + if request.param == 'sqlite_db': + db = str(tmp_path / 'test_nominatim_python_unittest.sqlite') + + def mkapi(apiobj, options={'reverse'}): + event_loop.run_until_complete(convert_sqlite.convert(Path('/invalid'), + db, options)) + return napi.NominatimAPI(Path('/invalid'), + {'NOMINATIM_DATABASE_DSN': f"sqlite:dbname={db}", + 'NOMINATIM_USE_US_TIGER_DATA': 'yes'}) + elif request.param == 'postgres_db': + def mkapi(apiobj, options=None): + return apiobj.api + + return mkapi diff --git a/test/python/api/test_api_lookup.py b/test/python/api/test_api_lookup.py index 8f5dd17c..48b07776 100644 --- a/test/python/api/test_api_lookup.py +++ b/test/python/api/test_api_lookup.py @@ -7,22 +7,26 @@ """ Tests for lookup API call. """ +import json + import pytest import nominatim.api as napi -def test_lookup_empty_list(apiobj): - assert apiobj.api.lookup([]) == [] +def test_lookup_empty_list(apiobj, frontend): + api = frontend(apiobj, options={'details'}) + assert api.lookup([]) == [] -def test_lookup_non_existing(apiobj): - assert apiobj.api.lookup((napi.PlaceID(332), napi.OsmID('W', 4), - napi.OsmID('W', 4, 'highway'))) == [] +def test_lookup_non_existing(apiobj, frontend): + api = frontend(apiobj, options={'details'}) + assert api.lookup((napi.PlaceID(332), napi.OsmID('W', 4), + napi.OsmID('W', 4, 'highway'))) == [] @pytest.mark.parametrize('idobj', (napi.PlaceID(332), napi.OsmID('W', 4), napi.OsmID('W', 4, 'highway'))) -def test_lookup_single_placex(apiobj, idobj): +def test_lookup_single_placex(apiobj, frontend, idobj): apiobj.add_placex(place_id=332, osm_type='W', osm_id=4, class_='highway', type='residential', name={'name': 'Road'}, address={'city': 'Barrow'}, @@ -36,7 +40,8 @@ def test_lookup_single_placex(apiobj, idobj): centroid=(23, 34), geometry='LINESTRING(23 34, 23.1 34, 23.1 34.1, 23 34)') - result = apiobj.api.lookup([idobj]) + api = frontend(apiobj, options={'details'}) + result = api.lookup([idobj]) assert len(result) == 1 @@ -72,7 +77,7 @@ def test_lookup_single_placex(apiobj, idobj): assert result.geometry == {} -def test_lookup_multiple_places(apiobj): +def test_lookup_multiple_places(apiobj, frontend): apiobj.add_placex(place_id=332, osm_type='W', osm_id=4, class_='highway', type='residential', name={'name': 'Road'}, address={'city': 'Barrow'}, @@ -93,9 +98,10 @@ def test_lookup_multiple_places(apiobj): geometry='LINESTRING(23 34, 23 35)') - result = apiobj.api.lookup((napi.OsmID('W', 1), - napi.OsmID('W', 4), - napi.OsmID('W', 9928))) + api = frontend(apiobj, options={'details'}) + result = api.lookup((napi.OsmID('W', 1), + napi.OsmID('W', 4), + napi.OsmID('W', 9928))) assert len(result) == 2 @@ -103,7 +109,7 @@ def test_lookup_multiple_places(apiobj): @pytest.mark.parametrize('gtype', list(napi.GeometryFormat)) -def test_simple_place_with_geometry(apiobj, gtype): +def test_simple_place_with_geometry(apiobj, frontend, gtype): apiobj.add_placex(place_id=332, osm_type='W', osm_id=4, class_='highway', type='residential', name={'name': 'Road'}, address={'city': 'Barrow'}, @@ -117,8 +123,8 @@ def test_simple_place_with_geometry(apiobj, gtype): centroid=(23, 34), geometry='POLYGON((23 34, 23.1 34, 23.1 34.1, 23 34))') - result = apiobj.api.lookup([napi.OsmID('W', 4)], - geometry_output=gtype) + api = frontend(apiobj, options={'details'}) + result = api.lookup([napi.OsmID('W', 4)], geometry_output=gtype) assert len(result) == 1 assert result[0].place_id == 332 @@ -129,7 +135,7 @@ def test_simple_place_with_geometry(apiobj, gtype): assert list(result[0].geometry.keys()) == [gtype.name.lower()] -def test_simple_place_with_geometry_simplified(apiobj): +def test_simple_place_with_geometry_simplified(apiobj, frontend): apiobj.add_placex(place_id=332, osm_type='W', osm_id=4, class_='highway', type='residential', name={'name': 'Road'}, address={'city': 'Barrow'}, @@ -143,11 +149,15 @@ def test_simple_place_with_geometry_simplified(apiobj): centroid=(23, 34), geometry='POLYGON((23 34, 22.999 34, 23.1 34, 23.1 34.1, 23 34))') - result = apiobj.api.lookup([napi.OsmID('W', 4)], - geometry_output=napi.GeometryFormat.TEXT, - geometry_simplification=0.1) + api = frontend(apiobj, options={'details'}) + result = api.lookup([napi.OsmID('W', 4)], + geometry_output=napi.GeometryFormat.GEOJSON, + geometry_simplification=0.1) assert len(result) == 1 assert result[0].place_id == 332 - assert result[0].geometry == {'text': 'POLYGON((23 34,23.1 34,23.1 34.1,23 34))'} + geom = json.loads(result[0].geometry['geojson']) + + assert geom['type'] == 'Polygon' + assert geom['coordinates'] == [[[23, 34], [23.1, 34], [23.1, 34.1], [23, 34]]] -- 2.39.5