From 26a10804ba1a0cd81b3330096cdf3b19e63409a7 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Tue, 26 Feb 2019 17:35:22 +0000 Subject: [PATCH] Bug 22368: (QA follow-up) Changed to LEFT JOIN as requested Signed-off-by: Martin Renvoize Signed-off-by: Kyle M Hall --- .../data/mysql/atomicupdate/bug_22368.perl | 41 ++++++++++++------- 1 file changed, 27 insertions(+), 14 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_22368.perl b/installer/data/mysql/atomicupdate/bug_22368.perl index 40c6edfb8d..e5b8a1056d 100644 --- a/installer/data/mysql/atomicupdate/bug_22368.perl +++ b/installer/data/mysql/atomicupdate/bug_22368.perl @@ -2,10 +2,13 @@ $DBversion = 'XXX'; # will be replaced by the RM if ( CheckVersion($DBversion) ) { # Add constraint for suggestedby - unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_suggestedby' ) ) { - $dbh->do("ALTER TABLE suggestions CHANGE COLUMN suggestedby suggestedby INT(11) NULL DEFAULT NULL;"); + unless ( foreign_key_exists( 'suggestions', 'suggestions_ibfk_suggestedby' ) ) + { $dbh->do( -"UPDATE suggestions SET suggestedby = NULL where suggestedby NOT IN (SELECT borrowernumber FROM borrowers)" +"ALTER TABLE suggestions CHANGE COLUMN suggestedby suggestedby INT(11) NULL DEFAULT NULL;" + ); + $dbh->do( +"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.suggestedby = borrowers.borrowernumber) SET suggestedby = null WHERE borrowernumber IS null" ); $dbh->do( "ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" @@ -13,9 +16,10 @@ if ( CheckVersion($DBversion) ) { } # Add constraint for managedby - unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_managedby' ) ) { + unless ( foreign_key_exists( 'suggestions', 'suggestions_ibfk_managedby' ) ) + { $dbh->do( -"UPDATE suggestions SET managedby = NULL where managedby NOT IN (SELECT borrowernumber FROM borrowers)" +"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.managedby = borrowers.borrowernumber) SET managedby = null WHERE borrowernumber IS NULL" ); $dbh->do( "ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" @@ -23,9 +27,11 @@ if ( CheckVersion($DBversion) ) { } # Add constraint for acceptedby - unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_acceptedby' ) ) { + unless ( + foreign_key_exists( 'suggestions', 'suggestions_ibfk_acceptedby' ) ) + { $dbh->do( -"UPDATE suggestions SET acceptedby = NULL where acceptedby NOT IN (SELECT borrowernumber FROM borrowers)" +"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.acceptedby = borrowers.borrowernumber) SET acceptedby = null WHERE borrowernumber IS NULL" ); $dbh->do( "ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" @@ -33,9 +39,11 @@ if ( CheckVersion($DBversion) ) { } # Add constraint for rejectedby - unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_rejectedby' ) ) { + unless ( + foreign_key_exists( 'suggestions', 'suggestions_ibfk_rejectedby' ) ) + { $dbh->do( -"UPDATE suggestions SET rejectedby = NULL where rejectedby NOT IN (SELECT borrowernumber FROM borrowers)" +"UPDATE suggestions LEFT JOIN borrowers ON (suggestions.rejectedby = borrowers.borrowernumber) SET rejectedby = null WHERE borrowernumber IS null" ); $dbh->do( "ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" @@ -43,9 +51,11 @@ if ( CheckVersion($DBversion) ) { } # Add constraint for biblionumber - unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_biblionumber' ) ) { + unless ( + foreign_key_exists( 'suggestions', 'suggestions_ibfk_biblionumber' ) ) + { $dbh->do( -"UPDATE suggestions SET biblionumber = NULL where biblionumber NOT IN (SELECT biblionumber FROM biblio)" +"UPDATE suggestions s LEFT JOIN biblio b ON (s.biblionumber = b.biblionumber) SET s.biblionumber = null WHERE b.biblionumber IS null" ); $dbh->do( "ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE" @@ -53,9 +63,11 @@ if ( CheckVersion($DBversion) ) { } # Add constraint for branchcode - unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_branchcode' ) ) { + unless ( + foreign_key_exists( 'suggestions', 'suggestions_ibfk_branchcode' ) ) + { $dbh->do( -"UPDATE suggestions SET branchcode = NULL where branchcode NOT IN (SELECT branchcode FROM branches)" +"UPDATE suggestions s LEFT JOIN branches b ON (s.branchcode = b.branchcode) SET s.branchcode = null WHERE b.branchcode IS null" ); $dbh->do( "ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE" @@ -63,5 +75,6 @@ if ( CheckVersion($DBversion) ) { } SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n"; + print +"Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n"; } -- 2.17.2 (Apple Git-113)