Bugzilla – Attachment 161920 Details for
Bug 22421
accountlines.issue_id is missing a foreign key constraint
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 22421: Add missing constraints
Bug-22421-Add-missing-constraints.patch (text/plain), 6.49 KB, created by
Martin Renvoize (ashimema)
on 2024-02-08 14:42:45 UTC
(
hide
)
Description:
Bug 22421: Add missing constraints
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2024-02-08 14:42:45 UTC
Size:
6.49 KB
patch
obsolete
>From 8cded11c708662bea3632137a5db62e0b201d118 Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@ptfs-europe.com> >Date: Thu, 6 Jun 2019 12:40:11 +0100 >Subject: [PATCH] Bug 22421: Add missing constraints > >DB update to add a new old_issue_id field to accountlines and set >foreign key constraints for both the new field and the existing issue_id >field. > >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> >Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> >--- > .../data/mysql/atomicupdate/bug_22421.pl | 80 +++++++++++++++++++ > installer/data/mysql/kohastructure.sql | 6 ++ > t/db_dependent/Koha/Account/Lines.t | 4 +- > 3 files changed, 88 insertions(+), 2 deletions(-) > create mode 100755 installer/data/mysql/atomicupdate/bug_22421.pl > >diff --git a/installer/data/mysql/atomicupdate/bug_22421.pl b/installer/data/mysql/atomicupdate/bug_22421.pl >new file mode 100755 >index 00000000000..21ef14767f5 >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_22421.pl >@@ -0,0 +1,80 @@ >+use Modern::Perl; >+ >+return { >+ bug_number => "22421", >+ description => "Add issue constraints to accountlines", >+ up => sub { >+ my ($args) = @_; >+ my ( $dbh, $out ) = @$args{qw(dbh out)}; >+ >+ # Add old_issue_id field >+ unless ( column_exists( 'accountlines', 'old_issue_id' ) ) { >+ $dbh->do( >+ q{ >+ ALTER TABLE accountlines >+ ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id >+ } >+ ); >+ say $out "Added column 'accountlines.old_issue_id'"; >+ >+ $dbh->do( >+ q{ >+ ALTER TABLE accountlines >+ ADD CONSTRAINT `accountlines_ibfk_old_issues` >+ FOREIGN KEY (`old_issue_id`) >+ REFERENCES `old_issues` (`issue_id`) >+ ON DELETE SET NULL >+ ON UPDATE CASCADE >+ } >+ ); >+ say $out "Added constraint 'accountlines.old_issues'"; >+ >+ $dbh->do( >+ q{ >+ UPDATE >+ accountlines a >+ LEFT JOIN old_issues o ON (a.issue_id = o.issue_id) >+ SET >+ a.old_issue_id = o.issue_id, >+ a.issue_id = NULL >+ WHERE >+ o.issue_id IS NOT NULL >+ } >+ ); >+ say $out "Updated 'accountlines.old_issue_id' from 'old_issues'"; >+ >+ # Add constraint for issue_id >+ unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_issues' ) ) { >+ $dbh->do( >+ q{ >+ UPDATE >+ accountlines a >+ LEFT JOIN issues i ON (a.issue_id = i.issue_id) >+ SET >+ a.issue_id = NULL >+ WHERE >+ i.issue_id IS NULL >+ } >+ ); >+ say $out "Fix accountlines.issue_id where missing from issues"; >+ >+ $dbh->do( >+ q{ >+ ALTER TABLE accountlines >+ ADD CONSTRAINT `accountlines_ibfk_issues` >+ FOREIGN KEY (`issue_id`) >+ REFERENCES `issues` (`issue_id`) >+ ON DELETE SET NULL >+ ON UPDATE CASCADE >+ } >+ ); >+ say $out "Added constraint 'accountlines.issues'"; >+ } >+ } >+ >+ say $out "Add old_issue_id to accountlines and setup appropriate constraints)\n"; >+ >+ } >+ >+}; >+ >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 16310f0f449..d82343aecdf 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -119,6 +119,7 @@ DROP TABLE IF EXISTS `accountlines`; > CREATE TABLE `accountlines` ( > `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, > `issue_id` int(11) DEFAULT NULL, >+ `old_issue_id` int(11) DEFAULT NULL, > `borrowernumber` int(11) DEFAULT NULL, > `itemnumber` int(11) DEFAULT NULL, > `date` timestamp NULL DEFAULT NULL, >@@ -143,6 +144,8 @@ CREATE TABLE `accountlines` ( > KEY `debit_type_code` (`debit_type_code`), > KEY `itemnumber` (`itemnumber`), > KEY `branchcode` (`branchcode`), >+ KEY `issue_id` (`issue_id`), >+ KEY `old_issue_id` (`old_issue_id`), > KEY `manager_id` (`manager_id`), > KEY `accountlines_ibfk_registers` (`register_id`), > CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, >@@ -150,11 +153,14 @@ CREATE TABLE `accountlines` ( > CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, > CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON UPDATE CASCADE, > CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON UPDATE CASCADE, >+ CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, > CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, >+ CONSTRAINT `accountlines_ibfk_old_issues` FOREIGN KEY (`old_issue_id`) REFERENCES `old_issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE, > CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; > /*!40101 SET character_set_client = @saved_cs_client */; > >+ > -- > -- Table structure for table `action_logs` > -- >diff --git a/t/db_dependent/Koha/Account/Lines.t b/t/db_dependent/Koha/Account/Lines.t >index e16618065ba..039fb809f07 100755 >--- a/t/db_dependent/Koha/Account/Lines.t >+++ b/t/db_dependent/Koha/Account/Lines.t >@@ -165,7 +165,7 @@ subtest 'total() tests' => sub { > > $lines = Koha::Account::Lines->search({ borrowernumber => $patron->id }); > is( $lines->total, 0, 'total sums correctly' ); >- >+ > my $credit_3 = Koha::Account::Line->new( > { borrowernumber => $patron->id, > credit_type_code => "PAYMENT", >@@ -174,7 +174,7 @@ subtest 'total() tests' => sub { > interface => 'commandline', > } > )->store; >- >+ > $lines = Koha::Account::Lines->search({ borrowernumber => $patron->id }); > is( $lines->total, -100, 'total sums correctly' ); > >-- >2.43.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 22421
:
85773
|
85774
|
85775
|
90352
|
90353
|
90354
|
90359
|
90360
|
90361
|
90362
|
90365
|
90366
|
90367
|
90368
|
161919
|
161920
|
161921
|
161922
|
161923
|
162244
|
162245
|
162246
|
162247
|
162248
|
166631
|
173287
|
173288
|
173289
|
173305
|
173790
|
173791
|
173792
|
173793
|
173794
|
173795
|
173796
|
173797
|
173798
|
173799
|
174281
|
174974