2 * Copyright (C) 2016 Equinox Software, Inc.
3 * Mike Rylander <miker@esilibrary.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
20 SELECT evergreen.upgrade_deps_block_check('0984', :eg_version);
22 ALTER TYPE search.search_result ADD ATTRIBUTE badges TEXT, ADD ATTRIBUTE popularity NUMERIC;
24 CREATE OR REPLACE FUNCTION search.query_parser_fts (
30 param_locations INT[],
37 param_pref_ou INT DEFAULT NULL
38 ) RETURNS SETOF search.search_result AS $func$
41 current_res search.search_result%ROWTYPE;
42 search_org_list INT[];
52 core_cursor REFCURSOR;
57 deleted_count INT := 0;
58 visible_count INT := 0;
59 excluded_count INT := 0;
64 check_limit := COALESCE( param_check, 1000 );
65 core_limit := COALESCE( param_limit, 25000 );
66 core_offset := COALESCE( param_offset, 0 );
68 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
70 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
72 IF param_search_ou > 0 THEN
73 IF param_depth IS NOT NULL THEN
74 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
76 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
80 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
82 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
85 ELSIF param_search_ou < 0 THEN
86 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
88 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
91 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
93 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
96 luri_org_list := luri_org_list || tmp_int_list;
99 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
101 ELSIF param_search_ou = 0 THEN
102 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
105 IF param_pref_ou IS NOT NULL THEN
107 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
109 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
112 luri_org_list := luri_org_list || tmp_int_list;
115 OPEN core_cursor FOR EXECUTE param_query;
119 FETCH core_cursor INTO core_result;
121 EXIT WHEN total_count >= core_limit;
123 total_count := total_count + 1;
125 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
127 check_count := check_count + 1;
129 IF NOT deleted_search THEN
131 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
133 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
134 deleted_count := deleted_count + 1;
139 FROM biblio.record_entry b
140 JOIN config.bib_source s ON (b.source = s.id)
142 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
145 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
146 visible_count := visible_count + 1;
148 current_res.id = core_result.id;
149 current_res.rel = core_result.rel;
150 current_res.badges = core_result.badges;
151 current_res.popularity = core_result.popularity;
155 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
159 current_res.record = core_result.records[1];
161 current_res.record = NULL;
164 RETURN NEXT current_res;
170 FROM asset.call_number cn
171 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
172 JOIN asset.uri uri ON (map.uri = uri.id)
174 AND cn.label = '##URI##'
176 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
177 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
178 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
182 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
183 visible_count := visible_count + 1;
185 current_res.id = core_result.id;
186 current_res.rel = core_result.rel;
187 current_res.badges = core_result.badges;
188 current_res.popularity = core_result.popularity;
192 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
196 current_res.record = core_result.records[1];
198 current_res.record = NULL;
201 RETURN NEXT current_res;
206 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
209 FROM asset.call_number cn
210 JOIN asset.copy cp ON (cp.call_number = cn.id)
213 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
214 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
215 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
220 FROM biblio.peer_bib_copy_map pr
221 JOIN asset.copy cp ON (cp.id = pr.target_copy)
223 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
224 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
225 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
229 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
230 excluded_count := excluded_count + 1;
237 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
240 FROM asset.call_number cn
241 JOIN asset.copy cp ON (cp.call_number = cn.id)
244 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
245 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
246 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
251 FROM biblio.peer_bib_copy_map pr
252 JOIN asset.copy cp ON (cp.id = pr.target_copy)
254 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
255 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
256 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
260 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
261 excluded_count := excluded_count + 1;
268 IF staff IS NULL OR NOT staff THEN
271 FROM asset.opac_visible_copies
272 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
273 AND record IN ( SELECT * FROM unnest( core_result.records ) )
278 FROM biblio.peer_bib_copy_map pr
279 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
280 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
281 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
286 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
287 excluded_count := excluded_count + 1;
295 FROM asset.call_number cn
296 JOIN asset.copy cp ON (cp.call_number = cn.id)
299 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
300 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
306 FROM biblio.peer_bib_copy_map pr
307 JOIN asset.copy cp ON (cp.id = pr.target_copy)
309 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
310 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
316 FROM asset.call_number cn
317 JOIN asset.copy cp ON (cp.call_number = cn.id)
318 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
323 -- Recheck Located URI visibility in the case of no "foreign" copies
325 FROM asset.call_number cn
326 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
327 JOIN asset.uri uri ON (map.uri = uri.id)
329 AND cn.label = '##URI##'
331 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
332 AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
336 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
337 excluded_count := excluded_count + 1;
341 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
342 excluded_count := excluded_count + 1;
353 visible_count := visible_count + 1;
355 current_res.id = core_result.id;
356 current_res.rel = core_result.rel;
357 current_res.badges = core_result.badges;
358 current_res.popularity = core_result.popularity;
362 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
366 current_res.record = core_result.records[1];
368 current_res.record = NULL;
371 RETURN NEXT current_res;
373 IF visible_count % 1000 = 0 THEN
374 -- RAISE NOTICE ' % visible so far ... ', visible_count;
379 current_res.id = NULL;
380 current_res.rel = NULL;
381 current_res.record = NULL;
382 current_res.badges = NULL;
383 current_res.popularity = NULL;
384 current_res.total = total_count;
385 current_res.checked = check_count;
386 current_res.deleted = deleted_count;
387 current_res.visible = visible_count;
388 current_res.excluded = excluded_count;
392 RETURN NEXT current_res;
395 $func$ LANGUAGE PLPGSQL;
397 CREATE OR REPLACE FUNCTION metabib.staged_browse(
401 context_locations INT[],
403 browse_superpage_size INT,
404 count_up_from_zero BOOL, -- if false, count down from -1
407 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
415 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
416 results_skipped INT := 0;
417 row_counter INT := 0;
422 all_records BIGINT[];
423 all_brecords BIGINT[];
424 all_arecords BIGINT[];
425 superpage_of_records BIGINT[];
428 IF count_up_from_zero THEN
434 OPEN curs FOR EXECUTE query;
439 IF result_row.pivot_point IS NOT NULL THEN
440 RETURN NEXT result_row;
446 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
447 SELECT INTO all_arecords, result_row.sees, afields
448 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
449 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
450 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
452 FROM metabib.browse_entry_simple_heading_map mbeshm
453 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
454 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
455 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
456 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
457 ash.atag = map.authority_field
458 AND map.metabib_field = ANY(fields)
460 WHERE mbeshm.entry = rec.id;
463 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
464 SELECT INTO all_brecords, result_row.authorities, bfields
465 ARRAY_AGG(DISTINCT source),
466 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
467 ARRAY_AGG(DISTINCT def)
468 FROM metabib.browse_entry_def_map
470 AND def = ANY(fields);
472 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
474 result_row.sources := 0;
475 result_row.asources := 0;
477 -- Bib-linked vis checking
478 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
480 full_end := ARRAY_LENGTH(all_brecords, 1);
481 superpage_size := COALESCE(browse_superpage_size, full_end);
483 slice_end := superpage_size;
485 WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
486 superpage_of_records := all_brecords[slice_start:slice_end];
488 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
489 'NULL AS badges, NULL::NUMERIC AS popularity, ' ||
490 '1::NUMERIC AS rel FROM (SELECT UNNEST(' ||
491 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
493 -- We use search.query_parser_fts() for visibility testing.
494 -- We're calling it once per browse-superpage worth of records
495 -- out of the set of records related to a given mbe, until we've
496 -- either exhausted that set of records or found at least 1
499 SELECT INTO result_row.sources visible
500 FROM search.query_parser_fts(
501 context_org, NULL, qpfts_query, NULL,
502 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
504 WHERE qpfts.rel IS NULL;
506 slice_start := slice_start + superpage_size;
507 slice_end := slice_end + superpage_size;
510 -- Accurate? Well, probably.
511 result_row.accurate := browse_superpage_size IS NULL OR
512 browse_superpage_size >= full_end;
516 -- Authority-linked vis checking
517 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
519 full_end := ARRAY_LENGTH(all_arecords, 1);
520 superpage_size := COALESCE(browse_superpage_size, full_end);
522 slice_end := superpage_size;
524 WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
525 superpage_of_records := all_arecords[slice_start:slice_end];
527 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
528 'NULL AS badges, NULL::NUMERIC AS popularity, ' ||
529 '1::NUMERIC AS rel FROM (SELECT UNNEST(' ||
530 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
532 -- We use search.query_parser_fts() for visibility testing.
533 -- We're calling it once per browse-superpage worth of records
534 -- out of the set of records related to a given mbe, via
535 -- authority until we've either exhausted that set of records
536 -- or found at least 1 visible record.
538 SELECT INTO result_row.asources visible
539 FROM search.query_parser_fts(
540 context_org, NULL, qpfts_query, NULL,
541 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
543 WHERE qpfts.rel IS NULL;
545 slice_start := slice_start + superpage_size;
546 slice_end := slice_end + superpage_size;
550 -- Accurate? Well, probably.
551 result_row.aaccurate := browse_superpage_size IS NULL OR
552 browse_superpage_size >= full_end;
556 IF result_row.sources > 0 OR result_row.asources > 0 THEN
558 -- The function that calls this function needs row_number in order
559 -- to correctly order results from two different runs of this
561 result_row.row_number := row_number;
563 -- Now, if row_counter is still less than limit, return a row. If
564 -- not, but it is less than next_pivot_pos, continue on without
565 -- returning actual result rows until we find
566 -- that next pivot, and return it.
568 IF row_counter < result_limit THEN
569 result_row.browse_entry := rec.id;
570 result_row.value := rec.value;
572 RETURN NEXT result_row;
574 result_row.browse_entry := NULL;
575 result_row.authorities := NULL;
576 result_row.fields := NULL;
577 result_row.value := NULL;
578 result_row.sources := NULL;
579 result_row.sees := NULL;
580 result_row.accurate := NULL;
581 result_row.aaccurate := NULL;
582 result_row.pivot_point := rec.id;
584 IF row_counter >= next_pivot_pos THEN
585 RETURN NEXT result_row;
590 IF count_up_from_zero THEN
591 row_number := row_number + 1;
593 row_number := row_number - 1;
596 -- row_counter is different from row_number.
597 -- It simply counts up from zero so that we know when
598 -- we've reached our limit.
599 row_counter := row_counter + 1;
603 $p$ LANGUAGE PLPGSQL;