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