Bugzilla – Attachment 85267 Details for
Bug 22008
accountlines.manager_id is missing a foreign key constraint
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 22008: Add missing constraints to accountlines
Bug-22008-Add-missing-constraints-to-accountlines.patch (text/plain), 4.63 KB, created by
Martin Renvoize (ashimema)
on 2019-02-19 12:00:17 UTC
(
hide
)
Description:
Bug 22008: Add missing constraints to accountlines
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2019-02-19 12:00:17 UTC
Size:
4.63 KB
patch
obsolete
>From 56c3ed3196e10b445139584cbe1ae20477cae6da Mon Sep 17 00:00:00 2001 >From: Martin Renvoize <martin.renvoize@ptfs-europe.com> >Date: Mon, 17 Dec 2018 13:29:08 +0000 >Subject: [PATCH] Bug 22008: Add missing constraints to accountlines > >Add constraints for manager_id referencing the borrowers table and issue_id >referencing the issues table and update the itemnumber constraint from >`ON UPDATE SET NULL` to `ON UPDATE CASCADE` >--- > .../data/mysql/atomicupdate/bug_22008.perl | 31 +++++++++++++++++++ > installer/data/mysql/kohastructure.sql | 8 +++-- > 2 files changed, 37 insertions(+), 2 deletions(-) > create mode 100644 installer/data/mysql/atomicupdate/bug_22008.perl > >diff --git a/installer/data/mysql/atomicupdate/bug_22008.perl b/installer/data/mysql/atomicupdate/bug_22008.perl >new file mode 100644 >index 0000000000..f6c652e19b >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_22008.perl >@@ -0,0 +1,31 @@ >+$DBversion = 'XXX'; # will be replaced by the RM >+if( CheckVersion( $DBversion ) ) { >+ >+ # Add constraint for manager_id >+ my $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_borrowers_2'|); >+ $sth->execute; >+ unless ($sth->fetchrow_hashref) { >+ $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL;"); >+ $dbh->do("UPDATE accountlines SET manager_id = NULL where manager_id NOT IN (SELECT borrowernumber FROM borrowers)"); >+ $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"); >+ } >+ >+ # Add constraint for issue_id >+ $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_issues'|); >+ $sth->execute; >+ unless ($sth->fetchrow_hashref) { >+ $dbh->do("UPDATE accountlines SET issue_id = NULL where issue_id NOT IN (SELECT issue_id FROM issues)"); >+ $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"); >+ } >+ >+ # Rename accountlines_ibfk_2 to accountlines_ibfk_items >+ $sth = $dbh->prepare(q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='accountlines_ibfk_2'|); >+ $sth->execute; >+ if ($sth->fetchrow_hashref) { >+ $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2"); >+ $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE"); >+ } >+ >+ SetVersion( $DBversion ); >+ print "Upgrade to $DBversion done (Bug 22008 - Add missing constraints to accountlines)\n"; >+} >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index fa5044e9fe..272ffa2746 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -2719,15 +2719,19 @@ CREATE TABLE `accountlines` ( > `lastincrement` decimal(28,6) default NULL, > `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, > `note` MEDIUMTEXT NULL default NULL, >- `manager_id` int(11) NULL, >+ `manager_id` int(11) NULL DEFAULT NULL, > `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc. > PRIMARY KEY (`accountlines_id`), > KEY `acctsborridx` (`borrowernumber`), > KEY `timeidx` (`timestamp`), > KEY `itemnumber` (`itemnumber`), > KEY `branchcode` (`branchcode`), >+ KEY `issue_id` (`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_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, >+ 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_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 22008
:
83278
|
83280
|
83460
|
83461
|
83462
|
85267
|
85268
|
85613
|
85614
|
85767
|
85768
|
86162
|
86163
|
86164
|
86659
|
86660
|
86661
|
86994
|
86995
|
87066
|
87072