#!/usr/bin/perl
#
# Test of creating a simple table and inserting $record_count records in it,
# $opt_loop_count rows in order, $opt_loop_count rows in reverse order and
# $opt_loop_count rows in random order
#
$opt_loop_count=10000;

##################### Standard benchmark inits ##############################

use Mysql;
use Getopt::Long;
use Benchmark;

package main;

$opt_skip_create=$opt_skip_in=$opt_skip_delete=$opt_verbose=$opt_fast_insert=
  $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0;
$opt_host=""; $opt_db="test";

GetOptions("host=s","db=s","skip-create","skip-in","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted";
$opt_verbose=$opt_debug=$opt_force=undef; # Ignore warnings from these

print "Testing the speed of inserting data into 1 table and do some selects on it.\n";
print "The tests are done with a table that has $opt_loop_count rows.\n\n";

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

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

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

####
####  Connect and start timeing
####

$start_time=new Benchmark;
$dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;

####
#### Create needed tables
####

goto select_test if ($opt_skip_create);

print "Creating tables\n";
$Mysql::QUIET = 1;
$dbh->Query("drop table bench");
$Mysql::QUIET = 0;

$dbh->Query("create table bench (id int(6),primary key (id))")
  or die $Mysql::db_errstr;

if ($opt_lock_tables)
{
  $dbh->Query("LOCK TABLES bench WRITE") || die $Mysql::db_errstr;
}

####
#### Insert $opt_loop_count*3 records in order, in reverse order and random.
####

$loop_time=new Benchmark;

if ($opt_fast_insert)
{
  $query="insert into bench (id) values (";
}
else
{
  $query="insert into bench values (";
}

print "Inserting $opt_loop_count rows in order\n";
for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $dbh->query($query . $i . ")") or die $Mysql::db_errstr;
}

print "Inserting $opt_loop_count rows in reverse order\n";
for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $dbh->query($query . ($opt_loop_count*3-1-$i) . ")")
    or die $Mysql::db_errstr;
}

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

for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $dbh->query($query . $random[$i] . ")") or die $Mysql::db_errstr;
}

$end_time=new Benchmark;
print "Time to insert " . ($opt_loop_count*3) . " rows: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";


####
#### insert $opt_loop_count records with duplicate id
####

print "Testing insert of duplicates\n";
$loop_time=new Benchmark;
$Mysql::QUIET = 1;
for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
  $tmp=$tmpvar % ($opt_loop_count*3);
  if ($dbh->query($query . $tmp . ")"))
  {
    die "Didn't get an error when inserting duplicate record $tmp\n";
  }
}
$Mysql::QUIET = 0;

$end_time=new Benchmark;
print "Time for insert_duplicates of " . ($opt_loop_count*3) . " rows: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

####
#### Do some selects on the table
####

select_test:

print "Retrieving data from the table\n";
$loop_time=new Benchmark;

# It's really a small table, so we can try a select on everything
$sth=$dbh->query("select * from bench") or die $Mysql::db_errstr;
#check the select
$rowcnt=0;
$row[0]=0;
while (@row = $sth->FetchRow())
{
  $rowcnt++;
}
if ($rowcnt != $opt_loop_count*3 || $sth->numrows != $opt_loop_count*3)
{
  die "Got wrong number of rows when selecting hole table\n";
}
$sth=$dbh->query("select * from bench order by id") or die $Mysql::db_errstr;
$sth=$dbh->query("select * from bench order by id desc") or die $Mysql::db_errstr;

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

# Test select that is very popular when using ODBC

