From 49d05487ffe1eab12da93e68aac0ba72aee2d12f Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 16 Jan 2018 16:26:31 -0500 Subject: [PATCH] LP#1743639: Test location as proxy for location group Location groups are a table remove from copies, and can cause very large updates to the copy vis cache. So, we will expand location groups to locations, in situ, to avoid this problem and the need to supply a trigger to update th copy vis cache for groups. This will allow us to reclaim the location group bit in the visibility cache namespace if necessary, at a later date. Signed-off-by: Mike Rylander Signed-off-by: Kathy Lussier --- .../lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm | 6 ++++-- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 2 +- .../sql/Pg/upgrade/XXXX.function.copy_location_group_visible.sql | 2 +- 3 files changed, 6 insertions(+), 4 deletions(-) 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 f7e710eb9a..0f19d4cc86 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 @@ -1526,15 +1526,17 @@ sub flatten { } elsif ($filter->name eq 'locations') { if (@{$filter->args} > 0) { my $negate = $filter->negate ? 'TRUE' : 'FALSE'; + my $filter_args = join(",", map(int, @{$filter->args})); push @{$vis_filter{'c_attr'}}, - "search.calculate_visibility_attribute_test('location','{".join(',', @{$filter->args})."}',$negate)"; + "search.calculate_visibility_attribute_test('location',$filter_args,$negate)"; } } elsif ($filter->name eq 'location_groups') { if (@{$filter->args} > 0) { my $negate = $filter->negate ? 'TRUE' : 'FALSE'; + my $filter_args = join(",", map(int, @{$filter->args})); push @{$vis_filter{'c_attr'}}, - "search.calculate_visibility_attribute_test('location_group','{".join(',', @{$filter->args})."}',$negate)"; + "search.calculate_visibility_attribute_test('location',(SELECT ARRAY_AGG(location) FROM asset.copy_location_group_map WHERE lgroup IN ($filter_args)),$negate)"; } } elsif ($filter->name eq 'statuses') { 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 cbd68f8f28..51be63d2ed 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -761,7 +761,7 @@ CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$ FROM asset.copy_location_group WHERE NOT opac_visible; */ -$f$ LANGUAGE SQL STABLE; +$f$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION asset.location_default () RETURNS TEXT AS $f$ SELECT '!(' || ARRAY_TO_STRING(ARRAY_AGG(search.calculate_visibility_attribute(id, 'location')),'|') || ')' diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_location_group_visible.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_location_group_visible.sql index 7b457c6f71..ca49b549f1 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_location_group_visible.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.copy_location_group_visible.sql @@ -7,7 +7,7 @@ CREATE OR REPLACE FUNCTION asset.location_group_default () RETURNS TEXT AS $f$ FROM asset.copy_location_group WHERE NOT opac_visible; */ -$f$ LANGUAGE SQL STABLE; +$f$ LANGUAGE SQL IMMUTABLE; COMMIT; -- 2.11.0