From b5799ebda078c7ddc93d85d6947eb34d8e4bbc25 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 18 May 2021 11:53:29 +0200 Subject: [PATCH] Bug 28267: Modify varchar from items to (TINY)TEXT Note that barcode, branchcode and holdingbranch have not been modified. --- .../data/mysql/atomicupdate/bug_28267.perl | 66 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 66 ++++++++++--------- 2 files changed, 102 insertions(+), 30 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_28267.perl diff --git a/installer/data/mysql/atomicupdate/bug_28267.perl b/installer/data/mysql/atomicupdate/bug_28267.perl new file mode 100644 index 00000000000..7a68f859ea7 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_28267.perl @@ -0,0 +1,66 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + my $fields = { + itemcallnumber => 255, + coded_location_qualifier => 10, + location => 80, + permanent_location => 80, + cn_source => 10, + cn_sort => 255, + ccode => 80, + itype => 10, + copynumber => 32, + stocknumber => 32, + new_status => 32, + }; + # Index on items + for my $index ( qw( itemstocknumberidx itemcallnumber items_location items_ccode itype_idx ) ) { + next unless index_exists('items', $index); + $dbh->do(qq| + ALTER TABLE items DROP INDEX $index + |); + } + + # Index on deleteditems + for my $index ( qw( delitemstocknumberidx itype_idx ) ) { + next unless index_exists('deleteditems', $index); + $dbh->do(qq| + ALTER TABLE deleteditems DROP INDEX $index + |); + } + + while ( my ( $f, $size ) = each %$fields ) { + my $datatype = ( $size >= 24 ) ? 'TEXT' : 'TINYTEXT'; + my $size_index = ( $size == 255 ) ? 191 : $size; + + $dbh->do(qq| + ALTER TABLE items MODIFY $f $datatype DEFAULT NULL + |); + + # Re-add index on items + unless ( index_exists( 'items', sprintf("%s_idx", $f) ) ) { + $dbh->do(sprintf q| + ALTER TABLE items ADD INDEX %s_idx( %s(%s) ) + |, $f, $f, $size_index); + } + + $dbh->do(qq| + ALTER TABLE deleteditems MODIFY $f $datatype DEFAULT NULL + |); + } + + # Re-add index on deleteditems + unless ( index_exists( 'deleteditems', 'itype_idx' ) ) { + $dbh->do(q| + ALTER TABLE deleteditems ADD INDEX itype_idx( itype(10)) + |); + } + unless ( index_exists( 'deleteditems', 'stocknumber_idx' ) ) { + $dbh->do(q| + ALTER TABLE deleteditems ADD INDEX stocknumber_idx( itype(32)) + |); + } + + NewVersion( $DBversion, 28267, "Switch items VARCHAR fields to TINYTEXT or MEDIUMTEXT"); +} + diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4896b9dcee9..957af5b679a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2464,8 +2464,8 @@ CREATE TABLE `deleteditems` ( `itemlost_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as lost, NULL if not lost', `withdrawn` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'authorized value defining this item as withdrawn (MARC21 952$0)', `withdrawn_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as withdrawn, NULL if not withdrawn', - `itemcallnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)', - `coded_location_qualifier` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)', + `itemcallnumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)', + `coded_location_qualifier` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)', `issues` smallint(6) DEFAULT 0 COMMENT 'number of times this item has been checked out', `renewals` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been renewed', `reserves` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been placed on hold/reserved', @@ -2474,30 +2474,30 @@ CREATE TABLE `deleteditems` ( `itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)', `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)', `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered', - `location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)', - `permanent_location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location', + `location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)', + `permanent_location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location', `onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)', - `cn_source` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)', - `cn_sort` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting', - `ccode` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)', + `cn_source` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)', + `cn_sort` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting', + `ccode` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)', `materials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'materials specified (MARC21 952$3)', `uri` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL for the item (MARC21 952$u)', - `itype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)', + `itype` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)', `more_subfields_xml` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'additional 952 subfields in XML format', `enumchron` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'serial enumeration/chronology for the item (MARC21 952$h)', - `copynumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)', - `stocknumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)', - `new_status` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.', + `copynumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)', + `stocknumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)', + `new_status` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.', `exclude_from_local_holds_priority` tinyint(1) DEFAULT NULL COMMENT 'Exclude this item from local holds priority', PRIMARY KEY (`itemnumber`), KEY `delitembarcodeidx` (`barcode`), - KEY `delitemstocknumberidx` (`stocknumber`), KEY `delitembinoidx` (`biblioitemnumber`), KEY `delitembibnoidx` (`biblionumber`), KEY `delhomebranch` (`homebranch`), KEY `delholdingbranch` (`holdingbranch`), - KEY `itype_idx` (`itype`), - KEY `timestamp` (`timestamp`) + KEY `timestamp` (`timestamp`), + KEY `itype_idx` (`itype`(10)), + KEY `stocknumber_idx` (`itype`(32)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; @@ -3022,8 +3022,8 @@ CREATE TABLE `items` ( `itemlost_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as lost, NULL if not lost', `withdrawn` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'authorized value defining this item as withdrawn (MARC21 952$0)', `withdrawn_on` datetime DEFAULT NULL COMMENT 'the date and time an item was last marked as withdrawn, NULL if not withdrawn', - `itemcallnumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)', - `coded_location_qualifier` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)', + `itemcallnumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'call number for this item (MARC21 952$o)', + `coded_location_qualifier` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'coded location qualifier(MARC21 952$f)', `issues` smallint(6) DEFAULT 0 COMMENT 'number of times this item has been checked out/issued', `renewals` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been renewed', `reserves` smallint(6) DEFAULT NULL COMMENT 'number of times this item has been placed on hold/reserved', @@ -3032,33 +3032,39 @@ CREATE TABLE `items` ( `itemnotes_nonpublic` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'non-public notes on this item (MARC21 952$x)', `holdingbranch` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table for the library that is currently in possession item (MARC21 952$b)', `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'date and time this item was last altered', - `location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)', - `permanent_location` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location', + `location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the shelving location for this item (MARC21 952$c)', + `permanent_location` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'linked to the CART and PROC temporary locations feature, stores the permanent shelving location', `onloan` date DEFAULT NULL COMMENT 'defines if item is checked out (NULL for not checked out, and due date for checked out)', - `cn_source` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)', - `cn_sort` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting', - `ccode` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)', + `cn_source` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'classification source used on this item (MARC21 952$2)', + `cn_sort` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'normalized form of the call number (MARC21 952$o) used for sorting', + `ccode` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'authorized value for the collection code associated with this item (MARC21 952$8)', `materials` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'materials specified (MARC21 952$3)', `uri` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL for the item (MARC21 952$u)', - `itype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)', + `itype` TINYTEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the itemtypes table defining the type for this item (MARC21 952$y)', `more_subfields_xml` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'additional 952 subfields in XML format', `enumchron` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'serial enumeration/chronology for the item (MARC21 952$h)', - `copynumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)', - `stocknumber` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)', - `new_status` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.', + `copynumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'copy number (MARC21 952$t)', + `stocknumber` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'inventory number (MARC21 952$i)', + `new_status` TEXT COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '''new'' value, you can put whatever free-text information. This field is intented to be managed by the automatic_item_modification_by_age cronjob.', `exclude_from_local_holds_priority` tinyint(1) DEFAULT NULL COMMENT 'Exclude this item from local holds priority', PRIMARY KEY (`itemnumber`), UNIQUE KEY `itembarcodeidx` (`barcode`), - KEY `itemstocknumberidx` (`stocknumber`), KEY `itembinoidx` (`biblioitemnumber`), KEY `itembibnoidx` (`biblionumber`), KEY `homebranch` (`homebranch`), KEY `holdingbranch` (`holdingbranch`), - KEY `itemcallnumber` (`itemcallnumber`(191)), - KEY `items_location` (`location`), - KEY `items_ccode` (`ccode`), - KEY `itype_idx` (`itype`), KEY `timestamp` (`timestamp`), + KEY `coded_location_qualifier_idx` (`coded_location_qualifier`(10)), + KEY `itemcallnumber_idx` (`itemcallnumber`(191)), + KEY `permanent_location_idx` (`permanent_location`(80)), + KEY `location_idx` (`location`(80)), + KEY `stocknumber_idx` (`stocknumber`(32)), + KEY `cn_sort_idx` (`cn_sort`(191)), + KEY `cn_source_idx` (`cn_source`(10)), + KEY `new_status_idx` (`new_status`(32)), + KEY `itype_idx` (`itype`(10)), + KEY `copynumber_idx` (`copynumber`(32)), + KEY `ccode_idx` (`ccode`(80)), CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, -- 2.20.1