Open-ILS/src/sql/Pg/upgrade/1304.schema.update_actor_usr_funcs.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1304', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
   6         src_usr  IN INTEGER,
   7         specified_dest_usr IN INTEGER
   8 ) RETURNS VOID AS $$
   9 DECLARE
  10         suffix TEXT;
  11         renamable_row RECORD;
  12         dest_usr INTEGER;
  13 BEGIN
  14
  15         IF specified_dest_usr IS NULL THEN
  16                 dest_usr := 1; -- Admin user on stock installs
  17         ELSE
  18                 dest_usr := specified_dest_usr;
  19         END IF;
  20
  21     -- action_trigger.event (even doing this, event_output may--and probably does--contain PII and should have a retention/removal policy)
  22     UPDATE action_trigger.event SET context_user = dest_usr WHERE context_user = src_usr;
  23
  24         -- acq.*
  25         UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
  26         UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
  27         UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
  28         UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
  29         UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
  30         UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
  31     UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
  32         DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
  33
  34         -- Update with a rename to avoid collisions
  35         FOR renamable_row in
  36                 SELECT id, name
  37                 FROM   acq.picklist
  38                 WHERE  owner = src_usr
  39         LOOP
  40                 suffix := ' (' || src_usr || ')';
  41                 LOOP
  42                         BEGIN
  43                                 UPDATE  acq.picklist
  44                                 SET     owner = dest_usr, name = name || suffix
  45                                 WHERE   id = renamable_row.id;
  46                         EXCEPTION WHEN unique_violation THEN
  47                                 suffix := suffix || ' ';
  48                                 CONTINUE;
  49                         END;
  50                         EXIT;
  51                 END LOOP;
  52         END LOOP;
  53
  54         UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
  55         UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
  56         UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
  57         UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
  58         UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
  59         UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
  60         UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
  61         UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
  62
  63         -- action.*
  64         DELETE FROM action.circulation WHERE usr = src_usr;
  65         UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
  66         UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
  67         UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
  68         UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
  69         UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
  70         DELETE FROM action.hold_request WHERE usr = src_usr;
  71         UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
  72         UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
  73         DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
  74         UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
  75         DELETE FROM action.survey_response WHERE usr = src_usr;
  76         UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
  77         DELETE FROM action.usr_circ_history WHERE usr = src_usr;
  78
  79         -- actor.*
  80         DELETE FROM actor.card WHERE usr = src_usr;
  81         DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
  82         DELETE FROM actor.usr_privacy_waiver WHERE usr = src_usr;
  83
  84         -- The following update is intended to avoid transient violations of a foreign
  85         -- key constraint, whereby actor.usr_address references itself.  It may not be
  86         -- necessary, but it does no harm.
  87         UPDATE actor.usr_address SET replaces = NULL
  88                 WHERE usr = src_usr AND replaces IS NOT NULL;
  89         DELETE FROM actor.usr_address WHERE usr = src_usr;
  90         DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
  91         UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
  92         DELETE FROM actor.usr_setting WHERE usr = src_usr;
  93         DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
  94         UPDATE actor.usr_message SET title = 'purged', message = 'purged', read_date = NOW() WHERE usr = src_usr;
  95         DELETE FROM actor.usr_message WHERE usr = src_usr;
  96         UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
  97         UPDATE actor.usr_message SET editor = dest_usr WHERE editor = src_usr;
  98
  99         -- asset.*
 100         UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
 101         UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
 102         UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
 103         UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
 104         UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
 105         UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
 106
 107         -- auditor.*
 108         DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
 109         DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
 110         UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
 111         UPDATE auditor.asset_call_number_history SET editor  = dest_usr WHERE editor  = src_usr;
 112         UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
 113         UPDATE auditor.asset_copy_history SET editor  = dest_usr WHERE editor  = src_usr;
 114         UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
 115         UPDATE auditor.biblio_record_entry_history SET editor  = dest_usr WHERE editor  = src_usr;
 116
 117         -- biblio.*
 118         UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
 119         UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
 120         UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
 121         UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
 122
 123         -- container.*
 124         -- Update buckets with a rename to avoid collisions
 125         FOR renamable_row in
 126                 SELECT id, name
 127                 FROM   container.biblio_record_entry_bucket
 128                 WHERE  owner = src_usr
 129         LOOP
 130                 suffix := ' (' || src_usr || ')';
 131                 LOOP
 132                         BEGIN
 133                                 UPDATE  container.biblio_record_entry_bucket
 134                                 SET     owner = dest_usr, name = name || suffix
 135                                 WHERE   id = renamable_row.id;
 136                         EXCEPTION WHEN unique_violation THEN
 137                                 suffix := suffix || ' ';
 138                                 CONTINUE;
 139                         END;
 140                         EXIT;
 141                 END LOOP;
 142         END LOOP;
 143
 144         FOR renamable_row in
 145                 SELECT id, name
 146                 FROM   container.call_number_bucket
 147                 WHERE  owner = src_usr
 148         LOOP
 149                 suffix := ' (' || src_usr || ')';
 150                 LOOP
 151                         BEGIN
 152                                 UPDATE  container.call_number_bucket
 153                                 SET     owner = dest_usr, name = name || suffix
 154                                 WHERE   id = renamable_row.id;
 155                         EXCEPTION WHEN unique_violation THEN
 156                                 suffix := suffix || ' ';
 157                                 CONTINUE;
 158                         END;
 159                         EXIT;
 160                 END LOOP;
 161         END LOOP;
 162
 163         FOR renamable_row in
 164                 SELECT id, name
 165                 FROM   container.copy_bucket
 166                 WHERE  owner = src_usr
 167         LOOP
 168                 suffix := ' (' || src_usr || ')';
 169                 LOOP
 170                         BEGIN
 171                                 UPDATE  container.copy_bucket
 172                                 SET     owner = dest_usr, name = name || suffix
 173                                 WHERE   id = renamable_row.id;
 174                         EXCEPTION WHEN unique_violation THEN
 175                                 suffix := suffix || ' ';
 176                                 CONTINUE;
 177                         END;
 178                         EXIT;
 179                 END LOOP;
 180         END LOOP;
 181
 182         FOR renamable_row in
 183                 SELECT id, name
 184                 FROM   container.user_bucket
 185                 WHERE  owner = src_usr
 186         LOOP
 187                 suffix := ' (' || src_usr || ')';
 188                 LOOP
 189                         BEGIN
 190                                 UPDATE  container.user_bucket
 191                                 SET     owner = dest_usr, name = name || suffix
 192                                 WHERE   id = renamable_row.id;
 193                         EXCEPTION WHEN unique_violation THEN
 194                                 suffix := suffix || ' ';
 195                                 CONTINUE;
 196                         END;
 197                         EXIT;
 198                 END LOOP;
 199         END LOOP;
 200
 201         DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
 202
 203         -- money.*
 204         DELETE FROM money.billable_xact WHERE usr = src_usr;
 205         DELETE FROM money.collections_tracker WHERE usr = src_usr;
 206         UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
 207
 208         -- permission.*
 209         DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
 210         DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
 211         DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
 212         DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
 213
 214         -- reporter.*
 215         -- Update with a rename to avoid collisions
 216         BEGIN
 217                 FOR renamable_row in
 218                         SELECT id, name
 219                         FROM   reporter.output_folder
 220                         WHERE  owner = src_usr
 221                 LOOP
 222                         suffix := ' (' || src_usr || ')';
 223                         LOOP
 224                                 BEGIN
 225                                         UPDATE  reporter.output_folder
 226                                         SET     owner = dest_usr, name = name || suffix
 227                                         WHERE   id = renamable_row.id;
 228                                 EXCEPTION WHEN unique_violation THEN
 229                                         suffix := suffix || ' ';
 230                                         CONTINUE;
 231                                 END;
 232                                 EXIT;
 233                         END LOOP;
 234                 END LOOP;
 235         EXCEPTION WHEN undefined_table THEN
 236                 -- do nothing
 237         END;
 238
 239         BEGIN
 240                 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
 241         EXCEPTION WHEN undefined_table THEN
 242                 -- do nothing
 243         END;
 244
 245         -- Update with a rename to avoid collisions
 246         BEGIN
 247                 FOR renamable_row in
 248                         SELECT id, name
 249                         FROM   reporter.report_folder
 250                         WHERE  owner = src_usr
 251                 LOOP
 252                         suffix := ' (' || src_usr || ')';
 253                         LOOP
 254                                 BEGIN
 255                                         UPDATE  reporter.report_folder
 256                                         SET     owner = dest_usr, name = name || suffix
 257                                         WHERE   id = renamable_row.id;
 258                                 EXCEPTION WHEN unique_violation THEN
 259                                         suffix := suffix || ' ';
 260                                         CONTINUE;
 261                                 END;
 262                                 EXIT;
 263                         END LOOP;
 264                 END LOOP;
 265         EXCEPTION WHEN undefined_table THEN
 266                 -- do nothing
 267         END;
 268
 269         BEGIN
 270                 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
 271         EXCEPTION WHEN undefined_table THEN
 272                 -- do nothing
 273         END;
 274
 275         BEGIN
 276                 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
 277         EXCEPTION WHEN undefined_table THEN
 278                 -- do nothing
 279         END;
 280
 281         -- Update with a rename to avoid collisions
 282         BEGIN
 283                 FOR renamable_row in
 284                         SELECT id, name
 285                         FROM   reporter.template_folder
 286                         WHERE  owner = src_usr
 287                 LOOP
 288                         suffix := ' (' || src_usr || ')';
 289                         LOOP
 290                                 BEGIN
 291                                         UPDATE  reporter.template_folder
 292                                         SET     owner = dest_usr, name = name || suffix
 293                                         WHERE   id = renamable_row.id;
 294                                 EXCEPTION WHEN unique_violation THEN
 295                                         suffix := suffix || ' ';
 296                                         CONTINUE;
 297                                 END;
 298                                 EXIT;
 299                         END LOOP;
 300                 END LOOP;
 301         EXCEPTION WHEN undefined_table THEN
 302         -- do nothing
 303         END;
 304
 305         -- vandelay.*
 306         -- Update with a rename to avoid collisions
 307         FOR renamable_row in
 308                 SELECT id, name
 309                 FROM   vandelay.queue
 310                 WHERE  owner = src_usr
 311         LOOP
 312                 suffix := ' (' || src_usr || ')';
 313                 LOOP
 314                         BEGIN
 315                                 UPDATE  vandelay.queue
 316                                 SET     owner = dest_usr, name = name || suffix
 317                                 WHERE   id = renamable_row.id;
 318                         EXCEPTION WHEN unique_violation THEN
 319                                 suffix := suffix || ' ';
 320                                 CONTINUE;
 321                         END;
 322                         EXIT;
 323                 END LOOP;
 324         END LOOP;
 325
 326     UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
 327
 328     -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
 329     -- can access the information before deletion.
 330         UPDATE actor.usr SET
 331                 active = FALSE,
 332                 card = NULL,
 333                 mailing_address = NULL,
 334                 billing_address = NULL
 335         WHERE id = src_usr;
 336
 337 END;
 338 $$ LANGUAGE plpgsql;
 339
 340 CREATE OR REPLACE FUNCTION actor.usr_delete(
 341         src_usr  IN INTEGER,
 342         dest_usr IN INTEGER
 343 ) RETURNS VOID AS $$
 344 DECLARE
 345         old_profile actor.usr.profile%type;
 346         old_home_ou actor.usr.home_ou%type;
 347         new_profile actor.usr.profile%type;
 348         new_home_ou actor.usr.home_ou%type;
 349         new_name    text;
 350         new_dob     actor.usr.dob%type;
 351 BEGIN
 352         SELECT
 353                 id || '-PURGED-' || now(),
 354                 profile,
 355                 home_ou,
 356                 dob
 357         INTO
 358                 new_name,
 359                 old_profile,
 360                 old_home_ou,
 361                 new_dob
 362         FROM
 363                 actor.usr
 364         WHERE
 365                 id = src_usr;
 366         --
 367         -- Quit if no such user
 368         --
 369         IF old_profile IS NULL THEN
 370                 RETURN;
 371         END IF;
 372         --
 373         perform actor.usr_purge_data( src_usr, dest_usr );
 374         --
 375         -- Find the root grp_tree and the root org_unit.  This would be simpler if we
 376         -- could assume that there is only one root.  Theoretically, someday, maybe,
 377         -- there could be multiple roots, so we take extra trouble to get the right ones.
 378         --
 379         SELECT
 380                 id
 381         INTO
 382                 new_profile
 383         FROM
 384                 permission.grp_ancestors( old_profile )
 385         WHERE
 386                 parent is null;
 387         --
 388         SELECT
 389                 id
 390         INTO
 391                 new_home_ou
 392         FROM
 393                 actor.org_unit_ancestors( old_home_ou )
 394         WHERE
 395                 parent_ou is null;
 396         --
 397         -- Truncate date of birth
 398         --
 399         IF new_dob IS NOT NULL THEN
 400                 new_dob := date_trunc( 'year', new_dob );
 401         END IF;
 402         --
 403         UPDATE
 404                 actor.usr
 405                 SET
 406                         card = NULL,
 407                         profile = new_profile,
 408                         usrname = new_name,
 409                         email = NULL,
 410                         passwd = random()::text,
 411                         standing = DEFAULT,
 412                         ident_type =
 413                         (
 414                                 SELECT MIN( id )
 415                                 FROM config.identification_type
 416                         ),
 417                         ident_value = NULL,
 418                         ident_type2 = NULL,
 419                         ident_value2 = NULL,
 420                         net_access_level = DEFAULT,
 421                         photo_url = NULL,
 422                         prefix = NULL,
 423                         first_given_name = new_name,
 424                         second_given_name = NULL,
 425                         family_name = new_name,
 426                         suffix = NULL,
 427                         alias = NULL,
 428             guardian = NULL,
 429                         day_phone = NULL,
 430                         evening_phone = NULL,
 431                         other_phone = NULL,
 432                         mailing_address = NULL,
 433                         billing_address = NULL,
 434                         home_ou = new_home_ou,
 435                         dob = new_dob,
 436                         active = FALSE,
 437                         master_account = DEFAULT,
 438                         super_user = DEFAULT,
 439                         barred = FALSE,
 440                         deleted = TRUE,
 441                         juvenile = DEFAULT,
 442                         usrgroup = 0,
 443                         claims_returned_count = DEFAULT,
 444                         credit_forward_balance = DEFAULT,
 445                         last_xact_id = DEFAULT,
 446                         pref_prefix = NULL,
 447                         pref_first_given_name = NULL,
 448                         pref_second_given_name = NULL,
 449                         pref_family_name = NULL,
 450                         pref_suffix = NULL,
 451                         name_keywords = NULL,
 452                         create_date = now(),
 453                         expire_date = now()
 454         WHERE
 455                 id = src_usr;
 456 END;
 457 $$ LANGUAGE plpgsql;
 458
 459 COMMIT;