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;
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 ) RETURNS INT[] AS $f$
552 bib_row biblio.record_entry%ROWTYPE;
553 cn_row asset.call_number%ROWTYPE;
556 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
558 IF bib_row.source IS NOT NULL THEN
559 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
564 FROM asset.call_number cn
565 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
566 JOIN asset.uri u ON (u.id = m.uri)
567 WHERE cn.record = bib_id
568 AND cn.label = '##URI##'
571 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
576 $f$ LANGUAGE PLPGSQL;
578 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
580 ocn asset.call_number%ROWTYPE;
581 ncn asset.call_number%ROWTYPE;
585 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
586 IF TG_OP = 'INSERT' THEN
587 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
590 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
594 ELSIF TG_OP = 'DELETE' THEN
595 DELETE FROM asset.copy_vis_attr_cache
596 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
602 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
603 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
604 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
605 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
608 asset.calculate_copy_visibility_attribute_set(NEW.id)
610 ELSIF TG_TABLE_NAME = 'record_entry' THEN
611 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
617 -- handle items first, since with circulation activity
618 -- their statuses change frequently
619 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
621 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
622 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
626 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
628 IF OLD.deleted <> NEW.deleted THEN
630 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
632 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
635 asset.calculate_copy_visibility_attribute_set(NEW.id)
640 ELSIF OLD.call_number <> NEW.call_number THEN
641 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
643 IF ncn.record <> ocn.record THEN
644 UPDATE biblio.record_entry
645 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
646 WHERE id = ocn.record;
650 IF 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
655 -- any of these could change visibility, but
656 -- we'll save some queries and not try to calculate
657 -- the change directly
658 UPDATE asset.copy_vis_attr_cache
659 SET target_copy = NEW.id,
660 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
661 WHERE target_copy = OLD.id;
665 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
667 IF OLD.record <> NEW.record THEN
668 IF NEW.label = '##URI##' THEN
669 UPDATE biblio.record_entry
670 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
671 WHERE id = OLD.record;
673 UPDATE biblio.record_entry
674 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
675 WHERE id = NEW.record;
678 UPDATE asset.copy_vis_attr_cache
679 SET record = NEW.record,
680 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
681 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
682 AND record = OLD.record;
684 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
685 UPDATE asset.copy_vis_attr_cache
686 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
687 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
688 AND record = NEW.record;
690 IF NEW.label = '##URI##' THEN
691 UPDATE biblio.record_entry
692 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
693 WHERE id = OLD.record;
697 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
699 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
700 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
702 ELSIF OLD.source <> NEW.source THEN
703 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
710 $func$ LANGUAGE PLPGSQL;
712 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();
713 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();
714 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
715 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
716 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
717 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
718 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
720 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
721 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
722 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
723 $f$ LANGUAGE SQL STABLE;
725 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
726 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
729 $f$ LANGUAGE SQL STABLE;
731 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
732 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
734 WHERE NOT opac_visible;
735 $f$ LANGUAGE SQL STABLE;
737 -- Bib-oriented defaults for search
738 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
739 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
740 FROM config.bib_source
742 $f$ LANGUAGE SQL IMMUTABLE;
744 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
745 SELECT * FROM asset.invisible_orgs('luri_org');
746 $f$ LANGUAGE SQL STABLE;
748 -- Copy-oriented defaults for search
749 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
750 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
751 FROM asset.copy_location_group
752 WHERE NOT opac_visible;
753 $f$ LANGUAGE SQL STABLE;
755 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
756 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
757 FROM asset.copy_location
758 WHERE NOT opac_visible;
759 $f$ LANGUAGE SQL STABLE;
761 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
762 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
763 FROM config.copy_status
764 WHERE NOT opac_visible;
765 $f$ LANGUAGE SQL STABLE;
767 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
768 SELECT * FROM asset.invisible_orgs('owning_lib');
769 $f$ LANGUAGE SQL STABLE;
771 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
772 SELECT * FROM asset.invisible_orgs('circ_lib');
773 $f$ LANGUAGE SQL STABLE;
775 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
777 copy_flags TEXT; -- "c" attr
779 owning_lib TEXT; -- "c" attr
780 circ_lib TEXT; -- "c" attr
781 status TEXT; -- "c" attr
782 location TEXT; -- "c" attr
783 location_group TEXT; -- "c" attr
785 luri_org TEXT; -- "b" attr
786 bib_sources TEXT; -- "b" attr
788 copy_flags := asset.all_visible_flags(); -- Will always have at least one
790 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
792 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
793 status := NULLIF(asset.status_default(),'!()');
794 location := NULLIF(asset.location_default(),'!()');
795 location_group := NULLIF(asset.location_group_default(),'!()');
797 luri_org := NULLIF(asset.luri_org_default(),'!()');
798 bib_sources := NULLIF(asset.bib_source_default(),'()');
801 '('||ARRAY_TO_STRING(
802 ARRAY[luri_org,bib_sources],
805 '('||ARRAY_TO_STRING(
806 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
810 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
812 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)
813 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
816 prepared_query_texts TEXT[];
819 opac_visibility_join TEXT;
820 search_class_join TEXT;
823 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
825 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
826 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
828 visibility_org := NULLIF(visibility_org,-1);
829 IF visibility_org IS NOT NULL THEN
830 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
832 opac_visibility_join := '';
834 opac_visibility_join := '
835 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
836 JOIN vm ON (acvac.vis_attr_vector @@
837 (vm.c_attrs || $$&$$ ||
838 search.calculate_visibility_attribute_test(
840 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
847 opac_visibility_join := '';
850 -- The following determines whether we only provide suggestsons matching
851 -- the user's selected search_class, or whether we show other suggestions
852 -- too. The reason for MIN() is that for search_classes like
853 -- 'title|proper|uniform' you would otherwise get multiple rows. The
854 -- implication is that if title as a class doesn't have restrict,
855 -- nor does the proper field, but the uniform field does, you're going
856 -- to get 'false' for your overall evaluation of 'should we restrict?'
857 -- To invert that, change from MIN() to MAX().
861 MIN(cmc.restrict::INT) AS restrict_class,
862 MIN(cmf.restrict::INT) AS restrict_field
863 FROM metabib.search_class_to_registered_components(search_class)
864 AS _registered (field_class TEXT, field INT)
866 config.metabib_class cmc ON (cmc.name = _registered.field_class)
868 config.metabib_field cmf ON (cmf.id = _registered.field);
870 -- evaluate 'should we restrict?'
871 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
872 search_class_join := '
874 metabib.search_class_to_registered_components($2)
875 AS _registered (field_class TEXT, field INT) ON (
876 (_registered.field IS NULL AND
877 _registered.field_class = cmf.field_class) OR
878 (_registered.field = cmf.id)
882 search_class_join := '
884 metabib.search_class_to_registered_components($2)
885 AS _registered (field_class TEXT, field INT) ON (
886 _registered.field_class = cmc.name
891 RETURN QUERY EXECUTE '
892 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
893 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
902 TS_HEADLINE(value, $7, $3)
903 FROM (SELECT DISTINCT
906 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
907 _registered.field = cmf.id AS restrict,
909 TS_RANK_CD(mbe.index_vector, $1, $6),
912 FROM metabib.browse_entry_def_map mbedm
913 JOIN mbe ON (mbe.id = mbedm.entry)
914 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
915 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
916 ' || search_class_join || '
917 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
919 ' || opac_visibility_join || '
920 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
922 ' -- sic, repeat the order by clause in the outer select too
924 query, search_class, headline_opts,
925 visibility_org, query_limit, normalization, plain_query
929 -- buoyant AND chosen class = match class
930 -- chosen field = match field
937 $f$ LANGUAGE plpgsql ROWS 10;
939 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)
940 RETURNS SETOF metabib.flat_browse_entry_appearance
949 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
950 results_skipped INT := 0;
951 row_counter INT := 0;
956 all_records BIGINT[];
957 all_brecords BIGINT[];
958 all_arecords BIGINT[];
959 superpage_of_records BIGINT[];
964 unauthorized_entry RECORD;
966 IF count_up_from_zero THEN
973 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
976 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
977 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
979 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
981 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
982 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
984 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
986 b_tests := b_tests || search.calculate_visibility_attribute_test(
988 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
991 b_tests := b_tests || search.calculate_visibility_attribute_test(
993 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
997 IF context_locations THEN
998 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
999 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
1002 OPEN curs NO SCROLL FOR EXECUTE query;
1005 FETCH curs INTO rec;
1007 IF result_row.pivot_point IS NOT NULL THEN
1008 RETURN NEXT result_row;
1014 SELECT INTO unauthorized_entry *
1015 FROM metabib.browse_entry_simple_heading_map mbeshm
1016 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1017 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
1018 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1019 WHERE mbeshm.entry = rec.id
1020 AND ahf.heading_purpose = 'variant';
1022 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1023 IF (unauthorized_entry.record IS NOT NULL) THEN
1024 --unauthorized term belongs to an auth linked to a bib?
1025 SELECT INTO all_arecords, result_row.sees, afields
1026 ARRAY_AGG(DISTINCT abl.bib),
1027 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
1028 ARRAY_AGG(DISTINCT map.metabib_field)
1029 FROM authority.bib_linking abl
1030 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1031 map.authority_field = unauthorized_entry.atag
1032 AND map.metabib_field = ANY(fields)
1034 WHERE abl.authority = unauthorized_entry.record;
1036 --do usual procedure
1037 SELECT INTO all_arecords, result_row.sees, afields
1038 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1039 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1040 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1042 FROM metabib.browse_entry_simple_heading_map mbeshm
1043 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1044 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1045 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1046 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1047 ash.atag = map.authority_field
1048 AND map.metabib_field = ANY(fields)
1050 JOIN authority.control_set_authority_field acsaf ON (
1051 map.authority_field = acsaf.id
1053 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1054 WHERE mbeshm.entry = rec.id
1055 AND ahf.heading_purpose = 'variant';
1059 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
1060 SELECT INTO all_brecords, result_row.authorities, bfields
1061 ARRAY_AGG(DISTINCT source),
1062 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
1063 ARRAY_AGG(DISTINCT def)
1064 FROM metabib.browse_entry_def_map
1065 WHERE entry = rec.id
1066 AND def = ANY(fields);
1068 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
1070 result_row.sources := 0;
1071 result_row.asources := 0;
1073 -- Bib-linked vis checking
1074 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
1076 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
1077 FROM biblio.record_entry b
1078 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1079 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
1081 acvac.vis_attr_vector @@ c_tests::query_int
1082 OR b.vis_attr_vector @@ b_tests::query_int
1085 result_row.accurate := TRUE;
1089 -- Authority-linked vis checking
1090 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
1092 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
1093 FROM biblio.record_entry b
1094 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1095 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
1097 acvac.vis_attr_vector @@ c_tests::query_int
1098 OR b.vis_attr_vector @@ b_tests::query_int
1101 result_row.aaccurate := TRUE;
1105 IF result_row.sources > 0 OR result_row.asources > 0 THEN
1107 -- The function that calls this function needs row_number in order
1108 -- to correctly order results from two different runs of this
1110 result_row.row_number := row_number;
1112 -- Now, if row_counter is still less than limit, return a row. If
1113 -- not, but it is less than next_pivot_pos, continue on without
1114 -- returning actual result rows until we find
1115 -- that next pivot, and return it.
1117 IF row_counter < result_limit THEN
1118 result_row.browse_entry := rec.id;
1119 result_row.value := rec.value;
1121 RETURN NEXT result_row;
1123 result_row.browse_entry := NULL;
1124 result_row.authorities := NULL;
1125 result_row.fields := NULL;
1126 result_row.value := NULL;
1127 result_row.sources := NULL;
1128 result_row.sees := NULL;
1129 result_row.accurate := NULL;
1130 result_row.aaccurate := NULL;
1131 result_row.pivot_point := rec.id;
1133 IF row_counter >= next_pivot_pos THEN
1134 RETURN NEXT result_row;
1139 IF count_up_from_zero THEN
1140 row_number := row_number + 1;
1142 row_number := row_number - 1;
1145 -- row_counter is different from row_number.
1146 -- It simply counts up from zero so that we know when
1147 -- we've reached our limit.
1148 row_counter := row_counter + 1;
1152 $f$ LANGUAGE plpgsql ROWS 10;
1154 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)
1155 RETURNS SETOF metabib.flat_browse_entry_appearance
1161 pivot_sort_value TEXT;
1162 pivot_sort_fallback TEXT;
1163 context_locations INT[];
1164 browse_superpage_size INT;
1165 results_skipped INT := 0;
1169 forward_to_pivot INT;
1171 -- First, find the pivot if we were given a browse term but not a pivot.
1172 IF pivot_id IS NULL THEN
1173 pivot_id := metabib.browse_pivot(search_field, browse_term);
1176 SELECT INTO pivot_sort_value, pivot_sort_fallback
1177 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
1179 -- Bail if we couldn't find a pivot.
1180 IF pivot_sort_value IS NULL THEN
1184 -- Transform the context_loc_group argument (if any) (logc at the
1185 -- TPAC layer) into a form we'll be able to use.
1186 IF context_loc_group IS NOT NULL THEN
1187 SELECT INTO context_locations ARRAY_AGG(location)
1188 FROM asset.copy_location_group_map
1189 WHERE lgroup = context_loc_group;
1192 -- Get the configured size of browse superpages.
1193 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
1194 FROM config.global_flag
1195 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
1197 -- First we're going to search backward from the pivot, then we're going
1198 -- to search forward. In each direction, we need two limits. At the
1199 -- lesser of the two limits, we delineate the edge of the result set
1200 -- we're going to return. At the greater of the two limits, we find the
1201 -- pivot value that would represent an offset from the current pivot
1202 -- at a distance of one "page" in either direction, where a "page" is a
1203 -- result set of the size specified in the "result_limit" argument.
1205 -- The two limits in each direction make four derived values in total,
1206 -- and we calculate them now.
1207 back_limit := CEIL(result_limit::FLOAT / 2);
1208 back_to_pivot := result_limit;
1209 forward_limit := result_limit / 2;
1210 forward_to_pivot := result_limit - 1;
1212 -- This is the meat of the SQL query that finds browse entries. We'll
1213 -- pass this to a function which uses it with a cursor, so that individual
1214 -- rows may be fetched in a loop until some condition is satisfied, without
1215 -- waiting for a result set of fixed size to be collected all at once.
1220 FROM metabib.browse_entry mbe
1222 EXISTS ( -- are there any bibs using this mbe via the requested fields?
1224 FROM metabib.browse_entry_def_map mbedm
1225 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
1226 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
1228 FROM metabib.browse_entry_simple_heading_map mbeshm
1229 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1230 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1231 ash.atag = map.authority_field
1232 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
1234 JOIN authority.control_set_authority_field acsaf ON (
1235 map.authority_field = acsaf.id
1237 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1238 WHERE mbeshm.entry = mbe.id
1239 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
1240 -- and authority that variant is coming from is linked to a bib
1243 FROM metabib.browse_entry_def_map mbedm2
1244 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) ||)
1250 -- This is the variant of the query for browsing backward.
1251 back_query := core_query ||
1252 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
1253 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
1255 -- This variant browses forward.
1256 forward_query := core_query ||
1257 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
1258 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
1260 -- We now call the function which applies a cursor to the provided
1261 -- queries, stopping at the appropriate limits and also giving us
1262 -- the next page's pivot.
1264 SELECT * FROM metabib.staged_browse(
1265 back_query, search_field, context_org, context_locations,
1266 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
1268 SELECT * FROM metabib.staged_browse(
1269 forward_query, search_field, context_org, context_locations,
1270 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
1271 ) ORDER BY row_number DESC;
1274 $f$ LANGUAGE plpgsql ROWS 10;
1276 CREATE OR REPLACE FUNCTION metabib.browse(
1279 context_org INT DEFAULT NULL,
1280 context_loc_group INT DEFAULT NULL,
1281 staff BOOL DEFAULT FALSE,
1282 pivot_id BIGINT DEFAULT NULL,
1283 result_limit INT DEFAULT 10
1284 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1286 RETURN QUERY SELECT * FROM metabib.browse(
1287 (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
1288 FROM config.metabib_field WHERE field_class = search_class),
1297 $p$ LANGUAGE PLPGSQL ROWS 10;