3 INSERT INTO config.upgrade_log (version) VALUES ('XXXX');
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 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
29 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
31 -- Update with a rename to avoid collisions
37 suffix := ' (' || src_usr || ')';
41 SET owner = dest_usr, name = name || suffix
42 WHERE id = renamable_row.id;
43 EXCEPTION WHEN unique_violation THEN
44 suffix := suffix || ' ';
51 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
52 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
53 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
54 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
55 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
56 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
57 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
58 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
61 DELETE FROM action.circulation WHERE usr = src_usr;
62 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
63 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
64 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
65 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
66 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
67 DELETE FROM action.hold_request WHERE usr = src_usr;
68 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
69 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
70 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
71 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
72 DELETE FROM action.survey_response WHERE usr = src_usr;
73 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
74 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
77 DELETE FROM actor.card WHERE usr = src_usr;
78 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
79 DELETE FROM actor.usr_privacy_waiver WHERE usr = src_usr;
81 -- The following update is intended to avoid transient violations of a foreign
82 -- key constraint, whereby actor.usr_address references itself. It may not be
83 -- necessary, but it does no harm.
84 UPDATE actor.usr_address SET replaces = NULL
85 WHERE usr = src_usr AND replaces IS NOT NULL;
86 DELETE FROM actor.usr_address WHERE usr = src_usr;
87 DELETE FROM actor.usr_note WHERE usr = src_usr;
88 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
89 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
90 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
91 DELETE FROM actor.usr_setting WHERE usr = src_usr;
92 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
93 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
96 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
97 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
98 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
99 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
100 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
101 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
104 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
105 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
106 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
107 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
108 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
109 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
110 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
111 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
114 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
115 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
116 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
117 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
120 -- Update buckets with a rename to avoid collisions
123 FROM container.biblio_record_entry_bucket
124 WHERE owner = src_usr
126 suffix := ' (' || src_usr || ')';
129 UPDATE container.biblio_record_entry_bucket
130 SET owner = dest_usr, name = name || suffix
131 WHERE id = renamable_row.id;
132 EXCEPTION WHEN unique_violation THEN
133 suffix := suffix || ' ';
142 FROM container.call_number_bucket
143 WHERE owner = src_usr
145 suffix := ' (' || src_usr || ')';
148 UPDATE container.call_number_bucket
149 SET owner = dest_usr, name = name || suffix
150 WHERE id = renamable_row.id;
151 EXCEPTION WHEN unique_violation THEN
152 suffix := suffix || ' ';
161 FROM container.copy_bucket
162 WHERE owner = src_usr
164 suffix := ' (' || src_usr || ')';
167 UPDATE container.copy_bucket
168 SET owner = dest_usr, name = name || suffix
169 WHERE id = renamable_row.id;
170 EXCEPTION WHEN unique_violation THEN
171 suffix := suffix || ' ';
180 FROM container.user_bucket
181 WHERE owner = src_usr
183 suffix := ' (' || src_usr || ')';
186 UPDATE container.user_bucket
187 SET owner = dest_usr, name = name || suffix
188 WHERE id = renamable_row.id;
189 EXCEPTION WHEN unique_violation THEN
190 suffix := suffix || ' ';
197 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
200 DELETE FROM money.billable_xact WHERE usr = src_usr;
201 DELETE FROM money.collections_tracker WHERE usr = src_usr;
202 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
205 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
206 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
207 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
208 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
211 -- Update with a rename to avoid collisions
215 FROM reporter.output_folder
216 WHERE owner = src_usr
218 suffix := ' (' || src_usr || ')';
221 UPDATE reporter.output_folder
222 SET owner = dest_usr, name = name || suffix
223 WHERE id = renamable_row.id;
224 EXCEPTION WHEN unique_violation THEN
225 suffix := suffix || ' ';
231 EXCEPTION WHEN undefined_table THEN
236 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
237 EXCEPTION WHEN undefined_table THEN
241 -- Update with a rename to avoid collisions
245 FROM reporter.report_folder
246 WHERE owner = src_usr
248 suffix := ' (' || src_usr || ')';
251 UPDATE reporter.report_folder
252 SET owner = dest_usr, name = name || suffix
253 WHERE id = renamable_row.id;
254 EXCEPTION WHEN unique_violation THEN
255 suffix := suffix || ' ';
261 EXCEPTION WHEN undefined_table THEN
266 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
267 EXCEPTION WHEN undefined_table THEN
272 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
273 EXCEPTION WHEN undefined_table THEN
277 -- Update with a rename to avoid collisions
281 FROM reporter.template_folder
282 WHERE owner = src_usr
284 suffix := ' (' || src_usr || ')';
287 UPDATE reporter.template_folder
288 SET owner = dest_usr, name = name || suffix
289 WHERE id = renamable_row.id;
290 EXCEPTION WHEN unique_violation THEN
291 suffix := suffix || ' ';
297 EXCEPTION WHEN undefined_table THEN
302 -- Update with a rename to avoid collisions
306 WHERE owner = src_usr
308 suffix := ' (' || src_usr || ')';
311 UPDATE vandelay.queue
312 SET owner = dest_usr, name = name || suffix
313 WHERE id = renamable_row.id;
314 EXCEPTION WHEN unique_violation THEN
315 suffix := suffix || ' ';
322 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
324 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
325 -- can access the information before deletion.
329 mailing_address = NULL,
330 billing_address = NULL
336 COMMENT ON FUNCTION actor.usr_purge_data(INT, INT) IS $$
337 Finds rows dependent on a given row in actor.usr and either deletes them
338 or reassigns them to a different user.