From 8fa051725cb319e26ded630f2ddd0de75d38162c Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 19 Feb 2019 14:03:34 +0000 Subject: [PATCH] Bug 22368: Add missing constraints to `suggestions` Signed-off-by: Mark Tompsett Signed-off-by: Josef Moravec Signed-off-by: Kyle M Hall --- .../data/mysql/atomicupdate/bug_22368.perl | 92 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 13 ++- 2 files changed, 103 insertions(+), 2 deletions(-) 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..f158556e40 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_22368.perl @@ -0,0 +1,92 @@ +$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("ALTER TABLE suggestions CHANGE COLUMN suggestedby suggestedby INT(11) NULL DEFAULT NULL;"); + $dbh->do( +"UPDATE suggestions SET suggestedby = NULL 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 NULL ON UPDATE CASCADE" + ); + } + + # Add constraint for managedby + $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 + $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 + $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 `branches` (`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 76798c3163..b7b22db28b 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3035,7 +3035,7 @@ CREATE TABLE aqbasketusers ( DROP TABLE IF EXISTS `suggestions`; CREATE TABLE `suggestions` ( -- purchase suggestions `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha - `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table + `suggestedby` int(11) DEFAULT NULL, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table `suggesteddate` date NOT NULL, -- date the suggestion was submitted `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table `manageddate` date default NULL, -- date the suggestion was updated @@ -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 NULL 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.17.2 (Apple Git-113)