misc/maintenance/fix_mysql_constraints.pl

   1 #!/usr/bin/perl
   2 #
   3 # Copyright (C) 2012 Tamil s.a.r.l.
   4 #
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it
   8 # under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation; either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but
  13 # WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  19
  20 use Modern::Perl;
  21 BEGIN {
  22     # find Koha's Perl modules
  23     # test carefully before changing this
  24     use FindBin;
  25     my $lib = "$FindBin::Bin/../kohalib.pl";
  26     eval { require $lib };
  27 }
  28
  29 use Getopt::Long;
  30 use Pod::Usage;
  31 use YAML;
  32 use Try::Tiny;
  33 use Koha::Script;
  34 use C4::Context;
  35
  36
  37 my ($doit, $alterengine, $help);
  38 my $result = GetOptions(
  39     'doit'        => \$doit,
  40     'alterengine' => \$alterengine,
  41     'help|h'      => \$help,
  42 );
  43
  44
  45 sub usage {
  46     pod2usage( -verbose => 2 );
  47     exit;
  48 }
  49
  50
  51 sub fix_mysql_constraints {
  52     my ($doit) = @_;
  53
  54     # Get all current DB constraints
  55     my $dbh = C4::Context->dbh;
  56     $dbh->{RaiseError} = 1;
  57     $dbh->{ShowErrorStatement} = 1;
  58     my $database = C4::Context->config('database');
  59     my %db_constraint = map { $_->[0] => undef } @{$dbh->selectall_arrayref(
  60         "SELECT CONSTRAINT_NAME
  61            FROM information_schema.table_constraints
  62           WHERE constraint_schema = '$database'
  63             AND CONSTRAINT_TYPE != 'PRIMARY KEY' ")};
  64
  65     my $base_dir = C4::Context->config('intranetdir');
  66     open my $fh, "<", "$base_dir/installer/data/mysql/kohastructure.sql"
  67         or die "Unable to open kohastructure.sql file";
  68
  69     my $table_name;
  70     my $engine_altered;
  71     # FIXME: This hide problem. But if you run this script, it means that you
  72     # have already identified issues with your Koha DB integrity, and will fix
  73     # any necessary tables requiring records deleting.
  74     $dbh->do("SET FOREIGN_KEY_CHECKS=0");
  75     my $line = <$fh>;
  76     while ( $line ) {
  77         if ( $line =~ /CREATE TABLE (.*?) / ) {
  78             $table_name = $1;
  79             $table_name =~ s/\`//g;
  80             $engine_altered = 0;
  81             $line = <$fh>;
  82             next;
  83         }
  84         unless ( $line =~ /CONSTRAINT /i ) {
  85             $line = <$fh>;
  86             next;
  87         }
  88         my $constraint = $line;
  89         CONTRAINT_LOOP:
  90         while ( $constraint !~ /,/ ) {
  91             $line = <$fh>;
  92             last CONTRAINT_LOOP if $line =~ /ENGINE/i;
  93             $line =~ s/^ */ /;
  94             $constraint .= $line;
  95         }
  96         $constraint =~ s/^ *//;
  97         $constraint =~ s/\n//g;
  98         $constraint =~ s/ *$//;
  99         $constraint =~ s/,$//;
 100         my ($name) = $constraint =~ /CONSTRAINT (.*?) /;
 101         $name =~ s/\`//g;
 102         unless ( exists($db_constraint{$name}) ) {
 103             if ( $alterengine && !$engine_altered ) {
 104                 my $sql = "ALTER TABLE $table_name ENGINE = 'InnoDB'";
 105                 say $sql;
 106                 if ( $doit ) {
 107                     try {
 108                         $dbh->do($sql) if $doit;
 109                         $engine_altered = 1;
 110                     } catch {
 111                         say "Error: $_;";
 112                     };
 113                 }
 114             }
 115             my $sql = "ALTER TABLE $table_name ADD $constraint";
 116             say $sql;
 117             if ( $doit ) {
 118                 try {
 119                     $dbh->do($sql) if $doit;
 120                 } catch {
 121                     say "Error: $_";
 122                 }
 123             }
 124         }
 125         $line = <$fh> if $line =~ /CONSTRAINT/i;
 126     }
 127 }
 128
 129
 130 usage() if $help;
 131
 132 fix_mysql_constraints($doit);
 133
 134 =head1 NAME
 135
 136 fix_mysql_constraints.pl
 137
 138 =head1 SYNOPSIS
 139
 140   fix_mysql_constraints.pl --help
 141   fix_mysql_constraints.pl
 142   fix_mysql_constraints.pl --doit
 143
 144 =head1 DESCRIPTION
 145
 146 See bug #8915
 147
 148 Alter tables to add missing constraints. Prior to altering tables, it may be
 149 necessary to alter tables storage engine from MyISAM to InnoDB.
 150
 151 =over 8
 152
 153 =item B<--help>
 154
 155 Prints this help
 156
 157 =item B<--doit>
 158
 159 Alter tables effectively, otherwise just display the ALTER TABLE directives.
 160
 161 =item B<--alterengine>
 162
 163 Prior to add missing constraints, alter table engine to InnoDB.
 164
 165 =back
 166
 167 =cut