1 -- Evergreen DB patch 0631.schema.located_uri_visiblity_fix.sql
4 -- check whether patch can be applied
5 SELECT evergreen.upgrade_deps_block_check('0631', :eg_version);
7 CREATE OR REPLACE FUNCTION search.query_parser_fts (
13 param_locations INT[],
20 ) RETURNS SETOF search.search_result AS $func$
23 current_res search.search_result%ROWTYPE;
24 search_org_list INT[];
34 core_cursor REFCURSOR;
39 deleted_count INT := 0;
40 visible_count INT := 0;
41 excluded_count INT := 0;
45 check_limit := COALESCE( param_check, 1000 );
46 core_limit := COALESCE( param_limit, 25000 );
47 core_offset := COALESCE( param_offset, 0 );
49 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
51 IF param_search_ou > 0 THEN
52 IF param_depth IS NOT NULL THEN
53 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
55 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
58 SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
60 ELSIF param_search_ou < 0 THEN
61 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
63 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
64 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
65 luri_org_list := luri_org_list || tmp_int_list;
68 SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
70 ELSIF param_search_ou = 0 THEN
71 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
74 OPEN core_cursor FOR EXECUTE param_query;
78 FETCH core_cursor INTO core_result;
80 EXIT WHEN total_count >= core_limit;
82 total_count := total_count + 1;
84 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
86 check_count := check_count + 1;
88 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
90 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
91 deleted_count := deleted_count + 1;
96 FROM biblio.record_entry b
97 JOIN config.bib_source s ON (b.source = s.id)
99 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
102 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
103 visible_count := visible_count + 1;
105 current_res.id = core_result.id;
106 current_res.rel = core_result.rel;
110 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
114 current_res.record = core_result.records[1];
116 current_res.record = NULL;
119 RETURN NEXT current_res;
125 FROM asset.call_number cn
126 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
127 JOIN asset.uri uri ON (map.uri = uri.id)
129 AND cn.label = '##URI##'
131 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
132 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
133 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
137 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
138 visible_count := visible_count + 1;
140 current_res.id = core_result.id;
141 current_res.rel = core_result.rel;
145 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
149 current_res.record = core_result.records[1];
151 current_res.record = NULL;
154 RETURN NEXT current_res;
159 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
162 FROM asset.call_number cn
163 JOIN asset.copy cp ON (cp.call_number = cn.id)
166 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
167 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
168 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
173 FROM biblio.peer_bib_copy_map pr
174 JOIN asset.copy cp ON (cp.id = pr.target_copy)
176 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
177 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
178 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
182 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
183 excluded_count := excluded_count + 1;
190 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
193 FROM asset.call_number cn
194 JOIN asset.copy cp ON (cp.call_number = cn.id)
197 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
198 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
199 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
204 FROM biblio.peer_bib_copy_map pr
205 JOIN asset.copy cp ON (cp.id = pr.target_copy)
207 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
208 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
209 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
213 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
214 excluded_count := excluded_count + 1;
221 IF staff IS NULL OR NOT staff THEN
224 FROM asset.opac_visible_copies
225 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
226 AND record IN ( SELECT * FROM unnest( core_result.records ) )
231 FROM biblio.peer_bib_copy_map pr
232 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
233 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
234 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
239 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
240 excluded_count := excluded_count + 1;
248 FROM asset.call_number cn
249 JOIN asset.copy cp ON (cp.call_number = cn.id)
252 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
253 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
259 FROM biblio.peer_bib_copy_map pr
260 JOIN asset.copy cp ON (cp.id = pr.target_copy)
262 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
263 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
269 FROM asset.call_number cn
270 JOIN asset.copy cp ON (cp.call_number = cn.id)
271 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
276 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
277 excluded_count := excluded_count + 1;
286 visible_count := visible_count + 1;
288 current_res.id = core_result.id;
289 current_res.rel = core_result.rel;
293 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
297 current_res.record = core_result.records[1];
299 current_res.record = NULL;
302 RETURN NEXT current_res;
304 IF visible_count % 1000 = 0 THEN
305 -- RAISE NOTICE ' % visible so far ... ', visible_count;
310 current_res.id = NULL;
311 current_res.rel = NULL;
312 current_res.record = NULL;
313 current_res.total = total_count;
314 current_res.checked = check_count;
315 current_res.deleted = deleted_count;
316 current_res.visible = visible_count;
317 current_res.excluded = excluded_count;
321 RETURN NEXT current_res;
324 $func$ LANGUAGE PLPGSQL;