#!/usr/bin/perl
use DBI;
use Benchmark ':hireswallclock';
use POSIX qw( floor );
use Getopt::Long;

$opt_loop_count=100000;         # number of loops(rows)

$opt_db_host="localhost";
$opt_db_socket="/tmp/mysql.sock";
$opt_db_name="test";
$opt_db_engine="InnoDB";

$opt_buffering="";
$db_stat=1;
$opt_rows="";

GetOptions("db-socket=s","db-engine=s","buffering=s","rows=s");

$opt_loop_count=$opt_rows if ($opt_rows);
           
$dbh = DBI->connect("dbi:mysql:$opt_db_name;host=$opt_db_host;mysql_socket=$opt_db_socket",
                    "root","", { PrintError => 0}) or die $DBI::errstr;

$sth = $dbh->prepare("select VERSION()") or die $DBI::errstr;
$version="MySQL ?";
if ($sth->execute && (@row = $sth->fetchrow_array))
{
  $row[0] =~ s/-/ /g;
  $version="MySQL $row[0]";
}
$sth->finish;

print "$version, ENGINE: $opt_db_engine \n";

if ($opt_buffering ne '')
{
  print "Set innodb_change_buffering=$opt_buffering\n\n";
  $dbh->do("set GLOBAL innodb_change_buffering=$opt_buffering") or die $DBI::errstr;
}
    
$sth = $dbh->prepare('select @@innodb_change_buffering') or die $DBI::errstr;
if ($sth->execute && (@row = $sth->fetchrow_array))
{
  $buffering=$row[0];
}
$sth->finish;
print "BUFFERING: $buffering\n\n";
      
####
#### Create needed table
####

print "Creating table bench1\n";
$dbh->do("drop table if exists bench1 ") or die $DBI::errstr;
$dbh->do("create table bench1 (id int NOT NULL, 
                               id2 int NOT NULL,
                               id3 int NOT NULL,
                               dummy1 char(30),
                               primary key (id,id2),
                               index ix_id3 (id3)) engine=$opt_db_engine") or die $DBI::errstr;

$query="insert into bench1 (id,id2,id3,dummy1) values ";

print "Inserting $opt_loop_count rows in order\n";

$loop_time=new Benchmark;
for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr;
}
  
$end_time=new Benchmark;
print "Time for insert_in_order (" . ($opt_loop_count) . "): " .
 timestr(timediff($end_time, $loop_time),"all") . "\n";

#print_stat() if ($db_stat);

$loop_time=new Benchmark;

$count=0;
$step=int($opt_loop_count/500+1);
#print "STEP=$step\n";
for ($i= 0 ; $i < $opt_loop_count ; $i+= $step)
{
  $count++;
  $sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 <= $i") or die $DBI::errstr;
}

#$dbh->do("update bench1 set id3=0-id3 where id3 >= 0 and id3 <= $opt_loop_count") or die $DBI::errstr;

$end_time=new Benchmark;
print "Time for update_of_key_big (" . ($opt_loop_count) . "):  " .
   timestr(timediff($end_time, $loop_time),"all") . "\n";
    
print_stat() if ($db_stat);

$dbh->disconnect();

sub print_stat
{
  $sth=$dbh->prepare("show status like 'handler%'");
  $sth->execute();
  while ((@arr=$sth->fetchrow_array))
  {
    print join(",",@arr),"\n";
  }
 $sth->finish;
}