3 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1057', :eg_version); -- miker/gmcharlt/kmlussier
5 -- Thist change drops a needless join and saves 10-15% in time cost
6 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
8 SELECT id, value, count
10 SELECT mfae.field AS id,
12 COUNT(DISTINCT mfae.source),
14 PARTITION BY mfae.field ORDER BY COUNT(DISTINCT mfae.source) DESC
16 FROM metabib.facet_entry mfae
17 JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
18 WHERE mfae.source = ANY ($2)
20 AND cmf.field_class NOT IN (SELECT * FROM unnest($1))
25 (SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled),
31 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$
33 layout unapi.bre_output_layout%ROWTYPE;
34 transform config.xml_transform%ROWTYPE;
37 xmlns_uri TEXT := 'http://open-ils.org/spec/feed-xml/v1';
42 IF org = '-' OR org IS NULL THEN
43 SELECT shortname INTO org FROM evergreen.org_top();
46 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
47 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
49 IF layout.name IS NULL THEN
53 SELECT * INTO transform FROM config.xml_transform WHERE name = layout.transform;
54 xmlns_uri := COALESCE(transform.namespace_uri,xmlns_uri);
57 SELECT XMLAGG( unapi.mmr(i, format, '', includes, org, depth, slimit, soffset, include_xmlns)) INTO tmp_xml FROM UNNEST( id_list ) i;
59 IF layout.title_element IS NOT NULL THEN
60 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.title_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, title;
63 IF layout.description_element IS NOT NULL THEN
64 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.description_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, description;
67 IF layout.creator_element IS NOT NULL THEN
68 EXECUTE 'SELECT XMLCONCAT( XMLELEMENT( name '|| layout.creator_element ||', XMLATTRIBUTES( $1 AS xmlns), $3), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML, creator;
71 IF layout.update_ts_element IS NOT NULL THEN
72 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;
75 IF unapi_url IS NOT NULL THEN
76 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;
79 IF header_xml IS NOT NULL THEN tmp_xml := XMLCONCAT(header_xml,tmp_xml::XML); END IF;
81 element_list := regexp_split_to_array(layout.feed_top,E'\\.');
82 FOR i IN REVERSE ARRAY_UPPER(element_list, 1) .. 1 LOOP
83 EXECUTE 'SELECT XMLELEMENT( name '|| quote_ident(element_list[i]) ||', XMLATTRIBUTES( $1 AS xmlns), $2)' INTO tmp_xml USING xmlns_uri, tmp_xml::XML;
88 $F$ LANGUAGE PLPGSQL STABLE;
90 CREATE TABLE asset.copy_vis_attr_cache (
91 id BIGSERIAL PRIMARY KEY,
92 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
93 target_copy BIGINT NOT NULL,
96 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
97 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
99 ALTER TABLE biblio.record_entry ADD COLUMN vis_attr_vector INT[];
101 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
104 WHEN 'luri_org' THEN 0 -- "b" attr
105 WHEN 'bib_source' THEN 1 -- "b" attr
107 WHEN 'copy_flags' THEN 0 -- "c" attr
108 WHEN 'owning_lib' THEN 1 -- "c" attr
109 WHEN 'circ_lib' THEN 2 -- "c" attr
110 WHEN 'status' THEN 3 -- "c" attr
111 WHEN 'location' THEN 4 -- "c" attr
112 WHEN 'location_group' THEN 5 -- "c" attr
116 /* copy_flags bit positions, LSB-first:
118 0: asset.copy.opac_visible
121 When adding flags, you must update asset.all_visible_flags()
123 Because bib and copy values are stored separately, we can reuse
124 shifts, saving us some space. We could probably take back a bit
125 too, but I'm not sure its worth squeezing that last one out. We'd
126 be left with just 2 slots for copy attrs, rather than 10.
129 $f$ LANGUAGE SQL IMMUTABLE;
131 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
132 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
133 $f$ LANGUAGE SQL IMMUTABLE;
135 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
136 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
137 $f$ LANGUAGE SQL IMMUTABLE;
139 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
141 copy_row asset.copy%ROWTYPE;
142 lgroup_map asset.copy_location_group_map%ROWTYPE;
145 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
147 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
148 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
149 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
150 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
154 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
156 FROM asset.call_number
157 WHERE id = copy_row.call_number;
159 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
160 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
165 $f$ LANGUAGE PLPGSQL;
167 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$
169 bib_row biblio.record_entry%ROWTYPE;
170 cn_row asset.call_number%ROWTYPE;
173 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
175 IF bib_row.source IS NOT NULL THEN
176 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
181 FROM asset.call_number cn
182 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
183 JOIN asset.uri u ON (u.id = m.uri)
184 WHERE cn.record = bib_id
185 AND cn.label = '##URI##'
188 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
193 $f$ LANGUAGE PLPGSQL;
195 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
197 ocn asset.call_number%ROWTYPE;
198 ncn asset.call_number%ROWTYPE;
202 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
203 IF TG_OP = 'INSERT' THEN
204 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
207 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
211 ELSIF TG_OP = 'DELETE' THEN
212 DELETE FROM asset.copy_vis_attr_cache
213 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
219 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
220 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
221 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
222 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
225 asset.calculate_copy_visibility_attribute_set(NEW.id)
227 ELSIF TG_TABLE_NAME = 'record_entry' THEN
228 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
234 -- handle items first, since with circulation activity
235 -- their statuses change frequently
236 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
238 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
239 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
243 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
245 IF OLD.deleted <> NEW.deleted THEN
247 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
249 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
252 asset.calculate_copy_visibility_attribute_set(NEW.id)
257 ELSIF OLD.call_number <> NEW.call_number THEN
258 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
260 IF ncn.record <> ocn.record THEN
261 UPDATE biblio.record_entry
262 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
263 WHERE id = ocn.record;
267 IF OLD.location <> NEW.location OR
268 OLD.status <> NEW.status OR
269 OLD.opac_visible <> NEW.opac_visible OR
270 OLD.circ_lib <> NEW.circ_lib
272 -- any of these could change visibility, but
273 -- we'll save some queries and not try to calculate
274 -- the change directly
275 UPDATE asset.copy_vis_attr_cache
276 SET target_copy = NEW.id,
277 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
278 WHERE target_copy = OLD.id;
282 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
284 IF OLD.record <> NEW.record THEN
285 IF NEW.label = '##URI##' THEN
286 UPDATE biblio.record_entry
287 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
288 WHERE id = OLD.record;
290 UPDATE biblio.record_entry
291 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
292 WHERE id = NEW.record;
295 UPDATE asset.copy_vis_attr_cache
296 SET record = NEW.record,
297 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
298 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
299 AND record = OLD.record;
301 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
302 UPDATE asset.copy_vis_attr_cache
303 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
304 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
305 AND record = NEW.record;
307 IF NEW.label = '##URI##' THEN
308 UPDATE biblio.record_entry
309 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
310 WHERE id = OLD.record;
314 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
316 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
317 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
319 ELSIF OLD.source <> NEW.source THEN
320 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
327 $func$ LANGUAGE PLPGSQL;
330 -- Helper functions for use in constructing searches --
332 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
333 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
334 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
335 $f$ LANGUAGE SQL STABLE;
337 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
338 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
341 $f$ LANGUAGE SQL STABLE;
343 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
344 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
346 WHERE NOT opac_visible;
347 $f$ LANGUAGE SQL STABLE;
349 -- Bib-oriented defaults for search
350 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
351 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
352 FROM config.bib_source
354 $f$ LANGUAGE SQL IMMUTABLE;
356 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
357 SELECT * FROM asset.invisible_orgs('luri_org');
358 $f$ LANGUAGE SQL STABLE;
360 -- Copy-oriented defaults for search
361 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
362 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
363 FROM asset.copy_location_group
364 WHERE NOT opac_visible;
365 $f$ LANGUAGE SQL STABLE;
367 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
368 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
369 FROM asset.copy_location
370 WHERE NOT opac_visible;
371 $f$ LANGUAGE SQL STABLE;
373 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
374 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
375 FROM config.copy_status
376 WHERE NOT opac_visible;
377 $f$ LANGUAGE SQL STABLE;
379 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
380 SELECT * FROM asset.invisible_orgs('owning_lib');
381 $f$ LANGUAGE SQL STABLE;
383 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
384 SELECT * FROM asset.invisible_orgs('circ_lib');
385 $f$ LANGUAGE SQL STABLE;
387 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
389 copy_flags TEXT; -- "c" attr
391 owning_lib TEXT; -- "c" attr
392 circ_lib TEXT; -- "c" attr
393 status TEXT; -- "c" attr
394 location TEXT; -- "c" attr
395 location_group TEXT; -- "c" attr
397 luri_org TEXT; -- "b" attr
398 bib_sources TEXT; -- "b" attr
400 copy_flags := asset.all_visible_flags(); -- Will always have at least one
402 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
404 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
405 status := NULLIF(asset.status_default(),'!()');
406 location := NULLIF(asset.location_default(),'!()');
407 location_group := NULLIF(asset.location_group_default(),'!()');
409 luri_org := NULLIF(asset.luri_org_default(),'!()');
410 bib_sources := NULLIF(asset.bib_source_default(),'()');
413 '('||ARRAY_TO_STRING(
414 ARRAY[luri_org,bib_sources],
417 '('||ARRAY_TO_STRING(
418 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
422 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
424 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)
425 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
428 prepared_query_texts TEXT[];
431 opac_visibility_join TEXT;
432 search_class_join TEXT;
435 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
437 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
438 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
440 visibility_org := NULLIF(visibility_org,-1);
441 IF visibility_org IS NOT NULL THEN
442 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
444 opac_visibility_join := '';
446 opac_visibility_join := '
447 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
448 JOIN vm ON (acvac.vis_attr_vector @@
449 (vm.c_attrs || $$&$$ ||
450 search.calculate_visibility_attribute_test(
452 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
459 opac_visibility_join := '';
462 -- The following determines whether we only provide suggestsons matching
463 -- the user's selected search_class, or whether we show other suggestions
464 -- too. The reason for MIN() is that for search_classes like
465 -- 'title|proper|uniform' you would otherwise get multiple rows. The
466 -- implication is that if title as a class doesn't have restrict,
467 -- nor does the proper field, but the uniform field does, you're going
468 -- to get 'false' for your overall evaluation of 'should we restrict?'
469 -- To invert that, change from MIN() to MAX().
473 MIN(cmc.restrict::INT) AS restrict_class,
474 MIN(cmf.restrict::INT) AS restrict_field
475 FROM metabib.search_class_to_registered_components(search_class)
476 AS _registered (field_class TEXT, field INT)
478 config.metabib_class cmc ON (cmc.name = _registered.field_class)
480 config.metabib_field cmf ON (cmf.id = _registered.field);
482 -- evaluate 'should we restrict?'
483 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
484 search_class_join := '
486 metabib.search_class_to_registered_components($2)
487 AS _registered (field_class TEXT, field INT) ON (
488 (_registered.field IS NULL AND
489 _registered.field_class = cmf.field_class) OR
490 (_registered.field = cmf.id)
494 search_class_join := '
496 metabib.search_class_to_registered_components($2)
497 AS _registered (field_class TEXT, field INT) ON (
498 _registered.field_class = cmc.name
503 RETURN QUERY EXECUTE '
504 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
505 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
514 TS_HEADLINE(value, $7, $3)
515 FROM (SELECT DISTINCT
518 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
519 _registered.field = cmf.id AS restrict,
521 TS_RANK_CD(mbe.index_vector, $1, $6),
524 FROM metabib.browse_entry_def_map mbedm
525 JOIN mbe ON (mbe.id = mbedm.entry)
526 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
527 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
528 ' || search_class_join || '
529 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
531 ' || opac_visibility_join || '
532 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
534 ' -- sic, repeat the order by clause in the outer select too
536 query, search_class, headline_opts,
537 visibility_org, query_limit, normalization, plain_query
541 -- buoyant AND chosen class = match class
542 -- chosen field = match field
549 $f$ LANGUAGE plpgsql ROWS 10;
551 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)
552 RETURNS SETOF metabib.flat_browse_entry_appearance
558 pivot_sort_value TEXT;
559 pivot_sort_fallback TEXT;
560 context_locations INT[];
561 browse_superpage_size INT;
562 results_skipped INT := 0;
566 forward_to_pivot INT;
568 -- First, find the pivot if we were given a browse term but not a pivot.
569 IF pivot_id IS NULL THEN
570 pivot_id := metabib.browse_pivot(search_field, browse_term);
573 SELECT INTO pivot_sort_value, pivot_sort_fallback
574 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
576 -- Bail if we couldn't find a pivot.
577 IF pivot_sort_value IS NULL THEN
581 -- Transform the context_loc_group argument (if any) (logc at the
582 -- TPAC layer) into a form we'll be able to use.
583 IF context_loc_group IS NOT NULL THEN
584 SELECT INTO context_locations ARRAY_AGG(location)
585 FROM asset.copy_location_group_map
586 WHERE lgroup = context_loc_group;
589 -- Get the configured size of browse superpages.
590 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
591 FROM config.global_flag
592 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
594 -- First we're going to search backward from the pivot, then we're going
595 -- to search forward. In each direction, we need two limits. At the
596 -- lesser of the two limits, we delineate the edge of the result set
597 -- we're going to return. At the greater of the two limits, we find the
598 -- pivot value that would represent an offset from the current pivot
599 -- at a distance of one "page" in either direction, where a "page" is a
600 -- result set of the size specified in the "result_limit" argument.
602 -- The two limits in each direction make four derived values in total,
603 -- and we calculate them now.
604 back_limit := CEIL(result_limit::FLOAT / 2);
605 back_to_pivot := result_limit;
606 forward_limit := result_limit / 2;
607 forward_to_pivot := result_limit - 1;
609 -- This is the meat of the SQL query that finds browse entries. We'll
610 -- pass this to a function which uses it with a cursor, so that individual
611 -- rows may be fetched in a loop until some condition is satisfied, without
612 -- waiting for a result set of fixed size to be collected all at once.
617 FROM metabib.browse_entry mbe
619 EXISTS ( -- are there any bibs using this mbe via the requested fields?
621 FROM metabib.browse_entry_def_map mbedm
622 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
623 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
625 FROM metabib.browse_entry_simple_heading_map mbeshm
626 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
627 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
628 ash.atag = map.authority_field
629 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
631 WHERE mbeshm.entry = mbe.id
635 -- This is the variant of the query for browsing backward.
636 back_query := core_query ||
637 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
638 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
640 -- This variant browses forward.
641 forward_query := core_query ||
642 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
643 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
645 -- We now call the function which applies a cursor to the provided
646 -- queries, stopping at the appropriate limits and also giving us
647 -- the next page's pivot.
649 SELECT * FROM metabib.staged_browse(
650 back_query, search_field, context_org, context_locations,
651 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
653 SELECT * FROM metabib.staged_browse(
654 forward_query, search_field, context_org, context_locations,
655 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
656 ) ORDER BY row_number DESC;
659 $f$ LANGUAGE plpgsql ROWS 10;
661 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)
662 RETURNS SETOF metabib.flat_browse_entry_appearance
671 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
672 results_skipped INT := 0;
673 row_counter INT := 0;
678 all_records BIGINT[];
679 all_brecords BIGINT[];
680 all_arecords BIGINT[];
681 superpage_of_records BIGINT[];
687 IF count_up_from_zero THEN
694 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
697 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
698 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
700 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
702 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
703 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
705 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
707 b_tests := b_tests || search.calculate_visibility_attribute_test(
709 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
712 b_tests := b_tests || search.calculate_visibility_attribute_test(
714 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
718 IF context_locations THEN
719 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
720 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
723 OPEN curs NO SCROLL FOR EXECUTE query;
728 IF result_row.pivot_point IS NOT NULL THEN
729 RETURN NEXT result_row;
734 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
735 SELECT INTO all_arecords, result_row.sees, afields
736 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
737 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
738 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
740 FROM metabib.browse_entry_simple_heading_map mbeshm
741 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
742 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
743 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
744 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
745 ash.atag = map.authority_field
746 AND map.metabib_field = ANY(fields)
748 WHERE mbeshm.entry = rec.id;
750 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
751 SELECT INTO all_brecords, result_row.authorities, bfields
752 ARRAY_AGG(DISTINCT source),
753 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
754 ARRAY_AGG(DISTINCT def)
755 FROM metabib.browse_entry_def_map
757 AND def = ANY(fields);
759 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
761 result_row.sources := 0;
762 result_row.asources := 0;
764 -- Bib-linked vis checking
765 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
767 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
768 FROM biblio.record_entry b
769 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
770 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
772 acvac.vis_attr_vector @@ c_tests::query_int
773 OR b.vis_attr_vector @@ b_tests::query_int
776 result_row.accurate := TRUE;
780 -- Authority-linked vis checking
781 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
783 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
784 FROM biblio.record_entry b
785 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
786 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
788 acvac.vis_attr_vector @@ c_tests::query_int
789 OR b.vis_attr_vector @@ b_tests::query_int
792 result_row.aaccurate := TRUE;
796 IF result_row.sources > 0 OR result_row.asources > 0 THEN
798 -- The function that calls this function needs row_number in order
799 -- to correctly order results from two different runs of this
801 result_row.row_number := row_number;
803 -- Now, if row_counter is still less than limit, return a row. If
804 -- not, but it is less than next_pivot_pos, continue on without
805 -- returning actual result rows until we find
806 -- that next pivot, and return it.
808 IF row_counter < result_limit THEN
809 result_row.browse_entry := rec.id;
810 result_row.value := rec.value;
812 RETURN NEXT result_row;
814 result_row.browse_entry := NULL;
815 result_row.authorities := NULL;
816 result_row.fields := NULL;
817 result_row.value := NULL;
818 result_row.sources := NULL;
819 result_row.sees := NULL;
820 result_row.accurate := NULL;
821 result_row.aaccurate := NULL;
822 result_row.pivot_point := rec.id;
824 IF row_counter >= next_pivot_pos THEN
825 RETURN NEXT result_row;
830 IF count_up_from_zero THEN
831 row_number := row_number + 1;
833 row_number := row_number - 1;
836 -- row_counter is different from row_number.
837 -- It simply counts up from zero so that we know when
838 -- we've reached our limit.
839 row_counter := row_counter + 1;
843 $f$ LANGUAGE plpgsql ROWS 10;
845 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
846 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
847 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
848 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
849 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
850 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
851 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
852 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
855 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
858 asset.calculate_copy_visibility_attribute_set(cp.id)
860 JOIN asset.call_number cn ON (cp.call_number = cn.id);
862 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id);
864 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();
865 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();
866 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
867 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
868 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
869 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
870 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
872 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$
877 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;
879 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
881 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
882 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
883 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
887 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
893 asset.copy_vis_attr_cache av
894 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
895 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
899 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
906 $f$ LANGUAGE PLPGSQL;
908 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$
913 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;
915 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
917 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
918 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
919 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
923 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
928 asset.copy_vis_attr_cache av
929 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
930 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
934 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
941 $f$ LANGUAGE PLPGSQL;
943 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$
948 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;
950 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
952 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
953 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
954 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
958 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
964 asset.copy_vis_attr_cache av
965 JOIN asset.copy cp ON (cp.id = av.target_copy)
966 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
967 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
971 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
978 $f$ LANGUAGE PLPGSQL;
980 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$
985 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;
987 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
989 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
990 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
991 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
995 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
1001 asset.copy_vis_attr_cache av
1002 JOIN asset.copy cp ON (cp.id = av.target_copy)
1003 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
1004 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
1008 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1015 $f$ LANGUAGE PLPGSQL;
1017 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
1023 depth INT DEFAULT NULL,
1024 slimit HSTORE DEFAULT NULL,
1025 soffset HSTORE DEFAULT NULL,
1026 include_xmlns BOOL DEFAULT TRUE,
1027 pref_lib INT DEFAULT NULL
1028 ) RETURNS XML AS $F$
1032 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
1033 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
1035 (SELECT XMLAGG(foo.y)
1037 WITH sourcelist AS (
1038 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1),
1039 basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
1040 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY_AGG(aoud.id)) AS mask
1041 FROM aou, LATERAL actor.org_unit_descendants(aou.id, $6) aoud)
1043 FROM aou, circvm, basevm, metabib.metarecord_source_map mmsm
1044 WHERE mmsm.metarecord = $1 AND (
1047 FROM circvm, basevm, asset.copy_vis_attr_cache acvac
1048 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
1049 AND acvac.record = mmsm.source
1051 OR EXISTS (SELECT 1 FROM evergreen.located_uris(source, aou.id, $10) LIMIT 1)
1052 OR EXISTS (SELECT 1 FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = mmsm.source)
1060 cmra.value AS "coded-value",
1061 cmra.aid AS "cvmid",
1071 SELECT DISTINCT aid, attr, value, STRING_AGG(x.id::TEXT, ',') AS source_list
1073 SELECT v.source AS id,
1077 FROM metabib.record_attr_vector_list v
1078 JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) )
1080 JOIN sourcelist ON (x.id = sourcelist.source)
1083 JOIN config.record_attr_definition rad ON (cmra.attr = rad.name)
1098 SELECT DISTINCT aid, attr, value
1100 SELECT v.source AS id,
1104 FROM metabib.record_attr_vector_list v
1105 JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) )
1107 JOIN sourcelist ON (x.id = sourcelist.source)
1109 JOIN config.record_attr_definition rad ON (umra.attr = rad.name)
1115 $F$ LANGUAGE SQL STABLE;
1117 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
1120 depth INT DEFAULT NULL,
1121 slimit HSTORE DEFAULT NULL,
1122 soffset HSTORE DEFAULT NULL,
1123 pref_lib INT DEFAULT NULL,
1124 includes TEXT[] DEFAULT NULL::TEXT[]
1125 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
1126 WITH RECURSIVE ou_depth AS (
1131 FROM actor.org_unit_type aout
1132 INNER JOIN actor.org_unit ou ON ou_type = aout.id
1136 ), descendant_depth AS (
1140 FROM actor.org_unit ou
1141 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1142 JOIN anscestor_depth ad ON (ad.id = ou.id),
1144 WHERE ad.depth = ou_depth.depth
1149 FROM actor.org_unit ou
1150 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1151 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
1152 ), anscestor_depth AS (
1156 FROM actor.org_unit ou
1157 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1163 FROM actor.org_unit ou
1164 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1165 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
1167 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
1170 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
1171 SELECT acn.id, owning_lib.name, acn.label_sortkey,
1172 evergreen.rank_cp(acp),
1174 FROM asset.call_number acn
1175 JOIN asset.copy acp ON (acn.id = acp.call_number)
1176 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
1177 JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
1178 WHERE acn.record = ANY ($1)
1179 AND acn.deleted IS FALSE
1180 AND acp.deleted IS FALSE
1181 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
1183 WITH basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
1184 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY[acp.circ_lib]) AS mask)
1186 FROM basevm, circvm, asset.copy_vis_attr_cache acvac
1187 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
1188 AND acvac.target_copy = acp.id
1189 AND acvac.record = acn.record
1191 GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
1195 CASE WHEN aou.id = $2 THEN -20000 END,
1196 CASE WHEN aou.id = $6 THEN -10000 END,
1197 (SELECT distance - 5000
1198 FROM actor.org_unit_descendants_distance($6) as x
1199 WHERE x.id = aou.id AND $6 IN (
1200 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
1201 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
1204 evergreen.rank_cp(acp)
1207 GROUP BY ua.id, ua.name, ua.label_sortkey
1208 ORDER BY rank, ua.name, ua.label_sortkey
1209 LIMIT ($4 -> 'acn')::INT
1210 OFFSET ($5 -> 'acn')::INT;
1211 $$ LANGUAGE SQL STABLE ROWS 10;