From 0796460961b510e385437c89bd4bf0b9a5ea7f77 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Fri, 6 Apr 2012 14:52:20 -0400 Subject: [PATCH] Add pref_ou query filter for preferred library searching Include the user's "preferred search library" (as set in search preferences, falling back to home OU) in searches, specifically for the purposes of ensuring that located URIs at the user's preferred library would trigger hits where physical copies would be out of scope. Practical example: 1. User sets preferred search library to BR1. 2. They jump onto the catalogue, log in (which changes their search org to their preferred search library of BR1), but then for some reason change their search org in the org selector to BR3. 3. They issue a search for "Harry Potter and the Philosopher's Stone". BR3 doesn't hold any copies or have any located URIs, but SYS1 (BR1's parent) has a PotterMore licence and has added their 856 $9 SYS1 to a bib record. As it currently stands, User is out of luck; they won't see any hits in search results as there are no copies or located URIs in the BR3 scope. The proposed enhancement would, however, make the search results contain a hit for "Harry Potter and the Philosopher's Stone" at the user's preferred search library. Sites can trigger the preferred library as part of the query filters using the "pref_ou(SHORTNAME)" syntax. Signed-off-by: Dan Scott Signed-off-by: Jason Stephenson --- .../lib/OpenILS/Application/Search/Biblio.pm | 11 +- .../Application/Storage/Driver/Pg/QueryParser.pm | 1 + .../Application/Storage/Publisher/metabib.pm | 13 +- .../perlmods/lib/OpenILS/WWW/EGCatLoader/Search.pm | 6 + Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 9 +- .../Pg/upgrade/0704.schema.query_parser_fts.sql | 341 +++++++++++++++++++++ 7 files changed, 377 insertions(+), 6 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0704.schema.query_parser_fts.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Biblio.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Biblio.pm index 189ec6c377..82ad1af73a 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Biblio.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Search/Biblio.pm @@ -748,6 +748,7 @@ Recognized search keys include: series (se) - search series * lang - limit by language (specifiy multiple langs with lang:l1 lang:l2 ...) site - search at specified org unit, corresponds to actor.org_unit.shortname + pref_ou - extend search to specified org unit, corresponds to actor.org_unit.shortname sort - sort type (title, author, pubdate) dir - sort direction (asc, desc) available - if set to anything other than "false" or "0", limits to available items @@ -805,7 +806,7 @@ sub multiclass_query { my $simple_class_re = qr/((?:\w+(?:\|\w+)?):[^:]+?)$/; my $class_list_re = qr/(?:keyword|title|author|subject|series)/; - my $modifier_list_re = qr/(?:site|dir|sort|lang|available)/; + my $modifier_list_re = qr/(?:site|dir|sort|lang|available|preflib)/; my $tmp_value = ''; while ($query =~ s/$simple_class_re//so) { @@ -841,6 +842,14 @@ sub multiclass_query { } else { $logger->warn("'site:' query used on invalid org shortname: $value ... ignoring"); } + } elsif($type eq 'pref_ou') { + # 'pref_ou' is the preferred org shortname. + my $e = new_editor(); + if(my $org = $e->search_actor_org_unit({shortname => $value})->[0]) { + $arghash->{pref_ou} = $org->id if $org; + } else { + $logger->warn("'pref_ou:' query used on invalid org shortname: $value ... ignoring"); + } } elsif($type eq 'available') { # limit to available diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index f88affe468..efd83e07a6 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -424,6 +424,7 @@ __PACKAGE__->add_search_filter( 'statuses' ); __PACKAGE__->add_search_filter( 'locations' ); __PACKAGE__->add_search_filter( 'location_groups' ); __PACKAGE__->add_search_filter( 'site' ); +__PACKAGE__->add_search_filter( 'pref_ou' ); __PACKAGE__->add_search_filter( 'lasso' ); __PACKAGE__->add_search_filter( 'my_lasso' ); __PACKAGE__->add_search_filter( 'depth' ); diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm index 22b50f89b8..1da0115738 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm @@ -2911,7 +2911,6 @@ sub query_parser_fts { } $ou = actor::org_unit->search( { shortname => $ou } )->next->id if ($ou and $ou !~ /^(-)?\d+$/); - # gather lasso, as with $ou my $lasso = $args{lasso}; if (my ($filter) = $query->parse_tree->find_filter('lasso')) { @@ -2933,6 +2932,13 @@ sub query_parser_fts { # if we have a lasso, go with that, otherwise ... ou $ou = $lasso if ($lasso); + # gather the preferred OU, if one is specified, as with $ou + my $pref_ou = $args{pref_ou}; + $log->info("pref_ou = $pref_ou"); + if (my ($filter) = $query->parse_tree->find_filter('pref_ou')) { + $pref_ou = $filter->args->[0] if (@{$filter->args}); + } + $pref_ou = actor::org_unit->search( { shortname => $pref_ou } )->next->id if ($pref_ou and $pref_ou !~ /^(-)?\d+$/); # get the default $ou if we have nothing $ou = actor::org_unit->search( { parent_ou => undef } )->next->id if (!$ou and !$lasso and !$mylasso); @@ -3028,6 +3034,7 @@ sub query_parser_fts { my $param_locations = '$${' . join(',', map { s/\$//go; "\"$_\""} @location) . '}$$'; my $staff = ($self->api_name =~ /staff/ or $query->parse_tree->find_modifier('staff')) ? "'t'" : "'f'"; my $metarecord = ($self->api_name =~ /metabib/ or $query->parse_tree->find_modifier('metabib') or $query->parse_tree->find_modifier('metarecord')) ? "'t'" : "'f'"; + my $param_pref_ou = $pref_ou || 'NULL'; my $sth = metabib::metarecord_source_map->db_Main->prepare(<<" SQL"); SELECT * -- bib search: $args{query} @@ -3041,7 +3048,8 @@ sub query_parser_fts { $param_check\:\:INT, $param_limit\:\:INT, $metarecord\:\:BOOL, - $staff\:\:BOOL + $staff\:\:BOOL, + $param_pref_ou\:\:INT ); SQL @@ -3184,6 +3192,7 @@ sub query_parser_fts_wrapper { $query = "estimation_strategy($args{estimation_strategy}) $query" if ($args{estimation_strategy}); $query = "site($args{org_unit}) $query" if ($args{org_unit}); + $query = "pref_ou($args{pref_ou}) $query" if ($args{pref_ou}); $query = "depth($args{depth}) $query" if (defined($args{depth})); $query = "sort($args{sort}) $query" if ($args{sort}); $query = "limit($args{limit}) $query" if ($args{limit}); diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Search.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Search.pm index d687de2827..b4d77e2d74 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Search.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Search.pm @@ -112,6 +112,12 @@ sub _prepare_biblio_search { $query .= " site($site)"; } + my $pref_ou = $ctx->{pref_ou}; + if (defined($pref_ou) and $pref_ou ne '' and $pref_ou != $org and ($pref_ou ne $ctx->{aou_tree}->()->id)) { + my $plib = $ctx->{get_aou}->($pref_ou)->shortname; + $query .= " pref_ou($plib)"; + } + if (my $grp = $ctx->{copy_location_group}) { $query .= " location_groups($grp)"; } diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index c6cdb04e50..b1f20934bc 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -87,7 +87,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 ('0703', :eg_version); -- tsbere +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0704', :eg_version); -- dbs CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, 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 9f23e17848..26d9b530bf 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -44,8 +44,8 @@ CREATE OR REPLACE FUNCTION search.query_parser_fts ( param_check INT, param_limit INT, metarecord BOOL, - staff BOOL - + staff BOOL, + param_pref_ou INT DEFAULT NULL ) RETURNS SETOF search.search_result AS $func$ DECLARE @@ -100,6 +100,11 @@ BEGIN -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. END IF; + IF param_pref_ou IS NOT NULL THEN + SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou); + luri_org_list := luri_org_list || tmp_int_list; + END IF; + OPEN core_cursor FOR EXECUTE param_query; LOOP diff --git a/Open-ILS/src/sql/Pg/upgrade/0704.schema.query_parser_fts.sql b/Open-ILS/src/sql/Pg/upgrade/0704.schema.query_parser_fts.sql new file mode 100644 index 0000000000..12ab2f6d38 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0704.schema.query_parser_fts.sql @@ -0,0 +1,341 @@ +-- Evergreen DB patch 0704.schema.query_parser_fts.sql +-- +-- Add pref_ou query filter for preferred library searching +-- +BEGIN; + + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0704', :eg_version); + +-- Create the new 11-parameter function, featuring param_pref_ou +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, + param_pref_ou INT DEFAULT NULL +) 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; + + IF param_pref_ou IS NOT NULL THEN + SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou); + luri_org_list := luri_org_list || tmp_int_list; + 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; + +-- Drop the old 10-parameter function +DROP FUNCTION IF EXISTS search.query_parser_fts ( + INT, INT, TEXT, INT[], INT[], INT, INT, INT, BOOL, BOOL +); + +COMMIT; -- 2.11.0