4 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
6 specified_dest_usr IN INTEGER
14 IF specified_dest_usr IS NULL THEN
15 dest_usr := 1; -- Admin user on stock installs
17 dest_usr := specified_dest_usr;
20 -- action_trigger.event (even doing this, event_output may--and probably does--contain PII and should have a retention/removal policy)
21 UPDATE action_trigger.event SET context_user = dest_usr WHERE context_user = src_usr;
24 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
25 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
26 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
27 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
28 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
29 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
30 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
31 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
33 -- Update with a rename to avoid collisions
39 suffix := ' (' || src_usr || ')';
43 SET owner = dest_usr, name = name || suffix
44 WHERE id = renamable_row.id;
45 EXCEPTION WHEN unique_violation THEN
46 suffix := suffix || ' ';
53 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
54 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
55 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
56 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
57 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
58 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
59 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
60 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
63 DELETE FROM action.circulation WHERE usr = src_usr;
64 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
65 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
66 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
67 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
68 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
69 DELETE FROM action.hold_request WHERE usr = src_usr;
70 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
71 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
72 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
73 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
74 DELETE FROM action.survey_response WHERE usr = src_usr;
75 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
76 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
77 UPDATE action.curbside SET notes = NULL WHERE patron = 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;
83 DELETE FROM actor.usr_message WHERE usr = src_usr;
85 -- The following update is intended to avoid transient violations of a foreign
86 -- key constraint, whereby actor.usr_address references itself. It may not be
87 -- necessary, but it does no harm.
88 UPDATE actor.usr_address SET replaces = NULL
89 WHERE usr = src_usr AND replaces IS NOT NULL;
90 DELETE FROM actor.usr_address WHERE usr = src_usr;
91 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
92 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
93 DELETE FROM actor.usr_setting WHERE usr = src_usr;
94 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
95 UPDATE actor.usr_message SET title = 'purged', message = 'purged', read_date = NOW() WHERE usr = src_usr;
96 DELETE FROM actor.usr_message WHERE usr = src_usr;
97 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
98 UPDATE actor.usr_message SET editor = dest_usr WHERE editor = src_usr;
101 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
102 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
103 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
104 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
105 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
106 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
109 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
110 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
111 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
112 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
113 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
114 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
115 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
116 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
119 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
120 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
121 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
122 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
125 -- Update buckets with a rename to avoid collisions
128 FROM container.biblio_record_entry_bucket
129 WHERE owner = src_usr
131 suffix := ' (' || src_usr || ')';
134 UPDATE container.biblio_record_entry_bucket
135 SET owner = dest_usr, name = name || suffix
136 WHERE id = renamable_row.id;
137 EXCEPTION WHEN unique_violation THEN
138 suffix := suffix || ' ';
147 FROM container.call_number_bucket
148 WHERE owner = src_usr
150 suffix := ' (' || src_usr || ')';
153 UPDATE container.call_number_bucket
154 SET owner = dest_usr, name = name || suffix
155 WHERE id = renamable_row.id;
156 EXCEPTION WHEN unique_violation THEN
157 suffix := suffix || ' ';
166 FROM container.copy_bucket
167 WHERE owner = src_usr
169 suffix := ' (' || src_usr || ')';
172 UPDATE container.copy_bucket
173 SET owner = dest_usr, name = name || suffix
174 WHERE id = renamable_row.id;
175 EXCEPTION WHEN unique_violation THEN
176 suffix := suffix || ' ';
185 FROM container.user_bucket
186 WHERE owner = src_usr
188 suffix := ' (' || src_usr || ')';
191 UPDATE container.user_bucket
192 SET owner = dest_usr, name = name || suffix
193 WHERE id = renamable_row.id;
194 EXCEPTION WHEN unique_violation THEN
195 suffix := suffix || ' ';
202 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
205 DELETE FROM money.billable_xact WHERE usr = src_usr;
206 DELETE FROM money.collections_tracker WHERE usr = src_usr;
207 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
210 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
211 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
212 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
213 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
216 -- Update with a rename to avoid collisions
220 FROM reporter.output_folder
221 WHERE owner = src_usr
223 suffix := ' (' || src_usr || ')';
226 UPDATE reporter.output_folder
227 SET owner = dest_usr, name = name || suffix
228 WHERE id = renamable_row.id;
229 EXCEPTION WHEN unique_violation THEN
230 suffix := suffix || ' ';
236 EXCEPTION WHEN undefined_table THEN
241 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
242 EXCEPTION WHEN undefined_table THEN
246 -- Update with a rename to avoid collisions
250 FROM reporter.report_folder
251 WHERE owner = src_usr
253 suffix := ' (' || src_usr || ')';
256 UPDATE reporter.report_folder
257 SET owner = dest_usr, name = name || suffix
258 WHERE id = renamable_row.id;
259 EXCEPTION WHEN unique_violation THEN
260 suffix := suffix || ' ';
266 EXCEPTION WHEN undefined_table THEN
271 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
272 EXCEPTION WHEN undefined_table THEN
277 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
278 EXCEPTION WHEN undefined_table THEN
282 -- Update with a rename to avoid collisions
286 FROM reporter.template_folder
287 WHERE owner = src_usr
289 suffix := ' (' || src_usr || ')';
292 UPDATE reporter.template_folder
293 SET owner = dest_usr, name = name || suffix
294 WHERE id = renamable_row.id;
295 EXCEPTION WHEN unique_violation THEN
296 suffix := suffix || ' ';
302 EXCEPTION WHEN undefined_table THEN
307 -- Update with a rename to avoid collisions
311 WHERE owner = src_usr
313 suffix := ' (' || src_usr || ')';
316 UPDATE vandelay.queue
317 SET owner = dest_usr, name = name || suffix
318 WHERE id = renamable_row.id;
319 EXCEPTION WHEN unique_violation THEN
320 suffix := suffix || ' ';
327 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
329 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
330 -- can access the information before deletion.
334 mailing_address = NULL,
335 billing_address = NULL