From 7fabe28e895e031d966ad37b97ae97c26fc56baf Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Fri, 31 Jan 2020 13:21:41 +0000 Subject: [PATCH] Bug 21901: (QA follow-up) Ensure consistent data before adding keys Content-Type: text/plain; charset=utf-8 Signed-off-by: Martin Renvoize Signed-off-by: Marcel de Rooy --- installer/data/mysql/atomicupdate/bug_21901.perl | 42 ++++++++++++++++++++++++ 1 file changed, 42 insertions(+) diff --git a/installer/data/mysql/atomicupdate/bug_21901.perl b/installer/data/mysql/atomicupdate/bug_21901.perl index 58657f91b7..5ea3bef734 100644 --- a/installer/data/mysql/atomicupdate/bug_21901.perl +++ b/installer/data/mysql/atomicupdate/bug_21901.perl @@ -2,12 +2,42 @@ $DBversion = 'XXX'; # will be replaced by the RM if( CheckVersion( $DBversion ) ) { $dbh->do(q| + UPDATE + serial + SET + planneddate = NULL + WHERE + planneddate = '0000-00-00' + |); + + $dbh->do(q| + UPDATE + serial + SET + publisheddate = NULL + WHERE + publisheddate = '0000-00-00' + |); + + $dbh->do(q| + UPDATE + serial + SET + claimdate = NULL + WHERE + claimdate = '0000-00-00' + |); + + $dbh->do(q| ALTER TABLE serial MODIFY COLUMN biblionumber INT(11) NOT NULL |); unless ( foreign_key_exists( 'serial', 'serial_ibfk_1' ) ) { $dbh->do(q| + DELETE FROM serial WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio) + |); + $dbh->do(q| ALTER TABLE serial ADD CONSTRAINT serial_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE |); @@ -20,6 +50,9 @@ if( CheckVersion( $DBversion ) ) { unless ( foreign_key_exists( 'serial', 'serial_ibfk_2' ) ) { $dbh->do(q| + DELETE FROM serial WHERE subscriptionid NOT IN (SELECT subscriptionid FROM subscription) + |); + $dbh->do(q| ALTER TABLE serial ADD CONSTRAINT serial_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE |); @@ -33,6 +66,9 @@ if( CheckVersion( $DBversion ) ) { unless ( foreign_key_exists( 'subscriptionhistory', 'subscription_history_ibfk_1' ) ) { $dbh->do(q| + DELETE FROM subscriptionhistory WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio) + |); + $dbh->do(q| ALTER TABLE subscriptionhistory ADD CONSTRAINT subscription_history_ibfk_1 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE |); @@ -40,6 +76,9 @@ if( CheckVersion( $DBversion ) ) { unless ( foreign_key_exists( 'subscriptionhistory', 'subscription_history_ibfk_2' ) ) { $dbh->do(q| + DELETE FROM subscriptionhistory WHERE subscriptionid NOT IN (SELECT subscriptionid FROM subscription) + |); + $dbh->do(q| ALTER TABLE subscriptionhistory ADD CONSTRAINT subscription_history_ibfk_2 FOREIGN KEY (subscriptionid) REFERENCES subscription (subscriptionid) ON DELETE CASCADE ON UPDATE CASCADE |); @@ -52,6 +91,9 @@ if( CheckVersion( $DBversion ) ) { unless ( foreign_key_exists( 'subscription', 'subscription_ibfk_3' ) ) { $dbh->do(q| + DELETE FROM subscription WHERE biblionumber NOT IN (SELECT biblionumber FROM biblio) + |); + $dbh->do(q| ALTER TABLE subscription ADD CONSTRAINT subscription_ibfk_3 FOREIGN KEY (biblionumber) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE |); -- 2.11.0