From f15b5d89c0a75bbb68f12b13e45bf0b54d15ddfd Mon Sep 17 00:00:00 2001
From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Date: Tue, 19 Apr 2022 15:34:31 +0100
Subject: [PATCH] Bug 21978: Add middle_name field to the database

This patch adds a new field, middle_name, to the borrowers,
deletedborrowers and borrower_modifications tables.

It also updates the DefaultPatronSearchFields preference to include the
new field if the preference is still set to it's default settings from
install.

Sponsored-by: Cheshire Libraries

Signed-off-by: Sally <sally.healey@cheshiresharedservices.gov.uk>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
---
 .../data/mysql/atomicupdate/bug_21978.pl      | 47 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  3 ++
 2 files changed, 50 insertions(+)
 create mode 100755 installer/data/mysql/atomicupdate/bug_21978.pl

diff --git a/installer/data/mysql/atomicupdate/bug_21978.pl b/installer/data/mysql/atomicupdate/bug_21978.pl
new file mode 100755
index 0000000000..8abb859971
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_21978.pl
@@ -0,0 +1,47 @@
+use Modern::Perl;
+
+return {
+    bug_number => "21978",
+    description => "Add middle_name to borrowers table",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+        if( !column_exists( 'borrowers', 'middle_name' ) ) {
+            $dbh->do(q{
+                ALTER TABLE borrowers
+                ADD COLUMN middle_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's middle name"
+                AFTER firstname
+            });
+            say $out "Added middle name column to borrowers table";
+        }
+        if( !column_exists( 'deletedborrowers', 'middle_name' ) ) {
+            $dbh->do(q{
+                ALTER TABLE deletedborrowers
+                ADD COLUMN middle_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's middle name"
+                AFTER firstname
+            });
+            say $out "Added middle name column to deletedborrowers table";
+        }
+        if( !column_exists( 'borrower_modifications', 'middle_name' ) ) {
+            $dbh->do(q{
+                ALTER TABLE borrower_modifications
+                ADD COLUMN middle_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's middle name"
+                AFTER firstname
+            });
+            say $out "Added middle name column to borrower_modifications table";
+        }
+        my @default_patron_search_fields = split(',',C4::Context->preference('DefaultPatronSearchFields'));
+        unless( grep /middle_name/, @default_patron_search_fields ){
+            if( grep /firstname/, @default_patron_search_fields ){
+                push @default_patron_search_fields,'middle_name';
+                C4::Context->set_preference('DefaultPatronSearchFields', join(',',@default_patron_search_fields) );
+                say $out "Added middle name to DefaultPatronSearchFields";
+            } else {
+                say $out "Please add 'middlename' to DefaultPatronSearchFields if you want it searched by default";
+            }
+        }
+    },
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 027ca181c0..0d009afe59 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1272,6 +1272,7 @@ CREATE TABLE `borrower_modifications` (
   `cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `surname` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `firstname` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `middle_name` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `othernames` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `initials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
@@ -1390,6 +1391,7 @@ CREATE TABLE `borrowers` (
   `cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'unique key, library assigned ID number for patrons/borrowers',
   `surname` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
   `firstname` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s first name',
+  `middle_name` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
   `othernames` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
   `initials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'initials for your patron/borrower',
@@ -2403,6 +2405,7 @@ CREATE TABLE `deletedborrowers` (
   `cardnumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'unique key, library assigned ID number for patrons/borrowers',
   `surname` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
   `firstname` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s first name',
+  `middle_name` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
   `othernames` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
   `initials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'initials for your patron/borrower',
-- 
2.20.1