From 24007bf27d6f53073cfb45e4db43b88f651b971b Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Tue, 7 Jun 2011 13:52:22 -0400 Subject: [PATCH] Use native unnest() db function instead of explode_array() Per LP 789747, the custom explode_array() database function was found to generate significantly slower results for searches than using the native (and equivalent) unnest() array function. Given that unnest() has been part of PostgreSQL since 8.4 and Evergreen 2.0 requires a minimum of PostgreSQL 8.4, this patch removes the definition of the explode_array() functions and replaces its usage with unnest(). Signed-off-by: Dan Scott --- Open-ILS/src/sql/Pg/002.functions.aggregate.sql | 5 - Open-ILS/src/sql/Pg/002.functions.config.sql | 2 +- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 6 +- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 4 +- Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql | 54 ++-- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 55 ++-- .../sql/Pg/pgmemcache-ou_tree_traversal_cache.sql | 2 +- .../Pg/upgrade/0549.unnest_oils_xpath_table.sql | 71 +++++ ...0.unnest_biblio_extract_metabib_field_entry.sql | 113 ++++++++ ...551.unnest_metabib_remap_metarecord_for_bib.sql | 63 +++++ .../0552.unnest_biblio_map_authority_linking.sql | 23 ++ ...0553.unnest_action_hold_request_permit_test.sql | 189 +++++++++++++ .../0554.unnest_search_query_parser_fts.sql | 314 +++++++++++++++++++++ 14 files changed, 833 insertions(+), 70 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0549.unnest_oils_xpath_table.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0550.unnest_biblio_extract_metabib_field_entry.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0551.unnest_metabib_remap_metarecord_for_bib.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0552.unnest_biblio_map_authority_linking.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0553.unnest_action_hold_request_permit_test.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0554.unnest_search_query_parser_fts.sql diff --git a/Open-ILS/src/sql/Pg/002.functions.aggregate.sql b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql index 41f80902e..cb42cd7b0 100644 --- a/Open-ILS/src/sql/Pg/002.functions.aggregate.sql +++ b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql @@ -65,9 +65,4 @@ CREATE AGGREGATE public.agg_text ( stype = text ); -CREATE OR REPLACE FUNCTION public.explode_array(anyarray) RETURNS SETOF anyelement AS $BODY$ - SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s; -$BODY$ -LANGUAGE 'sql' IMMUTABLE; - COMMIT; diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql index c97a1dd57..56a3df86c 100644 --- a/Open-ILS/src/sql/Pg/002.functions.config.sql +++ b/Open-ILS/src/sql/Pg/002.functions.config.sql @@ -278,7 +278,7 @@ BEGIN select_list := ARRAY_APPEND( select_list, $sel$ - EXPLODE_ARRAY( + unnest( COALESCE( NULLIF( oils_xpath( diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 5ea5a861b..26a3a3f92 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -86,7 +86,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0548', :eg_version); -- dbwells +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0554', :eg_version); -- dbs CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 5dc476169..1ccbd573b 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -303,7 +303,7 @@ BEGIN xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); raw_text := NULL; - FOR xml_node IN SELECT x FROM explode_array(xml_node_list) AS x LOOP + FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP CONTINUE WHEN xml_node !~ E'^\\s*<'; curr_text := ARRAY_TO_STRING( @@ -961,7 +961,7 @@ BEGIN INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN - UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT explode_array(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved END IF; RETURN old_mr; @@ -976,7 +976,7 @@ CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT y.authority FROM ( SELECT DISTINCT $1 AS bib, BTRIM(remove_paren_substring(txt))::BIGINT AS authority - FROM explode_array(oils_xpath('//*[@code="0"]/text()',$2)) x(txt) + FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt) WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$ ) y JOIN authority.record_entry r ON r.id = y.authority; SELECT $1; diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index 633fb5711..5ffa88f7d 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -312,7 +312,7 @@ BEGIN FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%HOLD%' LOOP @@ -328,7 +328,7 @@ BEGIN FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%CIRC%' LOOP diff --git a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql index d1d3a5156..92eda10cb 100644 --- a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql @@ -3556,7 +3556,7 @@ BEGIN check_count := check_count + 1; - PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) ); + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); IF NOT FOUND THEN -- RAISE NOTICE ' % were all deleted ... ', core_result.records; deleted_count := deleted_count + 1; @@ -3567,7 +3567,7 @@ BEGIN FROM biblio.record_entry b JOIN config.bib_source s ON (b.source = s.id) WHERE s.transcendant - AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) ); + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); IF FOUND THEN -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; @@ -3600,8 +3600,8 @@ BEGIN AND cn.label = '##URI##' AND uri.active AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF FOUND THEN @@ -3634,9 +3634,9 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -3644,9 +3644,9 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -3665,9 +3665,9 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -3675,9 +3675,9 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -3693,16 +3693,16 @@ BEGIN PERFORM 1 FROM asset.opac_visible_copies - WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN PERFORM 1 FROM biblio.peer_bib_copy_map pr JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) - WHERE cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN @@ -3720,8 +3720,8 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN @@ -3730,15 +3730,15 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN PERFORM 1 FROM asset.call_number cn - WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF FOUND THEN @@ -5921,7 +5921,7 @@ BEGIN FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%HOLD%' LOOP @@ -5937,7 +5937,7 @@ BEGIN FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%CIRC%' LOOP diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql index e13bb6007..79de91364 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -33,11 +33,6 @@ CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( fie CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT ); CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT ); -CREATE OR REPLACE FUNCTION search.explode_array(anyarray) RETURNS SETOF anyelement AS $BODY$ - SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s; -$BODY$ -LANGUAGE 'sql' IMMUTABLE; - CREATE OR REPLACE FUNCTION search.query_parser_fts ( param_search_ou INT, @@ -106,7 +101,7 @@ BEGIN check_count := check_count + 1; - PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) ); + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); IF NOT FOUND THEN -- RAISE NOTICE ' % were all deleted ... ', core_result.records; deleted_count := deleted_count + 1; @@ -117,7 +112,7 @@ BEGIN FROM biblio.record_entry b JOIN config.bib_source s ON (b.source = s.id) WHERE s.transcendant - AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) ); + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); IF FOUND THEN -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; @@ -150,8 +145,8 @@ BEGIN AND cn.label = '##URI##' AND uri.active AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF FOUND THEN @@ -184,9 +179,9 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -194,9 +189,9 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -215,9 +210,9 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -225,9 +220,9 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -243,16 +238,16 @@ BEGIN PERFORM 1 FROM asset.opac_visible_copies - WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN PERFORM 1 FROM biblio.peer_bib_copy_map pr JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) - WHERE cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN @@ -270,8 +265,8 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN @@ -280,15 +275,15 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN PERFORM 1 FROM asset.call_number cn - WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF FOUND THEN diff --git a/Open-ILS/src/sql/Pg/pgmemcache-ou_tree_traversal_cache.sql b/Open-ILS/src/sql/Pg/pgmemcache-ou_tree_traversal_cache.sql index 53eea2e8e..1559947aa 100644 --- a/Open-ILS/src/sql/Pg/pgmemcache-ou_tree_traversal_cache.sql +++ b/Open-ILS/src/sql/Pg/pgmemcache-ou_tree_traversal_cache.sql @@ -69,7 +69,7 @@ BEGIN FOR curr_org IN SELECT * FROM actor.org_unit - WHERE id IN ( SELECT * FROM explode_array( STRING_TO_ARRAY( cached_value.x, ',' ) ) ) + WHERE id IN ( SELECT * FROM unnest( STRING_TO_ARRAY( cached_value.x, ',' ) ) ) LOOP RETURN NEXT curr_org; END LOOP; diff --git a/Open-ILS/src/sql/Pg/upgrade/0549.unnest_oils_xpath_table.sql b/Open-ILS/src/sql/Pg/upgrade/0549.unnest_oils_xpath_table.sql new file mode 100644 index 000000000..117dba29e --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0549.unnest_oils_xpath_table.sql @@ -0,0 +1,71 @@ +-- Evergreen DB patch 0549.unnest_oils_xpath_table.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0549', :eg_version); + +CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$ +DECLARE + xpath_list TEXT[]; + select_list TEXT[]; + where_list TEXT[]; + q TEXT; + out_record RECORD; + empty_test RECORD; +BEGIN + xpath_list := STRING_TO_ARRAY( xpaths, '|' ); + + select_list := ARRAY_APPEND( select_list, key || '::INT AS key' ); + + FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP + IF xpath_list[i] = 'null()' THEN + select_list := ARRAY_APPEND( select_list, 'NULL::TEXT AS c_' || i ); + ELSE + select_list := ARRAY_APPEND( + select_list, + $sel$ + unnest( + COALESCE( + NULLIF( + oils_xpath( + $sel$ || + quote_literal( + CASE + WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i] + ELSE xpath_list[i] || '//text()' + END + ) || + $sel$, + $sel$ || document_field || $sel$ + ), + '{}'::TEXT[] + ), + '{NULL}'::TEXT[] + ) + ) AS c_$sel$ || i + ); + where_list := ARRAY_APPEND( + where_list, + 'c_' || i || ' IS NOT NULL' + ); + END IF; + END LOOP; + + q := $q$ +SELECT * FROM ( + SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$) +)x WHERE $q$ || ARRAY_TO_STRING( where_list, ' OR ' ); + -- RAISE NOTICE 'query: %', q; + + FOR out_record IN EXECUTE q LOOP + RETURN NEXT out_record; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL IMMUTABLE; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0550.unnest_biblio_extract_metabib_field_entry.sql b/Open-ILS/src/sql/Pg/upgrade/0550.unnest_biblio_extract_metabib_field_entry.sql new file mode 100644 index 000000000..41d26e580 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0550.unnest_biblio_extract_metabib_field_entry.sql @@ -0,0 +1,113 @@ +-- Evergreen DB patch 0550.unnest_biblio_extract_metabib_field_entry.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0550', :eg_version); + +CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$ +DECLARE + bib biblio.record_entry%ROWTYPE; + idx config.metabib_field%ROWTYPE; + xfrm config.xml_transform%ROWTYPE; + prev_xfrm TEXT; + transformed_xml TEXT; + xml_node TEXT; + xml_node_list TEXT[]; + facet_text TEXT; + raw_text TEXT; + curr_text TEXT; + joiner TEXT := default_joiner; -- XXX will index defs supply a joiner? + output_row metabib.field_entry_template%ROWTYPE; +BEGIN + + -- Get the record + SELECT INTO bib * FROM biblio.record_entry WHERE id = rid; + + -- Loop over the indexing entries + FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP + + SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format; + + -- See if we can skip the XSLT ... it's expensive + IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN + -- Can't skip the transform + IF xfrm.xslt <> '---' THEN + transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt); + ELSE + transformed_xml := bib.marc; + END IF; + + prev_xfrm := xfrm.name; + END IF; + + xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + + raw_text := NULL; + FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP + CONTINUE WHEN xml_node !~ E'^\\s*<'; + + curr_text := ARRAY_TO_STRING( + oils_xpath( '//text()', + REGEXP_REPLACE( -- This escapes all &s not followed by "amp;". Data ise returned from oils_xpath (above) in UTF-8, not entity encoded + REGEXP_REPLACE( -- This escapes embeded [^<]+)(<)([^>]+<)$re$, + E'\\1<\\3', + 'g' + ), + '&(?!amp;)', + '&', + 'g' + ) + ), + ' ' + ); + + CONTINUE WHEN curr_text IS NULL OR curr_text = ''; + + IF raw_text IS NOT NULL THEN + raw_text := raw_text || joiner; + END IF; + + raw_text := COALESCE(raw_text,'') || curr_text; + + -- insert raw node text for faceting + IF idx.facet_field THEN + + IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN + facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + ELSE + facet_text := curr_text; + END IF; + + output_row.field_class = idx.field_class; + output_row.field = -1 * idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g')); + + RETURN NEXT output_row; + END IF; + + END LOOP; + + CONTINUE WHEN raw_text IS NULL OR raw_text = ''; + + -- insert combined node text for searching + IF idx.search_field THEN + output_row.field_class = idx.field_class; + output_row.field = idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g')); + + RETURN NEXT output_row; + END IF; + + END LOOP; + +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0551.unnest_metabib_remap_metarecord_for_bib.sql b/Open-ILS/src/sql/Pg/upgrade/0551.unnest_metabib_remap_metarecord_for_bib.sql new file mode 100644 index 000000000..b19ea7ddc --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0551.unnest_metabib_remap_metarecord_for_bib.sql @@ -0,0 +1,63 @@ +-- Evergreen DB patch 0551.unnest_metabib_remap_metarecord_for_bib.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0551', :eg_version); + +CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT ) RETURNS BIGINT AS $func$ +DECLARE + source_count INT; + old_mr BIGINT; + tmp_mr metabib.metarecord%ROWTYPE; + deleted_mrs BIGINT[]; +BEGIN + + DELETE FROM metabib.metarecord_source_map WHERE source = bib_id; -- Rid ourselves of the search-estimate-killing linkage + + FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP + + IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching + old_mr := tmp_mr.id; + ELSE + SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id; + IF source_count = 0 THEN -- No other records + deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id); + DELETE FROM metabib.metarecord WHERE id = tmp_mr.id; + END IF; + END IF; + + END LOOP; + + IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint? + IF old_mr IS NULL THEN -- nope, create one and grab its id + INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id ); + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; + ELSE -- indeed there is. update it with a null cache and recalcualated master record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + ELSE -- there was one we already attached to, update its mods cache and master_record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + + INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping + + IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN + UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + END IF; + + RETURN old_mr; + +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0552.unnest_biblio_map_authority_linking.sql b/Open-ILS/src/sql/Pg/upgrade/0552.unnest_biblio_map_authority_linking.sql new file mode 100644 index 000000000..840505416 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0552.unnest_biblio_map_authority_linking.sql @@ -0,0 +1,23 @@ +-- Evergreen DB patch 0552.unnest_biblio_map_authority_linking.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0552', :eg_version); + +CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$ + DELETE FROM authority.bib_linking WHERE bib = $1; + INSERT INTO authority.bib_linking (bib, authority) + SELECT y.bib, + y.authority + FROM ( SELECT DISTINCT $1 AS bib, + BTRIM(remove_paren_substring(txt))::BIGINT AS authority + FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt) + WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$ + ) y JOIN authority.record_entry r ON r.id = y.authority; + SELECT $1; +$func$ LANGUAGE SQL; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0553.unnest_action_hold_request_permit_test.sql b/Open-ILS/src/sql/Pg/upgrade/0553.unnest_action_hold_request_permit_test.sql new file mode 100644 index 000000000..9e5acc59d --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0553.unnest_action_hold_request_permit_test.sql @@ -0,0 +1,189 @@ +-- Evergreen DB patch 0553.unnest_action_hold_request_permit_test.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0553', :eg_version); + +CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$ +DECLARE + matchpoint_id INT; + user_object actor.usr%ROWTYPE; + age_protect_object config.rule_age_hold_protect%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + transit_range_ou_type actor.org_unit_type%ROWTYPE; + transit_source actor.org_unit%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_cn_object asset.call_number%ROWTYPE; + ou_skip actor.org_unit_setting%ROWTYPE; + result action.matrix_test_result; + hold_test config.hold_matrix_matchpoint%ROWTYPE; + hold_count INT; + hold_transit_prox INT; + frozen_hold_count INT; + context_org_list INT[]; + done BOOL := FALSE; +BEGIN + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou ); + + result.success := TRUE; + + -- Fail if we couldn't find a user + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + + -- Fail if we couldn't find a copy + IF item_object.id IS NULL THEN + result.fail_part := 'no_item'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor); + result.matchpoint := matchpoint_id; + + SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib; + + -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true + IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN + result.fail_part := 'circ.holds.target_skip_me'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- Fail if user is barred + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- Fail if we couldn't find any matchpoint (requires a default) + IF matchpoint_id IS NULL THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id; + + IF hold_test.holdable IS FALSE THEN + result.fail_part := 'config.hold_matrix_test.holdable'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + IF hold_test.transit_range IS NOT NULL THEN + SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range; + IF hold_test.distance_is_from_owner THEN + SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number; + ELSE + SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib; + END IF; + + PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou; + + IF NOT FOUND THEN + result.fail_part := 'transit_range'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE '%HOLD%' LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + + IF hold_test.stop_blocked_user IS TRUE THEN + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE '%CIRC%' LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + END IF; + + IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN + SELECT INTO hold_count COUNT(*) + FROM action.hold_request + WHERE usr = match_user + AND fulfillment_time IS NULL + AND cancel_time IS NULL + AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END; + + IF hold_count >= hold_test.max_holds THEN + result.fail_part := 'config.hold_matrix_test.max_holds'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + IF item_object.age_protect IS NOT NULL THEN + SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect; + + IF item_object.create_date + age_protect_object.age > NOW() THEN + IF hold_test.distance_is_from_owner THEN + SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number; + SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou; + ELSE + SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou; + END IF; + + IF hold_transit_prox > age_protect_object.prox THEN + result.fail_part := 'config.rule_age_hold_protect.prox'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + END IF; + + IF NOT done THEN + RETURN NEXT result; + END IF; + + RETURN; +END; +$func$ LANGUAGE plpgsql; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0554.unnest_search_query_parser_fts.sql b/Open-ILS/src/sql/Pg/upgrade/0554.unnest_search_query_parser_fts.sql new file mode 100644 index 000000000..6a5a8e250 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0554.unnest_search_query_parser_fts.sql @@ -0,0 +1,314 @@ +-- Evergreen DB patch 0554.unnest_search_query_parser_fts.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0554', :eg_version); + +CREATE OR REPLACE FUNCTION search.query_parser_fts ( + + param_search_ou INT, + param_depth INT, + param_query TEXT, + param_statuses INT[], + param_locations INT[], + param_offset INT, + param_check INT, + param_limit INT, + metarecord BOOL, + staff BOOL + +) RETURNS SETOF search.search_result AS $func$ +DECLARE + + current_res search.search_result%ROWTYPE; + search_org_list INT[]; + + check_limit INT; + core_limit INT; + core_offset INT; + tmp_int INT; + + core_result RECORD; + core_cursor REFCURSOR; + core_rel_query TEXT; + + total_count INT := 0; + check_count INT := 0; + deleted_count INT := 0; + visible_count INT := 0; + excluded_count INT := 0; + +BEGIN + + check_limit := COALESCE( param_check, 1000 ); + core_limit := COALESCE( param_limit, 25000 ); + core_offset := COALESCE( param_offset, 0 ); + + -- core_skip_chk := COALESCE( param_skip_chk, 1 ); + + IF param_search_ou > 0 THEN + IF param_depth IS NOT NULL THEN + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); + ELSE + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); + END IF; + ELSIF param_search_ou < 0 THEN + SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; + ELSIF param_search_ou = 0 THEN + -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. + END IF; + + OPEN core_cursor FOR EXECUTE param_query; + + LOOP + + FETCH core_cursor INTO core_result; + EXIT WHEN NOT FOUND; + EXIT WHEN total_count >= core_limit; + + total_count := total_count + 1; + + CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset; + + check_count := check_count + 1; + + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + IF NOT FOUND THEN + -- RAISE NOTICE ' % were all deleted ... ', core_result.records; + deleted_count := deleted_count + 1; + CONTINUE; + END IF; + + PERFORM 1 + FROM biblio.record_entry b + JOIN config.bib_source s ON (b.source = s.id) + WHERE s.transcendant + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + + IF FOUND THEN + -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) + JOIN asset.uri uri ON (map.uri = uri.id) + WHERE NOT cn.deleted + AND cn.label = '##URI##' + AND uri.active + AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF staff IS NULL OR NOT staff THEN + + PERFORM 1 + FROM asset.opac_visible_copies + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + ELSE + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM asset.call_number cn + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + END IF; + + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + IF visible_count % 1000 = 0 THEN + -- RAISE NOTICE ' % visible so far ... ', visible_count; + END IF; + + END LOOP; + + current_res.id = NULL; + current_res.rel = NULL; + current_res.record = NULL; + current_res.total = total_count; + current_res.checked = check_count; + current_res.deleted = deleted_count; + current_res.visible = visible_count; + current_res.excluded = excluded_count; + + CLOSE core_cursor; + + RETURN NEXT current_res; + +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.11.0