#!/usr/local/bin/perl

use strict;
use warnings;
use feature qw/say/;
use Data::Dumper;
use Time::HiRes qw/gettimeofday tv_interval/;
use DBI;

#--------------------------------------------------------
sub rowcnt
{
    my $dbh  = shift;

    my $rowcnt = $dbh->selectrow_array('select count(*) from speedtest');

    return $rowcnt;
}
#--------------------------------------------------------
sub gencurr
{
    my @set = 'A' .. 'F';
    return join '' => map $set[rand @set], 1 .. 3;
}
#--------------------------------------------------------
sub genstr
{
    my $len = shift;

    return sprintf("%08X", rand(0x7fffffff));
}
#--------------------------------------------------------
sub gennum
{
    return sprintf("%08d", rand(0x7fffffff));
}
#--------------------------------------------------------
sub gendate
{
    #     0    1    2     3     4    5     6     7     8
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
            localtime(rand(0xffffffff));

    return sprintf("%04d-%02d-%02d", $year+1900, $mon+1, $mday);
}
#--------------------------------------------------------
sub genreal
{
    return sprintf("%10.6f", rand(0xffffffff));
}
#--------------------------------------------------------
sub gendata
{
    my $count = shift;

    my $d;

    for(my $i=0; $i<$count; ++$i) {

        $d->{$i}->{id}     = gennum();
        $d->{$i}->{name}   = genstr();
        $d->{$i}->{aod}    = gendate();
        $d->{$i}->{curr1}  = gencurr();
        $d->{$i}->{price1} = genreal();
        $d->{$i}->{name}   = genstr();
        $d->{$i}->{curr2}  = gencurr();
        $d->{$i}->{price2} = genreal();
   }

   return $d;
}
#--------------------------------------------------------
sub testins1
{
    my $dbh  = shift;
    my $data = shift;

    say 'positional bind_param and execute by row';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (?, ?, ?, ?, ?, ?, ?)
    };

    my $isth = $dbh->prepare($isql);

    for my $i (sort keys %$data) {
        $isth->bind_param(1, $data->{$i}->{id});
        $isth->bind_param(2, $data->{$i}->{name});
        $isth->bind_param(3, $data->{$i}->{aod});
        $isth->bind_param(4, $data->{$i}->{curr1});
        $isth->bind_param(5, $data->{$i}->{price1});
        $isth->bind_param(6, $data->{$i}->{curr2});
        $isth->bind_param(7, $data->{$i}->{price2});
        $isth->execute;
    }

    $isth->finish;
    $dbh->commit;
}
#--------------------------------------------------------
sub testins2
{
    my $dbh  = shift;
    my $data = shift;

    say 'positional execute and execute by row';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (?, ?, ?, ?, ?, ?, ?)
    };

    my $isth = $dbh->prepare($isql);

    my @fields = qw/id name aod curr1 price1 curr2 price2/;
    for my $i (sort keys %$data) {
        $isth->execute(@{$data->{$i}}{@fields});
    }

    $isth->finish;
    $dbh->commit;
}
#--------------------------------------------------------
sub testins3
{
    my $dbh  = shift;
    my $data = shift;

    say 'positional execute array';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (?, ?, ?, ?, ?, ?, ?)
    };

    my $isth = $dbh->prepare($isql);

    my $id     = [map { $data->{$_}->{id}     } sort keys %$data];
    my $name   = [map { $data->{$_}->{name}   } sort keys %$data];
    my $aod    = [map { $data->{$_}->{aod}    } sort keys %$data];
    my $curr1  = [map { $data->{$_}->{curr1}  } sort keys %$data];
    my $price1 = [map { $data->{$_}->{price1} } sort keys %$data];
    my $curr2  = [map { $data->{$_}->{curr2}  } sort keys %$data];
    my $price2 = [map { $data->{$_}->{price2} } sort keys %$data];

    my $t0 = [gettimeofday];
    my $rv = $isth->execute_array({ ArrayTupleStatus => \my @tuple_status },
            $id, $name, $aod, $curr1, $price1, $curr2, $price2);
    my $elapsed = tv_interval($t0, [gettimeofday]);
    say "\texecute_array takes $elapsed sec, $rv rows";
    my @err = grep {$_ != -1} @tuple_status;
    say "\terr: @err";

    $isth->finish;
    $dbh->commit;
}
#--------------------------------------------------------
sub testins4
{
    my $dbh  = shift;
    my $data = shift;

    say 'bind_param by name and execute by row';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (:id, :name, :aod, :curr1, :price1, :curr2, :price2)
    };

    my $isth = $dbh->prepare($isql);

    for my $i (sort keys %$data) {
        $isth->bind_param(':id',     $data->{$i}->{id});
        $isth->bind_param(':name',   $data->{$i}->{name});
        $isth->bind_param(':aod',    $data->{$i}->{aod});
        $isth->bind_param(':curr1',  $data->{$i}->{curr1});
        $isth->bind_param(':price1', $data->{$i}->{price1});
        $isth->bind_param(':curr2',  $data->{$i}->{curr2});
        $isth->bind_param(':price2', $data->{$i}->{price2});
        $isth->execute;
    }

    $isth->finish;
    $dbh->commit;
}
#========================================================
$Data::Dumper::Sortkeys = 1;
my $dbname = '???';
my $user   = '???';
my $passwd = '???';
my $count = $ARGV[0] // 1;
my $verbose = 0;

$| = 1;

my $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd,
        {
            AutoCommit => 0,
            RaiseError => 1,
            RowCacheSize => 10000,
            ora_array_chunk_size => 100000,
            #ora_check_sql => 0,
            #ora_verbose => 0xff,
            #ora_oci_success_warn => 1,
        }
);

print 'DBI version: ', $DBI::VERSION, "\n";
print 'DBD version: ', $DBD::Oracle::VERSION, "\n";

$dbh->do("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'");
$dbh->do("alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF'");

say "RowCacheSize: ", $dbh->{RowCacheSize};

my $data = gendata($count);
say Dumper($data) if $verbose;

my ($t0, $elapsed);

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed";
$t0 = [gettimeofday];
testins1($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "1 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed";
$t0 = [gettimeofday];
testins2($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "2 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed";
$t0 = [gettimeofday];
testins3($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "3 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed";
#$dbh->{ora_verbose} = 6;
#$dbh->{ora_oci_success_warn} = 1;
$t0 = [gettimeofday];
testins4($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "4 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->disconnect;

exit 0;

__END__