1 --Upgrade Script for 2.1 to 2.2-alpha1
3 -- DROP objects that might have existed from a prior run of 0526
5 DROP TABLE IF EXISTS config.db_patch_dependencies;
6 ALTER TABLE config.upgrade_log DROP COLUMN applied_to;
7 DROP FUNCTION evergreen.upgrade_list_applied_deprecates(TEXT);
8 DROP FUNCTION evergreen.upgrade_list_applied_supersedes(TEXT);
11 INSERT INTO config.upgrade_log (version) VALUES ('2.2-alpha1');
13 INSERT INTO config.upgrade_log (version) VALUES ('0526'); --miker
15 CREATE TABLE config.db_patch_dependencies (
16 db_patch TEXT PRIMARY KEY,
21 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
27 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
29 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
35 CREATE TRIGGER no_overlapping_sups
36 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
37 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
39 CREATE TRIGGER no_overlapping_deps
40 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
41 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
43 ALTER TABLE config.upgrade_log
44 ADD COLUMN applied_to TEXT;
46 -- Provide a named type for patching functions
47 CREATE TYPE evergreen.patch AS (patch TEXT);
49 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
50 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
51 SELECT DISTINCT l.version
52 FROM config.upgrade_log l
53 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
57 -- List applied db patches that are superseded by (and block the application of) my_db_patch
58 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
59 SELECT DISTINCT l.version
60 FROM config.upgrade_log l
61 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
65 -- List applied db patches that deprecates (and block the application of) my_db_patch
66 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$
68 FROM config.db_patch_dependencies
69 WHERE ARRAY[$1]::TEXT[] && deprecates
72 -- List applied db patches that supersedes (and block the application of) my_db_patch
73 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$
75 FROM config.db_patch_dependencies
76 WHERE ARRAY[$1]::TEXT[] && supersedes
79 -- Make sure that no deprecated or superseded db patches are currently applied
80 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
82 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
84 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
86 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
88 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
91 -- Raise an exception if there are, in fact, dep/sup confilct
92 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
97 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
98 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
99 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
101 Upgrade script % can not be applied:
102 applied deprecated scripts %
103 applied superseded scripts %
107 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
108 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
109 evergreen.upgrade_list_applied_deprecated(my_db_patch),
110 evergreen.upgrade_list_applied_superseded(my_db_patch);
113 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
118 -- Evergreen DB patch 0536.schema.lazy_circ-barcode_lookup.sql
120 -- FIXME: insert description of change, if needed
123 -- check whether patch can be applied
124 INSERT INTO config.upgrade_log (version) VALUES ('0536');
126 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype) VALUES ( 'circ.staff_client.actor_on_checkout', 'Load patron from Checkout', 'When scanning barcodes into Checkout auto-detect if a new patron barcode is scanned and auto-load the new patron.', 'bool');
128 CREATE TABLE config.barcode_completion (
129 id SERIAL PRIMARY KEY,
130 active BOOL NOT NULL DEFAULT true,
131 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
134 length INT NOT NULL DEFAULT 0,
136 padding_end BOOL NOT NULL DEFAULT false,
137 asset BOOL NOT NULL DEFAULT true,
138 actor BOOL NOT NULL DEFAULT true
141 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
143 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
148 asset_barcodes TEXT[];
149 actor_barcodes TEXT[];
150 do_asset BOOL = false;
151 do_serial BOOL = false;
152 do_booking BOOL = false;
153 do_actor BOOL = false;
154 completion_set config.barcode_completion%ROWTYPE;
157 IF position('asset' in type) > 0 THEN
160 IF position('serial' in type) > 0 THEN
163 IF position('booking' in type) > 0 THEN
166 IF do_asset OR do_serial OR do_booking THEN
167 asset_barcodes = asset_barcodes || in_barcode;
169 IF position('actor' in type) > 0 THEN
171 actor_barcodes = actor_barcodes || in_barcode;
174 barcode_len := length(in_barcode);
176 FOR completion_set IN
177 SELECT * FROM config.barcode_completion
179 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
181 IF completion_set.prefix IS NULL THEN
182 completion_set.prefix := '';
184 IF completion_set.suffix IS NULL THEN
185 completion_set.suffix := '';
187 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
188 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
190 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
191 IF completion_len >= barcode_len THEN
192 IF completion_set.padding_end THEN
193 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
195 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
197 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
200 IF completion_set.actor THEN
201 actor_barcodes = actor_barcodes || cur_barcode;
203 IF completion_set.asset THEN
204 asset_barcodes = asset_barcodes || cur_barcode;
208 IF do_asset AND do_serial THEN
209 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
210 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
212 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
214 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
217 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
220 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE c.barcode = ANY(actor_barcodes) AND c.active AND NOT u.deleted ORDER BY usr;
226 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
227 Given user input, find an appropriate barcode in the proper class.
229 Will add prefix/suffix information to do so, and return all results.
234 INSERT INTO config.upgrade_log (version) VALUES ('0537'); --miker
236 DROP FUNCTION evergreen.upgrade_deps_block_check(text,text);
237 DROP FUNCTION evergreen.upgrade_verify_no_dep_conflicts(text);
238 DROP FUNCTION evergreen.upgrade_list_applied_deprecated(text);
239 DROP FUNCTION evergreen.upgrade_list_applied_superseded(text);
241 -- List applied db patches that deprecates (and block the application of) my_db_patch
242 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
244 FROM config.db_patch_dependencies
245 WHERE ARRAY[$1]::TEXT[] && deprecates
248 -- List applied db patches that supersedes (and block the application of) my_db_patch
249 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
251 FROM config.db_patch_dependencies
252 WHERE ARRAY[$1]::TEXT[] && supersedes
255 -- Make sure that no deprecated or superseded db patches are currently applied
256 CREATE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
258 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
260 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
262 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
264 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
267 -- Raise an exception if there are, in fact, dep/sup confilct
268 CREATE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
270 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
272 Upgrade script % can not be applied:
273 applied deprecated scripts %
274 applied superseded scripts %
278 ARRAY_ACCUM(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
279 ARRAY_ACCUM(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
280 evergreen.upgrade_list_applied_deprecated(my_db_patch),
281 evergreen.upgrade_list_applied_superseded(my_db_patch);
284 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
290 INSERT INTO config.upgrade_log (version) VALUES ('0544');
292 INSERT INTO config.usr_setting_type
293 ( name, opac_visible, label, description, datatype) VALUES
294 ( 'circ.collections.exempt',
296 oils_i18n_gettext('circ.collections.exempt', 'Collections: Exempt', 'cust', 'description'),
297 oils_i18n_gettext('circ.collections.exempt', 'User is exempt from collections tracking/processing', 'cust', 'description'),
303 SELECT evergreen.upgrade_deps_block_check('0545', :eg_version);
305 INSERT INTO permission.perm_list VALUES
306 (507, 'ABORT_TRANSIT_ON_LOST', oils_i18n_gettext(507, 'Allows a user to abort a transit on a copy with status of LOST', 'ppl', 'description')),
307 (508, 'ABORT_TRANSIT_ON_MISSING', oils_i18n_gettext(508, 'Allows a user to abort a transit on a copy with status of MISSING', 'ppl', 'description'));
309 --- stock Circulation Administrator group
311 INSERT INTO permission.grp_perm_map ( grp, perm, depth, grantable )
317 FROM permission.perm_list
318 WHERE code in ('ABORT_TRANSIT_ON_LOST', 'ABORT_TRANSIT_ON_MISSING');
320 -- Evergreen DB patch 0546.schema.sip_statcats.sql
323 -- check whether patch can be applied
324 SELECT evergreen.upgrade_deps_block_check('0546', :eg_version);
326 CREATE TABLE actor.stat_cat_sip_fields (
327 field CHAR(2) PRIMARY KEY,
329 one_only BOOL NOT NULL DEFAULT FALSE
331 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
332 Actor Statistical Category SIP Fields
334 Contains the list of valid SIP Field identifiers for
335 Statistical Categories.
337 ALTER TABLE actor.stat_cat
338 ADD COLUMN sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
339 ADD COLUMN sip_format TEXT;
341 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
343 sipfield actor.stat_cat_sip_fields%ROWTYPE;
346 IF NEW.sip_field IS NOT NULL THEN
347 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
348 IF sipfield.one_only THEN
349 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
350 IF use_count > 0 THEN
351 RAISE EXCEPTION 'Sip field cannot be used twice';
357 $func$ LANGUAGE PLPGSQL;
359 CREATE TRIGGER actor_stat_cat_sip_update_trigger
360 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
361 EXECUTE PROCEDURE actor.stat_cat_check();
363 CREATE TABLE asset.stat_cat_sip_fields (
364 field CHAR(2) PRIMARY KEY,
366 one_only BOOL NOT NULL DEFAULT FALSE
368 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
369 Asset Statistical Category SIP Fields
371 Contains the list of valid SIP Field identifiers for
372 Statistical Categories.
375 ALTER TABLE asset.stat_cat
376 ADD COLUMN sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
377 ADD COLUMN sip_format TEXT;
379 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
381 sipfield asset.stat_cat_sip_fields%ROWTYPE;
384 IF NEW.sip_field IS NOT NULL THEN
385 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
386 IF sipfield.one_only THEN
387 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
388 IF use_count > 0 THEN
389 RAISE EXCEPTION 'Sip field cannot be used twice';
395 $func$ LANGUAGE PLPGSQL;
397 CREATE TRIGGER asset_stat_cat_sip_update_trigger
398 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
399 EXECUTE PROCEDURE asset.stat_cat_check();
403 SELECT evergreen.upgrade_deps_block_check('0548', :eg_version); -- dbwells
405 \qecho This redoes the original part 1 of 0547 which did not apply to rel_2_1,
406 \qecho and is being added for the sake of clarity
408 -- delete errant inserts from 0545 (group 4 is NOT the circulation admin group)
409 DELETE FROM permission.grp_perm_map WHERE grp = 4 AND perm IN (
410 SELECT id FROM permission.perm_list
411 WHERE code in ('ABORT_TRANSIT_ON_LOST', 'ABORT_TRANSIT_ON_MISSING')
414 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
416 pgt.id, perm.id, aout.depth, TRUE
418 permission.grp_tree pgt,
419 permission.perm_list perm,
420 actor.org_unit_type aout
422 pgt.name = 'Circulation Administrator' AND
423 aout.name = 'Consortium' AND
425 'ABORT_TRANSIT_ON_LOST',
426 'ABORT_TRANSIT_ON_MISSING'
429 FROM permission.grp_perm_map AS map
432 AND map.perm = perm.id
435 -- Evergreen DB patch XXXX.data.transit-checkin-interval.sql
437 -- New org unit setting "circ.transit.min_checkin_interval"
438 -- New TRANSIT_CHECKIN_INTERVAL_BLOCK.override permission
442 -- check whether patch can be applied
443 SELECT evergreen.upgrade_deps_block_check('0549', :eg_version);
445 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
446 'circ.transit.min_checkin_interval',
448 'circ.transit.min_checkin_interval',
449 'Circ: Minimum Transit Checkin Interval',
454 'circ.transit.min_checkin_interval',
455 'In-Transit items checked in this close to the transit start time will be prevented from checking in',
462 INSERT INTO permission.perm_list ( id, code, description ) VALUES (
464 'TRANSIT_CHECKIN_INTERVAL_BLOCK.override',
467 'Allows a user to override the TRANSIT_CHECKIN_INTERVAL_BLOCK event',
473 -- add the perm to the default circ admin group
474 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
476 pgt.id, perm.id, aout.depth, TRUE
478 permission.grp_tree pgt,
479 permission.perm_list perm,
480 actor.org_unit_type aout
482 pgt.name = 'Circulation Administrator' AND
483 aout.name = 'System' AND
484 perm.code IN ( 'TRANSIT_CHECKIN_INTERVAL_BLOCK.override' );
487 -- check whether patch can be applied
488 SELECT evergreen.upgrade_deps_block_check('0550', :eg_version);
490 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
491 'org.patron_opt_boundary',
493 'org.patron_opt_boundary',
494 'Circ: Patron Opt-In Boundary',
499 'org.patron_opt_boundary',
500 'This determines at which depth above which patrons must be opted in, and below which patrons will be assumed to be opted in.',
507 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
508 'org.patron_opt_default',
510 'org.patron_opt_default',
511 'Circ: Patron Opt-In Default',
516 'org.patron_opt_default',
517 'This is the default depth at which a patron is opted in; it is calculated as an org unit relative to the current workstation.',
524 -- Evergreen DB patch 0562.schema.copy_active_date.sql
529 -- check whether patch can be applied
530 SELECT evergreen.upgrade_deps_block_check('0562', :eg_version);
532 ALTER TABLE asset.copy
533 ADD COLUMN active_date TIMESTAMP WITH TIME ZONE;
535 ALTER TABLE auditor.asset_copy_history
536 ADD COLUMN active_date TIMESTAMP WITH TIME ZONE;
538 ALTER TABLE auditor.serial_unit_history
539 ADD COLUMN active_date TIMESTAMP WITH TIME ZONE;
541 ALTER TABLE config.copy_status
542 ADD COLUMN copy_active BOOL NOT NULL DEFAULT FALSE;
544 ALTER TABLE config.circ_matrix_weights
545 ADD COLUMN item_age NUMERIC(6,2) NOT NULL DEFAULT 0.0;
547 ALTER TABLE config.hold_matrix_weights
548 ADD COLUMN item_age NUMERIC(6,2) NOT NULL DEFAULT 0.0;
550 -- The two defaults above were to stop erroring on NOT NULL
552 ALTER TABLE config.circ_matrix_weights
553 ALTER COLUMN item_age DROP DEFAULT;
555 ALTER TABLE config.hold_matrix_weights
556 ALTER COLUMN item_age DROP DEFAULT;
558 ALTER TABLE config.circ_matrix_matchpoint
559 ADD COLUMN item_age INTERVAL;
561 ALTER TABLE config.hold_matrix_matchpoint
562 ADD COLUMN item_age INTERVAL;
564 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
565 RETURNS TRIGGER AS $$
567 IF NEW.status <> OLD.status THEN
568 NEW.status_changed_time := now();
569 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
570 NEW.active_date := now();
577 CREATE OR REPLACE FUNCTION asset.acp_created()
578 RETURNS TRIGGER AS $$
580 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
581 NEW.active_date := now();
583 IF NEW.status_changed_time IS NULL THEN
584 NEW.status_changed_time := now();
590 CREATE TRIGGER acp_created_trig
591 BEFORE INSERT ON asset.copy
592 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
594 CREATE TRIGGER sunit_created_trig
595 BEFORE INSERT ON serial.unit
596 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
598 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
601 user_object actor.usr%ROWTYPE;
602 age_protect_object config.rule_age_hold_protect%ROWTYPE;
603 standing_penalty config.standing_penalty%ROWTYPE;
604 transit_range_ou_type actor.org_unit_type%ROWTYPE;
605 transit_source actor.org_unit%ROWTYPE;
606 item_object asset.copy%ROWTYPE;
607 item_cn_object asset.call_number%ROWTYPE;
608 ou_skip actor.org_unit_setting%ROWTYPE;
609 result action.matrix_test_result;
610 hold_test config.hold_matrix_matchpoint%ROWTYPE;
611 use_active_date TEXT;
612 age_protect_date TIMESTAMP WITH TIME ZONE;
614 hold_transit_prox INT;
615 frozen_hold_count INT;
616 context_org_list INT[];
619 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
620 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
622 result.success := TRUE;
624 -- Fail if we couldn't find a user
625 IF user_object.id IS NULL THEN
626 result.fail_part := 'no_user';
627 result.success := FALSE;
633 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
635 -- Fail if we couldn't find a copy
636 IF item_object.id IS NULL THEN
637 result.fail_part := 'no_item';
638 result.success := FALSE;
644 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
645 result.matchpoint := matchpoint_id;
647 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
649 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
650 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
651 result.fail_part := 'circ.holds.target_skip_me';
652 result.success := FALSE;
658 -- Fail if user is barred
659 IF user_object.barred IS TRUE THEN
660 result.fail_part := 'actor.usr.barred';
661 result.success := FALSE;
667 -- Fail if we couldn't find any matchpoint (requires a default)
668 IF matchpoint_id IS NULL THEN
669 result.fail_part := 'no_matchpoint';
670 result.success := FALSE;
676 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
678 IF hold_test.holdable IS FALSE THEN
679 result.fail_part := 'config.hold_matrix_test.holdable';
680 result.success := FALSE;
685 IF hold_test.transit_range IS NOT NULL THEN
686 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
687 IF hold_test.distance_is_from_owner THEN
688 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
690 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
693 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
696 result.fail_part := 'transit_range';
697 result.success := FALSE;
703 FOR standing_penalty IN
704 SELECT DISTINCT csp.*
705 FROM actor.usr_standing_penalty usp
706 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
707 WHERE usr = match_user
708 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
709 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
710 AND csp.block_list LIKE '%HOLD%' LOOP
712 result.fail_part := standing_penalty.name;
713 result.success := FALSE;
718 IF hold_test.stop_blocked_user IS TRUE THEN
719 FOR standing_penalty IN
720 SELECT DISTINCT csp.*
721 FROM actor.usr_standing_penalty usp
722 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
723 WHERE usr = match_user
724 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
725 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
726 AND csp.block_list LIKE '%CIRC%' LOOP
728 result.fail_part := standing_penalty.name;
729 result.success := FALSE;
735 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
736 SELECT INTO hold_count COUNT(*)
737 FROM action.hold_request
738 WHERE usr = match_user
739 AND fulfillment_time IS NULL
740 AND cancel_time IS NULL
741 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
743 IF hold_count >= hold_test.max_holds THEN
744 result.fail_part := 'config.hold_matrix_test.max_holds';
745 result.success := FALSE;
751 IF item_object.age_protect IS NOT NULL THEN
752 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
753 IF hold_test.distance_is_from_owner THEN
754 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
756 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
758 IF use_active_date = 'true' THEN
759 age_protect_date := COALESCE(item_object.active_date, NOW());
761 age_protect_date := item_object.create_date;
763 IF age_protect_date + age_protect_object.age > NOW() THEN
764 IF hold_test.distance_is_from_owner THEN
765 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
766 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
768 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
771 IF hold_transit_prox > age_protect_object.prox THEN
772 result.fail_part := 'config.rule_age_hold_protect.prox';
773 result.success := FALSE;
786 $func$ LANGUAGE plpgsql;
788 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, item_object asset.copy, user_object actor.usr, renewal BOOL ) RETURNS action.found_circ_matrix_matchpoint AS $func$
790 cn_object asset.call_number%ROWTYPE;
791 rec_descriptor metabib.rec_descriptor%ROWTYPE;
792 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
793 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
794 weights config.circ_matrix_weights%ROWTYPE;
796 my_item_age INTERVAL;
797 denominator NUMERIC(6,2);
799 result action.found_circ_matrix_matchpoint;
802 result.success = false;
805 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
806 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
808 -- Pre-generate this so we only calc it once
809 IF user_object.dob IS NOT NULL THEN
810 SELECT INTO user_age age(user_object.dob);
814 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
816 -- Grab the closest set circ weight setting.
817 SELECT INTO weights cw.*
818 FROM config.weight_assoc wa
819 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
820 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
825 -- No weights? Bad admin! Defaults to handle that anyway.
826 IF weights.id IS NULL THEN
828 weights.org_unit := 10.0;
829 weights.circ_modifier := 5.0;
830 weights.marc_type := 4.0;
831 weights.marc_form := 3.0;
832 weights.marc_bib_level := 2.0;
833 weights.marc_vr_format := 2.0;
834 weights.copy_circ_lib := 8.0;
835 weights.copy_owning_lib := 8.0;
836 weights.user_home_ou := 8.0;
837 weights.ref_flag := 1.0;
838 weights.juvenile_flag := 6.0;
839 weights.is_renewal := 7.0;
840 weights.usr_age_lower_bound := 0.0;
841 weights.usr_age_upper_bound := 0.0;
842 weights.item_age := 0.0;
845 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
846 -- If you break your org tree with funky parenting this may be wrong
847 -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function
848 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
849 WITH all_distance(distance) AS (
850 SELECT depth AS distance FROM actor.org_unit_type
852 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
854 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
856 -- Loop over all the potential matchpoints
857 FOR cur_matchpoint IN
859 FROM config.circ_matrix_matchpoint m
860 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
861 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
862 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
863 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
864 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
867 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
869 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
870 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
871 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
872 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
874 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
875 -- Static User Checks
876 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
877 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
878 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
879 -- Static Item Checks
880 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
881 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
882 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
883 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
884 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
885 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
886 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
889 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
891 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
892 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
893 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
894 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
895 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
896 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
897 -- Static User Checks
898 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
899 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
900 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
901 -- Static Item Checks
902 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
903 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
904 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
905 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
906 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
907 -- Item age has a slight adjustment to weight based on value.
908 -- This should ensure that a shorter age limit comes first when all else is equal.
909 -- NOTE: This assumes that intervals will normally be in days.
910 CASE WHEN m.item_age IS NOT NULL THEN 4^weights.item_age - 1 + 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC,
911 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
912 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
915 -- Record the full matching row list
916 row_list := row_list || cur_matchpoint.id;
918 -- No matchpoint yet?
919 IF matchpoint.id IS NULL THEN
920 -- Take the entire matchpoint as a starting point
921 matchpoint := cur_matchpoint;
922 CONTINUE; -- No need to look at this row any more.
925 -- Incomplete matchpoint?
926 IF matchpoint.circulate IS NULL THEN
927 matchpoint.circulate := cur_matchpoint.circulate;
929 IF matchpoint.duration_rule IS NULL THEN
930 matchpoint.duration_rule := cur_matchpoint.duration_rule;
932 IF matchpoint.recurring_fine_rule IS NULL THEN
933 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
935 IF matchpoint.max_fine_rule IS NULL THEN
936 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
938 IF matchpoint.hard_due_date IS NULL THEN
939 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
941 IF matchpoint.total_copy_hold_ratio IS NULL THEN
942 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
944 IF matchpoint.available_copy_hold_ratio IS NULL THEN
945 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
947 IF matchpoint.renewals IS NULL THEN
948 matchpoint.renewals := cur_matchpoint.renewals;
950 IF matchpoint.grace_period IS NULL THEN
951 matchpoint.grace_period := cur_matchpoint.grace_period;
955 -- Check required fields
956 IF matchpoint.circulate IS NOT NULL AND
957 matchpoint.duration_rule IS NOT NULL AND
958 matchpoint.recurring_fine_rule IS NOT NULL AND
959 matchpoint.max_fine_rule IS NOT NULL THEN
960 -- All there? We have a completed match.
961 result.success := true;
964 -- Include the assembled matchpoint, even if it isn't complete
965 result.matchpoint := matchpoint;
967 -- Include (for debugging) the full list of matching rows
968 result.buildrows := row_list;
970 -- Hand the result back to caller
973 $func$ LANGUAGE plpgsql;
975 CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint(pickup_ou integer, request_ou integer, match_item bigint, match_user integer, match_requestor integer)
979 requestor_object actor.usr%ROWTYPE;
980 user_object actor.usr%ROWTYPE;
981 item_object asset.copy%ROWTYPE;
982 item_cn_object asset.call_number%ROWTYPE;
983 my_item_age INTERVAL;
984 rec_descriptor metabib.rec_descriptor%ROWTYPE;
985 matchpoint config.hold_matrix_matchpoint%ROWTYPE;
986 weights config.hold_matrix_weights%ROWTYPE;
987 denominator NUMERIC(6,2);
989 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
990 SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
991 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
992 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
993 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = item_cn_object.record;
995 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
997 -- The item's owner should probably be the one determining if the item is holdable
998 -- How to decide that is debatable. Decided to default to the circ library (where the item lives)
999 -- This flag will allow for setting it to the owning library (where the call number "lives")
1000 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.weight_owner_not_circ' AND enabled;
1002 -- Grab the closest set circ weight setting.
1004 -- Default to circ library
1005 SELECT INTO weights hw.*
1006 FROM config.weight_assoc wa
1007 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
1008 JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) d ON (wa.org_unit = d.id)
1013 -- Flag is set, use owning library
1014 SELECT INTO weights hw.*
1015 FROM config.weight_assoc wa
1016 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
1017 JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) d ON (wa.org_unit = d.id)
1023 -- No weights? Bad admin! Defaults to handle that anyway.
1024 IF weights.id IS NULL THEN
1025 weights.user_home_ou := 5.0;
1026 weights.request_ou := 5.0;
1027 weights.pickup_ou := 5.0;
1028 weights.item_owning_ou := 5.0;
1029 weights.item_circ_ou := 5.0;
1030 weights.usr_grp := 7.0;
1031 weights.requestor_grp := 8.0;
1032 weights.circ_modifier := 4.0;
1033 weights.marc_type := 3.0;
1034 weights.marc_form := 2.0;
1035 weights.marc_bib_level := 1.0;
1036 weights.marc_vr_format := 1.0;
1037 weights.juvenile_flag := 4.0;
1038 weights.ref_flag := 0.0;
1039 weights.item_age := 0.0;
1042 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
1043 -- If you break your org tree with funky parenting this may be wrong
1044 -- Note: This CTE is duplicated in the find_circ_matrix_matchpoint function, and it may be a good idea to split it off to a function
1045 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
1046 WITH all_distance(distance) AS (
1047 SELECT depth AS distance FROM actor.org_unit_type
1049 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
1051 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
1053 -- To ATTEMPT to make this work like it used to, make it reverse the user/requestor profile ids.
1054 -- This may be better implemented as part of the upgrade script?
1055 -- Set usr_grp = requestor_grp, requestor_grp = 1 or something when this flag is already set
1056 -- Then remove this flag, of course.
1057 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled;
1060 -- Note: This, to me, is REALLY hacky. I put it in anyway.
1061 -- If you can't tell, this is a single call swap on two variables.
1062 SELECT INTO user_object.profile, requestor_object.profile
1063 requestor_object.profile, user_object.profile;
1066 -- Select the winning matchpoint into the matchpoint variable for returning
1067 SELECT INTO matchpoint m.*
1068 FROM config.hold_matrix_matchpoint m
1069 /*LEFT*/ JOIN permission.grp_ancestors_distance( requestor_object.profile ) rpgad ON m.requestor_grp = rpgad.id
1070 LEFT JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.usr_grp = upgad.id
1071 LEFT JOIN actor.org_unit_ancestors_distance( pickup_ou ) puoua ON m.pickup_ou = puoua.id
1072 LEFT JOIN actor.org_unit_ancestors_distance( request_ou ) rqoua ON m.request_ou = rqoua.id
1073 LEFT JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) cnoua ON m.item_owning_ou = cnoua.id
1074 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.item_circ_ou = iooua.id
1075 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
1077 -- Permission Groups
1078 -- AND (m.requestor_grp IS NULL OR upgad.id IS NOT NULL) -- Optional Requestor Group?
1079 AND (m.usr_grp IS NULL OR upgad.id IS NOT NULL)
1081 AND (m.pickup_ou IS NULL OR (puoua.id IS NOT NULL AND (puoua.distance = 0 OR NOT m.strict_ou_match)))
1082 AND (m.request_ou IS NULL OR (rqoua.id IS NOT NULL AND (rqoua.distance = 0 OR NOT m.strict_ou_match)))
1083 AND (m.item_owning_ou IS NULL OR (cnoua.id IS NOT NULL AND (cnoua.distance = 0 OR NOT m.strict_ou_match)))
1084 AND (m.item_circ_ou IS NULL OR (iooua.id IS NOT NULL AND (iooua.distance = 0 OR NOT m.strict_ou_match)))
1085 AND (m.user_home_ou IS NULL OR (uhoua.id IS NOT NULL AND (uhoua.distance = 0 OR NOT m.strict_ou_match)))
1086 -- Static User Checks
1087 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
1088 -- Static Item Checks
1089 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
1090 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
1091 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
1092 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
1093 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
1094 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
1095 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
1097 -- Permission Groups
1098 CASE WHEN rpgad.distance IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END +
1099 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END +
1101 CASE WHEN puoua.distance IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END +
1102 CASE WHEN rqoua.distance IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END +
1103 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END +
1104 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END +
1105 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
1106 -- Static User Checks -- Note: 4^x is equiv to 2^(2*x)
1107 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
1108 -- Static Item Checks
1109 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
1110 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
1111 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
1112 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
1113 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
1114 -- Item age has a slight adjustment to weight based on value.
1115 -- This should ensure that a shorter age limit comes first when all else is equal.
1116 -- NOTE: This assumes that intervals will normally be in days.
1117 CASE WHEN m.item_age IS NOT NULL THEN 4^weights.item_age - 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC,
1118 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
1119 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
1122 -- Return just the ID for now
1123 RETURN matchpoint.id;
1125 $func$ LANGUAGE 'plpgsql';
1127 DROP INDEX IF EXISTS config.ccmm_once_per_paramset;
1129 DROP INDEX IF EXISTS config.chmm_once_per_paramset;
1131 CREATE UNIQUE INDEX ccmm_once_per_paramset ON config.circ_matrix_matchpoint (org_unit, grp, COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level,''), COALESCE(marc_vr_format, ''), COALESCE(copy_circ_lib::TEXT, ''), COALESCE(copy_owning_lib::TEXT, ''), COALESCE(user_home_ou::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(is_renewal::TEXT, ''), COALESCE(usr_age_lower_bound::TEXT, ''), COALESCE(usr_age_upper_bound::TEXT, ''), COALESCE(item_age::TEXT, '')) WHERE active;
1133 CREATE UNIQUE INDEX chmm_once_per_paramset ON config.hold_matrix_matchpoint (COALESCE(user_home_ou::TEXT, ''), COALESCE(request_ou::TEXT, ''), COALESCE(pickup_ou::TEXT, ''), COALESCE(item_owning_ou::TEXT, ''), COALESCE(item_circ_ou::TEXT, ''), COALESCE(usr_grp::TEXT, ''), COALESCE(requestor_grp::TEXT, ''), COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level, ''), COALESCE(marc_vr_format, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(item_age::TEXT, '')) WHERE active;
1135 UPDATE config.copy_status SET copy_active = true WHERE id IN (0, 1, 7, 8, 10, 12, 15);
1137 INSERT into config.org_unit_setting_type
1138 ( name, label, description, datatype ) VALUES
1139 ( 'circ.holds.age_protect.active_date', 'Holds: Use Active Date for Age Protection', 'When calculating age protection rules use the active date instead of the creation date.', 'bool');
1141 -- Assume create date when item is in status we would update active date for anyway
1142 UPDATE asset.copy SET active_date = create_date WHERE status IN (SELECT id FROM config.copy_status WHERE copy_active = true);
1144 -- Assume create date for any item with circs
1145 UPDATE asset.copy SET active_date = create_date WHERE id IN (SELECT id FROM extend_reporter.full_circ_count WHERE circ_count > 0);
1147 -- Assume create date for status change time while we are at it. Because being created WAS a change in status.
1148 UPDATE asset.copy SET status_changed_time = create_date WHERE status_changed_time IS NULL;
1150 -- Evergreen DB patch 0564.data.delete_empty_volume.sql
1152 -- New org setting cat.volume.delete_on_empty
1155 -- check whether patch can be applied
1156 SELECT evergreen.upgrade_deps_block_check('0564', :eg_version);
1158 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
1160 'cat.volume.delete_on_empty',
1161 oils_i18n_gettext('cat.volume.delete_on_empty', 'Cat: Delete volume with last copy', 'coust', 'label'),
1162 oils_i18n_gettext('cat.volume.delete_on_empty', 'Automatically delete a volume when the last linked copy is deleted', 'coust', 'description'),
1167 -- Evergreen DB patch 0565.schema.action-trigger.event_definition.hold-cancel-no-target-notification.sql
1169 -- New action trigger event definition: Hold Cancelled (No Target) Email Notification
1172 -- check whether patch can be applied
1173 SELECT evergreen.upgrade_deps_block_check('0565', :eg_version);
1175 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field, group_field, template)
1176 VALUES (38, FALSE, 1,
1177 'Hold Cancelled (No Target) Email Notification',
1178 'hold_request.cancel.expire_no_target',
1179 'HoldIsCancelled', 'SendEmail', '30 minutes', 'cancel_time', 'usr',
1182 [%- user = target.0.usr -%]
1183 To: [%- params.recipient_email || user.email %]
1184 From: [%- params.sender_email || default_sender %]
1185 Subject: Hold Request Cancelled
1187 Dear [% user.family_name %], [% user.first_given_name %]
1188 The following holds were cancelled because no items were found to fullfil the hold.
1190 [% FOR hold IN target %]
1191 Title: [% hold.bib_rec.bib_record.simple_record.title %]
1192 Author: [% hold.bib_rec.bib_record.simple_record.author %]
1193 Library: [% hold.pickup_lib.name %]
1194 Request Date: [% date.format(helpers.format_date(hold.rrequest_time), '%Y-%m-%d') %]
1199 INSERT INTO action_trigger.environment (event_def, path) VALUES
1202 (38, 'bib_rec.bib_record.simple_record');
1204 -- Evergreen DB patch XXXX.data.ou_setting_generate_overdue_on_lost.sql.sql
1206 -- check whether patch can be applied
1207 SELECT evergreen.upgrade_deps_block_check('0567', :eg_version);
1209 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
1210 'circ.lost.generate_overdue_on_checkin',
1212 'circ.lost.generate_overdue_on_checkin',
1213 'Circ: Lost Checkin Generates New Overdues',
1218 'circ.lost.generate_overdue_on_checkin',
1219 'Enabling this setting causes retroactive creation of not-yet-existing overdue fines on lost item checkin, up to the point of checkin time (or max fines is reached). This is different than "restore overdue on lost", because it only creates new overdue fines. Use both settings together to get the full complement of overdue fines for a lost item',
1226 -- Evergreen DB patch 0572.vandelay-record-matching-and-quality.sql
1230 -- check whether patch can be applied
1231 SELECT evergreen.upgrade_deps_block_check('0572', :eg_version);
1233 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL;
1235 CREATE TABLE vandelay.match_set (
1236 id SERIAL PRIMARY KEY,
1238 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE,
1239 mtype TEXT NOT NULL DEFAULT 'biblio', -- 'biblio','authority','mfhd'?, others?
1240 CONSTRAINT name_once_per_owner_mtype UNIQUE (name, owner, mtype)
1243 -- Table to define match points, either FF via SVF or tag+subfield
1244 CREATE TABLE vandelay.match_set_point (
1245 id SERIAL PRIMARY KEY,
1246 match_set INT REFERENCES vandelay.match_set (id) ON DELETE CASCADE,
1247 parent INT REFERENCES vandelay.match_set_point (id),
1248 bool_op TEXT CHECK (bool_op IS NULL OR (bool_op IN ('AND','OR','NOT'))),
1249 svf TEXT REFERENCES config.record_attr_definition (name),
1252 negate BOOL DEFAULT FALSE,
1253 quality INT NOT NULL DEFAULT 1, -- higher is better
1254 CONSTRAINT vmsp_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
1255 CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_bo CHECK (
1256 (tag IS NOT NULL AND svf IS NULL AND bool_op IS NULL) OR
1257 (tag IS NULL AND svf IS NOT NULL AND bool_op IS NULL) OR
1258 (tag IS NULL AND svf IS NULL AND bool_op IS NOT NULL)
1262 CREATE TABLE vandelay.match_set_quality (
1263 id SERIAL PRIMARY KEY,
1264 match_set INT NOT NULL REFERENCES vandelay.match_set (id) ON DELETE CASCADE,
1265 svf TEXT REFERENCES config.record_attr_definition,
1268 value TEXT NOT NULL,
1269 quality INT NOT NULL DEFAULT 1, -- higher is better
1270 CONSTRAINT vmsq_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL),
1271 CONSTRAINT vmsq_need_a_tag_or_a_ff CHECK ((tag IS NOT NULL AND svf IS NULL) OR (tag IS NULL AND svf IS NOT NULL))
1273 CREATE UNIQUE INDEX vmsq_def_once_per_set ON vandelay.match_set_quality (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,''), value);
1277 ALTER TABLE vandelay.queue ADD COLUMN match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
1278 ALTER TABLE vandelay.queued_record ADD COLUMN quality INT NOT NULL DEFAULT 0;
1279 ALTER TABLE vandelay.bib_attr_definition DROP COLUMN ident;
1281 CREATE TABLE vandelay.import_error (
1282 code TEXT PRIMARY KEY,
1283 description TEXT NOT NULL -- i18n
1286 ALTER TABLE vandelay.queued_bib_record
1287 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1288 ADD COLUMN error_detail TEXT;
1290 ALTER TABLE vandelay.bib_match
1291 DROP COLUMN field_type,
1292 DROP COLUMN matched_attr,
1293 ADD COLUMN quality INT NOT NULL DEFAULT 1,
1294 ADD COLUMN match_score INT NOT NULL DEFAULT 0;
1296 ALTER TABLE vandelay.import_item
1297 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1298 ADD COLUMN error_detail TEXT,
1299 ADD COLUMN imported_as BIGINT REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED,
1300 ADD COLUMN import_time TIMESTAMP WITH TIME ZONE;
1302 ALTER TABLE vandelay.merge_profile ADD COLUMN lwm_ratio NUMERIC;
1304 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
1311 retval config.marc21_rec_type_map%ROWTYPE;
1313 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
1315 IF ldr IS NULL OR ldr = '' THEN
1316 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
1320 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
1321 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
1324 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
1325 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
1327 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
1329 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
1332 IF retval.code IS NULL THEN
1333 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
1338 $func$ LANGUAGE PLPGSQL;
1340 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
1347 rtype := (vandelay.marc21_record_type( marc )).code;
1348 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
1349 IF ff_pos.tag = 'ldr' THEN
1350 val := oils_xpath_string('//*[local-name()="leader"]', marc);
1351 IF val IS NOT NULL THEN
1352 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
1356 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
1357 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
1361 val := REPEAT( ff_pos.default_val, ff_pos.length );
1367 $func$ LANGUAGE PLPGSQL;
1369 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
1374 output biblio.record_ff_map%ROWTYPE;
1376 rtype := (vandelay.marc21_record_type( marc )).code;
1378 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
1379 output.ff_name := ff_pos.fixed_field;
1380 output.ff_value := NULL;
1382 IF ff_pos.tag = 'ldr' THEN
1383 output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
1384 IF output.ff_value IS NOT NULL THEN
1385 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
1387 output.ff_value := NULL;
1390 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
1391 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
1392 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
1394 output.ff_value := NULL;
1402 $func$ LANGUAGE PLPGSQL;
1404 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
1408 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
1409 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
1410 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
1411 retval biblio.marc21_physical_characteristics%ROWTYPE;
1414 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
1416 IF _007 IS NOT NULL AND _007 <> '' THEN
1417 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
1419 IF ptype.ptype_key IS NOT NULL THEN
1420 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
1421 SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007, psf.start_pos + 1, psf.length );
1423 IF pval.id IS NOT NULL THEN
1426 retval.ptype := ptype.ptype_key;
1427 retval.subfield := psf.id;
1428 retval.value := pval.id;
1438 $func$ LANGUAGE PLPGSQL;
1440 CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT );
1441 CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
1444 use MARC::File::XML (BinaryEncoding => 'UTF-8');
1448 MARC::Charset->assume_unicode(1);
1451 my $r = MARC::Record->new_from_xml( $xml );
1453 return_next( { tag => 'LDR', value => $r->leader } );
1455 for my $f ( $r->fields ) {
1456 if ($f->is_control_field) {
1457 return_next({ tag => $f->tag, value => $f->data });
1459 for my $s ($f->subfields) {
1462 ind1 => $f->indicator(1),
1463 ind2 => $f->indicator(2),
1464 subfield => $s->[0],
1468 if ( $f->tag eq '245' and $s->[0] eq 'a' ) {
1469 my $trim = $f->indicator(2) || 0;
1472 ind1 => $f->indicator(1),
1473 ind2 => $f->indicator(2),
1475 value => substr( $s->[1], $trim )
1484 $func$ LANGUAGE PLPERLU;
1486 CREATE OR REPLACE FUNCTION vandelay.flatten_marc ( marc TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$
1488 output vandelay.flat_marc%ROWTYPE;
1491 FOR field IN SELECT * FROM vandelay.flay_marc( marc ) LOOP
1492 output.ind1 := field.ind1;
1493 output.ind2 := field.ind2;
1494 output.tag := field.tag;
1495 output.subfield := field.subfield;
1496 IF field.subfield IS NOT NULL AND field.tag NOT IN ('020','022','024') THEN -- exclude standard numbers and control fields
1497 output.value := naco_normalize(field.value, field.subfield);
1499 output.value := field.value;
1502 CONTINUE WHEN output.value IS NULL;
1507 $func$ LANGUAGE PLPGSQL;
1509 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
1511 transformed_xml TEXT;
1514 xfrm config.xml_transform%ROWTYPE;
1516 new_attrs HSTORE := ''::HSTORE;
1517 attr_def config.record_attr_definition%ROWTYPE;
1520 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
1522 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
1523 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value
1524 FROM vandelay.flatten_marc(xml) AS x
1525 WHERE x.tag LIKE attr_def.tag
1527 WHEN attr_def.sf_list IS NOT NULL
1528 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
1535 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
1536 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
1538 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
1540 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
1542 -- See if we can skip the XSLT ... it's expensive
1543 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1544 -- Can't skip the transform
1545 IF xfrm.xslt <> '---' THEN
1546 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
1548 transformed_xml := xml;
1551 prev_xfrm := xfrm.name;
1554 IF xfrm.name IS NULL THEN
1555 -- just grab the marcxml (empty) transform
1556 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
1557 prev_xfrm := xfrm.name;
1560 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
1562 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
1563 SELECT m.value::TEXT INTO attr_value
1564 FROM vandelay.marc21_physical_characteristics(xml) v
1565 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
1566 WHERE v.subfield = attr_def.phys_char_sf
1567 LIMIT 1; -- Just in case ...
1571 -- apply index normalizers to attr_value
1573 SELECT n.func AS func,
1574 n.param_count AS param_count,
1576 FROM config.index_normalizer n
1577 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
1578 WHERE attr = attr_def.name
1580 EXECUTE 'SELECT ' || normalizer.func || '(' ||
1581 quote_literal( attr_value ) ||
1583 WHEN normalizer.param_count > 0
1584 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1587 ')' INTO attr_value;
1591 -- Add the new value to the hstore
1592 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
1598 $_$ LANGUAGE PLPGSQL;
1600 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
1601 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_ACCUM(name) FROM config.record_attr_definition));
1604 -- Everything between this comment and the beginning of the definition of
1605 -- vandelay.match_bib_record() is strictly in service of that function.
1606 CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER);
1608 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
1609 match_set_id INTEGER, record_xml TEXT
1610 ) RETURNS SETOF vandelay.match_set_test_result AS $$
1621 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
1622 svf_rstore := vandelay.extract_rec_attrs(record_xml);
1624 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
1625 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
1627 -- generate the where clause and return that directly (into wq), and as
1628 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
1629 wq := vandelay.get_expr_from_match_set(match_set_id);
1631 query_ := 'SELECT bre.id AS record, ';
1633 -- qrows table is for the quality bits we add to the SELECT clause
1634 SELECT ARRAY_TO_STRING(
1635 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
1636 ) INTO coal FROM _vandelay_tmp_qrows;
1638 -- our query string so far is the SELECT clause and the inital FROM.
1639 -- no JOINs yet nor the WHERE clause
1640 query_ := query_ || coal || ' AS quality ' || E'\n' ||
1641 'FROM biblio.record_entry bre ';
1643 -- jrows table is for the joins we must make (and the real text conditions)
1644 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
1645 FROM _vandelay_tmp_jrows;
1647 -- add those joins and the where clause to our query.
1648 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
1650 -- this will return rows of record,quality
1651 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
1655 DROP TABLE _vandelay_tmp_qrows;
1656 DROP TABLE _vandelay_tmp_jrows;
1660 $$ LANGUAGE PLPGSQL;
1662 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
1664 ) RETURNS HSTORE AS $$
1668 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
1672 SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value
1673 FROM vandelay.flatten_marc(record_xml)
1674 GROUP BY tag, subfield ORDER BY tag, subfield
1678 $$ LANGUAGE PLPGSQL;
1680 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
1681 match_set_id INTEGER
1682 ) RETURNS TEXT AS $$
1684 root vandelay.match_set_point;
1686 SELECT * INTO root FROM vandelay.match_set_point
1687 WHERE parent IS NULL AND match_set = match_set_id;
1689 RETURN vandelay.get_expr_from_match_set_point(root);
1691 $$ LANGUAGE PLPGSQL;
1693 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
1694 node vandelay.match_set_point
1695 ) RETURNS TEXT AS $$
1701 child vandelay.match_set_point;
1703 SELECT ARRAY_ACCUM(id) INTO children FROM vandelay.match_set_point
1704 WHERE parent = node.id;
1706 IF ARRAY_LENGTH(children, 1) > 0 THEN
1707 this_op := vandelay._get_expr_render_one(node);
1710 WHILE children[i] IS NOT NULL LOOP
1711 SELECT * INTO child FROM vandelay.match_set_point
1712 WHERE id = children[i];
1714 q := q || ' ' || this_op || ' ';
1717 q := q || vandelay.get_expr_from_match_set_point(child);
1721 ELSIF node.bool_op IS NULL THEN
1722 PERFORM vandelay._get_expr_push_qrow(node);
1723 PERFORM vandelay._get_expr_push_jrow(node);
1724 RETURN vandelay._get_expr_render_one(node);
1729 $$ LANGUAGE PLPGSQL;
1731 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_qrow(
1732 node vandelay.match_set_point
1733 ) RETURNS VOID AS $$
1736 INSERT INTO _vandelay_tmp_qrows (q) VALUES (node.id);
1738 $$ LANGUAGE PLPGSQL;
1740 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
1741 node vandelay.match_set_point
1742 ) RETURNS VOID AS $$
1755 IF node.tag IS NOT NULL THEN
1757 IF node.subfield IS NOT NULL THEN
1758 tagkey := tagkey || node.subfield;
1762 my_alias := 'n' || node.id::TEXT;
1764 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
1765 ' AS quality FROM metabib.';
1766 IF node.tag IS NOT NULL THEN
1767 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
1768 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
1770 IF node.subfield IS NOT NULL THEN
1771 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
1772 node.subfield || '''';
1774 jrow := jrow || ' AND (' || my_alias || '.value ' || op ||
1775 ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
1777 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
1778 my_alias || '.id = bre.id AND (' ||
1779 my_alias || '.attrs->''' || node.svf ||
1780 ''' ' || op || ' $2->''' || node.svf || '''))';
1782 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
1784 $$ LANGUAGE PLPGSQL;
1786 CREATE OR REPLACE FUNCTION vandelay._get_expr_render_one(
1787 node vandelay.match_set_point
1788 ) RETURNS TEXT AS $$
1792 IF node.bool_op IS NOT NULL THEN
1793 RETURN node.bool_op;
1795 RETURN '(n' || node.id::TEXT || '.id IS NOT NULL)';
1798 $$ LANGUAGE PLPGSQL;
1800 CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$
1802 incoming_existing_id TEXT;
1803 test_result vandelay.match_set_test_result%ROWTYPE;
1807 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
1811 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1813 SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue;
1815 IF match_set IS NOT NULL THEN
1816 NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set );
1819 -- Perfect matches on 901$c exit early with a match with high quality.
1820 incoming_existing_id :=
1821 oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
1823 IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
1824 SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint;
1825 IF tmp_rec IS NOT NULL THEN
1826 INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality)
1831 -- note: no match_set means quality==0
1832 vandelay.measure_record_quality( b.marc, match_set )
1833 FROM biblio.record_entry b
1834 WHERE id = incoming_existing_id::bigint;
1838 IF match_set IS NULL THEN
1842 FOR test_result IN SELECT * FROM
1843 vandelay.match_set_test_marcxml(match_set, NEW.marc) LOOP
1845 INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality )
1849 test_result.quality,
1850 vandelay.measure_record_quality( b.marc, match_set )
1851 FROM biblio.record_entry b
1852 WHERE id = test_result.record;
1858 $func$ LANGUAGE PLPGSQL;
1860 CREATE OR REPLACE FUNCTION vandelay.measure_record_quality ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
1864 test vandelay.match_set_quality%ROWTYPE;
1867 FOR test IN SELECT * FROM vandelay.match_set_quality WHERE match_set = match_set_id LOOP
1868 IF test.tag IS NOT NULL THEN
1869 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml ) WHERE tag = test.tag AND subfield = test.subfield LOOP
1870 IF test.value = rvalue THEN
1871 out_q := out_q + test.quality;
1875 IF test.value = vandelay.extract_rec_attrs(xml, ARRAY[test.svf]) -> test.svf THEN
1876 out_q := out_q + test.quality;
1883 $_$ LANGUAGE PLPGSQL;
1886 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1888 merge_profile vandelay.merge_profile%ROWTYPE;
1889 dyn_profile vandelay.compile_profile%ROWTYPE;
1899 SELECT q.marc INTO v_marc
1900 FROM vandelay.queued_record q
1901 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
1904 IF v_marc IS NULL THEN
1905 -- RAISE NOTICE 'no marc for vandelay or bib record';
1909 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
1910 UPDATE vandelay.queued_bib_record
1911 SET imported_as = eg_id,
1913 WHERE id = import_id;
1915 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
1917 IF editor_string IS NOT NULL AND editor_string <> '' THEN
1918 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
1920 IF editor_id IS NULL THEN
1921 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
1924 IF editor_id IS NOT NULL THEN
1925 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
1932 -- RAISE NOTICE 'update of biblio.record_entry failed';
1937 $$ LANGUAGE PLPGSQL;
1940 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record_with_best ( import_id BIGINT, merge_profile_id INT, lwm_ratio_value_p NUMERIC ) RETURNS BOOL AS $$
1943 lwm_ratio_value NUMERIC;
1946 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
1948 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
1951 -- RAISE NOTICE 'already imported, cannot auto-overlay'
1955 SELECT m.eg_record INTO eg_id
1956 FROM vandelay.bib_match m
1957 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
1958 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
1959 JOIN biblio.record_entry r ON (r.id = m.eg_record)
1960 WHERE m.queued_record = import_id
1961 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
1962 ORDER BY m.match_score DESC, -- required match score
1963 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
1964 m.id -- when in doubt, use the first match
1967 IF eg_id IS NULL THEN
1968 -- RAISE NOTICE 'incoming record is not of high enough quality';
1972 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
1974 $$ LANGUAGE PLPGSQL;
1976 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record_with_best ( import_id BIGINT, merge_profile_id INT, lwm_ratio_value_p NUMERIC ) RETURNS BOOL AS $$
1979 lwm_ratio_value NUMERIC;
1982 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
1984 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
1987 -- RAISE NOTICE 'already imported, cannot auto-overlay'
1991 SELECT m.eg_record INTO eg_id
1992 FROM vandelay.bib_match m
1993 JOIN vandelay.queued_bib_record qr ON (m.queued_record = qr.id)
1994 JOIN vandelay.bib_queue q ON (qr.queue = q.id)
1995 JOIN biblio.record_entry r ON (r.id = m.eg_record)
1996 WHERE m.queued_record = import_id
1997 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
1998 ORDER BY m.match_score DESC, -- required match score
1999 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
2000 m.id -- when in doubt, use the first match
2003 IF eg_id IS NULL THEN
2004 -- RAISE NOTICE 'incoming record is not of high enough quality';
2008 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
2010 $$ LANGUAGE PLPGSQL;
2013 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( queue_id BIGINT, merge_profile_id INT, lwm_ratio_value NUMERIC ) RETURNS SETOF BIGINT AS $$
2015 queued_record vandelay.queued_bib_record%ROWTYPE;
2018 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
2020 IF vandelay.auto_overlay_bib_record_with_best( queued_record.id, merge_profile_id, lwm_ratio_value ) THEN
2021 RETURN NEXT queued_record.id;
2029 $$ LANGUAGE PLPGSQL;
2031 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue_with_best ( import_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
2032 SELECT vandelay.auto_overlay_bib_queue_with_best( $1, $2, p.lwm_ratio ) FROM vandelay.merge_profile p WHERE id = $2;
2035 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
2041 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
2045 FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
2047 SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
2048 IF (value IS NOT NULL AND value <> '') THEN
2049 INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
2056 $$ LANGUAGE PLPGSQL;
2058 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
2061 item_data vandelay.import_item%ROWTYPE;
2064 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
2068 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
2070 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
2071 INSERT INTO vandelay.import_item (
2095 item_data.definition,
2096 item_data.owning_lib,
2098 item_data.call_number,
2099 item_data.copy_number,
2102 item_data.circulate,
2104 item_data.deposit_amount,
2109 item_data.circ_modifier,
2110 item_data.circ_as_type,
2111 item_data.alert_message,
2113 item_data.priv_note,
2114 item_data.opac_visible
2120 $func$ LANGUAGE PLPGSQL;
2122 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
2124 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
2128 DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
2129 DELETE FROM vandelay.import_item WHERE record = OLD.id;
2131 IF TG_OP = 'UPDATE' THEN
2136 $$ LANGUAGE PLPGSQL;
2140 DROP TRIGGER zz_match_bibs_trigger ON vandelay.queued_bib_record;
2141 CREATE TRIGGER zz_match_bibs_trigger
2142 BEFORE INSERT OR UPDATE ON vandelay.queued_bib_record
2143 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
2145 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
2151 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
2155 FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
2157 SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
2158 IF (value IS NOT NULL AND value <> '') THEN
2159 INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
2166 $$ LANGUAGE PLPGSQL;
2168 ALTER TABLE vandelay.authority_attr_definition DROP COLUMN ident;
2169 ALTER TABLE vandelay.queued_authority_record
2170 ADD COLUMN import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
2171 ADD COLUMN error_detail TEXT;
2173 ALTER TABLE vandelay.authority_match DROP COLUMN matched_attr;
2175 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
2177 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
2181 DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
2182 IF TG_OP = 'UPDATE' THEN
2187 $$ LANGUAGE PLPGSQL;
2189 CREATE OR REPLACE FUNCTION authority.flatten_marc ( rid BIGINT ) RETURNS SETOF authority.full_rec AS $func$
2191 auth authority.record_entry%ROWTYPE;
2192 output authority.full_rec%ROWTYPE;
2195 SELECT INTO auth * FROM authority.record_entry WHERE id = rid;
2197 FOR field IN SELECT * FROM vandelay.flatten_marc( auth.marc ) LOOP
2198 output.record := rid;
2199 output.ind1 := field.ind1;
2200 output.ind2 := field.ind2;
2201 output.tag := field.tag;
2202 output.subfield := field.subfield;
2203 output.value := field.value;
2208 $func$ LANGUAGE PLPGSQL;
2210 CREATE OR REPLACE FUNCTION biblio.flatten_marc ( rid BIGINT ) RETURNS SETOF metabib.full_rec AS $func$
2212 bib biblio.record_entry%ROWTYPE;
2213 output metabib.full_rec%ROWTYPE;
2216 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
2218 FOR field IN SELECT * FROM vandelay.flatten_marc( bib.marc ) LOOP
2219 output.record := rid;
2220 output.ind1 := field.ind1;
2221 output.ind2 := field.ind2;
2222 output.tag := field.tag;
2223 output.subfield := field.subfield;
2224 output.value := field.value;
2229 $func$ LANGUAGE PLPGSQL;
2231 -----------------------------------------------
2232 -- Seed data for import errors
2233 -----------------------------------------------
2235 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'general.unknown', oils_i18n_gettext('general.unknown', 'Import or Overlay failed', 'vie', 'description') );
2236 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.item.duplicate.barcode', oils_i18n_gettext('import.item.duplicate.barcode', 'Import failed due to barcode collision', 'vie', 'description') );
2237 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.item.invalid.circ_modifier', oils_i18n_gettext('import.item.invalid.circ_modifier', 'Import failed due to invalid circulation modifier', 'vie', 'description') );
2238 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.item.invalid.location', oils_i18n_gettext('import.item.invalid.location', 'Import failed due to invalid copy location', 'vie', 'description') );
2239 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.duplicate.sysid', oils_i18n_gettext('import.duplicate.sysid', 'Import failed due to system id collision', 'vie', 'description') );
2240 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.duplicate.tcn', oils_i18n_gettext('import.duplicate.sysid', 'Import failed due to system id collision', 'vie', 'description') );
2241 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.missing.sysid', oils_i18n_gettext('overlay.missing.sysid', 'Overlay failed due to missing system id', 'vie', 'description') );
2242 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.auth.duplicate.acn', oils_i18n_gettext('import.auth.duplicate.acn', 'Import failed due to Accession Number collision', 'vie', 'description') );
2243 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.xml.malformed', oils_i18n_gettext('import.xml.malformed', 'Malformed record cause Import failure', 'vie', 'description') );
2244 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.xml.malformed', oils_i18n_gettext('overlay.xml.malformed', 'Malformed record cause Overlay failure', 'vie', 'description') );
2245 INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.record.quality', oils_i18n_gettext('overlay.record.quality', 'New record had insufficient quality', 'vie', 'description') );
2248 ----------------------------------------------------------------
2249 -- Seed data for queued record/item exports
2250 ----------------------------------------------------------------
2252 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES (
2253 'vandelay.queued_bib_record.print',
2256 'vandelay.queued_bib_record.print',
2257 'Print output has been requested for records in an Importer Bib Queue.',
2264 'vandelay.queued_bib_record.csv',
2267 'vandelay.queued_bib_record.csv',
2268 'CSV output has been requested for records in an Importer Bib Queue.',
2275 'vandelay.queued_bib_record.email',
2278 'vandelay.queued_bib_record.email',
2279 'An email has been requested for records in an Importer Bib Queue.',
2286 'vandelay.queued_auth_record.print',
2289 'vandelay.queued_auth_record.print',
2290 'Print output has been requested for records in an Importer Authority Queue.',
2297 'vandelay.queued_auth_record.csv',
2300 'vandelay.queued_auth_record.csv',
2301 'CSV output has been requested for records in an Importer Authority Queue.',
2308 'vandelay.queued_auth_record.email',
2311 'vandelay.queued_auth_record.email',
2312 'An email has been requested for records in an Importer Authority Queue.',
2319 'vandelay.import_items.print',
2322 'vandelay.import_items.print',
2323 'Print output has been requested for Import Items from records in an Importer Bib Queue.',
2330 'vandelay.import_items.csv',
2333 'vandelay.import_items.csv',
2334 'CSV output has been requested for Import Items from records in an Importer Bib Queue.',
2341 'vandelay.import_items.email',
2344 'vandelay.import_items.email',
2345 'An email has been requested for Import Items from records in an Importer Bib Queue.',
2353 INSERT INTO action_trigger.event_definition (
2368 'Print Output for Queued Bib Records',
2369 'vandelay.queued_bib_record.print',
2377 Queue ID: [% target.0.queue.id %]
2378 Queue Name: [% target.0.queue.name %]
2379 Queue Type: [% target.0.queue.queue_type %]
2380 Complete? [% target.0.queue.complete %]
2382 [% FOR vqbr IN target %]
2384 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
2385 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
2386 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
2387 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
2388 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
2389 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
2390 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
2391 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
2392 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
2393 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
2394 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
2395 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
2396 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
2397 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
2398 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
2406 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2411 INSERT INTO action_trigger.event_definition (
2426 'CSV Output for Queued Bib Records',
2427 'vandelay.queued_bib_record.csv',
2434 "Title of work","Author of work","Language of work","Pagination","ISBN","ISSN","Price","Accession Number","TCN Value","TCN Source","Internal ID","Publisher","Publication Date","Edition","Item Barcode"
2435 [% FOR vqbr IN target %]"[% helpers.get_queued_bib_attr('title',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('author',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('language',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('pagination',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('isbn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('issn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('price',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('publisher',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('edition',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) | replace('"', '""') %]"
2441 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2446 INSERT INTO action_trigger.event_definition (
2461 'Email Output for Queued Bib Records',
2462 'vandelay.queued_bib_record.email',
2469 [%- SET user = target.0.queue.owner -%]
2470 To: [%- params.recipient_email || user.email || 'root@localhost' %]
2471 From: [%- params.sender_email || default_sender %]
2472 Subject: Bibs from Import Queue
2474 Queue ID: [% target.0.queue.id %]
2475 Queue Name: [% target.0.queue.name %]
2476 Queue Type: [% target.0.queue.queue_type %]
2477 Complete? [% target.0.queue.complete %]
2479 [% FOR vqbr IN target %]
2481 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
2482 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
2483 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
2484 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
2485 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
2486 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
2487 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
2488 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
2489 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
2490 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
2491 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
2492 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
2493 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
2494 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
2495 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
2503 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2506 ,( 41, 'queue.owner')
2509 INSERT INTO action_trigger.event_definition (
2524 'Print Output for Queued Authority Records',
2525 'vandelay.queued_auth_record.print',
2533 Queue ID: [% target.0.queue.id %]
2534 Queue Name: [% target.0.queue.name %]
2535 Queue Type: [% target.0.queue.queue_type %]
2536 Complete? [% target.0.queue.complete %]
2538 [% FOR vqar IN target %]
2540 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
2548 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2553 INSERT INTO action_trigger.event_definition (
2568 'CSV Output for Queued Authority Records',
2569 'vandelay.queued_auth_record.csv',
2577 [% FOR vqar IN target %]"[% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) | replace('"', '""') %]"
2583 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2588 INSERT INTO action_trigger.event_definition (
2603 'Email Output for Queued Authority Records',
2604 'vandelay.queued_auth_record.email',
2611 [%- SET user = target.0.queue.owner -%]
2612 To: [%- params.recipient_email || user.email || 'root@localhost' %]
2613 From: [%- params.sender_email || default_sender %]
2614 Subject: Authorities from Import Queue
2616 Queue ID: [% target.0.queue.id %]
2617 Queue Name: [% target.0.queue.name %]
2618 Queue Type: [% target.0.queue.queue_type %]
2619 Complete? [% target.0.queue.complete %]
2621 [% FOR vqar IN target %]
2623 Record Identifier | [% helpers.get_queued_auth_attr('rec_identifier',vqar.attributes) %]
2631 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2634 ,( 44, 'queue.owner')
2637 INSERT INTO action_trigger.event_definition (
2652 'Print Output for Import Items from Queued Bib Records',
2653 'vandelay.import_items.print',
2656 'record.queue.owner',
2661 Queue ID: [% target.0.record.queue.id %]
2662 Queue Name: [% target.0.record.queue.name %]
2663 Queue Type: [% target.0.record.queue.queue_type %]
2664 Complete? [% target.0.record.queue.complete %]
2666 [% FOR vii IN target %]
2668 Import Item ID | [% vii.id %]
2669 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
2670 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
2671 Attribute Definition | [% vii.definition %]
2672 Import Error | [% vii.import_error %]
2673 Import Error Detail | [% vii.error_detail %]
2674 Owning Library | [% vii.owning_lib %]
2675 Circulating Library | [% vii.circ_lib %]
2676 Call Number | [% vii.call_number %]
2677 Copy Number | [% vii.copy_number %]
2678 Status | [% vii.status.name %]
2679 Shelving Location | [% vii.location.name %]
2680 Circulate | [% vii.circulate %]
2681 Deposit | [% vii.deposit %]
2682 Deposit Amount | [% vii.deposit_amount %]
2683 Reference | [% vii.ref %]
2684 Holdable | [% vii.holdable %]
2685 Price | [% vii.price %]
2686 Barcode | [% vii.barcode %]
2687 Circulation Modifier | [% vii.circ_modifier %]
2688 Circulate As MARC Type | [% vii.circ_as_type %]
2689 Alert Message | [% vii.alert_message %]
2690 Public Note | [% vii.pub_note %]
2691 Private Note | [% vii.priv_note %]
2692 OPAC Visible | [% vii.opac_visible %]
2700 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2702 ,( 45, 'record.attributes')
2703 ,( 45, 'record.queue')
2704 ,( 45, 'record.queue.owner')
2707 INSERT INTO action_trigger.event_definition (
2722 'CSV Output for Import Items from Queued Bib Records',
2723 'vandelay.import_items.csv',
2726 'record.queue.owner',
2730 "Import Item ID","Title of work","ISBN","Attribute Definition","Import Error","Import Error Detail","Owning Library","Circulating Library","Call Number","Copy Number","Status","Shelving Location","Circulate","Deposit","Deposit Amount","Reference","Holdable","Price","Barcode","Circulation Modifier","Circulate As MARC Type","Alert Message","Public Note","Private Note","OPAC Visible"
2731 [% FOR vii IN target %]"[% vii.id | replace('"', '""') %]","[% helpers.get_queued_bib_attr('title',vii.record.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('isbn',vii.record.attributes) | replace('"', '""') %]","[% vii.definition | replace('"', '""') %]","[% vii.import_error | replace('"', '""') %]","[% vii.error_detail | replace('"', '""') %]","[% vii.owning_lib | replace('"', '""') %]","[% vii.circ_lib | replace('"', '""') %]","[% vii.call_number | replace('"', '""') %]","[% vii.copy_number | replace('"', '""') %]","[% vii.status.name | replace('"', '""') %]","[% vii.location.name | replace('"', '""') %]","[% vii.circulate | replace('"', '""') %]","[% vii.deposit | replace('"', '""') %]","[% vii.deposit_amount | replace('"', '""') %]","[% vii.ref | replace('"', '""') %]","[% vii.holdable | replace('"', '""') %]","[% vii.price | replace('"', '""') %]","[% vii.barcode | replace('"', '""') %]","[% vii.circ_modifier | replace('"', '""') %]","[% vii.circ_as_type | replace('"', '""') %]","[% vii.alert_message | replace('"', '""') %]","[% vii.pub_note | replace('"', '""') %]","[% vii.priv_note | replace('"', '""') %]","[% vii.opac_visible | replace('"', '""') %]"
2737 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2739 ,( 46, 'record.attributes')
2740 ,( 46, 'record.queue')
2741 ,( 46, 'record.queue.owner')
2744 INSERT INTO action_trigger.event_definition (
2759 'Email Output for Import Items from Queued Bib Records',
2760 'vandelay.import_items.email',
2763 'record.queue.owner',
2767 [%- SET user = target.0.record.queue.owner -%]
2768 To: [%- params.recipient_email || user.email || 'root@localhost' %]
2769 From: [%- params.sender_email || default_sender %]
2770 Subject: Import Items from Import Queue
2772 Queue ID: [% target.0.record.queue.id %]
2773 Queue Name: [% target.0.record.queue.name %]
2774 Queue Type: [% target.0.record.queue.queue_type %]
2775 Complete? [% target.0.record.queue.complete %]
2777 [% FOR vii IN target %]
2779 Import Item ID | [% vii.id %]
2780 Title of work | [% helpers.get_queued_bib_attr('title',vii.record.attributes) %]
2781 ISBN | [% helpers.get_queued_bib_attr('isbn',vii.record.attributes) %]
2782 Attribute Definition | [% vii.definition %]
2783 Import Error | [% vii.import_error %]
2784 Import Error Detail | [% vii.error_detail %]
2785 Owning Library | [% vii.owning_lib %]
2786 Circulating Library | [% vii.circ_lib %]
2787 Call Number | [% vii.call_number %]
2788 Copy Number | [% vii.copy_number %]
2789 Status | [% vii.status.name %]
2790 Shelving Location | [% vii.location.name %]
2791 Circulate | [% vii.circulate %]
2792 Deposit | [% vii.deposit %]
2793 Deposit Amount | [% vii.deposit_amount %]
2794 Reference | [% vii.ref %]
2795 Holdable | [% vii.holdable %]
2796 Price | [% vii.price %]
2797 Barcode | [% vii.barcode %]
2798 Circulation Modifier | [% vii.circ_modifier %]
2799 Circulate As MARC Type | [% vii.circ_as_type %]
2800 Alert Message | [% vii.alert_message %]
2801 Public Note | [% vii.pub_note %]
2802 Private Note | [% vii.priv_note %]
2803 OPAC Visible | [% vii.opac_visible %]
2810 INSERT INTO action_trigger.environment ( event_def, path) VALUES (
2812 ,( 47, 'record.attributes')
2813 ,( 47, 'record.queue')
2814 ,( 47, 'record.queue.owner')
2819 SELECT evergreen.upgrade_deps_block_check('0574', :eg_version);
2821 UPDATE action_trigger.event_definition SET template =
2825 table { border-collapse: collapse; }
2826 td { padding: 5px; border-bottom: 1px solid #888; }
2827 th { font-weight: bold; }
2830 # Sort the holds into copy-location buckets
2831 # In the main print loop, sort each bucket by callnumber before printing
2832 SET holds_list = [];
2834 SET current_location = target.0.current_copy.location.id;
2836 IF current_location != hold.current_copy.location.id;
2837 SET current_location = hold.current_copy.location.id;
2838 holds_list.push(loc_data);
2843 'callnumber' => hold.current_copy.call_number.label
2845 loc_data.push(hold_data);
2847 holds_list.push(loc_data)
2854 <th>Shelving Location</th>
2855 <th>Call Number</th>
2856 <th>Barcode/Part</th>
2861 [% FOR loc_data IN holds_list %]
2862 [% FOR hold_data IN loc_data.sort('callnumber') %]
2864 SET hold = hold_data.hold;
2865 SET copy_data = helpers.get_copy_bib_basics(hold.current_copy.id);
2868 <td>[% copy_data.title | truncate %]</td>
2869 <td>[% copy_data.author | truncate %]</td>
2870 <td>[% hold.current_copy.location.name %]</td>
2871 <td>[% hold.current_copy.call_number.label %]</td>
2872 <td>[% hold.current_copy.barcode %]
2873 [% FOR part IN hold.current_copy.parts %]
2874 [% part.part.label %]
2877 <td>[% hold.usr.card.barcode %]</td>
2886 INSERT INTO action_trigger.environment (
2890 (35, 'current_copy.parts'),
2891 (35, 'current_copy.parts.part')
2895 -- Evergreen DB patch XXXX.schema.authority-control-sets.sql
2897 -- Schema upgrade to add Authority Control Set functionality
2901 -- check whether patch can be applied
2902 SELECT evergreen.upgrade_deps_block_check('0575', :eg_version);
2904 CREATE TABLE authority.control_set (
2905 id SERIAL PRIMARY KEY,
2906 name TEXT NOT NULL UNIQUE, -- i18n
2907 description TEXT -- i18n
2910 CREATE TABLE authority.control_set_authority_field (
2911 id SERIAL PRIMARY KEY,
2912 main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2913 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2914 tag CHAR(3) NOT NULL,
2915 sf_list TEXT NOT NULL,
2916 name TEXT NOT NULL, -- i18n
2917 description TEXT -- i18n
2920 CREATE TABLE authority.control_set_bib_field (
2921 id SERIAL PRIMARY KEY,
2922 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2923 tag CHAR(3) NOT NULL
2926 CREATE TABLE authority.thesaurus (
2927 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
2928 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2929 name TEXT NOT NULL UNIQUE, -- i18n
2930 description TEXT -- i18n
2933 CREATE TABLE authority.browse_axis (
2934 code TEXT PRIMARY KEY,
2935 name TEXT UNIQUE NOT NULL, -- i18n
2936 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2940 CREATE TABLE authority.browse_axis_authority_field_map (
2941 id SERIAL PRIMARY KEY,
2942 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2943 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
2946 ALTER TABLE authority.record_entry ADD COLUMN control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
2947 ALTER TABLE authority.rec_descriptor DROP COLUMN char_encoding, ADD COLUMN encoding_level TEXT, ADD COLUMN thesaurus TEXT;
2949 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
2950 CREATE OR REPLACE RULE protect_authority_rec_delete AS ON DELETE TO authority.record_entry DO INSTEAD (UPDATE authority.record_entry SET deleted = TRUE WHERE OLD.id = authority.record_entry.id; DELETE FROM authority.full_rec WHERE record = OLD.id);
2952 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
2954 acsaf authority.control_set_authority_field%ROWTYPE;
2962 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
2963 IF thes_code IS NULL THEN
2967 SELECT control_set INTO cset FROM authority.thesaurus WHERE code = thes_code;
2973 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
2974 tag_used := acsaf.tag;
2975 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
2976 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
2977 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
2978 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
2981 EXIT WHEN heading_text <> '';
2984 IF thes_code = 'z' THEN
2985 thes_code := oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml);
2988 IF heading_text <> '' THEN
2989 IF no_thesaurus IS TRUE THEN
2990 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
2992 heading_text := tag_used || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
2995 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
2998 RETURN heading_text;
3000 $func$ LANGUAGE PLPGSQL IMMUTABLE;
3002 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
3003 SELECT authority.normalize_heading($1, TRUE);
3004 $func$ LANGUAGE SQL IMMUTABLE;
3006 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
3007 SELECT authority.normalize_heading($1, FALSE);
3008 $func$ LANGUAGE SQL IMMUTABLE;
3010 CREATE OR REPLACE VIEW authority.tracing_links AS
3011 SELECT main.record AS record,
3013 main.tag AS main_tag,
3014 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
3015 substr(link.value,1,1) AS relationship,
3016 substr(link.value,2,1) AS use_restriction,
3017 substr(link.value,3,1) AS deprecation,
3018 substr(link.value,4,1) AS display_restriction,
3020 link.tag AS link_tag,
3021 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
3022 authority.normalize_heading(are.marc) AS normalized_main_value
3023 FROM authority.full_rec main
3024 JOIN authority.record_entry are ON (main.record = are.id)
3025 JOIN authority.control_set_authority_field main_entry
3026 ON (main_entry.tag = main.tag
3027 AND main_entry.main_entry IS NULL
3028 AND main.subfield = 'a' )
3029 JOIN authority.control_set_authority_field sub_entry
3030 ON (main_entry.id = sub_entry.main_entry)
3031 JOIN authority.full_rec link
3032 ON (link.record = main.record
3033 AND link.tag = sub_entry.tag
3034 AND link.subfield = 'w' );
3036 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
3039 main_entry authority.control_set_authority_field%ROWTYPE;
3040 bib_field authority.control_set_bib_field%ROWTYPE;
3041 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
3042 replace_data XML[] DEFAULT '{}'::XML[];
3043 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
3046 IF auth_id IS NULL THEN
3050 -- Default to the LoC controll set
3051 SELECT COALESCE(control_set,1) INTO cset FROM authority.record_entry WHERE id = auth_id;
3053 FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
3054 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
3055 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
3056 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
3057 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
3058 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
3066 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
3067 XMLELEMENT( name leader, '00881nam a2200193 4500'),
3071 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
3074 XMLATTRIBUTES('r' AS code),
3075 ARRAY_TO_STRING(replace_rules,',')
3080 $f$ STABLE LANGUAGE PLPGSQL;
3082 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
3083 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
3084 $func$ LANGUAGE SQL;
3086 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
3089 use MARC::File::XML (BinaryEncoding => 'UTF-8');
3093 MARC::Charset->assume_unicode(1);
3095 my $target_xml = shift;
3096 my $source_xml = shift;
3097 my $field_spec = shift;
3098 my $force_add = shift || 0;
3100 my $target_r = MARC::Record->new_from_xml( $target_xml );
3101 my $source_r = MARC::Record->new_from_xml( $source_xml );
3103 return $target_xml unless ($target_r && $source_r);
3105 my @field_list = split(',', $field_spec);
3108 for my $f (@field_list) {
3109 $f =~ s/^\s*//; $f =~ s/\s*$//;
3110 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
3116 $match =~ s/^\s*//; $match =~ s/\s*$//;
3117 $fields{$field} = { sf => [ split('', $sf) ] };
3119 my ($msf,$mre) = split('~', $match);
3120 if (length($msf) > 0 and length($mre) > 0) {
3121 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
3122 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
3123 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
3129 for my $f ( keys %fields) {
3130 if ( @{$fields{$f}{sf}} ) {
3131 for my $from_field ($source_r->field( $f )) {
3132 my @tos = $target_r->field( $f );
3134 next if (exists($fields{$f}{match}) and !$force_add);
3135 my @new_fields = map { $_->clone } $source_r->field( $f );
3136 $target_r->insert_fields_ordered( @new_fields );
3138 for my $to_field (@tos) {
3139 if (exists($fields{$f}{match})) {
3140 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
3142 my @new_sf = map { ($_ => $from_field->subfield($_)) } grep { defined($from_field->subfield($_)) } @{$fields{$f}{sf}};
3143 $to_field->add_subfields( @new_sf );
3148 my @new_fields = map { $_->clone } $source_r->field( $f );
3149 $target_r->insert_fields_ordered( @new_fields );
3153 $target_xml = $target_r->as_xml_record;
3154 $target_xml =~ s/^<\?.+?\?>$//mo;
3155 $target_xml =~ s/\n//sgo;
3156 $target_xml =~ s/>\s+</></sgo;
3160 $_$ LANGUAGE PLPERLU;
3163 CREATE INDEX by_heading ON authority.record_entry (authority.simple_normalize_heading(marc)) WHERE deleted IS FALSE or deleted = FALSE;
3165 INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, search_field, facet_field) VALUES
3166 (28, 'identifier', 'authority_id', oils_i18n_gettext(28, 'Authority Record ID', 'cmf', 'label'), 'marcxml', '//marc:datafield/marc:subfield[@code="0"]', FALSE, TRUE);
3168 INSERT INTO config.marc21_rec_type_map (code, type_val, blvl_val) VALUES ('AUT','z',' ');
3169 INSERT INTO config.marc21_rec_type_map (code, type_val, blvl_val) VALUES ('MFHD','uvxy',' ');
3171 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('ELvl', 'ldr', 'AUT', 17, 1, ' ');
3172 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('Subj', '008', 'AUT', 11, 1, '|');
3173 INSERT INTO config.marc21_ff_pos_map (fixed_field, tag, rec_type,start_pos, length, default_val) VALUES ('RecStat', 'ldr', 'AUT', 5, 1, 'n');
3175 INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
3179 FROM config.metabib_field m,
3180 config.index_normalizer i
3181 WHERE i.func = 'remove_paren_substring'
3184 SELECT SETVAL('authority.control_set_id_seq'::TEXT, 100);
3185 SELECT SETVAL('authority.control_set_authority_field_id_seq'::TEXT, 1000);
3186 SELECT SETVAL('authority.control_set_bib_field_id_seq'::TEXT, 1000);
3188 INSERT INTO authority.control_set (id, name, description) VALUES (
3190 oils_i18n_gettext('1','LoC','acs','name'),
3191 oils_i18n_gettext('1','Library of Congress standard authority record control semantics','acs','description')
3194 INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, tag, sf_list, name) VALUES
3197 (1, 1, NULL, '100', 'abcdefklmnopqrstvxyz', oils_i18n_gettext('1','Heading -- Personal Name','acsaf','name')),
3198 (2, 1, NULL, '110', 'abcdefgklmnoprstvxyz', oils_i18n_gettext('2','Heading -- Corporate Name','acsaf','name')),
3199 (3, 1, NULL, '111', 'acdefgklnpqstvxyz', oils_i18n_gettext('3','Heading -- Meeting Name','acsaf','name')),
3200 (4, 1, NULL, '130', 'adfgklmnoprstvxyz', oils_i18n_gettext('4','Heading -- Uniform Title','acsaf','name')),
3201 (5, 1, NULL, '150', 'abvxyz', oils_i18n_gettext('5','Heading -- Topical Term','acsaf','name')),
3202 (6, 1, NULL, '151', 'avxyz', oils_i18n_gettext('6','Heading -- Geographic Name','acsaf','name')),
3203 (7, 1, NULL, '155', 'avxyz', oils_i18n_gettext('7','Heading -- Genre/Form Term','acsaf','name')),
3204 (8, 1, NULL, '180', 'vxyz', oils_i18n_gettext('8','Heading -- General Subdivision','acsaf','name')),
3205 (9, 1, NULL, '181', 'vxyz', oils_i18n_gettext('9','Heading -- Geographic Subdivision','acsaf','name')),
3206 (10, 1, NULL, '182', 'vxyz', oils_i18n_gettext('10','Heading -- Chronological Subdivision','acsaf','name')),
3207 (11, 1, NULL, '185', 'vxyz', oils_i18n_gettext('11','Heading -- Form Subdivision','acsaf','name')),
3208 (12, 1, NULL, '148', 'avxyz', oils_i18n_gettext('12','Heading -- Chronological Term','acsaf','name')),
3210 -- See Also From tracings
3211 (21, 1, 1, '500', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('21','See Also From Tracing -- Personal Name','acsaf','name')),
3212 (22, 1, 2, '510', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('22','See Also From Tracing -- Corporate Name','acsaf','name')),
3213 (23, 1, 3, '511', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('23','See Also From Tracing -- Meeting Name','acsaf','name')),
3214 (24, 1, 4, '530', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('24','See Also From Tracing -- Uniform Title','acsaf','name')),
3215 (25, 1, 5, '550', 'abivwxyz4', oils_i18n_gettext('25','See Also From Tracing -- Topical Term','acsaf','name')),
3216 (26, 1, 6, '551', 'aivwxyz4', oils_i18n_gettext('26','See Also From Tracing -- Geographic Name','acsaf','name')),
3217 (27, 1, 7, '555', 'aivwxyz4', oils_i18n_gettext('27','See Also From Tracing -- Genre/Form Term','acsaf','name')),
3218 (28, 1, 8, '580', 'ivwxyz4', oils_i18n_gettext('28','See Also From Tracing -- General Subdivision','acsaf','name')),
3219 (29, 1, 9, '581', 'ivwxyz4', oils_i18n_gettext('29','See Also From Tracing -- Geographic Subdivision','acsaf','name')),
3220 (30, 1, 10, '582', 'ivwxyz4', oils_i18n_gettext('30','See Also From Tracing -- Chronological Subdivision','acsaf','name')),
3221 (31, 1, 11, '585', 'ivwxyz4', oils_i18n_gettext('31','See Also From Tracing -- Form Subdivision','acsaf','name')),
3222 (32, 1, 12, '548', 'aivwxyz4', oils_i18n_gettext('32','See Also From Tracing -- Chronological Term','acsaf','name')),
3225 (41, 1, 1, '700', 'abcdefghjklmnopqrstvwxyz25', oils_i18n_gettext('41','Established Heading Linking Entry -- Personal Name','acsaf','name')),
3226 (42, 1, 2, '710', 'abcdefghklmnoprstvwxyz25', oils_i18n_gettext('42','Established Heading Linking Entry -- Corporate Name','acsaf','name')),
3227 (43, 1, 3, '711', 'acdefghklnpqstvwxyz25', oils_i18n_gettext('43','Established Heading Linking Entry -- Meeting Name','acsaf','name')),
3228 (44, 1, 4, '730', 'adfghklmnoprstvwxyz25', oils_i18n_gettext('44','Established Heading Linking Entry -- Uniform Title','acsaf','name')),
3229 (45, 1, 5, '750', 'abvwxyz25', oils_i18n_gettext('45','Established Heading Linking Entry -- Topical Term','acsaf','name')),
3230 (46, 1, 6, '751', 'avwxyz25', oils_i18n_gettext('46','Established Heading Linking Entry -- Geographic Name','acsaf','name')),
3231 (47, 1, 7, '755', 'avwxyz25', oils_i18n_gettext('47','Established Heading Linking Entry -- Genre/Form Term','acsaf','name')),
3232 (48, 1, 8, '780', 'vwxyz25', oils_i18n_gettext('48','Subdivision Linking Entry -- General Subdivision','acsaf','name')),
3233 (49, 1, 9, '781', 'vwxyz25', oils_i18n_gettext('49','Subdivision Linking Entry -- Geographic Subdivision','acsaf','name')),
3234 (50, 1, 10, '782', 'vwxyz25', oils_i18n_gettext('50','Subdivision Linking Entry -- Chronological Subdivision','acsaf','name')),
3235 (51, 1, 11, '785', 'vwxyz25', oils_i18n_gettext('51','Subdivision Linking Entry -- Form Subdivision','acsaf','name')),
3236 (52, 1, 12, '748', 'avwxyz25', oils_i18n_gettext('52','Established Heading Linking Entry -- Chronological Term','acsaf','name')),
3238 -- See From tracings
3239 (61, 1, 1, '400', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('61','See Also Tracing -- Personal Name','acsaf','name')),
3240 (62, 1, 2, '410', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('62','See Also Tracing -- Corporate Name','acsaf','name')),
3241 (63, 1, 3, '411', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('63','See Also Tracing -- Meeting Name','acsaf','name')),
3242 (64, 1, 4, '430', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('64','See Also Tracing -- Uniform Title','acsaf','name')),
3243 (65, 1, 5, '450', 'abivwxyz4', oils_i18n_gettext('65','See Also Tracing -- Topical Term','acsaf','name')),
3244 (66, 1, 6, '451', 'aivwxyz4', oils_i18n_gettext('66','See Also Tracing -- Geographic Name','acsaf','name')),
3245 (67, 1, 7, '455', 'aivwxyz4', oils_i18n_gettext('67','See Also Tracing -- Genre/Form Term','acsaf','name')),
3246 (68, 1, 8, '480', 'ivwxyz4', oils_i18n_gettext('68','See Also Tracing -- General Subdivision','acsaf','name')),
3247 (69, 1, 9, '481', 'ivwxyz4', oils_i18n_gettext('69','See Also Tracing -- Geographic Subdivision','acsaf','name')),
3248 (70, 1, 10, '482', 'ivwxyz4', oils_i18n_gettext('70','See Also Tracing -- Chronological Subdivision','acsaf','name')),
3249 (71, 1, 11, '485', 'ivwxyz4', oils_i18n_gettext('71','See Also Tracing -- Form Subdivision','acsaf','name')),
3250 (72, 1, 12, '448', 'aivwxyz4', oils_i18n_gettext('72','See Also Tracing -- Chronological Term','acsaf','name'));
3252 INSERT INTO authority.browse_axis (code,name,description,sorter) VALUES
3253 ('title','Title','Title axis','titlesort'),
3254 ('author','Author','Author axis','titlesort'),
3255 ('subject','Subject','Subject axis','titlesort'),
3256 ('topic','Topic','Topic Subject axis','titlesort');
3258 INSERT INTO authority.browse_axis_authority_field_map (axis,field) VALUES
3269 INSERT INTO authority.control_set_bib_field (tag, authority_field)
3270 SELECT '100', id FROM authority.control_set_authority_field WHERE tag IN ('100')
3272 SELECT '600', id FROM authority.control_set_authority_field WHERE tag IN ('100','180','181','182','185')
3274 SELECT '700', id FROM authority.control_set_authority_field WHERE tag IN ('100')
3276 SELECT '800', id FROM authority.control_set_authority_field WHERE tag IN ('100')
3279 SELECT '110', id FROM authority.control_set_authority_field WHERE tag IN ('110')
3281 SELECT '610', id FROM authority.control_set_authority_field WHERE tag IN ('110')
3283 SELECT '710', id FROM authority.control_set_authority_field WHERE tag IN ('110')
3285 SELECT '810', id FROM authority.control_set_authority_field WHERE tag IN ('110')
3288 SELECT '111', id FROM authority.control_set_authority_field WHERE tag IN ('111')
3290 SELECT '611', id FROM authority.control_set_authority_field WHERE tag IN ('111')
3292 SELECT '711', id FROM authority.control_set_authority_field WHERE tag IN ('111')
3294 SELECT '811', id FROM authority.control_set_authority_field WHERE tag IN ('111')
3297 SELECT '130', id FROM authority.control_set_authority_field WHERE tag IN ('130')
3299 SELECT '240', id FROM authority.control_set_authority_field WHERE tag IN ('130')
3301 SELECT '630', id FROM authority.control_set_authority_field WHERE tag IN ('130')
3303 SELECT '730', id FROM authority.control_set_authority_field WHERE tag IN ('130')
3305 SELECT '830', id FROM authority.control_set_authority_field WHERE tag IN ('130')
3308 SELECT '648', id FROM authority.control_set_authority_field WHERE tag IN ('148')
3311 SELECT '650', id FROM authority.control_set_authority_field WHERE tag IN ('150','180','181','182','185')
3313 SELECT '651', id FROM authority.control_set_authority_field WHERE tag IN ('151','180','181','182','185')
3315 SELECT '655', id FROM authority.control_set_authority_field WHERE tag IN ('155','180','181','182','185')
3318 INSERT INTO authority.thesaurus (code, name, control_set) VALUES
3319 ('a', oils_i18n_gettext('a','Library of Congress Subject Headings','at','name'), 1),
3320 ('b', oils_i18n_gettext('b',$$LC subject headings for children's literature$$,'at','name'), 1), -- silly vim '
3321 ('c', oils_i18n_gettext('c','Medical Subject Headings','at','name'), 1),
3322 ('d', oils_i18n_gettext('d','National Agricultural Library subject authority file','at','name'), 1),
3323 ('k', oils_i18n_gettext('k','Canadian Subject Headings','at','name'), 1),
3324 ('n', oils_i18n_gettext('n','Not applicable','at','name'), 1),
3325 ('r', oils_i18n_gettext('r','Art and Architecture Thesaurus','at','name'), 1),
3326 ('s', oils_i18n_gettext('s','Sears List of Subject Headings','at','name'), 1),
3327 ('v', oils_i18n_gettext('v','Repertoire de vedettes-matiere','at','name'), 1),
3328 ('z', oils_i18n_gettext('z','Other','at','name'), 1),
3329 ('|', oils_i18n_gettext('|','No attempt to code','at','name'), 1);
3331 CREATE OR REPLACE FUNCTION authority.map_thesaurus_to_control_set () RETURNS TRIGGER AS $func$
3333 IF NEW.control_set IS NULL THEN
3334 SELECT control_set INTO NEW.control_set
3335 FROM authority.thesaurus
3336 WHERE vandelay.marc21_extract_fixed_field(NEW.marc,'Subj') = code;
3341 $func$ LANGUAGE PLPGSQL;
3343 CREATE TRIGGER map_thesaurus_to_control_set BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.map_thesaurus_to_control_set ();
3345 CREATE OR REPLACE FUNCTION authority.reingest_authority_rec_descriptor( auth_id BIGINT ) RETURNS VOID AS $func$
3347 DELETE FROM authority.rec_descriptor WHERE record = auth_id;
3348 INSERT INTO authority.rec_descriptor (record, record_status, encoding_level, thesaurus)
3350 vandelay.marc21_extract_fixed_field(marc,'RecStat'),
3351 vandelay.marc21_extract_fixed_field(marc,'ELvl'),
3352 vandelay.marc21_extract_fixed_field(marc,'Subj')
3353 FROM authority.record_entry
3357 $func$ LANGUAGE PLPGSQL;
3359 CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
3362 IF NEW.deleted IS TRUE THEN -- If this authority is deleted
3363 DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
3364 DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
3365 -- Should remove matching $0 from controlled fields at the same time?
3366 RETURN NEW; -- and we're done
3369 IF TG_OP = 'UPDATE' THEN -- re-ingest?
3370 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
3372 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
3375 -- Propagate these updates to any linked bib records
3376 PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id;
3379 -- Flatten and insert the afr data
3380 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
3382 PERFORM authority.reingest_authority_full_rec(NEW.id);
3383 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
3385 PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
3391 $func$ LANGUAGE PLPGSQL;
3394 -- Evergreen DB patch 0577.schema.vandelay-item-import-copy-loc-ancestors.sql
3396 -- Ingest items copy location inheritance
3399 -- check whether patch can be applied
3400 SELECT evergreen.upgrade_deps_block_check('0577', :eg_version); -- berick
3402 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
3413 deposit_amount TEXT;
3426 tmp_attr_set RECORD;
3427 attr_set vandelay.import_item%ROWTYPE;
3433 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
3437 attr_set.definition := attr_def.id;
3439 -- Build the combined XPath
3443 WHEN attr_def.owning_lib IS NULL THEN 'null()'
3444 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
3445 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
3450 WHEN attr_def.circ_lib IS NULL THEN 'null()'
3451 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
3452 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
3457 WHEN attr_def.call_number IS NULL THEN 'null()'
3458 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
3459 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
3464 WHEN attr_def.copy_number IS NULL THEN 'null()'
3465 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
3466 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
3471 WHEN attr_def.status IS NULL THEN 'null()'
3472 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
3473 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
3478 WHEN attr_def.location IS NULL THEN 'null()'
3479 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
3480 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
3485 WHEN attr_def.circulate IS NULL THEN 'null()'
3486 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
3487 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
3492 WHEN attr_def.deposit IS NULL THEN 'null()'
3493 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
3494 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
3499 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
3500 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
3501 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
3506 WHEN attr_def.ref IS NULL THEN 'null()'
3507 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
3508 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
3513 WHEN attr_def.holdable IS NULL THEN 'null()'
3514 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
3515 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
3520 WHEN attr_def.price IS NULL THEN 'null()'
3521 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
3522 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
3527 WHEN attr_def.barcode IS NULL THEN 'null()'
3528 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
3529 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
3534 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
3535 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
3536 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
3541 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
3542 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
3543 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
3548 WHEN attr_def.alert_message IS NULL THEN 'null()'
3549 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
3550 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
3555 WHEN attr_def.opac_visible IS NULL THEN 'null()'
3556 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
3557 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
3562 WHEN attr_def.pub_note IS NULL THEN 'null()'
3563 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
3564 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
3568 WHEN attr_def.priv_note IS NULL THEN 'null()'
3569 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
3570 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
3575 owning_lib || '|' ||
3577 call_number || '|' ||
3578 copy_number || '|' ||
3583 deposit_amount || '|' ||
3588 circ_modifier || '|' ||
3589 circ_as_type || '|' ||
3590 alert_message || '|' ||
3595 -- RAISE NOTICE 'XPath: %', xpath;
3599 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
3600 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
3601 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
3602 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
3605 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
3606 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
3608 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
3609 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
3611 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
3612 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
3613 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
3616 -- search up the org unit tree for a matching copy location
3618 WITH RECURSIVE anscestor_depth AS (
3622 FROM actor.org_unit ou
3623 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
3624 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
3629 FROM actor.org_unit ou
3630 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
3631 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
3632 ) SELECT cpl.id INTO attr_set.location
3633 FROM anscestor_depth a
3634 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
3635 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
3636 ORDER BY a.depth DESC
3639 attr_set.circulate :=
3640 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
3641 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
3644 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
3645 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
3647 attr_set.holdable :=
3648 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
3649 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
3651 attr_set.opac_visible :=
3652 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
3653 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
3656 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
3657 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
3659 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
3660 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
3661 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
3663 attr_set.call_number := tmp_attr_set.cn; -- TEXT
3664 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
3665 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
3666 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
3667 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
3668 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
3669 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
3670 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
3672 RETURN NEXT attr_set;
3681 $$ LANGUAGE PLPGSQL;
3684 -- Evergreen DB patch XXXX.data.org-setting-ui.circ.billing.uncheck_bills_and_unfocus_payment_box.sql
3686 -- New org setting ui.circ.billing.uncheck_bills_and_unfocus_payment_box
3689 -- check whether patch can be applied
3690 SELECT evergreen.upgrade_deps_block_check('0584', :eg_version);
3692 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
3694 'ui.circ.billing.uncheck_bills_and_unfocus_payment_box',
3696 'ui.circ.billing.uncheck_bills_and_unfocus_payment_box',
3697 'GUI: Uncheck bills by default in the patron billing interface',
3702 'ui.circ.billing.uncheck_bills_and_unfocus_payment_box',
3703 'Uncheck bills by default in the patron billing interface,'
3704 || ' and focus on the Uncheck All button instead of the'
3705 || ' Payment Received field.',
3713 -- check whether patch can be applied
3714 SELECT evergreen.upgrade_deps_block_check('0585', :eg_version);
3716 INSERT into config.org_unit_setting_type
3717 ( name, label, description, datatype ) VALUES
3718 ( 'circ.checkout_fills_related_hold_exact_match_only',
3719 'Checkout Fills Related Hold On Valid Copy Only',
3720 'When filling related holds on checkout only match on items that are valid for opportunistic capture for the hold. Without this set a Title or Volume hold could match when the item is not holdable. With this set only holdable items will match.',
3724 -- check whether patch can be applied
3725 SELECT evergreen.upgrade_deps_block_check('0586', :eg_version);
3727 INSERT INTO permission.perm_list (id, code, description) VALUES (
3732 'Allows a user to authenticate and get a long-lived session (length configured in opensrf.xml)',
3738 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
3740 pgt.id, perm.id, aout.depth, FALSE
3742 permission.grp_tree pgt,
3743 permission.perm_list perm,
3744 actor.org_unit_type aout
3746 pgt.name = 'Users' AND
3747 aout.name = 'Consortium' AND
3748 perm.code = 'PERSISTENT_LOGIN';
3751 \qecho If this transaction succeeded, your users (staff and patrons) now have
3752 \qecho the PERSISTENT_LOGIN permission by default.
3756 -- Evergreen DB patch XXXX.data.org-setting-circ.offline.skip_foo_if_newer_status_changed_time.sql
3758 -- New org setting circ.offline.skip_checkout_if_newer_status_changed_time
3759 -- New org setting circ.offline.skip_renew_if_newer_status_changed_time
3760 -- New org setting circ.offline.skip_checkin_if_newer_status_changed_time
3763 -- check whether patch can be applied
3764 SELECT evergreen.upgrade_deps_block_check('0593', :eg_version);
3766 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
3768 'circ.offline.skip_checkout_if_newer_status_changed_time',
3770 'circ.offline.skip_checkout_if_newer_status_changed_time',
3771 'Offline: Skip offline checkout if newer item Status Changed Time.',
3776 'circ.offline.skip_checkout_if_newer_status_changed_time',
3777 'Skip offline checkout transaction (raise exception when'
3778 || ' processing) if item Status Changed Time is newer than the'
3779 || ' recorded transaction time. WARNING: The Reshelving to'
3780 || ' Available status rollover will trigger this.',
3786 'circ.offline.skip_renew_if_newer_status_changed_time',
3788 'circ.offline.skip_renew_if_newer_status_changed_time',
3789 'Offline: Skip offline renewal if newer item Status Changed Time.',
3794 'circ.offline.skip_renew_if_newer_status_changed_time',
3795 'Skip offline renewal transaction (raise exception when'
3796 || ' processing) if item Status Changed Time is newer than the'
3797 || ' recorded transaction time. WARNING: The Reshelving to'
3798 || ' Available status rollover will trigger this.',
3804 'circ.offline.skip_checkin_if_newer_status_changed_time',
3806 'circ.offline.skip_checkin_if_newer_status_changed_time',
3807 'Offline: Skip offline checkin if newer item Status Changed Time.',
3812 'circ.offline.skip_checkin_if_newer_status_changed_time',
3813 'Skip offline checkin transaction (raise exception when'
3814 || ' processing) if item Status Changed Time is newer than the'
3815 || ' recorded transaction time. WARNING: The Reshelving to'
3816 || ' Available status rollover will trigger this.',
3823 -- Evergreen DB patch YYYY.schema.acp_status_date_changed.sql
3825 -- Change trigger which updates copy status_changed_time to ignore the
3826 -- Reshelving->Available status rollover
3828 -- FIXME: 0039.schema.acp_status_date_changed.sql defines this the first time
3829 -- around, but along with the column itself, etc. And it gets modified with
3830 -- 0562.schema.copy_active_date.sql. Not sure how to use the supercedes /
3831 -- deprecate stuff for upgrade scripts, if it's even applicable when a given
3832 -- upgrade script is doing so much.
3834 -- check whether patch can be applied
3835 SELECT evergreen.upgrade_deps_block_check('0594', :eg_version);
3837 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
3838 RETURNS TRIGGER AS $$
3840 IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
3841 NEW.status_changed_time := now();
3842 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
3843 NEW.active_date := now();
3848 $$ LANGUAGE plpgsql;
3850 -- Evergreen DB patch 0595.data.org-setting-ui.patron_search.result_cap.sql
3852 -- New org setting ui.patron_search.result_cap
3855 -- check whether patch can be applied
3856 SELECT evergreen.upgrade_deps_block_check('0595', :eg_version);
3858 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype )
3860 'ui.patron_search.result_cap',
3862 'ui.patron_search.result_cap',
3863 'GUI: Cap results in Patron Search at this number.',
3868 'ui.patron_search.result_cap',
3869 'So for example, if you search for John Doe, normally you would get'
3870 || ' at most 50 results. This setting allows you to raise or lower'
3878 -- Evergreen DB patch 0596.schema.vandelay-item-import-error-detail.sql
3880 -- check whether patch can be applied
3881 SELECT evergreen.upgrade_deps_block_check('0596', :eg_version);
3883 INSERT INTO vandelay.import_error ( code, description ) VALUES (
3884 'import.item.invalid.status', oils_i18n_gettext('import.item.invalid.status', 'Invalid value for "status"', 'vie', 'description') );
3885 INSERT INTO vandelay.import_error ( code, description ) VALUES (
3886 'import.item.invalid.price', oils_i18n_gettext('import.item.invalid.price', 'Invalid value for "price"', 'vie', 'description') );
3887 INSERT INTO vandelay.import_error ( code, description ) VALUES (
3888 'import.item.invalid.deposit_amount', oils_i18n_gettext('import.item.invalid.deposit_amount', 'Invalid value for "deposit_amount"', 'vie', 'description') );
3889 INSERT INTO vandelay.import_error ( code, description ) VALUES (
3890 'import.item.invalid.owning_lib', oils_i18n_gettext('import.item.invalid.owning_lib', 'Invalid value for "owning_lib"', 'vie', 'description') );
3891 INSERT INTO vandelay.import_error ( code, description ) VALUES (
3892 'import.item.invalid.circ_lib', oils_i18n_gettext('import.item.invalid.circ_lib', 'Invalid value for "circ_lib"', 'vie', 'description') );
3893 INSERT INTO vandelay.import_error ( code, description ) VALUES (
3894 'import.item.invalid.copy_number', oils_i18n_gettext('import.item.invalid.copy_number', 'Invalid value for "copy_number"', 'vie', 'description') );
3895 INSERT INTO vandelay.import_error ( code, description ) VALUES (
3896 'import.item.invalid.circ_as_type', oils_i18n_gettext('import.item.invalid.circ_as_type', 'Invalid value for "circ_as_type"', 'vie', 'description') );
3898 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
3909 deposit_amount TEXT;
3922 tmp_attr_set RECORD;
3923 attr_set vandelay.import_item%ROWTYPE;
3930 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
3934 attr_set.definition := attr_def.id;
3936 -- Build the combined XPath
3940 WHEN attr_def.owning_lib IS NULL THEN 'null()'
3941 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
3942 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
3947 WHEN attr_def.circ_lib IS NULL THEN 'null()'
3948 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
3949 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
3954 WHEN attr_def.call_number IS NULL THEN 'null()'
3955 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
3956 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
3961 WHEN attr_def.copy_number IS NULL THEN 'null()'
3962 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
3963 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
3968 WHEN attr_def.status IS NULL THEN 'null()'
3969 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
3970 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
3975 WHEN attr_def.location IS NULL THEN 'null()'
3976 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
3977 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
3982 WHEN attr_def.circulate IS NULL THEN 'null()'
3983 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
3984 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
3989 WHEN attr_def.deposit IS NULL THEN 'null()'
3990 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
3991 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
3996 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
3997 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
3998 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
4003 WHEN attr_def.ref IS NULL THEN 'null()'
4004 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
4005 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
4010 WHEN attr_def.holdable IS NULL THEN 'null()'
4011 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
4012 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
4017 WHEN attr_def.price IS NULL THEN 'null()'
4018 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
4019 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
4024 WHEN attr_def.barcode IS NULL THEN 'null()'
4025 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
4026 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
4031 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
4032 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
4033 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
4038 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
4039 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
4040 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
4045 WHEN attr_def.alert_message IS NULL THEN 'null()'
4046 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
4047 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
4052 WHEN attr_def.opac_visible IS NULL THEN 'null()'
4053 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
4054 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
4059 WHEN attr_def.pub_note IS NULL THEN 'null()'
4060 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
4061 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
4065 WHEN attr_def.priv_note IS NULL THEN 'null()'
4066 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
4067 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
4072 owning_lib || '|' ||
4074 call_number || '|' ||
4075 copy_number || '|' ||
4080 deposit_amount || '|' ||
4085 circ_modifier || '|' ||
4086 circ_as_type || '|' ||
4087 alert_message || '|' ||
4094 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
4095 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
4096 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
4097 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
4100 attr_set.import_error := NULL;
4101 attr_set.error_detail := NULL;
4102 attr_set.deposit_amount := NULL;
4103 attr_set.copy_number := NULL;
4104 attr_set.price := NULL;
4106 IF tmp_attr_set.pr != '' THEN
4107 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
4108 IF tmp_str = '' THEN
4109 attr_set.import_error := 'import.item.invalid.price';
4110 attr_set.error_detail := tmp_attr_set.pr; -- original value
4111 RETURN NEXT attr_set; CONTINUE;
4113 attr_set.price := tmp_str::NUMERIC(8,2);
4116 IF tmp_attr_set.dep_amount != '' THEN
4117 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
4118 IF tmp_str = '' THEN
4119 attr_set.import_error := 'import.item.invalid.deposit_amount';
4120 attr_set.error_detail := tmp_attr_set.dep_amount;
4121 RETURN NEXT attr_set; CONTINUE;
4123 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
4126 IF tmp_attr_set.cnum != '' THEN
4127 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
4128 IF tmp_str = '' THEN
4129 attr_set.import_error := 'import.item.invalid.copy_number';
4130 attr_set.error_detail := tmp_attr_set.cnum;
4131 RETURN NEXT attr_set; CONTINUE;
4133 attr_set.copy_number := tmp_str::INT;
4136 IF tmp_attr_set.ol != '' THEN
4137 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
4139 attr_set.import_error := 'import.item.invalid.owning_lib';
4140 attr_set.error_detail := tmp_attr_set.ol;
4141 RETURN NEXT attr_set; CONTINUE;
4145 IF tmp_attr_set.clib != '' THEN
4146 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
4148 attr_set.import_error := 'import.item.invalid.circ_lib';
4149 attr_set.error_detail := tmp_attr_set.clib;
4150 RETURN NEXT attr_set; CONTINUE;
4154 IF tmp_attr_set.cs != '' THEN
4155 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
4157 attr_set.import_error := 'import.item.invalid.status';
4158 attr_set.error_detail := tmp_attr_set.cs;
4159 RETURN NEXT attr_set; CONTINUE;
4163 IF tmp_attr_set.circ_mod != '' THEN
4164 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
4166 attr_set.import_error := 'import.item.invalid.circ_modifier';
4167 attr_set.error_detail := tmp_attr_set.circ_mod;
4168 RETURN NEXT attr_set; CONTINUE;
4172 IF tmp_attr_set.circ_as != '' THEN
4173 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
4175 attr_set.import_error := 'import.item.invalid.circ_as_type';
4176 attr_set.error_detail := tmp_attr_set.circ_as;
4177 RETURN NEXT attr_set; CONTINUE;
4181 IF tmp_attr_set.cl != '' THEN
4183 -- search up the org unit tree for a matching copy location
4184 WITH RECURSIVE anscestor_depth AS (
4188 FROM actor.org_unit ou
4189 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
4190 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
4195 FROM actor.org_unit ou
4196 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
4197 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
4198 ) SELECT cpl.id INTO attr_set.location
4199 FROM anscestor_depth a
4200 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
4201 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
4202 ORDER BY a.depth DESC
4206 attr_set.import_error := 'import.item.invalid.location';
4207 attr_set.error_detail := tmp_attr_set.cs;
4208 RETURN NEXT attr_set; CONTINUE;
4212 attr_set.circulate :=
4213 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
4214 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
4217 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
4218 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
4220 attr_set.holdable :=
4221 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
4222 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
4224 attr_set.opac_visible :=
4225 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
4226 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
4229 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
4230 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
4232 attr_set.call_number := tmp_attr_set.cn; -- TEXT
4233 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
4234 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
4235 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
4236 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
4237 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
4239 RETURN NEXT attr_set;
4248 $$ LANGUAGE PLPGSQL;
4250 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
4253 item_data vandelay.import_item%ROWTYPE;
4256 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
4260 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
4262 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
4263 INSERT INTO vandelay.import_item (
4289 item_data.definition,
4290 item_data.owning_lib,
4292 item_data.call_number,
4293 item_data.copy_number,
4296 item_data.circulate,
4298 item_data.deposit_amount,
4303 item_data.circ_modifier,
4304 item_data.circ_as_type,
4305 item_data.alert_message,
4307 item_data.priv_note,
4308 item_data.opac_visible,
4309 item_data.import_error,
4310 item_data.error_detail
4316 $func$ LANGUAGE PLPGSQL;
4318 -- Evergreen DB patch XXXX.schema.vandelay.bib_match_isxn_caseless.sql
4321 -- check whether patch can be applied
4322 SELECT evergreen.upgrade_deps_block_check('0597', :eg_version);
4324 CREATE INDEX metabib_full_rec_isxn_caseless_idx
4325 ON metabib.real_full_rec (LOWER(value))
4326 WHERE tag IN ('020', '022', '024');
4329 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
4331 ) RETURNS HSTORE AS $$
4335 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
4341 CASE WHEN tag IN ('020', '022', '024') THEN -- caseless
4342 ARRAY_ACCUM(LOWER(value))::TEXT
4344 ARRAY_ACCUM(value)::TEXT
4346 FROM vandelay.flatten_marc(record_xml)
4347 GROUP BY tag, subfield ORDER BY tag, subfield
4351 $$ LANGUAGE PLPGSQL;
4353 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
4354 node vandelay.match_set_point
4355 ) RETURNS VOID AS $$
4363 -- remember $1 is tags_rstore, and $2 is svf_rstore
4373 IF node.tag IS NOT NULL THEN
4374 caseless := (node.tag IN ('020', '022', '024'));
4376 IF node.subfield IS NOT NULL THEN
4377 tagkey := tagkey || node.subfield;
4381 my_alias := 'n' || node.id::TEXT;
4383 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
4384 ' AS quality FROM metabib.';
4385 IF node.tag IS NOT NULL THEN
4386 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
4387 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
4389 IF node.subfield IS NOT NULL THEN
4390 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
4391 node.subfield || '''';
4393 jrow := jrow || ' AND (';
4396 jrow := jrow || 'LOWER(' || my_alias || '.value) ' || op;
4398 jrow := jrow || my_alias || '.value ' || op;
4401 jrow := jrow || ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
4403 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
4404 my_alias || '.id = bre.id AND (' ||
4405 my_alias || '.attrs->''' || node.svf ||
4406 ''' ' || op || ' $2->''' || node.svf || '''))';
4408 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
4410 $$ LANGUAGE PLPGSQL;
4412 -- Evergreen DB patch 0598.schema.vandelay_one_match_per.sql
4416 -- check whether patch can be applied
4417 SELECT evergreen.upgrade_deps_block_check('0598', :eg_version);
4419 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
4420 match_set_id INTEGER, record_xml TEXT
4421 ) RETURNS SETOF vandelay.match_set_test_result AS $$
4432 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
4433 svf_rstore := vandelay.extract_rec_attrs(record_xml);
4435 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
4436 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
4438 -- generate the where clause and return that directly (into wq), and as
4439 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
4440 wq := vandelay.get_expr_from_match_set(match_set_id);
4442 query_ := 'SELECT DISTINCT(bre.id) AS record, ';
4444 -- qrows table is for the quality bits we add to the SELECT clause
4445 SELECT ARRAY_TO_STRING(
4446 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
4447 ) INTO coal FROM _vandelay_tmp_qrows;
4449 -- our query string so far is the SELECT clause and the inital FROM.
4450 -- no JOINs yet nor the WHERE clause
4451 query_ := query_ || coal || ' AS quality ' || E'\n' ||
4452 'FROM biblio.record_entry bre ';
4454 -- jrows table is for the joins we must make (and the real text conditions)
4455 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
4456 FROM _vandelay_tmp_jrows;
4458 -- add those joins and the where clause to our query.
4459 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
4461 -- this will return rows of record,quality
4462 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
4466 DROP TABLE _vandelay_tmp_qrows;
4467 DROP TABLE _vandelay_tmp_jrows;
4471 $$ LANGUAGE PLPGSQL;
4473 -- Evergreen DB patch 0606.schema.czs_use_perm_column.sql
4475 -- This adds a column to config.z3950_source called use_perm.
4476 -- The idea is that if a permission is set for a given source,
4477 -- then staff will need the referenced permission to use that
4481 -- check whether patch can be applied
4482 SELECT evergreen.upgrade_deps_block_check('0606', :eg_version);
4484 ALTER TABLE config.z3950_source
4485 ADD COLUMN use_perm INT REFERENCES permission.perm_list (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
4487 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
4488 If set, this permission is required for the source to be listed in the staff
4489 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
4492 -- Evergreen DB patch 0608.data.vandelay-export-error-match-info.sql
4497 -- check whether patch can be applied
4498 SELECT evergreen.upgrade_deps_block_check('0608', :eg_version);
4500 -- Add vqbr.import_error, vqbr.error_detail, and vqbr.matches.size to queue print output
4502 UPDATE action_trigger.event_definition SET template = $$
4505 Queue ID: [% target.0.queue.id %]
4506 Queue Name: [% target.0.queue.name %]
4507 Queue Type: [% target.0.queue.queue_type %]
4508 Complete? [% target.0.queue.complete %]
4510 [% FOR vqbr IN target %]
4512 Title of work | [% helpers.get_queued_bib_attr('title',vqbr.attributes) %]
4513 Author of work | [% helpers.get_queued_bib_attr('author',vqbr.attributes) %]
4514 Language of work | [% helpers.get_queued_bib_attr('language',vqbr.attributes) %]
4515 Pagination | [% helpers.get_queued_bib_attr('pagination',vqbr.attributes) %]
4516 ISBN | [% helpers.get_queued_bib_attr('isbn',vqbr.attributes) %]
4517 ISSN | [% helpers.get_queued_bib_attr('issn',vqbr.attributes) %]
4518 Price | [% helpers.get_queued_bib_attr('price',vqbr.attributes) %]
4519 Accession Number | [% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) %]
4520 TCN Value | [% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) %]
4521 TCN Source | [% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) %]
4522 Internal ID | [% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) %]
4523 Publisher | [% helpers.get_queued_bib_attr('publisher',vqbr.attributes) %]
4524 Publication Date | [% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) %]
4525 Edition | [% helpers.get_queued_bib_attr('edition',vqbr.attributes) %]
4526 Item Barcode | [% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) %]
4527 Import Error | [% vqbr.import_error %]
4528 Error Detail | [% vqbr.error_detail %]
4529 Match Count | [% vqbr.matches.size %]
4537 -- Do the same for the CVS version
4539 UPDATE action_trigger.event_definition SET template = $$
4541 "Title of work","Author of work","Language of work","Pagination","ISBN","ISSN","Price","Accession Number","TCN Value","TCN Source","Internal ID","Publisher","Publication Date","Edition","Item Barcode","Import Error","Error Detail","Match Count"
4542 [% FOR vqbr IN target %]"[% helpers.get_queued_bib_attr('title',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('author',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('language',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('pagination',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('isbn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('issn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('price',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('rec_identifier',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_tcn',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_tcn_source',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('eg_identifier',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('publisher',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('pubdate',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('edition',vqbr.attributes) | replace('"', '""') %]","[% helpers.get_queued_bib_attr('item_barcode',vqbr.attributes) | replace('"', '""') %]","[% vqbr.import_error | replace('"', '""') %]","[% vqbr.error_detail | replace('"', '""') %]","[% vqbr.matches.size %]"
4547 -- Add matches to the env for both
4548 INSERT INTO action_trigger.environment (event_def, path) VALUES (39, 'matches');
4549 INSERT INTO action_trigger.environment (event_def, path) VALUES (40, 'matches');
4552 -- Evergreen DB patch XXXX.data.acq-copy-creator-from-receiver.sql
4554 -- check whether patch can be applied
4555 SELECT evergreen.upgrade_deps_block_check('0609', :eg_version);
4557 ALTER TABLE acq.lineitem_detail
4558 ADD COLUMN receiver INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED;
4561 -- Evergreen DB patch XXXX.data.acq-copy-creator-from-receiver.sql
4563 -- check whether patch can be applied
4564 SELECT evergreen.upgrade_deps_block_check('0610', :eg_version);
4566 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES (
4567 'acq.copy_creator_uses_receiver',
4569 'acq.copy_creator_uses_receiver',
4570 'Acq: Set copy creator as receiver',
4575 'acq.copy_creator_uses_receiver',
4576 'When receiving a copy in acquisitions, set the copy "creator" to be the staff that received the copy',
4583 -- Evergreen DB patch 0611.data.magic_macros.sql
4585 -- check whether patch can be applied
4586 SELECT evergreen.upgrade_deps_block_check('0611', :eg_version);
4588 INSERT into config.org_unit_setting_type
4589 ( name, label, description, datatype ) VALUES
4591 'circ.staff_client.receipt.header_text',
4593 'circ.staff_client.receipt.header_text',
4594 'Receipt Template: Content of header_text include',
4599 'circ.staff_client.receipt.header_text',
4600 'Text/HTML/Macros to be inserted into receipt templates in place of %INCLUDE(header_text)%',
4607 'circ.staff_client.receipt.footer_text',
4609 'circ.staff_client.receipt.footer_text',
4610 'Receipt Template: Content of footer_text include',
4615 'circ.staff_client.receipt.footer_text',
4616 'Text/HTML/Macros to be inserted into receipt templates in place of %INCLUDE(footer_text)%',
4623 'circ.staff_client.receipt.notice_text',
4625 'circ.staff_client.receipt.notice_text',
4626 'Receipt Template: Content of notice_text include',
4631 'circ.staff_client.receipt.notice_text',
4632 'Text/HTML/Macros to be inserted into receipt templates in place of %INCLUDE(notice_text)%',
4639 'circ.staff_client.receipt.alert_text',
4641 'circ.staff_client.receipt.alert_text',
4642 'Receipt Template: Content of alert_text include',
4647 'circ.staff_client.receipt.alert_text',
4648 'Text/HTML/Macros to be inserted into receipt templates in place of %INCLUDE(alert_text)%',
4655 'circ.staff_client.receipt.event_text',
4657 'circ.staff_client.receipt.event_text',
4658 'Receipt Template: Content of event_text include',
4663 'circ.staff_client.receipt.event_text',
4664 'Text/HTML/Macros to be inserted into receipt templates in place of %INCLUDE(event_text)%',
4671 -- Evergreen DB patch 0612.schema.authority_overlay_protection.sql
4675 -- check whether patch can be applied
4676 SELECT evergreen.upgrade_deps_block_check('0612', :eg_version);
4678 -- FIXME: add/check SQL statements to perform the upgrade
4680 -- Function to generate an ephemeral overlay template from an authority record
4681 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
4684 main_entry authority.control_set_authority_field%ROWTYPE;
4685 bib_field authority.control_set_bib_field%ROWTYPE;
4686 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
4687 replace_data XML[] DEFAULT '{}'::XML[];
4688 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
4691 IF auth_id IS NULL THEN
4695 -- Default to the LoC controll set
4696 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
4698 -- if none, make a best guess
4699 IF cset IS NULL THEN
4700 SELECT control_set INTO cset
4701 FROM authority.control_set_authority_field
4703 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
4704 FROM authority.record_entry
4710 -- if STILL none, no-op change
4711 IF cset IS NULL THEN
4714 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
4715 XMLELEMENT( name leader, '00881nam a2200193 4500'),
4718 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
4721 XMLATTRIBUTES('d' AS code),
4728 FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
4729 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
4730 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
4731 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
4732 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
4733 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
4741 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
4742 XMLELEMENT( name leader, '00881nam a2200193 4500'),
4746 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
4749 XMLATTRIBUTES('r' AS code),
4750 ARRAY_TO_STRING(replace_rules,',')
4755 $f$ STABLE LANGUAGE PLPGSQL;
4759 -- Evergreen DB patch 0613.schema.vandelay_isxn_normalization.sql
4763 -- check whether patch can be applied
4764 SELECT evergreen.upgrade_deps_block_check('0613', :eg_version);
4766 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
4768 ) RETURNS HSTORE AS $func$
4772 ARRAY_ACCUM(tag || (COALESCE(subfield, ''))),
4776 SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value
4779 CASE WHEN tag = '020' THEN -- caseless -- isbn
4780 LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%')
4781 WHEN tag = '022' THEN -- caseless -- issn
4782 LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
4783 WHEN tag = '024' THEN -- caseless -- upc (other)
4788 FROM vandelay.flatten_marc(record_xml)) x
4789 GROUP BY tag, subfield ORDER BY tag, subfield
4793 $func$ LANGUAGE PLPGSQL;
4795 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
4796 node vandelay.match_set_point
4797 ) RETURNS VOID AS $$
4805 -- remember $1 is tags_rstore, and $2 is svf_rstore
4809 IF node.tag IS NOT NULL THEN
4810 caseless := (node.tag IN ('020', '022', '024'));
4812 IF node.subfield IS NOT NULL THEN
4813 tagkey := tagkey || node.subfield;
4831 my_alias := 'n' || node.id::TEXT;
4833 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
4834 ' AS quality FROM metabib.';
4835 IF node.tag IS NOT NULL THEN
4836 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
4837 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
4839 IF node.subfield IS NOT NULL THEN
4840 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
4841 node.subfield || '''';
4843 jrow := jrow || ' AND (';
4846 jrow := jrow || 'LOWER(' || my_alias || '.value) ' || op;
4848 jrow := jrow || my_alias || '.value ' || op;
4851 jrow := jrow || ' ANY(($1->''' || tagkey || ''')::TEXT[])))';
4853 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
4854 my_alias || '.id = bre.id AND (' ||
4855 my_alias || '.attrs->''' || node.svf ||
4856 ''' ' || op || ' $2->''' || node.svf || '''))';
4858 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
4860 $$ LANGUAGE PLPGSQL;
4864 -- Evergreen DB patch XXXX.schema.generic-mapping-index-normalizer.sql
4867 -- check whether patch can be applied
4868 SELECT evergreen.upgrade_deps_block_check('0615', :eg_version);
4870 -- evergreen.generic_map_normalizer
4872 CREATE OR REPLACE FUNCTION evergreen.generic_map_normalizer ( TEXT, TEXT ) RETURNS TEXT AS $f$
4876 my $default = $string;
4879 while (/^\s*?(.*?)\s*?=>\s*?(\S+)\s*/) {
4883 $map{$2} = [split(/\s*,\s*/, $1)];
4888 for my $key ( keys %map ) {
4889 return $key if (grep { $_ eq $string } @{ $map{$key} });
4894 $f$ LANGUAGE PLPERLU;
4896 -- evergreen.generic_map_normalizer
4898 INSERT INTO config.index_normalizer (name, description, func, param_count) VALUES (
4899 'Generic Mapping Normalizer',
4900 'Map values or sets of values to new values',
4901 'generic_map_normalizer',
4906 SELECT evergreen.upgrade_deps_block_check('0616', :eg_version);
4908 CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
4912 IF TG_OP = 'DELETE' THEN
4914 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
4918 IF TG_OP = 'UPDATE' THEN
4920 IF NEW.parent_ou <> OLD.parent_ou THEN
4922 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
4923 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
4924 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
4925 FROM actor.org_unit l, actor.org_unit r
4926 WHERE (l.id = NEW.id or r.id = NEW.id);
4932 IF TG_OP = 'INSERT' THEN
4934 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
4935 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
4936 FROM actor.org_unit l, actor.org_unit r
4937 WHERE (l.id = NEW.id or r.id = NEW.id);
4944 $$ LANGUAGE plpgsql;
4947 CREATE TRIGGER proximity_update_tgr AFTER INSERT OR UPDATE OR DELETE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE actor.org_unit_prox_update ();
4950 SELECT evergreen.upgrade_deps_block_check('0617', :eg_version);
4952 -- add notify columns to booking.reservation
4953 ALTER TABLE booking.reservation
4954 ADD COLUMN email_notify BOOLEAN NOT NULL DEFAULT FALSE;
4956 -- create the hook and validator
4957 INSERT INTO action_trigger.hook (key, core_type, description, passive)
4958 VALUES ('reservation.available', 'bresv', 'A reservation is available for pickup', false);
4959 INSERT INTO action_trigger.validator (module, description)
4960 VALUES ('ReservationIsAvailable','Checked that a reserved resource is available for checkout');
4962 -- create org unit setting to toggle checkbox display
4963 INSERT INTO config.org_unit_setting_type (name, label, description, datatype)
4964 VALUES ('booking.allow_email_notify', 'booking.allow_email_notify', 'Permit email notification when a reservation is ready for pickup.', 'bool');
4967 SELECT evergreen.upgrade_deps_block_check('0618', :eg_version);
4969 UPDATE config.org_unit_setting_type SET description = E'The Regular Expression for validation on the day_phone field in patron registration. Note: The first capture group will be used for the "last 4 digits of phone number" feature, if enabled. Ex: "[2-9]\\d{2}-\\d{3}-(\\d{4})( x\\d+)?" will ignore the extension on a NANP number.' WHERE name = 'ui.patron.edit.au.day_phone.regex';
4971 UPDATE config.org_unit_setting_type SET description = 'The Regular Expression for validation on phone fields in patron registration. Applies to all phone fields without their own setting. NOTE: See description of the day_phone regex for important information about capture groups with it.' WHERE name = 'ui.patron.edit.phone.regex';
4973 UPDATE config.org_unit_setting_type SET description = oils_i18n_gettext('patron.password.use_phone', 'By default, use the last 4 alphanumeric characters of the patrons phone number as the default password when creating new users. The exact characters used may be configured via the "GUI: Regex for day_phone field on patron registration" setting.', 'coust', 'description') WHERE name = 'patron.password.use_phone';
4975 -- Evergreen DB patch 0619.schema.au_last_update_time.sql
4977 -- check whether patch can be applied
4978 SELECT evergreen.upgrade_deps_block_check('0619', :eg_version);
4980 -- Add new column last_update_time to actor.usr, with trigger to maintain it
4981 -- Add corresponding new column to auditor.actor_usr_history
4983 ALTER TABLE actor.usr
4984 ADD COLUMN last_update_time TIMESTAMPTZ;
4986 ALTER TABLE auditor.actor_usr_history
4987 ADD COLUMN last_update_time TIMESTAMPTZ;
4989 CREATE OR REPLACE FUNCTION actor.au_updated()
4990 RETURNS TRIGGER AS $$
4992 NEW.last_update_time := now();
4995 $$ LANGUAGE plpgsql;
4997 CREATE TRIGGER au_update_trig
4998 BEFORE INSERT OR UPDATE ON actor.usr
4999 FOR EACH ROW EXECUTE PROCEDURE actor.au_updated();
5001 -- Evergreen DB patch XXXX.data.opac_payment_history_age_limit.sql
5004 SELECT evergreen.upgrade_deps_block_check('0621', :eg_version);
5006 INSERT into config.org_unit_setting_type (name, label, description, datatype)
5008 'opac.payment_history_age_limit',
5009 oils_i18n_gettext('opac.payment_history_age_limit',
5010 'OPAC: Payment History Age Limit', 'coust', 'label'),
5011 oils_i18n_gettext('opac.payment_history_age_limit',
5012 'The OPAC should not display payments by patrons that are older than any interval defined here.', 'coust', 'label'),
5016 -- Updates config.org_unit_setting_type to remove the old tag prefixes for once
5017 -- groups have been added.
5020 SELECT evergreen.upgrade_deps_block_check('0622', :eg_version);
5022 INSERT INTO config.settings_group (name, label) VALUES
5023 ('sys', oils_i18n_gettext('config.settings_group.system', 'System', 'coust', 'label')),
5024 ('gui', oils_i18n_gettext('config.settings_group.gui', 'GUI', 'coust', 'label')),
5025 ('lib', oils_i18n_gettext('config.settings_group.lib', 'Library', 'coust', 'label')),
5026 ('sec', oils_i18n_gettext('config.settings_group.sec', 'Security', 'coust', 'label')),
5027 ('cat', oils_i18n_gettext('config.settings_group.cat', 'Cataloging', 'coust', 'label')),
5028 ('holds', oils_i18n_gettext('config.settings_group.holds', 'Holds', 'coust', 'label')),
5029 ('circ', oils_i18n_gettext('config.settings_group.circulation', 'Circulation', 'coust', 'label')),
5030 ('self', oils_i18n_gettext('config.settings_group.self', 'Self Check', 'coust', 'label')),
5031 ('opac', oils_i18n_gettext('config.settings_group.opac', 'OPAC', 'coust', 'label')),
5032 ('prog', oils_i18n_gettext('config.settings_group.program', 'Program', 'coust', 'label')),
5033 ('glob', oils_i18n_gettext('config.settings_group.global', 'Global', 'coust', 'label')),
5034 ('finance', oils_i18n_gettext('config.settings_group.finances', 'Finanaces', 'coust', 'label')),
5035 ('credit', oils_i18n_gettext('config.settings_group.ccp', 'Credit Card Processing', 'coust', 'label')),
5036 ('serial', oils_i18n_gettext('config.settings_group.serial', 'Serials', 'coust', 'label')),
5037 ('recall', oils_i18n_gettext('config.settings_group.recall', 'Recalls', 'coust', 'label')),
5038 ('booking', oils_i18n_gettext('config.settings_group.booking', 'Booking', 'coust', 'label')),
5039 ('offline', oils_i18n_gettext('config.settings_group.offline', 'Offline', 'coust', 'label')),
5040 ('receipt_template', oils_i18n_gettext('config.settings_group.receipt_template', 'Receipt Template', 'coust', 'label'));
5042 UPDATE config.org_unit_setting_type SET grp = 'lib', label='Set copy creator as receiver' WHERE name = 'acq.copy_creator_uses_receiver';
5043 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'acq.default_circ_modifier';
5044 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'acq.default_copy_location';
5045 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'acq.fund.balance_limit.block';
5046 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'acq.fund.balance_limit.warn';
5047 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'acq.holds.allow_holds_from_purchase_request';
5048 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'acq.tmp_barcode_prefix';
5049 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'acq.tmp_callnumber_prefix';
5050 UPDATE config.org_unit_setting_type SET grp = 'sec' WHERE name = 'auth.opac_timeout';
5051 UPDATE config.org_unit_setting_type SET grp = 'sec' WHERE name = 'auth.persistent_login_interval';
5052 UPDATE config.org_unit_setting_type SET grp = 'sec' WHERE name = 'auth.staff_timeout';
5053 UPDATE config.org_unit_setting_type SET grp = 'booking' WHERE name = 'booking.allow_email_notify';
5054 UPDATE config.org_unit_setting_type SET grp = 'gui' WHERE name = 'cat.bib.alert_on_empty';
5055 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Delete bib if all copies are deleted via Acquisitions lineitem cancellation.' WHERE name = 'cat.bib.delete_on_no_copy_via_acq_lineitem_cancel';
5056 UPDATE config.org_unit_setting_type SET grp = 'prog' WHERE name = 'cat.bib.keep_on_empty';
5057 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Default Classification Scheme' WHERE name = 'cat.default_classification_scheme';
5058 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Default copy status (fast add)' WHERE name = 'cat.default_copy_status_fast';
5059 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Default copy status (normal)' WHERE name = 'cat.default_copy_status_normal';
5060 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'cat.default_item_price';
5061 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Spine and pocket label font family' WHERE name = 'cat.label.font.family';
5062 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Spine and pocket label font size' WHERE name = 'cat.label.font.size';
5063 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Spine and pocket label font weight' WHERE name = 'cat.label.font.weight';
5064 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Defines the control number identifier used in 003 and 035 fields.' WHERE name = 'cat.marc_control_number_identifier';
5065 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Spine label maximum lines' WHERE name = 'cat.spine.line.height';
5066 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Spine label left margin' WHERE name = 'cat.spine.line.margin';
5067 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Spine label line width' WHERE name = 'cat.spine.line.width';
5068 UPDATE config.org_unit_setting_type SET grp = 'cat', label='Delete volume with last copy' WHERE name = 'cat.volume.delete_on_empty';
5069 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Toggle off the patron summary sidebar after first view.' WHERE name = 'circ.auto_hide_patron_summary';
5070 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Block Renewal of Items Needed for Holds' WHERE name = 'circ.block_renews_for_holds';
5071 UPDATE config.org_unit_setting_type SET grp = 'booking', label='Elbow room' WHERE name = 'circ.booking_reservation.default_elbow_room';
5072 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'circ.charge_lost_on_zero';
5073 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'circ.charge_on_damaged';
5074 UPDATE config.org_unit_setting_type SET grp = 'circ' WHERE name = 'circ.checkout_auto_renew_age';
5075 UPDATE config.org_unit_setting_type SET grp = 'circ' WHERE name = 'circ.checkout_fills_related_hold';
5076 UPDATE config.org_unit_setting_type SET grp = 'circ' WHERE name = 'circ.checkout_fills_related_hold_exact_match_only';
5077 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'circ.claim_never_checked_out.mark_missing';
5078 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'circ.claim_return.copy_status';
5079 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'circ.damaged.void_ovedue';
5080 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'circ.damaged_item_processing_fee';
5081 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Do not include outstanding Claims Returned circulations in lump sum tallies in Patron Display.' WHERE name = 'circ.do_not_tally_claims_returned';
5082 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Hard boundary' WHERE name = 'circ.hold_boundary.hard';
5083 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Soft boundary' WHERE name = 'circ.hold_boundary.soft';
5084 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Expire Alert Interval' WHERE name = 'circ.hold_expire_alert_interval';
5085 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Expire Interval' WHERE name = 'circ.hold_expire_interval';
5086 UPDATE config.org_unit_setting_type SET grp = 'circ' WHERE name = 'circ.hold_shelf_status_delay';
5087 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Soft stalling interval' WHERE name = 'circ.hold_stalling.soft';
5088 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Hard stalling interval' WHERE name = 'circ.hold_stalling_hard';
5089 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Use Active Date for Age Protection' WHERE name = 'circ.holds.age_protect.active_date';
5090 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Behind Desk Pickup Supported' WHERE name = 'circ.holds.behind_desk_pickup_supported';
5091 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Canceled holds display age' WHERE name = 'circ.holds.canceled.display_age';
5092 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Canceled holds display count' WHERE name = 'circ.holds.canceled.display_count';
5093 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Clear shelf copy status' WHERE name = 'circ.holds.clear_shelf.copy_status';
5094 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Bypass hold capture during clear shelf process' WHERE name = 'circ.holds.clear_shelf.no_capture_holds';
5095 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Default Estimated Wait' WHERE name = 'circ.holds.default_estimated_wait_interval';
5096 UPDATE config.org_unit_setting_type SET grp = 'holds' WHERE name = 'circ.holds.default_shelf_expire_interval';
5097 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Block hold request if hold recipient privileges have expired' WHERE name = 'circ.holds.expired_patron_block';
5098 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Has Local Copy Alert' WHERE name = 'circ.holds.hold_has_copy_at.alert';
5099 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Has Local Copy Block' WHERE name = 'circ.holds.hold_has_copy_at.block';
5100 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Maximum library target attempts' WHERE name = 'circ.holds.max_org_unit_target_loops';
5101 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Minimum Estimated Wait' WHERE name = 'circ.holds.min_estimated_wait_interval';
5102 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Org Unit Target Weight' WHERE name = 'circ.holds.org_unit_target_weight';
5103 UPDATE config.org_unit_setting_type SET grp = 'recall', label='An array of fine amount, fine interval, and maximum fine.' WHERE name = 'circ.holds.recall_fine_rules';
5104 UPDATE config.org_unit_setting_type SET grp = 'recall', label='Truncated loan period.' WHERE name = 'circ.holds.recall_return_interval';
5105 UPDATE config.org_unit_setting_type SET grp = 'recall', label='Circulation duration that triggers a recall.' WHERE name = 'circ.holds.recall_threshold';
5106 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Use weight-based hold targeting' WHERE name = 'circ.holds.target_holds_by_org_unit_weight';
5107 UPDATE config.org_unit_setting_type SET grp = 'holds' WHERE name = 'circ.holds.target_skip_me';
5108 UPDATE config.org_unit_setting_type SET grp = 'holds', label='Reset request time on un-cancel' WHERE name = 'circ.holds.uncancel.reset_request_time';
5109 UPDATE config.org_unit_setting_type SET grp = 'holds', label='FIFO' WHERE name = 'circ.holds_fifo';
5110 UPDATE config.org_unit_setting_type SET grp = 'gui' WHERE name = 'circ.item_checkout_history.max';
5111 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Lost Checkin Generates New Overdues' WHERE name = 'circ.lost.generate_overdue_on_checkin';
5112 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Lost items usable on checkin' WHERE name = 'circ.lost_immediately_available';
5113 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'circ.lost_materials_processing_fee';
5114 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Void lost max interval' WHERE name = 'circ.max_accept_return_of_lost';
5115 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Cap Max Fine at Item Price' WHERE name = 'circ.max_fine.cap_at_price';
5116 UPDATE config.org_unit_setting_type SET grp = 'circ' WHERE name = 'circ.max_patron_claim_return_count';
5117 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Item Status for Missing Pieces' WHERE name = 'circ.missing_pieces.copy_status';
5118 UPDATE config.org_unit_setting_type SET grp = 'sec' WHERE name = 'circ.obscure_dob';
5119 UPDATE config.org_unit_setting_type SET grp = 'offline', label='Skip offline checkin if newer item Status Changed Time.' WHERE name = 'circ.offline.skip_checkin_if_newer_status_changed_time';
5120 UPDATE config.org_unit_setting_type SET grp = 'offline', label='Skip offline checkout if newer item Status Changed Time.' WHERE name = 'circ.offline.skip_checkout_if_newer_status_changed_time';
5121 UPDATE config.org_unit_setting_type SET grp = 'offline', label='Skip offline renewal if newer item Status Changed Time.' WHERE name = 'circ.offline.skip_renew_if_newer_status_changed_time';
5122 UPDATE config.org_unit_setting_type SET grp = 'sec', label='Offline: Patron Usernames Allowed' WHERE name = 'circ.offline.username_allowed';
5123 UPDATE config.org_unit_setting_type SET grp = 'sec', label='Maximum concurrently active self-serve password reset requests per user' WHERE name = 'circ.password_reset_request_per_user_limit';
5124 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Require matching email address for password reset requests' WHERE name = 'circ.password_reset_request_requires_matching_email';
5125 UPDATE config.org_unit_setting_type SET grp = 'sec', label='Maximum concurrently active self-serve password reset requests' WHERE name = 'circ.password_reset_request_throttle';
5126 UPDATE config.org_unit_setting_type SET grp = 'sec', label='Self-serve password reset request time-to-live' WHERE name = 'circ.password_reset_request_time_to_live';
5127 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Patron Registration: Cloned patrons get address copy' WHERE name = 'circ.patron_edit.clone.copy_address';
5128 UPDATE config.org_unit_setting_type SET grp = 'circ' WHERE name = 'circ.patron_invalid_address_apply_penalty';
5129 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'circ.pre_cat_copy_circ_lib';
5130 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'circ.reshelving_complete.interval';
5131 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Restore overdues on lost item return' WHERE name = 'circ.restore_overdue_on_lost_return';
5132 UPDATE config.org_unit_setting_type SET grp = 'self', label='Pop-up alert for errors' WHERE name = 'circ.selfcheck.alert.popup';
5133 UPDATE config.org_unit_setting_type SET grp = 'self', label='Audio Alerts' WHERE name = 'circ.selfcheck.alert.sound';
5134 UPDATE config.org_unit_setting_type SET grp = 'self' WHERE name = 'circ.selfcheck.auto_override_checkout_events';
5135 UPDATE config.org_unit_setting_type SET grp = 'self', label='Block copy checkout status' WHERE name = 'circ.selfcheck.block_checkout_on_copy_status';
5136 UPDATE config.org_unit_setting_type SET grp = 'self', label='Patron Login Timeout (in seconds)' WHERE name = 'circ.selfcheck.patron_login_timeout';
5137 UPDATE config.org_unit_setting_type SET grp = 'self', label='Require Patron Password' WHERE name = 'circ.selfcheck.patron_password_required';
5138 UPDATE config.org_unit_setting_type SET grp = 'self', label='Require patron password' WHERE name = 'circ.selfcheck.require_patron_password';
5139 UPDATE config.org_unit_setting_type SET grp = 'self', label='Workstation Required' WHERE name = 'circ.selfcheck.workstation_required';
5140 UPDATE config.org_unit_setting_type SET grp = 'circ' WHERE name = 'circ.staff_client.actor_on_checkout';
5141 UPDATE config.org_unit_setting_type SET grp = 'prog' WHERE name = 'circ.staff_client.do_not_auto_attempt_print';
5142 UPDATE config.org_unit_setting_type SET grp = 'receipt_template', label='Content of alert_text include' WHERE name = 'circ.staff_client.receipt.alert_text';
5143 UPDATE config.org_unit_setting_type SET grp = 'receipt_template', label='Content of event_text include' WHERE name = 'circ.staff_client.receipt.event_text';
5144 UPDATE config.org_unit_setting_type SET grp = 'receipt_template', label='Content of footer_text include' WHERE name = 'circ.staff_client.receipt.footer_text';
5145 UPDATE config.org_unit_setting_type SET grp = 'receipt_template', label='Content of header_text include' WHERE name = 'circ.staff_client.receipt.header_text';
5146 UPDATE config.org_unit_setting_type SET grp = 'receipt_template', label='Content of notice_text include' WHERE name = 'circ.staff_client.receipt.notice_text';
5147 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Minimum Transit Checkin Interval' WHERE name = 'circ.transit.min_checkin_interval';
5148 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Patron Merge Deactivate Card' WHERE name = 'circ.user_merge.deactivate_cards';
5149 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Patron Merge Address Delete' WHERE name = 'circ.user_merge.delete_addresses';
5150 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Patron Merge Barcode Delete' WHERE name = 'circ.user_merge.delete_cards';
5151 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Void lost item billing when returned' WHERE name = 'circ.void_lost_on_checkin';
5152 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Void processing fee on lost item return' WHERE name = 'circ.void_lost_proc_fee_on_checkin';
5153 UPDATE config.org_unit_setting_type SET grp = 'finance', label='Void overdue fines when items are marked lost' WHERE name = 'circ.void_overdue_on_lost';
5154 UPDATE config.org_unit_setting_type SET grp = 'finance' WHERE name = 'credit.payments.allow';
5155 UPDATE config.org_unit_setting_type SET grp = 'credit', label='Enable AuthorizeNet payments' WHERE name = 'credit.processor.authorizenet.enabled';
5156 UPDATE config.org_unit_setting_type SET grp = 'credit', label='AuthorizeNet login' WHERE name = 'credit.processor.authorizenet.login';
5157 UPDATE config.org_unit_setting_type SET grp = 'credit', label='AuthorizeNet password' WHERE name = 'credit.processor.authorizenet.password';
5158 UPDATE config.org_unit_setting_type SET grp = 'credit', label='AuthorizeNet server' WHERE name = 'credit.processor.authorizenet.server';
5159 UPDATE config.org_unit_setting_type SET grp = 'credit', label='AuthorizeNet test mode' WHERE name = 'credit.processor.authorizenet.testmode';
5160 UPDATE config.org_unit_setting_type SET grp = 'credit', label='Name default credit processor' WHERE name = 'credit.processor.default';
5161 UPDATE config.org_unit_setting_type SET grp = 'credit', label='Enable PayflowPro payments' WHERE name = 'credit.processor.payflowpro.enabled';
5162 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayflowPro login/merchant ID' WHERE name = 'credit.processor.payflowpro.login';
5163 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayflowPro partner' WHERE name = 'credit.processor.payflowpro.partner';
5164 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayflowPro password' WHERE name = 'credit.processor.payflowpro.password';
5165 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayflowPro test mode' WHERE name = 'credit.processor.payflowpro.testmode';
5166 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayflowPro vendor' WHERE name = 'credit.processor.payflowpro.vendor';
5167 UPDATE config.org_unit_setting_type SET grp = 'credit', label='Enable PayPal payments' WHERE name = 'credit.processor.paypal.enabled';
5168 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayPal login' WHERE name = 'credit.processor.paypal.login';
5169 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayPal password' WHERE name = 'credit.processor.paypal.password';
5170 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayPal signature' WHERE name = 'credit.processor.paypal.signature';
5171 UPDATE config.org_unit_setting_type SET grp = 'credit', label='PayPal test mode' WHERE name = 'credit.processor.paypal.testmode';
5172 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Format Dates with this pattern.' WHERE name = 'format.date';
5173 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Format Times with this pattern.' WHERE name = 'format.time';
5174 UPDATE config.org_unit_setting_type SET grp = 'glob' WHERE name = 'global.default_locale';
5175 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'global.juvenile_age_threshold';
5176 UPDATE config.org_unit_setting_type SET grp = 'glob' WHERE name = 'global.password_regex';
5177 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Disable the ability to save list column configurations locally.' WHERE name = 'gui.disable_local_save_columns';
5178 UPDATE config.org_unit_setting_type SET grp = 'lib', label='Courier Code' WHERE name = 'lib.courier_code';
5179 UPDATE config.org_unit_setting_type SET grp = 'lib' WHERE name = 'notice.telephony.callfile_lines';
5180 UPDATE config.org_unit_setting_type SET grp = 'opac', label='Allow pending addresses' WHERE name = 'opac.allow_pending_address';
5181 UPDATE config.org_unit_setting_type SET grp = 'glob' WHERE name = 'opac.barcode_regex';
5182 UPDATE config.org_unit_setting_type SET grp = 'opac', label='Use fully compressed serial holdings' WHERE name = 'opac.fully_compressed_serial_holdings';
5183 UPDATE config.org_unit_setting_type SET grp = 'opac', label='Org Unit Hiding Depth' WHERE name = 'opac.org_unit_hiding.depth';
5184 UPDATE config.org_unit_setting_type SET grp = 'opac', label='Payment History Age Limit' WHERE name = 'opac.payment_history_age_limit';
5185 UPDATE config.org_unit_setting_type SET grp = 'prog' WHERE name = 'org.bounced_emails';
5186 UPDATE config.org_unit_setting_type SET grp = 'sec', label='Patron Opt-In Boundary' WHERE name = 'org.patron_opt_boundary';
5187 UPDATE config.org_unit_setting_type SET grp = 'sec', label='Patron Opt-In Default' WHERE name = 'org.patron_opt_default';
5188 UPDATE config.org_unit_setting_type SET grp = 'sec' WHERE name = 'patron.password.use_phone';
5189 UPDATE config.org_unit_setting_type SET grp = 'serial', label='Previous Issuance Copy Location' WHERE name = 'serial.prev_issuance_copy_location';
5190 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Work Log: Maximum Patrons Logged' WHERE name = 'ui.admin.patron_log.max_entries';
5191 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Work Log: Maximum Actions Logged' WHERE name = 'ui.admin.work_log.max_entries';
5192 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Horizontal layout for Volume/Copy Creator/Editor.' WHERE name = 'ui.cat.volume_copy_editor.horizontal';
5193 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Uncheck bills by default in the patron billing interface' WHERE name = 'ui.circ.billing.uncheck_bills_and_unfocus_payment_box';
5194 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Record In-House Use: Maximum # of uses allowed per entry.' WHERE name = 'ui.circ.in_house_use.entry_cap';
5195 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Record In-House Use: # of uses threshold for Are You Sure? dialog.' WHERE name = 'ui.circ.in_house_use.entry_warn';
5196 UPDATE config.org_unit_setting_type SET grp = 'gui' WHERE name = 'ui.circ.patron_summary.horizontal';
5197 UPDATE config.org_unit_setting_type SET grp = 'gui' WHERE name = 'ui.circ.show_billing_tab_on_bills';
5198 UPDATE config.org_unit_setting_type SET grp = 'circ', label='Suppress popup-dialogs during check-in.' WHERE name = 'ui.circ.suppress_checkin_popups';
5199 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Button bar' WHERE name = 'ui.general.button_bar';
5200 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Default Hotkeyset' WHERE name = 'ui.general.hotkeyset';
5201 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Idle timeout' WHERE name = 'ui.general.idle_timeout';
5202 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Default Country for New Addresses in Patron Editor' WHERE name = 'ui.patron.default_country';
5203 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Default Ident Type for Patron Registration' WHERE name = 'ui.patron.default_ident_type';
5204 UPDATE config.org_unit_setting_type SET grp = 'sec', label='Default level of patrons'' internet access' WHERE name = 'ui.patron.default_inet_access_level';
5205 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show active field on patron registration' WHERE name = 'ui.patron.edit.au.active.show';
5206 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest active field on patron registration' WHERE name = 'ui.patron.edit.au.active.suggest';
5207 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show alert_message field on patron registration' WHERE name = 'ui.patron.edit.au.alert_message.show';
5208 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest alert_message field on patron registration' WHERE name = 'ui.patron.edit.au.alert_message.suggest';
5209 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show alias field on patron registration' WHERE name = 'ui.patron.edit.au.alias.show';
5210 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest alias field on patron registration' WHERE name = 'ui.patron.edit.au.alias.suggest';
5211 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show barred field on patron registration' WHERE name = 'ui.patron.edit.au.barred.show';
5212 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest barred field on patron registration' WHERE name = 'ui.patron.edit.au.barred.suggest';
5213 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show claims_never_checked_out_count field on patron registration' WHERE name = 'ui.patron.edit.au.claims_never_checked_out_count.show';
5214 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest claims_never_checked_out_count field on patron registration' WHERE name = 'ui.patron.edit.au.claims_never_checked_out_count.suggest';
5215 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show claims_returned_count field on patron registration' WHERE name = 'ui.patron.edit.au.claims_returned_count.show';
5216 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest claims_returned_count field on patron registration' WHERE name = 'ui.patron.edit.au.claims_returned_count.suggest';
5217 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Example for day_phone field on patron registration' WHERE name = 'ui.patron.edit.au.day_phone.example';
5218 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Regex for day_phone field on patron registration' WHERE name = 'ui.patron.edit.au.day_phone.regex';
5219 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require day_phone field on patron registration' WHERE name = 'ui.patron.edit.au.day_phone.require';
5220 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show day_phone field on patron registration' WHERE name = 'ui.patron.edit.au.day_phone.show';
5221 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest day_phone field on patron registration' WHERE name = 'ui.patron.edit.au.day_phone.suggest';
5222 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show calendar widget for dob field on patron registration' WHERE name = 'ui.patron.edit.au.dob.calendar';
5223 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require dob field on patron registration' WHERE name = 'ui.patron.edit.au.dob.require';
5224 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show dob field on patron registration' WHERE name = 'ui.patron.edit.au.dob.show';
5225 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest dob field on patron registration' WHERE name = 'ui.patron.edit.au.dob.suggest';
5226 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Example for email field on patron registration' WHERE name = 'ui.patron.edit.au.email.example';
5227 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Regex for email field on patron registration' WHERE name = 'ui.patron.edit.au.email.regex';
5228 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require email field on patron registration' WHERE name = 'ui.patron.edit.au.email.require';
5229 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show email field on patron registration' WHERE name = 'ui.patron.edit.au.email.show';
5230 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest email field on patron registration' WHERE name = 'ui.patron.edit.au.email.suggest';
5231 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Example for evening_phone field on patron registration' WHERE name = 'ui.patron.edit.au.evening_phone.example';
5232 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Regex for evening_phone field on patron registration' WHERE name = 'ui.patron.edit.au.evening_phone.regex';
5233 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require evening_phone field on patron registration' WHERE name = 'ui.patron.edit.au.evening_phone.require';
5234 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show evening_phone field on patron registration' WHERE name = 'ui.patron.edit.au.evening_phone.show';
5235 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest evening_phone field on patron registration' WHERE name = 'ui.patron.edit.au.evening_phone.suggest';
5236 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show ident_value field on patron registration' WHERE name = 'ui.patron.edit.au.ident_value.show';
5237 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest ident_value field on patron registration' WHERE name = 'ui.patron.edit.au.ident_value.suggest';
5238 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show ident_value2 field on patron registration' WHERE name = 'ui.patron.edit.au.ident_value2.show';
5239 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest ident_value2 field on patron registration' WHERE name = 'ui.patron.edit.au.ident_value2.suggest';
5240 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show juvenile field on patron registration' WHERE name = 'ui.patron.edit.au.juvenile.show';
5241 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest juvenile field on patron registration' WHERE name = 'ui.patron.edit.au.juvenile.suggest';
5242 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show master_account field on patron registration' WHERE name = 'ui.patron.edit.au.master_account.show';
5243 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest master_account field on patron registration' WHERE name = 'ui.patron.edit.au.master_account.suggest';
5244 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Example for other_phone field on patron registration' WHERE name = 'ui.patron.edit.au.other_phone.example';
5245 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Regex for other_phone field on patron registration' WHERE name = 'ui.patron.edit.au.other_phone.regex';
5246 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require other_phone field on patron registration' WHERE name = 'ui.patron.edit.au.other_phone.require';
5247 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show other_phone field on patron registration' WHERE name = 'ui.patron.edit.au.other_phone.show';
5248 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest other_phone field on patron registration' WHERE name = 'ui.patron.edit.au.other_phone.suggest';
5249 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show second_given_name field on patron registration' WHERE name = 'ui.patron.edit.au.second_given_name.show';
5250 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest second_given_name field on patron registration' WHERE name = 'ui.patron.edit.au.second_given_name.suggest';
5251 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Show suffix field on patron registration' WHERE name = 'ui.patron.edit.au.suffix.show';
5252 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Suggest suffix field on patron registration' WHERE name = 'ui.patron.edit.au.suffix.suggest';
5253 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require county field on patron registration' WHERE name = 'ui.patron.edit.aua.county.require';
5254 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Example for post_code field on patron registration' WHERE name = 'ui.patron.edit.aua.post_code.example';
5255 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Regex for post_code field on patron registration' WHERE name = 'ui.patron.edit.aua.post_code.regex';
5256 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Default showing suggested patron registration fields' WHERE name = 'ui.patron.edit.default_suggested';
5257 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Example for phone fields on patron registration' WHERE name = 'ui.patron.edit.phone.example';
5258 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Regex for phone fields on patron registration' WHERE name = 'ui.patron.edit.phone.regex';
5259 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require at least one address for Patron Registration' WHERE name = 'ui.patron.registration.require_address';
5260 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Cap results in Patron Search at this number.' WHERE name = 'ui.patron_search.result_cap';
5261 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Require staff initials for entry/edit of item/patron/penalty notes/messages.' WHERE name = 'ui.staff.require_initials';
5262 UPDATE config.org_unit_setting_type SET grp = 'gui', label='Unified Volume/Item Creator/Editor' WHERE name = 'ui.unified_volume_copy_editor';
5263 UPDATE config.org_unit_setting_type SET grp = 'gui', label='URL for remote directory containing list column settings.' WHERE name = 'url.remote_column_settings';
5268 SELECT evergreen.upgrade_deps_block_check('0623', :eg_version);
5271 CREATE TABLE config.org_unit_setting_type_log (
5272 id BIGSERIAL PRIMARY KEY,
5273 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
5274 org INT REFERENCES actor.org_unit (id),
5275 original_value TEXT,
5277 field_name TEXT REFERENCES config.org_unit_setting_type (name)
5280 -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working.
5281 CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$
5285 -- Check for which setting is being updated, and log it.
5286 SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit;
5288 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name);
5292 $ous_change_log$ LANGUAGE plpgsql;
5294 CREATE TRIGGER log_ous_change
5295 BEFORE INSERT OR UPDATE ON actor.org_unit_setting
5296 FOR EACH ROW EXECUTE PROCEDURE ous_change_log();
5298 CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$
5302 -- Check for which setting is being updated, and log it.
5303 SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit;
5305 INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name);
5309 $ous_delete_log$ LANGUAGE plpgsql;
5311 CREATE TRIGGER log_ous_del
5312 BEFORE DELETE ON actor.org_unit_setting
5313 FOR EACH ROW EXECUTE PROCEDURE ous_delete_log();
5315 -- Evergreen DB patch 0625.data.opac_staff_saved_search_size.sql
5318 SELECT evergreen.upgrade_deps_block_check('0625', :eg_version);
5320 INSERT into config.org_unit_setting_type (name, grp, label, description, datatype)
5322 'opac.staff_saved_search.size', 'opac',
5323 oils_i18n_gettext('opac.staff_saved_search.size',
5324 'OPAC: Number of staff client saved searches to display on left side of results and record details pages', 'coust', 'label'),
5325 oils_i18n_gettext('opac.staff_saved_search.size',
5326 'If unset, the OPAC (only when wrapped in the staff client!) will default to showing you your ten most recent searches on the left side of the results and record details pages. If you actually don''t want to see this feature at all, set this value to zero at the top of your organizational tree.', 'coust', 'description'),
5330 -- Evergreen DB patch 0626.schema.bookbag-goodies.sql
5333 SELECT evergreen.upgrade_deps_block_check('0626', :eg_version);
5335 ALTER TABLE container.biblio_record_entry_bucket
5336 ADD COLUMN description TEXT;
5338 ALTER TABLE container.call_number_bucket
5339 ADD COLUMN description TEXT;
5341 ALTER TABLE container.copy_bucket
5342 ADD COLUMN description TEXT;
5344 ALTER TABLE container.user_bucket
5345 ADD COLUMN description TEXT;
5347 INSERT INTO action_trigger.hook (key, core_type, description, passive)
5349 'container.biblio_record_entry_bucket.csv',
5352 'container.biblio_record_entry_bucket.csv',
5353 'Produce a CSV file representing a bookbag',
5360 INSERT INTO action_trigger.reactor (module, description)
5365 'Facilitates produce a CSV file representing a bookbag by introducing an "items" variable into the TT environment, sorted as dictated according to user params',
5371 INSERT INTO action_trigger.event_definition (
5373 name, hook, reactor,
5377 'Bookbag CSV', 'container.biblio_record_entry_bucket.csv', 'ContainerCSV',
5381 # target is the bookbag itself. The 'items' variable does not need to be in
5382 # the environment because a special reactor will take care of filling it in.
5385 bibxml = helpers.xml_doc(item.target_biblio_record_entry.marc);
5387 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
5388 title = title _ part.textContent;
5390 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
5392 helpers.csv_datum(title) %],[% helpers.csv_datum(author) %],[% FOR note IN item.notes; helpers.csv_datum(note.note); ","; END; "\n";
5397 -- Evergreen DB patch 0627.data.patron-password-reset-msg.sql
5399 -- Updates password reset template to match TPAC reset form
5402 -- check whether patch can be applied
5403 SELECT evergreen.upgrade_deps_block_check('0627', :eg_version);
5405 UPDATE action_trigger.event_definition SET template =
5408 [%- user = target.usr -%]
5409 To: [%- params.recipient_email || user.email %]
5410 From: [%- params.sender_email || user.home_ou.email || default_sender %]
5411 Subject: [% user.home_ou.name %]: library account password reset request
5413 You have received this message because you, or somebody else, requested a reset
5414 of your library system password. If you did not request a reset of your library
5415 system password, just ignore this message and your current password will
5418 If you did request a reset of your library system password, please perform
5419 the following steps to continue the process of resetting your password:
5421 1. Open the following link in a web browser: https://[% params.hostname %]/eg/opac/password_reset/[% target.uuid %]
5422 The browser displays a password reset form.
5424 2. Enter your new password in the password reset form in the browser. You must
5425 enter the password twice to ensure that you do not make a mistake. If the
5426 passwords match, you will then be able to log in to your library system account
5427 with the new password.
5430 WHERE id = 20; -- Password reset request notification
5433 SELECT evergreen.upgrade_deps_block_check('0630', :eg_version);
5435 INSERT into config.org_unit_setting_type (name, grp, label, description, datatype) VALUES
5436 ( 'circ.transit.suppress_hold', 'circ',
5437 oils_i18n_gettext('circ.transit.suppress_hold',
5438 'Suppress Hold Transits Group',
5440 oils_i18n_gettext('circ.transit.suppress_hold',
5441 'If set to a non-empty value, Hold Transits will be suppressed between this OU and others with the same value. If set to an empty value, transits will not be suppressed.',
5442 'coust', 'description'),
5444 ,( 'circ.transit.suppress_non_hold', 'circ',
5445 oils_i18n_gettext('circ.transit.suppress_non_hold',
5446 'Suppress Non-Hold Transits Group',
5448 oils_i18n_gettext('circ.transit.suppress_non_hold',
5449 'If set to a non-empty value, Non-Hold Transits will be suppressed between this OU and others with the same value. If set to an empty value, transits will not be suppressed.',
5450 'coust', 'description'),
5454 -- check whether patch can be applied
5455 SELECT evergreen.upgrade_deps_block_check('0632', :eg_version);
5457 INSERT INTO config.org_unit_setting_type (name, grp, label, description, datatype) VALUES
5458 ( 'opac.username_regex', 'glob',
5459 oils_i18n_gettext('opac.username_regex',
5460 'Patron username format',
5462 oils_i18n_gettext('opac.username_regex',
5463 'Regular expression defining the patron username format, used for patron registration and self-service username changing only',
5464 'coust', 'description'),
5466 ,( 'opac.lock_usernames', 'glob',
5467 oils_i18n_gettext('opac.lock_usernames',
5470 oils_i18n_gettext('opac.lock_usernames',
5471 'If enabled username changing via the OPAC will be disabled',
5472 'coust', 'description'),
5474 ,( 'opac.unlimit_usernames', 'glob',
5475 oils_i18n_gettext('opac.unlimit_usernames',
5476 'Allow multiple username changes',
5478 oils_i18n_gettext('opac.unlimit_usernames',
5479 'If enabled (and Lock Usernames is not set) patrons will be allowed to change their username when it does not look like a barcode. Otherwise username changing in the OPAC will only be allowed when the patron''s username looks like a barcode.',
5480 'coust', 'description'),
5484 -- Evergreen DB patch 0635.data.opac.jump-to-details-setting.sql
5488 -- check whether patch can be applied
5489 SELECT evergreen.upgrade_deps_block_check('0635', :eg_version);
5491 INSERT INTO config.org_unit_setting_type ( name, grp, label, description, datatype )
5493 'opac.staff.jump_to_details_on_single_hit',
5496 'opac.staff.jump_to_details_on_single_hit',
5497 'Jump to details on 1 hit (staff client)',
5502 'opac.staff.jump_to_details_on_single_hit',
5503 'When a search yields only 1 result, jump directly to the record details page. This setting only affects the OPAC within the staff client',
5509 'opac.patron.jump_to_details_on_single_hit',
5512 'opac.patron.jump_to_details_on_single_hit',
5513 'Jump to details on 1 hit (public)',
5518 'opac.patron.jump_to_details_on_single_hit',
5519 'When a search yields only 1 result, jump directly to the record details page. This setting only affects the public OPAC',
5526 -- Evergreen DB patch 0636.data.grace_period_extend.sql
5528 -- OU setting turns on grace period auto extension. By default they only do so
5529 -- when the grace period ends on a closed date, but there are two modifiers to
5532 -- The first modifier causes grace periods to extend for all closed dates that
5533 -- they intersect. This is "grace periods are only consumed by open days."
5535 -- The second modifier causes a grace period that ends just before a closed
5536 -- day, with or without extension having happened, to include the closed day
5537 -- (and any following it) as well. This is mainly so that a backdate into the
5538 -- closed period following the grace period will assume the "best case" of the
5539 -- item having been returned after hours on the last day of the closed date.
5543 -- check whether patch can be applied
5544 SELECT evergreen.upgrade_deps_block_check('0636', :eg_version);
5546 INSERT INTO config.org_unit_setting_type(name, grp, label, description, datatype) VALUES
5548 ( 'circ.grace.extend', 'circ',
5549 oils_i18n_gettext('circ.grace.extend',
5550 'Auto-Extend Grace Periods',
5552 oils_i18n_gettext('circ.grace.extend',
5553 'When enabled grace periods will auto-extend. By default this will be only when they are a full day or more and end on a closed date, though other options can alter this.',
5554 'coust', 'description'),
5557 ,( 'circ.grace.extend.all', 'circ',
5558 oils_i18n_gettext('circ.grace.extend.all',
5559 'Auto-Extending Grace Periods extend for all closed dates',
5561 oils_i18n_gettext('circ.grace.extend.all',
5562 'If enabled and Grace Periods auto-extending is turned on grace periods will extend past all closed dates they intersect, within hard-coded limits. This basically becomes "grace periods can only be consumed by closed dates".',
5563 'coust', 'description'),
5566 ,( 'circ.grace.extend.into_closed', 'circ',
5567 oils_i18n_gettext('circ.grace.extend.into_closed',
5568 'Auto-Extending Grace Periods include trailing closed dates',
5570 oils_i18n_gettext('circ.grace.extend.into_closed',
5571 'If enabled and Grace Periods auto-extending is turned on grace periods will include closed dates that directly follow the last day of the grace period, to allow a backdate into the closed dates to assume "returned after hours on the last day of the grace period, and thus still within it" automatically.',
5572 'coust', 'description'),
5576 -- XXXX.schema-acs-nfi.sql
5578 SELECT evergreen.upgrade_deps_block_check('0640', :eg_version);
5580 -- AFTER UPDATE OR INSERT trigger for authority.record_entry
5581 CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
5584 IF NEW.deleted IS TRUE THEN -- If this authority is deleted
5585 DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
5586 DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
5587 DELETE FROM authority.simple_heading WHERE record = NEW.id;
5588 -- Should remove matching $0 from controlled fields at the same time?
5589 RETURN NEW; -- and we're done
5592 IF TG_OP = 'UPDATE' THEN -- re-ingest?
5593 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
5595 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
5599 -- Propagate these updates to any linked bib records
5600 PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id;
5602 DELETE FROM authority.simple_heading WHERE record = NEW.id;
5605 INSERT INTO authority.simple_heading (record,atag,value,sort_value)
5606 SELECT record, atag, value, sort_value FROM authority.simple_heading_set(NEW.marc);
5608 -- Flatten and insert the afr data
5609 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
5611 PERFORM authority.reingest_authority_full_rec(NEW.id);
5612 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
5614 PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
5620 $func$ LANGUAGE PLPGSQL;
5622 ALTER TABLE authority.control_set_authority_field ADD COLUMN nfi CHAR(1);
5624 -- Entries that need to respect an NFI
5625 UPDATE authority.control_set_authority_field SET nfi = '2'
5626 WHERE id IN (4,24,44,64);
5628 DROP TRIGGER authority_full_rec_fti_trigger ON authority.full_rec;
5629 CREATE TRIGGER authority_full_rec_fti_trigger
5630 BEFORE UPDATE OR INSERT ON authority.full_rec
5631 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
5633 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
5635 acsaf authority.control_set_authority_field%ROWTYPE;
5644 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
5646 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
5648 IF cset IS NULL THEN
5649 SELECT control_set INTO cset
5650 FROM authority.control_set_authority_field
5651 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
5655 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
5656 IF thes_code IS NULL THEN
5658 ELSIF thes_code = 'z' THEN
5659 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
5663 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
5664 tag_used := acsaf.tag;
5665 nfi_used := acsaf.nfi;
5667 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
5668 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
5670 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
5672 tmp_text := SUBSTRING(
5677 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
5692 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
5693 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
5696 EXIT WHEN heading_text <> '';
5699 IF heading_text <> '' THEN
5700 IF no_thesaurus IS TRUE THEN
5701 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
5703 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
5706 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
5709 RETURN heading_text;
5711 $func$ LANGUAGE PLPGSQL IMMUTABLE;
5713 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
5714 SELECT authority.normalize_heading($1, TRUE);
5715 $func$ LANGUAGE SQL IMMUTABLE;
5717 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
5718 SELECT authority.normalize_heading($1, FALSE);
5719 $func$ LANGUAGE SQL IMMUTABLE;
5722 CREATE TABLE authority.simple_heading (
5723 id BIGSERIAL PRIMARY KEY,
5724 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
5725 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
5726 value TEXT NOT NULL,
5727 sort_value TEXT NOT NULL,
5728 index_vector tsvector NOT NULL
5730 CREATE TRIGGER authority_simple_heading_fti_trigger
5731 BEFORE UPDATE OR INSERT ON authority.simple_heading
5732 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
5734 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
5735 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
5736 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
5738 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
5740 res authority.simple_heading%ROWTYPE;
5741 acsaf authority.control_set_authority_field%ROWTYPE;
5751 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
5754 res.record := auth_id;
5756 SELECT control_set INTO cset
5757 FROM authority.control_set_authority_field
5758 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
5761 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
5763 res.atag := acsaf.id;
5764 tag_used := acsaf.tag;
5765 nfi_used := acsaf.nfi;
5767 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
5770 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
5771 heading_text := heading_text || COALESCE( ' ' || oils_xpath_string('//*[@code="'||sf||'"]',tmp_xml::TEXT), '');
5774 heading_text := public.naco_normalize(heading_text);
5776 IF nfi_used IS NOT NULL THEN
5778 sort_text := SUBSTRING(
5783 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
5795 sort_text := heading_text;
5798 IF heading_text IS NOT NULL AND heading_text <> '' THEN
5799 res.value := heading_text;
5800 res.sort_value := sort_text;
5810 $func$ LANGUAGE PLPGSQL IMMUTABLE;
5812 -- Support function used to find the pivot for alpha-heading-browse style searching
5813 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
5815 sort_value_row RECORD;
5820 t_term := public.naco_normalize(q);
5822 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
5823 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
5826 FROM authority.simple_heading ash
5827 WHERE ash.atag = ANY (a)
5828 AND ash.sort_value >= t_term
5829 ORDER BY rank DESC, ash.sort_value
5832 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
5833 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
5836 FROM authority.simple_heading ash
5837 WHERE ash.atag = ANY (a)
5838 AND ash.value >= t_term
5839 ORDER BY rank DESC, ash.sort_value
5842 IF value_row.rank > sort_value_row.rank THEN
5843 RETURN value_row.sort_value;
5845 RETURN sort_value_row.sort_value;
5848 $$ LANGUAGE PLPGSQL;
5851 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
5853 pivot_sort_value TEXT;
5854 boffset INT DEFAULT 0;
5855 aoffset INT DEFAULT 0;
5856 blimit INT DEFAULT 0;
5857 alimit INT DEFAULT 0;
5860 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
5863 blimit := pagesize / 2;
5866 IF pagesize % 2 <> 0 THEN
5867 alimit := alimit + 1;
5873 boffset := pagesize / 2;
5876 IF pagesize % 2 <> 0 THEN
5877 boffset := boffset + 1;
5883 -- "bottom" half of the browse results
5886 row_number() over ()
5887 FROM authority.simple_heading ash
5888 WHERE ash.atag = ANY (atag_list)
5889 AND ash.sort_value < pivot_sort_value
5890 ORDER BY ash.sort_value DESC
5892 OFFSET ABS(page) * pagesize - boffset
5893 ) x ORDER BY row_number DESC;
5898 -- "bottom" half of the browse results
5900 FROM authority.simple_heading ash
5901 WHERE ash.atag = ANY (atag_list)
5902 AND ash.sort_value >= pivot_sort_value
5903 ORDER BY ash.sort_value
5905 OFFSET ABS(page) * pagesize - aoffset;
5908 $$ LANGUAGE PLPGSQL ROWS 10;
5910 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
5912 pivot_sort_value TEXT;
5915 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
5919 -- "bottom" half of the browse results
5922 row_number() over ()
5923 FROM authority.simple_heading ash
5924 WHERE ash.atag = ANY (atag_list)
5925 AND ash.sort_value < pivot_sort_value
5926 ORDER BY ash.sort_value DESC
5928 OFFSET (ABS(page) - 1) * pagesize
5929 ) x ORDER BY row_number DESC;
5934 -- "bottom" half of the browse results
5936 FROM authority.simple_heading ash
5937 WHERE ash.atag = ANY (atag_list)
5938 AND ash.sort_value >= pivot_sort_value
5939 ORDER BY ash.sort_value
5941 OFFSET ABS(page) * pagesize ;
5944 $$ LANGUAGE PLPGSQL ROWS 10;
5946 CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
5948 FROM authority.simple_heading ash,
5949 public.naco_normalize($2) t(term),
5950 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
5951 WHERE ash.atag = ANY ($1)
5952 AND ash.index_vector @@ ptsq.term
5953 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
5954 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
5955 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
5958 $$ LANGUAGE SQL ROWS 10;
5960 CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
5962 FROM authority.simple_heading ash,
5963 public.naco_normalize($2) t(term),
5964 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
5965 WHERE ash.atag = ANY ($1)
5966 AND ash.index_vector @@ ptsq.term
5967 ORDER BY ash.sort_value
5970 $$ LANGUAGE SQL ROWS 10;
5973 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
5974 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
5977 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
5980 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
5982 FROM authority.browse_axis_authority_field_map a
5988 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
5989 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
5992 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
5994 ARRAY[a.authority_field],
5995 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
5997 FROM authority.control_set_bib_field a
6003 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
6004 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
6007 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
6010 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
6012 FROM authority.control_set_authority_field a
6017 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
6018 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
6019 $$ LANGUAGE SQL ROWS 10;
6021 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
6022 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
6023 $$ LANGUAGE SQL ROWS 10;
6025 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
6026 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
6027 $$ LANGUAGE SQL ROWS 10;
6029 CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
6030 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
6031 $$ LANGUAGE SQL ROWS 10;
6033 CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
6034 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
6035 $$ LANGUAGE SQL ROWS 10;
6037 CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
6038 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
6039 $$ LANGUAGE SQL ROWS 10;
6042 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6043 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
6044 $$ LANGUAGE SQL ROWS 10;
6046 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6047 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
6048 $$ LANGUAGE SQL ROWS 10;
6050 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6051 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
6052 $$ LANGUAGE SQL ROWS 10;
6054 CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6055 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
6056 $$ LANGUAGE SQL ROWS 10;
6058 CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6059 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
6060 $$ LANGUAGE SQL ROWS 10;
6062 CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6063 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
6064 $$ LANGUAGE SQL ROWS 10;
6067 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6068 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
6069 $$ LANGUAGE SQL ROWS 10;
6071 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6072 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
6073 $$ LANGUAGE SQL ROWS 10;
6075 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6076 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
6077 $$ LANGUAGE SQL ROWS 10;
6079 CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6080 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
6081 $$ LANGUAGE SQL ROWS 10;
6083 CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6084 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
6085 $$ LANGUAGE SQL ROWS 10;
6087 CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6088 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
6089 $$ LANGUAGE SQL ROWS 10;
6092 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6093 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
6094 $$ LANGUAGE SQL ROWS 10;
6096 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6097 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
6098 $$ LANGUAGE SQL ROWS 10;
6100 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6101 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
6102 $$ LANGUAGE SQL ROWS 10;
6104 CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6105 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
6106 $$ LANGUAGE SQL ROWS 10;
6108 CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6109 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
6110 $$ LANGUAGE SQL ROWS 10;
6112 CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
6113 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
6114 $$ LANGUAGE SQL ROWS 10;
6118 -- Evergreen DB patch 0641.schema.org_unit_setting_json_check.sql
6122 -- check whether patch can be applied
6123 SELECT evergreen.upgrade_deps_block_check('0641', :eg_version);
6125 ALTER TABLE actor.org_unit_setting ADD CONSTRAINT aous_must_be_json CHECK ( is_json(value) );
6127 -- Evergreen DB patch 0642.data.acq-worksheet-hold-count.sql
6129 -- check whether patch can be applied
6130 SELECT evergreen.upgrade_deps_block_check('0642', :eg_version);
6132 UPDATE action_trigger.event_definition SET template =
6135 [%- SET li = target; -%]
6136 <div class="wrapper">
6137 <div class="summary" style='font-size:110%; font-weight:bold;'>
6139 <div>Title: [% helpers.get_li_attr("title", "", li.attributes) %]</div>
6140 <div>Author: [% helpers.get_li_attr("author", "", li.attributes) %]</div>
6141 <div class="count">Item Count: [% li.lineitem_details.size %]</div>
6142 <div class="lineid">Lineitem ID: [% li.id %]</div>
6143 <div>Open Holds: [% helpers.bre_open_hold_count(li.eg_bib_id) %]</div>
6145 [% IF li.distribution_formulas.size > 0 %]
6146 [% SET forms = [] %]
6147 [% FOREACH form IN li.distribution_formulas; forms.push(form.formula.name); END %]
6148 <div>Distribution Formulas: [% forms.join(',') %]</div>
6151 [% IF li.lineitem_notes.size > 0 %]
6154 [%- FOR note IN li.lineitem_notes -%]
6156 [% IF note.alert_text %]
6157 [% note.alert_text.code -%]
6158 [% IF note.value -%]
6175 <th>Call Number</th>
6177 <th>Shelving Location</th>
6183 [% FOREACH detail IN li.lineitem_details.sort('owning_lib') %]
6185 IF detail.eg_copy_id;
6186 SET copy = detail.eg_copy_id;
6187 SET cn_label = copy.call_number.label;
6190 SET cn_label = detail.cn_label;
6194 <!-- acq.lineitem_detail.id = [%- detail.id -%] -->
6195 <td style='padding:5px;'>[% detail.owning_lib.shortname %]</td>
6196 <td style='padding:5px;'>[% IF copy.barcode %]<span class="barcode" >[% detail.barcode %]</span>[% END %]</td>
6197 <td style='padding:5px;'>[% IF cn_label %]<span class="cn_label" >[% cn_label %]</span>[% END %]</td>
6198 <td style='padding:5px;'>[% IF detail.fund %]<span class="fund">[% detail.fund.code %] ([% detail.fund.year %])</span>[% END %]</td>
6199 <td style='padding:5px;'>[% copy.location.name %]</td>
6200 <td style='padding:5px;'>[% IF detail.recv_time %]<span class="recv_time">[% detail.recv_time %]</span>[% END %]</td>
6201 <td style='padding:5px;'>[% detail.note %]</td>
6211 SELECT evergreen.upgrade_deps_block_check('0643', :eg_version);
6219 FROM authority.record_entry
6222 AND id NOT IN (SELECT DISTINCT record FROM authority.simple_heading)
6224 INSERT INTO authority.simple_heading (record,atag,value,sort_value)
6225 SELECT record, atag, value, sort_value FROM authority.simple_heading_set(x);
6232 SELECT evergreen.upgrade_deps_block_check('0644', :eg_version);
6234 INSERT into config.org_unit_setting_type (name, grp, label, description, datatype) VALUES
6235 ( 'circ.holds.target_when_closed', 'circ',
6236 oils_i18n_gettext('circ.holds.target_when_closed',
6237 'Target copies for a hold even if copy''s circ lib is closed',
6239 oils_i18n_gettext('circ.holds.target_when_closed',
6240 'If this setting is true at a given org unit or one of its ancestors, the hold targeter will target copies from this org unit even if the org unit is closed (according to the actor.org_unit.closed_date table).',
6241 'coust', 'description'),
6243 ( 'circ.holds.target_when_closed_if_at_pickup_lib', 'circ',
6244 oils_i18n_gettext('circ.holds.target_when_closed_if_at_pickup_lib',
6245 'Target copies for a hold even if copy''s circ lib is closed IF the circ lib is the hold''s pickup lib',
6247 oils_i18n_gettext('circ.holds.target_when_closed_if_at_pickup_lib',
6248 'If this setting is true at a given org unit or one of its ancestors, the hold targeter will target copies from this org unit even if the org unit is closed (according to the actor.org_unit.closed_date table) IF AND ONLY IF the copy''s circ lib is the same as the hold''s pickup lib.',
6249 'coust', 'description'),
6253 -- Evergreen DB patch XXXX.data.hold-notification-cleanup-mod.sql
6255 -- check whether patch can be applied
6256 SELECT evergreen.upgrade_deps_block_check('0647', :eg_version);
6258 INSERT INTO action_trigger.cleanup ( module, description ) VALUES (
6259 'CreateHoldNotification',
6261 'CreateHoldNotification',
6262 'Creates a hold_notification record for each notified hold',
6268 UPDATE action_trigger.event_definition
6270 cleanup_success = 'CreateHoldNotification'
6272 id = 5 -- stock hold-ready email event_def
6273 AND cleanup_success IS NULL; -- don't clobber any existing cleanup mod
6275 -- Evergreen DB patch XXXX.schema.unnest-hold-permit-upgrade-script-repair.sql
6277 -- This patch makes no changes to the baseline schema and is
6278 -- only meant to repair a previous upgrade script.
6281 -- check whether patch can be applied
6282 SELECT evergreen.upgrade_deps_block_check('0651', :eg_version);
6284 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
6287 user_object actor.usr%ROWTYPE;
6288 age_protect_object config.rule_age_hold_protect%ROWTYPE;
6289 standing_penalty config.standing_penalty%ROWTYPE;
6290 transit_range_ou_type actor.org_unit_type%ROWTYPE;
6291 transit_source actor.org_unit%ROWTYPE;
6292 item_object asset.copy%ROWTYPE;
6293 item_cn_object asset.call_number%ROWTYPE;
6294 ou_skip actor.org_unit_setting%ROWTYPE;
6295 result action.matrix_test_result;
6296 hold_test config.hold_matrix_matchpoint%ROWTYPE;
6297 use_active_date TEXT;
6298 age_protect_date TIMESTAMP WITH TIME ZONE;
6300 hold_transit_prox INT;
6301 frozen_hold_count INT;
6302 context_org_list INT[];
6305 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
6306 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
6308 result.success := TRUE;
6310 -- Fail if we couldn't find a user
6311 IF user_object.id IS NULL THEN
6312 result.fail_part := 'no_user';
6313 result.success := FALSE;
6319 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
6321 -- Fail if we couldn't find a copy
6322 IF item_object.id IS NULL THEN
6323 result.fail_part := 'no_item';
6324 result.success := FALSE;
6330 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
6331 result.matchpoint := matchpoint_id;
6333 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
6335 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
6336 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
6337 result.fail_part := 'circ.holds.target_skip_me';
6338 result.success := FALSE;
6344 -- Fail if user is barred
6345 IF user_object.barred IS TRUE THEN
6346 result.fail_part := 'actor.usr.barred';
6347 result.success := FALSE;
6353 -- Fail if we couldn't find any matchpoint (requires a default)
6354 IF matchpoint_id IS NULL THEN
6355 result.fail_part := 'no_matchpoint';
6356 result.success := FALSE;
6362 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
6364 IF hold_test.holdable IS FALSE THEN
6365 result.fail_part := 'config.hold_matrix_test.holdable';
6366 result.success := FALSE;
6371 IF hold_test.transit_range IS NOT NULL THEN
6372 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
6373 IF hold_test.distance_is_from_owner THEN
6374 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
6376 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
6379 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
6382 result.fail_part := 'transit_range';
6383 result.success := FALSE;
6389 FOR standing_penalty IN
6390 SELECT DISTINCT csp.*
6391 FROM actor.usr_standing_penalty usp
6392 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
6393 WHERE usr = match_user
6394 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
6395 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
6396 AND csp.block_list LIKE '%HOLD%' LOOP
6398 result.fail_part := standing_penalty.name;
6399 result.success := FALSE;
6404 IF hold_test.stop_blocked_user IS TRUE THEN
6405 FOR standing_penalty IN
6406 SELECT DISTINCT csp.*
6407 FROM actor.usr_standing_penalty usp
6408 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
6409 WHERE usr = match_user
6410 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
6411 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
6412 AND csp.block_list LIKE '%CIRC%' LOOP
6414 result.fail_part := standing_penalty.name;
6415 result.success := FALSE;
6421 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
6422 SELECT INTO hold_count COUNT(*)
6423 FROM action.hold_request
6424 WHERE usr = match_user
6425 AND fulfillment_time IS NULL
6426 AND cancel_time IS NULL
6427 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
6429 IF hold_count >= hold_test.max_holds THEN
6430 result.fail_part := 'config.hold_matrix_test.max_holds';
6431 result.success := FALSE;
6437 IF item_object.age_protect IS NOT NULL THEN
6438 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
6439 IF hold_test.distance_is_from_owner THEN
6440 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
6442 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
6444 IF use_active_date = 'true' THEN
6445 age_protect_date := COALESCE(item_object.active_date, NOW());
6447 age_protect_date := item_object.create_date;
6449 IF age_protect_date + age_protect_object.age > NOW() THEN
6450 IF hold_test.distance_is_from_owner THEN
6451 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
6452 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
6454 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
6457 IF hold_transit_prox > age_protect_object.prox THEN
6458 result.fail_part := 'config.rule_age_hold_protect.prox';
6459 result.success := FALSE;
6472 $func$ LANGUAGE plpgsql;