Bugzilla – Attachment 85774 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), 3.98 KB, created by
Martin Renvoize (ashimema)
on 2019-02-27 13:55:34 UTC
(
hide
)
Description:
Bug 22421: Add missing constraints
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2019-02-27 13:55:34 UTC
Size:
3.98 KB
patch
obsolete
>From 92fdb56312c84ac58e266134130dd77483916e7b Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@ptfs-europe.com> >Date: Wed, 27 Feb 2019 12:05:50 +0000 >Subject: [PATCH] Bug 22421: Add missing constraints > >--- > .../data/mysql/atomicupdate/bug_22421.perl | 36 +++++++++++++++++++ > installer/data/mysql/kohastructure.sql | 5 +++ > 2 files changed, 41 insertions(+) > create mode 100644 installer/data/mysql/atomicupdate/bug_22421.perl > >diff --git a/installer/data/mysql/atomicupdate/bug_22421.perl b/installer/data/mysql/atomicupdate/bug_22421.perl >new file mode 100644 >index 0000000000..71e9a7ae75 >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_22421.perl >@@ -0,0 +1,36 @@ >+$DBversion = 'XXX'; # will be replaced by the RM >+if( CheckVersion( $DBversion ) ) { >+ >+ # Add old_issue_id field >+ unless( column_exists( 'accountlines', 'old_issue_id' )) { >+ $dbh->do("ALTER TABLE accountlines ADD COLUMN old_issue_id int(11) DEFAULT NULL AFTER issue_id"); >+ $dbh->do("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"); >+ $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 >+ }); >+ >+ # 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 >+ }); >+ $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE"); >+ } >+ } >+ >+ SetVersion( $DBversion ); >+ print "Upgrade to $DBversion done (Bug 22421 - 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 d456dac21f..f1538dd76b 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -2707,6 +2707,7 @@ DROP TABLE IF EXISTS `accountlines`; > CREATE TABLE `accountlines` ( > `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, > `issue_id` int(11) NULL DEFAULT NULL, >+ `old_issue_id` int(11) NULL DEFAULT NULL, > `borrowernumber` int(11) DEFAULT NULL, > `accountno` smallint(6) NOT NULL default 0, > `itemnumber` int(11) default NULL, >@@ -2726,9 +2727,13 @@ CREATE TABLE `accountlines` ( > KEY `timeidx` (`timestamp`), > KEY `itemnumber` (`itemnumber`), > KEY `branchcode` (`branchcode`), >+ KEY `issue_id` (`issue_id`), >+ KEY `old_issue_id` (`old_issue_id`), > KEY `manager_id` (`manager_id`), > CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) 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_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) 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_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, > CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; >-- >2.20.1
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