@@ -, +, @@ --- .../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 --- a/installer/data/mysql/atomicupdate/bug_22368.perl +++ a/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"; +} --- a/installer/data/mysql/kohastructure.sql +++ a/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; -- --