From 40fe2515ceca4bf62d601a16f26da2ea76549bcd Mon Sep 17 00:00:00 2001 From: senator Date: Thu, 5 May 2011 16:40:08 +0000 Subject: [PATCH] Patch from Niles Ingalls to make patron phone number searching more matchy. With assistance from Mike Rylander on pointing out the needed indexes to keep search fast, and from Mike Peters on arranging a last minute DCO :-) Type numbers into a patron phone number field and get a match regardless of the punctuation actually used in the database. git-svn-id: svn://svn.open-ils.org/ILS/trunk@20429 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../OpenILS/Application/Storage/Publisher/actor.pm | 6 +++++- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/005.schema.actors.sql | 9 +++++++++ ...30.schema.actor-usr-index-phone-fields-more.sql | 14 ++++++++++++++ 4 files changed, 29 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0530.schema.actor-usr-index-phone-fields-more.sql 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 cd3d4b5..1cb3dce 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 @@ -680,7 +680,11 @@ sub patron_search { my @phonev; if ($pv) { for my $p ( qw/day_phone evening_phone other_phone/ ) { - push @ps, "evergreen.lowercase($p) ~ ?"; + if ($pv =~ /^\d+$/) { + push @ps, "evergreen.lowercase(REGEXP_REPLACE($p, '[^0-9]', '', 'g')) ~ ?"; + } else { + push @ps, "evergreen.lowercase($p) ~ ?"; + } push @phonev, "^$pv"; } $phone = '(' . join(' OR ', @ps) . ')'; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index ff48b1e..cf59bc0 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 ('0529', :eg_version); -- miker, for Joseph Lewis and Michael Peters +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0530', :eg_version); -- senator, for Niles Ingalls CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index f87ffa0..d56e644 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -88,6 +88,15 @@ CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone)); CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone)); +CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE + (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g'))); + +CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE + (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g'))); + +CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE + (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g'))); + CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value)); CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2)); diff --git a/Open-ILS/src/sql/Pg/upgrade/0530.schema.actor-usr-index-phone-fields-more.sql b/Open-ILS/src/sql/Pg/upgrade/0530.schema.actor-usr-index-phone-fields-more.sql new file mode 100644 index 0000000..c8dd6d3 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0530.schema.actor-usr-index-phone-fields-more.sql @@ -0,0 +1,14 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0530'); -- senator + +CREATE INDEX actor_usr_day_phone_idx_numeric ON actor.usr USING BTREE + (evergreen.lowercase(REGEXP_REPLACE(day_phone, '[^0-9]', '', 'g'))); + +CREATE INDEX actor_usr_evening_phone_idx_numeric ON actor.usr USING BTREE + (evergreen.lowercase(REGEXP_REPLACE(evening_phone, '[^0-9]', '', 'g'))); + +CREATE INDEX actor_usr_other_phone_idx_numeric ON actor.usr USING BTREE + (evergreen.lowercase(REGEXP_REPLACE(other_phone, '[^0-9]', '', 'g'))); + +COMMIT; -- 1.7.2.5