oracle - mysql to oracle(perl)

#!/usr/bin/perl -w

$|++;

################################################################################

# Migrate rt3 tablespaces from mysql to oracle.

# Could Easily Be adapted for postgres as well

# This assumes that you have the new system installed and the required Oracle Tables have been created and

# Of course that they are the same names as the original mysql tables.

# This script could be optimized in many ways.

# However the safety of the data move was the primary design goal.

# Not Speed, Neatness, or "There is a Better way".

# The Oracle inserts are transactional, and the script

# will Rollback and die the entire data move.

# On a single failure of any kind.....

# Converted pg -> mysql script from rt3-on-pg-to-mysql

# You can distribute this script under the same license terms and restrictions of perl itself.

# (c) 2005 Carapace Software, Inc.

################################################################################

package main;

################################################################################

# Setup vars

################################################################################

#MYSQL SETUP

$mysql::dbname="rt3";

$mysql::username="rt3";

$mysql::password="password";

$mysql::host="tracking";

$mysql::port=3306;

################################################################################

#ORACLE SETUP

$oracle::dbname="rt3";

$oracle::sid="production";

$oracle::username="rt3";

$oracle::password="password";

$oracle::host="prodorcl";

$oracle::port=1521;

################################################################################

use strict;

use DBI;

my $data_mover = RT3::Migrate::Mysql2Oracle->new(

 MySql=>{

   DataBaseName=>$mysql::dbname,

       UserName=>$mysql::username,

       PassWord=>$mysql::password,

           Host=>$mysql::host,

           Port=>$mysql::port

        },

   Oracle=>{

      DataBaseName=>$oracle::dbname,

                     SID=>$oracle::sid,

          UserName=>$oracle::username,

          PassWord=>$oracle::password,

              Host=>$oracle::host,

              Port=>$oracle::port

           }

);

 #Turn on the Level of Verbosity.

 #0 silent,1 Basic,2 Alot,3 Everything

 $data_mover->{"Verbose"}=3;

 foreach my $table(@{$data_mover->{'tables'}}){

        if (! $data_mover->Migrate($table)){

    last;

         };

 };

 foreach my $table(@{$data_mover->{'tables'}}){

     if (! $data_mover->ValidateMove($table)){

         print "$table Not in sync\n";

     } else {

         print "$table in Sync\n";

     };

 };

$data_mover->Destroy();

exit 0;

#END MAIN

###########################################################

#The Guts

package RT3::Migrate::Mysql2Oracle;

use strict;

use DBI;

sub new {

    my $proto = shift;

    my $class = ref($proto) || $proto;

    my $self  = {};

    bless( $self, $class );

    $self->_Init(@_);

    $self->{"Verbose"} = 3;

    return ($self);

};

sub _Init{

 my $self = shift;

 my %args = (@_);

    foreach my $database(sort keys(%args)){

        $self->_Connect($database,$args{$database});

    };

};

sub _Connect{

 my $self = shift;

 my ($database,$args) = @_;

    my $dbi = $database;

 #funky hack on mysql dbi name

    $dbi =~s/^MySql/mysql/;

    #Build up the Connect String

    my $cs = "dbi:$dbi:";

       $cs.= "host=$args->{'Host'}\;";

       $cs.= "sid=$args->{'SID'}\;" if $args->{'SID'};

       $cs.= "database=$args->{'DataBaseName'}\;";

       $cs.= "port=$args->{'Port'}";

     print "Connecting to $cs\n";

    #Make the Relevant Connection

    $self->{$database}=DBI->connect($cs,

                                    $args->{'UserName'},

                                    $args->{'PassWord'}

                                    ) or die DBI->errstr();

 if (defined($self->{$database})

        && $database =~ /mysql/i)

        {

         $self->_GrabTables();

  };

 if (defined($self->{$database})

 && $database =~/oracle/i)

 {

                my $altersql =<<EOF

 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

EOF

;

                my $alter=$self->{'Oracle'}->prepare($altersql) || die @$;

                $alter->execute() || die @$;

 };

};

#Grab the List of Tables from the Mysql Instance

#If your dataset is pure rt3 and you created

#your oracle instance properly. This should work fine...

sub _GrabTables{

 my $self = shift;

    my $sth  = $self->{'MySql'}->prepare('show tables') or die $self->{'MySql'}->errstr();

    $sth->execute() or die $self->{'MySql'}->errstr();

 my @tables;

 while(@tables=$sth->fetchrow_array()){

        #Ignoring RTFM and RTIR tables

        next if ($tables[0] =~ /^FM/i);

        #No reason to copy sessions;

        next if ($tables[0] =~ /sessions/i);

  push(@{$self->{'tables'}},$tables[0]);

 };

 $sth->finish();

};

sub _Disconnect{

  my $self = shift;

  $self->{'MySql'}->disconnect() if ($self->{'MySql'});

  $self->{'Oracle'}->disconnect() if ($self->{'Oracle'});

};

sub DataDump{

 use Data::Dumper;

 return Data::Dumper::Dumper(@_);

};

sub Destroy{

 my $self = shift;

 $self->_Disconnect();

};

sub Migrate{

 my $self =shift;

 my $tablename = shift;

    if ($self->{"Verbose"}){

   print "\nMigrating $tablename\n";

        };

 my $sql = qq(select * from $tablename);

    if ($self->{"Verbose"} > 1){

     print "Built Query: $sql\n";

    };

 my $sth = $self->{'MySql'}->prepare($sql) || die $@;

 $self->_copy_table_rows($tablename,$sth);

    return $sth->finish();

};

sub _copy_table_rows {

    my $self = shift;

 my $table = shift;

 my $sth = shift;

 $sth->execute() or die $self->{'MySql'}->errstr();

 while (my $row = $sth->fetchrow_hashref) {

  my @keys;

  my @values;

  my @placeholders;

  foreach my $key (keys %$row) {

            if (! $row->{$key}){

             $row->{$key}='';

            };

   if ($self->{"Verbose"} > 2){

   print "$key = $row->{$key}\n" unless $table=~/attachments/i;

   };

    push @keys, $key;

   push @values, $row->{$key};

   push @placeholders, '?';

  }

     my $insert = "INSERT into $table (".join(',',@keys).") VALUES (".join(',',@placeholders).")\n";

        if ($self->{"Verbose"} > 2){

         print "INSERT STRING: $insert\n";

        };

         my $sth= $self->{'Oracle'}->prepare($insert) || die  $@;

      $sth->execute(@values) || die $@ . "\n ".join(",",@values);

 }

}

sub ValidateMove{

   my $self = shift;

   my $table = shift;

    my $sql =<<EOF

select count(*) from $table

EOF

;

 my $sth1 = $self->{'MySql'}->prepare($sql);

 my $sth2 = $self->{'Oracle'}->prepare($sql);

    my $count1;

    my $count2;

    $sth1->execute();

    $sth2->execute();

    $sth1->bind_col(1,\$count1);

    $sth2->bind_col(1,\$count2);

    $sth1->fetch();

    $sth2->fetch();

    $sth1->finish();

    $sth2->finish();

    print "$table Mysql($count1) - Oracle($count2)\n";

    if ($count1 == $count2){

     return 1;

    } else {

       return 2;

    };

};