From 5ccc7d919ac3317d14cca34efc65cbe17c77c50e Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Mon, 27 Aug 2018 22:49:12 -0400
Subject: [PATCH] LP#1776020 Deduplicate name keywords in patron merge
Signed-off-by: Bill Erickson <berickxx@gmail.com>
Signed-off-by: Kathy Lussier <klussier@masslnc.org>
Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
---
Open-ILS/src/sql/Pg/999.functions.global.sql | 20 ++++++++++++++++----
.../sql/Pg/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.11.0