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 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
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.
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.
22 # find Koha's Perl modules
23 # test carefully before changing this
25 eval { require "$FindBin::Bin/../kohalib.pl" };
35 my ($doit, $alterengine, $help);
36 my $result = GetOptions(
38 'alterengine' => \$alterengine,
44 pod2usage( -verbose => 2 );
49 sub fix_mysql_constraints {
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' ")};
63 my $base_dir = C4::Context->config('intranetdir');
64 open my $fh, "<", "$base_dir/installer/data/mysql/kohastructure.sql";
66 say "Unable to open kohastructure.sql file";
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");
78 if ( $line =~ /CREATE TABLE (.*?) / ) {
80 $table_name =~ s/\`//g;
85 unless ( $line =~ /CONSTRAINT /i ) {
89 my $constraint = $line;
91 while ( $constraint !~ /,/ ) {
93 last CONTRAINT_LOOP if $line =~ /ENGINE/i;
97 $constraint =~ s/^ *//;
98 $constraint =~ s/\n//g;
99 $constraint =~ s/ *$//;
100 $constraint =~ s/,$//;
101 my ($name) = $constraint =~ /CONSTRAINT (.*?) /;
103 unless ( exists($db_constraint{$name}) ) {
104 if ( $alterengine && !$engine_altered ) {
105 my $sql = "ALTER TABLE $table_name ENGINE = 'InnoDB'";
109 $dbh->do($sql) if $doit;
116 my $sql = "ALTER TABLE $table_name ADD $constraint";
120 $dbh->do($sql) if $doit;
126 $line = <$fh> if $line =~ /CONSTRAINT/i;
133 fix_mysql_constraints($doit);
137 fix_mysql_constraints.pl
141 fix_mysql_constraints.pl --help
142 fix_mysql_constraints.pl
143 fix_mysql_constraints.pl --doit
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.
160 Alter tables effectively, otherwise just display the ALTER TABLE directives.
162 =item B<--alterengine
164 Prior to add missing constraints, alter table engine to InnoDB.