1 --Upgrade Script for 2.6.1 to 2.6.2
2 \set eg_version '''2.6.2'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.6.2', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0882', :eg_version);
8 CREATE OR REPLACE FUNCTION search.query_parser_fts (
14 param_locations INT[],
21 param_pref_ou INT DEFAULT NULL
22 ) RETURNS SETOF search.search_result AS $func$
25 current_res search.search_result%ROWTYPE;
26 search_org_list INT[];
36 core_cursor REFCURSOR;
41 deleted_count INT := 0;
42 visible_count INT := 0;
43 excluded_count INT := 0;
48 check_limit := COALESCE( param_check, 1000 );
49 core_limit := COALESCE( param_limit, 25000 );
50 core_offset := COALESCE( param_offset, 0 );
52 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
54 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
56 IF param_search_ou > 0 THEN
57 IF param_depth IS NOT NULL THEN
58 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
60 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
64 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
66 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
69 ELSIF param_search_ou < 0 THEN
70 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
72 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
75 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
77 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
80 luri_org_list := luri_org_list || tmp_int_list;
83 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
85 ELSIF param_search_ou = 0 THEN
86 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
89 IF param_pref_ou IS NOT NULL THEN
91 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
93 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
96 luri_org_list := luri_org_list || tmp_int_list;
99 OPEN core_cursor FOR EXECUTE param_query;
103 FETCH core_cursor INTO core_result;
105 EXIT WHEN total_count >= core_limit;
107 total_count := total_count + 1;
109 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
111 check_count := check_count + 1;
113 IF NOT deleted_search THEN
115 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
117 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
118 deleted_count := deleted_count + 1;
123 FROM biblio.record_entry b
124 JOIN config.bib_source s ON (b.source = s.id)
126 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
129 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
130 visible_count := visible_count + 1;
132 current_res.id = core_result.id;
133 current_res.rel = core_result.rel;
137 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
141 current_res.record = core_result.records[1];
143 current_res.record = NULL;
146 RETURN NEXT current_res;
152 FROM asset.call_number cn
153 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
154 JOIN asset.uri uri ON (map.uri = uri.id)
156 AND cn.label = '##URI##'
158 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
159 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
160 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
164 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
165 visible_count := visible_count + 1;
167 current_res.id = core_result.id;
168 current_res.rel = core_result.rel;
172 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
176 current_res.record = core_result.records[1];
178 current_res.record = NULL;
181 RETURN NEXT current_res;
186 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
189 FROM asset.call_number cn
190 JOIN asset.copy cp ON (cp.call_number = cn.id)
193 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
194 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
195 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
200 FROM biblio.peer_bib_copy_map pr
201 JOIN asset.copy cp ON (cp.id = pr.target_copy)
203 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
204 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
205 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
209 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
210 excluded_count := excluded_count + 1;
217 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
220 FROM asset.call_number cn
221 JOIN asset.copy cp ON (cp.call_number = cn.id)
224 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
225 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
226 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
231 FROM biblio.peer_bib_copy_map pr
232 JOIN asset.copy cp ON (cp.id = pr.target_copy)
234 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
235 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
236 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
240 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
241 excluded_count := excluded_count + 1;
248 IF staff IS NULL OR NOT staff THEN
251 FROM asset.opac_visible_copies
252 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
253 AND record IN ( SELECT * FROM unnest( core_result.records ) )
258 FROM biblio.peer_bib_copy_map pr
259 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
260 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
261 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
266 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
267 excluded_count := excluded_count + 1;
275 FROM asset.call_number cn
276 JOIN asset.copy cp ON (cp.call_number = cn.id)
279 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
280 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
286 FROM biblio.peer_bib_copy_map pr
287 JOIN asset.copy cp ON (cp.id = pr.target_copy)
289 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
290 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
296 FROM asset.call_number cn
297 JOIN asset.copy cp ON (cp.call_number = cn.id)
298 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
303 -- Recheck Located URI visibility in the case of no "foreign" copies
305 FROM asset.call_number cn
306 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
307 JOIN asset.uri uri ON (map.uri = uri.id)
309 AND cn.label = '##URI##'
311 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
312 AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
316 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
317 excluded_count := excluded_count + 1;
321 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
322 excluded_count := excluded_count + 1;
333 visible_count := visible_count + 1;
335 current_res.id = core_result.id;
336 current_res.rel = core_result.rel;
340 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
344 current_res.record = core_result.records[1];
346 current_res.record = NULL;
349 RETURN NEXT current_res;
351 IF visible_count % 1000 = 0 THEN
352 -- RAISE NOTICE ' % visible so far ... ', visible_count;
357 current_res.id = NULL;
358 current_res.rel = NULL;
359 current_res.record = NULL;
360 current_res.total = total_count;
361 current_res.checked = check_count;
362 current_res.deleted = deleted_count;
363 current_res.visible = visible_count;
364 current_res.excluded = excluded_count;
368 RETURN NEXT current_res;
371 $func$ LANGUAGE PLPGSQL;
374 SELECT evergreen.upgrade_deps_block_check('0883', :eg_version);
376 -- This is a placeholder for 0883 which will be a backported version of the
377 -- staff URI visibility function for rel_2_5. This script does nothing for
378 -- rel_2_6 and later.