3 SELECT evergreen.upgrade_deps_block_check('1304', :eg_version);
5 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
7 specified_dest_usr IN INTEGER
15 IF specified_dest_usr IS NULL THEN
16 dest_usr := 1; -- Admin user on stock installs
18 dest_usr := specified_dest_usr;
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;
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;
34 -- Update with a rename to avoid collisions
40 suffix := ' (' || src_usr || ')';
44 SET owner = dest_usr, name = name || suffix
45 WHERE id = renamable_row.id;
46 EXCEPTION WHEN unique_violation THEN
47 suffix := suffix || ' ';
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;
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;
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;
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;
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;
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;
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;
124 -- Update buckets with a rename to avoid collisions
127 FROM container.biblio_record_entry_bucket
128 WHERE owner = src_usr
130 suffix := ' (' || src_usr || ')';
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 || ' ';
146 FROM container.call_number_bucket
147 WHERE owner = src_usr
149 suffix := ' (' || src_usr || ')';
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 || ' ';
165 FROM container.copy_bucket
166 WHERE owner = src_usr
168 suffix := ' (' || src_usr || ')';
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 || ' ';
184 FROM container.user_bucket
185 WHERE owner = src_usr
187 suffix := ' (' || src_usr || ')';
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 || ' ';
201 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
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;
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;
215 -- Update with a rename to avoid collisions
219 FROM reporter.output_folder
220 WHERE owner = src_usr
222 suffix := ' (' || src_usr || ')';
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 || ' ';
235 EXCEPTION WHEN undefined_table THEN
240 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
241 EXCEPTION WHEN undefined_table THEN
245 -- Update with a rename to avoid collisions
249 FROM reporter.report_folder
250 WHERE owner = src_usr
252 suffix := ' (' || src_usr || ')';
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 || ' ';
265 EXCEPTION WHEN undefined_table THEN
270 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
271 EXCEPTION WHEN undefined_table THEN
276 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
277 EXCEPTION WHEN undefined_table THEN
281 -- Update with a rename to avoid collisions
285 FROM reporter.template_folder
286 WHERE owner = src_usr
288 suffix := ' (' || src_usr || ')';
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 || ' ';
301 EXCEPTION WHEN undefined_table THEN
306 -- Update with a rename to avoid collisions
310 WHERE owner = src_usr
312 suffix := ' (' || src_usr || ')';
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 || ' ';
326 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
328 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
329 -- can access the information before deletion.
333 mailing_address = NULL,
334 billing_address = NULL
340 CREATE OR REPLACE FUNCTION actor.usr_delete(
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;
350 new_dob actor.usr.dob%type;
353 id || '-PURGED-' || now(),
367 -- Quit if no such user
369 IF old_profile IS NULL THEN
373 perform actor.usr_purge_data( src_usr, dest_usr );
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.
384 permission.grp_ancestors( old_profile )
393 actor.org_unit_ancestors( old_home_ou )
397 -- Truncate date of birth
399 IF new_dob IS NOT NULL THEN
400 new_dob := date_trunc( 'year', new_dob );
407 profile = new_profile,
410 passwd = random()::text,
415 FROM config.identification_type
420 net_access_level = DEFAULT,
423 first_given_name = new_name,
424 second_given_name = NULL,
425 family_name = new_name,
430 evening_phone = NULL,
432 mailing_address = NULL,
433 billing_address = NULL,
434 home_ou = new_home_ou,
437 master_account = DEFAULT,
438 super_user = DEFAULT,
443 claims_returned_count = DEFAULT,
444 credit_forward_balance = DEFAULT,
445 last_xact_id = DEFAULT,
447 pref_first_given_name = NULL,
448 pref_second_given_name = NULL,
449 pref_family_name = NULL,
451 name_keywords = NULL,