Bugzilla – Attachment 121103 Details for
Bug 28267
Older databases fail to upgrade due to having a row format other than "DYNAMIC"
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 28267: Modify varchar from items to (TINY)TEXT
Bug-28267-Modify-varchar-from-items-to-TINYTEXT.patch (text/plain), 15.11 KB, created by
Jonathan Druart
on 2021-05-18 09:54:53 UTC
(
hide
)
Description:
Bug 28267: Modify varchar from items to (TINY)TEXT
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2021-05-18 09:54:53 UTC
Size:
15.11 KB
patch
obsolete
>From b5799ebda078c7ddc93d85d6947eb34d8e4bbc25 Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 28267
:
121103
|
145380
|
145386
|
145390
|
145507
|
150627
|
150628
|
150645
|
150646
|
154481
|
154482