From e99b9aca78218769c4b02fdb4c676d8977a29d2e Mon Sep 17 00:00:00 2001 From: simith Date: Thu, 21 Aug 2014 14:42:04 -0400 Subject: [PATCH] [PASSED QA] Bug 12424 - ddc sorting of call numbers truncates long Cutter parts This patch increases the cn_sort field length to 255 chars. Modified: C4/ClassSortRoutine.pm - change the hardcoded cn_sort length to 255 installer/data/mysql/kohastructure.sql - alter tables items and deleteditems, biblioitems and deletedbiblioitems installer/data/mysql/updatedatabase.pl - alter tables items and deleteditems, biblioitems and deletedbiblioitems Testing: Before applying the patch: 0) Select an item 1) Edit the item selected 2) Change "Full call number" option to 530 F435_1996 v2p1 3) Save changes 4) Valide 530_000000000000000_F435_1996_ (table items - cn_sort column). After applying the patch: 5) Edit again the item selected in 0 3) Save changes 4) Valide 530_000000000000000_F435_1996_V2P1 (table items - cn_sort column). Signed-off-by: Chris Cormack Signed-off-by: Katrin Fischer Passes tests and QA script. Works as described. --- C4/ClassSortRoutine.pm | 2 +- installer/data/mysql/kohastructure.sql | 31 +++++++++++++++---------------- installer/data/mysql/updatedatabase.pl | 9 +++++++++ 3 files changed, 25 insertions(+), 17 deletions(-) diff --git a/C4/ClassSortRoutine.pm b/C4/ClassSortRoutine.pm index 04c24a5..8cd5334 100644 --- a/C4/ClassSortRoutine.pm +++ b/C4/ClassSortRoutine.pm @@ -101,7 +101,7 @@ sub GetClassSortKey { # should replace with some way of getting column widths from # the DB schema -- since doing this should ideally be # independent of the DBMS, deferring for the moment. - return substr($key, 0, 30); + return substr($key, 0, 255); } =head2 _get_class_sort_key diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index aeeba17..e33c168 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2,7 +2,7 @@ -- -- Host: localhost Database: koha30test -- ------------------------------------------------------ --- Server version 4.1.22 +-- Server version 4.1.22 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -178,7 +178,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `cn_class` varchar(30) default NULL, `cn_item` varchar(10) default NULL, `cn_suffix` varchar(10) default NULL, - `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting + `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a) `totalissues` int(10), `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML @@ -783,7 +783,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t `cn_class` varchar(30) default NULL, `cn_item` varchar(10) default NULL, `cn_suffix` varchar(10) default NULL, - `cn_sort` varchar(30) default NULL, -- normalized version of the call number used for sorting + `cn_sort` varchar(255) default NULL, -- normalized version of the call number used for sorting `agerestriction` varchar(255) default NULL, -- target audience/age restriction from the bib record (MARC21 521$a) `totalissues` int(10), `marcxml` longtext NOT NULL, -- full bibliographic MARC record in MARCXML @@ -911,7 +911,7 @@ CREATE TABLE `deleteditems` ( `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) - `cn_sort` varchar(30) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting + `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` text default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) @@ -1209,7 +1209,7 @@ CREATE TABLE `items` ( -- holdings/item information `permanent_location` varchar(80) default NULL, -- linked to the CART and PROC temporary locations feature, stores the permanent shelving location `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) - `cn_sort` varchar(30) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting + `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` text default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) @@ -2177,10 +2177,10 @@ CREATE TABLE `suggestions` ( -- purchase suggestions branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table collectiontitle text default NULL, -- collection name for the suggested item itemtype VARCHAR(30) default NULL, -- suggested item type - quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased - currency VARCHAR(3) default NULL, -- suggested currency for the suggested price - price DECIMAL(28,6) default NULL, -- suggested price - total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) + quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased + currency VARCHAR(3) default NULL, -- suggested currency for the suggested price + price DECIMAL(28,6) default NULL, -- suggested price + total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) PRIMARY KEY (`suggestionid`), KEY `suggestedby` (`suggestedby`), KEY `managedby` (`managedby`) @@ -2478,12 +2478,12 @@ CREATE TABLE `permissions` ( DROP TABLE IF EXISTS `serialitems`; CREATE TABLE `serialitems` ( - `itemnumber` int(11) NOT NULL, - `serialid` int(11) NOT NULL, - UNIQUE KEY `serialitemsidx` (`itemnumber`), - KEY `serialitems_sfk_1` (`serialid`), - CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE + `itemnumber` int(11) NOT NULL, + `serialid` int(11) NOT NULL, + UNIQUE KEY `serialitemsidx` (`itemnumber`), + KEY `serialitems_sfk_1` (`serialid`), + CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -3464,4 +3464,3 @@ CREATE TABLE IF NOT EXISTS columns_settings ( /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; - diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index d6b7d24..3fa3518 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8800,6 +8800,15 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do("ALTER TABLE `items` CHANGE `cn_sort` `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL"); + $dbh->do("ALTER TABLE `deleteditems` CHANGE `cn_sort` `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL"); + $dbh->do("ALTER TABLE `biblioitems` CHANGE `cn_sort` `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL"); + $dbh->do("ALTER TABLE `deletedbiblioitems` CHANGE `cn_sort` `cn_sort` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL"); + print "Upgrade to $DBversion done (Bug 12424 - ddc sorting of call numbers truncates long Cutter parts)\n"; + SetVersion ($DBversion); +} =head1 FUNCTIONS -- 1.9.1