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

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

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

$opt_rows=50000;
$opt_insert_duplicates=$opt_update_big=$opt_update_with_key=$opt_update_with_key_prefix=$opt_skip_create="";
$opt_subselect_exists=$opt_subselect_in=$opt_select_join_in=$opt_select_in=$opt_skip_create='';
$opt_inner_table_size=100;

$opt_mode="";
$db_stat=1;
$opt_range_count=250;

GetOptions("subselect_exists","subselect_in","select_join_in","inner_table_size=s",
           "select_in","db-socket=s","db-engine=s","skip-create","mode=s","range-count=s","loop-count=s");
           
$random_loop_count=$opt_loop_count;

if ($opt_insert_duplicates eq '' && 
    $opt_select_join_in eq '' && $opt_select_in eq '' &&
    $opt_subselect_in eq '' &&
    $opt_subselect_exists eq '')
{
  print <<EOF;
Please specify at least one of the tests in the command line: 
--subselect_exists --subselect_in --select_join_in --select_in
EOF
exit;
}

$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_skip_create eq '')
{

####
#### Generating random keys
####

print "Generating random keys\n";
$random[$random_loop_count]=0;
for ($i=0 ; $i < $random_loop_count ; $i++)
{
  $random[$i]=$i;
}

my $tmpvar=1;
for ($i=0 ; $i < $random_loop_count ; $i++)
{
  $tmpvar^= ((($tmpvar + 63) + $i)*3 % $random_loop_count);
  $swap=$tmpvar % $random_loop_count;
  $tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp;
}

sub random {
  my $i = shift;
  $i += $opt_loop_count;
  return $random[$i % $random_loop_count]
           + floor($i/$random_loop_count)*$random_loop_count;
}


####
#### Create needed table
####

print "Creating table bench1\n";
$dbh->do("drop table if exists bench1 ") or die $DBI::errstr;
$dbh->do("drop table if exists bench2 ") 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 ";

$total_rows=$opt_loop_count*3;

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";

  $loop_time=new Benchmark;

  print "Inserting $opt_loop_count rows in reverse order\n";
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $sth = $dbh->do($query . "(" . ($total_rows-1-$i) . "," .
                    ($total_rows-1-$i) . "," .
                    ($total_rows-1-$i) . ",'BCDEFGHIJK')")
      or die $DBI::errstr;
  }

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

  $loop_time=new Benchmark;
  print "Inserting $opt_loop_count rows in random order\n";

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

}

$dbh->do("drop table if exists bench2 ") or die $DBI::errstr;

$dbh->do("create table bench2 (id int NOT NULL,
                               primary key (id))
                               engine=$opt_db_engine") or die $DBI::errstr;

# Fill join table to have the same id's as 'query'
for ($i=1 ; $i <= $opt_inner_table_size ; $i++)
{
  $dbh->do("insert into bench2 values($i)") or die $DBI::errstr;
}

print "Inner table size: $opt_inner_table_size\n";

$query="SELECT bench1.* FROM bench1 WHERE id IN (";
for ($i=1 ; $i <= $opt_inner_table_size ; $i++)
{
  $query.="$i,";
}
$query=substr($query,0,length($query)-1) . ")";

if($opt_select_in)
{
   time_fetch_all_rows("Testing SELECT ... WHERE id in ($opt_inner_table_size values)",
                    "select_in", $query, $dbh,
                    $opt_range_count*10);
}

if($opt_select_join_in)
{
  time_fetch_all_rows(undef, "select_join_in",
                    "SELECT bench1.* FROM bench2 left outer join bench1 on (bench1.id=bench2.id)",
                     $dbh, $opt_range_count*10);
}

if($opt_subselect_in)
{
    time_fetch_all_rows(undef,'subselect_in',
                        "SELECT * FROM bench1 WHERE id IN (SELECT id FROM bench2)",
                        $dbh, $opt_range_count);
}

if ($opt_subselect_exists)
{
  time_fetch_all_rows(undef,'subselect_exists',
                      "SELECT * FROM bench1 WHERE EXISTS (SELECT * FROM bench2 WHERE bench2.id=bench1.id)",
                      $dbh, $opt_range_count);
}

$dbh->disconnect();

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

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);
}