View | Details | Raw Unified | Return to bug 8915
Collapse All | Expand All

(-)a/misc/maintenance/fix_mysql_constraints.pl (-1 / +169 lines)
Line 0 Link Here
0
- 
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
    eval { require "$FindBin::Bin/../kohalib.pl" };
26
}
27
28
use Getopt::Long;
29
use Pod::Usage;
30
use YAML;
31
use Try::Tiny;
32
use C4::Context;
33
34
35
my ($doit, $alterengine, $help);
36
my $result = GetOptions(
37
    'doit'        => \$doit,
38
    'alterengine' => \$alterengine,
39
    'help|h'      => \$help,
40
);
41
42
43
sub usage {
44
    pod2usage( -verbose => 2 );
45
    exit;
46
}
47
48
49
sub fix_mysql_constraints {
50
    my ($doit) = @_;
51
52
    # Get all current DB constraints
53
    my $dbh = C4::Context->dbh;
54
    $dbh->{RaiseError} = 1;
55
    $dbh->{ShowErrorStatement} = 1;
56
    my $database = C4::Context->config('database');
57
    my %db_constraint = map { $_->[0] => undef } @{$dbh->selectall_arrayref(
58
        "SELECT CONSTRAINT_NAME
59
           FROM information_schema.table_constraints
60
          WHERE constraint_schema = '$database'
61
            AND CONSTRAINT_TYPE != 'PRIMARY KEY' ")};
62
63
    my $base_dir = C4::Context->config('intranetdir');
64
    open my $fh, "<", "$base_dir/installer/data/mysql/kohastructure.sql";
65
    unless ($fh) {
66
        say "Unable to open kohastructure.sql file";
67
        exit;
68
    }
69
70
    my $table_name;
71
    my $engine_altered;
72
    # FIXME: This hide problem. But if you run this script, it means that you
73
    # have already identified issues with your Koha DB integrity, and will fix
74
    # any necessary tables requiring records deleting.
75
    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
76
    my $line = <$fh>;
77
    while ( $line ) {
78
        if ( $line =~ /CREATE TABLE (.*?) / ) {
79
            $table_name = $1;
80
            $table_name =~ s/\`//g;
81
            $engine_altered = 0;
82
            $line = <$fh>;
83
            next;
84
        }
85
        unless ( $line =~ /CONSTRAINT /i ) {
86
            $line = <$fh>;
87
            next;
88
        }
89
        my $constraint = $line;
90
        CONTRAINT_LOOP:
91
        while ( $constraint !~ /,/ ) {
92
            $line = <$fh>;
93
            last CONTRAINT_LOOP if $line =~ /ENGINE/i;
94
            $line =~ s/^ */ /;
95
            $constraint .= $line;
96
        }
97
        $constraint =~ s/^ *//;
98
        $constraint =~ s/\n//g;
99
        $constraint =~ s/ *$//;
100
        $constraint =~ s/,$//;
101
        my ($name) = $constraint =~ /CONSTRAINT (.*?) /;
102
        $name =~ s/\`//g;
103
        unless ( exists($db_constraint{$name}) ) {
104
            if ( $alterengine && !$engine_altered ) {
105
                my $sql = "ALTER TABLE $table_name ENGINE = 'InnoDB'";
106
                say $sql;
107
                if ( $doit ) {
108
                    try {
109
                        $dbh->do($sql) if $doit;
110
                        $engine_altered = 1;
111
                    } catch {
112
                        say "Error: $_;";
113
                    };
114
                }
115
            }
116
            my $sql = "ALTER TABLE $table_name ADD $constraint";
117
            say $sql;
118
            if ( $doit ) {
119
                try {
120
                    $dbh->do($sql) if $doit;
121
                } catch {
122
                    say "Error: $_";
123
                }
124
            }
125
        }
126
        $line = <$fh> if $line =~ /CONSTRAINT/i;
127
    }
128
}
129
130
131
usage() if $help;
132
133
fix_mysql_constraints($doit);
134
135
=head1 NAME
136
137
fix_mysql_constraints.pl
138
139
=head1 SYNOPSIS
140
141
  fix_mysql_constraints.pl --help
142
  fix_mysql_constraints.pl
143
  fix_mysql_constraints.pl --doit
144
145
=head1 DESCRIPTION
146
147
See bug #8915
148
149
Alter tables to add missing constraints. Prior to altering tables, it may be
150
necessary to alter tables storage engine from MyISAM to InnoDB.
151
152
=over 8
153
154
=item B<--help>
155
156
Prints this help
157
158
=item B<--doit>
159
160
Alter tables effectively, otherwise just display the ALTER TABLE directives.
161
162
=item B<--alterengine
163
164
Prior to add missing constraints, alter table engine to InnoDB.
165
166
=back
167
168
=cut
169

Return to bug 8915