From c40f965c8388785eac5d75892faa28353ea05765 Mon Sep 17 00:00:00 2001 From: Katrin Fischer Date: Thu, 17 Aug 2017 13:54:36 +0200 Subject: [PATCH] Bug 13766 - Change *bibioitems.ean to mediumtext and add indexes - biblioitems.ean and deleteditems.ean are changed to mediumtext to match issn and isbn. - An index is added for ean on both tables. Patch cleans up inconsistencies caused by bug 5337 that causes some older databases to have an existing index on biblioitems.ean. --- .../data/mysql/atomicupdate/bug_13766_fix_ean.perl | 20 ++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 ++++-- 2 files changed, 24 insertions(+), 2 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_13766_fix_ean.perl diff --git a/installer/data/mysql/atomicupdate/bug_13766_fix_ean.perl b/installer/data/mysql/atomicupdate/bug_13766_fix_ean.perl new file mode 100644 index 0000000..780ef64 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_13766_fix_ean.perl @@ -0,0 +1,20 @@ +$DBversion = "XXX"; +if(CheckVersion($DBversion)) { + # Drop index that might exist because of bug 5337 + my $temp = $dbh->selectall_arrayref(q{ + SHOW INDEXES FROM biblioitems WHERE key_name = 'ean' }); + if( @$temp > 0 ) { + $dbh->do(q{ ALTER TABLE biblioitems DROP INDEX ean }); + } + + # Change data type of column + $dbh->do(q{ ALTER TABLE biblioitems MODIFY COLUMN ean MEDIUMTEXT default NULL }); + $dbh->do(q{ ALTER TABLE deletedbiblioitems MODIFY COLUMN ean MEDIUMTEXT default NULL }); + + # Add indexes + $dbh->do(q{ ALTER TABLE biblioitems ADD INDEX ean ( ean(255) )}); + $dbh->do(q{ ALTER TABLE deletedbiblioitems ADD INDEX ean ( ean(255 ) )}); + + print "Upgrade to $DBversion done (Bug 13766 - Make ean mediumtext and add ean indexes)\n"; + SetVersion($DBversion); +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e227125..5bc4835 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -168,7 +168,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c) `isbn` mediumtext, -- ISBN (MARC21 020$a) `issn` mediumtext, -- ISSN (MARC21 022$a) - `ean` varchar(13) default NULL, + `ean` mediumtext default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, @@ -199,6 +199,7 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in KEY `itemtype_idx` (`itemtype`), KEY `isbn` (`isbn`(255)), KEY `issn` (`issn`(255)), + KEY `ean` (`ean`(255)), KEY `publishercode` (`publishercode`), KEY `timestamp` (`timestamp`), CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE @@ -524,7 +525,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c) `isbn` mediumtext default NULL, -- ISBN (MARC21 020$a) `issn` mediumtext default NULL, -- ISSN (MARC21 022$a) - `ean` varchar(13) default NULL, + `ean` mediumtext default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) `volumedate` date default NULL, @@ -554,6 +555,7 @@ CREATE TABLE `deletedbiblioitems` ( -- information about bibliographic records t KEY `bibnoidx` (`biblionumber`), KEY `itemtype_idx` (`itemtype`), KEY `isbn` (`isbn`(255)), + KEY `ean` (`ean`(255)), KEY `publishercode` (`publishercode`), KEY `timestamp` (`timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- 2.7.4