From cdde536f1e12c82e04426ad453ba3e1e1890fc06 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 Signed-off-by: Martin Renvoize --- .../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 @@ -1,12 +1,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 @@ -19,6 +49,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 @@ -32,6 +65,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 @@ -39,6 +75,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 @@ -51,6 +90,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.20.1