From 960c8c60685fba21394bc4b6c044a440232fc082 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 27 Jan 2014 13:21:48 +0100 Subject: [PATCH] Bug 11617: Add itemnumber constraint to aqorders_items Content-Type: text/plain; charset=utf-8 This patch adds a foreign key constraint to aqorders_items on itemnumber. It is a follow-up on report 10869 with changes to DelOrder. This removes the need to delete these records in DelOrder. Test plan: Do a new install, and check that the constraint on itemnumber is created in the table aqorders_items. Run an upgrade via install.pl?step=3&op=updatestructure. Check again. --- installer/data/mysql/kohastructure.sql | 3 ++- installer/data/mysql/updatedatabase.pl | 9 +++++++++ 2 files changed, 11 insertions(+), 1 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 136104f..54e9d08 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3038,7 +3038,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 f98daa7..54ca19f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7945,6 +7945,15 @@ if (CheckVersion($DBversion)) { SetVersion($DBversion); } +$DBversion = "3.15.00.XXX"; +if (CheckVersion($DBversion)) { + $dbh->do('delete aqorders_items from aqorders_items left join items it using (itemnumber) where it.itemnumber is null'); + $dbh->do('alter table aqorders_items add constraint aqorders_items_ibfk_2 foreign key (itemnumber) references items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE'); + print "Upgrade to $DBversion done (Bug 11617: Add itemnumber constraint to aqorders_items)\n"; + SetVersion($DBversion); +} + + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.7.6