3 -- Thist change drops a needless join and saves 10-15% in time cost
4 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
6 SELECT id, value, count
8 SELECT mfae.field AS id,
10 COUNT(DISTINCT mfae.source),
12 PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
14 FROM metabib.facet_entry mfae
15 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
16 WHERE mfae.source = ANY ($2)
18 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
23 (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
29 CREATE OR REPLACE FUNCTION unapi.metabib_virtual_record_feed ( id_list BIGINT[], format TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE, title TEXT DEFAULT NULL, description TEXT DEFAULT NULL, creator TEXT DEFAULT NULL, update_ts TEXT DEFAULT NULL, unapi_url TEXT DEFAULT NULL, header_xml XML DEFAULT NULL ) RETURNS XML AS $F$
31 layout unapi.bre_output_layout%ROWTYPE;
32 transform config.xml_transform%ROWTYPE;
35 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
40 IF org = '-' OR org IS NULL THEN
41 SELECT shortname INTO org FROM evergreen.org_top();
44 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
45 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
47 IF layout.name IS NULL THEN
51 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
52 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
55 SELECT XMLAGG( unapi.mmr(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
57 IF layout.title_element IS NOT NULL THEN
58 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
61 IF layout.description_element IS NOT NULL THEN
62 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
65 IF layout.creator_element IS NOT NULL THEN
66 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
69 IF layout.update_ts_element IS NOT NULL THEN
70 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.update_ts_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, update_ts;
73 IF unapi_url IS NOT NULL THEN
74 EXECUTE $$SELECT XMLCONCAT( XMLELEMENT( name link, XMLATTRIBUTES( 'http://www.w3.org/1999/xhtml' AS xmlns, 'unapi-server' AS rel, $1 AS href, 'unapi' AS title)), $2)$$ INTO tmp_xml USING unapi_url, tmp_xml::XML;
77 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
79 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
80 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
81 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
86 $F$ LANGUAGE PLPGSQL STABLE;
88 CREATE TABLE asset.copy_vis_attr_cache (
89 id BIGSERIAL PRIMARY KEY,
90 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
91 target_copy BIGINT NOT NULL,
94 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
95 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
97 ALTER TABLE biblio.record_entry ADD COLUMN vis_attr_vector INT[];
99 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
102 WHEN 'luri_org' THEN 0 -- "b" attr
103 WHEN 'bib_source' THEN 1 -- "b" attr
105 WHEN 'copy_flags' THEN 0 -- "c" attr
106 WHEN 'owning_lib' THEN 1 -- "c" attr
107 WHEN 'circ_lib' THEN 2 -- "c" attr
108 WHEN 'status' THEN 3 -- "c" attr
109 WHEN 'location' THEN 4 -- "c" attr
110 WHEN 'location_group' THEN 5 -- "c" attr
114 /* copy_flags bit positions, LSB-first:
116 0: asset.copy.opac_visible
119 When adding flags, you must update asset.all_visible_flags()
121 Because bib and copy values are stored separately, we can reuse
122 shifts, saving us some space. We could probably take back a bit
123 too, but I'm not sure its worth squeezing that last one out. We'd
124 be left with just 2 slots for copy attrs, rather than 10.
127 $f$ LANGUAGE SQL IMMUTABLE;
129 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
130 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
131 $f$ LANGUAGE SQL IMMUTABLE;
133 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
134 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
135 $f$ LANGUAGE SQL IMMUTABLE;
137 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
139 copy_row asset.copy%ROWTYPE;
140 lgroup_map asset.copy_location_group_map%ROWTYPE;
143 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
145 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
146 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
147 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
148 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
152 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
154 FROM asset.call_number
155 WHERE id = copy_row.call_number;
157 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
158 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
163 $f$ LANGUAGE PLPGSQL;
165 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$
167 bib_row biblio.record_entry%ROWTYPE;
168 cn_row asset.call_number%ROWTYPE;
171 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
173 IF bib_row.source IS NOT NULL THEN
174 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
179 FROM asset.call_number cn
180 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
181 JOIN asset.uri u ON (u.id = m.uri)
182 WHERE cn.record = bib_id
183 AND cn.label = '##URI##'
186 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
191 $f$ LANGUAGE PLPGSQL;
193 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
195 ocn asset.call_number%ROWTYPE;
196 ncn asset.call_number%ROWTYPE;
200 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
201 IF TG_OP = 'INSERT' THEN
202 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
205 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
209 ELSIF TG_OP = 'DELETE' THEN
210 DELETE FROM asset.copy_vis_attr_cache
211 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
217 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
218 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
219 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
220 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
223 asset.calculate_copy_visibility_attribute_set(NEW.id)
225 ELSIF TG_TABLE_NAME = 'record_entry' THEN
226 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
232 -- handle items first, since with circulation activity
233 -- their statuses change frequently
234 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
236 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
237 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
241 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
243 IF OLD.deleted <> NEW.deleted THEN
245 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
247 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
250 asset.calculate_copy_visibility_attribute_set(NEW.id)
255 ELSIF OLD.call_number <> NEW.call_number THEN
256 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
258 IF ncn.record <> ocn.record THEN
259 UPDATE biblio.record_entry
260 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
261 WHERE id = ocn.record;
265 IF OLD.location <> NEW.location OR
266 OLD.status <> NEW.status OR
267 OLD.opac_visible <> NEW.opac_visible OR
268 OLD.circ_lib <> NEW.circ_lib
270 -- any of these could change visibility, but
271 -- we'll save some queries and not try to calculate
272 -- the change directly
273 UPDATE asset.copy_vis_attr_cache
274 SET target_copy = NEW.id,
275 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
276 WHERE target_copy = OLD.id;
280 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
282 IF OLD.record <> NEW.record THEN
283 IF NEW.label = '##URI##' THEN
284 UPDATE biblio.record_entry
285 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
286 WHERE id = OLD.record;
288 UPDATE biblio.record_entry
289 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
290 WHERE id = NEW.record;
293 UPDATE asset.copy_vis_attr_cache
294 SET record = NEW.record,
295 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
296 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
297 AND record = OLD.record;
299 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
300 UPDATE asset.copy_vis_attr_cache
301 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
302 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
303 AND record = NEW.record;
305 IF NEW.label = '##URI##' THEN
306 UPDATE biblio.record_entry
307 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
308 WHERE id = OLD.record;
312 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
314 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
315 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
317 ELSIF OLD.source <> NEW.source THEN
318 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
325 $func$ LANGUAGE PLPGSQL;
328 -- Helper functions for use in constructing searches --
330 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
331 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
332 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
333 $f$ LANGUAGE SQL STABLE;
335 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
336 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
339 $f$ LANGUAGE SQL STABLE;
341 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
342 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
344 WHERE NOT opac_visible;
345 $f$ LANGUAGE SQL STABLE;
347 -- Bib-oriented defaults for search
348 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
349 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
350 FROM config.bib_source
352 $f$ LANGUAGE SQL IMMUTABLE;
354 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
355 SELECT * FROM asset.invisible_orgs('luri_org');
356 $f$ LANGUAGE SQL STABLE;
358 -- Copy-oriented defaults for search
359 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
360 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
361 FROM asset.copy_location_group
362 WHERE NOT opac_visible;
363 $f$ LANGUAGE SQL STABLE;
365 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
366 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
367 FROM asset.copy_location
368 WHERE NOT opac_visible;
369 $f$ LANGUAGE SQL STABLE;
371 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
372 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
373 FROM config.copy_status
374 WHERE NOT opac_visible;
375 $f$ LANGUAGE SQL STABLE;
377 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
378 SELECT * FROM asset.invisible_orgs('owning_lib');
379 $f$ LANGUAGE SQL STABLE;
381 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
382 SELECT * FROM asset.invisible_orgs('circ_lib');
383 $f$ LANGUAGE SQL STABLE;
385 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
387 copy_flags TEXT; -- "c" attr
389 owning_lib TEXT; -- "c" attr
390 circ_lib TEXT; -- "c" attr
391 status TEXT; -- "c" attr
392 location TEXT; -- "c" attr
393 location_group TEXT; -- "c" attr
395 luri_org TEXT; -- "b" attr
396 bib_sources TEXT; -- "b" attr
398 copy_flags := asset.all_visible_flags(); -- Will always have at least one
400 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
402 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
403 status := NULLIF(asset.status_default(),'!()');
404 location := NULLIF(asset.location_default(),'!()');
405 location_group := NULLIF(asset.location_group_default(),'!()');
407 luri_org := NULLIF(asset.luri_org_default(),'!()');
408 bib_sources := NULLIF(asset.bib_source_default(),'()');
411 '('||ARRAY_TO_STRING(
412 ARRAY[luri_org,bib_sources],
415 '('||ARRAY_TO_STRING(
416 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
420 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
422 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)
423 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
426 prepared_query_texts TEXT[];
429 opac_visibility_join TEXT;
430 search_class_join TEXT;
433 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
435 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
436 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
438 visibility_org := NULLIF(visibility_org,-1);
439 IF visibility_org IS NOT NULL THEN
440 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
442 opac_visibility_join := '';
444 opac_visibility_join := '
445 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
446 JOIN vm ON (acvac.vis_attr_vector @@
447 (vm.c_attrs || $$&$$ ||
448 search.calculate_visibility_attribute_test(
450 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
457 opac_visibility_join := '';
460 -- The following determines whether we only provide suggestsons matching
461 -- the user's selected search_class, or whether we show other suggestions
462 -- too. The reason for MIN() is that for search_classes like
463 -- 'title|proper|uniform' you would otherwise get multiple rows. The
464 -- implication is that if title as a class doesn't have restrict,
465 -- nor does the proper field, but the uniform field does, you're going
466 -- to get 'false' for your overall evaluation of 'should we restrict?'
467 -- To invert that, change from MIN() to MAX().
471 MIN(cmc.restrict::INT) AS restrict_class,
472 MIN(cmf.restrict::INT) AS restrict_field
473 FROM metabib.search_class_to_registered_components(search_class)
474 AS _registered (field_class TEXT, field INT)
476 config.metabib_class cmc ON (cmc.name = _registered.field_class)
478 config.metabib_field cmf ON (cmf.id = _registered.field);
480 -- evaluate 'should we restrict?'
481 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
482 search_class_join := '
484 metabib.search_class_to_registered_components($2)
485 AS _registered (field_class TEXT, field INT) ON (
486 (_registered.field IS NULL AND
487 _registered.field_class = cmf.field_class) OR
488 (_registered.field = cmf.id)
492 search_class_join := '
494 metabib.search_class_to_registered_components($2)
495 AS _registered (field_class TEXT, field INT) ON (
496 _registered.field_class = cmc.name
501 RETURN QUERY EXECUTE '
502 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
503 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
512 TS_HEADLINE(value, $7, $3)
513 FROM (SELECT DISTINCT
516 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
517 _registered.field = cmf.id AS restrict,
519 TS_RANK_CD(mbe.index_vector, $1, $6),
522 FROM metabib.browse_entry_def_map mbedm
523 JOIN mbe ON (mbe.id = mbedm.entry)
524 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
525 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
526 ' || search_class_join || '
527 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
529 ' || opac_visibility_join || '
530 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
532 ' -- sic, repeat the order by clause in the outer select too
534 query, search_class, headline_opts,
535 visibility_org, query_limit, normalization, plain_query
539 -- buoyant AND chosen class = match class
540 -- chosen field = match field
547 $f$ LANGUAGE plpgsql ROWS 10;
549 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)
550 RETURNS SETOF metabib.flat_browse_entry_appearance
556 pivot_sort_value TEXT;
557 pivot_sort_fallback TEXT;
558 context_locations INT[];
559 browse_superpage_size INT;
560 results_skipped INT := 0;
564 forward_to_pivot INT;
566 -- First, find the pivot if we were given a browse term but not a pivot.
567 IF pivot_id IS NULL THEN
568 pivot_id := metabib.browse_pivot(search_field, browse_term);
571 SELECT INTO pivot_sort_value, pivot_sort_fallback
572 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
574 -- Bail if we couldn't find a pivot.
575 IF pivot_sort_value IS NULL THEN
579 -- Transform the context_loc_group argument (if any) (logc at the
580 -- TPAC layer) into a form we'll be able to use.
581 IF context_loc_group IS NOT NULL THEN
582 SELECT INTO context_locations ARRAY_AGG(location)
583 FROM asset.copy_location_group_map
584 WHERE lgroup = context_loc_group;
587 -- Get the configured size of browse superpages.
588 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
589 FROM config.global_flag
590 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
592 -- First we're going to search backward from the pivot, then we're going
593 -- to search forward. In each direction, we need two limits. At the
594 -- lesser of the two limits, we delineate the edge of the result set
595 -- we're going to return. At the greater of the two limits, we find the
596 -- pivot value that would represent an offset from the current pivot
597 -- at a distance of one "page" in either direction, where a "page" is a
598 -- result set of the size specified in the "result_limit" argument.
600 -- The two limits in each direction make four derived values in total,
601 -- and we calculate them now.
602 back_limit := CEIL(result_limit::FLOAT / 2);
603 back_to_pivot := result_limit;
604 forward_limit := result_limit / 2;
605 forward_to_pivot := result_limit - 1;
607 -- This is the meat of the SQL query that finds browse entries. We'll
608 -- pass this to a function which uses it with a cursor, so that individual
609 -- rows may be fetched in a loop until some condition is satisfied, without
610 -- waiting for a result set of fixed size to be collected all at once.
615 FROM metabib.browse_entry mbe
617 EXISTS ( -- are there any bibs using this mbe via the requested fields?
619 FROM metabib.browse_entry_def_map mbedm
620 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
621 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
623 FROM metabib.browse_entry_simple_heading_map mbeshm
624 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
625 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
626 ash.atag = map.authority_field
627 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
629 WHERE mbeshm.entry = mbe.id
633 -- This is the variant of the query for browsing backward.
634 back_query := core_query ||
635 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
636 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
638 -- This variant browses forward.
639 forward_query := core_query ||
640 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
641 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
643 -- We now call the function which applies a cursor to the provided
644 -- queries, stopping at the appropriate limits and also giving us
645 -- the next page's pivot.
647 SELECT * FROM metabib.staged_browse(
648 back_query, search_field, context_org, context_locations,
649 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
651 SELECT * FROM metabib.staged_browse(
652 forward_query, search_field, context_org, context_locations,
653 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
654 ) ORDER BY row_number DESC;
657 $f$ LANGUAGE plpgsql ROWS 10;
659 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)
660 RETURNS SETOF metabib.flat_browse_entry_appearance
669 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
670 results_skipped INT := 0;
671 row_counter INT := 0;
676 all_records BIGINT[];
677 all_brecords BIGINT[];
678 all_arecords BIGINT[];
679 superpage_of_records BIGINT[];
685 IF count_up_from_zero THEN
692 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
695 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
696 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
698 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
700 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
701 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
703 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
705 b_tests := b_tests || search.calculate_visibility_attribute_test(
707 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
710 b_tests := b_tests || search.calculate_visibility_attribute_test(
712 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
716 IF context_locations THEN
717 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
718 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
721 OPEN curs NO SCROLL FOR EXECUTE query;
726 IF result_row.pivot_point IS NOT NULL THEN
727 RETURN NEXT result_row;
732 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
733 SELECT INTO all_arecords, result_row.sees, afields
734 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
735 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
736 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
738 FROM metabib.browse_entry_simple_heading_map mbeshm
739 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
740 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
741 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
742 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
743 ash.atag = map.authority_field
744 AND map.metabib_field = ANY(fields)
746 WHERE mbeshm.entry = rec.id;
748 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
749 SELECT INTO all_brecords, result_row.authorities, bfields
750 ARRAY_AGG(DISTINCT source),
751 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
752 ARRAY_AGG(DISTINCT def)
753 FROM metabib.browse_entry_def_map
755 AND def = ANY(fields);
757 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
759 result_row.sources := 0;
760 result_row.asources := 0;
762 -- Bib-linked vis checking
763 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
765 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
766 FROM biblio.record_entry b
767 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
768 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
770 acvac.vis_attr_vector @@ c_tests::query_int
771 OR b.vis_attr_vector @@ b_tests::query_int
774 result_row.accurate := TRUE;
778 -- Authority-linked vis checking
779 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
781 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
782 FROM biblio.record_entry b
783 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
784 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
786 acvac.vis_attr_vector @@ c_tests::query_int
787 OR b.vis_attr_vector @@ b_tests::query_int
790 result_row.aaccurate := TRUE;
794 IF result_row.sources > 0 OR result_row.asources > 0 THEN
796 -- The function that calls this function needs row_number in order
797 -- to correctly order results from two different runs of this
799 result_row.row_number := row_number;
801 -- Now, if row_counter is still less than limit, return a row. If
802 -- not, but it is less than next_pivot_pos, continue on without
803 -- returning actual result rows until we find
804 -- that next pivot, and return it.
806 IF row_counter < result_limit THEN
807 result_row.browse_entry := rec.id;
808 result_row.value := rec.value;
810 RETURN NEXT result_row;
812 result_row.browse_entry := NULL;
813 result_row.authorities := NULL;
814 result_row.fields := NULL;
815 result_row.value := NULL;
816 result_row.sources := NULL;
817 result_row.sees := NULL;
818 result_row.accurate := NULL;
819 result_row.aaccurate := NULL;
820 result_row.pivot_point := rec.id;
822 IF row_counter >= next_pivot_pos THEN
823 RETURN NEXT result_row;
828 IF count_up_from_zero THEN
829 row_number := row_number + 1;
831 row_number := row_number - 1;
834 -- row_counter is different from row_number.
835 -- It simply counts up from zero so that we know when
836 -- we've reached our limit.
837 row_counter := row_counter + 1;
841 $f$ LANGUAGE plpgsql ROWS 10;
843 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
844 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
845 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
846 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
847 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
848 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
849 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
850 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
853 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
856 asset.calculate_copy_visibility_attribute_set(cp.id)
858 JOIN asset.call_number cn ON (cp.call_number = cn.id);
860 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id);
862 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();
863 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();
864 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
865 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
866 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
867 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
868 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
870 CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
875 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
877 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
879 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
880 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
881 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
885 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
891 asset.copy_vis_attr_cache av
892 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
893 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
897 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
904 $f$ LANGUAGE PLPGSQL;
906 CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
911 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
913 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
915 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
916 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
917 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
921 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
926 asset.copy_vis_attr_cache av
927 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
928 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
932 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
939 $f$ LANGUAGE PLPGSQL;
941 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
946 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
948 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
950 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
951 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
952 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
956 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
962 asset.copy_vis_attr_cache av
963 JOIN asset.copy cp ON (cp.id = av.target_copy)
964 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
965 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
969 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
976 $f$ LANGUAGE PLPGSQL;
978 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
983 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
985 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
987 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
988 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
989 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
993 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
999 asset.copy_vis_attr_cache av
1000 JOIN asset.copy cp ON (cp.id = av.target_copy)
1001 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
1002 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
1006 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1013 $f$ LANGUAGE PLPGSQL;