1 from itertools import chain
5 from place_inserter import PlaceColumn
6 from table_compare import NominatimID, DBRow
8 from nominatim.indexer.indexer import Indexer
10 def check_database_integrity(context):
11 """ Check some generic constraints on the tables.
13 # place_addressline should not have duplicate (place_id, address_place_id)
14 cur = context.db.cursor()
15 cur.execute("""SELECT count(*) FROM
16 (SELECT place_id, address_place_id, count(*) as c
17 FROM place_addressline GROUP BY place_id, address_place_id) x
19 assert cur.fetchone()[0] == 0, "Duplicates found in place_addressline"
22 ################################ GIVEN ##################################
24 @given("the (?P<named>named )?places")
25 def add_data_to_place_table(context, named):
26 """ Add entries into the place table. 'named places' makes sure that
27 the entries get a random name when none is explicitly given.
29 with context.db.cursor() as cur:
30 cur.execute('ALTER TABLE place DISABLE TRIGGER place_before_insert')
31 for row in context.table:
32 PlaceColumn(context).add_row(row, named is not None).db_insert(cur)
33 cur.execute('ALTER TABLE place ENABLE TRIGGER place_before_insert')
35 @given("the relations")
36 def add_data_to_planet_relations(context):
37 """ Add entries into the osm2pgsql relation middle table. This is needed
38 for tests on data that looks up members.
40 with context.db.cursor() as cur:
41 for r in context.table:
47 for m in r['members'].split(','):
50 parts.insert(last_node, int(mid.oid))
54 parts.insert(last_way, int(mid.oid))
57 parts.append(int(mid.oid))
59 members.extend((mid.typ.lower() + mid.oid, mid.cls or ''))
63 tags = chain.from_iterable([(h[5:], r[h]) for h in r.headings if h.startswith("tags+")])
65 cur.execute("""INSERT INTO planet_osm_rels (id, way_off, rel_off, parts, members, tags)
66 VALUES (%s, %s, %s, %s, %s, %s)""",
67 (r['id'], last_node, last_way, parts, members, list(tags)))
70 def add_data_to_planet_ways(context):
71 """ Add entries into the osm2pgsql way middle table. This is necessary for
72 tests on that that looks up node ids in this table.
74 with context.db.cursor() as cur:
75 for r in context.table:
76 tags = chain.from_iterable([(h[5:], r[h]) for h in r.headings if h.startswith("tags+")])
77 nodes = [ int(x.strip()) for x in r['nodes'].split(',') ]
79 cur.execute("INSERT INTO planet_osm_ways (id, nodes, tags) VALUES (%s, %s, %s)",
80 (r['id'], nodes, list(tags)))
82 ################################ WHEN ##################################
85 def import_and_index_data_from_place_table(context):
86 """ Import data previously set up in the place table.
88 context.nominatim.copy_from_place(context.db)
90 # XXX use tool function as soon as it is ported
91 with context.db.cursor() as cur:
92 with (context.nominatim.src_dir / 'lib-sql' / 'postcode_tables.sql').open('r') as fd:
93 cur.execute(fd.read())
95 INSERT INTO location_postcode
96 (place_id, indexed_status, country_code, postcode, geometry)
97 SELECT nextval('seq_place'), 1, country_code,
98 upper(trim (both ' ' from address->'postcode')) as pc,
99 ST_Centroid(ST_Collect(ST_Centroid(geometry)))
101 WHERE address ? 'postcode' AND address->'postcode' NOT SIMILAR TO '%(,|;)%'
102 AND geometry IS NOT null
103 GROUP BY country_code, pc""")
105 # Call directly as the refresh function does not include postcodes.
106 indexer = Indexer(context.nominatim.get_libpq_dsn(), 1)
107 indexer.index_full(analyse=False)
109 check_database_integrity(context)
111 @when("updating places")
112 def update_place_table(context):
113 """ Update the place table with the given data. Also runs all triggers
114 related to updates and reindexes the new data.
116 context.nominatim.run_nominatim('refresh', '--functions')
117 with context.db.cursor() as cur:
118 for row in context.table:
119 PlaceColumn(context).add_row(row, False).db_insert(cur)
121 context.nominatim.reindex_placex(context.db)
122 check_database_integrity(context)
124 @when("updating postcodes")
125 def update_postcodes(context):
126 """ Rerun the calculation of postcodes.
128 context.nominatim.run_nominatim('refresh', '--postcodes')
130 @when("marking for delete (?P<oids>.*)")
131 def delete_places(context, oids):
132 """ Remove entries from the place table. Multiple ids may be given
133 separated by commas. Also runs all triggers
134 related to updates and reindexes the new data.
136 context.nominatim.run_nominatim('refresh', '--functions')
137 with context.db.cursor() as cur:
138 for oid in oids.split(','):
139 NominatimID(oid).query_osm_id(cur, 'DELETE FROM place WHERE {}')
141 context.nominatim.reindex_placex(context.db)
143 ################################ THEN ##################################
145 @then("(?P<table>placex|place) contains(?P<exact> exactly)?")
146 def check_place_contents(context, table, exact):
147 """ Check contents of place/placex tables. Each row represents a table row
148 and all data must match. Data not present in the expected table, may
149 be arbitry. The rows are identified via the 'object' column which must
150 have an identifier of the form '<NRW><osm id>[:<class>]'. When multiple
151 rows match (for example because 'class' was left out and there are
152 multiple entries for the given OSM object) then all must match. All
153 expected rows are expected to be present with at least one database row.
154 When 'exactly' is given, there must not be additional rows in the database.
156 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
157 expected_content = set()
158 for row in context.table:
159 nid = NominatimID(row['object'])
160 query = 'SELECT *, ST_AsText(geometry) as geomtxt, ST_GeometryType(geometry) as geometrytype'
161 if table == 'placex':
162 query += ' ,ST_X(centroid) as cx, ST_Y(centroid) as cy'
163 query += " FROM %s WHERE {}" % (table, )
164 nid.query_osm_id(cur, query)
165 assert cur.rowcount > 0, "No rows found for " + row['object']
169 expected_content.add((res['osm_type'], res['osm_id'], res['class']))
171 DBRow(nid, res, context).assert_row(row, ['object'])
174 cur.execute('SELECT osm_type, osm_id, class from {}'.format(table))
175 assert expected_content == set([(r[0], r[1], r[2]) for r in cur])
178 @then("(?P<table>placex|place) has no entry for (?P<oid>.*)")
179 def check_place_has_entry(context, table, oid):
180 """ Ensure that no database row for the given object exists. The ID
181 must be of the form '<NRW><osm id>[:<class>]'.
183 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
184 NominatimID(oid).query_osm_id(cur, "SELECT * FROM %s where {}" % table)
185 assert cur.rowcount == 0, \
186 "Found {} entries for ID {}".format(cur.rowcount, oid)
189 @then("search_name contains(?P<exclude> not)?")
190 def check_search_name_contents(context, exclude):
191 """ Check contents of place/placex tables. Each row represents a table row
192 and all data must match. Data not present in the expected table, may
193 be arbitry. The rows are identified via the 'object' column which must
194 have an identifier of the form '<NRW><osm id>[:<class>]'. All
195 expected rows are expected to be present with at least one database row.
197 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
198 for row in context.table:
199 nid = NominatimID(row['object'])
200 nid.row_by_place_id(cur, 'search_name',
201 ['ST_X(centroid) as cx', 'ST_Y(centroid) as cy'])
202 assert cur.rowcount > 0, "No rows found for " + row['object']
205 db_row = DBRow(nid, res, context)
206 for name, value in zip(row.headings, row.cells):
207 if name in ('name_vector', 'nameaddress_vector'):
208 items = [x.strip() for x in value.split(',')]
209 with context.db.cursor() as subcur:
210 subcur.execute(""" SELECT word_id, word_token
211 FROM word, (SELECT unnest(%s::TEXT[]) as term) t
212 WHERE word_token = make_standard_name(t.term)
213 and class is null and country_code is null
216 SELECT word_id, word_token
217 FROM word, (SELECT unnest(%s::TEXT[]) as term) t
218 WHERE word_token = ' ' || make_standard_name(t.term)
219 and class is null and country_code is null
222 (list(filter(lambda x: not x.startswith('#'), items)),
223 list(filter(lambda x: x.startswith('#'), items))))
225 assert subcur.rowcount >= len(items), \
226 "No word entry found for {}. Entries found: {!s}".format(value, subcur.rowcount)
228 present = wid[0] in res[name]
230 assert not present, "Found term for {}/{}: {}".format(row['object'], name, wid[1])
232 assert present, "Missing term for {}/{}: {}".fromat(row['object'], name, wid[1])
233 elif name != 'object':
234 assert db_row.contains(name, value), db_row.assert_msg(name, value)
236 @then("search_name has no entry for (?P<oid>.*)")
237 def check_search_name_has_entry(context, oid):
238 """ Check that there is noentry in the search_name table for the given
239 objects. IDs are in format '<NRW><osm id>[:<class>]'.
241 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
242 NominatimID(oid).row_by_place_id(cur, 'search_name')
244 assert cur.rowcount == 0, \
245 "Found {} entries for ID {}".format(cur.rowcount, oid)
247 @then("location_postcode contains exactly")
248 def check_location_postcode(context):
249 """ Check full contents for location_postcode table. Each row represents a table row
250 and all data must match. Data not present in the expected table, may
251 be arbitry. The rows are identified via 'country' and 'postcode' columns.
252 All rows must be present as excepted and there must not be additional
255 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
256 cur.execute("SELECT *, ST_AsText(geometry) as geomtxt FROM location_postcode")
257 assert cur.rowcount == len(list(context.table)), \
258 "Postcode table has {} rows, expected {}.".foramt(cur.rowcount, len(list(context.table)))
262 key = (row['country_code'], row['postcode'])
263 assert key not in results, "Postcode table has duplicate entry: {}".format(row)
264 results[key] = DBRow((row['country_code'],row['postcode']), row, context)
266 for row in context.table:
267 db_row = results.get((row['country'],row['postcode']))
268 assert db_row is not None, \
269 "Missing row for country '{r['country']}' postcode '{r['postcode']}'.".format(r=row)
271 db_row.assert_row(row, ('country', 'postcode'))
273 @then("word contains(?P<exclude> not)?")
274 def check_word_table(context, exclude):
275 """ Check the contents of the word table. Each row represents a table row
276 and all data must match. Data not present in the expected table, may
277 be arbitry. The rows are identified via all given columns.
279 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
280 for row in context.table:
281 wheres = ' AND '.join(["{} = %s".format(h) for h in row.headings])
282 cur.execute("SELECT * from word WHERE " + wheres, list(row.cells))
284 assert cur.rowcount == 0, "Row still in word table: %s" % '/'.join(values)
286 assert cur.rowcount > 0, "Row not in word table: %s" % '/'.join(values)
288 @then("place_addressline contains")
289 def check_place_addressline(context):
290 """ Check the contents of the place_addressline table. Each row represents
291 a table row and all data must match. Data not present in the expected
292 table, may be arbitry. The rows are identified via the 'object' column,
293 representing the addressee and the 'address' column, representing the
296 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
297 for row in context.table:
298 nid = NominatimID(row['object'])
299 pid = nid.get_place_id(cur)
300 apid = NominatimID(row['address']).get_place_id(cur)
301 cur.execute(""" SELECT * FROM place_addressline
302 WHERE place_id = %s AND address_place_id = %s""",
304 assert cur.rowcount > 0, \
305 "No rows found for place %s and address %s" % (row['object'], row['address'])
308 DBRow(nid, res, context).assert_row(row, ('address', 'object'))
310 @then("place_addressline doesn't contain")
311 def check_place_addressline_exclude(context):
312 """ Check that the place_addressline doesn't contain any entries for the
313 given addressee/address item pairs.
315 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
316 for row in context.table:
317 pid = NominatimID(row['object']).get_place_id(cur)
318 apid = NominatimID(row['address']).get_place_id(cur)
319 cur.execute(""" SELECT * FROM place_addressline
320 WHERE place_id = %s AND address_place_id = %s""",
322 assert cur.rowcount == 0, \
323 "Row found for place %s and address %s" % (row['object'], row['address'])
325 @then("W(?P<oid>\d+) expands to(?P<neg> no)? interpolation")
326 def check_location_property_osmline(context, oid, neg):
327 """ Check that the given way is present in the interpolation table.
329 with context.db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
330 cur.execute("""SELECT *, ST_AsText(linegeo) as geomtxt
331 FROM location_property_osmline
332 WHERE osm_id = %s AND startnumber IS NOT NULL""",
336 assert cur.rowcount == 0, "Interpolation found for way {}.".format(oid)
339 todo = list(range(len(list(context.table))))
342 row = context.table[i]
343 if (int(row['start']) == res['startnumber']
344 and int(row['end']) == res['endnumber']):
348 assert False, "Unexpected row " + str(res)
350 DBRow(oid, res, context).assert_row(row, ('start', 'end'))