From cdf93bffee9e59d6fe19a0d4872ef47d1fbe72c4 Mon Sep 17 00:00:00 2001 From: Chris Nighswonger Date: Fri, 25 Jul 2008 10:44:30 -0500 Subject: [PATCH] kohabug 2380 Correcting serialitems table structure and fixing duplicate barcode check 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 Signed-off-by: Joshua Ferraro --- 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 b0061585a2..53da4fe656 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 bb38ae2d98..9ea474ecd8 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 48af193115..ecaa007730 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 16197629e9..23eebc96e5 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; -- 2.39.5