1 --Upgrade Script for 3.2.1 to 3.2.2
2 \set eg_version '''3.2.2'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.2.2', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1134', :eg_version);
8 CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer)
9 RETURNS SETOF metabib.flat_browse_entry_appearance
18 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
19 results_skipped INT := 0;
26 all_brecords BIGINT[];
27 all_arecords BIGINT[];
28 superpage_of_records BIGINT[];
33 unauthorized_entry RECORD;
35 IF count_up_from_zero THEN
42 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
45 -- b_tests supplies its own query_int operator, c_tests does not
46 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
48 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
50 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
51 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
53 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
55 b_tests := b_tests || search.calculate_visibility_attribute_test(
57 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
60 b_tests := b_tests || search.calculate_visibility_attribute_test(
62 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
66 IF context_locations THEN
67 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
68 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
71 OPEN curs NO SCROLL FOR EXECUTE query;
76 IF result_row.pivot_point IS NOT NULL THEN
77 RETURN NEXT result_row;
83 SELECT INTO unauthorized_entry *
84 FROM metabib.browse_entry_simple_heading_map mbeshm
85 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
86 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
87 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
88 WHERE mbeshm.entry = rec.id
89 AND ahf.heading_purpose = 'variant';
91 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
92 IF (unauthorized_entry.record IS NOT NULL) THEN
93 --unauthorized term belongs to an auth linked to a bib?
94 SELECT INTO all_arecords, result_row.sees, afields
95 ARRAY_AGG(DISTINCT abl.bib),
96 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
97 ARRAY_AGG(DISTINCT map.metabib_field)
98 FROM authority.bib_linking abl
99 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
100 map.authority_field = unauthorized_entry.atag
101 AND map.metabib_field = ANY(fields)
103 WHERE abl.authority = unauthorized_entry.record;
106 SELECT INTO all_arecords, result_row.sees, afields
107 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
108 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
109 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
111 FROM metabib.browse_entry_simple_heading_map mbeshm
112 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
113 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
114 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
115 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
116 ash.atag = map.authority_field
117 AND map.metabib_field = ANY(fields)
119 JOIN authority.control_set_authority_field acsaf ON (
120 map.authority_field = acsaf.id
122 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
123 WHERE mbeshm.entry = rec.id
124 AND ahf.heading_purpose = 'variant';
128 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
129 SELECT INTO all_brecords, result_row.authorities, bfields
130 ARRAY_AGG(DISTINCT source),
131 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
132 ARRAY_AGG(DISTINCT def)
133 FROM metabib.browse_entry_def_map
135 AND def = ANY(fields);
137 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
139 result_row.sources := 0;
140 result_row.asources := 0;
142 -- Bib-linked vis checking
143 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
145 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
146 FROM biblio.record_entry b
147 LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
148 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
150 acvac.vis_attr_vector @@ c_tests::query_int
151 OR b.vis_attr_vector @@ b_tests::query_int
154 result_row.accurate := TRUE;
158 -- Authority-linked vis checking
159 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
161 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
162 FROM biblio.record_entry b
163 LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
164 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
166 acvac.vis_attr_vector @@ c_tests::query_int
167 OR b.vis_attr_vector @@ b_tests::query_int
170 result_row.aaccurate := TRUE;
174 IF result_row.sources > 0 OR result_row.asources > 0 THEN
176 -- The function that calls this function needs row_number in order
177 -- to correctly order results from two different runs of this
179 result_row.row_number := row_number;
181 -- Now, if row_counter is still less than limit, return a row. If
182 -- not, but it is less than next_pivot_pos, continue on without
183 -- returning actual result rows until we find
184 -- that next pivot, and return it.
186 IF row_counter < result_limit THEN
187 result_row.browse_entry := rec.id;
188 result_row.value := rec.value;
190 RETURN NEXT result_row;
192 result_row.browse_entry := NULL;
193 result_row.authorities := NULL;
194 result_row.fields := NULL;
195 result_row.value := NULL;
196 result_row.sources := NULL;
197 result_row.sees := NULL;
198 result_row.accurate := NULL;
199 result_row.aaccurate := NULL;
200 result_row.pivot_point := rec.id;
202 IF row_counter >= next_pivot_pos THEN
203 RETURN NEXT result_row;
208 IF count_up_from_zero THEN
209 row_number := row_number + 1;
211 row_number := row_number - 1;
214 -- row_counter is different from row_number.
215 -- It simply counts up from zero so that we know when
216 -- we've reached our limit.
217 row_counter := row_counter + 1;
221 $f$ LANGUAGE plpgsql ROWS 10;
224 SELECT evergreen.upgrade_deps_block_check('1136', :eg_version);
226 -- update mods33 data entered by 1100 with a format of 'mods32'
227 -- harmless if you have not run 1100 yet
228 UPDATE config.metabib_field SET format = 'mods33' WHERE format = 'mods32' and id in (38, 39, 40, 41, 42, 43, 44, 46, 47, 48, 49, 50);
230 -- change the default format to 'mods33'
231 ALTER TABLE config.metabib_field ALTER COLUMN format SET DEFAULT 'mods33'::text;
235 SELECT evergreen.upgrade_deps_block_check('1137', :eg_version);
237 -- This is a placeholder for 1137 which will be a backported version of the
238 -- actor.usr_merge function for rel_3_1. This script does nothing for
239 -- rel_3_2 and later.
242 SELECT evergreen.upgrade_deps_block_check('1138', :eg_version);
244 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 $$
253 -- Bail if src_usr equals dest_usr because the result of merging a
254 -- user with itself is not what you want.
255 IF src_usr = dest_usr THEN
259 -- do some initial cleanup
260 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
261 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
262 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
266 DELETE FROM actor.card where usr = src_usr;
268 IF deactivate_cards THEN
269 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
271 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
276 DELETE FROM actor.usr_address WHERE usr = src_usr;
278 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
281 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
282 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
283 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
284 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
285 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
288 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
289 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
290 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
291 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
296 -- For each *_bucket table: transfer every bucket belonging to src_usr
297 -- into the custody of dest_usr.
299 -- In order to avoid colliding with an existing bucket owned by
300 -- the destination user, append the source user's id (in parenthesese)
301 -- to the name. If you still get a collision, add successive
302 -- spaces to the name and keep trying until you succeed.
306 FROM container.biblio_record_entry_bucket
307 WHERE owner = src_usr
309 suffix := ' (' || src_usr || ')';
312 UPDATE container.biblio_record_entry_bucket
313 SET owner = dest_usr, name = name || suffix
314 WHERE id = bucket_row.id;
315 EXCEPTION WHEN unique_violation THEN
316 suffix := suffix || ' ';
325 FROM container.call_number_bucket
326 WHERE owner = src_usr
328 suffix := ' (' || src_usr || ')';
331 UPDATE container.call_number_bucket
332 SET owner = dest_usr, name = name || suffix
333 WHERE id = bucket_row.id;
334 EXCEPTION WHEN unique_violation THEN
335 suffix := suffix || ' ';
344 FROM container.copy_bucket
345 WHERE owner = src_usr
347 suffix := ' (' || src_usr || ')';
350 UPDATE container.copy_bucket
351 SET owner = dest_usr, name = name || suffix
352 WHERE id = bucket_row.id;
353 EXCEPTION WHEN unique_violation THEN
354 suffix := suffix || ' ';
363 FROM container.user_bucket
364 WHERE owner = src_usr
366 suffix := ' (' || src_usr || ')';
369 UPDATE container.user_bucket
370 SET owner = dest_usr, name = name || suffix
371 WHERE id = bucket_row.id;
372 EXCEPTION WHEN unique_violation THEN
373 suffix := suffix || ' ';
380 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
383 -- transfer queues the same way we transfer buckets (see above)
387 WHERE owner = src_usr
389 suffix := ' (' || src_usr || ')';
392 UPDATE vandelay.queue
393 SET owner = dest_usr, name = name || suffix
394 WHERE id = queue_row.id;
395 EXCEPTION WHEN unique_violation THEN
396 suffix := suffix || ' ';
403 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
406 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
407 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
408 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
409 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
410 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
413 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
414 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
415 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
416 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
418 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
419 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
420 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
421 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
423 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
424 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
425 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
426 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
427 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
430 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
431 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
432 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
434 -- transfer picklists the same way we transfer buckets (see above)
438 WHERE owner = src_usr
440 suffix := ' (' || src_usr || ')';
444 SET owner = dest_usr, name = name || suffix
445 WHERE id = picklist_row.id;
446 EXCEPTION WHEN unique_violation THEN
447 suffix := suffix || ' ';
454 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
455 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
456 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
457 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
458 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
459 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
460 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
461 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
464 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
465 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
466 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
467 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
468 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
469 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
472 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
473 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
476 -- It's not uncommon to define the reporter schema in a replica
477 -- DB only, so don't assume these tables exist in the write DB.
479 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
480 EXCEPTION WHEN undefined_table THEN
484 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
485 EXCEPTION WHEN undefined_table THEN
489 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
490 EXCEPTION WHEN undefined_table THEN
494 -- transfer folders the same way we transfer buckets (see above)
497 FROM reporter.template_folder
498 WHERE owner = src_usr
500 suffix := ' (' || src_usr || ')';
503 UPDATE reporter.template_folder
504 SET owner = dest_usr, name = name || suffix
505 WHERE id = folder_row.id;
506 EXCEPTION WHEN unique_violation THEN
507 suffix := suffix || ' ';
513 EXCEPTION WHEN undefined_table THEN
517 -- transfer folders the same way we transfer buckets (see above)
520 FROM reporter.report_folder
521 WHERE owner = src_usr
523 suffix := ' (' || src_usr || ')';
526 UPDATE reporter.report_folder
527 SET owner = dest_usr, name = name || suffix
528 WHERE id = folder_row.id;
529 EXCEPTION WHEN unique_violation THEN
530 suffix := suffix || ' ';
536 EXCEPTION WHEN undefined_table THEN
540 -- transfer folders the same way we transfer buckets (see above)
543 FROM reporter.output_folder
544 WHERE owner = src_usr
546 suffix := ' (' || src_usr || ')';
549 UPDATE reporter.output_folder
550 SET owner = dest_usr, name = name || suffix
551 WHERE id = folder_row.id;
552 EXCEPTION WHEN unique_violation THEN
553 suffix := suffix || ' ';
559 EXCEPTION WHEN undefined_table THEN
563 -- propagate preferred name values from the source user to the
564 -- destination user, but only when values are not being replaced.
565 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
568 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
569 pref_first_given_name =
570 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
571 pref_second_given_name =
572 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
574 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
576 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
579 -- Copy and deduplicate name keywords
580 -- String -> array -> rows -> DISTINCT -> array -> string
581 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
582 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
583 UPDATE actor.usr SET name_keywords = (
585 SELECT DISTINCT UNNEST(
586 REGEXP_SPLIT_TO_ARRAY(
587 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
588 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
591 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
592 ) WHERE id = dest_usr;
594 -- Finally, delete the source user
595 PERFORM actor.usr_delete(src_usr,dest_usr);