From abb1872bcc6e5f2efa30c4ca08caa1e98be1fe46 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 +++++++++++++++ t/db_dependent/Acquisition/CancelReceipt.t | 1 + 3 files changed, 18 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index b46dc6a..05951a3 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3049,7 +3049,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 de77bf3..a5ead0f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8613,6 +8613,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) diff --git a/t/db_dependent/Acquisition/CancelReceipt.t b/t/db_dependent/Acquisition/CancelReceipt.t index e2d4e4d..9fb7bcb 100644 --- a/t/db_dependent/Acquisition/CancelReceipt.t +++ b/t/db_dependent/Acquisition/CancelReceipt.t @@ -69,6 +69,7 @@ CancelReceipt($ordernumber); my $order = GetOrder( $ordernumber ); is(scalar GetItemnumbersFromOrder($order->{ordernumber}), 0, "Create items on receiving: 0 item exist after cancelling a receipt"); +$itemnumber = AddItem({}, $biblionumber); t::lib::Mocks::mock_preference('AcqCreateItem', 'ordering'); ( undef, $ordernumber ) = C4::Acquisition::NewOrder( { -- 2.0.0.rc2