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 bib_tests TEXT := '';
801 copy_flags := asset.all_visible_flags(); -- Will always have at least one
803 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
805 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
806 status := NULLIF(asset.status_default(),'!()');
807 location := NULLIF(asset.location_default(),'!()');
808 location_group := NULLIF(asset.location_group_default(),'!()');
810 -- LURIs will be handled at the perl layer directly
811 -- luri_org := NULLIF(asset.luri_org_default(),'!()');
812 bib_sources := NULLIF(asset.bib_source_default(),'()');
815 IF luri_org IS NOT NULL AND bib_sources IS NOT NULL THEN
816 bib_tests := '('||ARRAY_TO_STRING( ARRAY[luri_org,bib_sources], '|')||')&('||luri_org||')&';
817 ELSIF luri_org IS NOT NULL THEN
818 bib_tests := luri_org || '&';
819 ELSIF bib_sources IS NOT NULL THEN
820 bib_tests := bib_sources || '|';
823 RETURN QUERY SELECT bib_tests,
824 '('||ARRAY_TO_STRING(
825 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
829 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
831 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)
832 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
835 prepared_query_texts TEXT[];
838 opac_visibility_join TEXT;
839 search_class_join TEXT;
842 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
844 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
845 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
847 visibility_org := NULLIF(visibility_org,-1);
848 IF visibility_org IS NOT NULL THEN
849 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
851 opac_visibility_join := '';
853 opac_visibility_join := '
854 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
855 JOIN vm ON (acvac.vis_attr_vector @@
856 (vm.c_attrs || $$&$$ ||
857 search.calculate_visibility_attribute_test(
859 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
866 opac_visibility_join := '';
869 -- The following determines whether we only provide suggestsons matching
870 -- the user's selected search_class, or whether we show other suggestions
871 -- too. The reason for MIN() is that for search_classes like
872 -- 'title|proper|uniform' you would otherwise get multiple rows. The
873 -- implication is that if title as a class doesn't have restrict,
874 -- nor does the proper field, but the uniform field does, you're going
875 -- to get 'false' for your overall evaluation of 'should we restrict?'
876 -- To invert that, change from MIN() to MAX().
880 MIN(cmc.restrict::INT) AS restrict_class,
881 MIN(cmf.restrict::INT) AS restrict_field
882 FROM metabib.search_class_to_registered_components(search_class)
883 AS _registered (field_class TEXT, field INT)
885 config.metabib_class cmc ON (cmc.name = _registered.field_class)
887 config.metabib_field cmf ON (cmf.id = _registered.field);
889 -- evaluate 'should we restrict?'
890 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
891 search_class_join := '
893 metabib.search_class_to_registered_components($2)
894 AS _registered (field_class TEXT, field INT) ON (
895 (_registered.field IS NULL AND
896 _registered.field_class = cmf.field_class) OR
897 (_registered.field = cmf.id)
901 search_class_join := '
903 metabib.search_class_to_registered_components($2)
904 AS _registered (field_class TEXT, field INT) ON (
905 _registered.field_class = cmc.name
910 RETURN QUERY EXECUTE '
911 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
912 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
921 TS_HEADLINE(value, $7, $3)
922 FROM (SELECT DISTINCT
925 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
926 _registered.field = cmf.id AS restrict,
928 TS_RANK_CD(mbe.index_vector, $1, $6),
931 FROM metabib.browse_entry_def_map mbedm
932 JOIN mbe ON (mbe.id = mbedm.entry)
933 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
934 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
935 ' || search_class_join || '
936 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
938 ' || opac_visibility_join || '
939 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
941 ' -- sic, repeat the order by clause in the outer select too
943 query, search_class, headline_opts,
944 visibility_org, query_limit, normalization, plain_query
948 -- buoyant AND chosen class = match class
949 -- chosen field = match field
956 $f$ LANGUAGE plpgsql ROWS 10;
958 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)
959 RETURNS SETOF metabib.flat_browse_entry_appearance
968 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
969 results_skipped INT := 0;
970 row_counter INT := 0;
975 all_records BIGINT[];
976 all_brecords BIGINT[];
977 all_arecords BIGINT[];
978 superpage_of_records BIGINT[];
983 unauthorized_entry RECORD;
985 IF count_up_from_zero THEN
992 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
995 -- b_tests supplies its own query_int operator, c_tests does not
996 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
998 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
1000 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
1001 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
1003 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
1005 b_tests := b_tests || search.calculate_visibility_attribute_test(
1007 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
1010 b_tests := b_tests || search.calculate_visibility_attribute_test(
1012 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
1016 IF context_locations THEN
1017 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
1018 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
1021 OPEN curs NO SCROLL FOR EXECUTE query;
1024 FETCH curs INTO rec;
1026 IF result_row.pivot_point IS NOT NULL THEN
1027 RETURN NEXT result_row;
1033 SELECT INTO unauthorized_entry *
1034 FROM metabib.browse_entry_simple_heading_map mbeshm
1035 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1036 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
1037 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1038 WHERE mbeshm.entry = rec.id
1039 AND ahf.heading_purpose = 'variant';
1041 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1042 IF (unauthorized_entry.record IS NOT NULL) THEN
1043 --unauthorized term belongs to an auth linked to a bib?
1044 SELECT INTO all_arecords, result_row.sees, afields
1045 ARRAY_AGG(DISTINCT abl.bib),
1046 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
1047 ARRAY_AGG(DISTINCT map.metabib_field)
1048 FROM authority.bib_linking abl
1049 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1050 map.authority_field = unauthorized_entry.atag
1051 AND map.metabib_field = ANY(fields)
1053 WHERE abl.authority = unauthorized_entry.record;
1055 --do usual procedure
1056 SELECT INTO all_arecords, result_row.sees, afields
1057 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1058 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1059 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1061 FROM metabib.browse_entry_simple_heading_map mbeshm
1062 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1063 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1064 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1065 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1066 ash.atag = map.authority_field
1067 AND map.metabib_field = ANY(fields)
1069 JOIN authority.control_set_authority_field acsaf ON (
1070 map.authority_field = acsaf.id
1072 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1073 WHERE mbeshm.entry = rec.id
1074 AND ahf.heading_purpose = 'variant';
1078 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
1079 SELECT INTO all_brecords, result_row.authorities, bfields
1080 ARRAY_AGG(DISTINCT source),
1081 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
1082 ARRAY_AGG(DISTINCT def)
1083 FROM metabib.browse_entry_def_map
1084 WHERE entry = rec.id
1085 AND def = ANY(fields);
1087 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
1089 result_row.sources := 0;
1090 result_row.asources := 0;
1092 -- Bib-linked vis checking
1093 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
1095 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
1096 FROM biblio.record_entry b
1097 LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1098 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
1100 acvac.vis_attr_vector @@ c_tests::query_int
1101 OR b.vis_attr_vector @@ b_tests::query_int
1104 result_row.accurate := TRUE;
1108 -- Authority-linked vis checking
1109 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
1111 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
1112 FROM biblio.record_entry b
1113 LEFT JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1114 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
1116 acvac.vis_attr_vector @@ c_tests::query_int
1117 OR b.vis_attr_vector @@ b_tests::query_int
1120 result_row.aaccurate := TRUE;
1124 IF result_row.sources > 0 OR result_row.asources > 0 THEN
1126 -- The function that calls this function needs row_number in order
1127 -- to correctly order results from two different runs of this
1129 result_row.row_number := row_number;
1131 -- Now, if row_counter is still less than limit, return a row. If
1132 -- not, but it is less than next_pivot_pos, continue on without
1133 -- returning actual result rows until we find
1134 -- that next pivot, and return it.
1136 IF row_counter < result_limit THEN
1137 result_row.browse_entry := rec.id;
1138 result_row.value := rec.value;
1140 RETURN NEXT result_row;
1142 result_row.browse_entry := NULL;
1143 result_row.authorities := NULL;
1144 result_row.fields := NULL;
1145 result_row.value := NULL;
1146 result_row.sources := NULL;
1147 result_row.sees := NULL;
1148 result_row.accurate := NULL;
1149 result_row.aaccurate := NULL;
1150 result_row.pivot_point := rec.id;
1152 IF row_counter >= next_pivot_pos THEN
1153 RETURN NEXT result_row;
1158 IF count_up_from_zero THEN
1159 row_number := row_number + 1;
1161 row_number := row_number - 1;
1164 -- row_counter is different from row_number.
1165 -- It simply counts up from zero so that we know when
1166 -- we've reached our limit.
1167 row_counter := row_counter + 1;
1171 $f$ LANGUAGE plpgsql ROWS 10;
1173 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)
1174 RETURNS SETOF metabib.flat_browse_entry_appearance
1180 pivot_sort_value TEXT;
1181 pivot_sort_fallback TEXT;
1182 context_locations INT[];
1183 browse_superpage_size INT;
1184 results_skipped INT := 0;
1188 forward_to_pivot INT;
1190 -- First, find the pivot if we were given a browse term but not a pivot.
1191 IF pivot_id IS NULL THEN
1192 pivot_id := metabib.browse_pivot(search_field, browse_term);
1195 SELECT INTO pivot_sort_value, pivot_sort_fallback
1196 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
1198 -- Bail if we couldn't find a pivot.
1199 IF pivot_sort_value IS NULL THEN
1203 -- Transform the context_loc_group argument (if any) (logc at the
1204 -- TPAC layer) into a form we'll be able to use.
1205 IF context_loc_group IS NOT NULL THEN
1206 SELECT INTO context_locations ARRAY_AGG(location)
1207 FROM asset.copy_location_group_map
1208 WHERE lgroup = context_loc_group;
1211 -- Get the configured size of browse superpages.
1212 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
1213 FROM config.global_flag
1214 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
1216 -- First we're going to search backward from the pivot, then we're going
1217 -- to search forward. In each direction, we need two limits. At the
1218 -- lesser of the two limits, we delineate the edge of the result set
1219 -- we're going to return. At the greater of the two limits, we find the
1220 -- pivot value that would represent an offset from the current pivot
1221 -- at a distance of one "page" in either direction, where a "page" is a
1222 -- result set of the size specified in the "result_limit" argument.
1224 -- The two limits in each direction make four derived values in total,
1225 -- and we calculate them now.
1226 back_limit := CEIL(result_limit::FLOAT / 2);
1227 back_to_pivot := result_limit;
1228 forward_limit := result_limit / 2;
1229 forward_to_pivot := result_limit - 1;
1231 -- This is the meat of the SQL query that finds browse entries. We'll
1232 -- pass this to a function which uses it with a cursor, so that individual
1233 -- rows may be fetched in a loop until some condition is satisfied, without
1234 -- waiting for a result set of fixed size to be collected all at once.
1239 FROM metabib.browse_entry mbe
1241 EXISTS ( -- are there any bibs using this mbe via the requested fields?
1243 FROM metabib.browse_entry_def_map mbedm
1244 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
1245 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
1247 FROM metabib.browse_entry_simple_heading_map mbeshm
1248 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1249 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1250 ash.atag = map.authority_field
1251 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
1253 JOIN authority.control_set_authority_field acsaf ON (
1254 map.authority_field = acsaf.id
1256 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1257 WHERE mbeshm.entry = mbe.id
1258 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
1259 -- and authority that variant is coming from is linked to a bib
1262 FROM metabib.browse_entry_def_map mbedm2
1263 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
1269 -- This is the variant of the query for browsing backward.
1270 back_query := core_query ||
1271 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
1272 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
1274 -- This variant browses forward.
1275 forward_query := core_query ||
1276 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
1277 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
1279 -- We now call the function which applies a cursor to the provided
1280 -- queries, stopping at the appropriate limits and also giving us
1281 -- the next page's pivot.
1283 SELECT * FROM metabib.staged_browse(
1284 back_query, search_field, context_org, context_locations,
1285 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
1287 SELECT * FROM metabib.staged_browse(
1288 forward_query, search_field, context_org, context_locations,
1289 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
1290 ) ORDER BY row_number DESC;
1293 $f$ LANGUAGE plpgsql ROWS 10;
1295 CREATE OR REPLACE FUNCTION metabib.browse(
1298 context_org INT DEFAULT NULL,
1299 context_loc_group INT DEFAULT NULL,
1300 staff BOOL DEFAULT FALSE,
1301 pivot_id BIGINT DEFAULT NULL,
1302 result_limit INT DEFAULT 10
1303 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1305 RETURN QUERY SELECT * FROM metabib.browse(
1306 (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
1307 FROM config.metabib_field WHERE field_class = search_class),
1316 $p$ LANGUAGE PLPGSQL ROWS 10;
1318 CREATE OR REPLACE VIEW search.best_tsconfig AS
1320 COALESCE(f.ts_config, c.ts_config, 'simple') AS ts_config
1321 FROM config.metabib_field m
1322 LEFT JOIN config.metabib_class_ts_map c ON (c.field_class = m.field_class AND c.index_weight = 'C')
1323 LEFT JOIN config.metabib_field_ts_map f ON (f.metabib_field = m.id AND f.index_weight = 'C');
1325 CREATE TYPE search.highlight_result AS ( id BIGINT, source BIGINT, field INT, value TEXT, highlight TEXT );
1327 CREATE OR REPLACE FUNCTION search.highlight_display_fields_impl(
1330 field_list INT[] DEFAULT '{}'::INT[],
1331 css_class TEXT DEFAULT 'oils_SH',
1332 hl_all BOOL DEFAULT TRUE,
1333 minwords INT DEFAULT 5,
1334 maxwords INT DEFAULT 25,
1335 shortwords INT DEFAULT 0,
1336 maxfrags INT DEFAULT 0,
1337 delimiter TEXT DEFAULT ' ... '
1338 ) RETURNS SETOF search.highlight_result AS $f$
1341 v_css_class TEXT := css_class;
1342 v_delimiter TEXT := delimiter;
1343 v_field_list INT[] := field_list;
1346 IF v_delimiter LIKE $$%'%$$ OR v_delimiter LIKE '%"%' THEN --"
1347 v_delimiter := ' ... ';
1351 opts := opts || 'MinWords=' || minwords;
1352 opts := opts || ', MaxWords=' || maxwords;
1353 opts := opts || ', ShortWords=' || shortwords;
1354 opts := opts || ', MaxFragments=' || maxfrags;
1355 opts := opts || ', FragmentDelimiter="' || delimiter || '"';
1357 opts := opts || 'HighlightAll=TRUE';
1360 IF v_css_class LIKE $$%'%$$ OR v_css_class LIKE '%"%' THEN -- "
1361 v_css_class := 'oils_SH';
1364 opts := opts || $$, StopSel=</b>, StartSel="<b class='$$ || v_css_class; -- "
1366 IF v_field_list = '{}'::INT[] THEN
1367 SELECT ARRAY_AGG(id) INTO v_field_list FROM config.metabib_field WHERE display_field;
1376 ts_config::REGCONFIG,
1377 evergreen.escape_for_html(de.value),
1378 $$ || quote_literal(tsq) || $$,
1379 $1 || ' ' || mf.field_class || ' ' || mf.name || $xx$'>"$xx$ -- "'
1381 FROM metabib.display_entry de
1382 JOIN config.metabib_field mf ON (mf.id = de.field)
1383 JOIN search.best_tsconfig t ON (t.id = de.field)
1384 WHERE de.source = $2
1385 AND field = ANY ($3)
1388 RETURN QUERY EXECUTE hl_query USING opts, rid, v_field_list;
1390 $f$ LANGUAGE PLPGSQL;
1392 CREATE OR REPLACE FUNCTION evergreen.escape_for_html (TEXT) RETURNS TEXT AS $$
1393 SELECT regexp_replace(
1409 $$ LANGUAGE SQL IMMUTABLE LEAKPROOF STRICT COST 10;
1411 CREATE OR REPLACE FUNCTION search.highlight_display_fields(
1413 tsq_map TEXT, -- { '(a | b) & c' => '1,2,3,4', ...}
1414 css_class TEXT DEFAULT 'oils_SH',
1415 hl_all BOOL DEFAULT TRUE,
1416 minwords INT DEFAULT 5,
1417 maxwords INT DEFAULT 25,
1418 shortwords INT DEFAULT 0,
1419 maxfrags INT DEFAULT 0,
1420 delimiter TEXT DEFAULT ' ... '
1421 ) RETURNS SETOF search.highlight_result AS $f$
1429 IF (tsq_map ILIKE 'hstore%') THEN
1430 EXECUTE 'SELECT ' || tsq_map INTO tsq_hstore;
1432 tsq_hstore := tsq_map::HSTORE;
1435 FOR tsq, fields IN SELECT key, value FROM each(tsq_hstore::HSTORE) LOOP
1436 SELECT ARRAY_AGG(unnest::INT) INTO afields
1437 FROM unnest(regexp_split_to_array(fields,','));
1438 seen := seen || afields;
1441 SELECT * FROM search.highlight_display_fields_impl(
1442 rid, tsq, afields, css_class, hl_all,minwords,
1443 maxwords, shortwords, maxfrags, delimiter
1453 FROM metabib.display_entry
1455 AND NOT (field = ANY (seen));
1457 $f$ LANGUAGE PLPGSQL ROWS 10;