#!/usr/bin/perl -w
use strict;
use DBI;
use Date::Calc qw(:all);
use Switch;
use Time::HiRes qw/ time gettimeofday tv_interval /;
$| = 1;
my @Month_name = ( "JAN", "FEB", "MAR", "APR", "MAY", "JUN",
  "JUL", "AUG", "SET", "OCT", "NOV", "DEC" );
sub tstampms {
my $u_sec = time;
my @date = localtime($u_sec);
my $format = sprintf(
    "%4d-%s-%02d %02d:%02d:%02d.%06d",
    $date[5] + 1900,
    $Month_name[$date[4]],
    $date[3],
    $date[2],
    $date[1],
    $date[0],
    substr($u_sec, index($u_sec, '.') + 1)
);
print $format, "\n";}
#defalt name for the table
my $hbn = 'trades';
$hbn = $ARGV[0] if $#ARGV > -1;
# mysql database information
my $db="db";
my $host="localhost";
my $userid="user";
my $passwd="password";
my $connectionInfo="dbi:mysql:$db;$host";
# make connection to database
my $dbh = DBI->connect($connectionInfo,$userid,$passwd)
    || die( $DBI::errstr . "\n" );
# database information to connect to ORACLE
my $port1='1521';
my $host1="192.168.133.127";
my $sid1="oradb1";
my $userid1="user";
my $passwd1="password";
#my $connectionInfo1="dbi:Oracle:HOST=$host1;SERVICE_NAME=$service1;PORT=$port1";
my $connectionInfo1="dbi:Oracle:HOST=$host1;SID=$sid1;PORT=$port1";
# connect to the Oracle server
my $db1 = DBI->connect( $connectionInfo1, $userid1, $passwd1 )
    || die( $DBI::errstr . "\n" );
$db1->{AutoCommit}    = 0;
$db1->{RaiseError}    = 1;
$db1->{ora_check_sql} = 0;
$db1->{RowCacheSize}  = 16;
# primary key
my $SEL2 = qq[SELECT cols.column_name
		 FROM all_constraints cons, all_cons_columns cols
		 WHERE cols.table_name = ?
		 AND cons.constraint_type = 'P'
		 AND cons.constraint_name = cols.constraint_name
		 AND cons.owner = cols.owner];
my $sth1 = $db1->prepare($SEL2);
$sth1->execute(uc($hbn));
my @row = $sth1->fetchrow_array();
my $PKeyn = $row[0];
print "Key field $PKeyn\n";
# get fields info trasformed to mysql format
my $SEL3 = qq[select column_name, 
        ( CASE data_type
           When 'VARCHAR2' THEN 'VARCHAR'||'('||data_length||')'
           When 'DATE' THEN 'DATE'
           When 'NUMBER' THEN 'DOUBLE'||
        ( CASE nvl(data_precision,-1)
          when -1 then null
          else '('||data_precision||','||data_scale||')'
          end )           End) data_typ,
        decode(NULLABLE,'N','NOT NULL') nl  
from ALL_TAB_COLUMNS 
where table_name = ?
order by table_name,column_id];
# excute SQL
my $sth2 = $db1->prepare($SEL3);
$sth2->execute(uc($hbn));
#drop table in mysql db schema
my $query = "DROP TABLE IF EXISTS $hbn";
print "$query\n";
my $sth = $dbh->prepare($query);
$sth->execute();
#construct create sql statement for Mysql
$query = "CREATE TABLE $hbn (\n";
while ( my @row = $sth2->fetchrow_array() ) {
    foreach (@row) {
        $_ = ' ' if !defined($_);
        $query .= "$_ ";
    } 
	$query .= ",\n";
}
$query .= "  PRIMARY KEY ($PKeyn)\n";
$query .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1\n\n";
print "$query\n";
$sth2->finish();
#execute creat table
$sth = $dbh->prepare($query);
$sth->execute();
# creat select random row fom oracle table
$SEL3 = qq[SELECT * FROM
( SELECT * FROM ].uc($hbn).qq[ 
ORDER BY dbms_random.value )
WHERE rownum < 11];
# excute SQL
$sth2 = $db1->prepare($SEL3);
$sth2->execute();
# assemble insert sql form results of Oracle query
while ( my @row = $sth2->fetchrow_array() ) {
    $query = "INSERT INTO $hbn VALUES (";
	my $j = 0;
    foreach my $fl (@row) {
    my $type2 = $sth2->{TYPE}[$j];
	$j++;
	switch ($type2) {
		case 12 {$fl = '\''.$fl.'\'';}
		case 93 {$fl = '\''.$fl.'\'';
				$fl =~ s/JAN/01/;
				$fl =~ s/FEB/02/;
				$fl =~ s/MAR/03/;
				$fl =~ s/APR/04/;
				$fl =~ s/MAY/05/;
				$fl =~ s/JUN/06/;
				$fl =~ s/JUL/07/;
				$fl =~ s/AUG/08/;
				$fl =~ s/SEP/09/;
				$fl =~ s/OCT/10/;
				$fl =~ s/NOV/11/;
				$fl =~ s/DEC/12/;
				}
#		else { print "previous case $type2 not true" } 
	}
	$query .= "$fl";
    $query .= "," if $j <	$sth2->{NUM_OF_FIELDS};
    } 
	$query .= ")";
	print "$query\n";
    # do the insert in mysql table
	$sth = $dbh->prepare($query);
	$sth->execute();
}
print "\n";
# an other way of getting the oracle table fields information
my $SEL1 = qq[select * from ].$hbn.qq[ where 0 = 1];
# excute SQL
$sth1 = $db1->prepare($SEL1);
$sth1->execute();
my $nf = $sth1->{NUM_OF_FIELDS};
for ( my $l = 0; $l < $nf; $l++ ) {
    my $name1 = $sth1->{NAME}[$l];
    my $type1 = $sth1->{TYPE}[$l];
    my $prec1 = $sth1->{PRECISION}[$l];
    my $scle1 = $sth1->{SCALE}[$l];
    my $tn1=$db1->type_info($type1)->{TYPE_NAME};
	$tn1 =~ s/ PRECISION//;
	$tn1 =~ s/VARCHAR2/VARCHAR/;
	$tn1 =~ s/DECIMAL/DOUBLE/;
    print
        "Field number $l: name $name1 of type $type1 $tn1 with precision $prec1,$scle1\n";
	#print "$name1 $tn1($prec1";
	#print ",$scle1" if $scle1 > 0; 
	#print ")  ,\n";
}
tstampms();
# Close all connections
END {
    $dbh->disconnect if defined($dbh);
    $db1->disconnect if defined($db1);
}