Bugzilla – Attachment 110076 Details for
Bug 18050
Missing constraint on aqbudgets.budget_period_id in aqbudgets
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 18050: Add FK constraint on aqbudgets.budget_period_id
Bug-18050-Add-FK-constraint-on-aqbudgetsbudgetperi.patch (text/plain), 3.73 KB, created by
Katrin Fischer
on 2020-09-14 22:16:54 UTC
(
hide
)
Description:
Bug 18050: Add FK constraint on aqbudgets.budget_period_id
Filename:
MIME Type:
Creator:
Katrin Fischer
Created:
2020-09-14 22:16:54 UTC
Size:
3.73 KB
patch
obsolete
>From 96b9e82fb4725677d6a8b88a6d8d54ed2ce12a9c Mon Sep 17 00:00:00 2001 >From: Katrin Fischer <katrin.fischer.83@web.de> >Date: Sun, 13 Sep 2020 22:42:27 +0000 >Subject: [PATCH] Bug 18050: Add FK constraint on aqbudgets.budget_period_id > >This adds a FK constraint on aqbudgets.budget_period_id so that >a fund cannot be added with an invalid aqbudget.budget_period_id. > >We should not have funds that belong to no budget. In case we have, the >update will be skipped and a note displayed. > >Part1: >- 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. >Part 2: >- 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 > >diff --git a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl b/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl >new file mode 100644 >index 0000000000..517c76551e >--- /dev/null >+++ b/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"); >+ } >+ >+} >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 9dff2f804b..dce9479eaa 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/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; > > -- >-- >2.11.0
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 18050
:
110076
|
110078
|
110079
|
110080
|
110169
|
110170
|
110171
|
111429
|
111712
|
111713
|
111751
|
111752
|
111753
|
111754
|
111755
|
112529
|
112530