3 INSERT INTO config.upgrade_log (version) VALUES ('0278'); -- Scott McKellar
5 CREATE OR REPLACE FUNCTION actor.usr_merge(
10 deactivate_cards BOOLEAN
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;
176 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
177 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
178 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
179 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
181 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
182 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
183 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
184 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
185 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
188 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
189 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
191 -- transfer picklists the same way we transfer buckets (see above)
195 WHERE owner = src_usr
197 suffix := ' (' || src_usr || ')';
201 SET owner = dest_usr, name = name || suffix
202 WHERE id = picklist_row.id;
203 EXCEPTION WHEN unique_violation THEN
204 suffix := suffix || ' ';
211 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
212 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
213 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
214 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
215 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
216 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
217 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
218 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
221 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
222 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
223 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
224 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
225 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
226 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
229 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
230 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
233 -- It's not uncommon to define the reporter schema in a replica
234 -- DB only, so don't assume these tables exist in the write DB.
236 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
237 EXCEPTION WHEN undefined_table THEN
241 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
242 EXCEPTION WHEN undefined_table THEN
246 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
247 EXCEPTION WHEN undefined_table THEN
251 -- transfer folders the same way we transfer buckets (see above)
254 FROM reporter.template_folder
255 WHERE owner = src_usr
257 suffix := ' (' || src_usr || ')';
260 UPDATE reporter.template_folder
261 SET owner = dest_usr, name = name || suffix
262 WHERE id = folder_row.id;
263 EXCEPTION WHEN unique_violation THEN
264 suffix := suffix || ' ';
270 EXCEPTION WHEN undefined_table THEN
274 -- transfer folders the same way we transfer buckets (see above)
277 FROM reporter.report_folder
278 WHERE owner = src_usr
280 suffix := ' (' || src_usr || ')';
283 UPDATE reporter.report_folder
284 SET owner = dest_usr, name = name || suffix
285 WHERE id = folder_row.id;
286 EXCEPTION WHEN unique_violation THEN
287 suffix := suffix || ' ';
293 EXCEPTION WHEN undefined_table THEN
297 -- transfer folders the same way we transfer buckets (see above)
300 FROM reporter.output_folder
301 WHERE owner = src_usr
303 suffix := ' (' || src_usr || ')';
306 UPDATE reporter.output_folder
307 SET owner = dest_usr, name = name || suffix
308 WHERE id = folder_row.id;
309 EXCEPTION WHEN unique_violation THEN
310 suffix := suffix || ' ';
316 EXCEPTION WHEN undefined_table THEN
320 -- Finally, delete the source user
321 DELETE FROM actor.usr WHERE id = src_usr;