Bug 24986: Modify some borrowers DB fields to (TINY|MEDIUM)TEXT

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

diff --git a/installer/data/mysql/atomicupdate/bug_24986.perl b/installer/data/mysql/atomicupdate/bug_24986.perl
new file mode 100644 (file)
index 0000000..c2a0433
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_24986.perl
@@ -0,0 +1,22 @@
+$DBversion = 'XXX'; # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+
+    for my $f (qw( streetnumber streettype zipcode mobile B_streetnumber B_streettype B_zipcode ) ) {
+        $dbh->do(qq|
+            ALTER TABLE borrowers MODIFY $f TINYTEXT DEFAULT NULL
+        |);
+        $dbh->do(qq|
+            ALTER TABLE deletedborrowers MODIFY $f TINYTEXT DEFAULT NULL
+        |);
+    }
+    for my $f ( qw( B_address altcontactfirstname altcontactsurname altcontactaddress1 altcontactaddress2 altcontactaddress3 altcontactzipcode altcontactphone ) ) {
+        $dbh->do(qq|
+            ALTER TABLE borrowers MODIFY $f MEDIUMTEXT DEFAULT NULL
+        |);
+        $dbh->do(qq|
+            ALTER TABLE deletedborrowers MODIFY $f MEDIUMTEXT DEFAULT NULL
+        |);
+    }
+
+    NewVersion( $DBversion, 24986, "Switch borrowers address related fields to TINYTEXT or MEDIUMTEXT");
+}

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index c5a3879..6e87aef 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -537,27 +537,27 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower
   `title` LONGTEXT, -- patron/borrower's title, for example: Mr. or Mrs.
   `othernames` LONGTEXT, -- any other names associated with the patron/borrower
   `initials` MEDIUMTEXT, -- initials for your patron/borrower
-  `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
-  `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
+  `streetnumber` TINYTEXT default NULL, -- the house number for your patron/borrower's primary address
+  `streettype` TINYTEXT default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
   `address` LONGTEXT, -- the first address line for your patron/borrower's primary address
   `address2` MEDIUMTEXT, -- the second address line for your patron/borrower's primary address
   `city` LONGTEXT, -- the city or town for your patron/borrower's primary address
   `state` MEDIUMTEXT default NULL, -- the state or province for your patron/borrower's primary address
-  `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
+  `zipcode` TINYTEXT default NULL, -- the zip or postal code for your patron/borrower's primary address
   `country` MEDIUMTEXT, -- the country for your patron/borrower's primary address
   `email` LONGTEXT, -- the primary email address for your patron/borrower's primary address
   `phone` MEDIUMTEXT, -- the primary phone number for your patron/borrower's primary address
-  `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
+  `mobile` TINYTEXT default NULL, -- the other phone number for your patron/borrower's primary address
   `fax` LONGTEXT, -- the fax number for your patron/borrower's primary address
   `emailpro` MEDIUMTEXT, -- the secondary email addres for your patron/borrower's primary address
   `phonepro` MEDIUMTEXT, -- the secondary phone number for your patron/borrower's primary address
-  `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
-  `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
-  `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
+  `B_streetnumber` TINYTEXT default NULL, -- the house number for your patron/borrower's alternate address
+  `B_streettype` TINYTEXT default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
+  `B_address` MEDIUMTEXT default NULL, -- the first address line for your patron/borrower's alternate address
   `B_address2` MEDIUMTEXT default NULL, -- the second address line for your patron/borrower's alternate address
   `B_city` LONGTEXT, -- the city or town for your patron/borrower's alternate address
   `B_state` MEDIUMTEXT default NULL, -- the state for your patron/borrower's alternate address
-  `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
+  `B_zipcode` TINYTEXT default NULL, -- the zip or postal code for your patron/borrower's alternate address
   `B_country` MEDIUMTEXT, -- the country for your patron/borrower's alternate address
   `B_email` MEDIUMTEXT, -- the patron/borrower's alternate email address
   `B_phone` LONGTEXT, -- the patron/borrower's alternate phone number
