#!/usr/bin/perl -w

#
# convert_engine.pl
# 
# Perl Script that converts tables in a specific schema from 
# one storage engine to another
# 
# Written by Patrick 'CaptTofu' Galbraith <patg@patg.net>
#
# 
# 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; either version 2 of the License, or
# (at your option) any later version.
# 
# 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., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
use strict;
use DBI;
use Data::Dumper;
use Getopt::Long;
use Time::HiRes qw(gettimeofday tv_interval);

our $from_engine;
our $to_engine;
our $schema;
our $user;
our $password;
our $opt_verbose;
our $host;
our $opt_dry_run;
our $opt_help;

GetOptions (
  
  'd|dry-run'     => \$opt_dry_run,
  'f|from=s'      => \$from_engine,
  'h|host=s'      => \$host,
  'p|password=s'  => \$password,
  't|to=s'        => \$to_engine,
  's|schema=s'    => \$schema,
  'u|user=s'      => \$user,
  'v|verbose'     => \$opt_verbose,
  'help'          => \$opt_help,
);

usage() if $opt_help;
$schema || usage("No schema supplied!");
$from_engine || usage("You must supply an engine type to convert from!");
$to_engine || usage("You must supply an engine type to convert to!");

$user||= 'root';
$password||= '';
$host||= 'localhost';

sub main {
  my $dbh= DBI->connect("DBI:mysql:database=information_schema;host=$host",
            $user, $password, {RaiseError => 1}); 

  my $sth = $dbh->prepare("select table_name from tables where table_schema= ? and table_type = 'BASE TABLE' and engine = ?");

  my $ret= $sth->execute($schema, $from_engine);
  my $tref= $sth->fetchall_arrayref();

  my $t0= [gettimeofday]; 
  for (@$tref) { 
    my $alter= 'ALTER TABLE ' . $schema . '.' . $_->[0] . ' ENGINE=' . $to_engine;
    print "Altering table " . $_->[0] . " from $from_engine to $to_engine $alter... " if $opt_verbose;
    $dbh->do($alter) or die "Unable to alter table $! $DBI::errstr" unless $opt_dry_run;
    print "Done.\n" if $opt_verbose;
  }
  my $elapsed = tv_interval ( $t0, [gettimeofday]);
  $elapsed = $elapsed >= 60 ? $elapsed/60 . ' minutes' : $elapsed . " seconds";
  print "Conversion of all tables in $schema schema (" . @$tref . ") tables from $from_engine to $to_engine: $elapsed\n";

}

sub usage {
  my ($message)= @_;

  print "ERROR: $message\n" if $message;
  print <<EOT;
  -d, -dry-run                      Dry run, don't actually perform conversion 
  -f <engine>,  --from=<engine>    The storage engine converting from 
  -p <pass>,    --password=<pass>  DB Password for user who has alter privs 
  -t <engine>, --to=<engine>       The storage engine converting to 
  -s <schema>, --schema <schema>   Schema name 
  -u <user>,   --user=<user>       DB Username for user who has alter privs 
  -v, --verbose                    Verbose flag 
EOT
  exit(0);
  'f|from=s'      => \$from_engine,
  'help'          => \$opt_help,
  'h|host=s'      => \$host,
  'p|password=s'  => \$password,
  't|to=s'        => \$to_engine,
  's|schema=s'    => \$schema,
  'u|user=s'      => \$user,
  'v|verbose'     => \$opt_verbose,
}

main();