1 --Upgrade Script for 3.0.11 to 3.0.12
2 \set eg_version '''3.0.12'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.0.12', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1130', :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 -- Bail if src_usr equals dest_usr because the result of merging a
18 -- user with itself is not what you want.
19 IF src_usr = dest_usr THEN
23 -- do some initial cleanup
24 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
25 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
26 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
30 DELETE FROM actor.card where usr = src_usr;
32 IF deactivate_cards THEN
33 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
35 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
40 DELETE FROM actor.usr_address WHERE usr = src_usr;
42 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
45 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
46 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
47 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
48 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
49 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
52 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
53 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
54 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
55 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
60 -- For each *_bucket table: transfer every bucket belonging to src_usr
61 -- into the custody of dest_usr.
63 -- In order to avoid colliding with an existing bucket owned by
64 -- the destination user, append the source user's id (in parenthesese)
65 -- to the name. If you still get a collision, add successive
66 -- spaces to the name and keep trying until you succeed.
70 FROM container.biblio_record_entry_bucket
73 suffix := ' (' || src_usr || ')';
76 UPDATE container.biblio_record_entry_bucket
77 SET owner = dest_usr, name = name || suffix
78 WHERE id = bucket_row.id;
79 EXCEPTION WHEN unique_violation THEN
80 suffix := suffix || ' ';
89 FROM container.call_number_bucket
92 suffix := ' (' || src_usr || ')';
95 UPDATE container.call_number_bucket
96 SET owner = dest_usr, name = name || suffix
97 WHERE id = bucket_row.id;
98 EXCEPTION WHEN unique_violation THEN
99 suffix := suffix || ' ';
108 FROM container.copy_bucket
109 WHERE owner = src_usr
111 suffix := ' (' || src_usr || ')';
114 UPDATE container.copy_bucket
115 SET owner = dest_usr, name = name || suffix
116 WHERE id = bucket_row.id;
117 EXCEPTION WHEN unique_violation THEN
118 suffix := suffix || ' ';
127 FROM container.user_bucket
128 WHERE owner = src_usr
130 suffix := ' (' || src_usr || ')';
133 UPDATE container.user_bucket
134 SET owner = dest_usr, name = name || suffix
135 WHERE id = bucket_row.id;
136 EXCEPTION WHEN unique_violation THEN
137 suffix := suffix || ' ';
144 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
147 -- transfer queues the same way we transfer buckets (see above)
151 WHERE owner = src_usr
153 suffix := ' (' || src_usr || ')';
156 UPDATE vandelay.queue
157 SET owner = dest_usr, name = name || suffix
158 WHERE id = queue_row.id;
159 EXCEPTION WHEN unique_violation THEN
160 suffix := suffix || ' ';
168 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
169 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
170 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
171 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
172 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
175 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
176 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
177 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
178 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
180 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
181 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
182 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
183 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
185 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
186 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
187 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
188 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
189 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
192 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
193 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = 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 -- Finally, delete the source user
325 DELETE FROM actor.usr WHERE id = src_usr;
332 SELECT evergreen.upgrade_deps_block_check('1133', :eg_version);
334 \qecho Applying a unique constraint to action.transit_copy. This will
335 \qecho only effect newly created transits. Admins are encouraged to manually
336 \qecho remove any existing duplicate transits by applying values for cancel_time
337 \qecho or dest_recv_time, or by deleting the offending transits. Below is a
338 \qecho query to locate duplicate transits. Note dupes may exist accross
339 \qecho parent (action.transit_copy) and child tables (action.hold_transit_copy,
340 \qecho action.reservation_transit_copy)
342 \qecho WITH dupe_transits AS (
343 \qecho SELECT COUNT(*), target_copy FROM action.transit_copy
344 \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL
345 \qecho GROUP BY 2 HAVING COUNT(*) > 1
346 \qecho ) SELECT atc.*
347 \qecho FROM dupe_transits
348 \qecho JOIN action.transit_copy atc USING (target_copy)
349 \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
353 Unique indexes are not inherited by child tables, so they will not prevent
354 duplicate inserts on action.transit_copy and action.hold_transit_copy,
355 for example. Use check constraints instead to enforce unique-per-copy
356 transits accross all transit types.
359 -- Create an index for speedy check constraint lookups.
360 CREATE INDEX active_transit_for_copy
361 ON action.transit_copy (target_copy)
362 WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
364 -- Check for duplicate transits across all transit types
365 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
366 RETURNS TRIGGER AS $func$
368 PERFORM * FROM action.transit_copy
369 WHERE target_copy = NEW.target_copy
370 AND dest_recv_time IS NULL
371 AND cancel_time IS NULL;
373 RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
377 $func$ LANGUAGE PLPGSQL STABLE;
379 -- Apply constraint to all transit tables
380 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
381 AFTER INSERT ON action.transit_copy
382 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
384 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
385 AFTER INSERT ON action.hold_transit_copy
386 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
388 CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check
389 AFTER INSERT ON action.reservation_transit_copy
390 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
394 DROP TRIGGER transit_copy_is_unique_check ON action.transit_copy;
395 DROP TRIGGER hold_transit_copy_is_unique_check ON action.hold_transit_copy;
396 DROP TRIGGER reservation_transit_copy_is_unique_check ON action.reservation_transit_copy;
397 DROP INDEX action.active_transit_for_copy;