From 71cd2a91d0e9e8d4fbead6e8cdcaa3a51d19f0b9 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 19 Feb 2019 14:03:34 +0000 Subject: [PATCH] Bug 22636: Add missing constraints to `suggestions` https://bugs.koha-community.org/show_bug.cgi?id=22368 --- .../data/mysql/atomicupdate/bug_22368.perl | 91 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 11 ++- 2 files changed, 101 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_22368.perl diff --git a/installer/data/mysql/atomicupdate/bug_22368.perl b/installer/data/mysql/atomicupdate/bug_22368.perl new file mode 100644 index 0000000000..a283d23d1a --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22368.perl @@ -0,0 +1,91 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if ( CheckVersion($DBversion) ) { + + # Add constraint for suggestedby + my $sth = $dbh->prepare( +q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_suggestedby'| + ); + $sth->execute; + unless ( $sth->fetchrow_hashref ) { + $dbh->do( +"UPDATE suggestions SET suggestedby = 0 where suggestedby NOT IN (SELECT borrowernumber FROM borrowers)" + ); + $dbh->do( +"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET 0 ON UPDATE CASCADE" + ); + } + + # Add constraint for managedby + my $sth = $dbh->prepare( +q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_managedby'| + ); + $sth->execute; + unless ( $sth->fetchrow_hashref ) { + $dbh->do( +"UPDATE suggestions SET managedby = NULL where managedby NOT IN (SELECT borrowernumber FROM borrowers)" + ); + $dbh->do( +"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" + ); + } + + # Add constraint for acceptedby + my $sth = $dbh->prepare( +q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_acceptedby'| + ); + $sth->execute; + unless ( $sth->fetchrow_hashref ) { + $dbh->do( +"UPDATE suggestions SET acceptedby = NULL where acceptedby NOT IN (SELECT borrowernumber FROM borrowers)" + ); + $dbh->do( +"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" + ); + } + + # Add constraint for rejectedby + my $sth = $dbh->prepare( +q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_rejectedby'| + ); + $sth->execute; + unless ( $sth->fetchrow_hashref ) { + $dbh->do( +"UPDATE suggestions SET rejectedby = NULL where rejectedby NOT IN (SELECT borrowernumber FROM borrowers)" + ); + $dbh->do( +"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" + ); + } + + # Add constraint for biblionumber + $sth = $dbh->prepare( +q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_biblionumber'| + ); + $sth->execute; + unless ( $sth->fetchrow_hashref ) { + $dbh->do( +"UPDATE suggestions SET biblionumber = NULL where biblionumber NOT IN (SELECT biblionumber FROM biblio)" + ); + $dbh->do( +"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE" + ); + } + + # Add constraint for branchcode + $sth = $dbh->prepare( +q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_branchcode'| + ); + $sth->execute; + unless ( $sth->fetchrow_hashref ) { + $dbh->do( +"UPDATE suggestions SET branchcode = NULL where branchcode NOT IN (SELECT branchcode FROM branches)" + ); + $dbh->do( +"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `biblio` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE" + ); + } + + SetVersion($DBversion); + print +"Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index fa5044e9fe..be0bf91c8f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3068,10 +3068,19 @@ CREATE TABLE `suggestions` ( -- purchase suggestions PRIMARY KEY (`suggestionid`), KEY `suggestedby` (`suggestedby`), KEY `managedby` (`managedby`), - KEY `status` (`STATUS`), + KEY `acceptedby` (`acceptedby`), + KEY `rejectedby` (`rejectedby`), KEY `biblionumber` (`biblionumber`), + KEY `budgetid` (`budgetid`), KEY `branchcode` (`branchcode`), + KEY `status` (`STATUS`), + CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET 0 ON UPDATE CASCADE, + CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `suggestions_ibfk_branchcode` 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