@@ -, +, @@ - Before applying the patch - Make sure you have a budget with some funds linked to it - You will have to change one of the funds to link to an invalid budget with SQL: UPDATE aqbudgets SET budget_period_id = 999 WHERE budget_id = max(budget_id); - Apply patch - Run updatedatabase - verify that you see the hint about 1 existing fund with invalid budget. - Repair your fund with SQL UPDATE aqbudgets SET budget_period_id = ... WHERE budget_id = max(budget_id); (... needs to be your existing budget_period_id) - Run updatedatabase again - verify it runs successfully now. - If you try to change the budget_period_id to a non-existing now with SQL, you will get a database error. The new FK doesn't permit it. - Start fresh with the web installer, verify there are no errors on creating the database tables --- .../atomicupdate/bug_18050_aqbudget_constraint.perl | 20 ++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 22 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl --- a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl +++ a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl @@ -0,0 +1,20 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + my $count_missing_budget = $dbh->selectrow_arrayref(q| + SELECT COUNT(*) FROM aqbudgets ab WHERE NOT EXISTS + (SELECT * FROM aqbudgetperiods abp WHERE abp.budget_period_id = ab.budget_period_id) + + |); + + if ( !foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) && $count_missing_budget->[0] == 0 ) { + $dbh->do(q| + ALTER TABLE aqbudgets ADD CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON UPDATE CASCADE ON DELETE CASCADE + |); + NewVersion( $DBversion, 18050, "Add FK constraint on aqbudgets.budget_period_id"); + } elsif ( $count_missing_budget->[0] > 0 ) { + NewVersion( $DBversion, 18050, "FK constraint on aqbudgets.budget_period_id couldn't be added. There are $count_missing_budget->[0] funds in your database that are not linked to a valid budget."); + } else { + NewVersion( $DBversion, 18050, "FK constraint on aqbudgets.budget already exists"); + } + +} --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -2925,7 +2925,8 @@ CREATE TABLE `aqbudgets` ( -- information related to Funds KEY `budget_code` (`budget_code`), KEY `budget_branchcode` (`budget_branchcode`), KEY `budget_period_id` (`budget_period_id`), - KEY `budget_owner_id` (`budget_owner_id`) + KEY `budget_owner_id` (`budget_owner_id`), + CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --