From 16d71e901e1d0394499ef1385b02fa726fcb7b60 Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Tue, 11 Apr 2023 17:33:55 +0000 Subject: [PATCH] Bug 33489: Add indices to default patron search fields This patch adds indices to the borrowers table to match the default search fields for patrons. To test: 1 - Apply patch 2 - Update database 3 - Ensure patron searching works as before --- installer/data/mysql/atomicupdate/bz_33489.pl | 23 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 +++ 2 files changed, 26 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bz_33489.pl diff --git a/installer/data/mysql/atomicupdate/bz_33489.pl b/installer/data/mysql/atomicupdate/bz_33489.pl new file mode 100755 index 0000000000..3026eb5458 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bz_33489.pl @@ -0,0 +1,23 @@ +use Modern::Perl; + +return { + bug_number => "33489", + description => "Add indices to default patron search fields", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + # Do you stuffs here + unless ( index_exists( 'borrowers', 'cardnumber_idx' ) ) { + $dbh->do(q{CREATE INDEX cardnumber_idx ON borrowers ( cardnumber )}); + say $out "Added new index on borrowers.cardnumber"; + } + unless ( index_exists( 'borrowers', 'userid_idx' ) ) { + $dbh->do(q{CREATE INDEX userid_idx ON borrowers ( userid )}); + say $out "Added new index on borrowers.userid"; + } + unless ( index_exists( 'borrowers', 'middle_name_idx' ) ) { + $dbh->do(q{CREATE INDEX middle_name_idx ON borrowers ( middle_name )}); + say $out "Added new index on borrowers.midddle_name"; + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index cba889d5e0..543f752eb7 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1488,6 +1488,9 @@ CREATE TABLE `borrowers` ( KEY `firstname_idx` (`firstname`(191)), KEY `othernames_idx` (`othernames`(191)), KEY `sms_provider_id` (`sms_provider_id`), + KEY `cardnumber_idx` (`cardnumber`), + KEY `userid_idx` (`userid`), + KEY `middle_name_idx` (`middle_name`(768)), CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE -- 2.30.2