[Koha-patches] [PATCH] (bug #4002) fix database for single itemnumber and foreign key

[Nahuel ANGELINETTI]

this fix serialitems to have a foreign key to items(if an item is deleted, serialitem must be deleted)
and it fixes issues to have only one issue by itemnumber(strange to have more than one).
---
 installer/data/mysql/kohastructure.sql   |    5 +++--
 installer/data/mysql/updatedatabase30.pl |    8 ++++++++
 2 files changed, 11 insertions(+), 2 deletions(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index d41b5e6..5a98d86 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1104,7 +1104,7 @@ CREATE TABLE `import_items` (
 DROP TABLE IF EXISTS `issues`;
 CREATE TABLE `issues` (
   `borrowernumber` int(11) default NULL,
-  `itemnumber` int(11) default NULL,
+  `itemnumber` int(11) UNIQUE default NULL,
   `date_due` date default NULL,
   `branchcode` varchar(10) default NULL,
   `issuingbranch` varchar(18) default NULL,
@@ -2195,7 +2195,8 @@ CREATE TABLE `serialitems` (
 	`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
+	CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE,
+	CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 		  
 DROP TABLE IF EXISTS `user_permissions`;
diff --git a/installer/data/mysql/updatedatabase30.pl b/installer/data/mysql/updatedatabase30.pl
index 941153e..d326379 100644
--- a/installer/data/mysql/updatedatabase30.pl
+++ b/installer/data/mysql/updatedatabase30.pl
@@ -672,6 +672,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.00.05.003";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    $dbh->do("ALTER TABLE issues CHANGE COLUMN `itemnumber` `itemnumber` int(11) UNIQUE DEFAULT NULL;");
+    $dbh->do("ALTER TABLE serialitems ADD CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE;");
+    print "Upgrade to $DBversion done (Improve serialitems table security)\n";
+    SetVersion ($DBversion);
+}
+
 =item DropAllForeignKeys($table)
 
   Drop all foreign keys of the table $table
-- 
1.6.3.3