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 OR REPLACE FUNCTION unapi.mmr_mra (
94 depth INT DEFAULT NULL,
95 slimit HSTORE DEFAULT NULL,
96 soffset HSTORE DEFAULT NULL,
97 include_xmlns BOOL DEFAULT TRUE,
98 pref_lib INT DEFAULT NULL
103 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
104 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
106 (SELECT XMLAGG(foo.y)
109 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1),
110 basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
111 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY_AGG(aoud.id)) AS mask
112 FROM aou, LATERAL actor.org_unit_descendants(aou.id, $6) aoud)
114 FROM aou, circvm, basevm, metabib.metarecord_source_map mmsm
115 WHERE mmsm.metarecord = $1 AND (
118 FROM circvm, basevm, asset.copy_vis_attr_cache acvac
119 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
120 AND acvac.record = mmsm.source
122 OR EXISTS (SELECT 1 FROM evergreen.located_uris(source, aou.id, $10) LIMIT 1)
123 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)
131 cmra.value AS "coded-value",
142 SELECT DISTINCT aid, attr, value, STRING_AGG(x.id::TEXT, ',') AS source_list
144 SELECT v.source AS id,
148 FROM metabib.record_attr_vector_list v
149 JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) )
151 JOIN sourcelist ON (x.id = sourcelist.source)
154 JOIN config.record_attr_definition rad ON (cmra.attr = rad.name)
169 SELECT DISTINCT aid, attr, value
171 SELECT v.source AS id,
175 FROM metabib.record_attr_vector_list v
176 JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) )
178 JOIN sourcelist ON (x.id = sourcelist.source)
180 JOIN config.record_attr_definition rad ON (umra.attr = rad.name)
186 $F$ LANGUAGE SQL STABLE;
188 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
191 depth INT DEFAULT NULL,
192 slimit HSTORE DEFAULT NULL,
193 soffset HSTORE DEFAULT NULL,
194 pref_lib INT DEFAULT NULL,
195 includes TEXT[] DEFAULT NULL::TEXT[]
196 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
197 WITH RECURSIVE ou_depth AS (
202 FROM actor.org_unit_type aout
203 INNER JOIN actor.org_unit ou ON ou_type = aout.id
207 ), descendant_depth AS (
211 FROM actor.org_unit ou
212 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
213 JOIN anscestor_depth ad ON (ad.id = ou.id),
215 WHERE ad.depth = ou_depth.depth
220 FROM actor.org_unit ou
221 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
222 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
223 ), anscestor_depth AS (
227 FROM actor.org_unit ou
228 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
234 FROM actor.org_unit ou
235 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
236 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
238 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
241 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
242 SELECT acn.id, owning_lib.name, acn.label_sortkey,
243 evergreen.rank_cp(acp),
245 FROM asset.call_number acn
246 JOIN asset.copy acp ON (acn.id = acp.call_number)
247 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
248 JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
249 WHERE acn.record = ANY ($1)
250 AND acn.deleted IS FALSE
251 AND acp.deleted IS FALSE
252 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
254 WITH basevm AS (SELECT c_attrs FROM asset.patron_default_visibility_mask()),
255 circvm AS (SELECT search.calculate_visibility_attribute_test('circ_lib', ARRAY[acp.circ_lib]) AS mask)
257 FROM basevm, circvm, asset.copy_vis_attr_cache acvac
258 WHERE acvac.vis_attr_vector @@ (basevm.c_attrs || '&' || circvm.mask)::query_int
259 AND acvac.target_copy = acp.id
260 AND acvac.record = acn.record
262 GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
266 CASE WHEN aou.id = $2 THEN -20000 END,
267 CASE WHEN aou.id = $6 THEN -10000 END,
268 (SELECT distance - 5000
269 FROM actor.org_unit_descendants_distance($6) as x
270 WHERE x.id = aou.id AND $6 IN (
271 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
272 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
275 evergreen.rank_cp(acp)
278 GROUP BY ua.id, ua.name, ua.label_sortkey
279 ORDER BY rank, ua.name, ua.label_sortkey
280 LIMIT ($4 -> 'acn')::INT
281 OFFSET ($5 -> 'acn')::INT;
282 $$ LANGUAGE SQL STABLE ROWS 10;
284 CREATE TABLE asset.copy_vis_attr_cache (
285 id BIGSERIAL PRIMARY KEY,
286 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
287 target_copy BIGINT NOT NULL,
288 vis_attr_vector INT[]
290 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
291 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
293 ALTER TABLE biblio.record_entry ADD COLUMN vis_attr_vector INT[];
295 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute ( value INT, attr TEXT ) RETURNS INT AS $f$
298 WHEN 'luri_org' THEN 0 -- "b" attr
299 WHEN 'bib_source' THEN 1 -- "b" attr
301 WHEN 'copy_flags' THEN 0 -- "c" attr
302 WHEN 'owning_lib' THEN 1 -- "c" attr
303 WHEN 'circ_lib' THEN 2 -- "c" attr
304 WHEN 'status' THEN 3 -- "c" attr
305 WHEN 'location' THEN 4 -- "c" attr
306 WHEN 'location_group' THEN 5 -- "c" attr
310 /* copy_flags bit positions, LSB-first:
312 0: asset.copy.opac_visible
315 When adding flags, you must update asset.all_visible_flags()
317 Because bib and copy values are stored separately, we can reuse
318 shifts, saving us some space. We could probably take back a bit
319 too, but I'm not sure its worth squeezing that last one out. We'd
320 be left with just 2 slots for copy attrs, rather than 10.
323 $f$ LANGUAGE SQL IMMUTABLE;
325 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_list ( attr TEXT, value INT[] ) RETURNS INT[] AS $f$
326 SELECT ARRAY_AGG(search.calculate_visibility_attribute(x, $1)) FROM UNNEST($2) AS X;
327 $f$ LANGUAGE SQL IMMUTABLE;
329 CREATE OR REPLACE FUNCTION search.calculate_visibility_attribute_test ( attr TEXT, value INT[], negate BOOL DEFAULT FALSE ) RETURNS TEXT AS $f$
330 SELECT CASE WHEN $3 THEN '!' ELSE '' END || '(' || ARRAY_TO_STRING(search.calculate_visibility_attribute_list($1,$2),'|') || ')';
331 $f$ LANGUAGE SQL IMMUTABLE;
333 CREATE OR REPLACE FUNCTION asset.calculate_copy_visibility_attribute_set ( copy_id BIGINT ) RETURNS INT[] AS $f$
335 copy_row asset.copy%ROWTYPE;
336 lgroup_map asset.copy_location_group_map%ROWTYPE;
339 SELECT * INTO copy_row FROM asset.copy WHERE id = copy_id;
341 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.opac_visible::INT, 'copy_flags');
342 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.circ_lib, 'circ_lib');
343 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.status, 'status');
344 attr_set := attr_set || search.calculate_visibility_attribute(copy_row.location, 'location');
348 search.calculate_visibility_attribute(owning_lib, 'owning_lib')
350 FROM asset.call_number
351 WHERE id = copy_row.call_number;
353 FOR lgroup_map IN SELECT * FROM asset.copy_location_group_map WHERE location = copy_row.location LOOP
354 attr_set := attr_set || search.calculate_visibility_attribute(lgroup_map.lgroup, 'location_group');
359 $f$ LANGUAGE PLPGSQL;
361 CREATE OR REPLACE FUNCTION biblio.calculate_bib_visibility_attribute_set ( bib_id BIGINT ) RETURNS INT[] AS $f$
363 bib_row biblio.record_entry%ROWTYPE;
364 cn_row asset.call_number%ROWTYPE;
367 SELECT * INTO bib_row FROM biblio.record_entry WHERE id = bib_id;
369 IF bib_row.source IS NOT NULL THEN
370 attr_set := attr_set || search.calculate_visibility_attribute(bib_row.source, 'bib_source');
375 FROM asset.call_number cn
376 JOIN asset.uri_call_number_map m ON (cn.id = m.call_number)
377 JOIN asset.uri u ON (u.id = m.uri)
378 WHERE cn.record = bib_id
379 AND cn.label = '##URI##'
382 attr_set := attr_set || search.calculate_visibility_attribute(cn_row.owning_lib, 'luri_org');
387 $f$ LANGUAGE PLPGSQL;
389 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
391 ocn asset.call_number%ROWTYPE;
392 ncn asset.call_number%ROWTYPE;
396 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
397 IF TG_OP = 'INSERT' THEN
398 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
401 asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
405 ELSIF TG_OP = 'DELETE' THEN
406 DELETE FROM asset.copy_vis_attr_cache
407 WHERE record = NEW.peer_record AND target_copy = NEW.target_copy;
413 IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
414 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
415 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
416 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
419 asset.calculate_copy_visibility_attribute_set(NEW.id)
421 ELSIF TG_TABLE_NAME = 'record_entry' THEN
422 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
428 -- handle items first, since with circulation activity
429 -- their statuses change frequently
430 IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above
432 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
433 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
437 SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
439 IF OLD.deleted <> NEW.deleted THEN
441 DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
443 INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
446 asset.calculate_copy_visibility_attribute_set(NEW.id)
451 ELSIF OLD.call_number <> NEW.call_number THEN
452 SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;
454 IF ncn.record <> ocn.record THEN
455 UPDATE biblio.record_entry
456 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(ncn.record)
457 WHERE id = ocn.record;
461 IF OLD.location <> NEW.location OR
462 OLD.status <> NEW.status OR
463 OLD.opac_visible <> NEW.opac_visible OR
464 OLD.circ_lib <> NEW.circ_lib
466 -- any of these could change visibility, but
467 -- we'll save some queries and not try to calculate
468 -- the change directly
469 UPDATE asset.copy_vis_attr_cache
470 SET target_copy = NEW.id,
471 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
472 WHERE target_copy = OLD.id;
476 ELSIF TG_TABLE_NAME = 'call_number' THEN -- Only ON UPDATE. Copy handler will deal with ON INSERT OR DELETE.
478 IF OLD.record <> NEW.record THEN
479 IF NEW.label = '##URI##' THEN
480 UPDATE biblio.record_entry
481 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
482 WHERE id = OLD.record;
484 UPDATE biblio.record_entry
485 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
486 WHERE id = NEW.record;
489 UPDATE asset.copy_vis_attr_cache
490 SET record = NEW.record,
491 vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
492 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
493 AND record = OLD.record;
495 ELSIF OLD.owning_lib <> NEW.owning_lib THEN
496 UPDATE asset.copy_vis_attr_cache
497 SET vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
498 WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
499 AND record = NEW.record;
501 IF NEW.label = '##URI##' THEN
502 UPDATE biblio.record_entry
503 SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
504 WHERE id = OLD.record;
508 ELSIF TG_TABLE_NAME = 'record_entry' THEN -- Only handles ON UPDATE OR DELETE
510 IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
511 DELETE FROM asset.copy_vis_attr_cache WHERE record = OLD.id;
513 ELSIF OLD.source <> NEW.source THEN
514 NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id);
521 $func$ LANGUAGE PLPGSQL;
524 -- Helper functions for use in constructing searches --
526 CREATE OR REPLACE FUNCTION asset.all_visible_flags () RETURNS TEXT AS $f$
527 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(1 << x, 'copy_flags')),'&') || ')'
528 FROM GENERATE_SERIES(0,0) AS x; -- increment as new flags are added.
529 $f$ LANGUAGE SQL STABLE;
531 CREATE OR REPLACE FUNCTION asset.visible_orgs (otype TEXT) RETURNS TEXT AS $f$
532 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
535 $f$ LANGUAGE SQL STABLE;
537 CREATE OR REPLACE FUNCTION asset.invisible_orgs (otype TEXT) RETURNS TEXT AS $f$
538 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, $1)),'|') || ')'
540 WHERE NOT opac_visible;
541 $f$ LANGUAGE SQL STABLE;
543 -- Bib-oriented defaults for search
544 CREATE OR REPLACE FUNCTION asset.bib_source_default () RETURNS TEXT AS $f$
545 SELECT '(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'bib_source')),'|') || ')'
546 FROM config.bib_source
548 $f$ LANGUAGE SQL IMMUTABLE;
550 CREATE OR REPLACE FUNCTION asset.luri_org_default () RETURNS TEXT AS $f$
551 SELECT * FROM asset.invisible_orgs('luri_org');
552 $f$ LANGUAGE SQL STABLE;
554 -- Copy-oriented defaults for search
555 CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$
556 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location_group')),'|') || ')'
557 FROM asset.copy_location_group
558 WHERE NOT opac_visible;
559 $f$ LANGUAGE SQL STABLE;
561 CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$
562 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')'
563 FROM asset.copy_location
564 WHERE NOT opac_visible;
565 $f$ LANGUAGE SQL STABLE;
567 CREATE OR REPLACE FUNCTION asset.status_default () RETURNS TEXT AS $f$
568 SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'status')),'|') || ')'
569 FROM config.copy_status
570 WHERE NOT opac_visible;
571 $f$ LANGUAGE SQL STABLE;
573 CREATE OR REPLACE FUNCTION asset.owning_lib_default () RETURNS TEXT AS $f$
574 SELECT * FROM asset.invisible_orgs('owning_lib');
575 $f$ LANGUAGE SQL STABLE;
577 CREATE OR REPLACE FUNCTION asset.circ_lib_default () RETURNS TEXT AS $f$
578 SELECT * FROM asset.invisible_orgs('circ_lib');
579 $f$ LANGUAGE SQL STABLE;
581 CREATE OR REPLACE FUNCTION asset.patron_default_visibility_mask () RETURNS TABLE (b_attrs TEXT, c_attrs TEXT) AS $f$
583 copy_flags TEXT; -- "c" attr
585 owning_lib TEXT; -- "c" attr
586 circ_lib TEXT; -- "c" attr
587 status TEXT; -- "c" attr
588 location TEXT; -- "c" attr
589 location_group TEXT; -- "c" attr
591 luri_org TEXT; -- "b" attr
592 bib_sources TEXT; -- "b" attr
594 copy_flags := asset.all_visible_flags(); -- Will always have at least one
596 owning_lib := NULLIF(asset.owning_lib_default(),'!()');
598 circ_lib := NULLIF(asset.circ_lib_default(),'!()');
599 status := NULLIF(asset.status_default(),'!()');
600 location := NULLIF(asset.location_default(),'!()');
601 location_group := NULLIF(asset.location_group_default(),'!()');
603 luri_org := NULLIF(asset.luri_org_default(),'!()');
604 bib_sources := NULLIF(asset.bib_source_default(),'()');
607 '('||ARRAY_TO_STRING(
608 ARRAY[luri_org,bib_sources],
611 '('||ARRAY_TO_STRING(
612 ARRAY[copy_flags,owning_lib,circ_lib,status,location,location_group]::TEXT[],
616 $f$ LANGUAGE PLPGSQL STABLE ROWS 1;
618 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)
619 RETURNS TABLE(value text, field integer, buoyant_and_class_match boolean, field_match boolean, field_weight integer, rank real, buoyant boolean, match text)
622 prepared_query_texts TEXT[];
625 opac_visibility_join TEXT;
626 search_class_join TEXT;
629 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
631 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
632 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
634 visibility_org := NULLIF(visibility_org,-1);
635 IF visibility_org IS NOT NULL THEN
636 PERFORM FROM actor.org_unit WHERE id = visibility_org AND parent_ou IS NULL;
638 opac_visibility_join := '';
640 opac_visibility_join := '
641 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = x.source)
642 JOIN vm ON (acvac.vis_attr_vector @@
643 (vm.c_attrs || $$&$$ ||
644 search.calculate_visibility_attribute_test(
646 (SELECT ARRAY_AGG(id) FROM actor.org_unit_descendants($4))
653 opac_visibility_join := '';
656 -- The following determines whether we only provide suggestsons matching
657 -- the user's selected search_class, or whether we show other suggestions
658 -- too. The reason for MIN() is that for search_classes like
659 -- 'title|proper|uniform' you would otherwise get multiple rows. The
660 -- implication is that if title as a class doesn't have restrict,
661 -- nor does the proper field, but the uniform field does, you're going
662 -- to get 'false' for your overall evaluation of 'should we restrict?'
663 -- To invert that, change from MIN() to MAX().
667 MIN(cmc.restrict::INT) AS restrict_class,
668 MIN(cmf.restrict::INT) AS restrict_field
669 FROM metabib.search_class_to_registered_components(search_class)
670 AS _registered (field_class TEXT, field INT)
672 config.metabib_class cmc ON (cmc.name = _registered.field_class)
674 config.metabib_field cmf ON (cmf.id = _registered.field);
676 -- evaluate 'should we restrict?'
677 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
678 search_class_join := '
680 metabib.search_class_to_registered_components($2)
681 AS _registered (field_class TEXT, field INT) ON (
682 (_registered.field IS NULL AND
683 _registered.field_class = cmf.field_class) OR
684 (_registered.field = cmf.id)
688 search_class_join := '
690 metabib.search_class_to_registered_components($2)
691 AS _registered (field_class TEXT, field INT) ON (
692 _registered.field_class = cmc.name
697 RETURN QUERY EXECUTE '
698 WITH vm AS ( SELECT * FROM asset.patron_default_visibility_mask() ),
699 mbe AS (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000)
708 TS_HEADLINE(value, $7, $3)
709 FROM (SELECT DISTINCT
712 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
713 _registered.field = cmf.id AS restrict,
715 TS_RANK_CD(mbe.index_vector, $1, $6),
718 FROM metabib.browse_entry_def_map mbedm
719 JOIN mbe ON (mbe.id = mbedm.entry)
720 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
721 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
722 ' || search_class_join || '
723 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
725 ' || opac_visibility_join || '
726 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
728 ' -- sic, repeat the order by clause in the outer select too
730 query, search_class, headline_opts,
731 visibility_org, query_limit, normalization, plain_query
735 -- buoyant AND chosen class = match class
736 -- chosen field = match field
743 $f$ LANGUAGE plpgsql ROWS 10;
745 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)
746 RETURNS SETOF metabib.flat_browse_entry_appearance
752 pivot_sort_value TEXT;
753 pivot_sort_fallback TEXT;
754 context_locations INT[];
755 browse_superpage_size INT;
756 results_skipped INT := 0;
760 forward_to_pivot INT;
762 -- First, find the pivot if we were given a browse term but not a pivot.
763 IF pivot_id IS NULL THEN
764 pivot_id := metabib.browse_pivot(search_field, browse_term);
767 SELECT INTO pivot_sort_value, pivot_sort_fallback
768 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
770 -- Bail if we couldn't find a pivot.
771 IF pivot_sort_value IS NULL THEN
775 -- Transform the context_loc_group argument (if any) (logc at the
776 -- TPAC layer) into a form we'll be able to use.
777 IF context_loc_group IS NOT NULL THEN
778 SELECT INTO context_locations ARRAY_AGG(location)
779 FROM asset.copy_location_group_map
780 WHERE lgroup = context_loc_group;
783 -- Get the configured size of browse superpages.
784 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
785 FROM config.global_flag
786 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
788 -- First we're going to search backward from the pivot, then we're going
789 -- to search forward. In each direction, we need two limits. At the
790 -- lesser of the two limits, we delineate the edge of the result set
791 -- we're going to return. At the greater of the two limits, we find the
792 -- pivot value that would represent an offset from the current pivot
793 -- at a distance of one "page" in either direction, where a "page" is a
794 -- result set of the size specified in the "result_limit" argument.
796 -- The two limits in each direction make four derived values in total,
797 -- and we calculate them now.
798 back_limit := CEIL(result_limit::FLOAT / 2);
799 back_to_pivot := result_limit;
800 forward_limit := result_limit / 2;
801 forward_to_pivot := result_limit - 1;
803 -- This is the meat of the SQL query that finds browse entries. We'll
804 -- pass this to a function which uses it with a cursor, so that individual
805 -- rows may be fetched in a loop until some condition is satisfied, without
806 -- waiting for a result set of fixed size to be collected all at once.
811 FROM metabib.browse_entry mbe
813 EXISTS ( -- are there any bibs using this mbe via the requested fields?
815 FROM metabib.browse_entry_def_map mbedm
816 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
817 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
819 FROM metabib.browse_entry_simple_heading_map mbeshm
820 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
821 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
822 ash.atag = map.authority_field
823 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
825 WHERE mbeshm.entry = mbe.id
829 -- This is the variant of the query for browsing backward.
830 back_query := core_query ||
831 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
832 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
834 -- This variant browses forward.
835 forward_query := core_query ||
836 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
837 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
839 -- We now call the function which applies a cursor to the provided
840 -- queries, stopping at the appropriate limits and also giving us
841 -- the next page's pivot.
843 SELECT * FROM metabib.staged_browse(
844 back_query, search_field, context_org, context_locations,
845 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
847 SELECT * FROM metabib.staged_browse(
848 forward_query, search_field, context_org, context_locations,
849 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
850 ) ORDER BY row_number DESC;
853 $f$ LANGUAGE plpgsql ROWS 10;
855 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)
856 RETURNS SETOF metabib.flat_browse_entry_appearance
865 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
866 results_skipped INT := 0;
867 row_counter INT := 0;
872 all_records BIGINT[];
873 all_brecords BIGINT[];
874 all_arecords BIGINT[];
875 superpage_of_records BIGINT[];
881 IF count_up_from_zero THEN
888 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
891 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
892 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
894 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
896 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
897 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
899 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
901 b_tests := b_tests || search.calculate_visibility_attribute_test(
903 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
906 b_tests := b_tests || search.calculate_visibility_attribute_test(
908 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
912 IF context_locations THEN
913 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
914 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
917 OPEN curs NO SCROLL FOR EXECUTE query;
922 IF result_row.pivot_point IS NOT NULL THEN
923 RETURN NEXT result_row;
928 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
929 SELECT INTO all_arecords, result_row.sees, afields
930 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
931 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
932 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
934 FROM metabib.browse_entry_simple_heading_map mbeshm
935 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
936 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
937 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
938 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
939 ash.atag = map.authority_field
940 AND map.metabib_field = ANY(fields)
942 WHERE mbeshm.entry = rec.id;
944 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
945 SELECT INTO all_brecords, result_row.authorities, bfields
946 ARRAY_AGG(DISTINCT source),
947 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
948 ARRAY_AGG(DISTINCT def)
949 FROM metabib.browse_entry_def_map
951 AND def = ANY(fields);
953 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
955 result_row.sources := 0;
956 result_row.asources := 0;
958 -- Bib-linked vis checking
959 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
961 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
962 FROM biblio.record_entry b
963 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
964 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
966 acvac.vis_attr_vector @@ c_tests::query_int
967 OR b.vis_attr_vector @@ b_tests::query_int
970 result_row.accurate := TRUE;
974 -- Authority-linked vis checking
975 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
977 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
978 FROM biblio.record_entry b
979 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
980 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
982 acvac.vis_attr_vector @@ c_tests::query_int
983 OR b.vis_attr_vector @@ b_tests::query_int
986 result_row.aaccurate := TRUE;
990 IF result_row.sources > 0 OR result_row.asources > 0 THEN
992 -- The function that calls this function needs row_number in order
993 -- to correctly order results from two different runs of this
995 result_row.row_number := row_number;
997 -- Now, if row_counter is still less than limit, return a row. If
998 -- not, but it is less than next_pivot_pos, continue on without
999 -- returning actual result rows until we find
1000 -- that next pivot, and return it.
1002 IF row_counter < result_limit THEN
1003 result_row.browse_entry := rec.id;
1004 result_row.value := rec.value;
1006 RETURN NEXT result_row;
1008 result_row.browse_entry := NULL;
1009 result_row.authorities := NULL;
1010 result_row.fields := NULL;
1011 result_row.value := NULL;
1012 result_row.sources := NULL;
1013 result_row.sees := NULL;
1014 result_row.accurate := NULL;
1015 result_row.aaccurate := NULL;
1016 result_row.pivot_point := rec.id;
1018 IF row_counter >= next_pivot_pos THEN
1019 RETURN NEXT result_row;
1024 IF count_up_from_zero THEN
1025 row_number := row_number + 1;
1027 row_number := row_number - 1;
1030 -- row_counter is different from row_number.
1031 -- It simply counts up from zero so that we know when
1032 -- we've reached our limit.
1033 row_counter := row_counter + 1;
1037 $f$ LANGUAGE plpgsql ROWS 10;
1039 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map;
1040 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry;
1041 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy;
1042 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.call_number;
1043 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON asset.copy_location;
1044 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON serial.unit;
1045 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON config.copy_status;
1046 DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON actor.org_unit;
1048 -- Upgrade the data!
1049 INSERT INTO asset.copy_vis_attr_cache (target_copy, record, vis_attr_vector)
1052 asset.calculate_copy_visibility_attribute_set(cp.id)
1054 JOIN asset.call_number cn ON (cp.call_number = cn.id);
1056 UPDATE biblio.record_entry SET vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(id);
1058 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();
1059 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();
1060 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER UPDATE ON asset.call_number FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
1061 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
1062 CREATE TRIGGER z_opac_vis_mat_view_del_tgr BEFORE DELETE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
1063 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON asset.copy FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
1064 CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility();
1066 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$
1071 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;
1073 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
1075 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
1076 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
1077 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
1081 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
1087 asset.copy_vis_attr_cache av
1088 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
1089 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
1093 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1100 $f$ LANGUAGE PLPGSQL;
1102 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$
1107 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;
1109 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
1111 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
1112 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
1113 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
1117 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
1122 asset.copy_vis_attr_cache av
1123 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
1124 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
1128 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1135 $f$ LANGUAGE PLPGSQL;
1137 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$
1142 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;
1144 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
1146 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
1147 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
1148 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
1152 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
1158 asset.copy_vis_attr_cache av
1159 JOIN asset.copy cp ON (cp.id = av.target_copy)
1160 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
1161 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
1165 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1172 $f$ LANGUAGE PLPGSQL;
1174 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$
1179 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;
1181 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
1183 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
1184 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
1185 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
1189 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
1195 asset.copy_vis_attr_cache av
1196 JOIN asset.copy cp ON (cp.id = av.target_copy)
1197 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
1198 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
1202 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
1209 $f$ LANGUAGE PLPGSQL;