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

(-)a/misc/maintenance/fix_mysql_constraints.pl (-1 / +156 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 C4::Context;
32
33
34
my ($doit, $alterengine, $help);
35
my $result = GetOptions(
36
    'doit'        => \$doit,
37
    'alterengine' => \$alterengine,
38
    'help|h'      => \$help,
39
);
40
41
42
sub usage {
43
    pod2usage( -verbose => 2 );
44
    exit;
45
}
46
47
48
sub fix_mysql_constraints {
49
    my ($doit) = @_;
50
51
    # Get all current DB constraints
52
    my $dbh = C4::Context->dbh;
53
    $dbh->{RaiseError} = 1;
54
    $dbh->{ShowErrorStatement} = 1;
55
    my $database = C4::Context->config('database');
56
    my %db_constraint = map { $_->[0] => undef } @{$dbh->selectall_arrayref(
57
        "SELECT CONSTRAINT_NAME
58
           FROM information_schema.table_constraints
59
          WHERE constraint_schema = '$database'
60
            AND CONSTRAINT_TYPE != 'PRIMARY KEY' ")};
61
62
    my $base_dir = C4::Context->config('intranetdir');
63
    open my $fh, "<", "$base_dir/installer/data/mysql/kohastructure.sql";
64
    unless ($fh) {
65
        say "Unable to open kohastructure.sql file";
66
        exit;
67
    }
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
                $dbh->do($sql) if $doit;
107
                $engine_altered = 1;
108
            }
109
            my $sql = "ALTER TABLE $table_name ADD $constraint";
110
            say $sql;
111
            $dbh->do($sql) if $doit;
112
        }
113
        $line = <$fh> if $line =~ /CONSTRAINT/i;
114
    }
115
}
116
117
118
usage() if $help;
119
120
fix_mysql_constraints($doit);
121
122
=head1 NAME
123
124
fix_mysql_constraints.pl
125
126
=head1 SYNOPSIS
127
128
  fix_mysql_constraints.pl --help
129
  fix_mysql_constraints.pl
130
  fix_mysql_constraints.pl --doit
131
132
=head1 DESCRIPTION
133
134
See bug #8915
135
136
Alter tables to add missing constraints. Prior to altering tables, it may be
137
necessary to alter tables storage engine from MyISAM to InnoDB.
138
139
=over 8
140
141
=item B<--help>
142
143
Prints this help
144
145
=item B<--doit>
146
147
Alter tables effectively, otherwise just display the ALTER TABLE directives.
148
149
=item B<--alterengine
150
151
Prior to add missing constraints, alter table engine to InnoDB.
152
153
=back
154
155
=cut
156

Return to bug 8915