From 0e606600d7e8fbfd62275953a65d072e1af5821e Mon Sep 17 00:00:00 2001 From: Andrew Isherwood 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..6863d56c22 --- /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 XXXXX - description)\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