3 # Copyright (C) 2012 Tamil s.a.r.l.
5 # This file is part of Koha.
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.
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.
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>.
22 use Getopt::Long qw( GetOptions );
23 use Pod::Usage qw( pod2usage );
24 use Try::Tiny qw( catch try );
29 my ($doit, $alterengine, $help);
30 my $result = GetOptions(
32 'alterengine' => \$alterengine,
38 pod2usage( -verbose => 2 );
43 sub fix_mysql_constraints {
46 # Get all current DB constraints
47 my $dbh = C4::Context->dbh;
48 $dbh->{RaiseError} = 1;
49 $dbh->{ShowErrorStatement} = 1;
50 my $database = C4::Context->config('database');
51 my %db_constraint = map { $_->[0] => undef } @{$dbh->selectall_arrayref(
52 "SELECT CONSTRAINT_NAME
53 FROM information_schema.table_constraints
54 WHERE constraint_schema = '$database'
55 AND CONSTRAINT_TYPE != 'PRIMARY KEY' ")};
57 my $base_dir = C4::Context->config('intranetdir');
58 open my $fh, "<", "$base_dir/installer/data/mysql/kohastructure.sql"
59 or die "Unable to open kohastructure.sql file";
63 # FIXME: This hide problem. But if you run this script, it means that you
64 # have already identified issues with your Koha DB integrity, and will fix
65 # any necessary tables requiring records deleting.
66 $dbh->do("SET FOREIGN_KEY_CHECKS=0");
69 if ( $line =~ /CREATE TABLE (.*?) / ) {
71 $table_name =~ s/\`//g;
76 unless ( $line =~ /CONSTRAINT /i ) {
80 my $constraint = $line;
82 while ( $constraint !~ /,/ ) {
84 last CONTRAINT_LOOP if $line =~ /ENGINE/i;
88 $constraint =~ s/^ *//;
89 $constraint =~ s/\n//g;
90 $constraint =~ s/ *$//;
91 $constraint =~ s/,$//;
92 my ($name) = $constraint =~ /CONSTRAINT (.*?) /;
94 unless ( exists($db_constraint{$name}) ) {
95 if ( $alterengine && !$engine_altered ) {
96 my $sql = "ALTER TABLE $table_name ENGINE = 'InnoDB'";
100 $dbh->do($sql) if $doit;
107 my $sql = "ALTER TABLE $table_name ADD $constraint";
111 $dbh->do($sql) if $doit;
117 $line = <$fh> if $line =~ /CONSTRAINT/i;
124 fix_mysql_constraints($doit);
128 fix_mysql_constraints.pl
132 fix_mysql_constraints.pl --help
133 fix_mysql_constraints.pl
134 fix_mysql_constraints.pl --doit
140 Alter tables to add missing constraints. Prior to altering tables, it may be
141 necessary to alter tables storage engine from MyISAM to InnoDB.
151 Alter tables effectively, otherwise just display the ALTER TABLE directives.
153 =item B<--alterengine>
155 Prior to add missing constraints, alter table engine to InnoDB.