From cc17aa8d6b173351c26ead51507499cb8b481a4b Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Fri, 4 Jan 2019 23:11:47 +0100 Subject: [PATCH] Remove postcodes also from word table when they no longer exist Also adds tests for postcode updates. Fixes #1273. --- sql/update-postcodes.sql | 6 +++ test/bdd/db/import/postcodes.feature | 12 ++++++ test/bdd/db/update/postcode.feature | 58 ++++++++++++++++++++++++++++ test/bdd/steps/db_ops.py | 41 ++++++++++++++++++++ 4 files changed, 117 insertions(+) create mode 100644 test/bdd/db/update/postcode.feature diff --git a/sql/update-postcodes.sql b/sql/update-postcodes.sql index a90abe25..1cbc8858 100644 --- a/sql/update-postcodes.sql +++ b/sql/update-postcodes.sql @@ -48,5 +48,11 @@ INSERT INTO location_postcode SELECT nextval('seq_place'), 1, country_code, pc, centroid FROM tmp_new_postcode_locations new; +-- Remove unused word entries +DELETE FROM word + WHERE class = 'place' AND type = 'postcode' + AND NOT EXISTS (SELECT 0 FROM location_postcode p + WHERE p.postcode = word.word); + -- Finally index the newly inserted postcodes UPDATE location_postcode SET indexed_status = 0 WHERE indexed_status > 0; diff --git a/test/bdd/db/import/postcodes.feature b/test/bdd/db/import/postcodes.feature index 940ac5b6..863a9b3a 100644 --- a/test/bdd/db/import/postcodes.feature +++ b/test/bdd/db/import/postcodes.feature @@ -125,3 +125,15 @@ Feature: Import of postcodes Then placex contains | object | postcode | | W93 | 112 DE 34 | + + Scenario: Postcodes are added to the postcode and word table + Given the places + | osm | class | type | addr+postcode | addr+housenumber | geometry | + | N34 | place | house | 01982 | 111 |country:de | + When importing + Then location_postcode contains exactly + | country | postcode | geometry | + | de | 01982 | country:de | + And word contains + | word | class | type | + | 01982 | place | postcode | diff --git a/test/bdd/db/update/postcode.feature b/test/bdd/db/update/postcode.feature new file mode 100644 index 00000000..fbac9341 --- /dev/null +++ b/test/bdd/db/update/postcode.feature @@ -0,0 +1,58 @@ +@DB +Feature: Update of postcode + Tests for updating of data related to postcodes + + Scenario: A new postcode appears in the postcode and word table + Given the places + | osm | class | type | addr+postcode | addr+housenumber | geometry | + | N34 | place | house | 01982 | 111 |country:de | + When importing + Then location_postcode contains exactly + | country | postcode | geometry | + | de | 01982 | country:de | + When updating places + | osm | class | type | addr+postcode | addr+housenumber | geometry | + | N35 | place | house | 4567 | 5 |country:ch | + And updating postcodes + Then location_postcode contains exactly + | country | postcode | geometry | + | de | 01982 | country:de | + | ch | 4567 | country:ch | + And word contains + | word | class | type | + | 01982 | place | postcode | + | 4567 | place | postcode | + + Scenario: When the last postcode is deleted, it is deleted from postcode and word + Given the places + | osm | class | type | addr+postcode | addr+housenumber | geometry | + | N34 | place | house | 01982 | 111 |country:de | + | N35 | place | house | 4567 | 5 |country:ch | + When importing + And marking for delete N34 + And updating postcodes + Then location_postcode contains exactly + | country | postcode | geometry | + | ch | 4567 | country:ch | + And word contains not + | word | class | type | + | 01982 | place | postcode | + And word contains + | word | class | type | + | 4567 | place | postcode | + + Scenario: A postcode is not deleted from postcode and word when it exist in another country + Given the places + | osm | class | type | addr+postcode | addr+housenumber | geometry | + | N34 | place | house | 01982 | 111 |country:de | + | N35 | place | house | 01982 | 5 |country:ch | + When importing + And marking for delete N34 + And updating postcodes + Then location_postcode contains exactly + | country | postcode | geometry | + | ch | 01982 | country:ch | + And word contains + | word | class | type | + | 01982 | place | postcode | + diff --git a/test/bdd/steps/db_ops.py b/test/bdd/steps/db_ops.py index b50f1d59..885c2df9 100644 --- a/test/bdd/steps/db_ops.py +++ b/test/bdd/steps/db_ops.py @@ -329,6 +329,10 @@ def update_place_table(context): check_database_integrity(context) +@when("updating postcodes") +def update_postcodes(context): + context.nominatim.run_update_script('calculate-postcodes') + @when("marking for delete (?P.*)") def delete_places(context, oids): context.nominatim.run_setup_script( @@ -477,6 +481,43 @@ def check_search_name_contents(context, exclude): context.db.commit() +@then("location_postcode contains exactly") +def check_location_postcode(context): + cur = context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) + + cur.execute("SELECT *, ST_AsText(geometry) as geomtxt FROM location_postcode") + eq_(cur.rowcount, len(list(context.table)), + "Postcode table has %d rows, expected %d rows." + % (cur.rowcount, len(list(context.table)))) + + table = list(cur) + for row in context.table: + for i in range(len(table)): + if table[i]['country_code'] != row['country'] \ + or table[i]['postcode'] != row['postcode']: + continue + for h in row.headings: + if h not in ('country', 'postcode'): + assert_db_column(table[i], h, row[h], context) + +@then("word contains(?P not)?") +def check_word_table(context, exclude): + cur = context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) + + for row in context.table: + wheres = [] + values = [] + for h in row.headings: + wheres.append("%s = %%s" % h) + values.append(row[h]) + cur.execute("SELECT * from word WHERE %s" % ' AND '.join(wheres), values) + if exclude: + eq_(0, cur.rowcount, + "Row still in word table: %s" % '/'.join(values)) + else: + assert_greater(cur.rowcount, 0, + "Row not in word table: %s" % '/'.join(values)) + @then("place_addressline contains") def check_place_addressline(context): cur = context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) -- 2.39.5