3 INSERT INTO config.upgrade_log (version) VALUES ('0480'); -- phasefx
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;
24 mailing_address = NULL,
25 billing_address = NULL
29 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
30 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
31 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
32 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
33 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
34 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
35 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
37 -- Update with a rename to avoid collisions
43 suffix := ' (' || src_usr || ')';
47 SET owner = dest_usr, name = name || suffix
48 WHERE id = renamable_row.id;
49 EXCEPTION WHEN unique_violation THEN
50 suffix := suffix || ' ';
57 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
58 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
59 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
60 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
61 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
62 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
63 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
64 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
67 DELETE FROM action.circulation WHERE usr = src_usr;
68 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
69 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
70 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
71 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
72 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
73 DELETE FROM action.hold_request WHERE usr = src_usr;
74 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
75 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
76 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
77 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
78 DELETE FROM action.survey_response WHERE usr = src_usr;
79 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
82 DELETE FROM actor.card WHERE usr = src_usr;
83 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_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_note WHERE usr = src_usr;
92 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
93 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
94 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
95 DELETE FROM actor.usr_setting WHERE usr = src_usr;
96 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
97 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = 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 || ' ';