From b47eac0a6e86ed863921969ff2be703f54f46f52 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Wed, 19 Feb 2014 16:39:17 -0500 Subject: [PATCH] Bug 5377: widen columns to better accommodate multiple ISBN and ISSN Test Plan: 1) Apply this patch 2) Create a record with 5 ISBNs and 5 ISSNs 3) Create a new report from the following SQL, or execute it from the mysql console: SELECT isbn, issn FROM biblioitems ORDER BY biblionumber DESC LIMIT 1 4) Note that all your ISBNs and ISSNs are listed, separated by the pipe character ( | ) Signed-off-by: Chris Cormack This might be slow to run on big databases, because of the 2 index rebuilds, however it changes no functionality just increases the field size which is safe enough (we store multiple now already) Signed-off-by: Jonathan Druart Signed-off-by: Galen Charlton --- installer/data/mysql/kohastructure.sql | 8 ++++---- installer/data/mysql/updatedatabase.pl | 16 ++++++++++++++++ 2 files changed, 20 insertions(+), 4 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index f083eb4397..3e7eb79eb1 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -153,8 +153,8 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in `volume` mediumtext, `number` mediumtext, `itemtype` varchar(10) default NULL, -- biblio level item type (MARC21 942$c) - `isbn` varchar(30) default NULL, -- ISBN (MARC21 020$a) - `issn` varchar(9) default NULL, -- ISSN (MARC21 022$a) + `isbn` mediumtext, -- ISBN (MARC21 020$a) + `issn` mediumtext, -- ISSN (MARC21 022$a) `ean` varchar(13) default NULL, `publicationyear` text, `publishercode` varchar(255) default NULL, -- publisher (MARC21 260$b) @@ -186,8 +186,8 @@ CREATE TABLE `biblioitems` ( -- information related to bibliographic records in KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), KEY `itemtype_idx` (`itemtype`), - KEY `isbn` (`isbn`), - KEY `issn` (`issn`), + KEY `isbn` (`isbn`(255)), + KEY `issn` (`issn`(255)), KEY `publishercode` (`publishercode`), CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 467253a271..5d0a26ec48 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8413,6 +8413,22 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.15.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE biblioitems DROP INDEX isbn"); + $dbh->do("ALTER TABLE biblioitems DROP INDEX issn"); + $dbh->do("ALTER TABLE biblioitems + CHANGE isbn isbn MEDIUMTEXT NULL DEFAULT NULL, + CHANGE issn issn MEDIUMTEXT NULL DEFAULT NULL + "); + $dbh->do("ALTER TABLE biblioitems + ADD INDEX isbn ( isbn ( 255 ) ), + ADD INDEX issn ( issn ( 255 ) ) + "); + print "Upgrade to $DBversion done (Bug 11268 - Biblioitems URL field is too small for some URLs)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 2.39.5