#!/usr/bin/perl
# This test is for testing how long it takes to create tables,
# make a count(*) from them and finally drop the tables. These
# commands will be done in different ways in this test.
# Using option --fast will drop all the tables in the end
# of this test with one command instead of making own
# 'drop' command for each and every table.
# By changing the variable '$table_amount' value you can make
# this test a lot harder/easier for your computer to drive.
# Note that when using value bigger than 64 for this variable
# will do 'drop table'-command  in totally different way because of that
# how MySQL handles these commands.

$opt_loop_count=1000; # Change this to make test harder/easier
# This is the default value for the amount of tables used in this test.
##################### 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","loop-count=i","skip-create","skip-in","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted";
$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_skip_create=$opt_skip_in=undef; # Ignore warnings from these

print "Testing the speed of createing and droping tables\n";
print "All tests are done $opt_loop_count times\n\n";

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

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

#### Here we'll make a loop $opt_loop_count times, which includes 1 create table,
#### 1 count * from table and 1 drop table. Then we'll check how much
#### time did it take.

if ($opt_force) # If tables used in this test exist, drop 'em
{
  print("Okay..Let's make sure that our tables don't exist yet.\n");
  $Mysql::QUIET = 1;
  for ($i=1 ; $i<=$opt_loop_count ; $i++)
  {
    $dbh->Query("drop table bench_$i");
  }
  $Mysql::QUIET = 0;
}   

print "Testing create of tables\n";

$loop_time=new Benchmark;

for ($i=1 ; $i <= $opt_loop_count ; $i++)
{
  $dbh->Query("create table bench_$i (i int,d double,f float,s char(10),v varchar(100), primary key (i))")
    or die "$Mysql::db_errstr\nTry using --force to avoid this error.\n";
}

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

#### Here comes $opt_loop_count couples of cont(*) to the tables.
#### We'll check how long it will take...
####

print "Testing count(*)\n";

$loop_time=new Benchmark;

for ($i=1 ; $i <= $opt_loop_count ; $i++)
{
  $dbh->Query("select count(*) from bench_$i")
    or die $Mysql::db_errstr;
}

$end_time=new Benchmark;
print "Time to select count(*): " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

####
#### Now we are going to drop $opt_loop_count tables;
####

print "Testing drop\n";

$loop_time=new Benchmark;


if (!$opt_fast)
{
  for ($i=1 ; $i <= $opt_loop_count ; $i++)
  {
    $dbh->Query("drop table bench_$i")
      or die $Mysql::db_errstr;
  }
}
else
{
  $query="drop table bench_1";
  for ($i=2 ; $i <= $opt_loop_count ; $i++)
  {
    $query.=",bench_$i";
  }
  $dbh->Query($query) or die $Mysql::db_errstr;
}

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

#### We'll do first one 'create table' and then we'll drop it
#### away immediately. This loop shall be executed $opt_loop_count
#### times.

print "Testing create+drop\n";

$loop_time=new Benchmark;

for ($i=1 ; $i <= $opt_loop_count ; $i++)
{
  $dbh->Query("create table bench_$i (i int,d double,f float,s char(10),v varchar(100), primary key (i))")
    or die $Mysql::db_errstr;
  $dbh->Query("drop table bench_$i")
}

$end_time=new Benchmark;
print "Time to create&drop tables: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

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

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

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

exit 0;

