1 --Upgrade Script for 2.12.5 to 2.12.6
2 \set eg_version '''2.12.6'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.12.6', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1055', :eg_version);
8 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 $$
17 -- do some initial cleanup
18 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
19 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
20 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
24 DELETE FROM actor.card where usr = src_usr;
26 IF deactivate_cards THEN
27 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
29 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
34 DELETE FROM actor.usr_address WHERE usr = src_usr;
36 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
39 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
40 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
41 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
42 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
43 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
46 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
47 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
48 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
49 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
54 -- For each *_bucket table: transfer every bucket belonging to src_usr
55 -- into the custody of dest_usr.
57 -- In order to avoid colliding with an existing bucket owned by
58 -- the destination user, append the source user's id (in parenthesese)
59 -- to the name. If you still get a collision, add successive
60 -- spaces to the name and keep trying until you succeed.
64 FROM container.biblio_record_entry_bucket
67 suffix := ' (' || src_usr || ')';
70 UPDATE container.biblio_record_entry_bucket
71 SET owner = dest_usr, name = name || suffix
72 WHERE id = bucket_row.id;
73 EXCEPTION WHEN unique_violation THEN
74 suffix := suffix || ' ';
83 FROM container.call_number_bucket
86 suffix := ' (' || src_usr || ')';
89 UPDATE container.call_number_bucket
90 SET owner = dest_usr, name = name || suffix
91 WHERE id = bucket_row.id;
92 EXCEPTION WHEN unique_violation THEN
93 suffix := suffix || ' ';
102 FROM container.copy_bucket
103 WHERE owner = src_usr
105 suffix := ' (' || src_usr || ')';
108 UPDATE container.copy_bucket
109 SET owner = dest_usr, name = name || suffix
110 WHERE id = bucket_row.id;
111 EXCEPTION WHEN unique_violation THEN
112 suffix := suffix || ' ';
121 FROM container.user_bucket
122 WHERE owner = src_usr
124 suffix := ' (' || src_usr || ')';
127 UPDATE container.user_bucket
128 SET owner = dest_usr, name = name || suffix
129 WHERE id = bucket_row.id;
130 EXCEPTION WHEN unique_violation THEN
131 suffix := suffix || ' ';
138 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
141 -- transfer queues the same way we transfer buckets (see above)
145 WHERE owner = src_usr
147 suffix := ' (' || src_usr || ')';
150 UPDATE vandelay.queue
151 SET owner = dest_usr, name = name || suffix
152 WHERE id = queue_row.id;
153 EXCEPTION WHEN unique_violation THEN
154 suffix := suffix || ' ';
162 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
163 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
164 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
165 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
166 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
169 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
170 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
171 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
172 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
174 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
175 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
176 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
177 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
179 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
180 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
181 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
182 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
183 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
186 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
187 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
189 -- transfer picklists the same way we transfer buckets (see above)
193 WHERE owner = src_usr
195 suffix := ' (' || src_usr || ')';
199 SET owner = dest_usr, name = name || suffix
200 WHERE id = picklist_row.id;
201 EXCEPTION WHEN unique_violation THEN
202 suffix := suffix || ' ';
209 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
210 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
211 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
212 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
213 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
214 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
215 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
216 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
219 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
220 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
221 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
222 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
223 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
224 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
227 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
228 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
231 -- It's not uncommon to define the reporter schema in a replica
232 -- DB only, so don't assume these tables exist in the write DB.
234 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
235 EXCEPTION WHEN undefined_table THEN
239 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
240 EXCEPTION WHEN undefined_table THEN
244 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
245 EXCEPTION WHEN undefined_table THEN
249 -- transfer folders the same way we transfer buckets (see above)
252 FROM reporter.template_folder
253 WHERE owner = src_usr
255 suffix := ' (' || src_usr || ')';
258 UPDATE reporter.template_folder
259 SET owner = dest_usr, name = name || suffix
260 WHERE id = folder_row.id;
261 EXCEPTION WHEN unique_violation THEN
262 suffix := suffix || ' ';
268 EXCEPTION WHEN undefined_table THEN
272 -- transfer folders the same way we transfer buckets (see above)
275 FROM reporter.report_folder
276 WHERE owner = src_usr
278 suffix := ' (' || src_usr || ')';
281 UPDATE reporter.report_folder
282 SET owner = dest_usr, name = name || suffix
283 WHERE id = folder_row.id;
284 EXCEPTION WHEN unique_violation THEN
285 suffix := suffix || ' ';
291 EXCEPTION WHEN undefined_table THEN
295 -- transfer folders the same way we transfer buckets (see above)
298 FROM reporter.output_folder
299 WHERE owner = src_usr
301 suffix := ' (' || src_usr || ')';
304 UPDATE reporter.output_folder
305 SET owner = dest_usr, name = name || suffix
306 WHERE id = folder_row.id;
307 EXCEPTION WHEN unique_violation THEN
308 suffix := suffix || ' ';
314 EXCEPTION WHEN undefined_table THEN
318 -- Finally, delete the source user
319 DELETE FROM actor.usr WHERE id = src_usr;