From 49b4bf3315f303a6bd8a8c88be5b724c1c124f8d Mon Sep 17 00:00:00 2001 From: Bill Erickson <berickxx@gmail.com> Date: Thu, 12 Dec 2019 16:47:31 -0500 Subject: [PATCH] LP1830391 Warn on dupe workstation settings Check for duplication workstations (by name and workstation) before applying the UNIQUE constraint on actor.workstation_setting. If found, raise a notice to the user to provide suggestions. Signed-off-by: Bill Erickson <berickxx@gmail.com> Signed-off-by: Jason Boyer <JBoyer@eoli.info> --- .../Pg/upgrade/XXXX.data.hatch-enable-print.sql | 35 +++++++++++++++++++++- 1 file changed, 34 insertions(+), 1 deletion(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.hatch-enable-print.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.hatch-enable-print.sql index e9e8b0e79a..f6cc5b3ced 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.hatch-enable-print.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.hatch-enable-print.sql @@ -12,8 +12,41 @@ VALUES ( ) ); -ALTER TABLE actor.workstation_setting + +DO $SQL$ +BEGIN + + PERFORM COUNT(*), workstation, name + FROM actor.workstation_setting GROUP BY 2, 3 HAVING COUNT(*) > 1; + + IF FOUND THEN + + RAISE NOTICE $NOTICE$ + +--- +The actor.workstation_setting table contains duplicate rows. The duplicates +should be removed before applying a new UNIQUE constraint. To find the rows, +execute the following SQL: + +SELECT COUNT(*), workstation, name FROM actor.workstation_setting + GROUP BY 2, 3 HAVING COUNT(*) > 1; + +Once the duplicates are cleared, execute the following SQL: + +ALTER TABLE actor.workstation_setting ADD CONSTRAINT ws_once_per_key UNIQUE (workstation, name); +--- + +$NOTICE$; + + ELSE + + ALTER TABLE actor.workstation_setting + ADD CONSTRAINT ws_once_per_key UNIQUE (workstation, name); + END IF; + +END; +$SQL$; COMMIT; -- 2.11.0