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 '!()'::TEXT; -- For now, as there's no way to cause a location group to hide all copies.
760 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
761 FROM asset.copy_location_group
762 WHERE NOT opac_visible;
764 $f$ LANGUAGE SQL IMMUTABLE;
766 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
767 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
768 FROM asset.copy_location
769 WHERE NOT opac_visible;
770 $f$ LANGUAGE SQL STABLE;
772 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
773 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
774 FROM config.copy_status
775 WHERE NOT opac_visible;
776 $f$ LANGUAGE SQL STABLE;
778 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
779 SELECT * FROM asset.invisible_orgs('owning_lib');
780 $f$ LANGUAGE SQL STABLE;
782 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
783 SELECT * FROM asset.invisible_orgs('circ_lib');
784 $f$ LANGUAGE SQL STABLE;
786 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
788 copy_flags TEXT; -- "c" attr
790 owning_lib TEXT; -- "c" attr
791 circ_lib TEXT; -- "c" attr
792 status TEXT; -- "c" attr
793 location TEXT; -- "c" attr
794 location_group TEXT; -- "c" attr
796 luri_org TEXT; -- "b" attr
797 bib_sources TEXT; -- "b" attr
799 copy_flags := asset.all_visible_flags(); -- Will always have at least one
801 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
803 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
804 status := NULLIF(asset.status_default(),'!()');
805 location := NULLIF(asset.location_default(),'!()');
806 location_group := NULLIF(asset.location_group_default(),'!()');
808 luri_org := NULLIF(asset.luri_org_default(),'!()');
809 bib_sources := NULLIF(asset.bib_source_default(),'()');
812 '('||ARRAY_TO_STRING(
813 ARRAY[luri_org,bib_sources],
816 '('||ARRAY_TO_STRING(
817 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
821 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
823 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)
824 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
827 prepared_query_texts TEXT[];
830 opac_visibility_join TEXT;
831 search_class_join TEXT;
834 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
836 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
837 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
839 visibility_org := NULLIF(visibility_org,-1);
840 IF visibility_org IS NOT NULL THEN
841 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
843 opac_visibility_join := '';
845 opac_visibility_join := '
846 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
847 JOIN vm ON (acvac.vis_attr_vector @@
848 (vm.c_attrs || $$&$$ ||
849 search.calculate_visibility_attribute_test(
851 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
858 opac_visibility_join := '';
861 -- The following determines whether we only provide suggestsons matching
862 -- the user's selected search_class, or whether we show other suggestions
863 -- too. The reason for MIN() is that for search_classes like
864 -- 'title|proper|uniform' you would otherwise get multiple rows. The
865 -- implication is that if title as a class doesn't have restrict,
866 -- nor does the proper field, but the uniform field does, you're going
867 -- to get 'false' for your overall evaluation of 'should we restrict?'
868 -- To invert that, change from MIN() to MAX().
872 MIN(cmc.restrict::INT) AS restrict_class,
873 MIN(cmf.restrict::INT) AS restrict_field
874 FROM metabib.search_class_to_registered_components(search_class)
875 AS _registered (field_class TEXT, field INT)
877 config.metabib_class cmc ON (cmc.name = _registered.field_class)
879 config.metabib_field cmf ON (cmf.id = _registered.field);
881 -- evaluate 'should we restrict?'
882 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
883 search_class_join := '
885 metabib.search_class_to_registered_components($2)
886 AS _registered (field_class TEXT, field INT) ON (
887 (_registered.field IS NULL AND
888 _registered.field_class = cmf.field_class) OR
889 (_registered.field = cmf.id)
893 search_class_join := '
895 metabib.search_class_to_registered_components($2)
896 AS _registered (field_class TEXT, field INT) ON (
897 _registered.field_class = cmc.name
902 RETURN QUERY EXECUTE '
903 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
904 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
913 TS_HEADLINE(value, $7, $3)
914 FROM (SELECT DISTINCT
917 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
918 _registered.field = cmf.id AS restrict,
920 TS_RANK_CD(mbe.index_vector, $1, $6),
923 FROM metabib.browse_entry_def_map mbedm
924 JOIN mbe ON (mbe.id = mbedm.entry)
925 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
926 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
927 ' || search_class_join || '
928 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
930 ' || opac_visibility_join || '
931 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
933 ' -- sic, repeat the order by clause in the outer select too
935 query, search_class, headline_opts,
936 visibility_org, query_limit, normalization, plain_query
940 -- buoyant AND chosen class = match class
941 -- chosen field = match field
948 $f$ LANGUAGE plpgsql ROWS 10;
950 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)
951 RETURNS SETOF metabib.flat_browse_entry_appearance
960 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
961 results_skipped INT := 0;
962 row_counter INT := 0;
967 all_records BIGINT[];
968 all_brecords BIGINT[];
969 all_arecords BIGINT[];
970 superpage_of_records BIGINT[];
975 unauthorized_entry RECORD;
977 IF count_up_from_zero THEN
984 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
987 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
988 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
990 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
992 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
993 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
995 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
997 b_tests := b_tests || search.calculate_visibility_attribute_test(
999 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
1002 b_tests := b_tests || search.calculate_visibility_attribute_test(
1004 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
1008 IF context_locations THEN
1009 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
1010 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
1013 OPEN curs NO SCROLL FOR EXECUTE query;
1016 FETCH curs INTO rec;
1018 IF result_row.pivot_point IS NOT NULL THEN
1019 RETURN NEXT result_row;
1025 SELECT INTO unauthorized_entry *
1026 FROM metabib.browse_entry_simple_heading_map mbeshm
1027 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1028 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
1029 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1030 WHERE mbeshm.entry = rec.id
1031 AND ahf.heading_purpose = 'variant';
1033 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1034 IF (unauthorized_entry.record IS NOT NULL) THEN
1035 --unauthorized term belongs to an auth linked to a bib?
1036 SELECT INTO all_arecords, result_row.sees, afields
1037 ARRAY_AGG(DISTINCT abl.bib),
1038 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
1039 ARRAY_AGG(DISTINCT map.metabib_field)
1040 FROM authority.bib_linking abl
1041 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1042 map.authority_field = unauthorized_entry.atag
1043 AND map.metabib_field = ANY(fields)
1045 WHERE abl.authority = unauthorized_entry.record;
1047 --do usual procedure
1048 SELECT INTO all_arecords, result_row.sees, afields
1049 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1050 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1051 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1053 FROM metabib.browse_entry_simple_heading_map mbeshm
1054 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1055 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1056 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1057 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1058 ash.atag = map.authority_field
1059 AND map.metabib_field = ANY(fields)
1061 JOIN authority.control_set_authority_field acsaf ON (
1062 map.authority_field = acsaf.id
1064 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1065 WHERE mbeshm.entry = rec.id
1066 AND ahf.heading_purpose = 'variant';
1070 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
1071 SELECT INTO all_brecords, result_row.authorities, bfields
1072 ARRAY_AGG(DISTINCT source),
1073 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
1074 ARRAY_AGG(DISTINCT def)
1075 FROM metabib.browse_entry_def_map
1076 WHERE entry = rec.id
1077 AND def = ANY(fields);
1079 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
1081 result_row.sources := 0;
1082 result_row.asources := 0;
1084 -- Bib-linked vis checking
1085 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
1087 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
1088 FROM biblio.record_entry b
1089 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1090 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
1092 acvac.vis_attr_vector @@ c_tests::query_int
1093 OR b.vis_attr_vector @@ b_tests::query_int
1096 result_row.accurate := TRUE;
1100 -- Authority-linked vis checking
1101 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
1103 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
1104 FROM biblio.record_entry b
1105 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1106 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
1108 acvac.vis_attr_vector @@ c_tests::query_int
1109 OR b.vis_attr_vector @@ b_tests::query_int
1112 result_row.aaccurate := TRUE;
1116 IF result_row.sources > 0 OR result_row.asources > 0 THEN
1118 -- The function that calls this function needs row_number in order
1119 -- to correctly order results from two different runs of this
1121 result_row.row_number := row_number;
1123 -- Now, if row_counter is still less than limit, return a row. If
1124 -- not, but it is less than next_pivot_pos, continue on without
1125 -- returning actual result rows until we find
1126 -- that next pivot, and return it.
1128 IF row_counter < result_limit THEN
1129 result_row.browse_entry := rec.id;
1130 result_row.value := rec.value;
1132 RETURN NEXT result_row;
1134 result_row.browse_entry := NULL;
1135 result_row.authorities := NULL;
1136 result_row.fields := NULL;
1137 result_row.value := NULL;
1138 result_row.sources := NULL;
1139 result_row.sees := NULL;
1140 result_row.accurate := NULL;
1141 result_row.aaccurate := NULL;
1142 result_row.pivot_point := rec.id;
1144 IF row_counter >= next_pivot_pos THEN
1145 RETURN NEXT result_row;
1150 IF count_up_from_zero THEN
1151 row_number := row_number + 1;
1153 row_number := row_number - 1;
1156 -- row_counter is different from row_number.
1157 -- It simply counts up from zero so that we know when
1158 -- we've reached our limit.
1159 row_counter := row_counter + 1;
1163 $f$ LANGUAGE plpgsql ROWS 10;
1165 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)
1166 RETURNS SETOF metabib.flat_browse_entry_appearance
1172 pivot_sort_value TEXT;
1173 pivot_sort_fallback TEXT;
1174 context_locations INT[];
1175 browse_superpage_size INT;
1176 results_skipped INT := 0;
1180 forward_to_pivot INT;
1182 -- First, find the pivot if we were given a browse term but not a pivot.
1183 IF pivot_id IS NULL THEN
1184 pivot_id := metabib.browse_pivot(search_field, browse_term);
1187 SELECT INTO pivot_sort_value, pivot_sort_fallback
1188 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
1190 -- Bail if we couldn't find a pivot.
1191 IF pivot_sort_value IS NULL THEN
1195 -- Transform the context_loc_group argument (if any) (logc at the
1196 -- TPAC layer) into a form we'll be able to use.
1197 IF context_loc_group IS NOT NULL THEN
1198 SELECT INTO context_locations ARRAY_AGG(location)
1199 FROM asset.copy_location_group_map
1200 WHERE lgroup = context_loc_group;
1203 -- Get the configured size of browse superpages.
1204 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
1205 FROM config.global_flag
1206 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
1208 -- First we're going to search backward from the pivot, then we're going
1209 -- to search forward. In each direction, we need two limits. At the
1210 -- lesser of the two limits, we delineate the edge of the result set
1211 -- we're going to return. At the greater of the two limits, we find the
1212 -- pivot value that would represent an offset from the current pivot
1213 -- at a distance of one "page" in either direction, where a "page" is a
1214 -- result set of the size specified in the "result_limit" argument.
1216 -- The two limits in each direction make four derived values in total,
1217 -- and we calculate them now.
1218 back_limit := CEIL(result_limit::FLOAT / 2);
1219 back_to_pivot := result_limit;
1220 forward_limit := result_limit / 2;
1221 forward_to_pivot := result_limit - 1;
1223 -- This is the meat of the SQL query that finds browse entries. We'll
1224 -- pass this to a function which uses it with a cursor, so that individual
1225 -- rows may be fetched in a loop until some condition is satisfied, without
1226 -- waiting for a result set of fixed size to be collected all at once.
1231 FROM metabib.browse_entry mbe
1233 EXISTS ( -- are there any bibs using this mbe via the requested fields?
1235 FROM metabib.browse_entry_def_map mbedm
1236 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
1237 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
1239 FROM metabib.browse_entry_simple_heading_map mbeshm
1240 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1241 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1242 ash.atag = map.authority_field
1243 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
1245 JOIN authority.control_set_authority_field acsaf ON (
1246 map.authority_field = acsaf.id
1248 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1249 WHERE mbeshm.entry = mbe.id
1250 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
1251 -- and authority that variant is coming from is linked to a bib
1254 FROM metabib.browse_entry_def_map mbedm2
1255 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
1261 -- This is the variant of the query for browsing backward.
1262 back_query := core_query ||
1263 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
1264 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
1266 -- This variant browses forward.
1267 forward_query := core_query ||
1268 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
1269 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
1271 -- We now call the function which applies a cursor to the provided
1272 -- queries, stopping at the appropriate limits and also giving us
1273 -- the next page's pivot.
1275 SELECT * FROM metabib.staged_browse(
1276 back_query, search_field, context_org, context_locations,
1277 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
1279 SELECT * FROM metabib.staged_browse(
1280 forward_query, search_field, context_org, context_locations,
1281 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
1282 ) ORDER BY row_number DESC;
1285 $f$ LANGUAGE plpgsql ROWS 10;
1287 CREATE OR REPLACE FUNCTION metabib.browse(
1290 context_org INT DEFAULT NULL,
1291 context_loc_group INT DEFAULT NULL,
1292 staff BOOL DEFAULT FALSE,
1293 pivot_id BIGINT DEFAULT NULL,
1294 result_limit INT DEFAULT 10
1295 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1297 RETURN QUERY SELECT * FROM metabib.browse(
1298 (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
1299 FROM config.metabib_field WHERE field_class = search_class),
1308 $p$ LANGUAGE PLPGSQL ROWS 10;