#!/usr/bin/perl
# (c) 2008 Philipp Kolmann
# philipp@kolmann.at

use DBI;
use strict;
use Data::Dumper;
use utf8;
use Encode;

my $old_dsn = "DBI:mysql:hostname=old.box.at;port=3306";
my $new_dsn = "DBI:mysql:hostname=localhost;port=3306";

my $old = DBI->connect($old_dsn, 'user', 'pass');
my $new = DBI->connect($new_dsn, 'user', 'pass');

my $query;
my $res;
my $res_hash;

$query  = "SHOW databases";
$res = $new->prepare($query);
$res->execute;

while ($res_hash= $res->fetchrow_hashref()) {
  next if ($res_hash->{Database} =~ /mysql/);
  next if ($res_hash->{Database} =~ /information_schema/);
  doDatabase($res_hash->{Database});
}

sub doDatabase {
  my ($database) = @_;

  print "$database\n";

  my $query;
  my $res;
  my $result;

  $query = "USE $database";
  $res = $old->prepare($query);
  $res->execute;

  $query = "USE $database";
  $res = $new->prepare($query);
  $res->execute;
  
  $query = "SHOW tables";
  $res = $old->prepare($query);
  $res->execute;
  
  while ($result = $res->fetchrow_arrayref()) {
    doTable($database, 1, $result->[0]);
  }

}

sub doTable {
  my ($database, $drop, $table) = @_;

  print "  $table\n";

  my $query;
  my $res;
  my @result;
  my $octets;

  $query = "SELECT * from $table;";
  $res = $old->prepare($query);
  $res->execute;
  
  while (@result = $res->fetchrow_array) {
    my $insert;
    $insert = "INSERT IGNORE INTO $table values(";
    for (my $i=0; $i<=$#result; $i++) {
      my $string = $result[$i];
      $string = $new->quote($string);
      utf8::encode($string);
      $insert .= $string;
      $insert .= ", " if ($i<$#result);
    }
    $insert .= ")";

#   if you use utf8::decode above, then you need to encode again
#    $octets = encode("iso-8859-1", $insert);
    $octets = $insert;
    print "$octets\n";

    my $ins = $new->prepare($octets);
    $ins->execute;

  }
}