From 912190be14107bbd08a4b64a440043733e20bc56 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 18 Jul 2014 12:34:07 +0200 Subject: [PATCH] Bug 12601: Add a foreign key for aqorders.budget_id When creating an order, the fund value is mandatory but the DB structure does not show this constraint. The aqorders.budget_id field should be linked to aqbudgets.budget_id. The updatedatabase.pl (entry 3.01.00.077) commented this constraint, certainly for a retro compatibility reason. Actually I found some cases (in production) where aqorders.budget_id is set to "0". To add this constraint, we should add a temporary fund to linked orphan orders. Test plan: 0/ Verify it is not possible to create an order not linked to a fund via the Koha interface. 1/ Using your SQL CLI (or equivalent), create or update 1+ orders and set "0" in the budget_id field. 2/ Execute the updatedabase script. 3/ Verify that your order is linked to a new fund (which is linked to a new budget). 4/ Verify the constraint has correctly been added (show create table aqorders). --- installer/data/mysql/updatedatabase.pl | 41 ++++++++++++++++++++++++++++++++++ 1 file changed, 41 insertions(+) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 1030454..7799c83 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8570,6 +8570,47 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.17.00.XXX"; +if ( CheckVersion($DBversion) ) { + my $number_of_orders_not_linked = $dbh->selectcol_arrayref(q| + SELECT COUNT(*) + FROM aqorders o + WHERE NOT EXISTS ( + SELECT NULL + FROM aqbudgets b + WHERE b.budget_id = o.budget_id + ); + |); + + if ( $number_of_orders_not_linked->[0] > 0 ) { + $dbh->do(q| + INSERT INTO aqbudgetperiods(budget_period_startdate, budget_period_enddate, budget_period_active, budget_period_description, budget_period_total) VALUES ( CAST(NOW() AS date), CAST(NOW() AS date), 0, "WARNING: This budget has been automatically created by the updatedatabase script, please see bug 12601 for more information", 0) + |); + my $budget_period_id = $dbh->last_insert_id( undef, undef, 'aqbudgetperiods', undef ); + $dbh->do(qq| + INSERT INTO aqbudgets(budget_code, budget_name, budget_amount, budget_period_id) VALUES ( "BACKUP_TMP", "WARNING: fund created by the updatedatabase script, please see bug 12601", 0, $budget_period_id ); + |); + my $budget_id = $dbh->last_insert_id( undef, undef, 'aqbudgets', undef ); + $dbh->do(qq| + UPDATE aqorders o + SET budget_id = $budget_id + WHERE NOT EXISTS ( + SELECT NULL + FROM aqbudgets b + WHERE b.budget_id = o.budget_id + ) + |); + } + + $dbh->do(q| + ALTER TABLE aqorders + ADD CONSTRAINT aqorders_budget_id_fk FOREIGN KEY (budget_id) REFERENCES aqbudgets(budget_id) + |); + + print "Upgrade to $DBversion done (Bug 12601 - Add new foreign key aqorders.budget_id" . ( ( $number_of_orders_not_linked->[0] > 0 ) ? ' WARNING: temporary budget and fund have been created (search for "BACKUP_TMP"). At least one of your order was not linked to a budget' : '' ) . ")\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 2.0.0.rc2