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;
648 -- We have to use a record-specific WHERE clause
649 -- to avoid modifying the entries for peer-bib copies.
650 UPDATE asset.copy_vis_attr_cache
651 SET target_copy = NEW.id,
653 WHERE target_copy = OLD.id
654 AND record = ocn.record;
658 IF OLD.location <> NEW.location OR
659 OLD.status <> NEW.status OR
660 OLD.opac_visible <> NEW.opac_visible OR
661 OLD.circ_lib <> NEW.circ_lib
663 -- Any of these could change visibility, but
664 -- we'll save some queries and not try to calculate
665 -- the change directly. We want to update peer-bib
666 -- entries in this case, unlike above.
667 UPDATE asset.copy_vis_attr_cache
668 SET target_copy = NEW.id,
669 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
670 WHERE target_copy = OLD.id;
674 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
676 IF OLD.record <> NEW.record THEN
677 IF NEW.label = '##URI##' THEN
678 UPDATE biblio.record_entry
679 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
680 WHERE id = OLD.record;
682 UPDATE biblio.record_entry
683 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
684 WHERE id = NEW.record;
687 UPDATE asset.copy_vis_attr_cache
688 SET record = NEW.record,
689 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
690 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
691 AND record = OLD.record;
693 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
694 UPDATE asset.copy_vis_attr_cache
695 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
696 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
697 AND record = NEW.record;
699 IF NEW.label = '##URI##' THEN
700 UPDATE biblio.record_entry
701 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
702 WHERE id = OLD.record;
706 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
708 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
709 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
711 ELSIF OLD.source <> NEW.source THEN
712 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
719 $func$ LANGUAGE PLPGSQL;
721 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();
722 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();
723 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
724 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
725 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
726 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
727 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
729 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
730 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
731 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
732 $f$ LANGUAGE SQL STABLE;
734 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
735 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
738 $f$ LANGUAGE SQL STABLE;
740 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
741 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
743 WHERE NOT opac_visible;
744 $f$ LANGUAGE SQL STABLE;
746 -- Bib-oriented defaults for search
747 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
748 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
749 FROM config.bib_source
751 $f$ LANGUAGE SQL IMMUTABLE;
753 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
754 SELECT * FROM asset.invisible_orgs('luri_org');
755 $f$ LANGUAGE SQL STABLE;
757 -- Copy-oriented defaults for search
758 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
759 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
760 FROM asset.copy_location_group
761 WHERE NOT opac_visible;
762 $f$ LANGUAGE SQL STABLE;
764 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
765 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
766 FROM asset.copy_location
767 WHERE NOT opac_visible;
768 $f$ LANGUAGE SQL STABLE;
770 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
771 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
772 FROM config.copy_status
773 WHERE NOT opac_visible;
774 $f$ LANGUAGE SQL STABLE;
776 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
777 SELECT * FROM asset.invisible_orgs('owning_lib');
778 $f$ LANGUAGE SQL STABLE;
780 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
781 SELECT * FROM asset.invisible_orgs('circ_lib');
782 $f$ LANGUAGE SQL STABLE;
784 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
786 copy_flags TEXT; -- "c" attr
788 owning_lib TEXT; -- "c" attr
789 circ_lib TEXT; -- "c" attr
790 status TEXT; -- "c" attr
791 location TEXT; -- "c" attr
792 location_group TEXT; -- "c" attr
794 luri_org TEXT; -- "b" attr
795 bib_sources TEXT; -- "b" attr
797 copy_flags := asset.all_visible_flags(); -- Will always have at least one
799 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
801 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
802 status := NULLIF(asset.status_default(),'!()');
803 location := NULLIF(asset.location_default(),'!()');
804 location_group := NULLIF(asset.location_group_default(),'!()');
806 luri_org := NULLIF(asset.luri_org_default(),'!()');
807 bib_sources := NULLIF(asset.bib_source_default(),'()');
810 '('||ARRAY_TO_STRING(
811 ARRAY[luri_org,bib_sources],
814 '('||ARRAY_TO_STRING(
815 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
819 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
821 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)
822 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
825 prepared_query_texts TEXT[];
828 opac_visibility_join TEXT;
829 search_class_join TEXT;
832 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
834 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
835 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
837 visibility_org := NULLIF(visibility_org,-1);
838 IF visibility_org IS NOT NULL THEN
839 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
841 opac_visibility_join := '';
843 opac_visibility_join := '
844 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
845 JOIN vm ON (acvac.vis_attr_vector @@
846 (vm.c_attrs || $$&$$ ||
847 search.calculate_visibility_attribute_test(
849 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
856 opac_visibility_join := '';
859 -- The following determines whether we only provide suggestsons matching
860 -- the user's selected search_class, or whether we show other suggestions
861 -- too. The reason for MIN() is that for search_classes like
862 -- 'title|proper|uniform' you would otherwise get multiple rows. The
863 -- implication is that if title as a class doesn't have restrict,
864 -- nor does the proper field, but the uniform field does, you're going
865 -- to get 'false' for your overall evaluation of 'should we restrict?'
866 -- To invert that, change from MIN() to MAX().
870 MIN(cmc.restrict::INT) AS restrict_class,
871 MIN(cmf.restrict::INT) AS restrict_field
872 FROM metabib.search_class_to_registered_components(search_class)
873 AS _registered (field_class TEXT, field INT)
875 config.metabib_class cmc ON (cmc.name = _registered.field_class)
877 config.metabib_field cmf ON (cmf.id = _registered.field);
879 -- evaluate 'should we restrict?'
880 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
881 search_class_join := '
883 metabib.search_class_to_registered_components($2)
884 AS _registered (field_class TEXT, field INT) ON (
885 (_registered.field IS NULL AND
886 _registered.field_class = cmf.field_class) OR
887 (_registered.field = cmf.id)
891 search_class_join := '
893 metabib.search_class_to_registered_components($2)
894 AS _registered (field_class TEXT, field INT) ON (
895 _registered.field_class = cmc.name
900 RETURN QUERY EXECUTE '
901 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
902 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
911 TS_HEADLINE(value, $7, $3)
912 FROM (SELECT DISTINCT
915 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
916 _registered.field = cmf.id AS restrict,
918 TS_RANK_CD(mbe.index_vector, $1, $6),
921 FROM metabib.browse_entry_def_map mbedm
922 JOIN mbe ON (mbe.id = mbedm.entry)
923 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
924 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
925 ' || search_class_join || '
926 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
928 ' || opac_visibility_join || '
929 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
931 ' -- sic, repeat the order by clause in the outer select too
933 query, search_class, headline_opts,
934 visibility_org, query_limit, normalization, plain_query
938 -- buoyant AND chosen class = match class
939 -- chosen field = match field
946 $f$ LANGUAGE plpgsql ROWS 10;
948 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)
949 RETURNS SETOF metabib.flat_browse_entry_appearance
958 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
959 results_skipped INT := 0;
960 row_counter INT := 0;
965 all_records BIGINT[];
966 all_brecords BIGINT[];
967 all_arecords BIGINT[];
968 superpage_of_records BIGINT[];
973 unauthorized_entry RECORD;
975 IF count_up_from_zero THEN
982 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
985 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
986 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
988 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
990 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
991 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
993 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
995 b_tests := b_tests || search.calculate_visibility_attribute_test(
997 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
1000 b_tests := b_tests || search.calculate_visibility_attribute_test(
1002 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
1006 IF context_locations THEN
1007 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
1008 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
1011 OPEN curs NO SCROLL FOR EXECUTE query;
1014 FETCH curs INTO rec;
1016 IF result_row.pivot_point IS NOT NULL THEN
1017 RETURN NEXT result_row;
1023 SELECT INTO unauthorized_entry *
1024 FROM metabib.browse_entry_simple_heading_map mbeshm
1025 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1026 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
1027 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1028 WHERE mbeshm.entry = rec.id
1029 AND ahf.heading_purpose = 'variant';
1031 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1032 IF (unauthorized_entry.record IS NOT NULL) THEN
1033 --unauthorized term belongs to an auth linked to a bib?
1034 SELECT INTO all_arecords, result_row.sees, afields
1035 ARRAY_AGG(DISTINCT abl.bib),
1036 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
1037 ARRAY_AGG(DISTINCT map.metabib_field)
1038 FROM authority.bib_linking abl
1039 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1040 map.authority_field = unauthorized_entry.atag
1041 AND map.metabib_field = ANY(fields)
1043 WHERE abl.authority = unauthorized_entry.record;
1045 --do usual procedure
1046 SELECT INTO all_arecords, result_row.sees, afields
1047 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1048 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1049 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1051 FROM metabib.browse_entry_simple_heading_map mbeshm
1052 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1053 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1054 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1055 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1056 ash.atag = map.authority_field
1057 AND map.metabib_field = ANY(fields)
1059 JOIN authority.control_set_authority_field acsaf ON (
1060 map.authority_field = acsaf.id
1062 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1063 WHERE mbeshm.entry = rec.id
1064 AND ahf.heading_purpose = 'variant';
1068 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
1069 SELECT INTO all_brecords, result_row.authorities, bfields
1070 ARRAY_AGG(DISTINCT source),
1071 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
1072 ARRAY_AGG(DISTINCT def)
1073 FROM metabib.browse_entry_def_map
1074 WHERE entry = rec.id
1075 AND def = ANY(fields);
1077 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
1079 result_row.sources := 0;
1080 result_row.asources := 0;
1082 -- Bib-linked vis checking
1083 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
1085 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
1086 FROM biblio.record_entry b
1087 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1088 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
1090 acvac.vis_attr_vector @@ c_tests::query_int
1091 OR b.vis_attr_vector @@ b_tests::query_int
1094 result_row.accurate := TRUE;
1098 -- Authority-linked vis checking
1099 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
1101 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
1102 FROM biblio.record_entry b
1103 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
1104 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
1106 acvac.vis_attr_vector @@ c_tests::query_int
1107 OR b.vis_attr_vector @@ b_tests::query_int
1110 result_row.aaccurate := TRUE;
1114 IF result_row.sources > 0 OR result_row.asources > 0 THEN
1116 -- The function that calls this function needs row_number in order
1117 -- to correctly order results from two different runs of this
1119 result_row.row_number := row_number;
1121 -- Now, if row_counter is still less than limit, return a row. If
1122 -- not, but it is less than next_pivot_pos, continue on without
1123 -- returning actual result rows until we find
1124 -- that next pivot, and return it.
1126 IF row_counter < result_limit THEN
1127 result_row.browse_entry := rec.id;
1128 result_row.value := rec.value;
1130 RETURN NEXT result_row;
1132 result_row.browse_entry := NULL;
1133 result_row.authorities := NULL;
1134 result_row.fields := NULL;
1135 result_row.value := NULL;
1136 result_row.sources := NULL;
1137 result_row.sees := NULL;
1138 result_row.accurate := NULL;
1139 result_row.aaccurate := NULL;
1140 result_row.pivot_point := rec.id;
1142 IF row_counter >= next_pivot_pos THEN
1143 RETURN NEXT result_row;
1148 IF count_up_from_zero THEN
1149 row_number := row_number + 1;
1151 row_number := row_number - 1;
1154 -- row_counter is different from row_number.
1155 -- It simply counts up from zero so that we know when
1156 -- we've reached our limit.
1157 row_counter := row_counter + 1;
1161 $f$ LANGUAGE plpgsql ROWS 10;
1163 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)
1164 RETURNS SETOF metabib.flat_browse_entry_appearance
1170 pivot_sort_value TEXT;
1171 pivot_sort_fallback TEXT;
1172 context_locations INT[];
1173 browse_superpage_size INT;
1174 results_skipped INT := 0;
1178 forward_to_pivot INT;
1180 -- First, find the pivot if we were given a browse term but not a pivot.
1181 IF pivot_id IS NULL THEN
1182 pivot_id := metabib.browse_pivot(search_field, browse_term);
1185 SELECT INTO pivot_sort_value, pivot_sort_fallback
1186 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
1188 -- Bail if we couldn't find a pivot.
1189 IF pivot_sort_value IS NULL THEN
1193 -- Transform the context_loc_group argument (if any) (logc at the
1194 -- TPAC layer) into a form we'll be able to use.
1195 IF context_loc_group IS NOT NULL THEN
1196 SELECT INTO context_locations ARRAY_AGG(location)
1197 FROM asset.copy_location_group_map
1198 WHERE lgroup = context_loc_group;
1201 -- Get the configured size of browse superpages.
1202 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
1203 FROM config.global_flag
1204 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
1206 -- First we're going to search backward from the pivot, then we're going
1207 -- to search forward. In each direction, we need two limits. At the
1208 -- lesser of the two limits, we delineate the edge of the result set
1209 -- we're going to return. At the greater of the two limits, we find the
1210 -- pivot value that would represent an offset from the current pivot
1211 -- at a distance of one "page" in either direction, where a "page" is a
1212 -- result set of the size specified in the "result_limit" argument.
1214 -- The two limits in each direction make four derived values in total,
1215 -- and we calculate them now.
1216 back_limit := CEIL(result_limit::FLOAT / 2);
1217 back_to_pivot := result_limit;
1218 forward_limit := result_limit / 2;
1219 forward_to_pivot := result_limit - 1;
1221 -- This is the meat of the SQL query that finds browse entries. We'll
1222 -- pass this to a function which uses it with a cursor, so that individual
1223 -- rows may be fetched in a loop until some condition is satisfied, without
1224 -- waiting for a result set of fixed size to be collected all at once.
1229 FROM metabib.browse_entry mbe
1231 EXISTS ( -- are there any bibs using this mbe via the requested fields?
1233 FROM metabib.browse_entry_def_map mbedm
1234 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
1235 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
1237 FROM metabib.browse_entry_simple_heading_map mbeshm
1238 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1239 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1240 ash.atag = map.authority_field
1241 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
1243 JOIN authority.control_set_authority_field acsaf ON (
1244 map.authority_field = acsaf.id
1246 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
1247 WHERE mbeshm.entry = mbe.id
1248 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
1249 -- and authority that variant is coming from is linked to a bib
1252 FROM metabib.browse_entry_def_map mbedm2
1253 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
1259 -- This is the variant of the query for browsing backward.
1260 back_query := core_query ||
1261 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
1262 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
1264 -- This variant browses forward.
1265 forward_query := core_query ||
1266 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
1267 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
1269 -- We now call the function which applies a cursor to the provided
1270 -- queries, stopping at the appropriate limits and also giving us
1271 -- the next page's pivot.
1273 SELECT * FROM metabib.staged_browse(
1274 back_query, search_field, context_org, context_locations,
1275 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
1277 SELECT * FROM metabib.staged_browse(
1278 forward_query, search_field, context_org, context_locations,
1279 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
1280 ) ORDER BY row_number DESC;
1283 $f$ LANGUAGE plpgsql ROWS 10;
1285 CREATE OR REPLACE FUNCTION metabib.browse(
1288 context_org INT DEFAULT NULL,
1289 context_loc_group INT DEFAULT NULL,
1290 staff BOOL DEFAULT FALSE,
1291 pivot_id BIGINT DEFAULT NULL,
1292 result_limit INT DEFAULT 10
1293 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1295 RETURN QUERY SELECT * FROM metabib.browse(
1296 (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[])
1297 FROM config.metabib_field WHERE field_class = search_class),
1306 $p$ LANGUAGE PLPGSQL ROWS 10;