From a395787002f07e39133b2ce49f8d1bd6919eff39 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 20 Aug 2014 12:02:56 +0200 Subject: [PATCH] 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. --- 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 865f930..22e897b 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3074,7 +3074,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 078a9f3..f9079a6 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8974,6 +8974,21 @@ if ( C4::Context->preference("Version") < TransformToNum($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) -- 2.1.0