@@ -584,15 +584,15 @@ CREATE TABLE `deletedborrowers` ( -- stores data related to the patrons/borrower
   `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
   `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
   `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
-  `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower
-  `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower
-  `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
-  `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
-  `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
+  `altcontactfirstname` MEDIUMTEXT default NULL, -- first name of alternate contact for the patron/borrower
+  `altcontactsurname` MEDIUMTEXT default NULL, -- surname or last name of the alternate contact for the patron/borrower
+  `altcontactaddress1` MEDIUMTEXT default NULL, -- the first address line for the alternate contact for the patron/borrower
+  `altcontactaddress2` MEDIUMTEXT default NULL, -- the second address line for the alternate contact for the patron/borrower
+  `altcontactaddress3` MEDIUMTEXT default NULL, -- the city for the alternate contact for the patron/borrower
   `altcontactstate` MEDIUMTEXT default NULL, -- the state for the alternate contact for the patron/borrower
-  `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
+  `altcontactzipcode` MEDIUMTEXT default NULL, -- the zipcode for the alternate contact for the patron/borrower
   `altcontactcountry` MEDIUMTEXT default NULL, -- the country for the alternate contact for the patron/borrower
-  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
+  `altcontactphone` MEDIUMTEXT default NULL, -- the phone number for the alternate contact for the patron/borrower
   `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
   `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
   `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history  KEY `borrowernumber` (`borrowernumber`),
@@ -1457,27 +1457,27 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons
   `title` LONGTEXT, -- patron/borrower's title, for example: Mr. or Mrs.
   `othernames` LONGTEXT, -- any other names associated with the patron/borrower
   `initials` MEDIUMTEXT, -- initials for your patron/borrower
-  `streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's primary address
-  `streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
+  `streetnumber` TINYTEXT default NULL, -- the house number for your patron/borrower's primary address
+  `streettype` TINYTEXT default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's primary address
   `address` LONGTEXT, -- the first address line for your patron/borrower's primary address
   `address2` MEDIUMTEXT, -- the second address line for your patron/borrower's primary address
   `city` LONGTEXT, -- the city or town for your patron/borrower's primary address
   `state` MEDIUMTEXT default NULL, -- the state or province for your patron/borrower's primary address
-  `zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's primary address
+  `zipcode` TINYTEXT default NULL, -- the zip or postal code for your patron/borrower's primary address
   `country` MEDIUMTEXT, -- the country for your patron/borrower's primary address
   `email` LONGTEXT, -- the primary email address for your patron/borrower's primary address
   `phone` MEDIUMTEXT, -- the primary phone number for your patron/borrower's primary address
-  `mobile` varchar(50) default NULL, -- the other phone number for your patron/borrower's primary address
+  `mobile` TINYTEXT default NULL, -- the other phone number for your patron/borrower's primary address
   `fax` LONGTEXT, -- the fax number for your patron/borrower's primary address
   `emailpro` MEDIUMTEXT, -- the secondary email addres for your patron/borrower's primary address
   `phonepro` MEDIUMTEXT, -- the secondary phone number for your patron/borrower's primary address
-  `B_streetnumber` varchar(10) default NULL, -- the house number for your patron/borrower's alternate address
-  `B_streettype` varchar(50) default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
-  `B_address` varchar(100) default NULL, -- the first address line for your patron/borrower's alternate address
+  `B_streetnumber` TINYTEXT default NULL, -- the house number for your patron/borrower's alternate address
+  `B_streettype` TINYTEXT default NULL, -- the street type (Rd., Blvd, etc) for your patron/borrower's alternate address
+  `B_address` MEDIUMTEXT default NULL, -- the first address line for your patron/borrower's alternate address
   `B_address2` MEDIUMTEXT default NULL, -- the second address line for your patron/borrower's alternate address
   `B_city` LONGTEXT, -- the city or town for your patron/borrower's alternate address
   `B_state` MEDIUMTEXT default NULL, -- the state for your patron/borrower's alternate address
-  `B_zipcode` varchar(25) default NULL, -- the zip or postal code for your patron/borrower's alternate address
+  `B_zipcode` TINYTEXT default NULL, -- the zip or postal code for your patron/borrower's alternate address
   `B_country` MEDIUMTEXT, -- the country for your patron/borrower's alternate address
   `B_email` MEDIUMTEXT, -- the patron/borrower's alternate email address
   `B_phone` LONGTEXT, -- the patron/borrower's alternate phone number
@@ -1504,15 +1504,15 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons
   `contactnote` varchar(255) default NULL, -- a note related to the patron/borrower's alternate address
   `sort1` varchar(80) default NULL, -- a field that can be used for any information unique to the library
   `sort2` varchar(80) default NULL, -- a field that can be used for any information unique to the library
-  `altcontactfirstname` varchar(255) default NULL, -- first name of alternate contact for the patron/borrower
-  `altcontactsurname` varchar(255) default NULL, -- surname or last name of the alternate contact for the patron/borrower
-  `altcontactaddress1` varchar(255) default NULL, -- the first address line for the alternate contact for the patron/borrower
-  `altcontactaddress2` varchar(255) default NULL, -- the second address line for the alternate contact for the patron/borrower
-  `altcontactaddress3` varchar(255) default NULL, -- the city for the alternate contact for the patron/borrower
+  `altcontactfirstname` MEDIUMTEXT  default NULL, -- first name of alternate contact for the patron/borrower
+  `altcontactsurname` MEDIUMTEXT default NULL, -- surname or last name of the alternate contact for the patron/borrower
+  `altcontactaddress1` MEDIUMTEXT default NULL, -- the first address line for the alternate contact for the patron/borrower
+  `altcontactaddress2` MEDIUMTEXT default NULL, -- the second address line for the alternate contact for the patron/borrower
+  `altcontactaddress3` MEDIUMTEXT default NULL, -- the city for the alternate contact for the patron/borrower
   `altcontactstate` MEDIUMTEXT default NULL, -- the state for the alternate contact for the patron/borrower
-  `altcontactzipcode` varchar(50) default NULL, -- the zipcode for the alternate contact for the patron/borrower
+  `altcontactzipcode` MEDIUMTEXT default NULL, -- the zipcode for the alternate contact for the patron/borrower
   `altcontactcountry` MEDIUMTEXT default NULL, -- the country for the alternate contact for the patron/borrower
-  `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
+  `altcontactphone` MEDIUMTEXT default NULL, -- the phone number for the alternate contact for the patron/borrower
   `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SMS turned on)
   `sms_provider_id` int(11) DEFAULT NULL, -- the provider of the mobile phone number defined in smsalertnumber
   `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history