From 13b35c0632be8b9c04ffa68ed95ecdd186936e1a Mon Sep 17 00:00:00 2001
From: Nick Clemens <nick@bywatersolutions.com>
Date: Mon, 3 Jan 2022 15:21:52 +0000
Subject: [PATCH] Bug 28633: DB update

This adds the field to necessary tables and updates API and columns def

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
---
 api/v1/swagger/definitions/patron.yaml        |  5 ++
 .../data/mysql/atomicupdate/bug_28633.pl      | 53 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  3 ++
 3 files changed, 61 insertions(+)
 create mode 100755 installer/data/mysql/atomicupdate/bug_28633.pl

diff --git a/api/v1/swagger/definitions/patron.yaml b/api/v1/swagger/definitions/patron.yaml
index 26f7b81948..331989f303 100644
--- a/api/v1/swagger/definitions/patron.yaml
+++ b/api/v1/swagger/definitions/patron.yaml
@@ -19,6 +19,11 @@ properties:
       - string
       - "null"
     description: patron's first name
+  preferred_name:
+    type:
+      - string
+      - "null"
+    description: patron's preferred name
   middle_name:
     type:
       - string
diff --git a/installer/data/mysql/atomicupdate/bug_28633.pl b/installer/data/mysql/atomicupdate/bug_28633.pl
new file mode 100755
index 0000000000..3121810030
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_28633.pl
@@ -0,0 +1,53 @@
+use Modern::Perl;
+
+return {
+    bug_number  => "28633",
+    description => "Add preferred_name to borrowers table",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+        if ( !column_exists( 'borrowers', 'preferred_name' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE borrowers
+                ADD COLUMN preferred_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's preferred name"
+                AFTER firstname
+            }
+            );
+            say $out "Added preferred name column to borrowers table";
+        }
+        if ( !column_exists( 'deletedborrowers', 'preferred_name' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE deletedborrowers
+                ADD COLUMN preferred_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's preferred name"
+                AFTER firstname
+            }
+            );
+            say $out "Added preferred name column to deletedborrowers table";
+        }
+        if ( !column_exists( 'borrower_modifications', 'preferred_name' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE borrower_modifications
+                ADD COLUMN preferred_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's preferred name"
+                AFTER firstname
+            }
+            );
+            say $out "Added preferred name column to borrower_modifications table";
+        }
+        my @default_patron_search_fields = split( '\|', C4::Context->preference('DefaultPatronSearchFields') );
+        unless ( grep /preferred_name/, @default_patron_search_fields ) {
+            if ( grep /firstname/, @default_patron_search_fields ) {
+                push @default_patron_search_fields, 'preferred_name';
+                C4::Context->set_preference( 'DefaultPatronSearchFields', join( '|', @default_patron_search_fields ) );
+                say $out "Added preferred name to DefaultPatronSearchFields";
+            } else {
+                say $out "Please add 'preferred_name' to DefaultPatronSearchFields if you want it searched by default";
+            }
+        }
+    },
+    }
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 1d394f32fa..2ba68277a3 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1401,6 +1401,7 @@ CREATE TABLE `borrower_modifications` (
   `cardnumber` varchar(32) DEFAULT NULL,
   `surname` longtext DEFAULT NULL,
   `firstname` mediumtext DEFAULT NULL,
+  `preferred_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s preferred name',
   `middle_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext DEFAULT NULL,
   `othernames` longtext DEFAULT NULL,
@@ -1522,6 +1523,7 @@ CREATE TABLE `borrowers` (
   `cardnumber` varchar(32) DEFAULT NULL COMMENT 'unique key, library assigned ID number for patrons/borrowers',
   `surname` longtext DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
   `firstname` mediumtext DEFAULT NULL COMMENT 'patron/borrower''s first name',
+  `preferred_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s preferred name',
   `middle_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
   `othernames` longtext DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
@@ -2658,6 +2660,7 @@ CREATE TABLE `deletedborrowers` (
   `cardnumber` varchar(32) DEFAULT NULL COMMENT 'unique key, library assigned ID number for patrons/borrowers',
   `surname` longtext DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
   `firstname` mediumtext DEFAULT NULL COMMENT 'patron/borrower''s first name',
+  `preferred_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s preferred name',
   `middle_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
   `othernames` longtext DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
-- 
2.34.1