From e171495ea9c444737e4ba479b858309475e53dba Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Tue, 4 Sep 2018 10:40:29 +0200 Subject: [PATCH] Bug 5458: increase ccode column to varchar 80 Koha allows you to define an authorised value having length 80, but then you can't use it. Here is why: The SQL field items.ccode is a varchar(10). The SQL field authorised_values.authorised_value is a varchar(80). Therefore the authorised value is truncated to length 10 in items.ccode and the mapping from authorised value to description string fails in Koha. This patch increases ccode column to varchar 80, like location. Test plan : 1) Dont apply patches 2) Check there is on item field a subfield with authorised value category CCODE 3) Check this subfield is linked with database column items.ccode 4) Create an authorised values in category CCODE with 'DOCUMENTATION' 5) Edit an item on ccode and choose 'DOCUMENTATION' 6) Save item 7) Look at items.ccode in database or export in MARCXML 8) The value is troncated to 'DOCUMENTAT' 9) Apply patches and update database with installer/data/mysql/atomicupdate/bug_5458.sql 10) Repeate step 5 to 7 11) The value is not truncated 'DOCUMENTATION' Signed-off-by: Michal Denar Signed-off-by: Martin Renvoize --- installer/data/mysql/atomicupdate/bug_5458.sql | 4 ++++ installer/data/mysql/kohastructure.sql | 10 +++++----- 2 files changed, 9 insertions(+), 5 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_5458.sql diff --git a/installer/data/mysql/atomicupdate/bug_5458.sql b/installer/data/mysql/atomicupdate/bug_5458.sql new file mode 100644 index 0000000000..2fa22afb66 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_5458.sql @@ -0,0 +1,4 @@ +ALTER TABLE items CHANGE COLUMN ccode ccode varchar(80) default NULL; +ALTER TABLE deleteditems CHANGE COLUMN ccode ccode varchar(80) default NULL; +ALTER TABLE branch_transfer_limits CHANGE COLUMN ccode ccode varchar(80) default NULL; +ALTER TABLE course_items CHANGE COLUMN ccode ccode varchar(80) default NULL; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 49a0f180be..95c36175d2 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -662,7 +662,7 @@ CREATE TABLE `deleteditems` ( `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and due date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) `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) + `ccode` varchar(80) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` MEDIUMTEXT default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y) @@ -924,7 +924,7 @@ CREATE TABLE `items` ( -- holdings/item information `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and due date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) `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) + `ccode` varchar(80) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` MEDIUMTEXT default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y) @@ -1989,7 +1989,7 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation `location` varchar(80) default NULL, -- authorized value for the shelving location for this item (MARC21 952$c) `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower `associatedborrower` int(11) default NULL, -- unused in Koha - `ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code + `ccode` varchar(80) default NULL, -- foreign key from the items table, links transaction to a specific collection code KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `proccode_idx` (`proccode`), @@ -2631,7 +2631,7 @@ CREATE TABLE branch_transfer_limits ( toBranch varchar(10) NOT NULL, fromBranch varchar(10) NOT NULL, itemtype varchar(10) NULL, - ccode varchar(10) NULL, + ccode varchar(80) NULL, PRIMARY KEY (limitId) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -3800,7 +3800,7 @@ CREATE TABLE `course_items` ( `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) - `ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) + `ccode` varchar(80) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) `enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no' -- 2.18.0