From ad93540d59660fdb887569d3837d5cf6eed4d089 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 text to avoid breaking import This updated the datatype of the following columns from varchar(255) to text 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 Signed-off-by: Sam Lau Signed-off-by: Marcel de Rooy Signed-off-by: Tomas Cohen Arazi (cherry picked from commit 00545f8780390d735dad664d76d09cc67f42fdaa) Signed-off-by: Martin Renvoize --- .../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..92b0736cbb --- /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` text DEFAULT NULL COMMENT 'publication place (MARC21 260$a and 264$a)' + }); + say $out "Updated biblioitems.place to text"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `place` text DEFAULT NULL COMMENT 'publication place (MARC21 260$a and 264$a)' + }); + say $out "Updated deletedbiblioitems.place to text"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `publishercode` text DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 264$b)' + }); + say $out "Updated biblioitems.publishercode to text"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `publishercode` text DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 264$b)' + }); + say $out "Updated deletedbiblioitems.publishercode to text"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `size` text DEFAULT NULL COMMENT 'material size (MARC21 300$c)' + }); + say $out "Updated biblioitems.size to text"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `size` text DEFAULT NULL COMMENT 'material size (MARC21 300$c)' + }); + say $out "Updated deletedbiblioitems.size to text"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `pages` text DEFAULT NULL COMMENT 'number of pages (MARC21 300$a)' + }); + say $out "Updated biblioitems.pages to text"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `pages` text DEFAULT NULL COMMENT 'number of pages (MARC21 300$a)' + }); + say $out "Updated deletedbiblioitems.pages to text"; + + $dbh->do(q{ + ALTER TABLE `biblioitems` + MODIFY COLUMN `illus` text DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)' + }); + say $out "Updated biblioitems.illus to text"; + $dbh->do(q{ + ALTER TABLE `deletedbiblioitems` + MODIFY COLUMN `illus` text DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)' + }); + say $out "Updated deletedbiblioitems.illus to text"; + + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3f666d2df9..2807fcda36 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` text 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` text DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)', + `pages` text 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` text DEFAULT NULL COMMENT 'material size (MARC21 300$c)', + `place` text 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` text 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` text DEFAULT NULL COMMENT 'illustrations (MARC21 300$b)', + `pages` text 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` text DEFAULT NULL COMMENT 'material size (MARC21 300$c)', + `place` text 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.39.5