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

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

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

$opt_rows=50000;
$opt_subselect_exists=$opt_subselect_in=$opt_select_join_in=$opt_select_in=$opt_create='';
$opt_inner_table_size=10;

$opt_select='';
$opt_mrr_mode="";
$opt_db_stat=0;

GetOptions("subselect_exists","subselect_in","select_join_in","inner_table_size=s","select",
           "select_in","db-socket=s","db-engine=s","create","mrr_mode=s","db-stat");
           

if ($opt_create eq '' && $opt_select_join_in eq '' && $opt_select_in eq '' &&
    $opt_subselect_in eq '' && $opt_select 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 --create --select
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_create)
{

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

$range_loop_count=100;

$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_mrr_mode)
{
  print "SET mrr_mode=$opt_mrr_mode\n";
  $dbh->do("set optimizer_use_mrr=\'$opt_mrr_mode\'") or print "Error:  $DBI::errstr";
}

$sth=$dbh->prepare('select @@optimizer_use_mrr') or print "Error: $DBI::errstr";
$n=$sth->execute or print "Error: $DBI::errstr";
print "optimizer_use_mrr=",$sth->fetchrow,"\n";
$sth->finish();


if ($opt_select)
{

#todo: check following queries
#
#    "select * from bench1 where !id!>=" . ($total_rows-1) ." or !id!<1",
#    2,
#    "select * from bench1 where (!id!>0 and !id!<2) or !id!>=" . ($total_rows-1),
#    2,
#    "select * from bench1 where (!id!>0 and !id!<2) or (!id!>= " . ($opt_loop_count/2) . " and !id! <= " . ($opt_loop_count/2+2) . ") or !id! = " . ($opt_loop_count/2-1),
#    5,


@Q=("select * from bench1 where !id!=3 or !id!=2 or !id!=1 or !id!=4 or !id!=16 or !id!=10",
    6,
    "select * from bench1 where !id!>=1 and !id!<=2",
    2,
    "select * from bench1 where (!id!>=1 and !id!<=2) or (!id!>=1 and !id!<=2)",
    2,
    "select * from bench1 where !id!>=1 and !id!<=10 and !id!<=5",
    5,
    "select * from bench1 where (!id!>=5 and !id!<=10) or (!id!>=1 and !id!<=4)",
    10,
    "select * from bench1 where (!id!=1 or !id!=2) and (!id!=3 or !id!=4)",
    0,
    "select * from bench1 where (!id!=1 or !id!=2) and (!id!=2 or !id!=3)",
    1,
    "select * from bench1 where (!id!=1 or !id!=5 or !id!=20 or !id!=40) and (!id!=1 or !id!>=20 or !id!=4)",
    3,
    "select * from bench1 where ((!id!=1 or !id!=3) or (!id!>1 and !id!<3)) and !id!<=2",
    2,
    "select * from bench1 where (!id! >= 0 and !id! < 4) or (!id! >=4 and !id! < 6)",
    6,
    "select * from bench1 where !id! <= -1 or (!id! >= 0 and !id! <= 5) or (!id! >=4 and !id! < 6) or (!id! >=6 and !id! <=7) or (!id!>7 and !id! <= 8)",
    9,
    "select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>=0 and !id! <=10)",
    11,
    "select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>2 and !id! <=10)",
    10,
    "select * from bench1 where (!id!>1 or !id! <1) and !id!<=2",
    2,
    "select * from bench1 where !id! <= 2 and (!id!>1 or !id! <=1)",
    3,
    "select * from bench1 where (!id!>=1 or !id! <1) and !id!<=2",
    3,
    "select * from bench1 where (!id!>=1 or !id! <=2) and !id!<=2",
    3
    );

print "\nTest of compares with simple ranges\n";
check_select_range("id","select_range_prefix");
check_select_range("id3","select_range_key2");
                    
}


if($opt_select_in)
{
   time_fetch_all_rows("Testing SELECT ... WHERE id in ($opt_inner_table_size values)",
                    "select_in", $query, $dbh,
                    $range_loop_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, $range_loop_count*10);
}

if($opt_subselect_in)
{
    time_fetch_all_rows(undef,'subselect_in',
                        "SELECT * FROM bench1 WHERE id IN (SELECT id FROM bench2)",
                        $dbh, $range_loop_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, $range_loop_count);
}

$dbh->disconnect();

sub print_stat
{
  my ($txt)=@_;
  my @arr=();
  
  print "$txt\n" if ($txt);
  $sth=$dbh->prepare("show global status like 'handler_%'");
  $sth->execute();
  while ((@arr=$sth->fetchrow_array))
  {
    if ($arr[0] eq 'Handler_read_next')
    {
      if ($handler_read_next)
      {
        $handler_read_next=$arr[1]-$handler_read_next;
        print "Handler_read_next: $handler_read_next\n";
      }
      else
      {
        $handler_read_next=$arr[1];
      }
    }
    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 "\nQUERY:",$query,"\n";
  $handler_read_next=0;
  print_stat('init values:') if ($opt_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_stat('end values:') if ($opt_db_stat);

  print "Time for $result_text ($test_count:$rows): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}

sub check_select_range
{
  my ($column,$check)= @_;
  my ($loop_time,$end_time,$i,$tmp_var,$tmp,$query,$rows,$estimated);

  $found=$count=0;
  $count+=$#Q+1;
  for ($i=0 ; $i < $#Q ; $i+=2)
  {
    $query=$Q[$i];
    $rows=$Q[$i+1];
    $query =~ s/!id!/$column/g;
    time_fetch_all_rows(undef,$check,$query,$dbh,$range_loop_count);
  }
}