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.

Signed-off-by: Colin Campbell <colin.campbell@ptfs-europe.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

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 (file)
index 0000000..bfd7dd1
--- /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 2edd371..eb02c95 100644 (file)
--- 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
@@ -525,7 +526,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,
@@ -555,6 +556,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;