--- /dev/null
+{% include('functions/utils.sql') %}
+{% include('functions/normalization.sql') %}
+{% include('functions/ranking.sql') %}
+{% include('functions/importance.sql') %}
+{% include('functions/address_lookup.sql') %}
+{% include('functions/interpolation.sql') %}
+
+{% if 'place' in db.tables %}
+ {% include 'functions/place_triggers.sql' %}
+{% endif %}
+
+{% if 'placex' in db.tables %}
+ {% include 'functions/placex_triggers.sql' %}
+{% endif %}
+
+{% if 'location_postcode' in db.tables %}
+ {% include 'functions/postcode_triggers.sql' %}
+{% endif %}
+
+{% include('functions/partition-functions.sql') %}
END IF;
--then query tiger data
- -- %NOTIGERDATA% IF 0 THEN
+ {% if config.get_bool('USE_US_TIGER_DATA') %}
IF place IS NULL AND in_housenumber >= 0 THEN
SELECT parent_place_id as place_id, 'us' as country_code,
in_housenumber as housenumber, postcode,
WHERE place_id = in_place_id
AND in_housenumber between startnumber and endnumber;
END IF;
- -- %NOTIGERDATA% END IF;
+ {% endif %}
- -- %NOAUXDATA% IF 0 THEN
+ -- then additional data
+ {% if config.get_bool('USE_AUX_LOCATION_DATA') %}
IF place IS NULL THEN
SELECT parent_place_id as place_id, 'us' as country_code,
housenumber, postcode,
FROM location_property_aux
WHERE place_id = in_place_id;
END IF;
- -- %NOAUXDATA% END IF;
+ {% endif %}
-- postcode table
IF place IS NULL THEN
-- Functions for term normalisation and access to the 'word' table.
CREATE OR REPLACE FUNCTION transliteration(text) RETURNS text
- AS '{modulepath}/nominatim.so', 'transliteration'
+ AS '{{ modulepath }}/nominatim.so', 'transliteration'
LANGUAGE c IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION gettokenstring(text) RETURNS text
- AS '{modulepath}/nominatim.so', 'gettokenstring'
+ AS '{{ modulepath }}/nominatim.so', 'gettokenstring'
LANGUAGE c IMMUTABLE STRICT;
r nearfeaturecentr%rowtype;
BEGIN
--- start
- IF in_partition = -partition- THEN
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
FOR r IN
SELECT place_id, keywords, rank_address, rank_search,
min(ST_Distance(feature, centroid)) as distance,
isguess, postcode, centroid
- FROM location_area_large_-partition-
+ FROM location_area_large_{{ partition }}
WHERE geometry && feature
AND is_relevant_geometry(ST_Relate(geometry, feature), ST_GeometryType(feature))
AND rank_address < maxrank
END LOOP;
RETURN;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
END
CONTINUE;
END IF;
--- start
- IF in_partition = -partition- THEN
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
SELECT place_id, keywords, rank_address, rank_search,
min(ST_Distance(feature, centroid)) as distance,
isguess, postcode, centroid INTO r
- FROM location_area_large_-partition-
+ FROM location_area_large_{{ partition }}
WHERE geometry && ST_Expand(feature, item.extent)
AND rank_address between item.from_rank and item.to_rank
AND word_ids_from_name(item.name) && keywords
END IF;
CONTINUE;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
END LOOP;
RETURN TRUE;
END IF;
--- start
- IF in_partition = -partition- THEN
- DELETE from location_area_large_-partition- WHERE place_id = in_place_id;
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
+ DELETE from location_area_large_{{ partition }} WHERE place_id = in_place_id;
RETURN TRUE;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
RETURN TRUE;
END IF;
--- start
- IF in_partition = -partition- THEN
- INSERT INTO location_area_large_-partition- (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
+ INSERT INTO location_area_large_{{ partition }} (partition, place_id, country_code, keywords, rank_search, rank_address, isguess, postcode, centroid, geometry)
values (in_partition, in_place_id, in_country_code, in_keywords, in_rank_search, in_rank_address, in_estimate, postcode, in_centroid, in_geometry);
RETURN TRUE;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
RETURN FALSE;
parent BIGINT;
BEGIN
--- start
- IF in_partition = -partition- THEN
- SELECT place_id FROM search_name_-partition-
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
+ SELECT place_id FROM search_name_{{ partition }}
INTO parent
WHERE name_vector && isin_token
AND centroid && ST_Expand(point, 0.015)
ORDER BY ST_Distance(centroid, point) ASC limit 1;
RETURN parent;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
END
parent BIGINT;
BEGIN
--- start
- IF in_partition = -partition- THEN
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
SELECT place_id
INTO parent
- FROM search_name_-partition-
+ FROM search_name_{{ partition }}
WHERE name_vector && isin_token
AND centroid && ST_Expand(point, 0.04)
AND address_rank between 16 and 25
ORDER BY ST_Distance(centroid, point) ASC limit 1;
RETURN parent;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
END
RETURNS BOOLEAN AS $$
DECLARE
BEGIN
--- start
- IF in_partition = -partition- THEN
- DELETE FROM search_name_-partition- values WHERE place_id = in_place_id;
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
+ DELETE FROM search_name_{{ partition }} values WHERE place_id = in_place_id;
IF in_rank_address > 0 THEN
- INSERT INTO search_name_-partition- (place_id, address_rank, name_vector, centroid)
+ INSERT INTO search_name_{{ partition }} (place_id, address_rank, name_vector, centroid)
values (in_place_id, in_rank_address, in_name_vector, in_geometry);
END IF;
RETURN TRUE;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
RETURN FALSE;
create or replace function deleteSearchName(in_partition INTEGER, in_place_id BIGINT) RETURNS BOOLEAN AS $$
DECLARE
BEGIN
--- start
- IF in_partition = -partition- THEN
- DELETE from search_name_-partition- WHERE place_id = in_place_id;
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
+ DELETE from search_name_{{ partition }} WHERE place_id = in_place_id;
RETURN TRUE;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
DECLARE
BEGIN
--- start
- IF in_partition = -partition- THEN
- DELETE FROM location_road_-partition- where place_id = in_place_id;
- INSERT INTO location_road_-partition- (partition, place_id, country_code, geometry)
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
+ DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
+ INSERT INTO location_road_{{ partition }} (partition, place_id, country_code, geometry)
values (in_partition, in_place_id, in_country_code, in_geometry);
RETURN TRUE;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
RETURN FALSE;
DECLARE
BEGIN
--- start
- IF in_partition = -partition- THEN
- DELETE FROM location_road_-partition- where place_id = in_place_id;
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
+ DELETE FROM location_road_{{ partition }} where place_id = in_place_id;
RETURN TRUE;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
search_diameter FLOAT;
BEGIN
--- start
- IF in_partition = -partition- THEN
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
search_diameter := 0.00005;
WHILE search_diameter < 0.1 LOOP
FOR r IN
- SELECT place_id FROM location_road_-partition-
+ SELECT place_id FROM location_road_{{ partition }}
WHERE ST_DWithin(geometry, point, search_diameter)
ORDER BY ST_Distance(geometry, point) ASC limit 1
LOOP
END LOOP;
RETURN NULL;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
END
p2 := ST_LineInterpolatePoint(line,0.5);
p3 := ST_LineInterpolatePoint(line,1);
--- start
- IF in_partition = -partition- THEN
+{% for partition in db.partitions %}
+ IF in_partition = {{ partition }} THEN
search_diameter := 0.0005;
WHILE search_diameter < 0.01 LOOP
FOR r IN
- SELECT place_id FROM location_road_-partition-
+ SELECT place_id FROM location_road_{{ partition }}
WHERE ST_DWithin(line, geometry, search_diameter)
ORDER BY (ST_distance(geometry, p1)+
ST_distance(geometry, p2)+
END LOOP;
RETURN NULL;
END IF;
--- end
+{% endfor %}
RAISE EXCEPTION 'Unknown partition %', in_partition;
END
partition INTEGER;
BEGIN
- --DEBUG: RAISE WARNING '-----------------------------------------------------------------------------------';
- --DEBUG: RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
+ {% if debug %}
+ RAISE WARNING '-----------------------------------------------------------------------------------';
+ RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
+ {% endif %}
-- filter wrong tupels
IF ST_IsEmpty(NEW.geometry) OR NOT ST_IsValid(NEW.geometry) OR ST_X(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') OR ST_Y(ST_Centroid(NEW.geometry))::text in ('NaN','Infinity','-Infinity') THEN
INSERT INTO import_polygon_error (osm_type, osm_id, class, type, name, country_code, updated, errormessage, prevgeometry, newgeometry)
DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class;
END IF;
- --DEBUG: RAISE WARNING 'Existing: %',existing.osm_id;
- --DEBUG: RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;
+ {% if debug %}RAISE WARNING 'Existing: %',existing.osm_id;{% endif %}
+ {% if debug %}RAISE WARNING 'Existing PlaceX: %',existingplacex.place_id;{% endif %}
-- Log and discard
IF existing.geometry is not null AND st_isvalid(existing.geometry)
(existingplacex.type != NEW.type)))
THEN
+ {% if config.get_bool('LIMIT_REINDEXING') %}
IF existingplacex.osm_type IS NOT NULL THEN
-- sanity check: ignore admin_level changes on places with too many active children
-- or we end up reindexing entire countries because somebody accidentally deleted admin_level
- --LIMIT INDEXING: SELECT count(*) FROM (SELECT 'a' FROM placex , place_addressline where address_place_id = existingplacex.place_id and placex.place_id = place_addressline.place_id and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub INTO i;
- --LIMIT INDEXING: IF i > 100000 THEN
- --LIMIT INDEXING: RETURN null;
- --LIMIT INDEXING: END IF;
+ SELECT count(*) INTO i FROM
+ (SELECT 'a' FROM placex, place_addressline
+ WHERE address_place_id = existingplacex.place_id
+ and placex.place_id = place_addressline.place_id
+ and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub;
+ IF i > 100000 THEN
+ RETURN null;
+ END IF;
END IF;
+ {% endif %}
IF existing.osm_type IS NOT NULL THEN
-- pathological case caused by the triggerless copy into place during initial import
values (NEW.osm_type, NEW.osm_id, NEW.class, NEW.type, NEW.name,
NEW.admin_level, NEW.address, NEW.extratags, NEW.geometry);
- --DEBUG: RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;
+ {% if debug %}RAISE WARNING 'insert done % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,NEW.name;{% endif %}
RETURN NEW;
END IF;
has_rank BOOLEAN;
BEGIN
- --DEBUG: RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
+ {% if debug %}RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;{% endif %}
-- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN
location RECORD;
parent RECORD;
BEGIN
- --DEBUG: RAISE WARNING 'finding street for % %', poi_osm_type, poi_osm_id;
+ {% if debug %}RAISE WARNING 'finding street for % %', poi_osm_type, poi_osm_id;{% endif %}
-- Is this object part of an associatedStreet relation?
FOR location IN
and poi_osm_id = any(x.nodes)
LIMIT 1
LOOP
- --DEBUG: RAISE WARNING 'Get parent from interpolation: %', parent.parent_place_id;
+ {% if debug %}RAISE WARNING 'Get parent from interpolation: %', parent.parent_place_id;{% endif %}
RETURN parent.parent_place_id;
END LOOP;
and p.geometry && bbox
and w.id = p.osm_id and poi_osm_id = any(w.nodes)
LOOP
- --DEBUG: RAISE WARNING 'Node is part of way % ', location.osm_id;
+ {% if debug %}RAISE WARNING 'Node is part of way % ', location.osm_id;{% endif %}
-- Way IS a road then we are on it - that must be our road
IF location.rank_search < 28 THEN
- --DEBUG: RAISE WARNING 'node in way that is a street %',location;
+ {% if debug %}RAISE WARNING 'node in way that is a street %',location;{% endif %}
return location.place_id;
END IF;
ELSEIF ST_Area(bbox) < 0.005 THEN
-- for smaller features get the nearest road
SELECT getNearestRoadPlaceId(poi_partition, bbox) INTO parent_place_id;
- --DEBUG: RAISE WARNING 'Checked for nearest way (%)', parent_place_id;
+ {% if debug %}RAISE WARNING 'Checked for nearest way (%)', parent_place_id;{% endif %}
ELSE
-- for larger features simply find the area with the largest rank that
-- contains the bbox, only use addressable features
IF bnd.osm_type = 'R' THEN
-- see if we have any special relation members
SELECT members FROM planet_osm_rels WHERE id = bnd.osm_id INTO relation_members;
- --DEBUG: RAISE WARNING 'Got relation members';
+ {% if debug %}RAISE WARNING 'Got relation members';{% endif %}
-- Search for relation members with role 'lable'.
IF relation_members IS NOT NULL THEN
FOR rel_member IN
SELECT get_rel_node_members(relation_members, ARRAY['label']) as member
LOOP
- --DEBUG: RAISE WARNING 'Found label member %', rel_member.member;
+ {% if debug %}RAISE WARNING 'Found label member %', rel_member.member;{% endif %}
FOR linked_placex IN
SELECT * from placex
WHERE osm_type = 'N' and osm_id = rel_member.member
and class = 'place'
LOOP
- --DEBUG: RAISE WARNING 'Linked label member';
+ {% if debug %}RAISE WARNING 'Linked label member';{% endif %}
RETURN linked_placex;
END LOOP;
AND placex.rank_search < 26 -- needed to select the right index
AND _st_covers(bnd.geometry, placex.geometry)
LOOP
- --DEBUG: RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;
+ {% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %}
RETURN linked_placex;
END LOOP;
END IF;
AND _st_covers(bnd.geometry, placex.geometry)
ORDER BY make_standard_name(name->'name') = bnd_name desc
LOOP
- --DEBUG: RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;
+ {% if debug %}RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;{% endif %}
RETURN linked_placex;
END LOOP;
END IF;
-- Name searches can be done for ways as well as relations
IF bnd_name is not null THEN
- --DEBUG: RAISE WARNING 'Looking for nodes with matching names';
+ {% if debug %}RAISE WARNING 'Looking for nodes with matching names';{% endif %}
FOR linked_placex IN
SELECT placex.* from placex
WHERE make_standard_name(name->'name') = bnd_name
AND placex.rank_search < 26 -- needed to select the right index
AND _st_covers(bnd.geometry, placex.geometry)
LOOP
- --DEBUG: RAISE WARNING 'Found matching place node %', linked_placex.osm_id;
+ {% if debug %}RAISE WARNING 'Found matching place node %', linked_placex.osm_id;{% endif %}
RETURN linked_placex;
END LOOP;
END IF;
address, country)
ORDER BY rank_address, distance, isguess desc
LOOP
- IF NOT %REVERSE-ONLY% THEN
+ {% if not db.reverse_only %}
nameaddress_vector := array_merge(nameaddress_vector,
location.keywords::int[]);
- END IF;
+ {% endif %}
IF location.place_id is not null THEN
location_isaddress := not address_havelevel[location.rank_address];
END IF;
-- Add it to the list of search terms
- IF NOT %REVERSE-ONLY% THEN
+ {% if not db.reverse_only %}
nameaddress_vector := array_merge(nameaddress_vector,
location.keywords::integer[]);
- END IF;
+ {% endif %}
INSERT INTO place_addressline (place_id, address_place_id, fromarea,
isaddress, distance, cached_rank_address)
diameter FLOAT;
classtable TEXT;
BEGIN
- --DEBUG: RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
+ {% if debug %}RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;{% endif %}
NEW.place_id := nextval('seq_place');
NEW.indexed_status := 1; --STATUS_NEW
END IF;
- --DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
+ {% if debug %}RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;{% endif %}
- RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
+{% if not disable_diff_updates %}
+ -- The following is not needed until doing diff updates, and slows the main index process down
IF NEW.osm_type = 'N' and NEW.rank_search > 28 THEN
-- might be part of an interpolation
USING NEW.place_id, ST_Centroid(NEW.geometry);
END IF;
+{% endif %} -- not disable_diff_updates
+
RETURN NEW;
END;
BEGIN
-- deferred delete
IF OLD.indexed_status = 100 THEN
- --DEBUG: RAISE WARNING 'placex_update delete % %',NEW.osm_type,NEW.osm_id;
+ {% if debug %}RAISE WARNING 'placex_update delete % %',NEW.osm_type,NEW.osm_id;{% endif %}
delete from placex where place_id = OLD.place_id;
RETURN NULL;
END IF;
RETURN NEW;
END IF;
- --DEBUG: RAISE WARNING 'placex_update % % (%)',NEW.osm_type,NEW.osm_id,NEW.place_id;
+ {% if debug %}RAISE WARNING 'placex_update % % (%)',NEW.osm_type,NEW.osm_id,NEW.place_id;{% endif %}
NEW.indexed_date = now();
- IF NOT %REVERSE-ONLY% THEN
+ {% if 'search_name' in db.tables %}
DELETE from search_name WHERE place_id = NEW.place_id;
- END IF;
+ {% endif %}
result := deleteSearchName(NEW.partition, NEW.place_id);
DELETE FROM place_addressline WHERE place_id = NEW.place_id;
result := deleteRoad(NEW.partition, NEW.place_id);
NEW.address := NEW.address - '_unlisted_place'::TEXT;
IF NEW.linked_place_id is not null THEN
- --DEBUG: RAISE WARNING 'place already linked to %', NEW.linked_place_id;
+ {% if debug %}RAISE WARNING 'place already linked to %', NEW.linked_place_id;{% endif %}
RETURN NEW;
END IF;
-- Speed up searches - just use the centroid of the feature
-- cheaper but less acurate
NEW.centroid := ST_PointOnSurface(NEW.geometry);
- --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(NEW.centroid);
+ {% if debug %}RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(NEW.centroid);{% endif %}
-- recompute the ranks, they might change when linking changes
SELECT * INTO NEW.rank_search, NEW.rank_address
parent_address_level := 3;
END IF;
- --DEBUG: RAISE WARNING 'Copy over address tags';
+ {% if debug %}RAISE WARNING 'Copy over address tags';{% endif %}
-- housenumber is a computed field, so start with an empty value
NEW.housenumber := NULL;
IF NEW.address is not NULL THEN
END IF;
NEW.partition := get_partition(NEW.country_code);
END IF;
- --DEBUG: RAISE WARNING 'Country updated: "%"', NEW.country_code;
+ {% if debug %}RAISE WARNING 'Country updated: "%"', NEW.country_code;{% endif %}
-- waterway ways are linked when they are part of a relation and have the same class/type
IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN
LOOP
FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP
IF relation_members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation_members[i],1,1) = 'w' THEN
- --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation_members[i];
+ {% if debug %}RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation_members[i];{% endif %}
FOR linked_node_id IN SELECT place_id FROM placex
WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint
and class = NEW.class and type in ('river', 'stream', 'canal', 'drain', 'ditch')
and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name')
LOOP
UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id;
- IF NOT %REVERSE-ONLY% THEN
+ {% if 'search_name' in db.tables %}
DELETE FROM search_name WHERE place_id = linked_node_id;
- END IF;
+ {% endif %}
END LOOP;
END IF;
END LOOP;
END LOOP;
- --DEBUG: RAISE WARNING 'Waterway processed';
+ {% if debug %}RAISE WARNING 'Waterway processed';{% endif %}
END IF;
NEW.importance := null;
FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id)
INTO NEW.wikipedia,NEW.importance;
---DEBUG: RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;
+{% if debug %}RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;{% endif %}
-- ---------------------------------------------------------------------------
-- For low level elements we inherit from our parent road
IF NEW.rank_search > 27 THEN
- --DEBUG: RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id;
+ {% if debug %}RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id;{% endif %}
NEW.parent_place_id := null;
-- if we have a POI and there is no address information,
END IF;
NEW.country_code := location.country_code;
- --DEBUG: RAISE WARNING 'Got parent details from search name';
+ {% if debug %}RAISE WARNING 'Got parent details from search name';{% endif %}
-- determine postcode
IF NEW.address is not null AND NEW.address ? 'postcode' THEN
name_vector, NEW.rank_search, NEW.rank_address,
upper(trim(NEW.address->'postcode')), NEW.geometry,
NEW.centroid);
- --DEBUG: RAISE WARNING 'Place added to location table';
+ {% if debug %}RAISE WARNING 'Place added to location table';{% endif %}
END IF;
END IF;
- IF not %REVERSE-ONLY% AND (array_length(name_vector, 1) is not NULL
- OR inherited_address is not NULL OR NEW.address is not NULL)
+ {% if not db.reverse_only %}
+ IF array_length(name_vector, 1) is not NULL
+ OR inherited_address is not NULL OR NEW.address is not NULL
THEN
SELECT * INTO name_vector, nameaddress_vector
FROM create_poi_search_terms(NEW.place_id,
VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address,
NEW.importance, NEW.country_code, name_vector,
nameaddress_vector, NEW.centroid);
- --DEBUG: RAISE WARNING 'Place added to search table';
+ {% if debug %}RAISE WARNING 'Place added to search table';{% endif %}
END IF;
END IF;
+ {% endif %}
RETURN NEW;
END IF;
-- ---------------------------------------------------------------------------
-- Full indexing
- --DEBUG: RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id;
+ {% if debug %}RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id;{% endif %}
SELECT * INTO location FROM find_linked_place(NEW);
IF location.place_id is not null THEN
- --DEBUG: RAISE WARNING 'Linked %', location;
+ {% if debug %}RAISE WARNING 'Linked %', location;{% endif %}
-- Use the linked point as the centre point of the geometry,
-- but only if it is within the area of the boundary.
NEW.centroid := geom;
END IF;
- --DEBUG: RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.rank_address;
+ {% if debug %}RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.rank_address;{% endif %}
IF location.rank_address > parent_address_level
and location.rank_address < 26
THEN
UPDATE placex set linked_place_id = NEW.place_id
WHERE place_id = location.place_id;
-- ensure that those places are not found anymore
- IF NOT %REVERSE-ONLY% THEN
+ {% if 'search_name' in db.tables %}
DELETE FROM search_name WHERE place_id = location.place_id;
- END IF;
+ {% endif %}
PERFORM deleteLocationArea(NEW.partition, location.place_id, NEW.rank_search);
SELECT wikipedia, importance
AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R'
THEN
PERFORM create_country(NEW.name, lower(NEW.country_code));
- --DEBUG: RAISE WARNING 'Country names updated';
+ {% if debug %}RAISE WARNING 'Country names updated';{% endif %}
-- Also update the list of country names. Adding an additional sanity
-- check here: make sure the country does overlap with the area where
WHERE ST_Covers(geometry, NEW.centroid) and country_code = NEW.country_code
LIMIT 1
LOOP
- --DEBUG: RAISE WARNING 'Updating names for country '%' with: %', NEW.country_code, NEW.name;
+ {% if debug %}RAISE WARNING 'Updating names for country '%' with: %', NEW.country_code, NEW.name;{% endif %}
UPDATE country_name SET name = name || NEW.name WHERE country_code = NEW.country_code;
END LOOP;
END IF;
NEW.address, geom, NEW.country_code)
INTO NEW.parent_place_id, NEW.postcode, nameaddress_vector;
- --DEBUG: RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;
+ {% if debug %}RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;{% endif %}
IF NEW.address is not null AND NEW.address ? 'postcode'
AND NEW.address->'postcode' not similar to '%(,|;)%' THEN
IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN
result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry, NEW.centroid);
- --DEBUG: RAISE WARNING 'added to location (full)';
+ {% if debug %}RAISE WARNING 'added to location (full)';{% endif %}
END IF;
IF NEW.rank_search between 26 and 27 and NEW.class = 'highway' THEN
result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
- --DEBUG: RAISE WARNING 'insert into road location table (full)';
+ {% if debug %}RAISE WARNING 'insert into road location table (full)';{% endif %}
END IF;
result := insertSearchName(NEW.partition, NEW.place_id, name_vector,
NEW.rank_search, NEW.rank_address, NEW.geometry);
- --DEBUG: RAISE WARNING 'added to search name (full)';
+ {% if debug %}RAISE WARNING 'added to search name (full)';{% endif %}
- IF NOT %REVERSE-ONLY% THEN
+ {% if not db.reverse_only %}
INSERT INTO search_name (place_id, search_rank, address_rank,
importance, country_code, name_vector,
nameaddress_vector, centroid)
VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address,
NEW.importance, NEW.country_code, name_vector,
nameaddress_vector, NEW.centroid);
- END IF;
+ {% endif %}
END IF;
- --DEBUG: RAISE WARNING 'place update % % finsihed.', NEW.osm_type, NEW.osm_id;
+ {% if debug %}RAISE WARNING 'place update % % finsihed.', NEW.osm_type, NEW.osm_id;{% endif %}
RETURN NEW;
END;
IF OLD.linked_place_id is null THEN
update placex set linked_place_id = null, indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
- --DEBUG: RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;{% endif %}
update placex set linked_place_id = null where linked_place_id = OLD.place_id;
- --DEBUG: RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;{% endif %}
ELSE
update placex set indexed_status = 2 where place_id = OLD.linked_place_id and indexed_status = 0;
END IF;
IF OLD.rank_address < 30 THEN
-- mark everything linked to this place for re-indexing
- --DEBUG: RAISE WARNING 'placex_delete:03 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:03 % %',OLD.osm_type,OLD.osm_id;{% endif %}
UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id
and placex.place_id = place_addressline.place_id and indexed_status = 0 and place_addressline.isaddress;
- --DEBUG: RAISE WARNING 'placex_delete:04 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:04 % %',OLD.osm_type,OLD.osm_id;{% endif %}
DELETE FROM place_addressline where address_place_id = OLD.place_id;
- --DEBUG: RAISE WARNING 'placex_delete:05 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:05 % %',OLD.osm_type,OLD.osm_id;{% endif %}
b := deleteRoad(OLD.partition, OLD.place_id);
- --DEBUG: RAISE WARNING 'placex_delete:06 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:06 % %',OLD.osm_type,OLD.osm_id;{% endif %}
update placex set indexed_status = 2 where parent_place_id = OLD.place_id and indexed_status = 0;
- --DEBUG: RAISE WARNING 'placex_delete:07 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:07 % %',OLD.osm_type,OLD.osm_id;{% endif %}
-- reparenting also for OSM Interpolation Lines (and for Tiger?)
update location_property_osmline set indexed_status = 2 where indexed_status = 0 and parent_place_id = OLD.place_id;
END IF;
- --DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id;{% endif %}
IF OLD.rank_address < 26 THEN
b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search);
END IF;
- --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;{% endif %}
IF OLD.name is not null THEN
- IF NOT %REVERSE-ONLY% THEN
+ {% if 'search_name' in db.tables %}
DELETE from search_name WHERE place_id = OLD.place_id;
- END IF;
+ {% endif %}
b := deleteSearchName(OLD.partition, OLD.place_id);
END IF;
- --DEBUG: RAISE WARNING 'placex_delete:10 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:10 % %',OLD.osm_type,OLD.osm_id;{% endif %}
DELETE FROM place_addressline where place_id = OLD.place_id;
- --DEBUG: RAISE WARNING 'placex_delete:11 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:11 % %',OLD.osm_type,OLD.osm_id;{% endif %}
-- remove from tables for special search
classtable := 'place_classtype_' || OLD.class || '_' || OLD.type;
EXECUTE 'DELETE FROM ' || classtable::regclass || ' WHERE place_id = $1' USING OLD.place_id;
END IF;
- --DEBUG: RAISE WARNING 'placex_delete:12 % %',OLD.osm_type,OLD.osm_id;
+ {% if debug %}RAISE WARNING 'placex_delete:12 % %',OLD.osm_type,OLD.osm_id;{% endif %}
RETURN OLD;
IF word_ids is not null THEN
parent_place_id := getNearestNamedRoadPlaceId(partition, centroid, word_ids);
IF parent_place_id is not null THEN
- --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent_place_id;
+ {% if debug %}RAISE WARNING 'Get parent form addr:street: %', parent_place_id;{% endif %}
RETURN parent_place_id;
END IF;
END IF;
IF word_ids is not null THEN
parent_place_id := getNearestNamedPlacePlaceId(partition, centroid, word_ids);
IF parent_place_id is not null THEN
- --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent_place_id;
+ {% if debug %}RAISE WARNING 'Get parent form addr:place: %', parent_place_id;{% endif %}
RETURN parent_place_id;
END IF;
END IF;
--- /dev/null
+"""
+Preprocessing of SQL files.
+"""
+import jinja2
+
+
+def _get_partitions(conn):
+ """ Get the set of partitions currently in use.
+ """
+ with conn.cursor() as cur:
+ cur.execute('SELECT DISTINCT partition FROM country_name')
+ partitions = set([0])
+ for row in cur:
+ partitions.add(row[0])
+
+ return partitions
+
+
+def _get_tables(conn):
+ """ Return the set of tables currently in use.
+ Only includes non-partitioned
+ """
+ with conn.cursor() as cur:
+ cur.execute("SELECT tablename FROM pg_tables WHERE schemaname = 'public'")
+
+ return set((row[0] for row in list(cur)))
+
+class SQLPreprocessor: # pylint: disable=too-few-public-methods
+ """ A environment for preprocessing SQL files from the
+ lib-sql directory.
+
+ The preprocessor provides a number of default filters and variables.
+ The variables may be overwritten when rendering an SQL file.
+
+ The preprocessing is currently based on the jinja2 templating library
+ and follows its syntax.
+ """
+
+ def __init__(self, conn, config, sqllib_dir):
+ self.env = jinja2.Environment(autoescape=False,
+ loader=jinja2.FileSystemLoader(str(sqllib_dir)))
+
+ db_info = {}
+ db_info['partitions'] = _get_partitions(conn)
+ db_info['tables'] = _get_tables(conn)
+ db_info['reverse_only'] = 'search_name' not in db_info['tables']
+
+ self.env.globals['config'] = config
+ self.env.globals['db'] = db_info
+ self.env.globals['modulepath'] = config.DATABASE_MODULE_PATH or \
+ str((config.project_dir / 'module').resolve())
+
+
+ def run_sql_file(self, conn, name, **kwargs):
+ """ Execute the given SQL file on the connection. The keyword arguments
+ may supply additional parameters for preprocessing.
+ """
+ sql = self.env.get_template(name).render(**kwargs)
+
+ with conn.cursor() as cur:
+ cur.execute(sql)
+ conn.commit()
"""
import json
import logging
-import re
from textwrap import dedent
from psycopg2.extras import execute_values
from ..db.utils import execute_file
+from ..db.sql_preprocessor import SQLPreprocessor
from ..version import NOMINATIM_VERSION
LOG = logging.getLogger()
with config_file.open('r') as fdesc:
load_address_levels(conn, 'address_levels', json.load(fdesc))
-PLPGSQL_BASE_MODULES = (
- 'utils.sql',
- 'normalization.sql',
- 'ranking.sql',
- 'importance.sql',
- 'address_lookup.sql',
- 'interpolation.sql'
-)
-
-PLPGSQL_TABLE_MODULES = (
- ('place', 'place_triggers.sql'),
- ('placex', 'placex_triggers.sql'),
- ('location_postcode', 'postcode_triggers.sql')
-)
-
-def _get_standard_function_sql(conn, config, sql_dir, enable_diff_updates, enable_debug):
- """ Read all applicable SQLs containing PL/pgSQL functions, replace
- placefolders and execute them.
- """
- sql_func_dir = sql_dir / 'functions'
- sql = ''
-
- # Get the basic set of functions that is always imported.
- for sql_file in PLPGSQL_BASE_MODULES:
- with (sql_func_dir / sql_file).open('r') as fdesc:
- sql += fdesc.read()
-
- # Some files require the presence of a certain table
- for table, fname in PLPGSQL_TABLE_MODULES:
- if conn.table_exists(table):
- with (sql_func_dir / fname).open('r') as fdesc:
- sql += fdesc.read()
-
- # Replace placeholders.
- sql = sql.replace('{modulepath}',
- config.DATABASE_MODULE_PATH or str((config.project_dir / 'module').resolve()))
-
- if enable_diff_updates:
- sql = sql.replace('RETURN NEW; -- %DIFFUPDATES%', '--')
-
- if enable_debug:
- sql = sql.replace('--DEBUG:', '')
-
- if config.get_bool('LIMIT_REINDEXING'):
- sql = sql.replace('--LIMIT INDEXING:', '')
-
- if not config.get_bool('USE_US_TIGER_DATA'):
- sql = sql.replace('-- %NOTIGERDATA% ', '')
-
- if not config.get_bool('USE_AUX_LOCATION_DATA'):
- sql = sql.replace('-- %NOAUXDATA% ', '')
- reverse_only = 'false' if conn.table_exists('search_name') else 'true'
-
- return sql.replace('%REVERSE-ONLY%', reverse_only)
-
-
-def replace_partition_string(sql, partitions):
- """ Replace a partition template with the actual partition code.
- """
- for match in re.findall('^-- start(.*?)^-- end', sql, re.M | re.S):
- repl = ''
- for part in partitions:
- repl += match.replace('-partition-', str(part))
- sql = sql.replace(match, repl)
-
- return sql
-
-def _get_partition_function_sql(conn, sql_dir):
- """ Create functions that work on partition tables.
- """
- with conn.cursor() as cur:
- cur.execute('SELECT distinct partition FROM country_name')
- partitions = set([0])
- for row in cur:
- partitions.add(row[0])
-
- with (sql_dir / 'partition-functions.src.sql').open('r') as fdesc:
- sql = fdesc.read()
-
- return replace_partition_string(sql, sorted(partitions))
-
-def create_functions(conn, config, sql_dir,
+def create_functions(conn, config, sqllib_dir,
enable_diff_updates=True, enable_debug=False):
""" (Re)create the PL/pgSQL functions.
"""
- sql = _get_standard_function_sql(conn, config, sql_dir,
- enable_diff_updates, enable_debug)
- sql += _get_partition_function_sql(conn, sql_dir)
+ sql = SQLPreprocessor(conn, config, sqllib_dir)
- with conn.cursor() as cur:
- cur.execute(sql)
+ sql.run_sql_file(conn, 'functions.sql',
+ disable_diff_update=not enable_diff_updates,
+ debug=enable_debug)
- conn.commit()
WEBSITE_SCRIPTS = (
--- /dev/null
+"""
+Tests for SQL preprocessing.
+"""
+from pathlib import Path
+
+import pytest
+
+from nominatim.db.sql_preprocessor import SQLPreprocessor
+
+@pytest.fixture
+def sql_factory(tmp_path):
+ def _mk_sql(sql_body):
+ (tmp_path / 'test.sql').write_text("""
+ CREATE OR REPLACE FUNCTION test() RETURNS TEXT
+ AS $$
+ BEGIN
+ {}
+ END;
+ $$ LANGUAGE plpgsql IMMUTABLE;""".format(sql_body))
+ return 'test.sql'
+
+ return _mk_sql
+
+
+@pytest.fixture
+def sql_preprocessor(temp_db_conn, tmp_path, def_config, monkeypatch, table_factory):
+ monkeypatch.setenv('NOMINATIM_DATABASE_MODULE_PATH', '.')
+ table_factory('country_name', 'partition INT', (0, 1, 2))
+ return SQLPreprocessor(temp_db_conn, def_config, tmp_path)
+
+@pytest.mark.parametrize("expr,ret", [
+ ("'a'", 'a'),
+ ("'{{db.partitions|join}}'", '012'),
+ ("{% if 'country_name' in db.tables %}'yes'{% else %}'no'{% endif %}", "yes"),
+ ("{% if 'xxx' in db.tables %}'yes'{% else %}'no'{% endif %}", "no"),
+ ("'{{config.DATABASE_MODULE_PATH}}'", '.')
+ ])
+def test_load_file_simple(sql_preprocessor, sql_factory, temp_db_conn, temp_db_cursor, expr, ret):
+ sqlfile = sql_factory("RETURN {};".format(expr))
+
+ sql_preprocessor.run_sql_file(temp_db_conn, sqlfile)
+
+ assert temp_db_cursor.scalar('SELECT test()') == ret
+
+
+def test_load_file_with_params(sql_preprocessor, sql_factory, temp_db_conn, temp_db_cursor):
+ sqlfile = sql_factory("RETURN '{{ foo }} {{ bar }}';")
+
+ sql_preprocessor.run_sql_file(temp_db_conn, sqlfile, bar='XX', foo='ZZ')
+
+ assert temp_db_cursor.scalar('SELECT test()') == 'ZZ XX'
"""
Tests for creating PL/pgSQL functions for Nominatim.
"""
-from pathlib import Path
import pytest
-from nominatim.db.connection import connect
-from nominatim.tools.refresh import _get_standard_function_sql, _get_partition_function_sql
-
-SQL_DIR = (Path(__file__) / '..' / '..' / '..' / 'lib-sql').resolve()
-
-@pytest.fixture
-def db(temp_db):
- with connect('dbname=' + temp_db) as conn:
- yield conn
+from nominatim.tools.refresh import create_functions
@pytest.fixture
-def db_with_tables(db):
- with db.cursor() as cur:
- for table in ('place', 'placex', 'location_postcode'):
- cur.execute('CREATE TABLE {} (place_id BIGINT)'.format(table))
-
- return db
-
-
-def test_standard_functions_replace_module_default(db, def_config):
- def_config.project_dir = Path('.')
- sql = _get_standard_function_sql(db, def_config, SQL_DIR, False, False)
-
- assert sql
- assert sql.find('{modulepath}') < 0
- assert sql.find("'{}'".format(Path('module/nominatim.so').resolve())) >= 0
-
-
-def test_standard_functions_replace_module_custom(monkeypatch, db, def_config):
- monkeypatch.setenv('NOMINATIM_DATABASE_MODULE_PATH', 'custom')
- sql = _get_standard_function_sql(db, def_config, SQL_DIR, False, False)
-
- assert sql
- assert sql.find('{modulepath}') < 0
- assert sql.find("'custom/nominatim.so'") >= 0
-
-
-@pytest.mark.parametrize("enabled", (True, False))
-def test_standard_functions_enable_diff(db_with_tables, def_config, enabled):
- def_config.project_dir = Path('.')
- sql = _get_standard_function_sql(db_with_tables, def_config, SQL_DIR, enabled, False)
-
- assert sql
- assert (sql.find('%DIFFUPDATES%') < 0) == enabled
-
-
-@pytest.mark.parametrize("enabled", (True, False))
-def test_standard_functions_enable_debug(db_with_tables, def_config, enabled):
- def_config.project_dir = Path('.')
- sql = _get_standard_function_sql(db_with_tables, def_config, SQL_DIR, False, enabled)
-
- assert sql
- assert (sql.find('--DEBUG') < 0) == enabled
-
-
-@pytest.mark.parametrize("enabled", (True, False))
-def test_standard_functions_enable_limit_reindexing(monkeypatch, db_with_tables, def_config, enabled):
- def_config.project_dir = Path('.')
- monkeypatch.setenv('NOMINATIM_LIMIT_REINDEXING', 'yes' if enabled else 'no')
- sql = _get_standard_function_sql(db_with_tables, def_config, SQL_DIR, False, False)
-
- assert sql
- assert (sql.find('--LIMIT INDEXING') < 0) == enabled
-
-
-@pytest.mark.parametrize("enabled", (True, False))
-def test_standard_functions_enable_tiger(monkeypatch, db_with_tables, def_config, enabled):
- def_config.project_dir = Path('.')
- monkeypatch.setenv('NOMINATIM_USE_US_TIGER_DATA', 'yes' if enabled else 'no')
- sql = _get_standard_function_sql(db_with_tables, def_config, SQL_DIR, False, False)
-
- assert sql
- assert (sql.find('%NOTIGERDATA%') >= 0) == enabled
-
-
-@pytest.mark.parametrize("enabled", (True, False))
-def test_standard_functions_enable_aux(monkeypatch, db_with_tables, def_config, enabled):
- def_config.project_dir = Path('.')
- monkeypatch.setenv('NOMINATIM_USE_AUX_LOCATION_DATA', 'yes' if enabled else 'no')
- sql = _get_standard_function_sql(db_with_tables, def_config, SQL_DIR, False, False)
-
- assert sql
- assert (sql.find('%NOAUXDATA%') >= 0) == enabled
-
-
-def test_partition_function(temp_db_cursor, db, def_config):
- temp_db_cursor.execute("CREATE TABLE country_name (partition SMALLINT)")
-
- sql = _get_partition_function_sql(db, SQL_DIR)
-
- assert sql
- assert sql.find('-partition-') < 0
+def conn(temp_db_conn, table_factory, monkeypatch):
+ monkeypatch.setenv('NOMINATIM_DATABASE_MODULE_PATH', '.')
+ table_factory('country_name', 'partition INT', (0, 1, 2))
+ return temp_db_conn
+
+
+def test_create_functions(temp_db_cursor, conn, def_config, tmp_path):
+ sqlfile = tmp_path / 'functions.sql'
+ sqlfile.write_text("""CREATE OR REPLACE FUNCTION test() RETURNS INTEGER
+ AS $$
+ BEGIN
+ RETURN 43;
+ END;
+ $$ LANGUAGE plpgsql IMMUTABLE;
+ """)
+
+ create_functions(conn, def_config, tmp_path)
+
+ assert temp_db_cursor.scalar('SELECT test()') == 43
+
+
+@pytest.mark.parametrize("dbg,ret", ((True, 43), (False, 22)))
+def test_create_functions_with_template(temp_db_cursor, conn, def_config, tmp_path, dbg, ret):
+ sqlfile = tmp_path / 'functions.sql'
+ sqlfile.write_text("""CREATE OR REPLACE FUNCTION test() RETURNS INTEGER
+ AS $$
+ BEGIN
+ {% if debug %}
+ RETURN 43;
+ {% else %}
+ RETURN 22;
+ {% endif %}
+ END;
+ $$ LANGUAGE plpgsql IMMUTABLE;
+ """)
+
+ create_functions(conn, def_config, tmp_path, enable_debug=dbg)
+
+ assert temp_db_cursor.scalar('SELECT test()') == ret
projects / nominatim.git / commitdiff