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 opac_visibility_join := '
441 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
442 JOIN vm ON (acvac.vis_attr_vector @@ vm.c_attrs::query_int)
445 opac_visibility_join := '';
448 -- The following determines whether we only provide suggestsons matching
449 -- the user's selected search_class, or whether we show other suggestions
450 -- too. The reason for MIN() is that for search_classes like
451 -- 'title|proper|uniform' you would otherwise get multiple rows. The
452 -- implication is that if title as a class doesn't have restrict,
453 -- nor does the proper field, but the uniform field does, you're going
454 -- to get 'false' for your overall evaluation of 'should we restrict?'
455 -- To invert that, change from MIN() to MAX().
459 MIN(cmc.restrict::INT) AS restrict_class,
460 MIN(cmf.restrict::INT) AS restrict_field
461 FROM metabib.search_class_to_registered_components(search_class)
462 AS _registered (field_class TEXT, field INT)
464 config.metabib_class cmc ON (cmc.name = _registered.field_class)
466 config.metabib_field cmf ON (cmf.id = _registered.field);
468 -- evaluate 'should we restrict?'
469 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
470 search_class_join := '
472 metabib.search_class_to_registered_components($2)
473 AS _registered (field_class TEXT, field INT) ON (
474 (_registered.field IS NULL AND
475 _registered.field_class = cmf.field_class) OR
476 (_registered.field = cmf.id)
480 search_class_join := '
482 metabib.search_class_to_registered_components($2)
483 AS _registered (field_class TEXT, field INT) ON (
484 _registered.field_class = cmc.name
489 RETURN QUERY EXECUTE '
490 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
491 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
500 TS_HEADLINE(value, $7, $3)
501 FROM (SELECT DISTINCT
504 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
505 _registered.field = cmf.id AS restrict,
507 TS_RANK_CD(mbe.index_vector, $1, $6),
510 FROM metabib.browse_entry_def_map mbedm
511 JOIN mbe ON (mbe.id = mbedm.entry)
512 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
513 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
514 ' || search_class_join || '
515 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
517 ' || opac_visibility_join || '
518 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
520 ' -- sic, repeat the order by clause in the outer select too
522 query, search_class, headline_opts,
523 visibility_org, query_limit, normalization, plain_query
527 -- buoyant AND chosen class = match class
528 -- chosen field = match field
535 $f$ LANGUAGE plpgsql ROWS 10;
537 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)
538 RETURNS SETOF metabib.flat_browse_entry_appearance
544 pivot_sort_value TEXT;
545 pivot_sort_fallback TEXT;
546 context_locations INT[];
547 browse_superpage_size INT;
548 results_skipped INT := 0;
552 forward_to_pivot INT;
554 -- First, find the pivot if we were given a browse term but not a pivot.
555 IF pivot_id IS NULL THEN
556 pivot_id := metabib.browse_pivot(search_field, browse_term);
559 SELECT INTO pivot_sort_value, pivot_sort_fallback
560 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
562 -- Bail if we couldn't find a pivot.
563 IF pivot_sort_value IS NULL THEN
567 -- Transform the context_loc_group argument (if any) (logc at the
568 -- TPAC layer) into a form we'll be able to use.
569 IF context_loc_group IS NOT NULL THEN
570 SELECT INTO context_locations ARRAY_AGG(location)
571 FROM asset.copy_location_group_map
572 WHERE lgroup = context_loc_group;
575 -- Get the configured size of browse superpages.
576 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
577 FROM config.global_flag
578 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
580 -- First we're going to search backward from the pivot, then we're going
581 -- to search forward. In each direction, we need two limits. At the
582 -- lesser of the two limits, we delineate the edge of the result set
583 -- we're going to return. At the greater of the two limits, we find the
584 -- pivot value that would represent an offset from the current pivot
585 -- at a distance of one "page" in either direction, where a "page" is a
586 -- result set of the size specified in the "result_limit" argument.
588 -- The two limits in each direction make four derived values in total,
589 -- and we calculate them now.
590 back_limit := CEIL(result_limit::FLOAT / 2);
591 back_to_pivot := result_limit;
592 forward_limit := result_limit / 2;
593 forward_to_pivot := result_limit - 1;
595 -- This is the meat of the SQL query that finds browse entries. We'll
596 -- pass this to a function which uses it with a cursor, so that individual
597 -- rows may be fetched in a loop until some condition is satisfied, without
598 -- waiting for a result set of fixed size to be collected all at once.
603 FROM metabib.browse_entry mbe
605 EXISTS ( -- are there any bibs using this mbe via the requested fields?
607 FROM metabib.browse_entry_def_map mbedm
608 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
609 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
611 FROM metabib.browse_entry_simple_heading_map mbeshm
612 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
613 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
614 ash.atag = map.authority_field
615 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
617 WHERE mbeshm.entry = mbe.id
621 -- This is the variant of the query for browsing backward.
622 back_query := core_query ||
623 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
624 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
626 -- This variant browses forward.
627 forward_query := core_query ||
628 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
629 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
631 -- We now call the function which applies a cursor to the provided
632 -- queries, stopping at the appropriate limits and also giving us
633 -- the next page's pivot.
635 SELECT * FROM metabib.staged_browse(
636 back_query, search_field, context_org, context_locations,
637 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
639 SELECT * FROM metabib.staged_browse(
640 forward_query, search_field, context_org, context_locations,
641 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
642 ) ORDER BY row_number DESC;
645 $f$ LANGUAGE plpgsql ROWS 10;
647 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)
648 RETURNS SETOF metabib.flat_browse_entry_appearance
657 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
658 results_skipped INT := 0;
659 row_counter INT := 0;
664 all_records BIGINT[];
665 all_brecords BIGINT[];
666 all_arecords BIGINT[];
667 superpage_of_records BIGINT[];
673 IF count_up_from_zero THEN
680 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
683 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
684 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
686 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
688 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
689 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
691 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
693 b_tests := b_tests || search.calculate_visibility_attribute_test(
695 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
698 b_tests := b_tests || search.calculate_visibility_attribute_test(
700 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
704 IF context_locations THEN
705 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
706 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
709 OPEN curs NO SCROLL FOR EXECUTE query;
714 IF result_row.pivot_point IS NOT NULL THEN
715 RETURN NEXT result_row;
720 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
721 SELECT INTO all_arecords, result_row.sees, afields
722 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
723 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
724 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
726 FROM metabib.browse_entry_simple_heading_map mbeshm
727 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
728 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
729 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
730 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
731 ash.atag = map.authority_field
732 AND map.metabib_field = ANY(fields)
734 WHERE mbeshm.entry = rec.id;
736 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
737 SELECT INTO all_brecords, result_row.authorities, bfields
738 ARRAY_AGG(DISTINCT source),
739 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
740 ARRAY_AGG(DISTINCT def)
741 FROM metabib.browse_entry_def_map
743 AND def = ANY(fields);
745 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
747 result_row.sources := 0;
748 result_row.asources := 0;
750 -- Bib-linked vis checking
751 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
753 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
754 FROM biblio.record_entry b
755 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
756 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
758 acvac.vis_attr_vector @@ c_tests::query_int
759 OR b.vis_attr_vector @@ b_tests::query_int
762 result_row.accurate := TRUE;
766 -- Authority-linked vis checking
767 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
769 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
770 FROM biblio.record_entry b
771 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
772 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
774 acvac.vis_attr_vector @@ c_tests::query_int
775 OR b.vis_attr_vector @@ b_tests::query_int
778 result_row.aaccurate := TRUE;
782 IF result_row.sources > 0 OR result_row.asources > 0 THEN
784 -- The function that calls this function needs row_number in order
785 -- to correctly order results from two different runs of this
787 result_row.row_number := row_number;
789 -- Now, if row_counter is still less than limit, return a row. If
790 -- not, but it is less than next_pivot_pos, continue on without
791 -- returning actual result rows until we find
792 -- that next pivot, and return it.
794 IF row_counter < result_limit THEN
795 result_row.browse_entry := rec.id;
796 result_row.value := rec.value;
798 RETURN NEXT result_row;
800 result_row.browse_entry := NULL;
801 result_row.authorities := NULL;
802 result_row.fields := NULL;
803 result_row.value := NULL;
804 result_row.sources := NULL;
805 result_row.sees := NULL;
806 result_row.accurate := NULL;
807 result_row.aaccurate := NULL;
808 result_row.pivot_point := rec.id;
810 IF row_counter >= next_pivot_pos THEN
811 RETURN NEXT result_row;
816 IF count_up_from_zero THEN
817 row_number := row_number + 1;
819 row_number := row_number - 1;
822 -- row_counter is different from row_number.
823 -- It simply counts up from zero so that we know when
824 -- we've reached our limit.
825 row_counter := row_counter + 1;
829 $f$ LANGUAGE plpgsql ROWS 10;
831 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
832 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
833 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
834 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
835 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
836 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
837 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
838 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
841 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
844 asset.calculate_copy_visibility_attribute_set(cp.id)
846 JOIN asset.call_number cn ON (cp.call_number = cn.id);
848 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id);
850 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();
851 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();
852 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
853 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
854 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
855 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
856 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();