From 06204dfcd861bc46547bfbb670dd7ce2e6ef3876 Mon Sep 17 00:00:00 2001 From: lujoh Date: Tue, 17 Oct 2023 18:22:27 -0400 Subject: [PATCH] moved sql function flush_deleted_places() to utils --- lib-sql/functions/place_triggers.sql | 54 ----------------------- lib-sql/functions/utils.sql | 53 +++++++++++++++++++++++ test/python/tools/test_admin.py | 65 ++++++++++------------------ 3 files changed, 77 insertions(+), 95 deletions(-) diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql index 3def6596..f3b6ab2b 100644 --- a/lib-sql/functions/place_triggers.sql +++ b/lib-sql/functions/place_triggers.sql @@ -363,57 +363,3 @@ BEGIN RETURN NULL; END; $$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION flush_deleted_places() - RETURNS INTEGER - AS $$ -BEGIN - -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through - INSERT INTO import_polygon_delete (osm_type, osm_id, class, type) - SELECT osm_type, osm_id, class, type FROM place_to_be_deleted WHERE deferred; - - -- delete from place table - ALTER TABLE place DISABLE TRIGGER place_before_delete; - DELETE FROM place USING place_to_be_deleted - WHERE place.osm_type = place_to_be_deleted.osm_type - and place.osm_id = place_to_be_deleted.osm_id - and place.class = place_to_be_deleted.class - and place.type = place_to_be_deleted.type - and not deferred; - ALTER TABLE place ENABLE TRIGGER place_before_delete; - - -- Mark for delete in the placex table - UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted - WHERE placex.osm_type = 'N' and place_to_be_deleted.osm_type = 'N' - and placex.osm_id = place_to_be_deleted.osm_id - and placex.class = place_to_be_deleted.class - and placex.type = place_to_be_deleted.type - and not deferred; - UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted - WHERE placex.osm_type = 'W' and place_to_be_deleted.osm_type = 'W' - and placex.osm_id = place_to_be_deleted.osm_id - and placex.class = place_to_be_deleted.class - and placex.type = place_to_be_deleted.type - and not deferred; - UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted - WHERE placex.osm_type = 'R' and place_to_be_deleted.osm_type = 'R' - and placex.osm_id = place_to_be_deleted.osm_id - and placex.class = place_to_be_deleted.class - and placex.type = place_to_be_deleted.type - and not deferred; - - -- Mark for delete in interpolations - UPDATE location_property_osmline SET indexed_status = 100 FROM place_to_be_deleted - WHERE place_to_be_deleted.osm_type = 'W' - and place_to_be_deleted.class = 'place' - and place_to_be_deleted.type = 'houses' - and location_property_osmline.osm_id = place_to_be_deleted.osm_id - and not deferred; - - -- Clear todo list. - TRUNCATE TABLE place_to_be_deleted; - - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - diff --git a/lib-sql/functions/utils.sql b/lib-sql/functions/utils.sql index b2771ba1..ff2f037d 100644 --- a/lib-sql/functions/utils.sql +++ b/lib-sql/functions/utils.sql @@ -487,3 +487,56 @@ BEGIN END; $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION flush_deleted_places() + RETURNS INTEGER + AS $$ +BEGIN + -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through + INSERT INTO import_polygon_delete (osm_type, osm_id, class, type) + SELECT osm_type, osm_id, class, type FROM place_to_be_deleted WHERE deferred; + + -- delete from place table + ALTER TABLE place DISABLE TRIGGER place_before_delete; + DELETE FROM place USING place_to_be_deleted + WHERE place.osm_type = place_to_be_deleted.osm_type + and place.osm_id = place_to_be_deleted.osm_id + and place.class = place_to_be_deleted.class + and place.type = place_to_be_deleted.type + and not deferred; + ALTER TABLE place ENABLE TRIGGER place_before_delete; + + -- Mark for delete in the placex table + UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted + WHERE placex.osm_type = 'N' and place_to_be_deleted.osm_type = 'N' + and placex.osm_id = place_to_be_deleted.osm_id + and placex.class = place_to_be_deleted.class + and placex.type = place_to_be_deleted.type + and not deferred; + UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted + WHERE placex.osm_type = 'W' and place_to_be_deleted.osm_type = 'W' + and placex.osm_id = place_to_be_deleted.osm_id + and placex.class = place_to_be_deleted.class + and placex.type = place_to_be_deleted.type + and not deferred; + UPDATE placex SET indexed_status = 100 FROM place_to_be_deleted + WHERE placex.osm_type = 'R' and place_to_be_deleted.osm_type = 'R' + and placex.osm_id = place_to_be_deleted.osm_id + and placex.class = place_to_be_deleted.class + and placex.type = place_to_be_deleted.type + and not deferred; + + -- Mark for delete in interpolations + UPDATE location_property_osmline SET indexed_status = 100 FROM place_to_be_deleted + WHERE place_to_be_deleted.osm_type = 'W' + and place_to_be_deleted.class = 'place' + and place_to_be_deleted.type = 'houses' + and location_property_osmline.osm_id = place_to_be_deleted.osm_id + and not deferred; + + -- Clear todo list. + TRUNCATE TABLE place_to_be_deleted; + + RETURN NULL; +END; +$$ LANGUAGE plpgsql; diff --git a/test/python/tools/test_admin.py b/test/python/tools/test_admin.py index dd96c943..3ce3c8f4 100644 --- a/test/python/tools/test_admin.py +++ b/test/python/tools/test_admin.py @@ -12,6 +12,7 @@ import pytest from nominatim.errors import UsageError from nominatim.tools import admin from nominatim.tokenizer import factory +from nominatim.db.sql_preprocessor import SQLPreprocessor @pytest.fixture(autouse=True) def create_placex_table(project_env, tokenizer_mock, temp_db_cursor, placex_table): @@ -75,8 +76,8 @@ def test_analyse_indexing_with_osm_id(project_env, temp_db_cursor): class TestAdminCleanDeleted: @pytest.fixture(autouse=True) - def setup_polygon_delete(self, project_env, table_factory, temp_db_cursor): - """ Set up import_polygon_delete table and simplified place_force_delete function + def setup_polygon_delete(self, project_env, table_factory, place_table, osmline_table, temp_db_cursor, temp_db_conn, def_config, src_dir): + """ Set up place_force_delete function and related tables """ self.project_env = project_env self.temp_db_cursor = temp_db_cursor @@ -88,51 +89,33 @@ class TestAdminCleanDeleted: ((100, 'N', 'boundary', 'administrative'), (145, 'N', 'boundary', 'administrative'), (175, 'R', 'landcover', 'grass'))) + temp_db_cursor.execute("""INSERT INTO placex (place_id, osm_id, osm_type, class, type, indexed_date, indexed_status) + VALUES(1, 100, 'N', 'boundary', 'administrative', current_date - INTERVAL '1 month', 1), + (2, 145, 'N', 'boundary', 'administrative', current_date - INTERVAL '1 month', 1), + (3, 175, 'R', 'landcover', 'grass', current_date - INTERVAL '1 month', 1)""") + # set up tables and triggers for utils function table_factory('place_to_be_deleted', """osm_id BIGINT, osm_type CHAR(1), class TEXT NOT NULL, type TEXT NOT NULL, deferred BOOLEAN""") - temp_db_cursor.execute("""INSERT INTO placex (place_id, osm_id, osm_type, class, type, indexed_date, indexed_status) - VALUES(1, 100, 'N', 'boundary', 'administrative', current_date - INTERVAL '1 month', 1), - (2, 145, 'N', 'boundary', 'administrative', current_date - INTERVAL '1 month', 1), - (3, 175, 'R', 'landcover', 'grass', current_date - INTERVAL '1 month', 1)""") - temp_db_cursor.execute("""CREATE OR REPLACE FUNCTION flush_deleted_places() - RETURNS INTEGER - AS $$ - BEGIN - UPDATE placex p SET indexed_status = 100 FROM place_to_be_deleted d - WHERE p.osm_type = d.osm_type - AND p.osm_id = d.osm_id - AND p.class = d.class - AND p.type = d.type - AND NOT deferred; - TRUNCATE TABLE place_to_be_deleted; - RETURN NULL; - END; - $$ - LANGUAGE plpgsql; - """) - temp_db_cursor.execute("""CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT) - RETURNS BOOLEAN - AS $$ - DECLARE - osmid BIGINT; - osmtype character(1); - pclass text; - ptype text; - BEGIN - SELECT osm_type, osm_id, class, type FROM placex WHERE place_id = placeid INTO osmtype, osmid, pclass, ptype; - DELETE FROM import_polygon_delete WHERE osm_type = osmtype AND osm_id = osmid AND class = pclass AND type = ptype; - INSERT INTO place_to_be_deleted (osm_type, osm_id, class, type, deferred) - VALUES(osmtype, osmid, pclass, ptype, false); - PERFORM flush_deleted_places(); - RETURN TRUE; - END; - $$ - LANGUAGE plpgsql; - """) + table_factory('country_name', 'partition INT') + table_factory('import_polygon_error', """osm_id BIGINT, + osm_type CHAR(1), + class TEXT NOT NULL, + type TEXT NOT NULL""") + temp_db_cursor.execute("""CREATE OR REPLACE FUNCTION place_delete() + RETURNS TRIGGER AS $$ + BEGIN RETURN NULL; END; + $$ LANGUAGE plpgsql;""") + temp_db_cursor.execute("""CREATE TRIGGER place_before_delete BEFORE DELETE ON place + FOR EACH ROW EXECUTE PROCEDURE place_delete();""") + orig_sql = def_config.lib_dir.sql + def_config.lib_dir.sql = src_dir / 'lib-sql' + sqlproc = SQLPreprocessor(temp_db_conn, def_config) + sqlproc.run_sql_file(temp_db_conn, 'functions/utils.sql') + def_config.lib_dir.sql = orig_sql def test_admin_clean_deleted_no_records(self): -- 2.39.5