From 13a2db5e248dcbd3ef150bc5e1f428e5c43e1d1f Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Wed, 8 Jan 2020 09:02:52 -0500 Subject: [PATCH] lp1846354 changes to IDL, DB, and upgrade script A new view actor.usr_message_penalty and associated IDL entry will be created for use with populating the staff Messages interface. It will perform a full outer join between penalties and user messages. The default IDL permissions for user messages will remain VIEW_USER and UPDATE_USER. The view that populates the Message Center will be changed slightly to ensure that staff-only messages are never retrieved for patrons. The Date column in the grid/list view will show the edit_date value if populated in lieu of the create_date value. The message view will explicitly show an Edit Date label and value below the Date row if set. The actor.usr_message table will gain a new boolean column called pub, which will default to false. The actor.usr_message table will also gain a stop_date column for handling archival as with standing penalties. The actor.usr_message table will also gain editor and edit_date columns. The actor.usr_message_limited view will be changed to only include rows where pub is true. As part of the upgrade script, Patron alert messages will be migrated as ALERT_NOTE penalties and linked User Messages. A database assertion will ensure that this has happened prior to the removal of the alert_message field from the actor.usr table. The field will also be removed from the corresponding IDL entry. The sending_lib column for these user messages and the org unit field for these penalties will be set to the top org from the org hierarchy. Also, non-public entries in the actor.usr_note table will be migrated as non-public User Messages. Subject to a database assertion that this has happened, the table actor.usr_note and the actor.convert_usr_note_to_message trigger will be removed. The corresponding IDL entry will also be removed. The sending_lib column for these user messages will be set to the top org from the org hierarchy. Also, rows from actor.org_unit_setting for ui.staff.require_initials.patron_info_notes will be migrated to ui.staff.require_initials.patron_standing_penalty. Collisions will be recorded in a text file but otherwise dropped. The row in config.org_unit_setting_type for ui.staff.require_initials.patron_info_notes will then be removed. The label and description for ui.staff.require_initials.patron_standing_penalty in config.org_unit_setting_type will be edited to reflect the Notes/Penalties labeling from the UI. The actor.usr_standing_penalty table and associated IDL entry will gain a foreign key column linking to actor.usr_message called usr_message, and the note column itself will be migrated to User Messages. The usr_message field on the penalties will be updated accordingly. Subject to a database assertion that this has happened, the note field on the actor.usr_standing_penalty table will be removed. The corresponding IDL entry will also be removed. The sending_lib column for these user messages will be set to match the org_unit column from their associated penalties. Signed-off-by: Jason Etheridge --- Open-ILS/examples/fm_IDL.xml | 86 +++-- Open-ILS/src/sql/Pg/005.schema.actors.sql | 98 +++--- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 36 +-- .../XXXX.schema.note_and_message_consolidation | 360 +++++++++++++++++++++ 4 files changed, 469 insertions(+), 111 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 83dca5d8c2..a82fa47b83 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -2253,9 +2253,14 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + @@ -2276,9 +2281,14 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + @@ -2288,38 +2298,56 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - + + + - - - - + + + + + + + + + + - + + + + + + + + + + + + + + - + + + + + + + + + - - - - - - - - - - - - - - - - - + + + + + + + @@ -3635,7 +3663,6 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - @@ -3720,7 +3747,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -4336,7 +4363,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -4393,13 +4420,14 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + + diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 0572543768..9e680e2c5b 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -68,7 +68,6 @@ CREATE TABLE actor.usr ( claims_returned_count INT NOT NULL DEFAULT 0, credit_forward_balance NUMERIC(6,2) NOT NULL DEFAULT 0.00, last_xact_id TEXT NOT NULL DEFAULT 'none', - alert_message TEXT, create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), expire_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now() + '3 years'::INTERVAL), claims_never_checked_out_count INT NOT NULL DEFAULT 0, @@ -192,18 +191,6 @@ CREATE TRIGGER user_ingest_name_keywords_tgr BEFORE INSERT OR UPDATE ON actor.usr FOR EACH ROW EXECUTE PROCEDURE actor.user_ingest_name_keywords(); -CREATE TABLE actor.usr_note ( - id BIGSERIAL PRIMARY KEY, - usr BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - creator BIGINT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), - pub BOOL NOT NULL DEFAULT FALSE, - title TEXT NOT NULL, - value TEXT NOT NULL -); -CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr); -CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator ); - CREATE TABLE actor.usr_setting ( id BIGSERIAL PRIMARY KEY, usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, @@ -678,8 +665,7 @@ CREATE TABLE actor.usr_standing_penalty ( standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), - stop_date TIMESTAMP WITH TIME ZONE, - note TEXT + stop_date TIMESTAMP WITH TIME ZONE ); COMMENT ON TABLE actor.usr_standing_penalty IS $$ User standing penalties @@ -801,9 +787,15 @@ CREATE TABLE actor.usr_message ( create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), deleted BOOL NOT NULL DEFAULT FALSE, read_date TIMESTAMP WITH TIME ZONE, - sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED + sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, + pub BOOL NOT NULL DEFAULT FALSE, + stop_date TIMESTAMP WITH TIME ZONE, + editor BIGINT REFERENCES actor.usr (id), + edit_date TIMESTAMP WITH TIME ZONE + ); CREATE INDEX aum_usr ON actor.usr_message (usr); +ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id); CREATE RULE protect_usr_message_delete AS ON DELETE TO actor.usr_message DO INSTEAD ( @@ -812,42 +804,11 @@ CREATE RULE protect_usr_message_delete AS WHERE OLD.id = actor.usr_message.id ); -CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$ -DECLARE - sending_ou INTEGER; -BEGIN - IF NEW.pub THEN - IF TG_OP = 'UPDATE' THEN - IF OLD.pub = TRUE THEN - RETURN NEW; - END IF; - END IF; - - SELECT INTO sending_ou aw.owning_lib - FROM auditor.get_audit_info() agai - JOIN actor.workstation aw ON (aw.id = agai.eg_ws); - IF sending_ou IS NULL THEN - SELECT INTO sending_ou home_ou - FROM actor.usr - WHERE id = NEW.creator; - END IF; - INSERT INTO actor.usr_message (usr, title, message, sending_lib) - VALUES (NEW.usr, NEW.title, NEW.value, sending_ou); - END IF; - - RETURN NEW; -END; -$$ LANGUAGE PLPGSQL; - -CREATE TRIGGER convert_usr_note_to_message_tgr - AFTER INSERT OR UPDATE ON actor.usr_note - FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message(); - -- limited view to ensure that a library user who somehow -- manages to figure out how to access pcrud cannot change -- the text of messages sent them CREATE VIEW actor.usr_message_limited -AS SELECT * FROM actor.usr_message; +AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted; CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$ BEGIN @@ -866,6 +827,47 @@ CREATE TRIGGER restrict_usr_message_limited_tgr INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited(); +-- combined view of actor.usr_standing_penalty and actor.usr_message for populating +-- staff Notes (formerly Messages) interface + +CREATE VIEW actor.usr_message_penalty +AS SELECT + COALESCE(ausp.id::TEXT,'') || ':' || COALESCE(aum.id::TEXT,'') AS "id", + ausp.id AS "ausp_id", + aum.id AS "aum_id", + COALESCE(ausp.org_unit,aum.sending_lib) AS "org_unit", + ausp.org_unit AS "ausp_org_unit", + aum.sending_lib AS "aum_sending_lib", + COALESCE(ausp.usr,aum.usr) AS "usr", + ausp.usr as "ausp_usr", + aum.usr as "aum_usr", + ausp.standing_penalty AS "standing_penalty", + ausp.staff AS "staff", + LEAST(ausp.set_date,aum.create_date) AS "create_date", + ausp.set_date AS "ausp_set_date", + aum.create_date AS "aum_create_date", + LEAST(ausp.stop_date,aum.stop_date) AS "stop_date", + ausp.stop_date AS "ausp_stop_date", + aum.stop_date AS "aum_stop_date", + ausp.usr_message AS "ausp_usr_message", + aum.title AS "title", + aum.message AS "message", + aum.deleted AS "deleted", + aum.read_date AS "read_date", + aum.pub AS "pub", + aum.editor AS "editor", + aum.edit_date AS "edit_date" +FROM + actor.usr_standing_penalty ausp +FULL OUTER JOIN + actor.usr_message aum +ON ( + ausp.usr_message = aum.id +) +WHERE + NOT (ausp.id IS NULL AND aum.deleted); +; + CREATE TABLE actor.passwd_type ( code TEXT PRIMARY KEY, name TEXT UNIQUE NOT NULL, diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 01621ab450..7de349d2d2 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -1655,8 +1655,6 @@ INSERT INTO permission.perm_list ( id, code, description ) VALUES 'VIEW_MERGE_PROFILE', 'ppl', 'description' )), ( 479, 'VIEW_SERIAL_SUBSCRIPTION', oils_i18n_gettext( 479, 'VIEW_SERIAL_SUBSCRIPTION', 'ppl', 'description' )), - ( 480, 'VIEW_STANDING_PENALTY', oils_i18n_gettext( 480, - 'VIEW_STANDING_PENALTY', 'ppl', 'description' )), ( 481, 'ADMIN_SERIAL_CAPTION_PATTERN', oils_i18n_gettext( 481, 'ADMIN_SERIAL_CAPTION_PATTERN', 'ppl', 'description' )), ( 482, 'ADMIN_SERIAL_DISTRIBUTION', oils_i18n_gettext( 482, @@ -2368,7 +2366,6 @@ INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) 'UPDATE_PICKUP_LIB FROM_TRANSIT', 'UPDATE_PICKUP_LIB_FROM_HOLDS_SHELF', 'VIEW_GROUP_PENALTY_THRESHOLD', - 'VIEW_STANDING_PENALTY', 'VOID_BILLING', 'VOLUME_HOLDS'); @@ -2528,7 +2525,6 @@ INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) 'UPDATE_PICKUP_LIB_FROM_TRANSIT', 'UPDATE_USER', 'VIEW_REPORT_OUTPUT', - 'VIEW_STANDING_PENALTY', 'VOID_BILLING', 'TRANSIT_CHECKIN_INTERVAL_BLOCK.override', 'VOLUME_HOLDS', @@ -4644,24 +4640,6 @@ INSERT into config.org_unit_setting_type 'coust', 'description'), 'bool', null) -,( 'ui.patron.edit.au.alert_message.show', 'gui', - oils_i18n_gettext('ui.patron.edit.au.alert_message.show', - 'Show alert_message field on patron registration', - 'coust', 'label'), - oils_i18n_gettext('ui.patron.edit.au.alert_message.show', - 'The alert_message field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', - 'coust', 'description'), - 'bool', null) - -,( 'ui.patron.edit.au.alert_message.suggest', 'gui', - oils_i18n_gettext('ui.patron.edit.au.alert_message.suggest', - 'Suggest alert_message field on patron registration', - 'coust', 'label'), - oils_i18n_gettext('ui.patron.edit.au.alert_message.suggest', - 'The alert_message field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', - 'coust', 'description'), - 'bool', null) - ,( 'ui.patron.edit.au.alias.show', 'gui', oils_i18n_gettext('ui.patron.edit.au.alias.show', 'Show alias field on patron registration', @@ -5213,22 +5191,12 @@ INSERT into config.org_unit_setting_type ,( 'ui.staff.require_initials.patron_standing_penalty', 'gui', oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty', - 'Require staff initials for entry/edit of patron standing penalties and messages.', + 'Require staff initials for entry/edit of patron standing penalties and notes.', 'coust', 'label'), oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty', - 'Appends staff initials and edit date into patron standing penalties and messages.', - 'coust', 'description'), - 'bool', null) - -,( 'ui.staff.require_initials.patron_info_notes', 'gui', - oils_i18n_gettext('ui.staff.require_initials.patron_info_notes', - 'Require staff initials for entry/edit of patron notes.', - 'coust', 'label'), - oils_i18n_gettext('ui.staff.require_initials.patron_info_notes', - 'Appends staff initials and edit date into patron note content.', + 'Require staff initials for entry/edit of patron standing penalties and notes.', 'coust', 'description'), 'bool', null) - ,( 'ui.staff.require_initials.copy_notes', 'gui', oils_i18n_gettext('ui.staff.require_initials.copy_notes', 'Require staff initials for entry/edit of copy notes.', 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 new file mode 100644 index 0000000000..56a1245475 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.note_and_message_consolidation @@ -0,0 +1,360 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +ALTER TABLE actor.usr_message ADD COLUMN pub BOOL NOT NULL DEFAULT FALSE; +ALTER TABLE actor.usr_message ADD COLUMN stop_date TIMESTAMP WITH TIME ZONE; +ALTER TABLE actor.usr_message ADD COLUMN editor BIGINT REFERENCES actor.usr (id); +ALTER TABLE actor.usr_message ADD COLUMN edit_date TIMESTAMP WITH TIME ZONE; + +DROP VIEW actor.usr_message_limited; +CREATE VIEW actor.usr_message_limited +AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted; + +ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id); + +-- alright, let's migrate penalty notes to usr_messages and link the messages back to the penalties: + +CREATE TEMP TABLE XXXX_penalty_notes AS + SELECT id, usr, org_unit, set_date, note + FROM actor.usr_standing_penalty + WHERE NULLIF(BTRIM(note),'') IS NOT NULL; + +-- here is our staging table which will be shaped exactly like +-- actor.usr_message and use the same id sequence +CREATE TABLE actor.XXXX_usr_message_for_penalty_notes ( + LIKE actor.usr_message INCLUDING DEFAULTS EXCLUDING CONSTRAINTS +); + +INSERT INTO actor.XXXX_usr_message_for_penalty_notes ( + usr, + title, + message, + create_date, + sending_lib, + pub +) SELECT + usr, + 'Penalty Note ID ' || id, + note, + set_date, + org_unit, + false +FROM + XXXX_penalty_notes +; + +-- so far so good, let's push this into production + +INSERT INTO actor.usr_message + SELECT * FROM actor.XXXX_usr_message_for_penalty_notes; + +-- and link the production penalties to these new user messages + +UPDATE actor.usr_standing_penalty p SET usr_message = m.id + FROM actor.XXXX_usr_message_for_penalty_notes m + WHERE m.title = 'Penalty Note ID ' || p.id; + +-- and remove the temporary overloading of the message title we used for this: + +UPDATE + actor.usr_message +SET + title = 'Penalty Note' +WHERE + id IN (SELECT id FROM actor.XXXX_usr_message_for_penalty_notes) +; + +-- probably redundant here, but the spec calls for an assertion before removing +-- the note column from actor.usr_standing_penalty, so being extra cautious: + +do $$ begin + assert ( + select count(*) + from actor.XXXX_usr_message_for_penalty_notes + where id not in ( + select id from actor.usr_message + ) + ) = 0, 'failed migrating to actor.usr_message'; +end; $$; + +ALTER TABLE actor.usr_standing_penalty DROP COLUMN note; + +-- combined view of actor.usr_standing_penalty and actor.usr_message for populating +-- staff Notes (formerly Messages) interface + +CREATE VIEW actor.usr_message_penalty +AS SELECT + COALESCE(ausp.id::TEXT,'') || ':' || COALESCE(aum.id::TEXT,'') AS "id", + ausp.id AS "ausp_id", + aum.id AS "aum_id", + COALESCE(ausp.org_unit,aum.sending_lib) AS "org_unit", + ausp.org_unit AS "ausp_org_unit", + aum.sending_lib AS "aum_sending_lib", + COALESCE(ausp.usr,aum.usr) AS "usr", + ausp.usr as "ausp_usr", + aum.usr as "aum_usr", + ausp.standing_penalty AS "standing_penalty", + ausp.staff AS "staff", + LEAST(ausp.set_date,aum.create_date) AS "create_date", + ausp.set_date AS "ausp_set_date", + aum.create_date AS "aum_create_date", + LEAST(ausp.stop_date,aum.stop_date) AS "stop_date", + ausp.stop_date AS "ausp_stop_date", + aum.stop_date AS "aum_stop_date", + ausp.usr_message AS "ausp_usr_message", + aum.title AS "title", + aum.message AS "message", + aum.deleted AS "deleted", + aum.read_date AS "read_date", + aum.pub AS "pub", + aum.editor AS "editor", + aum.edit_date AS "edit_date" +FROM + actor.usr_standing_penalty ausp +FULL OUTER JOIN + actor.usr_message aum +ON ( + ausp.usr_message = aum.id +) +WHERE + NOT (ausp.id IS NULL AND aum.deleted); +; + +-- fun part where we migrate the following alert messages: + +CREATE TEMP TABLE 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; + +-- here is our staging table which will be shaped exactly like +-- actor.usr_message and use the same id sequence +CREATE TABLE actor.XXXX_usr_message ( + LIKE actor.usr_message INCLUDING DEFAULTS EXCLUDING CONSTRAINTS +); + +INSERT INTO actor.XXXX_usr_message ( + usr, + title, + message, + create_date, + sending_lib, + pub +) SELECT + id, + 'Alert Message', + alert_message, + NOW(), -- best we can do + 1, -- it's this or home_ou + false +FROM + XXXX_note_and_message_consolidation +; + +-- another staging table, but for actor.usr_standing_penalty +CREATE TABLE actor.XXXX_usr_standing_penalty ( + LIKE actor.usr_standing_penalty INCLUDING DEFAULTS EXCLUDING CONSTRAINTS +); + +INSERT INTO actor.XXXX_usr_standing_penalty ( + org_unit, + usr, + standing_penalty, + staff, + set_date, + usr_message +) SELECT + sending_lib, + usr, + 20, -- ALERT_NOTE + 1, -- admin user, usually; best we can do + create_date, + id +FROM + actor.XXXX_usr_message +; + +-- so far so good, let's push these into production + +INSERT INTO actor.usr_message + SELECT * FROM actor.XXXX_usr_message; +INSERT INTO actor.usr_standing_penalty + SELECT * FROM actor.XXXX_usr_standing_penalty; + +-- probably redundant here, but the spec calls for an assertion before removing +-- the alert message column from actor.usr, so being extra cautious: + +do $$ begin + assert ( + select count(*) + from actor.XXXX_usr_message + where id not in ( + select id from actor.usr_message + ) + ) = 0, 'failed migrating to actor.usr_message'; +end; $$; + +do $$ begin + assert ( + select count(*) + from actor.XXXX_usr_standing_penalty + where id not in ( + select id from actor.usr_standing_penalty + ) + ) = 0, 'failed migrating to actor.usr_standing_penalty'; +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) + +CREATE TABLE actor.XXXX_usr_message_for_private_notes ( + LIKE actor.usr_message INCLUDING DEFAULTS EXCLUDING CONSTRAINTS +); + +INSERT INTO actor.XXXX_usr_message_for_private_notes ( + usr, + title, + message, + create_date, + sending_lib, + pub +) SELECT + usr, + title, + value, + create_date, + (select home_ou from actor.usr where id = creator), -- best we can do + false +FROM + actor.usr_note +WHERE + NOT pub +; + +-- so far so good, let's push these into production + +INSERT INTO actor.usr_message + SELECT * FROM actor.XXXX_usr_message_for_private_notes; + +-- probably redundant here, but the spec calls for an assertion before dropping +-- the actor.usr_note table, so being extra cautious: + +do $$ begin + assert ( + select count(*) + from actor.XXXX_usr_message_for_private_notes + where id not in ( + select id from actor.usr_message + ) + ) = 0, 'failed migrating to actor.usr_message'; +end; $$; + +DROP TABLE actor.usr_note; + +-- preserve would-be collisions for migrating +-- ui.staff.require_initials.patron_info_notes +-- to ui.staff.require_initials.patron_standing_penalty + +\o ui.staff.require_initials.patron_info_notes.collisions.txt +SELECT a.* +FROM actor.org_unit_setting a +WHERE + a.name = 'ui.staff.require_initials.patron_info_notes' + -- hits on org_unit + AND a.org_unit IN ( + SELECT b.org_unit + FROM actor.org_unit_setting b + WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty' + ) + -- but doesn't hit on org_unit + value + AND CONCAT_WS('|',a.org_unit::TEXT,a.value::TEXT) NOT IN ( + SELECT CONCAT_WS('|',b.org_unit::TEXT,b.value::TEXT) + FROM actor.org_unit_setting b + WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty' + ); +\o + +-- and preserve the _log data + +\o ui.staff.require_initials.patron_info_notes.log_data.txt +SELECT * +FROM config.org_unit_setting_type_log +WHERE field_name = 'ui.staff.require_initials.patron_info_notes'; +\o + +-- migrate the non-collisions + +INSERT INTO actor.org_unit_setting (org_unit, name, value) +SELECT a.org_unit, 'ui.staff.require_initials.patron_standing_penalty', a.value +FROM actor.org_unit_setting a +WHERE + a.name = 'ui.staff.require_initials.patron_info_notes' + AND a.org_unit NOT IN ( + SELECT b.org_unit + FROM actor.org_unit_setting b + WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty' + ) +; + +-- and now delete the old patron_info_notes settings + +DELETE FROM actor.org_unit_setting + WHERE name = 'ui.staff.require_initials.patron_info_notes'; +DELETE FROM config.org_unit_setting_type_log + WHERE field_name = 'ui.staff.require_initials.patron_info_notes'; +DELETE FROM config.org_unit_setting_type + WHERE name = 'ui.staff.require_initials.patron_info_notes'; + +-- relabel the org unit setting type + +UPDATE config.org_unit_setting_type +SET + label = oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty', + 'Require staff initials for entry/edit of patron standing penalties and notes.', + 'coust', 'label'), + description = oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty', + 'Require staff initials for entry/edit of patron standing penalties and notes.', + 'coust', 'description') +WHERE + name = 'ui.staff.require_initials.patron_standing_penalty' +; + +-- preserve _log data for some different settings on their way out + +\o ui.patron.edit.au.alert_message.show_suggest.log_data.txt +SELECT * +FROM config.org_unit_setting_type_log +WHERE field_name IN ( + 'ui.patron.edit.au.alert_message.show', + 'ui.patron.edit.au.alert_message.suggest' +); +\o + +-- remove patron editor alert message settings + +DELETE FROM actor.org_unit_setting + WHERE name = 'ui.patron.edit.au.alert_message.show'; +DELETE FROM config.org_unit_setting_type_log + WHERE field_name = 'ui.patron.edit.au.alert_message.show'; +DELETE FROM config.org_unit_setting_type + WHERE name = 'ui.patron.edit.au.alert_message.show'; + +DELETE FROM actor.org_unit_setting + WHERE name = 'ui.patron.edit.au.alert_message.suggest'; +DELETE FROM config.org_unit_setting_type_log + WHERE field_name = 'ui.patron.edit.au.alert_message.suggest'; +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_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; + +COMMIT; + -- 2.11.0