+ # This finds us the rows in location_postcode and word that are
+ # missing in the other table.
+ cur.execute("""SELECT * FROM
+ (SELECT pc, word FROM
+ (SELECT distinct(postcode) as pc FROM location_postcode) p
+ FULL JOIN
+ (SELECT word FROM word
+ WHERE class ='place' and type = 'postcode') w
+ ON pc = word) x
+ WHERE pc is null or word is null""")
+
+ to_delete = []
+ to_add = []
+
+ for postcode, word in cur:
+ if postcode is None:
+ to_delete.append(word)
+ else:
+ to_add.append(postcode)
+
+ if to_delete:
+ cur.execute("""DELETE FROM WORD
+ WHERE class ='place' and type = 'postcode'
+ and word = any(%s)
+ """, (to_delete, ))
+ if to_add:
+ cur.execute("""SELECT count(create_postcode_id(pc))
+ FROM unnest(%s) as pc
+ """, (to_add, ))
+
+
+
+ def update_special_phrases(self, phrases, should_replace):
+ """ Replace the search index for special phrases with the new phrases.
+ """
+ norm_phrases = set(((self.normalize(p[0]), p[1], p[2], p[3])
+ for p in phrases))
+
+ with self.conn.cursor() as cur:
+ # Get the old phrases.
+ existing_phrases = set()
+ cur.execute("""SELECT word, class, type, operator FROM word
+ WHERE class != 'place'
+ OR (type != 'house' AND type != 'postcode')""")
+ for label, cls, typ, oper in cur:
+ existing_phrases.add((label, cls, typ, oper or '-'))
+
+ to_add = norm_phrases - existing_phrases
+ to_delete = existing_phrases - norm_phrases
+
+ if to_add:
+ cur.execute_values(
+ """ INSERT INTO word (word_id, word_token, word, class, type,
+ search_name_count, operator)
+ (SELECT nextval('seq_word'), ' ' || make_standard_name(name), name,
+ class, type, 0,
+ CASE WHEN op in ('in', 'near') THEN op ELSE null END
+ FROM (VALUES %s) as v(name, class, type, op))""",
+ to_add)
+
+ if to_delete and should_replace:
+ cur.execute_values(
+ """ DELETE FROM word USING (VALUES %s) as v(name, in_class, in_type, op)
+ WHERE word = name and class = in_class and type = in_type
+ and ((op = '-' and operator is null) or op = operator)""",
+ to_delete)
+
+ LOG.info("Total phrases: %s. Added: %s. Deleted: %s",
+ len(norm_phrases), len(to_add), len(to_delete))
+
+
+ def add_country_names(self, country_code, names):
+ """ Add names for the given country to the search index.
+ """
+ with self.conn.cursor() as cur:
+ cur.execute(
+ """INSERT INTO word (word_id, word_token, country_code)
+ (SELECT nextval('seq_word'), lookup_token, %s
+ FROM (SELECT DISTINCT ' ' || make_standard_name(n) as lookup_token
+ FROM unnest(%s)n) y
+ WHERE NOT EXISTS(SELECT * FROM word
+ WHERE word_token = lookup_token and country_code = %s))
+ """, (country_code, list(names.values()), country_code))
+