From 16cadc5dab2e095859a8ea494f8edd6c9eca0758 Mon Sep 17 00:00:00 2001 From: Katrin Fischer Date: Fri, 16 Jun 2023 11:14:29 +0000 Subject: [PATCH] Bug 34029: Extend datatypes of biblioitems.columns to longtext to avoid breaking import This updated the datatype of the following columns from varchar(255) to longtext to avoid import problems if the mapped MARC field's data exceeds 255 characters. This is for: * place (260$a and 264$a) * publishercode (260$b and 264$b) * size (300$c) * illus (300$b) * pages (300$a) To test: * Edit a bibliographic record and enter more than 255 characters into the MARC fields listed above. Try to save. * Run the updatedatabase script to modify the DB structure * restart_all * Edit a bibliographic record and enter more than 255 characters into the MARC fields listed above. * Save * Export the record * Import the record using staging tools, all should work well --- .../atomicupdate/bug_34029_biblioitems.pl | 65 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 20 +++--- 2 files changed, 75 insertions(+), 10 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_34029_biblioitems.pl diff --git a/installer/data/mysql/atomicupdate/bug_34029_biblioitems.pl b/installer/data/mysql/atomicupdate/bug_34029_biblioitems.pl new file mode 100755 index 0000000000..701b7170c2 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_34029_biblioitems.pl @@ -0,0 +1,65 @@ +use Modern::Perl; + +return { + bug_number => "34029", + description => "Extend datatypes in biblioitems and deletedbiblioitems tables to avoid import errors", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `place` longtext DEFAULT NULL COMMENT 'publication place (MARC21 260$a and 264$a)' + }); + say $out "Updated biblioitems.place to longtext"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `place` longtext DEFAULT NULL COMMENT 'publication place (MARC21 260$a and 264$a)' + }); + say $out "Updated deletedbiblioitems.place to longtext"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `publishercode` longtext DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 264$b)' + }); + say $out "Updated biblioitems.publishercode to longtext"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `publishercode` longtext DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 264$b)' + }); + say $out "Updated deletedbiblioitems.publishercode to longtext"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `size` longtext DEFAULT NULL COMMENT 'material size (MARC21 300$c)' + }); + say $out "Updated biblioitems.size to longtext"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `size` longtext DEFAULT NULL COMMENT 'material size (MARC21 300$c)' + }); + say $out "Updated deletedbiblioitems.size to longtext"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `pages` longtext DEFAULT NULL COMMENT 'number of pages (MARC21 300$a)' + }); + say $out "Updated biblioitems.pages to longtext"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `pages` longtext DEFAULT NULL COMMENT 'number of pages (MARC21 300$a)' + }); + say $out "Updated deletedbiblioitems.pages to longtext"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `illus` longtext DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)' + }); + say $out "Updated biblioitems.illus to longtext"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `illus` longtext DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)' + }); + say $out "Updated deletedbiblioitems.illus to longtext"; + + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3f666d2df9..a5182e15c5 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1123,7 +1123,7 @@ CREATE TABLE `biblioitems` ( `issn` longtext DEFAULT NULL COMMENT 'ISSN (MARC21 022$a)', `ean` longtext DEFAULT NULL, `publicationyear` mediumtext DEFAULT NULL, - `publishercode` varchar(255) DEFAULT NULL COMMENT 'publisher (MARC21 260$b)', + `publishercode` longtext DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 246$b)', `volumedate` date DEFAULT NULL, `volumedesc` mediumtext DEFAULT NULL COMMENT 'volume information (MARC21 362$a)', `collectiontitle` longtext DEFAULT NULL, @@ -1132,11 +1132,11 @@ CREATE TABLE `biblioitems` ( `editionstatement` mediumtext DEFAULT NULL, `editionresponsibility` mediumtext DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), - `illus` varchar(255) DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)', - `pages` varchar(255) DEFAULT NULL COMMENT 'number of pages (MARC21 300$c)', + `illus` longtext DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)', + `pages` longtext DEFAULT NULL COMMENT 'number of pages (MARC21 300$a)', `notes` longtext DEFAULT NULL, - `size` varchar(255) DEFAULT NULL COMMENT 'material size (MARC21 300$c)', - `place` varchar(255) DEFAULT NULL COMMENT 'publication place (MARC21 260$a)', + `size` longtext DEFAULT NULL COMMENT 'material size (MARC21 300$c)', + `place` longtext DEFAULT NULL COMMENT 'publication place (MARC21 260$a and 264$a)', `lccn` longtext DEFAULT NULL COMMENT 'library of congress control number (MARC21 010$a)', `url` mediumtext DEFAULT NULL COMMENT 'url (MARC21 856$u)', `cn_source` varchar(10) DEFAULT NULL COMMENT 'classification source (MARC21 942$2)', @@ -2526,7 +2526,7 @@ CREATE TABLE `deletedbiblioitems` ( `issn` longtext DEFAULT NULL COMMENT 'ISSN (MARC21 022$a)', `ean` longtext DEFAULT NULL, `publicationyear` mediumtext DEFAULT NULL, - `publishercode` varchar(255) DEFAULT NULL COMMENT 'publisher (MARC21 260$b)', + `publishercode` longtext DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 264$b)', `volumedate` date DEFAULT NULL, `volumedesc` mediumtext DEFAULT NULL COMMENT 'volume information (MARC21 362$a)', `collectiontitle` longtext DEFAULT NULL, @@ -2535,11 +2535,11 @@ CREATE TABLE `deletedbiblioitems` ( `editionstatement` mediumtext DEFAULT NULL, `editionresponsibility` mediumtext DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), - `illus` varchar(255) DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)', - `pages` varchar(255) DEFAULT NULL COMMENT 'number of pages (MARC21 300$c)', + `illus` longtext DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)', + `pages` longtext DEFAULT NULL COMMENT 'number of pages (MARC21 300$a)', `notes` longtext DEFAULT NULL, - `size` varchar(255) DEFAULT NULL COMMENT 'material size (MARC21 300$c)', - `place` varchar(255) DEFAULT NULL COMMENT 'publication place (MARC21 260$a)', + `size` longtext DEFAULT NULL COMMENT 'material size (MARC21 300$c)', + `place` longtext DEFAULT NULL COMMENT 'publication place (MARC21 260$a and 264§a)', `lccn` longtext DEFAULT NULL COMMENT 'library of congress control number (MARC21 010$a)', `url` mediumtext DEFAULT NULL COMMENT 'url (MARC21 856$u)', `cn_source` varchar(10) DEFAULT NULL COMMENT 'classification source (MARC21 942$2)', -- 2.30.2