From aec072fc142bfd1a90cf395c90290aefd75c8c63 Mon Sep 17 00:00:00 2001 From: Nahuel ANGELINETTI Date: Thu, 24 Dec 2009 17:11:42 +0100 Subject: [PATCH] (bug #4002) fix database for single itemnumber and foreign key 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 d41b5e65d6..5a98d86a6e 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 62880266d9..c3247ed926 100644 --- a/installer/data/mysql/updatedatabase30.pl +++ b/installer/data/mysql/updatedatabase30.pl @@ -697,6 +697,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.00.06.002"; +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 -- 2.39.5