1 --Upgrade Script for 3.1.7 to 3.1.8
2 \set eg_version '''3.1.8'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.1.8', :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 function upgrade is only for rel_3_1. The next upgrade script 1138 in master/rel_3_2 is for future releases
238 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 $$
247 -- Bail if src_usr equals dest_usr because the result of merging a
248 -- user with itself is not what you want.
249 IF src_usr = dest_usr THEN
253 -- do some initial cleanup
254 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
255 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
256 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
260 DELETE FROM actor.card where usr = src_usr;
262 IF deactivate_cards THEN
263 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
265 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
270 DELETE FROM actor.usr_address WHERE usr = src_usr;
272 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
275 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
276 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
277 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
278 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
279 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
282 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
283 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
284 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
285 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
290 -- For each *_bucket table: transfer every bucket belonging to src_usr
291 -- into the custody of dest_usr.
293 -- In order to avoid colliding with an existing bucket owned by
294 -- the destination user, append the source user's id (in parenthesese)
295 -- to the name. If you still get a collision, add successive
296 -- spaces to the name and keep trying until you succeed.
300 FROM container.biblio_record_entry_bucket
301 WHERE owner = src_usr
303 suffix := ' (' || src_usr || ')';
306 UPDATE container.biblio_record_entry_bucket
307 SET owner = dest_usr, name = name || suffix
308 WHERE id = bucket_row.id;
309 EXCEPTION WHEN unique_violation THEN
310 suffix := suffix || ' ';
319 FROM container.call_number_bucket
320 WHERE owner = src_usr
322 suffix := ' (' || src_usr || ')';
325 UPDATE container.call_number_bucket
326 SET owner = dest_usr, name = name || suffix
327 WHERE id = bucket_row.id;
328 EXCEPTION WHEN unique_violation THEN
329 suffix := suffix || ' ';
338 FROM container.copy_bucket
339 WHERE owner = src_usr
341 suffix := ' (' || src_usr || ')';
344 UPDATE container.copy_bucket
345 SET owner = dest_usr, name = name || suffix
346 WHERE id = bucket_row.id;
347 EXCEPTION WHEN unique_violation THEN
348 suffix := suffix || ' ';
357 FROM container.user_bucket
358 WHERE owner = src_usr
360 suffix := ' (' || src_usr || ')';
363 UPDATE container.user_bucket
364 SET owner = dest_usr, name = name || suffix
365 WHERE id = bucket_row.id;
366 EXCEPTION WHEN unique_violation THEN
367 suffix := suffix || ' ';
374 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
377 -- transfer queues the same way we transfer buckets (see above)
381 WHERE owner = src_usr
383 suffix := ' (' || src_usr || ')';
386 UPDATE vandelay.queue
387 SET owner = dest_usr, name = name || suffix
388 WHERE id = queue_row.id;
389 EXCEPTION WHEN unique_violation THEN
390 suffix := suffix || ' ';
398 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
399 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
400 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
401 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
402 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
405 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
406 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
407 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
408 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
410 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
411 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
412 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
413 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
415 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
416 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
417 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
418 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
419 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
422 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
423 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
425 -- transfer picklists the same way we transfer buckets (see above)
429 WHERE owner = src_usr
431 suffix := ' (' || src_usr || ')';
435 SET owner = dest_usr, name = name || suffix
436 WHERE id = picklist_row.id;
437 EXCEPTION WHEN unique_violation THEN
438 suffix := suffix || ' ';
445 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
446 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
447 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
448 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
449 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
450 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
451 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
452 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
455 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
456 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
457 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
458 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
459 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
460 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
463 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
464 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
467 -- It's not uncommon to define the reporter schema in a replica
468 -- DB only, so don't assume these tables exist in the write DB.
470 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
471 EXCEPTION WHEN undefined_table THEN
475 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
476 EXCEPTION WHEN undefined_table THEN
480 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
481 EXCEPTION WHEN undefined_table THEN
485 -- transfer folders the same way we transfer buckets (see above)
488 FROM reporter.template_folder
489 WHERE owner = src_usr
491 suffix := ' (' || src_usr || ')';
494 UPDATE reporter.template_folder
495 SET owner = dest_usr, name = name || suffix
496 WHERE id = folder_row.id;
497 EXCEPTION WHEN unique_violation THEN
498 suffix := suffix || ' ';
504 EXCEPTION WHEN undefined_table THEN
508 -- transfer folders the same way we transfer buckets (see above)
511 FROM reporter.report_folder
512 WHERE owner = src_usr
514 suffix := ' (' || src_usr || ')';
517 UPDATE reporter.report_folder
518 SET owner = dest_usr, name = name || suffix
519 WHERE id = folder_row.id;
520 EXCEPTION WHEN unique_violation THEN
521 suffix := suffix || ' ';
527 EXCEPTION WHEN undefined_table THEN
531 -- transfer folders the same way we transfer buckets (see above)
534 FROM reporter.output_folder
535 WHERE owner = src_usr
537 suffix := ' (' || src_usr || ')';
540 UPDATE reporter.output_folder
541 SET owner = dest_usr, name = name || suffix
542 WHERE id = folder_row.id;
543 EXCEPTION WHEN unique_violation THEN
544 suffix := suffix || ' ';
550 EXCEPTION WHEN undefined_table THEN
554 -- Finally, delete the source user
555 PERFORM actor.usr_delete(src_usr,dest_usr);