1 --Upgrade Script for 2.10.7 to 2.11.0
2 \set eg_version '''2.11.0'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.11.0', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0979', :eg_version);
8 -- Replace connectby from the tablefunc extension with CTEs
11 CREATE OR REPLACE FUNCTION permission.grp_ancestors( INT ) RETURNS SETOF permission.grp_tree AS $$
12 WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
15 SELECT ou.parent, ouad.distance+1
16 FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad ON (ou.id = ouad.id)
17 WHERE ou.parent IS NOT NULL
19 SELECT ou.* FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;
20 $$ LANGUAGE SQL ROWS 1;
22 -- Add a utility function to find descendant groups.
24 CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permission.grp_tree AS $$
25 WITH RECURSIVE descendant_depth AS (
28 FROM permission.grp_tree gr
33 FROM permission.grp_tree gr
34 JOIN descendant_depth dd ON (dd.id = gr.parent)
35 ) SELECT gr.* FROM permission.grp_tree gr JOIN descendant_depth USING (id);
36 $$ LANGUAGE SQL ROWS 1;
38 -- Add utility functions to work with permission groups as general tree-ish sets.
40 CREATE OR REPLACE FUNCTION permission.grp_tree_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$
42 FROM permission.grp_ancestors($1)
45 FROM permission.grp_descendants($1);
46 $$ LANGUAGE SQL STABLE ROWS 1;
48 CREATE OR REPLACE FUNCTION permission.grp_tree_combined_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
50 FROM permission.grp_ancestors($1)
53 FROM permission.grp_ancestors($2);
54 $$ LANGUAGE SQL STABLE ROWS 1;
56 CREATE OR REPLACE FUNCTION permission.grp_tree_common_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$
58 FROM permission.grp_ancestors($1)
61 FROM permission.grp_ancestors($2);
62 $$ LANGUAGE SQL STABLE ROWS 1;
66 SELECT evergreen.upgrade_deps_block_check('0980', :eg_version);
68 ALTER TABLE vandelay.merge_profile ADD COLUMN update_bib_source BOOLEAN NOT NULL DEFAULT false;
69 UPDATE vandelay.merge_profile SET update_bib_source = true WHERE id=2;
71 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
82 SELECT q.marc, q.bib_source INTO v_marc, v_bib_source
83 FROM vandelay.queued_bib_record q
84 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
87 IF v_marc IS NULL THEN
88 -- RAISE NOTICE 'no marc for vandelay or bib record';
92 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
93 UPDATE vandelay.queued_bib_record
94 SET imported_as = eg_id,
98 SELECT q.update_bib_source INTO update_bib FROM vandelay.merge_profile q where q.id = merge_profile_id;
101 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
103 IF editor_string IS NOT NULL AND editor_string <> '' THEN
104 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
106 IF editor_id IS NULL THEN
107 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
110 IF editor_id IS NOT NULL THEN
111 --only update the edit date if we have a valid editor
112 update_fields := ARRAY_APPEND(update_fields, 'editor = ' || editor_id || ', edit_date = NOW()');
116 IF v_bib_source IS NOT NULL THEN
117 update_fields := ARRAY_APPEND(update_fields, 'source = ' || v_bib_source);
120 IF ARRAY_LENGTH(update_fields, 1) > 0 THEN
121 update_query := 'UPDATE biblio.record_entry SET ' || ARRAY_TO_STRING(update_fields, ',') || ' WHERE id = ' || eg_id || ';';
122 --RAISE NOTICE 'query: %', update_query;
123 EXECUTE update_query;
130 -- RAISE NOTICE 'update of biblio.record_entry failed';
138 SELECT evergreen.upgrade_deps_block_check('0982', :eg_version);
140 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
144 RETURNS SETOF INTEGER AS $$
146 -- Return a set of all the org units for which a given user has a given
147 -- permission, granted directly (not through inheritance from a parent
150 -- The permissions apply to a minimum depth of the org unit hierarchy,
151 -- for the org unit(s) to which the user is assigned. (They also apply
152 -- to the subordinates of those org units, but we don't report the
153 -- subordinates here.)
155 -- For purposes of this function, the permission.usr_work_ou_map table
156 -- defines which users belong to which org units. I.e. we ignore the
157 -- home_ou column of actor.usr.
159 -- The result set may contain duplicates, which should be eliminated
160 -- by a DISTINCT clause.
169 n_curr_depth INTEGER;
172 -- Check for superuser
182 return; -- No user? No permissions.
185 -- Super user has all permissions everywhere
195 RETURN NEXT n_work_ou;
200 -- Translate the permission name
201 -- to a numeric permission id
211 RETURN; -- No such permission
214 -- Find the highest-level org unit (i.e. the minimum depth)
215 -- to which the permission is applied for this user
217 -- This query is modified from the one in permission.usr_perms().
219 SELECT INTO n_min_depth
223 FROM permission.usr_perm_map upm
224 WHERE upm.usr = user_id
225 AND (upm.perm = n_perm OR upm.perm = -1)
228 FROM permission.grp_perm_map gpm
229 WHERE (gpm.perm = n_perm OR gpm.perm = -1)
231 SELECT (permission.grp_ancestors(
232 (SELECT profile FROM actor.usr WHERE id = user_id)
237 FROM permission.grp_perm_map p
238 WHERE (p.perm = n_perm OR p.perm = -1)
240 SELECT (permission.grp_ancestors(m.grp)).id
241 FROM permission.usr_grp_map m
242 WHERE m.usr = user_id
247 RETURN; -- No such permission for this user
250 -- Identify the org units to which the user is assigned. Note that
251 -- we pay no attention to the home_ou column in actor.usr.
257 permission.usr_work_ou_map
260 LOOP -- For each org unit to which the user is assigned
262 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
263 -- We take it on faith that this depth agrees with the actual hierarchy
264 -- defined in actor.org_unit.
269 actor.org_unit_type type
270 INNER JOIN actor.org_unit ou
271 ON ( ou.ou_type = type.id )
276 CONTINUE; -- Maybe raise exception?
279 -- Compare the depth of the work org unit to the
280 -- minimum depth, and branch accordingly
282 IF n_depth = n_min_depth THEN
284 -- The org unit is at the right depth, so return it.
286 RETURN NEXT n_work_ou;
287 ELSIF n_depth > n_min_depth THEN
289 -- Traverse the org unit tree toward the root,
290 -- until you reach the minimum depth determined above
292 n_curr_depth := n_depth;
293 n_curr_ou := n_work_ou;
294 WHILE n_curr_depth > n_min_depth LOOP
295 SELECT INTO n_curr_ou
303 n_curr_depth := n_curr_depth - 1;
306 -- This can happen only if the hierarchy defined in
307 -- actor.org_unit is corrupted, or out of sync with
308 -- the depths defined in actor.org_unit_type.
309 -- Maybe we should raise an exception here, instead
310 -- of silently ignoring the problem.
317 IF n_curr_ou IS NOT NULL THEN
318 RETURN NEXT n_curr_ou;
322 -- The permission applies only at a depth greater than the work org unit.
323 -- Use connectby() to find all dependent org units at the specified depth.
327 FROM actor.org_unit_descendants_distance(n_work_ou)
329 distance = n_min_depth - n_depth
331 RETURN NEXT n_curr_ou;
340 $$ LANGUAGE 'plpgsql' ROWS 1;
343 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
347 RETURNS SETOF INTEGER AS $$
349 -- Return a set of all the org units for which a given user has a given
350 -- permission, granted either directly or through inheritance from a parent
353 -- The permissions apply to a minimum depth of the org unit hierarchy, and
354 -- to the subordinates of those org units, for the org unit(s) to which the
357 -- For purposes of this function, the permission.usr_work_ou_map table
358 -- assigns users to org units. I.e. we ignore the home_ou column of actor.usr.
360 -- The result set may contain duplicates, which should be eliminated
361 -- by a DISTINCT clause.
368 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
371 -- The permission applies only at a depth greater than the work org unit.
375 FROM actor.org_unit_descendants(n_head_ou)
377 RETURN NEXT n_child_ou;
384 $$ LANGUAGE 'plpgsql' ROWS 1;
387 \qecho The tablefunc database extension is no longer necessary for Evergreen.
388 \qecho Unless you use some of its functions in your own scripts, you may
389 \qecho want to run the following command in the database to drop it:
390 \qecho DROP EXTENSION tablefunc;
393 SELECT evergreen.upgrade_deps_block_check('0983', :eg_version);
395 -- Create these so that the queries in the UDFs will validate
396 CREATE TEMP TABLE precalc_filter_bib_list (
400 CREATE TEMP TABLE precalc_bib_filter_bib_list (
404 CREATE TEMP TABLE precalc_src_filter_bib_list (
408 CREATE TEMP TABLE precalc_copy_filter_bib_list (
413 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list (
418 CREATE TEMP TABLE precalc_location_filter_bib_list (
423 CREATE TEMP TABLE precalc_attr_filter_bib_list (
427 CREATE TEMP TABLE precalc_bibs_by_copy_list (
431 CREATE TEMP TABLE precalc_bibs_by_uri_list (
435 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list (
439 CREATE TEMP TABLE precalc_bib_list (
443 -- rhrr needs to be a real table, so it can be fast. To that end, we use
444 -- a materialized view updated via a trigger.
446 DROP VIEW reporter.hold_request_record;
448 CREATE TABLE reporter.hold_request_record AS
456 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
458 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
459 WHEN hold_type IN ('C','R','F')
460 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
462 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
464 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
466 FROM action.hold_request ahr;
468 CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id);
469 CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record);
471 ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx;
473 CREATE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
475 IF TG_OP = 'INSERT' THEN
476 INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
481 WHEN NEW.hold_type = 'T'
483 WHEN NEW.hold_type = 'I'
484 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
485 WHEN NEW.hold_type = 'V'
486 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
487 WHEN NEW.hold_type IN ('C','R','F')
488 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
489 WHEN NEW.hold_type = 'M'
490 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
491 WHEN NEW.hold_type = 'P'
492 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
494 ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
495 UPDATE reporter.hold_request_record
496 SET target = NEW.target,
497 hold_type = NEW.hold_type,
499 WHEN NEW.hold_type = 'T'
501 WHEN NEW.hold_type = 'I'
502 THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
503 WHEN NEW.hold_type = 'V'
504 THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
505 WHEN NEW.hold_type IN ('C','R','F')
506 THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
507 WHEN NEW.hold_type = 'M'
508 THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
509 WHEN NEW.hold_type = 'P'
510 THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
517 CREATE TRIGGER reporter_hold_request_record_trigger AFTER INSERT OR UPDATE ON action.hold_request
518 FOR EACH ROW EXECUTE PROCEDURE reporter.hold_request_record_mapper();
520 CREATE SCHEMA rating;
522 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
524 'OPAC Default Sort (titlesort, authorsort, pubdate, popularity, poprel, or empty for relevance)',
529 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
530 'search.max_popularity_importance_multiplier',
532 'search.max_popularity_importance_multiplier',
533 'Maximum popularity importance multiplier for popularity-adjusted relevance searches (decimal value between 1.0 and 2.0)',
541 CREATE TABLE rating.popularity_parameter (
543 name TEXT NOT NULL UNIQUE, -- i18n
546 require_horizon BOOL NOT NULL DEFAULT FALSE,
547 require_importance BOOL NOT NULL DEFAULT FALSE,
548 require_percentile BOOL NOT NULL DEFAULT FALSE
551 INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
552 (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
553 (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
554 (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
555 (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
556 (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
557 (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
558 (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
559 (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
560 (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
561 (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
562 (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
563 (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
564 (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
565 (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
566 (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);
568 CREATE TABLE rating.badge (
569 id SERIAL PRIMARY KEY,
572 scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
573 weight INT NOT NULL DEFAULT 1,
574 horizon_age INTERVAL,
575 importance_age INTERVAL,
576 importance_interval INTERVAL NOT NULL DEFAULT '1 day',
577 importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0),
578 recalc_interval INTERVAL NOT NULL DEFAULT '1 month',
580 src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
581 circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
582 loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
583 popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
584 fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
585 percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
586 discard INT NOT NULL DEFAULT 0,
587 last_calc TIMESTAMPTZ,
588 CONSTRAINT unique_name_scope UNIQUE (name,scope)
591 CREATE TABLE rating.record_badge_score (
592 id BIGSERIAL PRIMARY KEY,
593 record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
594 badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
595 score INT NOT NULL CHECK (score BETWEEN -5 AND 5),
596 CONSTRAINT unique_record_badge UNIQUE (record,badge)
598 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
599 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
601 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
604 array_agg(tree) AS orgs
606 (actor.org_unit_descendants(id)).id AS tree
614 JOIN org_scope s ON (b.scope = s.id);
616 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
622 SET LOCAL client_min_messages = error;
623 DROP TABLE IF EXISTS precalc_src_filter_bib_list;
624 IF src IS NOT NULL THEN
625 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
626 SELECT id FROM biblio.record_entry
627 WHERE source = src AND NOT deleted;
629 CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
630 SELECT id FROM biblio.record_entry
631 WHERE id > 0 AND NOT deleted;
634 SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
637 $f$ LANGUAGE PLPGSQL;
639 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
645 SET LOCAL client_min_messages = error;
646 DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
647 IF cm IS NOT NULL THEN
648 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
649 SELECT cn.record AS id,
651 FROM asset.call_number cn
652 JOIN asset.copy cp ON (cn.id = cp.call_number)
653 WHERE cp.circ_modifier = cm
656 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
657 SELECT cn.record AS id,
659 FROM asset.call_number cn
660 JOIN asset.copy cp ON (cn.id = cp.call_number)
661 WHERE NOT cp.deleted;
664 SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
667 $f$ LANGUAGE PLPGSQL;
669 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
675 SET LOCAL client_min_messages = error;
676 DROP TABLE IF EXISTS precalc_location_filter_bib_list;
677 IF loc IS NOT NULL THEN
678 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
679 SELECT cn.record AS id,
681 FROM asset.call_number cn
682 JOIN asset.copy cp ON (cn.id = cp.call_number)
683 JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
684 WHERE lg.lgroup = loc
687 CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
688 SELECT cn.record AS id,
690 FROM asset.call_number cn
691 JOIN asset.copy cp ON (cn.id = cp.call_number)
692 WHERE NOT cp.deleted;
695 SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
698 $f$ LANGUAGE PLPGSQL;
701 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
708 SET LOCAL client_min_messages = error;
709 DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
710 IF attr_filter IS NOT NULL THEN
711 afilter := metabib.compile_composite_attr(attr_filter);
712 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
713 SELECT source AS id FROM metabib.record_attr_vector_list
714 WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
716 CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
717 SELECT source AS id FROM metabib.record_attr_vector_list;
720 SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
723 $f$ LANGUAGE PLPGSQL;
725 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
729 badge_row rating.badge_with_orgs%ROWTYPE;
734 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
736 SET LOCAL client_min_messages = error;
737 DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
738 CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
739 SELECT DISTINCT cn.record AS id
740 FROM asset.call_number cn
741 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
742 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
743 WHERE cn.owning_lib = ANY (badge_row.orgs)
746 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
749 $f$ LANGUAGE PLPGSQL;
751 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
755 badge_row rating.badge_with_orgs%ROWTYPE;
758 SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
760 SET LOCAL client_min_messages = error;
761 DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
762 CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
763 SELECT DISTINCT record AS id
764 FROM asset.call_number cn
765 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
766 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
767 WHERE cn.owning_lib = ANY (badge_row.orgs)
768 AND cn.label = '##URI##'
771 SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
774 $f$ LANGUAGE PLPGSQL;
776 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
782 PERFORM rating.precalc_bibs_by_copy(badge_id);
783 PERFORM rating.precalc_bibs_by_uri(badge_id);
785 SET LOCAL client_min_messages = error;
786 DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
787 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
788 SELECT id FROM precalc_bibs_by_copy_list
790 SELECT id FROM precalc_bibs_by_uri_list;
792 SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
795 $f$ LANGUAGE PLPGSQL;
798 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
800 badge_row rating.badge%ROWTYPE;
801 param rating.popularity_parameter%ROWTYPE;
803 SET LOCAL client_min_messages = error;
805 -- Find what we're doing
806 SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
807 SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
809 -- Calculate the filtered bib set, or all bibs if none
810 PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
811 PERFORM rating.precalc_src_filter(badge_row.src_filter);
812 PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
813 PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
815 -- Bring the bib-level filter lists together
816 DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
817 CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
818 SELECT id FROM precalc_attr_filter_bib_list
820 SELECT id FROM precalc_src_filter_bib_list;
822 -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
823 DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
824 CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
825 SELECT id, copy FROM precalc_circ_mod_filter_bib_list
827 SELECT id, copy FROM precalc_location_filter_bib_list;
829 -- Bring the collapsed filter lists together
830 DROP TABLE IF EXISTS precalc_filter_bib_list;
831 CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
832 SELECT id FROM precalc_bib_filter_bib_list
834 SELECT id FROM precalc_copy_filter_bib_list;
836 CREATE INDEX precalc_filter_bib_list_idx
837 ON precalc_filter_bib_list (id);
843 -- If it's a fixed-rating badge, just do it ...
844 IF badge_row.fixed_rating IS NOT NULL THEN
845 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
847 INSERT INTO rating.record_badge_score (record, badge, score)
848 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
849 USING badge_id, badge_row.fixed_rating;
851 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
857 -- Make a session-local scratchpad for calculating scores
858 CREATE TEMP TABLE record_score_scratchpad (
865 INSERT INTO record_score_scratchpad (bib, value)
866 SELECT * FROM $e$ || param.func || $e$($1)$e$
869 IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
870 -- To speed up discard-common
871 CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
872 ANALYZE record_score_scratchpad;
875 IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
876 DELETE FROM record_score_scratchpad WHERE value IN (
877 SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
881 IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
882 DELETE FROM record_score_scratchpad WHERE value <= (
885 CUME_DIST() OVER (ORDER BY value) AS p
886 FROM record_score_scratchpad
887 ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
892 -- And, finally, push new data in
893 DELETE FROM rating.record_badge_score WHERE badge = badge_id;
894 INSERT INTO rating.record_badge_score (badge, record, score)
897 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
898 FROM record_score_scratchpad;
900 DROP TABLE record_score_scratchpad;
902 -- Now, finally-finally, mark the badge as recalculated
903 UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
907 $f$ LANGUAGE PLPGSQL;
909 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
910 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
912 badge rating.badge_with_orgs%ROWTYPE;
915 iscale NUMERIC := NULL;
918 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
920 IF badge.horizon_age IS NULL THEN
921 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
926 PERFORM rating.precalc_bibs_by_copy(badge_id);
928 SET LOCAL client_min_messages = error;
929 DROP TABLE IF EXISTS precalc_bib_list;
930 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
931 SELECT id FROM precalc_filter_bib_list
933 SELECT id FROM precalc_bibs_by_copy_list;
935 iint := EXTRACT(EPOCH FROM badge.importance_interval);
936 IF badge.importance_age IS NOT NULL THEN
937 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
940 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
941 iscale := COALESCE(badge.importance_scale, 1.0);
945 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
948 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
949 COUNT(h.id)::INT AS holds
950 FROM action.hold_request h
951 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
952 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
953 WHERE h.fulfillment_time >= NOW() - badge.horizon_age
954 AND h.request_lib = ANY (badge.orgs)
959 $f$ LANGUAGE PLPGSQL STRICT;
961 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
962 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
964 badge rating.badge_with_orgs%ROWTYPE;
967 iscale NUMERIC := NULL;
970 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
972 IF badge.horizon_age IS NULL THEN
973 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
978 PERFORM rating.precalc_bibs_by_copy(badge_id);
980 SET LOCAL client_min_messages = error;
981 DROP TABLE IF EXISTS precalc_bib_list;
982 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
983 SELECT id FROM precalc_filter_bib_list
985 SELECT id FROM precalc_bibs_by_copy_list;
987 iint := EXTRACT(EPOCH FROM badge.importance_interval);
988 IF badge.importance_age IS NOT NULL THEN
989 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
992 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
993 iscale := COALESCE(badge.importance_scale, 1.0);
997 SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
1000 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
1001 COUNT(h.id)::INT AS holds
1002 FROM action.hold_request h
1003 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
1004 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
1005 WHERE h.request_time >= NOW() - badge.horizon_age
1006 AND h.request_lib = ANY (badge.orgs)
1011 $f$ LANGUAGE PLPGSQL STRICT;
1013 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
1014 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1016 badge rating.badge_with_orgs%ROWTYPE;
1019 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1021 PERFORM rating.precalc_bibs_by_copy(badge_id);
1023 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1024 SELECT id FROM precalc_filter_bib_list
1026 SELECT id FROM precalc_bibs_by_copy_list
1029 ANALYZE precalc_copy_filter_bib_list;
1032 SELECT rhrr.bib_record AS bib,
1033 COUNT(DISTINCT h.id)::NUMERIC AS holds
1034 FROM action.hold_request h
1035 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
1036 JOIN action.hold_copy_map m ON (m.hold = h.id)
1037 JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
1038 WHERE h.fulfillment_time IS NULL
1039 AND h.request_lib = ANY (badge.orgs)
1042 $f$ LANGUAGE PLPGSQL STRICT;
1044 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
1045 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1047 badge rating.badge_with_orgs%ROWTYPE;
1050 iscale NUMERIC := NULL;
1053 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1055 IF badge.horizon_age IS NULL THEN
1056 RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
1061 PERFORM rating.precalc_bibs_by_copy(badge_id);
1063 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1064 SELECT id FROM precalc_filter_bib_list
1066 SELECT id FROM precalc_bibs_by_copy_list
1069 ANALYZE precalc_copy_filter_bib_list;
1071 iint := EXTRACT(EPOCH FROM badge.importance_interval);
1072 IF badge.importance_age IS NOT NULL THEN
1073 iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
1076 -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
1077 iscale := COALESCE(badge.importance_scale, 1.0);
1081 SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
1083 SELECT cn.record AS bib,
1084 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
1085 COUNT(c.id)::INT AS circs
1086 FROM action.circulation c
1087 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
1088 JOIN asset.copy cp ON (cp.id = c.target_copy)
1089 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1090 WHERE c.xact_start >= NOW() - badge.horizon_age
1091 AND cn.owning_lib = ANY (badge.orgs)
1092 AND c.phone_renewal IS FALSE -- we don't count renewals
1093 AND c.desk_renewal IS FALSE
1094 AND c.opac_renewal IS FALSE
1099 $f$ LANGUAGE PLPGSQL STRICT;
1101 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
1102 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1104 badge rating.badge_with_orgs%ROWTYPE;
1107 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1109 PERFORM rating.precalc_bibs_by_copy(badge_id);
1111 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1112 SELECT id FROM precalc_filter_bib_list
1114 SELECT id FROM precalc_bibs_by_copy_list
1117 ANALYZE precalc_copy_filter_bib_list;
1120 SELECT cn.record AS bib,
1121 COUNT(c.id)::NUMERIC AS circs
1122 FROM action.circulation c
1123 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
1124 JOIN asset.copy cp ON (cp.id = c.target_copy)
1125 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1126 WHERE c.checkin_time IS NULL
1127 AND cn.owning_lib = ANY (badge.orgs)
1131 $f$ LANGUAGE PLPGSQL STRICT;
1133 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
1134 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1136 badge rating.badge_with_orgs%ROWTYPE;
1139 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1141 PERFORM rating.precalc_bibs_by_copy(badge_id);
1143 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1144 SELECT id FROM precalc_filter_bib_list
1146 SELECT id FROM precalc_bibs_by_copy_list
1149 ANALYZE precalc_copy_filter_bib_list;
1153 SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
1154 FROM (SELECT cn.record AS bib,
1155 (cp.status = 1)::INT AS checked_out,
1158 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
1159 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1160 WHERE cn.owning_lib = ANY (badge.orgs)
1164 $f$ LANGUAGE PLPGSQL STRICT;
1166 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
1167 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1169 badge rating.badge_with_orgs%ROWTYPE;
1172 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1174 PERFORM rating.precalc_bibs_by_copy(badge_id);
1176 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1177 SELECT id FROM precalc_filter_bib_list
1179 SELECT id FROM precalc_bibs_by_copy_list
1182 ANALYZE precalc_copy_filter_bib_list;
1185 SELECT cn.record AS bib,
1186 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
1188 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
1189 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1190 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
1191 WHERE cn.owning_lib = ANY (badge.orgs)
1194 $f$ LANGUAGE PLPGSQL STRICT;
1196 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
1197 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1199 badge rating.badge_with_orgs%ROWTYPE;
1202 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1204 PERFORM rating.precalc_bibs_by_copy(badge_id);
1206 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1207 SELECT id FROM precalc_filter_bib_list
1209 SELECT id FROM precalc_bibs_by_copy_list
1212 ANALYZE precalc_copy_filter_bib_list;
1215 SELECT cn.record AS bib,
1216 COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
1218 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
1219 JOIN asset.copy_location cl ON (cl.id = cp.location)
1220 JOIN config.copy_status cs ON (cs.id = cp.status)
1221 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1222 JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
1223 WHERE cn.owning_lib = ANY (badge.orgs)
1224 AND cp.holdable IS TRUE
1225 AND cl.holdable IS TRUE
1226 AND cs.holdable IS TRUE
1229 $f$ LANGUAGE PLPGSQL STRICT;
1231 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
1232 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1234 badge rating.badge_with_orgs%ROWTYPE;
1237 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1239 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
1241 SET LOCAL client_min_messages = error;
1242 DROP TABLE IF EXISTS precalc_bib_list;
1243 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
1244 SELECT id FROM precalc_filter_bib_list
1246 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
1250 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
1251 FROM precalc_bib_list pop
1252 JOIN biblio.record_entry b ON (b.id = pop.id);
1254 $f$ LANGUAGE PLPGSQL STRICT;
1256 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
1257 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1259 badge rating.badge_with_orgs%ROWTYPE;
1262 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1264 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
1266 SET LOCAL client_min_messages = error;
1267 DROP TABLE IF EXISTS precalc_bib_list;
1268 CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
1269 SELECT id FROM precalc_filter_bib_list
1271 SELECT id FROM precalc_bibs_by_copy_or_uri_list;
1274 SELECT pop.id AS bib,
1276 FROM precalc_bib_list pop
1277 JOIN metabib.record_sorter s ON (
1279 AND s.attr = 'pubdate'
1280 AND s.value ~ '^\d+$'
1282 WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
1284 $f$ LANGUAGE PLPGSQL STRICT;
1286 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
1287 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1289 badge rating.badge_with_orgs%ROWTYPE;
1292 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
1294 PERFORM rating.precalc_bibs_by_copy(badge_id);
1296 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
1297 SELECT id FROM precalc_filter_bib_list
1299 SELECT id FROM precalc_bibs_by_copy_list
1302 ANALYZE precalc_copy_filter_bib_list;
1306 SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
1307 FROM (SELECT cn.record AS bib,
1309 EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
1310 SUM( -- time copy spent circulating
1314 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
1318 )::NUMERIC AS circ_time
1320 JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
1321 JOIN asset.call_number cn ON (cn.id = cp.call_number)
1322 LEFT JOIN action.all_circulation circ ON (
1323 circ.target_copy = cp.id
1324 AND stop_fines NOT IN (
1331 checkin_time IS NULL AND
1332 stop_fines = 'MAXFINES'
1335 WHERE cn.owning_lib = ANY (badge.orgs)
1336 AND cp.active_date IS NOT NULL
1337 -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
1338 AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
1343 $f$ LANGUAGE PLPGSQL STRICT;
1345 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
1346 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1348 PERFORM rating.precalc_bibs_by_copy(badge_id);
1350 SELECT id, 1.0 FROM precalc_filter_bib_list
1352 SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
1354 $f$ LANGUAGE PLPGSQL STRICT;
1356 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
1357 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1359 PERFORM rating.precalc_bibs_by_uri(badge_id);
1361 SELECT id, 1.0 FROM precalc_bib_filter_bib_list
1363 SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
1365 $f$ LANGUAGE PLPGSQL STRICT;
1367 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
1368 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1370 PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
1372 (SELECT id, 1.0 FROM precalc_filter_bib_list
1374 SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
1376 (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
1378 SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
1380 $f$ LANGUAGE PLPGSQL STRICT;
1382 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
1383 RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
1386 SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
1388 $f$ LANGUAGE PLPGSQL STRICT;
1390 CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL;
1391 CREATE INDEX hold_request_time_idx ON action.hold_request (request_time);
1395 * Copyright (C) 2016 Equinox Software, Inc.
1396 * Mike Rylander <miker@esilibrary.com>
1398 * This program is free software; you can redistribute it and/or
1399 * modify it under the terms of the GNU General Public License
1400 * as published by the Free Software Foundation; either version 2
1401 * of the License, or (at your option) any later version.
1403 * This program is distributed in the hope that it will be useful,
1404 * but WITHOUT ANY WARRANTY; without even the implied warranty of
1405 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
1406 * GNU General Public License for more details.
1412 SELECT evergreen.upgrade_deps_block_check('0984', :eg_version);
1414 ALTER TYPE search.search_result ADD ATTRIBUTE badges TEXT, ADD ATTRIBUTE popularity NUMERIC;
1416 CREATE OR REPLACE FUNCTION search.query_parser_fts (
1418 param_search_ou INT,
1421 param_statuses INT[],
1422 param_locations INT[],
1428 deleted_search BOOL,
1429 param_pref_ou INT DEFAULT NULL
1430 ) RETURNS SETOF search.search_result AS $func$
1433 current_res search.search_result%ROWTYPE;
1434 search_org_list INT[];
1435 luri_org_list INT[];
1444 core_cursor REFCURSOR;
1445 core_rel_query TEXT;
1447 total_count INT := 0;
1448 check_count INT := 0;
1449 deleted_count INT := 0;
1450 visible_count INT := 0;
1451 excluded_count INT := 0;
1456 check_limit := COALESCE( param_check, 1000 );
1457 core_limit := COALESCE( param_limit, 25000 );
1458 core_offset := COALESCE( param_offset, 0 );
1460 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
1462 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
1464 IF param_search_ou > 0 THEN
1465 IF param_depth IS NOT NULL THEN
1466 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
1468 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
1471 IF luri_as_copy THEN
1472 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
1474 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
1477 ELSIF param_search_ou < 0 THEN
1478 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
1480 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
1482 IF luri_as_copy THEN
1483 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
1485 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
1488 luri_org_list := luri_org_list || tmp_int_list;
1491 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
1493 ELSIF param_search_ou = 0 THEN
1494 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
1497 IF param_pref_ou IS NOT NULL THEN
1498 IF luri_as_copy THEN
1499 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
1501 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
1504 luri_org_list := luri_org_list || tmp_int_list;
1507 OPEN core_cursor FOR EXECUTE param_query;
1511 FETCH core_cursor INTO core_result;
1512 EXIT WHEN NOT FOUND;
1513 EXIT WHEN total_count >= core_limit;
1515 total_count := total_count + 1;
1517 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
1519 check_count := check_count + 1;
1521 IF NOT deleted_search THEN
1523 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
1525 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
1526 deleted_count := deleted_count + 1;
1531 FROM biblio.record_entry b
1532 JOIN config.bib_source s ON (b.source = s.id)
1533 WHERE s.transcendant
1534 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
1537 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
1538 visible_count := visible_count + 1;
1540 current_res.id = core_result.id;
1541 current_res.rel = core_result.rel;
1542 current_res.badges = core_result.badges;
1543 current_res.popularity = core_result.popularity;
1547 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
1551 current_res.record = core_result.records[1];
1553 current_res.record = NULL;
1556 RETURN NEXT current_res;
1562 FROM asset.call_number cn
1563 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
1564 JOIN asset.uri uri ON (map.uri = uri.id)
1565 WHERE NOT cn.deleted
1566 AND cn.label = '##URI##'
1568 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
1569 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1570 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
1574 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
1575 visible_count := visible_count + 1;
1577 current_res.id = core_result.id;
1578 current_res.rel = core_result.rel;
1579 current_res.badges = core_result.badges;
1580 current_res.popularity = core_result.popularity;
1584 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
1588 current_res.record = core_result.records[1];
1590 current_res.record = NULL;
1593 RETURN NEXT current_res;
1598 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
1601 FROM asset.call_number cn
1602 JOIN asset.copy cp ON (cp.call_number = cn.id)
1603 WHERE NOT cn.deleted
1605 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
1606 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1607 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1612 FROM biblio.peer_bib_copy_map pr
1613 JOIN asset.copy cp ON (cp.id = pr.target_copy)
1614 WHERE NOT cp.deleted
1615 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
1616 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1617 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1621 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
1622 excluded_count := excluded_count + 1;
1629 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
1632 FROM asset.call_number cn
1633 JOIN asset.copy cp ON (cp.call_number = cn.id)
1634 WHERE NOT cn.deleted
1636 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
1637 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1638 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1643 FROM biblio.peer_bib_copy_map pr
1644 JOIN asset.copy cp ON (cp.id = pr.target_copy)
1645 WHERE NOT cp.deleted
1646 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
1647 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1648 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1652 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
1653 excluded_count := excluded_count + 1;
1660 IF staff IS NULL OR NOT staff THEN
1663 FROM asset.opac_visible_copies
1664 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1665 AND record IN ( SELECT * FROM unnest( core_result.records ) )
1670 FROM biblio.peer_bib_copy_map pr
1671 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
1672 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1673 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1678 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
1679 excluded_count := excluded_count + 1;
1687 FROM asset.call_number cn
1688 JOIN asset.copy cp ON (cp.call_number = cn.id)
1689 WHERE NOT cn.deleted
1691 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1692 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1698 FROM biblio.peer_bib_copy_map pr
1699 JOIN asset.copy cp ON (cp.id = pr.target_copy)
1700 WHERE NOT cp.deleted
1701 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
1702 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
1708 FROM asset.call_number cn
1709 JOIN asset.copy cp ON (cp.call_number = cn.id)
1710 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1715 -- Recheck Located URI visibility in the case of no "foreign" copies
1717 FROM asset.call_number cn
1718 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
1719 JOIN asset.uri uri ON (map.uri = uri.id)
1720 WHERE NOT cn.deleted
1721 AND cn.label = '##URI##'
1723 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
1724 AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
1728 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
1729 excluded_count := excluded_count + 1;
1733 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
1734 excluded_count := excluded_count + 1;
1745 visible_count := visible_count + 1;
1747 current_res.id = core_result.id;
1748 current_res.rel = core_result.rel;
1749 current_res.badges = core_result.badges;
1750 current_res.popularity = core_result.popularity;
1754 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
1758 current_res.record = core_result.records[1];
1760 current_res.record = NULL;
1763 RETURN NEXT current_res;
1765 IF visible_count % 1000 = 0 THEN
1766 -- RAISE NOTICE ' % visible so far ... ', visible_count;
1771 current_res.id = NULL;
1772 current_res.rel = NULL;
1773 current_res.record = NULL;
1774 current_res.badges = NULL;
1775 current_res.popularity = NULL;
1776 current_res.total = total_count;
1777 current_res.checked = check_count;
1778 current_res.deleted = deleted_count;
1779 current_res.visible = visible_count;
1780 current_res.excluded = excluded_count;
1784 RETURN NEXT current_res;
1787 $func$ LANGUAGE PLPGSQL;
1789 CREATE OR REPLACE FUNCTION metabib.staged_browse(
1793 context_locations INT[],
1795 browse_superpage_size INT,
1796 count_up_from_zero BOOL, -- if false, count down from -1
1799 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1807 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
1808 results_skipped INT := 0;
1809 row_counter INT := 0;
1814 all_records BIGINT[];
1815 all_brecords BIGINT[];
1816 all_arecords BIGINT[];
1817 superpage_of_records BIGINT[];
1820 IF count_up_from_zero THEN
1826 OPEN curs FOR EXECUTE query;
1829 FETCH curs INTO rec;
1831 IF result_row.pivot_point IS NOT NULL THEN
1832 RETURN NEXT result_row;
1838 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1839 SELECT INTO all_arecords, result_row.sees, afields
1840 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1841 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1842 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1844 FROM metabib.browse_entry_simple_heading_map mbeshm
1845 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1846 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1847 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1848 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1849 ash.atag = map.authority_field
1850 AND map.metabib_field = ANY(fields)
1852 WHERE mbeshm.entry = rec.id;
1855 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
1856 SELECT INTO all_brecords, result_row.authorities, bfields
1857 ARRAY_AGG(DISTINCT source),
1858 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
1859 ARRAY_AGG(DISTINCT def)
1860 FROM metabib.browse_entry_def_map
1861 WHERE entry = rec.id
1862 AND def = ANY(fields);
1864 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
1866 result_row.sources := 0;
1867 result_row.asources := 0;
1869 -- Bib-linked vis checking
1870 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
1872 full_end := ARRAY_LENGTH(all_brecords, 1);
1873 superpage_size := COALESCE(browse_superpage_size, full_end);
1875 slice_end := superpage_size;
1877 WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
1878 superpage_of_records := all_brecords[slice_start:slice_end];
1880 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
1881 'NULL AS badges, NULL::NUMERIC AS popularity, ' ||
1882 '1::NUMERIC AS rel FROM (SELECT UNNEST(' ||
1883 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
1885 -- We use search.query_parser_fts() for visibility testing.
1886 -- We're calling it once per browse-superpage worth of records
1887 -- out of the set of records related to a given mbe, until we've
1888 -- either exhausted that set of records or found at least 1
1891 SELECT INTO result_row.sources visible
1892 FROM search.query_parser_fts(
1893 context_org, NULL, qpfts_query, NULL,
1894 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
1896 WHERE qpfts.rel IS NULL;
1898 slice_start := slice_start + superpage_size;
1899 slice_end := slice_end + superpage_size;
1902 -- Accurate? Well, probably.
1903 result_row.accurate := browse_superpage_size IS NULL OR
1904 browse_superpage_size >= full_end;
1908 -- Authority-linked vis checking
1909 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
1911 full_end := ARRAY_LENGTH(all_arecords, 1);
1912 superpage_size := COALESCE(browse_superpage_size, full_end);
1914 slice_end := superpage_size;
1916 WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
1917 superpage_of_records := all_arecords[slice_start:slice_end];
1919 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
1920 'NULL AS badges, NULL::NUMERIC AS popularity, ' ||
1921 '1::NUMERIC AS rel FROM (SELECT UNNEST(' ||
1922 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
1924 -- We use search.query_parser_fts() for visibility testing.
1925 -- We're calling it once per browse-superpage worth of records
1926 -- out of the set of records related to a given mbe, via
1927 -- authority until we've either exhausted that set of records
1928 -- or found at least 1 visible record.
1930 SELECT INTO result_row.asources visible
1931 FROM search.query_parser_fts(
1932 context_org, NULL, qpfts_query, NULL,
1933 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
1935 WHERE qpfts.rel IS NULL;
1937 slice_start := slice_start + superpage_size;
1938 slice_end := slice_end + superpage_size;
1942 -- Accurate? Well, probably.
1943 result_row.aaccurate := browse_superpage_size IS NULL OR
1944 browse_superpage_size >= full_end;
1948 IF result_row.sources > 0 OR result_row.asources > 0 THEN
1950 -- The function that calls this function needs row_number in order
1951 -- to correctly order results from two different runs of this
1953 result_row.row_number := row_number;
1955 -- Now, if row_counter is still less than limit, return a row. If
1956 -- not, but it is less than next_pivot_pos, continue on without
1957 -- returning actual result rows until we find
1958 -- that next pivot, and return it.
1960 IF row_counter < result_limit THEN
1961 result_row.browse_entry := rec.id;
1962 result_row.value := rec.value;
1964 RETURN NEXT result_row;
1966 result_row.browse_entry := NULL;
1967 result_row.authorities := NULL;
1968 result_row.fields := NULL;
1969 result_row.value := NULL;
1970 result_row.sources := NULL;
1971 result_row.sees := NULL;
1972 result_row.accurate := NULL;
1973 result_row.aaccurate := NULL;
1974 result_row.pivot_point := rec.id;
1976 IF row_counter >= next_pivot_pos THEN
1977 RETURN NEXT result_row;
1982 IF count_up_from_zero THEN
1983 row_number := row_number + 1;
1985 row_number := row_number - 1;
1988 -- row_counter is different from row_number.
1989 -- It simply counts up from zero so that we know when
1990 -- we've reached our limit.
1991 row_counter := row_counter + 1;
1995 $p$ LANGUAGE PLPGSQL;
1999 SELECT evergreen.upgrade_deps_block_check('0985', :eg_version);
2001 CREATE OR REPLACE FUNCTION metabib.reingest_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS VOID AS $func$
2003 transformed_xml TEXT;
2004 rmarc TEXT := prmarc;
2008 xfrm config.xml_transform%ROWTYPE;
2009 attr_vector INT[] := '{}'::INT[];
2010 attr_vector_tmp INT[];
2011 attr_list TEXT[] := pattr_list;
2013 norm_attr_value TEXT[];
2015 attr_def config.record_attr_definition%ROWTYPE;
2016 ccvm_row config.coded_value_map%ROWTYPE;
2019 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
2020 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition
2023 fixed_field IS NOT NULL OR
2024 xpath IS NOT NULL OR
2025 phys_char_sf IS NOT NULL OR
2032 IF rmarc IS NULL THEN
2033 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
2036 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
2038 attr_value := '{}'::TEXT[];
2039 norm_attr_value := '{}'::TEXT[];
2040 attr_vector_tmp := '{}'::INT[];
2042 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
2044 -- tag+sf attrs only support SVF
2045 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
2046 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
2047 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
2049 AND tag LIKE attr_def.tag
2051 WHEN attr_def.sf_list IS NOT NULL
2052 THEN POSITION(subfield IN attr_def.sf_list) > 0
2059 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
2060 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
2062 IF NOT attr_def.multi THEN
2063 attr_value := ARRAY[attr_value[1]];
2066 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
2068 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
2070 -- See if we can skip the XSLT ... it's expensive
2071 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
2072 -- Can't skip the transform
2073 IF xfrm.xslt <> '---' THEN
2074 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
2076 transformed_xml := rmarc;
2079 prev_xfrm := xfrm.name;
2082 IF xfrm.name IS NULL THEN
2083 -- just grab the marcxml (empty) transform
2084 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
2085 prev_xfrm := xfrm.name;
2088 FOR tmp_xml IN SELECT UNNEST(oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]])) LOOP
2089 tmp_val := oils_xpath_string(
2092 COALESCE(attr_def.joiner,' '),
2093 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
2095 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
2096 attr_value := attr_value || tmp_val;
2097 EXIT WHEN NOT attr_def.multi;
2101 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
2102 SELECT ARRAY_AGG(m.value) INTO attr_value
2103 FROM vandelay.marc21_physical_characteristics(rmarc) v
2104 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
2105 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
2106 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
2108 IF NOT attr_def.multi THEN
2109 attr_value := ARRAY[attr_value[1]];
2114 -- apply index normalizers to attr_value
2115 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
2117 SELECT n.func AS func,
2118 n.param_count AS param_count,
2120 FROM config.index_normalizer n
2121 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
2122 WHERE attr = attr_def.name
2124 EXECUTE 'SELECT ' || normalizer.func || '(' ||
2125 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
2127 WHEN normalizer.param_count > 0
2128 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
2134 IF tmp_val IS NOT NULL AND tmp_val <> '' THEN
2135 -- note that a string that contains only blanks
2136 -- is a valid value for some attributes
2137 norm_attr_value := norm_attr_value || tmp_val;
2141 IF attr_def.filter THEN
2142 -- Create unknown uncontrolled values and find the IDs of the values
2143 IF ccvm_row.id IS NULL THEN
2144 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
2145 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
2146 BEGIN -- use subtransaction to isolate unique constraint violations
2147 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
2148 EXCEPTION WHEN unique_violation THEN END;
2152 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.uncontrolled_record_attr_value WHERE attr = attr_def.name AND value = ANY( norm_attr_value );
2154 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
2157 -- Add the new value to the vector
2158 attr_vector := attr_vector || attr_vector_tmp;
2161 IF attr_def.sorter THEN
2162 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
2163 IF norm_attr_value[1] IS NOT NULL THEN
2164 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
2170 /* We may need to rewrite the vlist to contain
2171 the intersection of new values for requested
2172 attrs and old values for ignored attrs. To
2173 do this, we take the old attr vlist and
2174 subtract any values that are valid for the
2175 requested attrs, and then add back the new
2176 set of attr values. */
2178 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
2179 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
2180 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
2181 attr_vector := attr_vector || attr_vector_tmp;
2184 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
2185 -- attributes can depend on earlier ones.
2186 PERFORM metabib.compile_composite_attr_cache_init();
2187 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
2189 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
2191 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
2192 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
2194 IF attr_def.filter THEN
2195 IF attr_vector @@ tmp_val::query_int THEN
2196 attr_vector = attr_vector + intset(ccvm_row.id);
2197 EXIT WHEN NOT attr_def.multi;
2201 IF attr_def.sorter THEN
2202 IF attr_vector @@ tmp_val THEN
2203 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
2204 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
2212 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
2213 IF rdeleted THEN -- initial insert OR revivication
2214 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
2215 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
2217 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
2223 $func$ LANGUAGE PLPGSQL;
2225 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
2228 SELECT evergreen.upgrade_deps_block_check('0986', :eg_version);
2230 CREATE EXTENSION IF NOT EXISTS unaccent SCHEMA public;
2232 CREATE OR REPLACE FUNCTION evergreen.unaccent_and_squash ( IN arg text) RETURNS text
2233 IMMUTABLE STRICT AS $$
2235 RETURN evergreen.lowercase(unaccent(regexp_replace(arg, '\s','','g')));
2237 $$ LANGUAGE PLPGSQL;
2239 -- The unaccented indices for patron name fields
2240 CREATE INDEX actor_usr_first_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(first_given_name));
2241 CREATE INDEX actor_usr_second_given_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(second_given_name));
2242 CREATE INDEX actor_usr_family_name_unaccent_idx ON actor.usr (evergreen.unaccent_and_squash(family_name));
2244 -- DB setting to control behavior; true by default
2245 INSERT INTO config.org_unit_setting_type
2246 ( name, grp, label, description, datatype )
2248 ('circ.patron_search.diacritic_insensitive',
2250 oils_i18n_gettext('circ.patron_search.diacritic_insensitive',
2251 'Patron search diacritic insensitive',
2253 oils_i18n_gettext('circ.patron_search.diacritic_insensitive',
2254 'Match patron last, first, and middle names irrespective of usage of diacritical marks or spaces. (e.g., Ines will match Inés; de la Cruz will match Delacruz)',
2255 'coust', 'description'),
2258 INSERT INTO actor.org_unit_setting (
2259 org_unit, name, value
2261 (SELECT id FROM actor.org_unit WHERE parent_ou IS NULL),
2262 'circ.patron_search.diacritic_insensitive',
2269 SELECT evergreen.upgrade_deps_block_check('0987', :eg_version);
2271 INSERT INTO config.org_unit_setting_type ( name, grp, label, description, datatype )
2273 'ui.circ.billing.amount_limit', 'gui',
2275 'ui.circ.billing.amount_limit',
2276 'Maximum payment amount allowed.',
2279 'ui.circ.billing.amount_limit',
2280 'The payment amount in the Patron Bills interface may not exceed the value of this setting.',
2281 'coust', 'description'),
2285 INSERT INTO config.org_unit_setting_type ( name, grp, label, description, datatype )
2287 'ui.circ.billing.amount_warn', 'gui',
2289 'ui.circ.billing.amount_warn',
2290 'Payment amount threshold for Are You Sure? dialog.',
2293 'ui.circ.billing.amount_warn',
2294 'In the Patron Bills interface, a payment attempt will warn if the amount exceeds the value of this setting.',
2295 'coust', 'description'),
2300 SELECT evergreen.upgrade_deps_block_check('0988', :eg_version);
2302 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
2305 use MARC::File::XML (BinaryEncoding => 'UTF-8');
2308 use Unicode::Normalize;
2310 MARC::Charset->assume_unicode(1);
2312 my $schema = $_TD->{table_schema};
2313 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
2315 my @old901s = $marc->field('901');
2316 $marc->delete_fields(@old901s);
2318 if ($schema eq 'biblio') {
2319 my $tcn_value = $_TD->{new}{tcn_value};
2321 # Set TCN value to record ID?
2322 my $id_as_tcn = spi_exec_query("
2324 FROM config.global_flag
2325 WHERE name = 'cat.bib.use_id_for_tcn'
2327 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
2328 $tcn_value = $_TD->{new}{id};
2329 $_TD->{new}{tcn_value} = $tcn_value;
2332 my $new_901 = MARC::Field->new("901", " ", " ",
2334 "b" => $_TD->{new}{tcn_source},
2335 "c" => $_TD->{new}{id},
2339 if ($_TD->{new}{owner}) {
2340 $new_901->add_subfields("o" => $_TD->{new}{owner});
2343 if ($_TD->{new}{share_depth}) {
2344 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
2347 if ($_TD->{new}{source}) {
2348 my $plan = spi_prepare('
2350 FROM config.bib_source
2354 spi_exec_prepared($plan, {limit => 1}, $_TD->{new}{source})->{rows}[0]{source};
2355 spi_freeplan($plan);
2356 $new_901->add_subfields("s" => $source_name) if $source_name;
2359 $marc->append_fields($new_901);
2360 } elsif ($schema eq 'authority') {
2361 my $new_901 = MARC::Field->new("901", " ", " ",
2362 "c" => $_TD->{new}{id},
2365 $marc->append_fields($new_901);
2366 } elsif ($schema eq 'serial') {
2367 my $new_901 = MARC::Field->new("901", " ", " ",
2368 "c" => $_TD->{new}{id},
2370 "o" => $_TD->{new}{owning_lib},
2373 if ($_TD->{new}{record}) {
2374 $new_901->add_subfields("r" => $_TD->{new}{record});
2377 $marc->append_fields($new_901);
2379 my $new_901 = MARC::Field->new("901", " ", " ",
2380 "c" => $_TD->{new}{id},
2383 $marc->append_fields($new_901);
2386 my $xml = $marc->as_xml_record();
2388 $xml =~ s/^<\?xml.+\?\s*>//go;
2389 $xml =~ s/>\s+</></go;
2390 $xml =~ s/\p{Cc}//go;
2392 # Embed a version of OpenILS::Application::AppUtils->entityize()
2393 # to avoid having to set PERL5LIB for PostgreSQL as well
2397 # Convert raw ampersands to entities
2398 $xml =~ s/&(?!\S+;)/&/gso;
2400 # Convert Unicode characters to entities
2401 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
2403 $xml =~ s/[\x00-\x1f]//go;
2404 $_TD->{new}{marc} = $xml;
2407 $func$ LANGUAGE PLPERLU;
2410 SELECT evergreen.upgrade_deps_block_check('0989', :eg_version); -- berick/miker/gmcharlt
2412 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
2414 merge_profile vandelay.merge_profile%ROWTYPE;
2415 dyn_profile vandelay.compile_profile%ROWTYPE;
2418 new_edit_date TIMESTAMPTZ;
2421 eg_marc_row authority.record_entry%ROWTYPE;
2429 SELECT * INTO eg_marc_row
2430 FROM authority.record_entry b
2431 JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
2434 SELECT q.marc INTO v_marc
2435 FROM vandelay.queued_record q
2436 JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
2439 eg_marc := eg_marc_row.marc;
2441 IF eg_marc IS NULL OR v_marc IS NULL THEN
2442 -- RAISE NOTICE 'no marc for vandelay or authority record';
2446 -- Extract the editor string before any modification to the vandelay
2449 (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
2451 -- If an editor value can be found, update the authority record
2452 -- editor and edit_date values.
2453 IF editor_string IS NOT NULL AND editor_string <> '' THEN
2455 -- Vandelay.pm sets the value to 'usrname' when needed.
2456 SELECT id INTO new_editor
2457 FROM actor.usr WHERE usrname = editor_string;
2459 IF new_editor IS NULL THEN
2460 SELECT usr INTO new_editor
2461 FROM actor.card WHERE barcode = editor_string;
2464 IF new_editor IS NOT NULL THEN
2465 new_edit_date := NOW();
2466 ELSE -- No valid editor, use current values
2467 new_editor = eg_marc_row.editor;
2468 new_edit_date = eg_marc_row.edit_date;
2471 new_editor = eg_marc_row.editor;
2472 new_edit_date = eg_marc_row.edit_date;
2475 dyn_profile := vandelay.compile_profile( v_marc );
2477 IF merge_profile_id IS NOT NULL THEN
2478 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
2480 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
2481 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
2482 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
2483 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
2487 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
2488 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
2492 IF dyn_profile.replace_rule = '' AND dyn_profile.preserve_rule = '' AND dyn_profile.add_rule = '' AND dyn_profile.strip_rule = '' THEN
2493 --Since we have nothing to do, just return a NOOP "we did it"
2495 ELSIF dyn_profile.replace_rule <> '' THEN
2496 source_marc = v_marc;
2497 target_marc = eg_marc;
2498 replace_rule = dyn_profile.replace_rule;
2500 source_marc = eg_marc;
2501 target_marc = v_marc;
2502 replace_rule = dyn_profile.preserve_rule;
2505 UPDATE authority.record_entry
2506 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule ),
2507 editor = new_editor,
2508 edit_date = new_edit_date
2512 -- Import/merge failed. Nothing left to do.
2516 -- Authority record successfully merged / imported.
2518 -- Update the vandelay record to show the successful import.
2519 UPDATE vandelay.queued_authority_record
2520 SET imported_as = eg_id,
2522 WHERE id = import_id;
2527 $$ LANGUAGE PLPGSQL;
2532 SELECT evergreen.upgrade_deps_block_check('0990', :eg_version);
2534 CREATE OR REPLACE FUNCTION rating.copy_count(badge_id INT)
2535 RETURNS TABLE (record INT, value NUMERIC) AS $f$
2537 badge rating.badge_with_orgs%ROWTYPE;
2540 SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
2542 PERFORM rating.precalc_bibs_by_copy(badge_id);
2544 DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
2545 SELECT id FROM precalc_filter_bib_list
2547 SELECT id FROM precalc_bibs_by_copy_list
2549 ANALYZE precalc_copy_filter_bib_list;
2552 SELECT f.id::INT AS bib,
2553 COUNT(f.copy)::NUMERIC
2554 FROM precalc_copy_filter_bib_list f
2555 JOIN asset.copy cp ON (f.copy = cp.id)
2556 JOIN asset.call_number cn ON (cn.id = cp.call_number)
2557 WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1;
2560 $f$ LANGUAGE PLPGSQL STRICT;
2562 INSERT INTO rating.popularity_parameter (id, name, func, require_percentile) VALUES (16, 'Copy Count', 'rating.copy_count', TRUE);
2566 SELECT evergreen.upgrade_deps_block_check('0991', :eg_version);
2568 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
2571 depth INT DEFAULT NULL,
2572 slimit HSTORE DEFAULT NULL,
2573 soffset HSTORE DEFAULT NULL,
2574 pref_lib INT DEFAULT NULL,
2575 includes TEXT[] DEFAULT NULL::TEXT[]
2576 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
2577 WITH RECURSIVE ou_depth AS (
2582 FROM actor.org_unit_type aout
2583 INNER JOIN actor.org_unit ou ON ou_type = aout.id
2587 ), descendant_depth AS (
2591 FROM actor.org_unit ou
2592 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2593 JOIN anscestor_depth ad ON (ad.id = ou.id),
2595 WHERE ad.depth = ou_depth.depth
2600 FROM actor.org_unit ou
2601 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2602 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
2603 ), anscestor_depth AS (
2607 FROM actor.org_unit ou
2608 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2614 FROM actor.org_unit ou
2615 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
2616 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
2618 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
2621 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
2622 SELECT acn.id, owning_lib.name, acn.label_sortkey,
2623 evergreen.rank_cp(acp),
2625 FROM asset.call_number acn
2626 JOIN asset.copy acp ON (acn.id = acp.call_number)
2627 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
2628 JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
2629 WHERE acn.record = ANY ($1)
2630 AND acn.deleted IS FALSE
2631 AND acp.deleted IS FALSE
2632 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
2635 FROM asset.opac_visible_copies
2636 WHERE copy_id = acp.id AND record = acn.record
2638 GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
2642 CASE WHEN aou.id = $2 THEN -20000 END,
2643 CASE WHEN aou.id = $6 THEN -10000 END,
2644 (SELECT distance - 5000
2645 FROM actor.org_unit_descendants_distance($6) as x
2646 WHERE x.id = aou.id AND $6 IN (
2647 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
2648 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
2651 evergreen.rank_cp(acp)
2654 GROUP BY ua.id, ua.name, ua.label_sortkey
2655 ORDER BY rank, ua.name, ua.label_sortkey
2656 LIMIT ($4 -> 'acn')::INT
2657 OFFSET ($5 -> 'acn')::INT;
2658 $$ LANGUAGE SQL STABLE ROWS 10;
2662 SELECT evergreen.upgrade_deps_block_check('0992', :eg_version);
2664 ALTER TABLE config.copy_status
2665 ADD COLUMN is_available BOOL NOT NULL DEFAULT FALSE;
2667 UPDATE config.copy_status SET is_available = TRUE
2668 WHERE id IN (0, 7); -- available, reshelving.
2670 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
2672 user_object actor.usr%ROWTYPE;
2673 standing_penalty config.standing_penalty%ROWTYPE;
2674 item_object asset.copy%ROWTYPE;
2675 item_status_object config.copy_status%ROWTYPE;
2676 item_location_object asset.copy_location%ROWTYPE;
2677 result action.circ_matrix_test_result;
2678 circ_test action.found_circ_matrix_matchpoint;
2679 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
2680 circ_limit_set config.circ_limit_set%ROWTYPE;
2681 hold_ratio action.hold_stats%ROWTYPE;
2684 context_org_list INT[];
2687 -- Assume success unless we hit a failure condition
2688 result.success := TRUE;
2690 -- Need user info to look up matchpoints
2691 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
2693 -- (Insta)Fail if we couldn't find the user
2694 IF user_object.id IS NULL THEN
2695 result.fail_part := 'no_user';
2696 result.success := FALSE;
2702 -- Need item info to look up matchpoints
2703 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
2705 -- (Insta)Fail if we couldn't find the item
2706 IF item_object.id IS NULL THEN
2707 result.fail_part := 'no_item';
2708 result.success := FALSE;
2714 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
2716 circ_matchpoint := circ_test.matchpoint;
2717 result.matchpoint := circ_matchpoint.id;
2718 result.circulate := circ_matchpoint.circulate;
2719 result.duration_rule := circ_matchpoint.duration_rule;
2720 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
2721 result.max_fine_rule := circ_matchpoint.max_fine_rule;
2722 result.hard_due_date := circ_matchpoint.hard_due_date;
2723 result.renewals := circ_matchpoint.renewals;
2724 result.grace_period := circ_matchpoint.grace_period;
2725 result.buildrows := circ_test.buildrows;
2727 -- (Insta)Fail if we couldn't find a matchpoint
2728 IF circ_test.success = false THEN
2729 result.fail_part := 'no_matchpoint';
2730 result.success := FALSE;
2736 -- All failures before this point are non-recoverable
2737 -- Below this point are possibly overridable failures
2739 -- Fail if the user is barred
2740 IF user_object.barred IS TRUE THEN
2741 result.fail_part := 'actor.usr.barred';
2742 result.success := FALSE;
2747 -- Fail if the item can't circulate
2748 IF item_object.circulate IS FALSE THEN
2749 result.fail_part := 'asset.copy.circulate';
2750 result.success := FALSE;
2755 -- Fail if the item isn't in a circulateable status on a non-renewal
2756 IF NOT renewal AND item_object.status <> 8 AND item_object.status NOT IN (
2757 (SELECT id FROM config.copy_status WHERE is_available) ) THEN
2758 result.fail_part := 'asset.copy.status';
2759 result.success := FALSE;
2762 -- Alternately, fail if the item isn't checked out on a renewal
2763 ELSIF renewal AND item_object.status <> 1 THEN
2764 result.fail_part := 'asset.copy.status';
2765 result.success := FALSE;
2770 -- Fail if the item can't circulate because of the shelving location
2771 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
2772 IF item_location_object.circulate IS FALSE THEN
2773 result.fail_part := 'asset.copy_location.circulate';
2774 result.success := FALSE;
2779 -- Use Circ OU for penalties and such
2780 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
2783 penalty_type = '%RENEW%';
2785 penalty_type = '%CIRC%';
2788 FOR standing_penalty IN
2789 SELECT DISTINCT csp.*
2790 FROM actor.usr_standing_penalty usp
2791 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
2792 WHERE usr = match_user
2793 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
2794 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
2795 AND csp.block_list LIKE penalty_type LOOP
2797 result.fail_part := standing_penalty.name;
2798 result.success := FALSE;
2803 -- Fail if the test is set to hard non-circulating
2804 IF circ_matchpoint.circulate IS FALSE THEN
2805 result.fail_part := 'config.circ_matrix_test.circulate';
2806 result.success := FALSE;
2811 -- Fail if the total copy-hold ratio is too low
2812 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
2813 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
2814 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
2815 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
2816 result.success := FALSE;
2822 -- Fail if the available copy-hold ratio is too low
2823 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
2824 IF hold_ratio.hold_count IS NULL THEN
2825 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
2827 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
2828 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
2829 result.success := FALSE;
2835 -- Fail if the user has too many items out by defined limit sets
2836 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
2837 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
2838 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
2839 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
2841 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
2842 SELECT INTO context_org_list ARRAY_AGG(aou.id)
2843 FROM actor.org_unit_full_path( circ_ou ) aou
2844 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
2845 WHERE aout.depth >= circ_limit_set.depth;
2846 IF circ_limit_set.global THEN
2847 WITH RECURSIVE descendant_depth AS (
2850 FROM actor.org_unit ou
2851 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
2855 FROM actor.org_unit ou
2856 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
2857 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
2859 SELECT INTO items_out COUNT(DISTINCT circ.id)
2860 FROM action.circulation circ
2861 JOIN asset.copy copy ON (copy.id = circ.target_copy)
2862 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
2863 WHERE circ.usr = match_user
2864 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
2865 AND circ.checkin_time IS NULL
2866 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
2867 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
2868 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
2869 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
2871 IF items_out >= circ_limit_set.items_out THEN
2872 result.fail_part := 'config.circ_matrix_circ_mod_test';
2873 result.success := FALSE;
2878 SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only;
2881 -- If we passed everything, return the successful matchpoint
2888 $func$ LANGUAGE plpgsql;
2892 SELECT evergreen.upgrade_deps_block_check('0993', :eg_version);
2894 ALTER TABLE config.usr_activity_type
2895 ALTER COLUMN transient SET DEFAULT TRUE;
2897 -- Utility function for removing all activity entries by activity type,
2898 -- except for the most recent entry per user. This is primarily useful
2899 -- when cleaning up rows prior to setting the transient flag on an
2900 -- activity type to true. It allows for immediate cleanup of data (e.g.
2901 -- for patron privacy) and lets admins control when the data is deleted,
2902 -- which could be useful for huge activity tables.
2904 CREATE OR REPLACE FUNCTION
2905 actor.purge_usr_activity_by_type(act_type INTEGER)
2910 FOR cur_usr IN SELECT DISTINCT(usr)
2911 FROM actor.usr_activity WHERE etype = act_type LOOP
2912 DELETE FROM actor.usr_activity WHERE id IN (
2914 FROM actor.usr_activity
2915 WHERE usr = cur_usr AND etype = act_type
2916 ORDER BY event_time DESC OFFSET 1
2920 END $$ LANGUAGE PLPGSQL;
2925 SELECT evergreen.upgrade_deps_block_check('0994', :eg_version);
2927 CREATE OR REPLACE FUNCTION authority.propagate_changes
2928 (aid BIGINT, bid BIGINT) RETURNS BIGINT AS $func$
2930 bib_rec biblio.record_entry%ROWTYPE;
2934 SELECT INTO bib_rec * FROM biblio.record_entry WHERE id = bid;
2936 new_marc := vandelay.merge_record_xml(
2937 bib_rec.marc, authority.generate_overlay_template(aid));
2939 IF new_marc = bib_rec.marc THEN
2940 -- Authority record change had no impact on this bib record.
2941 -- Nothing left to do.
2945 PERFORM 1 FROM config.global_flag
2946 WHERE name = 'ingest.disable_authority_auto_update_bib_meta'
2950 -- update the bib record editor and edit_date
2952 SELECT editor FROM authority.record_entry WHERE id = aid);
2953 bib_rec.edit_date = NOW();
2956 UPDATE biblio.record_entry SET
2958 editor = bib_rec.editor,
2959 edit_date = bib_rec.edit_date
2965 $func$ LANGUAGE PLPGSQL;
2969 -- Disabled by default
2970 INSERT INTO config.global_flag (name, enabled, label) VALUES (
2971 'ingest.disable_authority_auto_update_bib_meta', FALSE,
2973 'ingest.disable_authority_auto_update_bib_meta',
2974 'Authority Automation: Disable automatic authority updates ' ||
2975 'from modifying bib record editor and edit_date',
2982 CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
2984 ashs authority.simple_heading%ROWTYPE;
2985 mbe_row metabib.browse_entry%ROWTYPE;
2990 IF NEW.deleted IS TRUE THEN -- If this authority is deleted
2991 DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible
2992 DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records
2993 DELETE FROM authority.simple_heading WHERE record = NEW.id;
2994 -- Should remove matching $0 from controlled fields at the same time?
2996 -- XXX What do we about the actual linking subfields present in
2997 -- authority records that target this one when this happens?
2998 DELETE FROM authority.authority_linking
2999 WHERE source = NEW.id OR target = NEW.id;
3001 RETURN NEW; -- and we're done
3004 IF TG_OP = 'UPDATE' THEN -- re-ingest?
3005 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
3007 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
3011 -- Unless there's a setting stopping us, propagate these updates to any linked bib records when the heading changes
3012 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_auto_update' AND enabled;
3014 IF NOT FOUND AND NEW.heading <> OLD.heading THEN
3015 PERFORM authority.propagate_changes(NEW.id);
3018 DELETE FROM authority.simple_heading WHERE record = NEW.id;
3019 DELETE FROM authority.authority_linking WHERE source = NEW.id;
3022 INSERT INTO authority.authority_linking (source, target, field)
3023 SELECT source, target, field FROM authority.calculate_authority_linking(
3024 NEW.id, NEW.control_set, NEW.marc::XML
3027 FOR ashs IN SELECT * FROM authority.simple_heading_set(NEW.marc) LOOP
3029 INSERT INTO authority.simple_heading (record,atag,value,sort_value,thesaurus)
3030 VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value, ashs.thesaurus);
3031 ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS);
3033 SELECT INTO mbe_row * FROM metabib.browse_entry
3034 WHERE value = ashs.value AND sort_value = ashs.sort_value;
3037 mbe_id := mbe_row.id;
3039 INSERT INTO metabib.browse_entry
3040 ( value, sort_value ) VALUES
3041 ( ashs.value, ashs.sort_value );
3043 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
3046 INSERT INTO metabib.browse_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id);
3050 -- Flatten and insert the afr data
3051 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
3053 PERFORM authority.reingest_authority_full_rec(NEW.id);
3054 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled;
3056 PERFORM authority.reingest_authority_rec_descriptor(NEW.id);
3062 $func$ LANGUAGE PLPGSQL;
3066 SELECT evergreen.upgrade_deps_block_check('0995', :eg_version);
3068 INSERT INTO rating.badge (name, description, scope, weight, horizon_age, importance_age, importance_interval, importance_scale, recalc_interval, popularity_parameter, percentile)
3069 VALUES('Top Holds Over Last 5 Years', 'The top 97th percentile for holds requested over the past five years on all materials. More weight is given to holds requested over the last year, with importance decreasing for every year after that.', 1, 3, '5 years', '5 years', '1 year', 2, '1 day', 2, 97);
3072 SELECT evergreen.upgrade_deps_block_check('0996', :eg_version);
3074 INSERT INTO config.usr_setting_type (
3081 'circ.send_email_checkout_receipts',
3083 oils_i18n_gettext('circ.send_email_checkout_receipts', 'Email checkout receipts by default?', 'cust', 'label'),
3084 oils_i18n_gettext('circ.send_email_checkout_receipts', 'Email checkout receipts by default?', 'cust', 'description'),
3088 INSERT INTO action_trigger.hook (key, core_type, description, passive)
3090 'circ.checkout.batch_notify',
3093 'circ.checkout.batch_notify',
3094 'Notification of a group of circs',
3101 INSERT INTO action_trigger.hook (key, core_type, description, passive)
3103 'circ.checkout.batch_notify.session',
3106 'circ.checkout.batch_notify.session',
3107 'Notification of a group of circs at the end of a checkout session',
3114 INSERT INTO action_trigger.event_definition (
3128 'Email Checkout Receipt',
3129 'circ.checkout.batch_notify.session',
3133 'circ.send_email_checkout_receipts',
3136 [%- user = target.0.usr -%]
3137 To: [%- params.recipient_email || user.email %]
3138 From: [%- helpers.get_org_setting(target.0.circ_lib.id, 'org.bounced_emails') || params.sender_email || default_sender %]
3139 Subject: Checkout Receipt
3140 Auto-Submitted: auto-generated
3142 You checked out the following items:
3144 [% FOR circ IN target %]
3145 [%- copy_details = helpers.get_copy_bib_basics(circ.target_copy.id) -%]
3146 Title: [% copy_details.title %]
3147 Author: [% copy_details.author %]
3148 Call Number: [% circ.target_copy.call_number.label %]
3149 Barcode: [% circ.target_copy.barcode %]
3150 Due: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
3151 Library: [% circ.circ_lib.name %]
3156 INSERT INTO action_trigger.environment (
3160 currval('action_trigger.event_definition_id_seq'),
3161 'target_copy.call_number'
3163 currval('action_trigger.event_definition_id_seq'),
3164 'target_copy.location'
3166 currval('action_trigger.event_definition_id_seq'),
3169 currval('action_trigger.event_definition_id_seq'),
3175 SELECT evergreen.upgrade_deps_block_check('0997', :eg_version);
3177 INSERT INTO config.copy_status (id, name, holdable, opac_visible) VALUES (18,oils_i18n_gettext(18, 'Canceled Transit', 'ccs', 'name'), 't', 't');
3181 SELECT evergreen.upgrade_deps_block_check('0998', :eg_version);
3183 DROP VIEW IF EXISTS action.all_circulation;
3184 CREATE VIEW action.all_circulation AS
3185 SELECT aged_circulation.id, aged_circulation.usr_post_code,
3186 aged_circulation.usr_home_ou, aged_circulation.usr_profile,
3187 aged_circulation.usr_birth_year, aged_circulation.copy_call_number,
3188 aged_circulation.copy_location, aged_circulation.copy_owning_lib,
3189 aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record,
3190 aged_circulation.xact_start, aged_circulation.xact_finish,
3191 aged_circulation.target_copy, aged_circulation.circ_lib,
3192 aged_circulation.circ_staff, aged_circulation.checkin_staff,
3193 aged_circulation.checkin_lib, aged_circulation.renewal_remaining,
3194 aged_circulation.grace_period, aged_circulation.due_date,
3195 aged_circulation.stop_fines_time, aged_circulation.checkin_time,
3196 aged_circulation.create_time, aged_circulation.duration,
3197 aged_circulation.fine_interval, aged_circulation.recurring_fine,
3198 aged_circulation.max_fine, aged_circulation.phone_renewal,
3199 aged_circulation.desk_renewal, aged_circulation.opac_renewal,
3200 aged_circulation.duration_rule,
3201 aged_circulation.recurring_fine_rule,
3202 aged_circulation.max_fine_rule, aged_circulation.stop_fines,
3203 aged_circulation.workstation, aged_circulation.checkin_workstation,
3204 aged_circulation.checkin_scan_time, aged_circulation.parent_circ,
3206 FROM action.aged_circulation
3208 SELECT DISTINCT circ.id,
3209 COALESCE(a.post_code, b.post_code) AS usr_post_code,
3210 p.home_ou AS usr_home_ou, p.profile AS usr_profile,
3211 date_part('year'::text, p.dob)::integer AS usr_birth_year,
3212 cp.call_number AS copy_call_number, circ.copy_location,
3213 cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
3214 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish,
3215 circ.target_copy, circ.circ_lib, circ.circ_staff,
3216 circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining,
3217 circ.grace_period, circ.due_date, circ.stop_fines_time,
3218 circ.checkin_time, circ.create_time, circ.duration,
3219 circ.fine_interval, circ.recurring_fine, circ.max_fine,
3220 circ.phone_renewal, circ.desk_renewal, circ.opac_renewal,
3221 circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule,
3222 circ.stop_fines, circ.workstation, circ.checkin_workstation,
3223 circ.checkin_scan_time, circ.parent_circ, circ.usr
3224 FROM action.circulation circ
3225 JOIN asset.copy cp ON circ.target_copy = cp.id
3226 JOIN asset.call_number cn ON cp.call_number = cn.id
3227 JOIN actor.usr p ON circ.usr = p.id
3228 LEFT JOIN actor.usr_address a ON p.mailing_address = a.id
3229 LEFT JOIN actor.usr_address b ON p.billing_address = b.id;
3232 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER)
3233 RETURNS SETOF action.all_circulation AS $$
3235 tmp_circ action.all_circulation%ROWTYPE;
3236 circ_0 action.all_circulation%ROWTYPE;
3239 SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id;
3241 IF tmp_circ IS NULL THEN
3242 RETURN NEXT tmp_circ;
3246 -- find the front of the chain
3248 SELECT INTO tmp_circ * FROM action.all_circulation
3249 WHERE id = tmp_circ.parent_circ;
3250 IF tmp_circ IS NULL THEN
3256 -- now send the circs to the caller, oldest to newest
3259 IF tmp_circ IS NULL THEN
3262 RETURN NEXT tmp_circ;
3263 SELECT INTO tmp_circ * FROM action.all_circulation
3264 WHERE parent_circ = tmp_circ.id;
3268 $$ LANGUAGE 'plpgsql';
3270 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain
3271 (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
3275 -- first circ in the chain
3276 circ_0 action.all_circulation%ROWTYPE;
3278 -- last circ in the chain
3279 circ_n action.all_circulation%ROWTYPE;
3281 -- circ chain under construction
3282 chain action.circ_chain_summary;
3283 tmp_circ action.all_circulation%ROWTYPE;
3287 chain.num_circs := 0;
3288 FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
3290 IF chain.num_circs = 0 THEN
3294 chain.num_circs := chain.num_circs + 1;
3298 chain.start_time := circ_0.xact_start;
3299 chain.last_stop_fines := circ_n.stop_fines;
3300 chain.last_stop_fines_time := circ_n.stop_fines_time;
3301 chain.last_checkin_time := circ_n.checkin_time;
3302 chain.last_checkin_scan_time := circ_n.checkin_scan_time;
3303 SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
3304 SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
3306 IF chain.num_circs > 1 THEN
3307 chain.last_renewal_time := circ_n.xact_start;
3308 SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
3314 $$ LANGUAGE 'plpgsql';
3319 SELECT evergreen.upgrade_deps_block_check('0999', :eg_version);
3321 CREATE TABLE staging.setting_stage (
3322 row_id BIGSERIAL PRIMARY KEY,
3323 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
3324 usrname TEXT NOT NULL,
3325 setting TEXT NOT NULL,
3326 value TEXT NOT NULL,
3327 complete BOOL DEFAULT FALSE
3330 -- Add Spanish to config.i18n_locale table
3333 SELECT evergreen.upgrade_deps_block_check('1000', :eg_version);
3335 INSERT INTO config.i18n_locale (code,marc_code,name,description)
3336 SELECT 'es-ES', 'spa', oils_i18n_gettext('es-ES', 'Spanish', 'i18n_l', 'name'),
3337 oils_i18n_gettext('es-ES', 'Spanish', 'i18n_l', 'description')
3338 WHERE NOT EXISTS (SELECT 1 FROM config.i18n_locale WHERE code = 'es-ES');
3344 \qecho Now running an update to set the 901$s for bibliographic
3345 \qecho records that have a source set. This may take a while.
3347 \qecho The update can be cancelled now and run later
3348 \qecho using the following SQL statement:
3350 \qecho UPDATE biblio.record_entry SET id = id WHERE source IS NOT NULL;
3352 UPDATE biblio.record_entry SET id = id WHERE source IS NOT NULL;