3 SELECT evergreen.upgrade_deps_block_check('1055', :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 -- do some initial cleanup
15 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
16 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
17 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
21 DELETE FROM actor.card where usr = src_usr;
23 IF deactivate_cards THEN
24 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
26 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
31 DELETE FROM actor.usr_address WHERE usr = src_usr;
33 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
36 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
37 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
38 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
39 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
40 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
43 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
44 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
45 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
46 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
51 -- For each *_bucket table: transfer every bucket belonging to src_usr
52 -- into the custody of dest_usr.
54 -- In order to avoid colliding with an existing bucket owned by
55 -- the destination user, append the source user's id (in parenthesese)
56 -- to the name. If you still get a collision, add successive
57 -- spaces to the name and keep trying until you succeed.
61 FROM container.biblio_record_entry_bucket
64 suffix := ' (' || src_usr || ')';
67 UPDATE container.biblio_record_entry_bucket
68 SET owner = dest_usr, name = name || suffix
69 WHERE id = bucket_row.id;
70 EXCEPTION WHEN unique_violation THEN
71 suffix := suffix || ' ';
80 FROM container.call_number_bucket
83 suffix := ' (' || src_usr || ')';
86 UPDATE container.call_number_bucket
87 SET owner = dest_usr, name = name || suffix
88 WHERE id = bucket_row.id;
89 EXCEPTION WHEN unique_violation THEN
90 suffix := suffix || ' ';
99 FROM container.copy_bucket
100 WHERE owner = src_usr
102 suffix := ' (' || src_usr || ')';
105 UPDATE container.copy_bucket
106 SET owner = dest_usr, name = name || suffix
107 WHERE id = bucket_row.id;
108 EXCEPTION WHEN unique_violation THEN
109 suffix := suffix || ' ';
118 FROM container.user_bucket
119 WHERE owner = src_usr
121 suffix := ' (' || src_usr || ')';
124 UPDATE container.user_bucket
125 SET owner = dest_usr, name = name || suffix
126 WHERE id = bucket_row.id;
127 EXCEPTION WHEN unique_violation THEN
128 suffix := suffix || ' ';
135 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
138 -- transfer queues the same way we transfer buckets (see above)
142 WHERE owner = src_usr
144 suffix := ' (' || src_usr || ')';
147 UPDATE vandelay.queue
148 SET owner = dest_usr, name = name || suffix
149 WHERE id = queue_row.id;
150 EXCEPTION WHEN unique_violation THEN
151 suffix := suffix || ' ';
159 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
160 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
161 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
162 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
163 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
166 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
167 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
168 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
169 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
171 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
172 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
173 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
174 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
176 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
177 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
178 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
179 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
180 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
183 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
184 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
186 -- transfer picklists the same way we transfer buckets (see above)
190 WHERE owner = src_usr
192 suffix := ' (' || src_usr || ')';
196 SET owner = dest_usr, name = name || suffix
197 WHERE id = picklist_row.id;
198 EXCEPTION WHEN unique_violation THEN
199 suffix := suffix || ' ';
206 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
207 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
208 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
209 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
210 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
211 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
212 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
213 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
216 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
217 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
218 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
219 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
220 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
221 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
224 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
225 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
228 -- It's not uncommon to define the reporter schema in a replica
229 -- DB only, so don't assume these tables exist in the write DB.
231 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
232 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 reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
242 EXCEPTION WHEN undefined_table THEN
246 -- transfer folders the same way we transfer buckets (see above)
249 FROM reporter.template_folder
250 WHERE owner = src_usr
252 suffix := ' (' || src_usr || ')';
255 UPDATE reporter.template_folder
256 SET owner = dest_usr, name = name || suffix
257 WHERE id = folder_row.id;
258 EXCEPTION WHEN unique_violation THEN
259 suffix := suffix || ' ';
265 EXCEPTION WHEN undefined_table THEN
269 -- transfer folders the same way we transfer buckets (see above)
272 FROM reporter.report_folder
273 WHERE owner = src_usr
275 suffix := ' (' || src_usr || ')';
278 UPDATE reporter.report_folder
279 SET owner = dest_usr, name = name || suffix
280 WHERE id = folder_row.id;
281 EXCEPTION WHEN unique_violation THEN
282 suffix := suffix || ' ';
288 EXCEPTION WHEN undefined_table THEN
292 -- transfer folders the same way we transfer buckets (see above)
295 FROM reporter.output_folder
296 WHERE owner = src_usr
298 suffix := ' (' || src_usr || ')';
301 UPDATE reporter.output_folder
302 SET owner = dest_usr, name = name || suffix
303 WHERE id = folder_row.id;
304 EXCEPTION WHEN unique_violation THEN
305 suffix := suffix || ' ';
311 EXCEPTION WHEN undefined_table THEN
315 -- Finally, delete the source user
316 DELETE FROM actor.usr WHERE id = src_usr;