2 * Copyright (C) 2007-2010 Equinox Software, Inc.
3 * Mike Rylander <miker@esilibrary.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS search CASCADE;
24 CREATE OR REPLACE FUNCTION evergreen.pg_statistics (tab TEXT, col TEXT) RETURNS TABLE(element TEXT, frequency INT) AS $$
26 -- This query will die on PG < 9.2, but the function can be created. We just won't use it where we can't.
30 FROM (SELECT ROW_NUMBER() OVER (),
32 FROM (SELECT UNNEST(most_common_elem_freqs) AS f
38 JOIN (SELECT ROW_NUMBER() OVER (),
40 FROM (SELECT UNNEST(most_common_elems::text::text[]) AS e
45 ) AS elems USING (row_number);
49 CREATE OR REPLACE FUNCTION evergreen.query_int_wrapper (INT[],TEXT) RETURNS BOOL AS $$
51 RETURN $1 @@ $2::query_int;
53 $$ LANGUAGE PLPGSQL STABLE;
55 CREATE TABLE search.relevance_adjustment (
56 id SERIAL PRIMARY KEY,
57 active BOOL NOT NULL DEFAULT TRUE,
58 field INT NOT NULL REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED,
59 bump_type TEXT NOT NULL CHECK (bump_type IN ('word_order','first_word','full_match')),
60 multiplier NUMERIC NOT NULL DEFAULT 1.0
62 CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( field, bump_type );
64 CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT, badges TEXT, popularity NUMERIC );
65 CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT );
67 CREATE OR REPLACE FUNCTION search.query_parser_fts (
73 param_locations INT[],
80 param_pref_ou INT DEFAULT NULL
81 ) RETURNS SETOF search.search_result AS $func$
84 current_res search.search_result%ROWTYPE;
85 search_org_list INT[];
95 core_cursor REFCURSOR;
100 deleted_count INT := 0;
101 visible_count INT := 0;
102 excluded_count INT := 0;
107 check_limit := COALESCE( param_check, 1000 );
108 core_limit := COALESCE( param_limit, 25000 );
109 core_offset := COALESCE( param_offset, 0 );
111 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
113 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
115 IF param_search_ou > 0 THEN
116 IF param_depth IS NOT NULL THEN
117 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
119 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
123 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
125 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
128 ELSIF param_search_ou < 0 THEN
129 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
131 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
134 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
136 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
139 luri_org_list := luri_org_list || tmp_int_list;
142 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
144 ELSIF param_search_ou = 0 THEN
145 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
148 IF param_pref_ou IS NOT NULL THEN
150 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
152 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
155 luri_org_list := luri_org_list || tmp_int_list;
158 OPEN core_cursor FOR EXECUTE param_query;
162 FETCH core_cursor INTO core_result;
164 EXIT WHEN total_count >= core_limit;
166 total_count := total_count + 1;
168 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
170 check_count := check_count + 1;
172 IF NOT deleted_search THEN
174 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
176 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
177 deleted_count := deleted_count + 1;
182 FROM biblio.record_entry b
183 JOIN config.bib_source s ON (b.source = s.id)
185 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
188 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
189 visible_count := visible_count + 1;
191 current_res.id = core_result.id;
192 current_res.rel = core_result.rel;
193 current_res.badges = core_result.badges;
194 current_res.popularity = core_result.popularity;
198 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
202 current_res.record = core_result.records[1];
204 current_res.record = NULL;
207 RETURN NEXT current_res;
213 FROM asset.call_number cn
214 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
215 JOIN asset.uri uri ON (map.uri = uri.id)
217 AND cn.label = '##URI##'
219 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
220 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
221 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
225 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
226 visible_count := visible_count + 1;
228 current_res.id = core_result.id;
229 current_res.rel = core_result.rel;
230 current_res.badges = core_result.badges;
231 current_res.popularity = core_result.popularity;
235 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
239 current_res.record = core_result.records[1];
241 current_res.record = NULL;
244 RETURN NEXT current_res;
249 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
252 FROM asset.call_number cn
253 JOIN asset.copy cp ON (cp.call_number = cn.id)
256 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
257 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
258 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
263 FROM biblio.peer_bib_copy_map pr
264 JOIN asset.copy cp ON (cp.id = pr.target_copy)
266 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
267 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
268 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
272 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
273 excluded_count := excluded_count + 1;
280 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
283 FROM asset.call_number cn
284 JOIN asset.copy cp ON (cp.call_number = cn.id)
287 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
288 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
289 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
294 FROM biblio.peer_bib_copy_map pr
295 JOIN asset.copy cp ON (cp.id = pr.target_copy)
297 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
298 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
299 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
303 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
304 excluded_count := excluded_count + 1;
311 IF staff IS NULL OR NOT staff THEN
314 FROM asset.opac_visible_copies
315 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
316 AND record IN ( SELECT * FROM unnest( core_result.records ) )
321 FROM biblio.peer_bib_copy_map pr
322 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
323 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
324 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
329 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
330 excluded_count := excluded_count + 1;
338 FROM asset.call_number cn
339 JOIN asset.copy cp ON (cp.call_number = cn.id)
342 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
343 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
349 FROM biblio.peer_bib_copy_map pr
350 JOIN asset.copy cp ON (cp.id = pr.target_copy)
352 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
353 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
359 FROM asset.call_number cn
360 JOIN asset.copy cp ON (cp.call_number = cn.id)
361 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
366 -- Recheck Located URI visibility in the case of no "foreign" copies
368 FROM asset.call_number cn
369 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
370 JOIN asset.uri uri ON (map.uri = uri.id)
372 AND cn.label = '##URI##'
374 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
375 AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
379 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
380 excluded_count := excluded_count + 1;
384 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
385 excluded_count := excluded_count + 1;
396 visible_count := visible_count + 1;
398 current_res.id = core_result.id;
399 current_res.rel = core_result.rel;
400 current_res.badges = core_result.badges;
401 current_res.popularity = core_result.popularity;
405 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
409 current_res.record = core_result.records[1];
411 current_res.record = NULL;
414 RETURN NEXT current_res;
416 IF visible_count % 1000 = 0 THEN
417 -- RAISE NOTICE ' % visible so far ... ', visible_count;
422 current_res.id = NULL;
423 current_res.rel = NULL;
424 current_res.record = NULL;
425 current_res.badges = NULL;
426 current_res.popularity = NULL;
427 current_res.total = total_count;
428 current_res.checked = check_count;
429 current_res.deleted = deleted_count;
430 current_res.visible = visible_count;
431 current_res.excluded = excluded_count;
435 RETURN NEXT current_res;
438 $func$ LANGUAGE PLPGSQL;
440 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
442 SELECT id, value, count
444 SELECT mfae.field AS id,
446 COUNT(DISTINCT mfae.source),
448 PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
450 FROM metabib.facet_entry mfae
451 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
452 WHERE mfae.source = ANY ($2)
454 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
459 (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
465 CREATE OR REPLACE FUNCTION search.facets_for_metarecord_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
466 SELECT id, value, count FROM (
467 SELECT mfae.field AS id,
469 COUNT(DISTINCT mmrsm.metarecord),
471 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.metarecord) DESC
473 FROM metabib.facet_entry mfae
474 JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
475 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
476 WHERE mmrsm.metarecord IN (SELECT * FROM unnest($2))
478 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
481 WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
484 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
487 WHEN 'luri_org' THEN 0 -- "b" attr
488 WHEN 'bib_source' THEN 1 -- "b" attr
490 WHEN 'copy_flags' THEN 0 -- "c" attr
491 WHEN 'owning_lib' THEN 1 -- "c" attr
492 WHEN 'circ_lib' THEN 2 -- "c" attr
493 WHEN 'status' THEN 3 -- "c" attr
494 WHEN 'location' THEN 4 -- "c" attr
495 WHEN 'location_group' THEN 5 -- "c" attr
499 /* copy_flags bit positions, LSB-first:
501 0: asset.copy.opac_visible
504 When adding flags, you must update asset.all_visible_flags()
506 Because bib and copy values are stored separately, we can reuse
507 shifts, saving us some space. We could probably take back a bit
508 too, but I'm not sure its worth squeezing that last one out. We'd
509 be left with just 2 slots for copy attrs, rather than 10.
512 $f$ LANGUAGE SQL IMMUTABLE;
514 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
515 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
516 $f$ LANGUAGE SQL IMMUTABLE;
518 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
519 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
520 $f$ LANGUAGE SQL IMMUTABLE;
522 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
524 copy_row asset.copy%ROWTYPE;
525 lgroup_map asset.copy_location_group_map%ROWTYPE;
526 attr_set INT[] := '{}'::INT[];
528 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
530 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
531 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
532 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
533 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
537 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
539 FROM asset.call_number
540 WHERE id = copy_row.call_number;
542 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
543 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
548 $f$ LANGUAGE PLPGSQL;
550 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT, new_source INT DEFAULT NULL, force_source BOOL DEFAULT FALSE ) RETURNS INT[] AS $f$
552 bib_row biblio.record_entry%ROWTYPE;
553 cn_row asset.call_number%ROWTYPE;
554 attr_set INT[] := '{}'::INT[];
556 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
559 IF new_source IS NOT NULL THEN
560 attr_set := attr_set || search.calculate_visibility_attribute(new_source, 'bib_source');
562 ELSIF bib_row.source IS NOT NULL THEN
563 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
568 FROM asset.call_number
569 WHERE record = bib_id
570 AND label = '##URI##'
573 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
578 $f$ LANGUAGE PLPGSQL;
580 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
582 ocn asset.call_number%ROWTYPE;
583 ncn asset.call_number%ROWTYPE;
588 SELECT enabled = FALSE INTO dobib FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc';
590 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
591 IF TG_OP = 'INSERT' THEN
592 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
595 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
599 ELSIF TG_OP = 'DELETE' THEN
600 DELETE FROM asset.copy_vis_attr_cache
601 WHERE record = OLD.peer_record AND target_copy = OLD.target_copy;
607 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
608 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
609 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
610 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
613 asset.calculate_copy_visibility_attribute_set(NEW.id)
615 ELSIF TG_TABLE_NAME = 'record_entry' THEN
616 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
617 ELSIF TG_TABLE_NAME = 'call_number' AND NEW.label = '##URI##' AND dobib THEN -- New located URI
618 UPDATE biblio.record_entry
619 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
620 WHERE id = NEW.record;
627 -- handle items first, since with circulation activity
628 -- their statuses change frequently
629 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
631 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
632 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
636 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
638 IF OLD.deleted <> NEW.deleted THEN
640 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
642 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
645 asset.calculate_copy_visibility_attribute_set(NEW.id)
650 ELSIF OLD.location <> NEW.location OR
651 OLD.status <> NEW.status OR
652 OLD.opac_visible <> NEW.opac_visible OR
653 OLD.circ_lib <> NEW.circ_lib OR
654 OLD.call_number <> NEW.call_number
656 IF OLD.call_number <> NEW.call_number THEN -- Special check since it's more expensive than the next branch
657 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
659 IF ncn.record <> ocn.record THEN
660 -- We have to use a record-specific WHERE clause
661 -- to avoid modifying the entries for peer-bib copies.
662 UPDATE asset.copy_vis_attr_cache
663 SET target_copy = NEW.id,
665 WHERE target_copy = OLD.id
666 AND record = ocn.record;
670 -- Any of these could change visibility, but
671 -- we'll save some queries and not try to calculate
672 -- the change directly. We want to update peer-bib
673 -- entries in this case, unlike above.
674 UPDATE asset.copy_vis_attr_cache
675 SET target_copy = NEW.id,
676 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
677 WHERE target_copy = OLD.id;
681 ELSIF TG_TABLE_NAME = 'call_number' THEN
683 IF TG_OP = 'DELETE' AND OLD.label = '##URI##' AND dobib THEN -- really deleted located URI, if the delete protection rule is disabled...
684 UPDATE biblio.record_entry
685 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
686 WHERE id = OLD.record;
690 IF OLD.label = '##URI##' AND dobib THEN -- Located URI
691 IF OLD.deleted <> NEW.deleted OR OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
692 UPDATE biblio.record_entry
693 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
694 WHERE id = NEW.record;
696 IF OLD.record <> NEW.record THEN -- maybe on merge?
697 UPDATE biblio.record_entry
698 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
699 WHERE id = OLD.record;
703 ELSIF OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
704 UPDATE asset.copy_vis_attr_cache
705 SET record = NEW.record,
706 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
707 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
708 AND record = OLD.record;
712 ELSIF TG_TABLE_NAME = 'record_entry' AND OLD.source IS DISTINCT FROM NEW.source THEN -- Only handles ON UPDATE, INSERT above
713 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
718 $func$ LANGUAGE PLPGSQL;
720 CREATE TRIGGER z_opac_vis_mat_view_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
721 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR DELETE ON biblio.peer_bib_copy_map FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
722 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE OR DELETE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
723 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
724 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
725 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
726 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
728 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
729 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
730 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
731 $f$ LANGUAGE SQL STABLE;
733 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
734 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
737 $f$ LANGUAGE SQL STABLE;
739 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
740 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
742 WHERE NOT opac_visible;
743 $f$ LANGUAGE SQL STABLE;
745 -- Bib-oriented defaults for search
746 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
747 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
748 FROM config.bib_source
750 $f$ LANGUAGE SQL IMMUTABLE;
752 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
753 SELECT * FROM asset.invisible_orgs('luri_org');
754 $f$ LANGUAGE SQL STABLE;
756 -- Copy-oriented defaults for search
757 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
758 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
759 FROM asset.copy_location_group
760 WHERE NOT opac_visible;
761 $f$ LANGUAGE SQL STABLE;
763 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
764 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
765 FROM asset.copy_location
766 WHERE NOT opac_visible;
767 $f$ LANGUAGE SQL STABLE;
769 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
770 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
771 FROM config.copy_status
772 WHERE NOT opac_visible;
773 $f$ LANGUAGE SQL STABLE;
775 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
776 SELECT * FROM asset.invisible_orgs('owning_lib');
777 $f$ LANGUAGE SQL STABLE;
779 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
780 SELECT * FROM asset.invisible_orgs('circ_lib');
781 $f$ LANGUAGE SQL STABLE;
783 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
785 copy_flags TEXT; -- "c" attr
787 owning_lib TEXT; -- "c" attr
788 circ_lib TEXT; -- "c" attr
789 status TEXT; -- "c" attr
790 location TEXT; -- "c" attr
791 location_group TEXT; -- "c" attr
793 luri_org TEXT; -- "b" attr
794 bib_sources TEXT; -- "b" attr
796 copy_flags := asset.all_visible_flags(); -- Will always have at least one
798 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
800 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
801 status := NULLIF(asset.status_default(),'!()');
802 location := NULLIF(asset.location_default(),'!()');
803 location_group := NULLIF(asset.location_group_default(),'!()');
805 luri_org := NULLIF(asset.luri_org_default(),'!()');
806 bib_sources := NULLIF(asset.bib_source_default(),'()');
809 '('||ARRAY_TO_STRING(
810 ARRAY[luri_org,bib_sources],
813 '('||ARRAY_TO_STRING(
814 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
818 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
820 CREATE OR REPLACE FUNCTION metabib.suggest_browse_entries(raw_query_text text, search_class text, headline_opts text, visibility_org integer, query_limit integer, normalization integer)
821 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
824 prepared_query_texts TEXT[];
827 opac_visibility_join TEXT;
828 search_class_join TEXT;
831 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
833 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
834 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
836 visibility_org := NULLIF(visibility_org,-1);
837 IF visibility_org IS NOT NULL THEN
838 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
840 opac_visibility_join := '';
842 opac_visibility_join := '
843 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
844 JOIN vm ON (acvac.vis_attr_vector @@
845 (vm.c_attrs || $$&$$ ||
846 search.calculate_visibility_attribute_test(
848 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
855 opac_visibility_join := '';
858 -- The following determines whether we only provide suggestsons matching
859 -- the user's selected search_class, or whether we show other suggestions
860 -- too. The reason for MIN() is that for search_classes like
861 -- 'title|proper|uniform' you would otherwise get multiple rows. The
862 -- implication is that if title as a class doesn't have restrict,
863 -- nor does the proper field, but the uniform field does, you're going
864 -- to get 'false' for your overall evaluation of 'should we restrict?'
865 -- To invert that, change from MIN() to MAX().
869 MIN(cmc.restrict::INT) AS restrict_class,
870 MIN(cmf.restrict::INT) AS restrict_field
871 FROM metabib.search_class_to_registered_components(search_class)
872 AS _registered (field_class TEXT, field INT)
874 config.metabib_class cmc ON (cmc.name = _registered.field_class)
876 config.metabib_field cmf ON (cmf.id = _registered.field);
878 -- evaluate 'should we restrict?'
879 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
880 search_class_join := '
882 metabib.search_class_to_registered_components($2)
883 AS _registered (field_class TEXT, field INT) ON (
884 (_registered.field IS NULL AND
885 _registered.field_class = cmf.field_class) OR
886 (_registered.field = cmf.id)
890 search_class_join := '
892 metabib.search_class_to_registered_components($2)
893 AS _registered (field_class TEXT, field INT) ON (
894 _registered.field_class = cmc.name
899 RETURN QUERY EXECUTE '
900 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
901 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
910 TS_HEADLINE(value, $7, $3)
911 FROM (SELECT DISTINCT
914 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
915 _registered.field = cmf.id AS restrict,
917 TS_RANK_CD(mbe.index_vector, $1, $6),
920 FROM metabib.browse_entry_def_map mbedm
921 JOIN mbe ON (mbe.id = mbedm.entry)
922 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
923 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
924 ' || search_class_join || '
925 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
927 ' || opac_visibility_join || '
928 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
930 ' -- sic, repeat the order by clause in the outer select too
932 query, search_class, headline_opts,
933 visibility_org, query_limit, normalization, plain_query
937 -- buoyant AND chosen class = match class
938 -- chosen field = match field
945 $f$ LANGUAGE plpgsql ROWS 10;
947 CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer)
948 RETURNS SETOF metabib.flat_browse_entry_appearance
957 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
958 results_skipped INT := 0;
959 row_counter INT := 0;
964 all_records BIGINT[];
965 all_brecords BIGINT[];
966 all_arecords BIGINT[];
967 superpage_of_records BIGINT[];
972 unauthorized_entry RECORD;
974 IF count_up_from_zero THEN
981 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
984 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
985 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
987 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
989 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
990 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
992 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
994 b_tests := b_tests || search.calculate_visibility_attribute_test(
996 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
999 b_tests := b_tests || search.calculate_visibility_attribute_test(
1001 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
1005 IF context_locations THEN
1006 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
1007 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
1010 OPEN curs NO SCROLL FOR EXECUTE query;
1013 FETCH curs INTO rec;
1015 IF result_row.pivot_point IS NOT NULL THEN
1016 RETURN NEXT result_row;
1022 SELECT INTO unauthorized_entry *
1023 FROM metabib.browse_entry_simple_heading_map mbeshm
1024 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1025 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
1026 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1027 WHERE mbeshm.entry = rec.id
1028 AND ahf.heading_purpose = 'variant';
1030 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1031 IF (unauthorized_entry.record IS NOT NULL) THEN
1032 --unauthorized term belongs to an auth linked to a bib?
1033 SELECT INTO all_arecords, result_row.sees, afields
1034 ARRAY_AGG(DISTINCT abl.bib),
1035 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
1036 ARRAY_AGG(DISTINCT map.metabib_field)
1037 FROM authority.bib_linking abl
1038 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1039 map.authority_field = unauthorized_entry.atag
1040 AND map.metabib_field = ANY(fields)
1042 WHERE abl.authority = unauthorized_entry.record;
1044 --do usual procedure
1045 SELECT INTO all_arecords, result_row.sees, afields
1046 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1047 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1048 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1050 FROM metabib.browse_entry_simple_heading_map mbeshm
1051 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1052 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1053 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1054 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1055 ash.atag = map.authority_field
1056 AND map.metabib_field = ANY(fields)
1058 JOIN authority.control_set_authority_field acsaf ON (
1059 map.authority_field = acsaf.id
1061 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1062 WHERE mbeshm.entry = rec.id
1063 AND ahf.heading_purpose = 'variant';
1067 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
1068 SELECT INTO all_brecords, result_row.authorities, bfields
1069 ARRAY_AGG(DISTINCT source),
1070 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
1071 ARRAY_AGG(DISTINCT def)
1072 FROM metabib.browse_entry_def_map
1073 WHERE entry = rec.id
1074 AND def = ANY(fields);
1076 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
1078 result_row.sources := 0;
1079 result_row.asources := 0;
1081 -- Bib-linked vis checking
1082 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
1084 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
1085 FROM biblio.record_entry b
1086 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1087 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
1089 acvac.vis_attr_vector @@ c_tests::query_int
1090 OR b.vis_attr_vector @@ b_tests::query_int
1093 result_row.accurate := TRUE;
1097 -- Authority-linked vis checking
1098 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
1100 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
1101 FROM biblio.record_entry b
1102 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1103 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
1105 acvac.vis_attr_vector @@ c_tests::query_int
1106 OR b.vis_attr_vector @@ b_tests::query_int
1109 result_row.aaccurate := TRUE;
1113 IF result_row.sources > 0 OR result_row.asources > 0 THEN
1115 -- The function that calls this function needs row_number in order
1116 -- to correctly order results from two different runs of this
1118 result_row.row_number := row_number;
1120 -- Now, if row_counter is still less than limit, return a row. If
1121 -- not, but it is less than next_pivot_pos, continue on without
1122 -- returning actual result rows until we find
1123 -- that next pivot, and return it.
1125 IF row_counter < result_limit THEN
1126 result_row.browse_entry := rec.id;
1127 result_row.value := rec.value;
1129 RETURN NEXT result_row;
1131 result_row.browse_entry := NULL;
1132 result_row.authorities := NULL;
1133 result_row.fields := NULL;
1134 result_row.value := NULL;
1135 result_row.sources := NULL;
1136 result_row.sees := NULL;
1137 result_row.accurate := NULL;
1138 result_row.aaccurate := NULL;
1139 result_row.pivot_point := rec.id;
1141 IF row_counter >= next_pivot_pos THEN
1142 RETURN NEXT result_row;
1147 IF count_up_from_zero THEN
1148 row_number := row_number + 1;
1150 row_number := row_number - 1;
1153 -- row_counter is different from row_number.
1154 -- It simply counts up from zero so that we know when
1155 -- we've reached our limit.
1156 row_counter := row_counter + 1;
1160 $f$ LANGUAGE plpgsql ROWS 10;
1162 CREATE OR REPLACE FUNCTION metabib.browse(search_field integer[], browse_term text, context_org integer DEFAULT NULL::integer, context_loc_group integer DEFAULT NULL::integer, staff boolean DEFAULT false, pivot_id bigint DEFAULT NULL::bigint, result_limit integer DEFAULT 10)
1163 RETURNS SETOF metabib.flat_browse_entry_appearance
1169 pivot_sort_value TEXT;
1170 pivot_sort_fallback TEXT;
1171 context_locations INT[];
1172 browse_superpage_size INT;
1173 results_skipped INT := 0;
1177 forward_to_pivot INT;
1179 -- First, find the pivot if we were given a browse term but not a pivot.
1180 IF pivot_id IS NULL THEN
1181 pivot_id := metabib.browse_pivot(search_field, browse_term);
1184 SELECT INTO pivot_sort_value, pivot_sort_fallback
1185 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
1187 -- Bail if we couldn't find a pivot.
1188 IF pivot_sort_value IS NULL THEN
1192 -- Transform the context_loc_group argument (if any) (logc at the
1193 -- TPAC layer) into a form we'll be able to use.
1194 IF context_loc_group IS NOT NULL THEN
1195 SELECT INTO context_locations ARRAY_AGG(location)
1196 FROM asset.copy_location_group_map
1197 WHERE lgroup = context_loc_group;
1200 -- Get the configured size of browse superpages.
1201 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
1202 FROM config.global_flag
1203 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
1205 -- First we're going to search backward from the pivot, then we're going
1206 -- to search forward. In each direction, we need two limits. At the
1207 -- lesser of the two limits, we delineate the edge of the result set
1208 -- we're going to return. At the greater of the two limits, we find the
1209 -- pivot value that would represent an offset from the current pivot
1210 -- at a distance of one "page" in either direction, where a "page" is a
1211 -- result set of the size specified in the "result_limit" argument.
1213 -- The two limits in each direction make four derived values in total,
1214 -- and we calculate them now.
1215 back_limit := CEIL(result_limit::FLOAT / 2);
1216 back_to_pivot := result_limit;
1217 forward_limit := result_limit / 2;
1218 forward_to_pivot := result_limit - 1;
1220 -- This is the meat of the SQL query that finds browse entries. We'll
1221 -- pass this to a function which uses it with a cursor, so that individual
1222 -- rows may be fetched in a loop until some condition is satisfied, without
1223 -- waiting for a result set of fixed size to be collected all at once.
1228 FROM metabib.browse_entry mbe
1230 EXISTS ( -- are there any bibs using this mbe via the requested fields?
1232 FROM metabib.browse_entry_def_map mbedm
1233 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
1234 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
1236 FROM metabib.browse_entry_simple_heading_map mbeshm
1237 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1238 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1239 ash.atag = map.authority_field
1240 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
1242 JOIN authority.control_set_authority_field acsaf ON (
1243 map.authority_field = acsaf.id
1245 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1246 WHERE mbeshm.entry = mbe.id
1247 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
1248 -- and authority that variant is coming from is linked to a bib
1251 FROM metabib.browse_entry_def_map mbedm2
1252 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
1258 -- This is the variant of the query for browsing backward.
1259 back_query := core_query ||
1260 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
1261 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
1263 -- This variant browses forward.
1264 forward_query := core_query ||
1265 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
1266 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
1268 -- We now call the function which applies a cursor to the provided
1269 -- queries, stopping at the appropriate limits and also giving us
1270 -- the next page's pivot.
1272 SELECT * FROM metabib.staged_browse(
1273 back_query, search_field, context_org, context_locations,
1274 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
1276 SELECT * FROM metabib.staged_browse(
1277 forward_query, search_field, context_org, context_locations,
1278 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
1279 ) ORDER BY row_number DESC;
1282 $f$ LANGUAGE plpgsql ROWS 10;
1284 CREATE OR REPLACE FUNCTION metabib.browse(
1287 context_org INT DEFAULT NULL,
1288 context_loc_group INT DEFAULT NULL,
1289 staff BOOL DEFAULT FALSE,
1290 pivot_id BIGINT DEFAULT NULL,
1291 result_limit INT DEFAULT 10
1292 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1294 RETURN QUERY SELECT * FROM metabib.browse(
1295 (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
1296 FROM config.metabib_field WHERE field_class = search_class),
1305 $p$ LANGUAGE PLPGSQL ROWS 10;