Bugzilla – Attachment 83227 Details for
Bug 21983
Better handling of deleted biblios on ILL requests
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 21983: Add foreign key constraint on biblio_id
Bug-21983-Add-foreign-key-constraint-on-biblioid.patch (text/plain), 3.13 KB, created by
Andrew Isherwood
on 2018-12-14 16:13:15 UTC
(
hide
)
Description:
Bug 21983: Add foreign key constraint on biblio_id
Filename:
MIME Type:
Creator:
Andrew Isherwood
Created:
2018-12-14 16:13:15 UTC
Size:
3.13 KB
patch
obsolete
>From 0e9d2c037b0db6f01ca10b541a9a2ef4006c31fb Mon Sep 17 00:00:00 2001 >From: Andrew Isherwood <andrew.isherwood@ptfs-europe.com> >Date: Fri, 14 Dec 2018 16:07:03 +0000 >Subject: [PATCH] Bug 21983: Add foreign key constraint on biblio_id > >This patch adds a foreign key constraint on biblio_id, this means that, >amongst other things, if a bib is deleted that an ILL request is a >attached to, the ILL request is no longer attached to that bib. > >Test plan: >- Apply the patch >- Create an ILL request, a bib should automatically be created for the >item being requested >- Note that when viewing the request, a link to the bib is displayed in >the ILL request list view >- Delete the bib >- Refresh the ILL request list view >- TEST => Note that the link to the bib no longer exists >--- > installer/data/mysql/atomicupdate/bug_21983.perl | 24 ++++++++++++++++++++++++ > installer/data/mysql/kohastructure.sql | 6 +++++- > 2 files changed, 29 insertions(+), 1 deletion(-) > create mode 100644 installer/data/mysql/atomicupdate/bug_21983.perl > >diff --git a/installer/data/mysql/atomicupdate/bug_21983.perl b/installer/data/mysql/atomicupdate/bug_21983.perl >new file mode 100644 >index 0000000000..afe9ffa40c >--- /dev/null >+++ b/installer/data/mysql/atomicupdate/bug_21983.perl >@@ -0,0 +1,24 @@ >+$DBversion = 'XXX'; # will be replaced by the RM >+if( CheckVersion( $DBversion ) ) { >+ unless ( foreign_key_exists('illrequests', 'illrequests_bifk') ) { >+ # There may be requests already in the DB that have orphan >+ # bib IDs, these will prevent the creation of the foreign key >+ # So we need to idenfify and NULL them >+ my @orphans; >+ my $sth = $dbh->prepare( "SELECT illrequest_id FROM illrequests WHERE biblio_id NOT IN (SELECT biblionumber FROM biblio)" ); >+ $sth->execute(); >+ while (( my $id ) = $sth->fetchrow_array) { >+ push @orphans, $id; >+ } >+ if (scalar @orphans > 0) { >+ $dbh->do( "UPDATE illrequests SET biblio_id = NULL WHERE illrequest_id IN ( ? )", undef, join(",", @orphans) ); >+ } >+ >+ # Now we can create the foreign key >+ $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_bifk FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE SET NULL ON UPDATE CASCADE;" ); >+ } >+ >+ # Always end with this (adjust the bug info) >+ SetVersion( $DBversion ); >+ print "Upgrade to $DBversion done (Bug 21983 - Add foreign key constraint on biblio_id)\n"; >+} >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index c3c9570a29..c8dfc269ca 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -4187,7 +4187,11 @@ CREATE TABLE illrequests ( > CONSTRAINT `illrequests_bcfk_2` > FOREIGN KEY (`branchcode`) > REFERENCES `branches` (`branchcode`) >- ON UPDATE CASCADE ON DELETE CASCADE >+ ON UPDATE CASCADE ON DELETE CASCADE, >+ CONSTRAINT `illrequests_bifk` >+ FOREIGN KEY (`biblio_id`) >+ REFERENCES `biblio` (`biblionumber`) >+ ON UPDATE CASCADE ON DELETE SET NULL > ) 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 21983
:
83226
|
83227
|
152005
|
152006
|
152007
|
152008
|
152010
|
152011
|
152012
|
152013
|
152056
|
152057
|
152058
|
152059
|
152060
|
152063