Open-ILS/src/sql/Pg/upgrade/1288.schema.note_and_message_consolidation.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1288', :eg_version);
   4
   5 -- stage a copy of notes, temporarily setting
   6 -- the id to the negative value for later ausp
   7 -- id munging
   8 CREATE TABLE actor.XXXX_penalty_notes AS
   9     SELECT id * -1 AS id, usr, org_unit, set_date, note
  10     FROM actor.usr_standing_penalty
  11     WHERE NULLIF(BTRIM(note),'') IS NOT NULL;
  12
  13 ALTER TABLE actor.usr_standing_penalty ALTER COLUMN id SET DEFAULT nextval('actor.usr_message_id_seq'::regclass);
  14 ALTER TABLE actor.usr_standing_penalty ADD COLUMN usr_message BIGINT REFERENCES actor.usr_message(id);
  15 CREATE INDEX usr_standing_penalty_usr_message_idx ON actor.usr_standing_penalty (usr_message);
  16 ALTER TABLE actor.usr_standing_penalty DROP COLUMN note;
  17
  18 -- munge ausp IDs and aum IDs so that they're disjoint sets
  19 UPDATE actor.usr_standing_penalty SET id = id * -1; -- move them out of the way to avoid mid-statement collisions
  20
  21 WITH messages AS ( SELECT COALESCE(MAX(id), 0) AS max_id FROM actor.usr_message )
  22 UPDATE actor.usr_standing_penalty SET id = id * -1 + messages.max_id FROM messages;
  23
  24 -- doing the same thing to the staging table because
  25 -- we had to grab a copy of ausp.note first. We had
  26 -- to grab that copy first because we're both ALTERing
  27 -- and UPDATEing ausp, and all of the ALTER TABLEs
  28 -- have to be done before we can modify data in the table
  29 -- lest ALTER TABLE gets blocked by a pending trigger
  30 -- event
  31 WITH messages AS ( SELECT COALESCE(MAX(id), 0) AS max_id FROM actor.usr_message )
  32 UPDATE actor.XXXX_penalty_notes SET id = id * -1 + messages.max_id FROM messages;
  33
  34 SELECT SETVAL('actor.usr_message_id_seq'::regclass, COALESCE((SELECT MAX(id) FROM actor.usr_standing_penalty) + 1, 1), FALSE);
  35
  36 ALTER TABLE actor.usr_message ADD COLUMN pub BOOL NOT NULL DEFAULT FALSE;
  37 ALTER TABLE actor.usr_message ADD COLUMN stop_date TIMESTAMP WITH TIME ZONE;
  38 ALTER TABLE actor.usr_message ADD COLUMN editor BIGINT REFERENCES actor.usr (id);
  39 ALTER TABLE actor.usr_message ADD COLUMN edit_date TIMESTAMP WITH TIME ZONE;
  40
  41 DROP VIEW actor.usr_message_limited;
  42 CREATE VIEW actor.usr_message_limited
  43 AS SELECT * FROM actor.usr_message WHERE pub AND NOT deleted;
  44
  45 -- alright, let's set all existing user messages to public
  46
  47 UPDATE actor.usr_message SET pub = TRUE;
  48
  49 -- alright, let's migrate penalty notes to usr_messages and link the messages back to the penalties:
  50
  51 -- here is our staging table which will be shaped exactly like
  52 -- actor.usr_message and use the same id sequence
  53 CREATE TABLE actor.XXXX_usr_message_for_penalty_notes (
  54     LIKE actor.usr_message INCLUDING DEFAULTS
  55 );
  56
  57 INSERT INTO actor.XXXX_usr_message_for_penalty_notes (
  58     usr,
  59     title,
  60     message,
  61     create_date,
  62     sending_lib,
  63     pub
  64 ) SELECT
  65     usr,
  66     'Penalty Note ID ' || id,
  67     note,
  68     set_date,
  69     org_unit,
  70     FALSE
  71 FROM
  72     actor.XXXX_penalty_notes
  73 ;
  74
  75 -- so far so good, let's push this into production
  76
  77 INSERT INTO actor.usr_message
  78     SELECT * FROM actor.XXXX_usr_message_for_penalty_notes;
  79
  80 -- and link the production penalties to these new user messages
  81
  82 UPDATE actor.usr_standing_penalty p SET usr_message = m.id
  83     FROM actor.XXXX_usr_message_for_penalty_notes m
  84     WHERE m.title = 'Penalty Note ID ' || p.id;
  85
  86 -- and remove the temporary overloading of the message title we used for this:
  87
  88 UPDATE
  89     actor.usr_message
  90 SET
  91     title = message
  92 WHERE
  93     id IN (SELECT id FROM actor.XXXX_usr_message_for_penalty_notes)
  94 ;
  95
  96 -- probably redundant here, but the spec calls for an assertion before removing
  97 -- the note column from actor.usr_standing_penalty, so being extra cautious:
  98 /*
  99 do $$ begin
 100     assert (
 101         select count(*)
 102         from actor.XXXX_usr_message_for_penalty_notes
 103         where id not in (
 104             select id from actor.usr_message
 105         )
 106     ) = 0, 'failed migrating to actor.usr_message';
 107 end; $$;
 108 */
 109
 110 -- combined view of actor.usr_standing_penalty and actor.usr_message for populating
 111 -- staff Notes (formerly Messages) interface
 112
 113 CREATE VIEW actor.usr_message_penalty AS
 114 SELECT -- ausp with or without messages
 115     ausp.id AS "id",
 116     ausp.id AS "ausp_id",
 117     aum.id AS "aum_id",
 118     ausp.org_unit AS "org_unit",
 119     ausp.org_unit AS "ausp_org_unit",
 120     aum.sending_lib AS "aum_sending_lib",
 121     ausp.usr AS "usr",
 122     ausp.usr as "ausp_usr",
 123     aum.usr as "aum_usr",
 124     ausp.standing_penalty AS "standing_penalty",
 125     ausp.staff AS "staff",
 126     ausp.set_date AS "create_date",
 127     ausp.set_date AS "ausp_set_date",
 128     aum.create_date AS "aum_create_date",
 129     ausp.stop_date AS "stop_date",
 130     ausp.stop_date AS "ausp_stop_date",
 131     aum.stop_date AS "aum_stop_date",
 132     ausp.usr_message AS "ausp_usr_message",
 133     aum.title AS "title",
 134     aum.message AS "message",
 135     aum.deleted AS "deleted",
 136     aum.read_date AS "read_date",
 137     aum.pub AS "pub",
 138     aum.editor AS "editor",
 139     aum.edit_date AS "edit_date"
 140 FROM
 141     actor.usr_standing_penalty ausp
 142     LEFT JOIN actor.usr_message aum ON (ausp.usr_message = aum.id)
 143         UNION ALL
 144 SELECT -- aum without penalties
 145     aum.id AS "id",
 146     NULL::INT AS "ausp_id",
 147     aum.id AS "aum_id",
 148     aum.sending_lib AS "org_unit",
 149     NULL::INT AS "ausp_org_unit",
 150     aum.sending_lib AS "aum_sending_lib",
 151     aum.usr AS "usr",
 152     NULL::INT as "ausp_usr",
 153     aum.usr as "aum_usr",
 154     NULL::INT AS "standing_penalty",
 155     NULL::INT AS "staff",
 156     aum.create_date AS "create_date",
 157     NULL::TIMESTAMPTZ AS "ausp_set_date",
 158     aum.create_date AS "aum_create_date",
 159     aum.stop_date AS "stop_date",
 160     NULL::TIMESTAMPTZ AS "ausp_stop_date",
 161     aum.stop_date AS "aum_stop_date",
 162     NULL::INT AS "ausp_usr_message",
 163     aum.title AS "title",
 164     aum.message AS "message",
 165     aum.deleted AS "deleted",
 166     aum.read_date AS "read_date",
 167     aum.pub AS "pub",
 168     aum.editor AS "editor",
 169     aum.edit_date AS "edit_date"
 170 FROM
 171     actor.usr_message aum
 172     LEFT JOIN actor.usr_standing_penalty ausp ON (ausp.usr_message = aum.id)
 173 WHERE NOT aum.deleted AND ausp.id IS NULL
 174 ;
 175
 176 -- fun part where we migrate the following alert messages:
 177
 178 CREATE TABLE actor.XXXX_note_and_message_consolidation AS
 179     SELECT id, home_ou, alert_message
 180     FROM actor.usr
 181     WHERE NOT deleted AND NULLIF(BTRIM(alert_message),'') IS NOT NULL;
 182
 183 -- here is our staging table which will be shaped exactly like
 184 -- actor.usr_message and use the same id sequence
 185 CREATE TABLE actor.XXXX_usr_message (
 186     LIKE actor.usr_message INCLUDING DEFAULTS
 187 );
 188
 189 INSERT INTO actor.XXXX_usr_message (
 190     usr,
 191     title,
 192     message,
 193     create_date,
 194     sending_lib,
 195     pub
 196 ) SELECT
 197     id,
 198     'converted Alert Message, real date unknown',
 199     alert_message,
 200     NOW(), -- best we can do
 201     1, -- it's this or home_ou
 202     FALSE
 203 FROM
 204     actor.XXXX_note_and_message_consolidation
 205 ;
 206
 207 -- another staging table, but for actor.usr_standing_penalty
 208 CREATE TABLE actor.XXXX_usr_standing_penalty (
 209     LIKE actor.usr_standing_penalty INCLUDING DEFAULTS
 210 );
 211
 212 INSERT INTO actor.XXXX_usr_standing_penalty (
 213     org_unit,
 214     usr,
 215     standing_penalty,
 216     staff,
 217     set_date,
 218     usr_message
 219 ) SELECT
 220     sending_lib,
 221     usr,
 222     20, -- ALERT_NOTE
 223     1, -- admin user, usually; best we can do
 224     create_date,
 225     id
 226 FROM
 227     actor.XXXX_usr_message
 228 ;
 229
 230 -- so far so good, let's push these into production
 231
 232 INSERT INTO actor.usr_message
 233     SELECT * FROM actor.XXXX_usr_message;
 234 INSERT INTO actor.usr_standing_penalty
 235     SELECT * FROM actor.XXXX_usr_standing_penalty;
 236
 237 -- probably redundant here, but the spec calls for an assertion before removing
 238 -- the alert message column from actor.usr, so being extra cautious:
 239 /*
 240 do $$ begin
 241     assert (
 242         select count(*)
 243         from actor.XXXX_usr_message
 244         where id not in (
 245             select id from actor.usr_message
 246         )
 247     ) = 0, 'failed migrating to actor.usr_message';
 248 end; $$;
 249
 250 do $$ begin
 251     assert (
 252         select count(*)
 253         from actor.XXXX_usr_standing_penalty
 254         where id not in (
 255             select id from actor.usr_standing_penalty
 256         )
 257     ) = 0, 'failed migrating to actor.usr_standing_penalty';
 258 end; $$;
 259 */
 260
 261 -- WARNING: we're going to lose the history of alert_message
 262 ALTER TABLE actor.usr DROP COLUMN alert_message CASCADE;
 263 SELECT auditor.update_auditors();
 264
 265 -- fun part where we migrate actor.usr_notes as penalties to preserve
 266 -- their creator, and then the private ones to private user messages.
 267 -- For public notes, we try to link to existing user messages if we
 268 -- can, but if we can't, we'll create new, but archived, user messages
 269 -- for the note contents.
 270
 271 CREATE TABLE actor.XXXX_usr_message_for_private_notes (
 272     LIKE actor.usr_message INCLUDING DEFAULTS
 273 );
 274 ALTER TABLE actor.XXXX_usr_message_for_private_notes ADD COLUMN orig_id BIGINT;
 275 CREATE INDEX ON actor.XXXX_usr_message_for_private_notes (orig_id);
 276
 277 INSERT INTO actor.XXXX_usr_message_for_private_notes (
 278     orig_id,
 279     usr,
 280     title,
 281     message,
 282     create_date,
 283     sending_lib,
 284     pub
 285 ) SELECT
 286     id,
 287     usr,
 288     title,
 289     value,
 290     create_date,
 291     (select home_ou from actor.usr where id = creator), -- best we can do
 292     FALSE
 293 FROM
 294     actor.usr_note
 295 WHERE
 296     NOT pub
 297 ;
 298
 299 CREATE TABLE actor.XXXX_usr_message_for_unmatched_public_notes (
 300     LIKE actor.usr_message INCLUDING DEFAULTS
 301 );
 302 ALTER TABLE actor.XXXX_usr_message_for_unmatched_public_notes ADD COLUMN orig_id BIGINT;
 303 CREATE INDEX ON actor.XXXX_usr_message_for_unmatched_public_notes (orig_id);
 304
 305 INSERT INTO actor.XXXX_usr_message_for_unmatched_public_notes (
 306     orig_id,
 307     usr,
 308     title,
 309     message,
 310     create_date,
 311     deleted,
 312     sending_lib,
 313     pub
 314 ) SELECT
 315     id,
 316     usr,
 317     title,
 318     value,
 319     create_date,
 320     TRUE, -- the patron has likely already seen and deleted the corresponding usr_message
 321     (select home_ou from actor.usr where id = creator), -- best we can do
 322     FALSE
 323 FROM
 324     actor.usr_note n
 325 WHERE
 326     pub AND NOT EXISTS (SELECT 1 FROM actor.usr_message m WHERE n.usr = m.usr AND n.create_date = m.create_date)
 327 ;
 328
 329 -- now, in order to preserve the creator from usr_note, we want to create standing SILENT_NOTE penalties for
 330 --  1) actor.XXXX_usr_message_for_private_notes and associated usr_note entries
 331 --  2) actor.XXXX_usr_message_for_unmatched_public_notes and associated usr_note entries, but archive these
 332 --  3) usr_note and usr_message entries that can be matched
 333
 334 CREATE TABLE actor.XXXX_usr_standing_penalties_for_notes (
 335     LIKE actor.usr_standing_penalty INCLUDING DEFAULTS
 336 );
 337
 338 --  1) actor.XXXX_usr_message_for_private_notes and associated usr_note entries
 339 INSERT INTO actor.XXXX_usr_standing_penalties_for_notes (
 340     org_unit,
 341     usr,
 342     standing_penalty,
 343     staff,
 344     set_date,
 345     stop_date,
 346     usr_message
 347 ) SELECT
 348     m.sending_lib,
 349     m.usr,
 350     21, -- SILENT_NOTE
 351     n.creator,
 352     m.create_date,
 353     m.stop_date,
 354     m.id
 355 FROM
 356     actor.usr_note n,
 357     actor.XXXX_usr_message_for_private_notes m
 358 WHERE
 359     n.usr = m.usr AND n.id = m.orig_id AND NOT n.pub AND NOT m.pub
 360 ;
 361
 362 --  2) actor.XXXX_usr_message_for_unmatched_public_notes and associated usr_note entries, but archive these
 363 INSERT INTO actor.XXXX_usr_standing_penalties_for_notes (
 364     org_unit,
 365     usr,
 366     standing_penalty,
 367     staff,
 368     set_date,
 369     stop_date,
 370     usr_message
 371 ) SELECT
 372     m.sending_lib,
 373     m.usr,
 374     21, -- SILENT_NOTE
 375     n.creator,
 376     m.create_date,
 377     m.stop_date,
 378     m.id
 379 FROM
 380     actor.usr_note n,
 381     actor.XXXX_usr_message_for_unmatched_public_notes m
 382 WHERE
 383     n.usr = m.usr AND n.id = m.orig_id AND n.pub AND m.pub
 384 ;
 385
 386 --  3) usr_note and usr_message entries that can be matched
 387 INSERT INTO actor.XXXX_usr_standing_penalties_for_notes (
 388     org_unit,
 389     usr,
 390     standing_penalty,
 391     staff,
 392     set_date,
 393     stop_date,
 394     usr_message
 395 ) SELECT
 396     m.sending_lib,
 397     m.usr,
 398     21, -- SILENT_NOTE
 399     n.creator,
 400     m.create_date,
 401     m.stop_date,
 402     m.id
 403 FROM
 404     actor.usr_note n
 405     JOIN actor.usr_message m ON (n.usr = m.usr AND n.id = m.id)
 406 WHERE
 407     NOT EXISTS ( SELECT 1 FROM actor.XXXX_usr_message_for_private_notes WHERE id = m.id )
 408     AND NOT EXISTS ( SELECT 1 FROM actor.XXXX_usr_message_for_unmatched_public_notes WHERE id = m.id )
 409 ;
 410
 411 -- so far so good, let's push these into production
 412
 413 INSERT INTO actor.usr_message
 414     SELECT id, usr, title, message, create_date, deleted, read_date, sending_lib, pub, stop_date, editor, edit_date FROM actor.XXXX_usr_message_for_private_notes
 415     UNION SELECT id, usr, title, message, create_date, deleted, read_date, sending_lib, pub, stop_date, editor, edit_date FROM actor.XXXX_usr_message_for_unmatched_public_notes;
 416 INSERT INTO actor.usr_standing_penalty
 417     SELECT * FROM actor.XXXX_usr_standing_penalties_for_notes;
 418
 419 -- probably redundant here, but the spec calls for an assertion before dropping
 420 -- the actor.usr_note table, so being extra cautious:
 421 /*
 422 do $$ begin
 423     assert (
 424         select count(*)
 425         from actor.XXXX_usr_message_for_private_notes
 426         where id not in (
 427             select id from actor.usr_message
 428         )
 429     ) = 0, 'failed migrating to actor.usr_message';
 430 end; $$;
 431 */
 432
 433 DROP TABLE actor.usr_note CASCADE;
 434
 435 -- preserve would-be collisions for migrating
 436 -- ui.staff.require_initials.patron_info_notes
 437 -- to ui.staff.require_initials.patron_standing_penalty
 438
 439 \o ui.staff.require_initials.patron_info_notes.collisions.txt
 440 SELECT a.*
 441 FROM actor.org_unit_setting a
 442 WHERE
 443         a.name = 'ui.staff.require_initials.patron_info_notes'
 444     -- hits on org_unit
 445     AND a.org_unit IN (
 446         SELECT b.org_unit
 447         FROM actor.org_unit_setting b
 448         WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty'
 449     )
 450     -- but doesn't hit on org_unit + value
 451     AND CONCAT_WS('|',a.org_unit::TEXT,a.value::TEXT) NOT IN (
 452         SELECT CONCAT_WS('|',b.org_unit::TEXT,b.value::TEXT)
 453         FROM actor.org_unit_setting b
 454         WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty'
 455     );
 456 \o
 457
 458 -- and preserve the _log data
 459
 460 \o ui.staff.require_initials.patron_info_notes.log_data.txt
 461 SELECT *
 462 FROM config.org_unit_setting_type_log
 463 WHERE field_name = 'ui.staff.require_initials.patron_info_notes';
 464 \o
 465
 466 -- migrate the non-collisions
 467
 468 INSERT INTO actor.org_unit_setting (org_unit, name, value)
 469 SELECT a.org_unit, 'ui.staff.require_initials.patron_standing_penalty', a.value
 470 FROM actor.org_unit_setting a
 471 WHERE
 472         a.name = 'ui.staff.require_initials.patron_info_notes'
 473     AND a.org_unit NOT IN (
 474         SELECT b.org_unit
 475         FROM actor.org_unit_setting b
 476         WHERE b.name = 'ui.staff.require_initials.patron_standing_penalty'
 477     )
 478 ;
 479
 480 -- and now delete the old patron_info_notes settings
 481
 482 DELETE FROM actor.org_unit_setting
 483     WHERE name = 'ui.staff.require_initials.patron_info_notes';
 484 DELETE FROM config.org_unit_setting_type_log
 485     WHERE field_name = 'ui.staff.require_initials.patron_info_notes';
 486 DELETE FROM config.org_unit_setting_type
 487     WHERE name = 'ui.staff.require_initials.patron_info_notes';
 488
 489 -- relabel the org unit setting type
 490
 491 UPDATE config.org_unit_setting_type
 492 SET
 493     label = oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty',
 494         'Require staff initials for entry/edit of patron standing penalties and notes.',
 495         'coust', 'label'),
 496     description = oils_i18n_gettext('ui.staff.require_initials.patron_standing_penalty',
 497         'Require staff initials for entry/edit of patron standing penalties and notes.',
 498         'coust', 'description')
 499 WHERE
 500     name = 'ui.staff.require_initials.patron_standing_penalty'
 501 ;
 502
 503 -- preserve _log data for some different settings on their way out
 504
 505 \o ui.patron.edit.au.alert_message.show_suggest.log_data.txt
 506 SELECT *
 507 FROM config.org_unit_setting_type_log
 508 WHERE field_name IN (
 509     'ui.patron.edit.au.alert_message.show',
 510     'ui.patron.edit.au.alert_message.suggest'
 511 );
 512 \o
 513
 514 -- remove patron editor alert message settings
 515
 516 DELETE FROM actor.org_unit_setting
 517     WHERE name = 'ui.patron.edit.au.alert_message.show';
 518 DELETE FROM config.org_unit_setting_type_log
 519     WHERE field_name = 'ui.patron.edit.au.alert_message.show';
 520 DELETE FROM config.org_unit_setting_type
 521     WHERE name = 'ui.patron.edit.au.alert_message.show';
 522
 523 DELETE FROM actor.org_unit_setting
 524     WHERE name = 'ui.patron.edit.au.alert_message.suggest';
 525 DELETE FROM config.org_unit_setting_type_log
 526     WHERE field_name = 'ui.patron.edit.au.alert_message.suggest';
 527 DELETE FROM config.org_unit_setting_type
 528     WHERE name = 'ui.patron.edit.au.alert_message.suggest';
 529
 530 -- comment these out if you want the staging tables to stick around
 531 DROP TABLE actor.XXXX_note_and_message_consolidation;
 532 DROP TABLE actor.XXXX_penalty_notes;
 533 DROP TABLE actor.XXXX_usr_message_for_penalty_notes;
 534 DROP TABLE actor.XXXX_usr_message;
 535 DROP TABLE actor.XXXX_usr_standing_penalty;
 536 DROP TABLE actor.XXXX_usr_message_for_private_notes;
 537 DROP TABLE actor.XXXX_usr_message_for_unmatched_public_notes;
 538 DROP TABLE actor.XXXX_usr_standing_penalties_for_notes;
 539
 540 COMMIT;
 541