From 5164b4860534124332c44a6287d9802db7de8f6a 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 Content-Type: text/plain; charset=utf-8 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 --- .../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.30.2