From 5ccc7d919ac3317d14cca34efc65cbe17c77c50e Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 27 Aug 2018 22:49:12 -0400 Subject: [PATCH] LP#1776020 Deduplicate name keywords in patron merge Signed-off-by: Bill Erickson Signed-off-by: Kathy Lussier Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/999.functions.global.sql | 20 +++++++++++++++---- .../upgrade/XXXX.schema.patron-alt-name.sql | 20 +++++++++++++++---- 2 files changed, 32 insertions(+), 8 deletions(-) diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 927e47c137..da9195900b 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -365,12 +365,24 @@ BEGIN pref_family_name = COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)), pref_suffix = - COALESCE(pref_suffix, (SELECT pref_suffix FROM susr)), - name_keywords = - COALESCE(name_keywords, '') || ' ' || - COALESCE((SELECT name_keywords FROM susr), '') + COALESCE(pref_suffix, (SELECT pref_suffix FROM susr)) WHERE id = dest_usr; + -- Copy and deduplicate name keywords + -- String -> array -> rows -> DISTINCT -> array -> string + WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr), + dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr) + UPDATE actor.usr SET name_keywords = ( + WITH keywords AS ( + SELECT DISTINCT UNNEST( + REGEXP_SPLIT_TO_ARRAY( + COALESCE((SELECT name_keywords FROM susr), '') || ' ' || + COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+' + ) + ) AS parts + ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw + ) WHERE id = dest_usr; + -- Finally, delete the source user DELETE FROM actor.usr WHERE id = src_usr; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-alt-name.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-alt-name.sql index 6eea68e639..9fb27264ed 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-alt-name.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron-alt-name.sql @@ -405,12 +405,24 @@ BEGIN pref_family_name = COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)), pref_suffix = - COALESCE(pref_suffix, (SELECT pref_suffix FROM susr)), - name_keywords = - COALESCE(name_keywords, '') || ' ' || - COALESCE((SELECT name_keywords FROM susr), '') + COALESCE(pref_suffix, (SELECT pref_suffix FROM susr)) WHERE id = dest_usr; + -- Copy and deduplicate name keywords + -- String -> array -> rows -> DISTINCT -> array -> string + WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr), + dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr) + UPDATE actor.usr SET name_keywords = ( + WITH keywords AS ( + SELECT DISTINCT UNNEST( + REGEXP_SPLIT_TO_ARRAY( + COALESCE((SELECT name_keywords FROM susr), '') || ' ' || + COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+' + ) + ) AS parts + ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw + ) WHERE id = dest_usr; + -- Finally, delete the source user DELETE FROM actor.usr WHERE id = src_usr; -- 2.43.2