3 --Check if we can apply the upgrade.
4 SELECT evergreen.upgrade_deps_block_check('0791', :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;
47 check_limit := COALESCE( param_check, 1000 );
48 core_limit := COALESCE( param_limit, 25000 );
49 core_offset := COALESCE( param_offset, 0 );
51 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
53 IF param_search_ou > 0 THEN
54 IF param_depth IS NOT NULL THEN
55 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
57 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
60 SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
62 ELSIF param_search_ou < 0 THEN
63 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
65 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
66 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
67 luri_org_list := luri_org_list || tmp_int_list;
70 SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
72 ELSIF param_search_ou = 0 THEN
73 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
76 IF param_pref_ou IS NOT NULL THEN
77 SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
78 luri_org_list := luri_org_list || tmp_int_list;
81 OPEN core_cursor FOR EXECUTE param_query;
85 FETCH core_cursor INTO core_result;
87 EXIT WHEN total_count >= core_limit;
89 total_count := total_count + 1;
91 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
93 check_count := check_count + 1;
95 IF NOT deleted_search THEN
97 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
99 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
100 deleted_count := deleted_count + 1;
105 FROM biblio.record_entry b
106 JOIN config.bib_source s ON (b.source = s.id)
108 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
111 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
112 visible_count := visible_count + 1;
114 current_res.id = core_result.id;
115 current_res.rel = core_result.rel;
119 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
123 current_res.record = core_result.records[1];
125 current_res.record = NULL;
128 RETURN NEXT current_res;
134 FROM asset.call_number cn
135 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
136 JOIN asset.uri uri ON (map.uri = uri.id)
138 AND cn.label = '##URI##'
140 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
141 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
142 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
146 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
147 visible_count := visible_count + 1;
149 current_res.id = core_result.id;
150 current_res.rel = core_result.rel;
154 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
158 current_res.record = core_result.records[1];
160 current_res.record = NULL;
163 RETURN NEXT current_res;
168 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
171 FROM asset.call_number cn
172 JOIN asset.copy cp ON (cp.call_number = cn.id)
175 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
176 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
177 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
182 FROM biblio.peer_bib_copy_map pr
183 JOIN asset.copy cp ON (cp.id = pr.target_copy)
185 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
186 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
187 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
191 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
192 excluded_count := excluded_count + 1;
199 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
202 FROM asset.call_number cn
203 JOIN asset.copy cp ON (cp.call_number = cn.id)
206 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
207 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
208 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
213 FROM biblio.peer_bib_copy_map pr
214 JOIN asset.copy cp ON (cp.id = pr.target_copy)
216 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
217 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
218 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
222 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
223 excluded_count := excluded_count + 1;
230 IF staff IS NULL OR NOT staff THEN
233 FROM asset.opac_visible_copies
234 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
235 AND record IN ( SELECT * FROM unnest( core_result.records ) )
240 FROM biblio.peer_bib_copy_map pr
241 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
242 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
243 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
248 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
249 excluded_count := excluded_count + 1;
257 FROM asset.call_number cn
258 JOIN asset.copy cp ON (cp.call_number = cn.id)
261 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
262 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
268 FROM biblio.peer_bib_copy_map pr
269 JOIN asset.copy cp ON (cp.id = pr.target_copy)
271 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
272 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
278 FROM asset.call_number cn
279 JOIN asset.copy cp ON (cp.call_number = cn.id)
280 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
285 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
286 excluded_count := excluded_count + 1;
297 visible_count := visible_count + 1;
299 current_res.id = core_result.id;
300 current_res.rel = core_result.rel;
304 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
308 current_res.record = core_result.records[1];
310 current_res.record = NULL;
313 RETURN NEXT current_res;
315 IF visible_count % 1000 = 0 THEN
316 -- RAISE NOTICE ' % visible so far ... ', visible_count;
321 current_res.id = NULL;
322 current_res.rel = NULL;
323 current_res.record = NULL;
324 current_res.total = total_count;
325 current_res.checked = check_count;
326 current_res.deleted = deleted_count;
327 current_res.visible = visible_count;
328 current_res.excluded = excluded_count;
332 RETURN NEXT current_res;
335 $func$ LANGUAGE PLPGSQL;
338 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
339 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
341 transformed_xml TEXT;
344 xfrm config.xml_transform%ROWTYPE;
346 new_attrs HSTORE := ''::HSTORE;
347 attr_def config.record_attr_definition%ROWTYPE;
350 IF NEW.deleted IS TRUE THEN -- If this bib is deleted
351 PERFORM * FROM config.internal_flag WHERE
352 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
354 -- One needs to keep these around to support searches
355 -- with the #deleted modifier, so one should turn on the named
356 -- internal flag for that functionality.
357 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id;
358 DELETE FROM metabib.record_attr WHERE id = NEW.id;
361 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
362 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
363 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
364 RETURN NEW; -- and we're done
367 IF TG_OP = 'UPDATE' THEN -- re-ingest?
368 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
370 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
375 -- Record authority linking
376 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
378 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
381 -- Flatten and insert the mfr data
382 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
384 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
386 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
387 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
389 FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
391 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
392 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value
393 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
394 WHERE record = NEW.id
395 AND tag LIKE attr_def.tag
397 WHEN attr_def.sf_list IS NOT NULL
398 THEN POSITION(subfield IN attr_def.sf_list) > 0
405 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
406 attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
408 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
410 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
412 -- See if we can skip the XSLT ... it's expensive
413 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
414 -- Can't skip the transform
415 IF xfrm.xslt <> '---' THEN
416 transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
418 transformed_xml := NEW.marc;
421 prev_xfrm := xfrm.name;
424 IF xfrm.name IS NULL THEN
425 -- just grab the marcxml (empty) transform
426 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
427 prev_xfrm := xfrm.name;
430 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
432 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
433 SELECT m.value INTO attr_value
434 FROM biblio.marc21_physical_characteristics(NEW.id) v
435 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
436 WHERE v.subfield = attr_def.phys_char_sf
437 LIMIT 1; -- Just in case ...
441 -- apply index normalizers to attr_value
443 SELECT n.func AS func,
444 n.param_count AS param_count,
446 FROM config.index_normalizer n
447 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
448 WHERE attr = attr_def.name
450 EXECUTE 'SELECT ' || normalizer.func || '(' ||
451 COALESCE( quote_literal( attr_value ), 'NULL' ) ||
453 WHEN normalizer.param_count > 0
454 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
461 -- Add the new value to the hstore
462 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
466 IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
467 DELETE FROM metabib.record_attr WHERE id = NEW.id;
468 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
470 UPDATE metabib.record_attr SET attrs = new_attrs WHERE id = NEW.id;
476 -- Gather and insert the field entry data
477 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
480 IF TG_OP = 'INSERT' THEN
481 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
483 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
486 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
488 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
492 -- (re)map metarecord-bib linking
493 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
494 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
496 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
498 ELSE -- we're doing an update, and we're not deleted, remap
499 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
501 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
507 $func$ LANGUAGE PLPGSQL;