#!/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="";
$opt_skip_create="";
$opt_loops=100;

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

$opt_loop_count=$opt_rows if ($opt_rows);
$subs_loop_count=$opt_loop_count / 10;
           
$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";
      
fill_tables_for_subselect() if (!$opt_skip_create);

            time_fetch_all_rows("Correlated",
                                "select_subq_sel_corr",
                                "select avg((select max(b) from bench5 where bench5.b=bench6.b)) from bench6",
                                $dbh, $opt_loops);
#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;
}

sub fill_tables_for_subselect
{
    $dbh->do("drop table bench6" . $server->{'drop_attr'});
    $dbh->do("drop table bench5" . $server->{'drop_attr'});

$dbh->do("create table bench5 (id int NOT NULL, b int , c char(3),
                               primary key (id), index bench6i1(b)) engine=$opt_db_engine") or die $DBI::errstr;

$dbh->do("create table bench6 (id int NOT NULL,
                               b int ,
                               c char(3),
                               primary key (id),
                               index bench6i(b)) engine=$opt_db_engine") or die $DBI::errstr;

    print "Fill tables for subqueries (bench5)\n";
    
    my $loop_time = new Benchmark;

    
    my $b5count=0;
    $sth = $dbh->prepare("insert into bench5 values(?,?,?)") or die $DBI::errstr;
    for ($i=0; $i<$subs_loop_count; $i++)
    {
	$sth->bind_param(1,$i,DBI::SQL_INTEGER);
	$sth->bind_param(2,$b5count,DBI::SQL_INTEGER);
	$sth->bind_param(3,"GPL",DBI::SQL_CHAR);
	$sth->execute;
	$b5count =  $i % 100;
    }
    $sth->finish;
    
    print "Fill tables for subqueries (bench6)\n";
    
    my $b6count=0;
    my $str;
    $sth = $dbh->prepare("insert into bench6 values(?,?,?)") or die $DBI::errstr;
    for ($i=0; $i<$subs_loop_count; $i++)
    {
	$sth->bind_param(1,$i,DBI::SQL_INTEGER);
	$sth->bind_param(2,$b6count,DBI::SQL_INTEGER);
	$str =  ( $i < ($subs_loop_count / 2) ) ? "GPL":"BSD"; 
	$sth->bind_param(3,$str,DBI::SQL_CHAR);
	$sth->execute;
	$b6count =  $i % 100;
    }
    $sth->finish;
    
    $end_time=new Benchmark;;
    print "Time for insert_subq (" . ($subs_loop_count*2) . "): " .
	timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}

sub time_fetch_all_rows
{
  my($test_text,$result_text,$query,$dbh,$test_count)=@_;
  my($i,$loop_time,$end_time,$rows);

#  print_stat if ($db_stat);

  print $test_text . "\n"   if (defined($test_text));
  $rows=0;
  $loop_time=new Benchmark;
  for ($i=1 ; $i <= $test_count ; $i++)
  {
    $sth=$dbh->prepare($query) or die $DBI::errstr;
    $sth->execute() or die $DBI::errstr;
    while ($sth->fetchrow_arrayref)
    {
      $rows++;
    }
    $sth->finish();
  }
  $end_time=new Benchmark;
  print "Time for $result_text (count=$test_count:rows=$rows): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";

#  print_stat if ($db_stat);
}