$loop_time=new Benchmark;
for ($rowcnt=0 ; $rowcnt <= 200 ; $rowcnt+=50)
{
  $query="select * from bench where ";
  $or_part= "id = 1000";

  for ($i=1 ; $i < $rowcnt ; $i++)
  {
    $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
    $tmp=$tmpvar % ($opt_loop_count*4);
    $or_part.=" or id=$tmp";
  }
  $sth=$dbh->query($query . $or_part) or die $Mysql::db_errstr;

  if (!$opt_skip_in)
  {
# Same query, but use 'IN' instead.
    $in_part=$or_part;
    $or_part=~ s/ = / IN \(/;
    $or_part=~ s/ or id=/,/g;
    $or_part.= ")";

    if (!($sth=$dbh->query($query . $in_part)))
    {
      print "Can't execute IN query. Continuing with --skip-in\n";
      $opt_skip_in=1;
    }
  }

# Do it a little harder by setting a extra range
  $sth=$dbh->query($query . "(" . $or_part . ") and id < 10") or die $Mysql::db_errstr;
}

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

# Do some sample selects on direct key

$loop_time=new Benchmark;
for ($i=0 ; $i < $opt_loop_count; $i++)
{
  $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
  $tmp=$tmpvar % ($opt_loop_count*3);
  $sth=$dbh->query("select * from bench where id=$tmp")
    or die $Mysql::db_errstr; 
}

# Do some sample selects on keys that isn't found
for ($i=0 ; $i < $opt_loop_count; $i++)
{
  $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
  $tmp=$tmpvar % ($opt_loop_count*3)+$opt_loop_count*3;
  $sth=$dbh->query("select * from bench where id=$tmp")
    or die $Mysql::db_errstr; 
  die "Found rows on impossible id: $tmp\n" if ($sth->num_rows);
}

$end_time=new Benchmark;
print "Time for select of " . ($opt_loop_count*2) . " rows: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

####
#### A lot of simple selects on ranges
####

@Q=("select * from bench where id=3 or id=2 or id=1 or id=4 or id=16 or id=10",
    6,
    "select * from bench where id>=" . ($opt_loop_count*3-1) ." or id<1",
    2,
    "select * from bench where id>=1 and id<=2",
    2,
    "select * from bench where id>=1 and id<=2 or id>=1 and id<=2",
    2,
    "select * from bench where id>=1 and id<=10 and id<=5",
    5,
    "select * from bench where id>0 and id<2 or id>=" . ($opt_loop_count*3-1),
    2,
    "select * from bench where id>0 and id<2 or id>=5000 and id <= 5002 or id =4999",
    5,
    "select * from bench where id>=5 and id<=10 or id>=1 and id<=4",
    10,
    "select * from bench where (id=1 or id=2) and (id=3 or id=4)",
    0,
    "select * from bench where (id=1 or id=2) and (id=2 or id=3)",
    1,
    "select * from bench where (id=1 or id=5 or id=20 or id=40) and (id=1 or id>=20 or id=4)",
    3,
    "select * from bench where ((id=1 or id=3) or (id>1 and id<3)) and id<=2",
    2,
    "select * from bench where (id >= 0 and id < 4) or (id >=4 and id < 6)",
    6,
    "select * from bench 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 bench where (id>=1 and id<=2 or id>=4 and id<=5) or (id>=0 and id <=10)",
    11,
    "select * from bench where (id>=1 and id<=2 or id>=4 and id<=5) or (id>2 and id <=10)",
    10,
    "select * from bench where (id>1 or id <1) and id<=2",
    2,
    "select * from bench where (id>1 or id <=1) and id<=2",
    3,
    "select * from bench where (id>=1 or id <1) and id<=2",
    3,
    "select * from bench where (id>=1 or id <=2) and id<=2",
    3
    );

$loop_time=new Benchmark;
for ($test=0 ; $test < 100 ; $test++)
{
  for ($i=0 ; $i < $#Q ; $i+=2)
  {
    my $query=$Q[$i];
    my $rows=$Q[$i+1];
    $sth=$dbh->query($query) or die $Mysql::db_errstr;
    if ($sth->num_rows != $rows)
    {
      die "'$query' returned wrong number of rows: " . $sth->num_rows . " instead of $rows\n";
    }
  }
}

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

####
#### Some group queries
####

$loop_time=new Benchmark;

$sth=$dbh->query($query="select count(*) from bench") or die $Mysql::db_errstr;
if ($sth->num_rows != 1 || (($sth->FetchRow())[0] != $opt_loop_count*3))
{
  die "'$query' returned wrong result\n";
}

$sth=$dbh->query($query="select count(*) from bench where id >= " .
		 ($opt_loop_count*2))
  or die $Mysql::db_errstr;
if ($sth->num_rows != 1 || (($sth->FetchRow())[0] != $opt_loop_count))
{
  die "'$query' returned wrong result\n";
}

$sth=$dbh->query($query="select count(id),sum(id),min(id),max(id),avg(id) from bench") or die $Mysql::db_errstr;
@row=$sth->FetchRow();
if ($sth->num_rows != 1 || $row[0] != $opt_loop_count*3 || $row[1] != (($opt_loop_count*3-1)/2*$opt_loop_count*3) || $row[2] != 0 || $row[3] != $opt_loop_count*3-1 ||
    $row[4] != ($opt_loop_count*3-1)/2)
{
  die "'$query' returned wrong result: @row\n";
}

$sth=$dbh->query($query="select mod(id,10) as last_digit,count(*) from bench group by last_digit") or die $Mysql::db_errstr;
die "'$query' returned wrong result\n" if ($sth->num_rows != 10);

$sth=$dbh->query($query="select id,id+1,id+2 from bench where id < 100 group by 1 desc,2,3") or die $Mysql::db_errstr;
die "'$query' returned wrong result\n" if ($sth->num_rows != 100);

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

####
#### Some updates on the table
####

$loop_time=new Benchmark;

print "Testing update\n";
$loop_time=new Benchmark;
$Mysql::QUIET = 1;
for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $opt_loop_count*3
  $dbh->query("update bench set id=-$tmp where id=$tmp") or die $Mysql::db_errstr;
}

$end_time=new Benchmark;
print "Time for update_key of $opt_loop_count keys: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

$loop_time=new Benchmark;

for ($i= 0 ; $i < $opt_loop_count/2 ; $i+=10)
{
  $sth=$dbh->query("update bench set id=-id where id >= 0 and id <= $i") or die $Mysql::db_errstr;
}

$sth=$dbh->query("update bench set id=-id where id >= 0 and id <= $opt_loop_count") or die $Mysql::db_errstr;

$sth=$dbh->query("update bench set id=-id where id >= $opt_loop_count and id <= ". ($opt_loop_count*2)) or die $Mysql::db_errstr;

# Check that everything except id=0 was updated
# In principle we shouldn't time this in the update loop..
#
$sth=$dbh->query($query="select * from bench where id>=0") or die $Mysql::db_errstr;
if ($sth->num_rows != 1)
{
  die "Update loop didn't update all records\n";
}

#restore id to 0 <= id < $opt_loop_count*3

$sth=$dbh->query($query="update bench set id=-id where id<=0") or die $Mysql::db_errstr;

$end_time=new Benchmark;

print "Time for update_key_big of " . ($opt_loop_count*5) . " keys: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

####
#### Do some deletes on the table
####

if (!$opt_skip_delete)
{
  print "Testing delete\n";
  $loop_time=new Benchmark;
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $opt_loop_count*3
    $dbh->query("delete from bench where id=$tmp") or die $Mysql::db_errstr;
  }

  $end_time=new Benchmark;
  print "Time for delete of $opt_loop_count rows: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

  $loop_time=new Benchmark;
  for ($i= 0 ; $i < $opt_loop_count/2 ; $i+=10)
  {
    $sth=$dbh->query("delete from bench where id >= 0 and id <= $i") or die $Mysql::db_errstr;
  }
  $sth=$dbh->query("delete from bench where id >= 0 and id <= $opt_loop_count") or die $Mysql::db_errstr;

  if ($opt_fast)
  {
    $sth=$dbh->query("delete from bench") or die $Mysql::db_errstr;
  }
  else
  {
    $sth=$dbh->query("delete from bench where id < " . ($opt_loop_count*3)) or die $Mysql::db_errstr;    
  }
  $end_time=new Benchmark;

  print "Time for delete_big of " . ($opt_loop_count*2) . " rows: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

  if ($opt_lock_tables)
  {
    $dbh->Query("UNLOCK TABLES ") || die $Mysql::db_errstr;
  }
  $sth=$dbh->query("drop table bench") or die $Mysql::db_errstr;
}

####
#### End of benchmark
####

$dbh=0;				# close connection
$end_time=new Benchmark;

print "Total time: " .
  timestr(timediff($end_time, $start_time),"noc") . "\n";

exit 0;
