From ba47ecc6196055886ad6f23284819be5dec8448d Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 3 Oct 2011 15:01:26 -0400 Subject: [PATCH] Correct Located URI visiblity Located URIs should be visible when they exist at the search context org unit or an ancestor thereof. However, before the change embodied in this commit, they effectively acted exactly like copies, making records visible anywhere within the context+depth range. An example, using the stock example OU hierarchy: * Before this change, a Located URI with an owner of BR1 would cause its record to show up in a search at (or scoped to via a depth parameter) BR1, SYS1 or CONS. * Before this change, a Located URI with an owner of SYS1 would NOT cause its record to show up in a search at (or scoped to via a depth parameter) BR1! * After this change, a Located URI with an owner of BR1 will cause its record to show up only in a search with a context OU of BR1, irrespective of depth scoping. * After this change, a Located URI with an owner of SYS1 WILL cause its record to show up in a search with a context OU of BR1 or SYS1, regardless of depth scoping. Signed-off-by: Mike Rylander Signed-off-by: Dan Scott --- .../src/sql/Pg/300.schema.staged_search.sql | 15 +- .../XXXX.schema.located_uri_visiblity_fix.sql | 333 ++++++++++++++++++ 2 files changed, 347 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.located_uri_visiblity_fix.sql 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 ebea8cd2d5..9f23e17848 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -51,6 +51,8 @@ DECLARE current_res search.search_result%ROWTYPE; search_org_list INT[]; + luri_org_list INT[]; + tmp_int_list INT[]; check_limit INT; core_limit INT; @@ -81,8 +83,19 @@ BEGIN ELSE SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); END IF; + + SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou ); + 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; + + FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP + SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int ); + luri_org_list := luri_org_list || tmp_int_list; + END LOOP; + + SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id); + ELSIF param_search_ou = 0 THEN -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. END IF; @@ -146,7 +159,7 @@ BEGIN 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 ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) ) LIMIT 1; IF FOUND THEN diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.located_uri_visiblity_fix.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.located_uri_visiblity_fix.sql new file mode 100644 index 0000000000..3b4421277b --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.located_uri_visiblity_fix.sql @@ -0,0 +1,333 @@ +-- Evergreen DB patch XXXX.schema.located_uri_visiblity_fix.sql +-- +-- FIXME: insert description of change, if needed +-- +BEGIN; + + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- FIXME: add/check SQL statements to perform the upgrade +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[]; + luri_org_list INT[]; + tmp_int_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; + + SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou ); + + 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; + + FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP + SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int ); + luri_org_list := luri_org_list || tmp_int_list; + END LOOP; + + SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id); + + 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( luri_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 + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND NOT cp.deleted + 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.43.2