mysql_convert_table_format.sh 4.07 KB
Newer Older
1
#!/usr/bin/perl
unknown's avatar
unknown committed
2 3 4 5 6 7 8 9 10 11 12 13 14 15
# Copyright (C) 2000-2002 MySQL AB
# 
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
unknown's avatar
unknown committed
16 17 18 19 20 21 22 23 24

# Convert given tables in a database to MYISAM

use DBI;
use Getopt::Long;

$opt_help=$opt_version=$opt_verbose=$opt_force=0;
$opt_user=$opt_database=$opt_password=undef;
$opt_host="localhost";
unknown's avatar
unknown committed
25
$opt_socket="";
26
$opt_engine="MYISAM";
unknown's avatar
unknown committed
27
$opt_port=0;
unknown's avatar
unknown committed
28 29
$exit_status=0;

30
GetOptions(
31
  "e|engine|type=s"       => \$opt_engine,
32 33 34 35 36 37 38 39 40 41 42
  "f|force"               => \$opt_force,
  "help|?"               => \$opt_help,
  "h|host=s"              => \$opt_host,
  "p|password=s"          => \$opt_password,
  "u|user=s"              => \$opt_user,
  "v|verbose"             => \$opt_verbose,
  "V|version"             => \$opt_version,
  "S|socket=s"            => \$opt_socket, 
  "P|port=i"              => \$opt_port
) || usage(0);

unknown's avatar
unknown committed
43
usage($opt_version) if ($#ARGV < 0 || $opt_help || $opt_version);
44

unknown's avatar
unknown committed
45 46
$opt_database=shift(@ARGV);

47
if (grep { /^$opt_engine$/i } qw(HEAP MEMORY BLACKHOLE))
unknown's avatar
unknown committed
48
{
49
  print "Converting to '$opt_engine' would delete your data; aborting\n";
unknown's avatar
unknown committed
50 51 52
  exit(1);
}

unknown's avatar
unknown committed
53 54 55 56 57 58 59 60 61 62 63
$connect_opt="";
if ($opt_port)
{
  $connect_opt.= ";port=$opt_port";
}
if (length($opt_socket))
{
  $connect_opt.=";mysql_socket=$opt_socket";
}

$dbh = DBI->connect("DBI:mysql:$opt_database:${opt_host}$connect_opt",
unknown's avatar
unknown committed
64 65 66 67 68
		    $opt_user,
		    $opt_password,
		    { PrintError => 0})
  || die "Can't connect to database $opt_database: $DBI::errstr\n";

69 70 71 72 73
my @tables;

push(@ARGV, "%") if(!@ARGV);

foreach $pattern (@ARGV)
unknown's avatar
unknown committed
74 75
{
  my ($sth,$row);
76 77 78 79 80 81 82
  $sth=$dbh->prepare("SHOW TABLES LIKE ?");
  $rv= $sth->execute($pattern);
  if(!int($rv))
  {
    warn "Can't get tables matching '$pattern' from $opt_database; $DBI::errstr\n"; 
    exit(1) unless $opt_force;
  }
unknown's avatar
unknown committed
83 84
  while (($row = $sth->fetchrow_arrayref))
  {
85
    push(@tables, $row->[0]);
unknown's avatar
unknown committed
86 87 88 89 90
  }
  $sth->finish;
}

print "Converting tables:\n" if ($opt_verbose);
91
foreach $table (@tables)
unknown's avatar
unknown committed
92 93 94 95 96 97 98
{
  my ($sth,$row);

  # Check if table is already converted
  $sth=$dbh->prepare("show table status like '$table'");  
  if ($sth->execute && ($row = $sth->fetchrow_arrayref))
  {
99
    if (uc($row->[1]) eq uc($opt_engine))
unknown's avatar
unknown committed
100
    {
101
      print "$table already uses the '$opt_engine' engine;  Ignored\n";
unknown's avatar
unknown committed
102 103 104 105
      next;
    }
  }
  print "converting $table\n" if ($opt_verbose);
106 107
  $table=~ s/`/``/g;
  if (!$dbh->do("ALTER TABLE `$table` ENGINE=$opt_engine"))
unknown's avatar
unknown committed
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
  {
    print STDERR "Can't convert $table: Error $DBI::errstr\n";
    exit(1) if (!$opt_force);
    $exit_status=1;
  }
}

$dbh->disconnect;
exit($exit_status);


sub usage
{
  my($version)=shift;
  print "$0  version 1.1\n";
  exit(0) if ($version);

  print <<EOF;

127
Conversion of a MySQL tables to other storage engines
unknown's avatar
unknown committed
128

129
 Usage: $0 database [table[ table ...]]
unknown's avatar
unknown committed
130
 If no tables has been specifed, all tables in the database will be converted.
131
 You can also use wildcards, ie "my%"
unknown's avatar
unknown committed
132 133 134

 The following options are available:

135
-f, --force
unknown's avatar
unknown committed
136 137
  Continue even if there is some error.

138
-?, --help
unknown's avatar
unknown committed
139 140
  Shows this help

141
-e, --engine=ENGINE
142 143
  Converts tables to the given storage engine (Default: $opt_engine)

144 145
-h, --host=HOST
  Host name where the database server is located. (Default: $opt_host)
unknown's avatar
unknown committed
146

147
-p, --password=PASSWORD
unknown's avatar
unknown committed
148 149
  Password for the current user.

150
-P, --port=PORT
unknown's avatar
unknown committed
151 152
  TCP/IP port to connect to if host is not "localhost".

153
-S, --socket=SOCKET
unknown's avatar
unknown committed
154 155
  Socket to connect with.

156
-u, --user=USER
unknown's avatar
unknown committed
157 158
  User name to log into the SQL server.

159
-v, --verbose
unknown's avatar
unknown committed
160 161 162
  This is a test specific option that is only used when debugging a test.
  Print more information about what is going on.

163
-V, --version
unknown's avatar
unknown committed
164 165 166 167
  Shows the version of this program.
EOF
  exit(1);
}