From 40a3293401fe32a73b19d11d82834d4752fd4598 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 20 Aug 2014 12:02:56 +0200 Subject: [PATCH 2/2] Bug 12555: Add a FK on aqorders_items.itemnumber The previous patch assumed that deleting an item will delete the entry in aqorders_items. But the FK did not exist. Looking at the code, we never join aqorders_items and deleteditems, so I think we can add this constraint. Before adding this constraint, we need to delete all entries in aqorders_items if the item has already been deleted. Signed-off-by: Paola Rossi --- installer/data/mysql/kohastructure.sql | 3 ++- installer/data/mysql/updatedatabase.pl | 15 +++++++++++++++ 2 files changed, 17 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 4ffbbf2..ceb378d 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3098,7 +3098,8 @@ CREATE TABLE `aqorders_items` ( -- information on items entered in the acquisiti `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this order item was last touched PRIMARY KEY (`itemnumber`), KEY `ordernumber` (`ordernumber`), - CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT aqorders_items_ibfk_1 FOREIGN KEY (ordernumber) REFERENCES aqorders (ordernumber) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `aqorders_items_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 393a3a1..2ecd207 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9573,6 +9573,21 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = '3.17.00.XXX'; +if ( CheckVersion($DBversion) ) { + $dbh->do(q| + DELETE aqorders_items.* + FROM aqorders_items + LEFT JOIN items ON items.itemnumber=aqorders_items.itemnumber + WHERE items.itemnumber IS NULL + |); + $dbh->do(q| + ALTER TABLE aqorders_items ADD FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE + |); + print "Upgrade to $DBversion done (Bug 12555 - Add a foreign key on aqorders_items.itemnumber)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.10.4