From a4ddd6fd707df2c6e731b2bfef518f788b96f8c4 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 27 Jan 2014 13:21:48 +0100 Subject: [PATCH] [SIGNED OFF] Bug 11617: Add itemnumber constraint to aqorders_items 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. REVISED TEST PLAN --------- 0) Backup DB, because you may want it back after. 1) go into mysql client, use the koha database and check: SHOW CREATE TABLE aqorders_items; -- should only be one constraint. 2) apply the patch 3) ./installer/mysql/data/updatedatabase.pl 4) go into mysql client, use the koha database and check: SHOW CREATE TABLE aqorders_items; -- should be two constraints. 5) drop the koha database and recreate it. 6) go to the staff client and do the web install. 7) go into mysql client, use the koha database and check: SHOW CREATE TABLE aqorders_items; -- should be two constraints. 8) Restore DB Note that the constraints are in different orders, but identical. The added constraint is: CONSTRAINT `aqorders_items_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE Signed-off-by: Mark Tompsett --- installer/data/mysql/kohastructure.sql | 3 ++- installer/data/mysql/updatedatabase.pl | 9 +++++++++ 2 files changed, 11 insertions(+), 1 deletion(-) 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.9.5