[Koha-patches] [PATCH] kohabug 2380 Correcting serialitems table structure and fixing duplicate barcode check

[Galen Charlton]

From: Chris Nighswonger <chris.nighswonger at liblime.com>

The current serialitems table structure does not provide for a *:1 relationship with
the serial table. This causes a problem when attempting to add multiple items to a given
serial. The db throws an error when attempting to INSERT in  serialitems due to serialid.serialitems
being a unique key. A further side effect is that the marc record is updated with the
item inspite of the error. The mods to the serialitems table structure in this patch
drop serialid.serialitems as a key and make itemnumber.serialitems the primary key
creating a *:1 relationship with the serial table. This patch also makes serialid.serialitems
a foreign key referencing serialid.serial to maintain referential integrity.

Fix for duplicate barcode check

Signed-off-by: Galen Charlton <galen.charlton at liblime.com>
---
 installer/data/mysql/kohastructure.sql |   16 +++++++++-------
 installer/data/mysql/updatedatabase.pl |   10 ++++++++++
 kohaversion.pl                         |    2 +-
 serials/serials-edit.pl                |    5 ++---
 4 files changed, 22 insertions(+), 11 deletions(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index b006158..53da4fe 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2133,13 +2133,15 @@ CREATE TABLE `permissions` (
     ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-DROP TABLE IF EXISTS serialitems;
-CREATE TABLE serialitems (
-        serialid int(11) NOT NULL,
-        itemnumber int(11) NOT NULL,
-        UNIQUE KEY `serialididx` (`serialid`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
+DROP TABLE IF EXISTS `serialitems`;
+CREATE TABLE `serialitems` (
+	`itemnumber` int(11) NOT NULL,
+	`serialid` int(11) NOT NULL,
+	UNIQUE KEY `serialitemsidx` (`itemnumber`),
+	KEY `serialitems_sfk_1` (`serialid`),
+	CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+		  
 DROP TABLE IF EXISTS `user_permissions`;
 CREATE TABLE `user_permissions` (
   `borrowernumber` int(11) NOT NULL DEFAULT 0,
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index bb38ae2..9ea474e 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -1879,6 +1879,16 @@ if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
     SetVersion($DBversion);
 }
 
+$DBversion = '3.00.00.102';
+if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
+	$dbh->do('ALTER TABLE serialitems MODIFY `serialid` int(11) NOT NULL AFTER itemnumber' );
+	$dbh->do('ALTER TABLE serialitems DROP KEY serialididx' );
+	$dbh->do('ALTER TABLE serialitems ADD CONSTRAINT UNIQUE KEY serialitemsidx (itemnumber)' );
+	$dbh->do('ALTER TABLE serialitems ADD CONSTRAINT serialitems_sfk_1 FOREIGN KEY (serialid) REFERENCES serial (serialid) ON DELETE CASCADE ON UPDATE CASCADE' );
+    print "Upgrade to $DBversion done (Updating serialitems table to allow for mulitple items per serial fixing kohabug 2380\n";
+    SetVersion($DBversion);
+}
+
 =item DropAllForeignKeys($table)
 
   Drop all foreign keys of the table $table
diff --git a/kohaversion.pl b/kohaversion.pl
index 48af193..ecaa007 100644
--- a/kohaversion.pl
+++ b/kohaversion.pl
@@ -10,7 +10,7 @@
 use strict;
 
 sub kohaversion {
-    our $VERSION = '3.00.00.101';
+    our $VERSION = '3.00.00.102';
     # version needs to be set this way
     # so that it can be picked up by Makefile.PL
     # during install
diff --git a/serials/serials-edit.pl b/serials/serials-edit.pl
index 1619762..23eebc9 100755
--- a/serials/serials-edit.pl
+++ b/serials/serials-edit.pl
@@ -225,11 +225,10 @@ if ($op eq 'serialchangestatus') {
           if ($item=~/^N/){
             #New Item
 			
-            # if autoBarcode is ON, calculate barcode...
+            # if autoBarcode is set to 'incremental', calculate barcode...
             my ($tagfield,$tagsubfield) = &GetMarcFromKohaField("items.barcode");
             if (C4::Context->preference("autoBarcode") eq 'incremental'  ) {
-              eval {    $record->field($tagfield)->subfield($tagsubfield) };
-              if ($@) {
+              if (!$record->field($tagfield)->subfield($tagsubfield)) {
                 my $sth_barcode = $dbh->prepare("select max(abs(barcode)) from items");
                 $sth_barcode->execute;
                 my ($newbarcode) = $sth_barcode->fetchrow;
-- 
1.5.5.GIT