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 ALTER TYPE search.search_result ADD ATTRIBUTE badges TEXT, ADD ATTRIBUTE popularity NUMERIC;
22 CREATE OR REPLACE FUNCTION search.query_parser_fts (
28 param_locations INT[],
35 param_pref_ou INT DEFAULT NULL
36 ) RETURNS SETOF search.search_result AS $func$
39 current_res search.search_result%ROWTYPE;
40 search_org_list INT[];
50 core_cursor REFCURSOR;
55 deleted_count INT := 0;
56 visible_count INT := 0;
57 excluded_count INT := 0;
62 check_limit := COALESCE( param_check, 1000 );
63 core_limit := COALESCE( param_limit, 25000 );
64 core_offset := COALESCE( param_offset, 0 );
66 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
68 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
70 IF param_search_ou > 0 THEN
71 IF param_depth IS NOT NULL THEN
72 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
74 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
78 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
80 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
83 ELSIF param_search_ou < 0 THEN
84 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
86 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
89 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
91 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
94 luri_org_list := luri_org_list || tmp_int_list;
97 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
99 ELSIF param_search_ou = 0 THEN
100 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
103 IF param_pref_ou IS NOT NULL THEN
105 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
107 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
110 luri_org_list := luri_org_list || tmp_int_list;
113 OPEN core_cursor FOR EXECUTE param_query;
117 FETCH core_cursor INTO core_result;
119 EXIT WHEN total_count >= core_limit;
121 total_count := total_count + 1;
123 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
125 check_count := check_count + 1;
127 IF NOT deleted_search THEN
129 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
131 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
132 deleted_count := deleted_count + 1;
137 FROM biblio.record_entry b
138 JOIN config.bib_source s ON (b.source = s.id)
140 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
143 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
144 visible_count := visible_count + 1;
146 current_res.id = core_result.id;
147 current_res.rel = core_result.rel;
148 current_res.badges = core_result.badges;
149 current_res.popularity = core_result.popularity;
153 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
157 current_res.record = core_result.records[1];
159 current_res.record = NULL;
162 RETURN NEXT current_res;
168 FROM asset.call_number cn
169 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
170 JOIN asset.uri uri ON (map.uri = uri.id)
172 AND cn.label = '##URI##'
174 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
175 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
176 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
180 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
181 visible_count := visible_count + 1;
183 current_res.id = core_result.id;
184 current_res.rel = core_result.rel;
185 current_res.badges = core_result.badges;
186 current_res.popularity = core_result.popularity;
190 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
194 current_res.record = core_result.records[1];
196 current_res.record = NULL;
199 RETURN NEXT current_res;
204 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
207 FROM asset.call_number cn
208 JOIN asset.copy cp ON (cp.call_number = cn.id)
211 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
212 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
213 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
218 FROM biblio.peer_bib_copy_map pr
219 JOIN asset.copy cp ON (cp.id = pr.target_copy)
221 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
222 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
223 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
227 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
228 excluded_count := excluded_count + 1;
235 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
238 FROM asset.call_number cn
239 JOIN asset.copy cp ON (cp.call_number = cn.id)
242 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
243 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
244 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
249 FROM biblio.peer_bib_copy_map pr
250 JOIN asset.copy cp ON (cp.id = pr.target_copy)
252 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
253 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
254 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
258 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
259 excluded_count := excluded_count + 1;
266 IF staff IS NULL OR NOT staff THEN
269 FROM asset.opac_visible_copies
270 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
271 AND record IN ( SELECT * FROM unnest( core_result.records ) )
276 FROM biblio.peer_bib_copy_map pr
277 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
278 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
279 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
284 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
285 excluded_count := excluded_count + 1;
293 FROM asset.call_number cn
294 JOIN asset.copy cp ON (cp.call_number = cn.id)
297 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
298 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
304 FROM biblio.peer_bib_copy_map pr
305 JOIN asset.copy cp ON (cp.id = pr.target_copy)
307 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
308 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
314 FROM asset.call_number cn
315 JOIN asset.copy cp ON (cp.call_number = cn.id)
316 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
321 -- Recheck Located URI visibility in the case of no "foreign" copies
323 FROM asset.call_number cn
324 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
325 JOIN asset.uri uri ON (map.uri = uri.id)
327 AND cn.label = '##URI##'
329 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
330 AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
334 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
335 excluded_count := excluded_count + 1;
339 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
340 excluded_count := excluded_count + 1;
351 visible_count := visible_count + 1;
353 current_res.id = core_result.id;
354 current_res.rel = core_result.rel;
355 current_res.badges = core_result.badges;
356 current_res.popularity = core_result.popularity;
360 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
364 current_res.record = core_result.records[1];
366 current_res.record = NULL;
369 RETURN NEXT current_res;
371 IF visible_count % 1000 = 0 THEN
372 -- RAISE NOTICE ' % visible so far ... ', visible_count;
377 current_res.id = NULL;
378 current_res.rel = NULL;
379 current_res.record = NULL;
380 current_res.badges = NULL;
381 current_res.popularity = NULL;
382 current_res.total = total_count;
383 current_res.checked = check_count;
384 current_res.deleted = deleted_count;
385 current_res.visible = visible_count;
386 current_res.excluded = excluded_count;
390 RETURN NEXT current_res;
393 $func$ LANGUAGE PLPGSQL;