From f33ec6c0e6e90149a341a8d19676adafe14ca9a4 Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Thu, 6 Aug 2020 11:56:47 -0400 Subject: [PATCH] lp1846354 upgrade script tweaks the main change is that we're creating SILENT_NOTE penalties for migrated actor.usr_note's in order to preserve the creator. I'm also removing explicit TEMP tables in case admins do want to preserve these staging tables (by commenting out the DROP's at the bottom of the script). Signed-off-by: Jason Etheridge Signed-off-by: Mike Rylander --- .../XXXX.schema.note_and_message_consolidation | 141 +++++++++++++++++++-- 1 file changed, 131 insertions(+), 10 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation index 728147bbf8..a109ac2c01 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation @@ -19,7 +19,7 @@ UPDATE actor.usr_message SET pub = TRUE; -- alright, let's migrate penalty notes to usr_messages and link the messages back to the penalties: -CREATE TEMP TABLE XXXX_penalty_notes AS +CREATE TABLE actor.XXXX_penalty_notes AS SELECT id, usr, org_unit, set_date, note FROM actor.usr_standing_penalty WHERE NULLIF(BTRIM(note),'') IS NOT NULL; @@ -43,9 +43,9 @@ INSERT INTO actor.XXXX_usr_message_for_penalty_notes ( note, set_date, org_unit, - false + FALSE FROM - XXXX_penalty_notes + actor.XXXX_penalty_notes ; -- so far so good, let's push this into production @@ -127,7 +127,7 @@ WHERE -- fun part where we migrate the following alert messages: -CREATE TEMP TABLE XXXX_note_and_message_consolidation AS +CREATE TABLE actor.XXXX_note_and_message_consolidation AS SELECT id, home_ou, alert_message FROM actor.usr WHERE NOT deleted AND NULLIF(BTRIM(alert_message),'') IS NOT NULL; @@ -151,9 +151,9 @@ INSERT INTO actor.XXXX_usr_message ( alert_message, NOW(), -- best we can do 1, -- it's this or home_ou - false + FALSE FROM - XXXX_note_and_message_consolidation + actor.XXXX_note_and_message_consolidation ; -- another staging table, but for actor.usr_standing_penalty @@ -212,8 +212,11 @@ end; $$; -- extends to auditor.actor_usr_lifecycle ALTER TABLE actor.usr DROP COLUMN alert_message CASCADE; --- fun part where we migrate private actor.usr_notes (public ones presumably --- have already been copied to usr_messages during normal operation) +-- fun part where we migrate actor.usr_notes as penalties to preserve +-- their creator, and then the private ones to private user messages. +-- For public notes, we try to link to existing user messages if we +-- can, but if we can't, we'll create new, but archived, user messages +-- for the note contents. CREATE TABLE actor.XXXX_usr_message_for_private_notes ( LIKE actor.usr_message INCLUDING DEFAULTS EXCLUDING CONSTRAINTS @@ -232,17 +235,131 @@ INSERT INTO actor.XXXX_usr_message_for_private_notes ( value, create_date, (select home_ou from actor.usr where id = creator), -- best we can do - false + FALSE FROM actor.usr_note WHERE NOT pub ; +CREATE TABLE actor.XXXX_usr_message_for_unmatched_public_notes ( + LIKE actor.usr_message INCLUDING DEFAULTS EXCLUDING CONSTRAINTS +); + +INSERT INTO actor.XXXX_usr_message_for_unmatched_public_notes ( + usr, + title, + message, + create_date, + stop_date, + sending_lib, + pub +) SELECT + usr, + title, + value, + create_date, + NOW(), -- we want these "archived" since the patron probably already saw a corresponding usr_message + (select home_ou from actor.usr where id = creator), -- best we can do + FALSE +FROM + actor.usr_note n +WHERE + pub AND NOT EXISTS (SELECT 1 FROM actor.usr_message m WHERE n.usr = m.usr AND n.create_date = m.create_date) +; + +-- now, in order to preserve the creator from usr_note, we want to create standing SILENT_NOTE penalties for +-- 1) actor.XXXX_usr_message_for_private_notes and associated usr_note entries +-- 2) actor.XXXX_usr_message_for_unmatched_public_notes and associated usr_note entries, but archive these +-- 3) usr_note and usr_message entries that can be matched + +CREATE TABLE actor.XXXX_usr_standing_penalties_for_notes ( + LIKE actor.usr_standing_penalty INCLUDING DEFAULTS EXCLUDING CONSTRAINTS +); + +-- 1) actor.XXXX_usr_message_for_private_notes and associated usr_note entries +INSERT INTO actor.XXXX_usr_standing_penalties_for_notes ( + org_unit, + usr, + standing_penalty, + staff, + set_date, + stop_date, + usr_message +) SELECT + m.sending_lib, + m.usr, + 21, -- SILENT_NOTE + n.creator, + m.create_date, + m.stop_date, + m.id +FROM + actor.usr_note n, + actor.XXXX_usr_message_for_private_notes m +WHERE + n.usr = m.usr AND n.create_date = m.create_date AND NOT n.pub AND NOT m.pub +; + +-- 2) actor.XXXX_usr_message_for_unmatched_public_notes and associated usr_note entries, but archive these +INSERT INTO actor.XXXX_usr_standing_penalties_for_notes ( + org_unit, + usr, + standing_penalty, + staff, + set_date, + stop_date, + usr_message +) SELECT + m.sending_lib, + m.usr, + 21, -- SILENT_NOTE + n.creator, + m.create_date, + m.stop_date, + m.id +FROM + actor.usr_note n, + actor.XXXX_usr_message_for_unmatched_public_notes m +WHERE + n.usr = m.usr AND n.create_date = m.create_date AND n.pub AND m.pub +; + +-- 3) usr_note and usr_message entries that can be matched +INSERT INTO actor.XXXX_usr_standing_penalties_for_notes ( + org_unit, + usr, + standing_penalty, + staff, + set_date, + stop_date, + usr_message +) SELECT + m.sending_lib, + m.usr, + 21, -- SILENT_NOTE + n.creator, + m.create_date, + m.stop_date, + m.id +FROM + actor.usr_note n, + actor.usr_message m +WHERE + n.usr = m.usr AND n.create_date = m.create_date AND m.id NOT IN ( + SELECT id FROM actor.XXXX_usr_message_for_private_notes + UNION + SELECT id FROM actor.XXXX_usr_message_for_unmatched_public_notes + ) +; + -- so far so good, let's push these into production INSERT INTO actor.usr_message - SELECT * FROM actor.XXXX_usr_message_for_private_notes; + SELECT * FROM actor.XXXX_usr_message_for_private_notes + UNION SELECT * FROM actor.XXXX_usr_message_for_unmatched_public_notes; +INSERT INTO actor.usr_standing_penalty + SELECT * FROM actor.XXXX_usr_standing_penalties_for_notes; -- probably redundant here, but the spec calls for an assertion before dropping -- the actor.usr_note table, so being extra cautious: @@ -355,10 +472,14 @@ DELETE FROM config.org_unit_setting_type WHERE name = 'ui.patron.edit.au.alert_message.suggest'; -- comment these out if you want the staging tables to stick around +DROP TABLE actor.XXXX_note_and_message_consolidation; +DROP TABLE actor.XXXX_penalty_notes; DROP TABLE actor.XXXX_usr_message_for_penalty_notes; DROP TABLE actor.XXXX_usr_message; DROP TABLE actor.XXXX_usr_standing_penalty; DROP TABLE actor.XXXX_usr_message_for_private_notes; +DROP TABLE actor.XXXX_usr_message_for_unmatched_public_notes; +DROP TABLE actor.XXXX_usr_standing_penalties_for_notes; COMMIT; -- 2.11.0