3 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
5 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
14 -- Bail if src_usr equals dest_usr because the result of merging a
15 -- user with itself probably not what you want.
16 IF src_usr = dest_usr THEN
20 -- do some initial cleanup
21 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
22 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
23 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
27 DELETE FROM actor.card where usr = src_usr;
29 IF deactivate_cards THEN
30 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
32 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
37 DELETE FROM actor.usr_address WHERE usr = src_usr;
39 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
42 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
43 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
44 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
45 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
46 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
49 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
50 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
51 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
52 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
57 -- For each *_bucket table: transfer every bucket belonging to src_usr
58 -- into the custody of dest_usr.
60 -- In order to avoid colliding with an existing bucket owned by
61 -- the destination user, append the source user's id (in parenthesese)
62 -- to the name. If you still get a collision, add successive
63 -- spaces to the name and keep trying until you succeed.
67 FROM container.biblio_record_entry_bucket
70 suffix := ' (' || src_usr || ')';
73 UPDATE container.biblio_record_entry_bucket
74 SET owner = dest_usr, name = name || suffix
75 WHERE id = bucket_row.id;
76 EXCEPTION WHEN unique_violation THEN
77 suffix := suffix || ' ';
86 FROM container.call_number_bucket
89 suffix := ' (' || src_usr || ')';
92 UPDATE container.call_number_bucket
93 SET owner = dest_usr, name = name || suffix
94 WHERE id = bucket_row.id;
95 EXCEPTION WHEN unique_violation THEN
96 suffix := suffix || ' ';
105 FROM container.copy_bucket
106 WHERE owner = src_usr
108 suffix := ' (' || src_usr || ')';
111 UPDATE container.copy_bucket
112 SET owner = dest_usr, name = name || suffix
113 WHERE id = bucket_row.id;
114 EXCEPTION WHEN unique_violation THEN
115 suffix := suffix || ' ';
124 FROM container.user_bucket
125 WHERE owner = src_usr
127 suffix := ' (' || src_usr || ')';
130 UPDATE container.user_bucket
131 SET owner = dest_usr, name = name || suffix
132 WHERE id = bucket_row.id;
133 EXCEPTION WHEN unique_violation THEN
134 suffix := suffix || ' ';
141 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
144 -- transfer queues the same way we transfer buckets (see above)
148 WHERE owner = src_usr
150 suffix := ' (' || src_usr || ')';
153 UPDATE vandelay.queue
154 SET owner = dest_usr, name = name || suffix
155 WHERE id = queue_row.id;
156 EXCEPTION WHEN unique_violation THEN
157 suffix := suffix || ' ';
165 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
166 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
167 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
168 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
169 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
172 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
173 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
174 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
175 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
177 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
178 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
179 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
180 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
182 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
183 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
184 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
185 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
186 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
189 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
190 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
192 -- transfer picklists the same way we transfer buckets (see above)
196 WHERE owner = src_usr
198 suffix := ' (' || src_usr || ')';
202 SET owner = dest_usr, name = name || suffix
203 WHERE id = picklist_row.id;
204 EXCEPTION WHEN unique_violation THEN
205 suffix := suffix || ' ';
212 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
213 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
214 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
215 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
216 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
217 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
218 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
219 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
222 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
223 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
224 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
225 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
226 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
227 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
230 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
231 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
234 -- It's not uncommon to define the reporter schema in a replica
235 -- DB only, so don't assume these tables exist in the write DB.
237 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
238 EXCEPTION WHEN undefined_table THEN
242 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
243 EXCEPTION WHEN undefined_table THEN
247 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
248 EXCEPTION WHEN undefined_table THEN
252 -- transfer folders the same way we transfer buckets (see above)
255 FROM reporter.template_folder
256 WHERE owner = src_usr
258 suffix := ' (' || src_usr || ')';
261 UPDATE reporter.template_folder
262 SET owner = dest_usr, name = name || suffix
263 WHERE id = folder_row.id;
264 EXCEPTION WHEN unique_violation THEN
265 suffix := suffix || ' ';
271 EXCEPTION WHEN undefined_table THEN
275 -- transfer folders the same way we transfer buckets (see above)
278 FROM reporter.report_folder
279 WHERE owner = src_usr
281 suffix := ' (' || src_usr || ')';
284 UPDATE reporter.report_folder
285 SET owner = dest_usr, name = name || suffix
286 WHERE id = folder_row.id;
287 EXCEPTION WHEN unique_violation THEN
288 suffix := suffix || ' ';
294 EXCEPTION WHEN undefined_table THEN
298 -- transfer folders the same way we transfer buckets (see above)
301 FROM reporter.output_folder
302 WHERE owner = src_usr
304 suffix := ' (' || src_usr || ')';
307 UPDATE reporter.output_folder
308 SET owner = dest_usr, name = name || suffix
309 WHERE id = folder_row.id;
310 EXCEPTION WHEN unique_violation THEN
311 suffix := suffix || ' ';
317 EXCEPTION WHEN undefined_table THEN
321 -- Finally, delete the source user
322 DELETE FROM actor.usr WHERE id = src_usr;