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 is 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 || ' ';
164 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
167 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
168 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
169 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
170 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
171 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
174 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
175 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
176 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
177 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
179 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
180 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
181 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
182 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
184 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
185 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
186 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
187 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
188 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
191 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
192 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
193 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
195 -- transfer picklists the same way we transfer buckets (see above)
199 WHERE owner = src_usr
201 suffix := ' (' || src_usr || ')';
205 SET owner = dest_usr, name = name || suffix
206 WHERE id = picklist_row.id;
207 EXCEPTION WHEN unique_violation THEN
208 suffix := suffix || ' ';
215 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
216 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
217 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
218 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
219 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
220 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
221 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
222 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
225 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
226 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
227 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
228 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
229 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
230 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
233 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
234 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
237 -- It's not uncommon to define the reporter schema in a replica
238 -- DB only, so don't assume these tables exist in the write DB.
240 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
241 EXCEPTION WHEN undefined_table THEN
245 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
246 EXCEPTION WHEN undefined_table THEN
250 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
251 EXCEPTION WHEN undefined_table THEN
255 -- transfer folders the same way we transfer buckets (see above)
258 FROM reporter.template_folder
259 WHERE owner = src_usr
261 suffix := ' (' || src_usr || ')';
264 UPDATE reporter.template_folder
265 SET owner = dest_usr, name = name || suffix
266 WHERE id = folder_row.id;
267 EXCEPTION WHEN unique_violation THEN
268 suffix := suffix || ' ';
274 EXCEPTION WHEN undefined_table THEN
278 -- transfer folders the same way we transfer buckets (see above)
281 FROM reporter.report_folder
282 WHERE owner = src_usr
284 suffix := ' (' || src_usr || ')';
287 UPDATE reporter.report_folder
288 SET owner = dest_usr, name = name || suffix
289 WHERE id = folder_row.id;
290 EXCEPTION WHEN unique_violation THEN
291 suffix := suffix || ' ';
297 EXCEPTION WHEN undefined_table THEN
301 -- transfer folders the same way we transfer buckets (see above)
304 FROM reporter.output_folder
305 WHERE owner = src_usr
307 suffix := ' (' || src_usr || ')';
310 UPDATE reporter.output_folder
311 SET owner = dest_usr, name = name || suffix
312 WHERE id = folder_row.id;
313 EXCEPTION WHEN unique_violation THEN
314 suffix := suffix || ' ';
320 EXCEPTION WHEN undefined_table THEN
324 -- propagate preferred name values from the source user to the
325 -- destination user, but only when values are not being replaced.
326 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
329 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
330 pref_first_given_name =
331 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
332 pref_second_given_name =
333 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
335 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
337 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
340 -- Copy and deduplicate name keywords
341 -- String -> array -> rows -> DISTINCT -> array -> string
342 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
343 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
344 UPDATE actor.usr SET name_keywords = (
346 SELECT DISTINCT UNNEST(
347 REGEXP_SPLIT_TO_ARRAY(
348 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
349 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
352 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
353 ) WHERE id = dest_usr;
355 -- Finally, delete the source user
356 PERFORM actor.usr_delete(src_usr,dest_usr);