This is an automated email from the git hooks/post-receive script. It was generated because a ref change was pushed to the repository containing the project "Evergreen ILS". The branch, main has been updated via 5b796601dad9bc8f7b5ab7de141a147a5bf59a40 (commit) via 5b90c95f19b83bebc266ab44918ccc62bb9ae5ef (commit) from 7e70a43c3a65fdc783c0f7045427ab8fbf2ee91d (commit) Those revisions listed above that are new to this repository have not appeared on any other notification email; so we list those revisions in full, below. - Log ----------------------------------------------------------------- commit 5b796601dad9bc8f7b5ab7de141a147a5bf59a40 Author: Jason Boyer <JBoyer@equinoxOLI.org> Date: Tue Mar 18 16:12:28 2025 -0400 LP2086106: Stamping Upgrade Script Signed-off-by: Jason Boyer <JBoyer@equinoxOLI.org> diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 3eb6bf8f11..f2428586a1 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -92,7 +92,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 ('1460', :eg_version); -- JBoyer/sandbergja +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1461', :eg_version); -- berick/llewellyn.masrshall/JBoyer CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.phone-settings-index.sql b/Open-ILS/src/sql/Pg/upgrade/1461.schema.phone-settings-index.sql similarity index 87% rename from Open-ILS/src/sql/Pg/upgrade/XXXX.schema.phone-settings-index.sql rename to Open-ILS/src/sql/Pg/upgrade/1461.schema.phone-settings-index.sql index 426bcded49..4cf6ce9a87 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.phone-settings-index.sql +++ b/Open-ILS/src/sql/Pg/upgrade/1461.schema.phone-settings-index.sql @@ -1,6 +1,6 @@ BEGIN; --- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version); +SELECT evergreen.upgrade_deps_block_check('1461', :eg_version); -- for searching e.g. "111-111-1111" CREATE INDEX actor_usr_setting_phone_values_idx commit 5b90c95f19b83bebc266ab44918ccc62bb9ae5ef Author: Bill Erickson <berickxx@gmail.com> Date: Thu Oct 31 10:27:05 2024 -0400 LP2086106 SMS/Default phone search performance improvements Move the patron phone search part of the query to a CTE/WITH clause and add an index on phone-related user settings to improve performance. Now the query looks more like this: WITH has_phone_number AS ( SELECT id FROM actor.usr WHERE evergreen.lowercase(day_phone) ~ '^333-333-3333' OR evergreen.lowercase(evening_phone) ~ '^333-333-3333' OR evergreen.lowercase(other_phone) ~ '^333-333-3333' UNION SELECT usr AS id FROM actor.usr_setting WHERE name IN ('opac.default_phone', 'opac.default_sms_notify') AND evergreen.lowercase(value) ~ '^"?333-333-3333' ) SELECT ... FROM actor.usr AS users JOIN actor.org_unit_descendants(1) d ON (d.id = users.home_ou) JOIN has_phone_number hpn ON hpn.id = users.id WHERE users.deleted = FALSE AND users.active = TRUE ... Signed-off-by: Bill Erickson <berickxx@gmail.com> Signed-off-by: Llewellyn Marshall <llewellyn.marshall@dncr.nc.gov> Signed-off-by: Jason Boyer <JBoyer@equinoxOLI.org> diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm index e40fe94d10..3ef2d8478a 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/actor.pm @@ -789,11 +789,16 @@ sub patron_search { unshift(@usrv, $cv); } - my $phone = ''; - my @ps; + my $phone_cte = ''; + my $phone_join = ''; my @phonev; + if ($pv) { - for my $p ( qw/day_phone evening_phone other_phone/ ) { + $phone_join = 'JOIN has_phone_number hpn ON hpn.id = users.id'; + + my @ps; + + for my $p (qw/day_phone evening_phone other_phone/) { if ($pv =~ /^\d+$/) { push @ps, "evergreen.lowercase(REGEXP_REPLACE($p, '[^0-9]', '', 'g')) ~ ?"; } else { @@ -802,26 +807,27 @@ sub patron_search { push @phonev, "^$pv"; } - # Also search user settings which may contain phone number values. + my $main_where = join(' OR ', @ps); + + my $main_query = "SELECT id FROM actor.usr WHERE $main_where"; + my $normalize = ($pv =~ /^\d+$/) ? "evergreen.lowercase(REGEXP_REPLACE(value, '[^0-9]', '', 'g')) ~ ?" : "evergreen.lowercase(value) ~ ?"; - push @ps, <<" SQL"; - EXISTS ( - SELECT TRUE FROM actor.usr_setting - WHERE usr = u.id - AND name IN ('opac.default_phone', 'opac.default_sms_notify') - AND $normalize - LIMIT 1 - ) + my $setting_query = <<" SQL"; + SELECT usr AS id + FROM actor.usr_setting + WHERE + name IN ('opac.default_phone', 'opac.default_sms_notify') + AND $normalize SQL # Prefix the search value with '"?' since user setting phone # values may be stored as JSON numbers or (more likely) strings. push(@phonev, "^\"?$pv"); - $phone = '(' . join(' OR ', @ps) . ')'; + $phone_cte = "WITH has_phone_number AS ($main_query UNION $setting_query)" } my $ident = ''; @@ -860,7 +866,7 @@ sub patron_search { $profile = '(profile IN (SELECT id FROM permission.grp_descendants(?)))'; push @profv, $prof; } - my $usr_where = join ' AND ', grep { $_ } ($usr,$phone,$ident,$name,$profile); + my $usr_where = join ' AND ', grep { $_ } ($usr,$ident,$name,$profile); my $addr_where = $addr; @@ -889,7 +895,7 @@ sub patron_search { $select = "$a_select"; } - return undef if (!$select && !$card); + return undef if (!$select && !$card && !$phone_cte); my $order_by = join ', ', map { 'evergreen.lowercase(CAST(users.'. (split / /,$_)[0] . ' AS text)) ' . (split / /,$_)[1] } @$sort; my $distinct_list = join ', ', map { 'evergreen.lowercase(CAST(users.'. (split / /,$_)[0] . ' AS text))' } @$sort; @@ -930,9 +936,11 @@ sub patron_search { $select = "JOIN ($select) AS search ON (search.id = users.id)" if ($select); $select = <<" SQL"; + $phone_cte SELECT $distinct_list FROM $u_table AS users $card JOIN $descendants d ON (d.id = users.home_ou) + $phone_join $select $clone_select $penalty_join @@ -945,7 +953,7 @@ sub patron_search { OFFSET $offset SQL - return actor::user->db_Main->selectcol_arrayref($select, {Columns=>[scalar(@$sort)]}, map {lc($_)} (@usrv,@phonev,@identv,@namev,@profv,@addrv)); + return actor::user->db_Main->selectcol_arrayref($select, {Columns=>[scalar(@$sort)]}, map {lc($_)} (@phonev,@usrv,@identv,@namev,@profv,@addrv)); } __PACKAGE__->register_method( api_name => 'open-ils.storage.actor.user.crazy_search', diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index 98ea60b9bc..5ddf319ccc 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -208,6 +208,15 @@ $$; CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr); +-- special indexes for phone number searches +CREATE INDEX actor_usr_setting_phone_values_idx + ON actor.usr_setting (evergreen.lowercase(value)) + WHERE name IN ('opac.default_phone', 'opac.default_sms_notify'); + +CREATE INDEX actor_usr_setting_phone_values_numeric_idx + ON actor.usr_setting (evergreen.lowercase(REGEXP_REPLACE(value, '[^0-9]', '', 'g'))) + WHERE name IN ('opac.default_phone', 'opac.default_sms_notify'); + CREATE TABLE actor.stat_cat_sip_fields ( field CHAR(2) PRIMARY KEY, name TEXT NOT NULL, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.phone-settings-index.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.phone-settings-index.sql new file mode 100644 index 0000000000..426bcded49 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.phone-settings-index.sql @@ -0,0 +1,15 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version); + +-- for searching e.g. "111-111-1111" +CREATE INDEX actor_usr_setting_phone_values_idx + ON actor.usr_setting (evergreen.lowercase(value)) + WHERE name IN ('opac.default_phone', 'opac.default_sms_notify'); + +-- for searching e.g. "1111111111" +CREATE INDEX actor_usr_setting_phone_values_numeric_idx + ON actor.usr_setting (evergreen.lowercase(REGEXP_REPLACE(value, '[^0-9]', '', 'g'))) + WHERE name IN ('opac.default_phone', 'opac.default_sms_notify'); + +COMMIT; ----------------------------------------------------------------------- Summary of changes: .../OpenILS/Application/Storage/Publisher/actor.pm | 40 +++++++++++++--------- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/005.schema.actors.sql | 9 +++++ .../upgrade/1461.schema.phone-settings-index.sql | 15 ++++++++ 4 files changed, 49 insertions(+), 17 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/1461.schema.phone-settings-index.sql hooks/post-receive -- Evergreen ILS