3 SELECT evergreen.upgrade_deps_block_check('0901', :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;
22 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
23 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
24 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
25 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
26 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
27 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
28 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
30 -- Update with a rename to avoid collisions
36 suffix := ' (' || src_usr || ')';
40 SET owner = dest_usr, name = name || suffix
41 WHERE id = renamable_row.id;
42 EXCEPTION WHEN unique_violation THEN
43 suffix := suffix || ' ';
50 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
51 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
52 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
53 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
54 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
55 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
56 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
57 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
60 DELETE FROM action.circulation WHERE usr = src_usr;
61 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
62 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
63 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
64 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
65 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
66 DELETE FROM action.hold_request WHERE usr = src_usr;
67 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
68 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
69 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
70 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
71 DELETE FROM action.survey_response WHERE usr = src_usr;
72 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
75 DELETE FROM actor.card WHERE usr = src_usr;
76 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
78 -- The following update is intended to avoid transient violations of a foreign
79 -- key constraint, whereby actor.usr_address references itself. It may not be
80 -- necessary, but it does no harm.
81 UPDATE actor.usr_address SET replaces = NULL
82 WHERE usr = src_usr AND replaces IS NOT NULL;
83 DELETE FROM actor.usr_address WHERE usr = src_usr;
84 DELETE FROM actor.usr_note WHERE usr = src_usr;
85 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
86 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
87 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
88 DELETE FROM actor.usr_setting WHERE usr = src_usr;
89 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
90 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
93 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
94 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
95 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
96 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
97 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
98 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
101 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
102 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
103 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
104 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
105 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
106 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
107 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
108 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
111 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
112 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
113 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
114 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
117 -- Update buckets with a rename to avoid collisions
120 FROM container.biblio_record_entry_bucket
121 WHERE owner = src_usr
123 suffix := ' (' || src_usr || ')';
126 UPDATE container.biblio_record_entry_bucket
127 SET owner = dest_usr, name = name || suffix
128 WHERE id = renamable_row.id;
129 EXCEPTION WHEN unique_violation THEN
130 suffix := suffix || ' ';
139 FROM container.call_number_bucket
140 WHERE owner = src_usr
142 suffix := ' (' || src_usr || ')';
145 UPDATE container.call_number_bucket
146 SET owner = dest_usr, name = name || suffix
147 WHERE id = renamable_row.id;
148 EXCEPTION WHEN unique_violation THEN
149 suffix := suffix || ' ';
158 FROM container.copy_bucket
159 WHERE owner = src_usr
161 suffix := ' (' || src_usr || ')';
164 UPDATE container.copy_bucket
165 SET owner = dest_usr, name = name || suffix
166 WHERE id = renamable_row.id;
167 EXCEPTION WHEN unique_violation THEN
168 suffix := suffix || ' ';
177 FROM container.user_bucket
178 WHERE owner = src_usr
180 suffix := ' (' || src_usr || ')';
183 UPDATE container.user_bucket
184 SET owner = dest_usr, name = name || suffix
185 WHERE id = renamable_row.id;
186 EXCEPTION WHEN unique_violation THEN
187 suffix := suffix || ' ';
194 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
197 DELETE FROM money.billable_xact WHERE usr = src_usr;
198 DELETE FROM money.collections_tracker WHERE usr = src_usr;
199 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
202 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
203 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
204 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
205 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
208 -- Update with a rename to avoid collisions
212 FROM reporter.output_folder
213 WHERE owner = src_usr
215 suffix := ' (' || src_usr || ')';
218 UPDATE reporter.output_folder
219 SET owner = dest_usr, name = name || suffix
220 WHERE id = renamable_row.id;
221 EXCEPTION WHEN unique_violation THEN
222 suffix := suffix || ' ';
228 EXCEPTION WHEN undefined_table THEN
233 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
234 EXCEPTION WHEN undefined_table THEN
238 -- Update with a rename to avoid collisions
242 FROM reporter.report_folder
243 WHERE owner = src_usr
245 suffix := ' (' || src_usr || ')';
248 UPDATE reporter.report_folder
249 SET owner = dest_usr, name = name || suffix
250 WHERE id = renamable_row.id;
251 EXCEPTION WHEN unique_violation THEN
252 suffix := suffix || ' ';
258 EXCEPTION WHEN undefined_table THEN
263 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
264 EXCEPTION WHEN undefined_table THEN
269 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
270 EXCEPTION WHEN undefined_table THEN
274 -- Update with a rename to avoid collisions
278 FROM reporter.template_folder
279 WHERE owner = src_usr
281 suffix := ' (' || src_usr || ')';
284 UPDATE reporter.template_folder
285 SET owner = dest_usr, name = name || suffix
286 WHERE id = renamable_row.id;
287 EXCEPTION WHEN unique_violation THEN
288 suffix := suffix || ' ';
294 EXCEPTION WHEN undefined_table THEN
299 -- Update with a rename to avoid collisions
303 WHERE owner = src_usr
305 suffix := ' (' || src_usr || ')';
308 UPDATE vandelay.queue
309 SET owner = dest_usr, name = name || suffix
310 WHERE id = renamable_row.id;
311 EXCEPTION WHEN unique_violation THEN
312 suffix := suffix || ' ';
319 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
320 -- can access the information before deletion.
324 mailing_address = NULL,
325 billing_address = NULL