use strict;
use Time::HiRes;

#my $dbh = DBI->connect('dbi:mysql:host=127.0.0.1:port=10440:database=test','root') or die "Can't connect to server";
#my $dbh = DBI->connect('dbi:mysql:host=ndbamd-5.mysql.com:port=10440:database=test','root');
#my $dbh = DBI->connect('dbi:mysql:host=127.0.0.1:port=13000:database=test','root');
#my $dbh = DBI->connect('dbi:mysql:host=127.0.0.1:port=10020:database=test','root');

my $mysql_client = "../client/mysql -u root -S var/tmp/mysqld.1.sock test";

my @partitioning = ('NO','YES');
my @engines = reverse('Memory','MyISAM','Innodb');
#my @engines = ('Memory','MyISAM');
#my @engines = ('InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB','InnoDB');
my @parts = reverse(1,2,4,8,16,32,64,128,256,512,1024);
#my @parts = reverse(1,4,16,64,256,512,1024);
my @keys = ('BOTH','NONE','PK','KEY');
#my @keys = ('NONE','PK','KEY','BOTH');
#my @keys = ('BOTH');
#my @partitioning = ('YES');
#my @partitioning = ('NO');
#my @engines = ('Merge');
#my @engines = ('MyISAM');
#my @parts = (1024);
my $create = 1;
my $insert_init = 1;
my $insert = 1;
my $truncate = 1;
my $sleep_after_truncate = 1;
my $load_data = 1;
my $update = 1;
my $select_exact = 2;
my $select_no_pruning = 2;
my $drop_table = 1;
my $sleep_before = 1;

my @num_parts;

#$dbh->do("SET max_open_partition_files=1024");
#my $qh = $dbh->prepare("show variables like 'max_open_partition_files'");
#$qh->execute();
#while (my @row = $qh->fetchrow_array)
#{
#  print "@row\n";
#}
#$dbh->do("SET max_heap_table_size=200000000") or die "Failed to set variable";
system("echo 'SET GLOBAL max_heap_table_size=200000000;' | $mysql_client"); # or die "Failed to set variable";
#$dbh->do("delimiter //") or die "Failed to change delimiter";
#$dbh->do("drop procedure if exists insert_init") or die "Failed to drop insert_init";
my $sp_script =
"drop procedure if exists insert_init;
drop procedure if exists insert_values;
drop procedure if exists select_exact;
drop procedure if exists select_no_pruning;
delimiter //
create procedure insert_init(IN num_vals INT)
BEGIN
  DECLARE curr_val INT;
  SET curr_val = 0;
  WHILE (curr_val < num_vals) DO
    INSERT INTO t VALUES (curr_val, num_vals - curr_val);
    SET curr_val = curr_val + 1;
  END WHILE;
END//
create procedure insert_values(IN start_value INT, IN end_value INT)
BEGIN
  WHILE (start_value < end_value) DO
    INSERT INTO t VALUES (start_value, end_value - start_value);
    SET start_value = start_value + 1;
  END WHILE;
END//
create procedure select_exact(IN num_vals INT)
BEGIN
  DECLARE a_dummy INT;
  DECLARE b_dummy INT;
  WHILE (num_vals > 0) DO
    SELECT f1, b INTO a_dummy, b_dummy FROM t WHERE f1 = num_vals;
    SET num_vals = num_vals - 1;
  END WHILE;
END//
create procedure select_no_pruning(IN num_vals INT)
BEGIN
  DECLARE a_dummy INT;
  DECLARE b_dummy INT;
  WHILE (num_vals > 0) DO
    SELECT f1, b INTO a_dummy, b_dummy FROM t WHERE b = num_vals LIMIT 1;
    SET num_vals = num_vals - 1;
  END WHILE;
END//
delimiter ;";
system("echo '$sp_script' | $mysql_client"); # or die "Failed to create procedure select_no_pruning";
print "Engine,Partitions,PK,KEY,Operation,Time\n";
foreach my $have_key (@keys) {
  my $pk = 0;
  my $key = 0;
  if ($have_key eq 'PK' or $have_key eq 'BOTH')
  {
    $pk = 1;
  }
  if ($have_key eq 'KEY' or $have_key eq 'BOTH')
  {
    $key = 1;
  }
  #print "key: $have_key\n";
  foreach my $engine (@engines) {
    foreach my $partitioning (@partitioning) {
      if ($partitioning eq 'YES')
      {
        @num_parts = @parts;
      }
      else
      {
        @num_parts = (0);
      }
      foreach my $partitions (@num_parts) {
        if ($create)
        {
          my $create_sql = "";
          my $merge_union = "";
          if ($engine eq "Merge")
          {
            foreach my $m_num (1..$partitions)
            {
              if ($merge_union eq "")
              {
                $merge_union .= "UNION (m_${m_num}";
              }
              else
              {
                $merge_union .= ",m_${m_num}";
              }
              $create_sql .= "CREATE TABLE m_${m_num} (f1 integer";
              if ($pk)
              {
                $create_sql .= " PRIMARY KEY";
              }
              $create_sql .= ", b int";
              if ($key)
              {
                $create_sql .= ", KEY(b)";
              }
              $create_sql .= ") ENGINE=MyISAM;\n";
            }
            $merge_union .= ")";
          }
          $create_sql .= "CREATE TABLE t (f1 integer";
          if ($pk)
          {
            $create_sql .= " PRIMARY KEY";
          }
          $create_sql .= ", b int";
          if ($key)
          {
            $create_sql .= ", KEY(b)";
          }
          $create_sql .= ") ENGINE=$engine";
          $create_sql .= " PARTITION BY HASH(f1) PARTITIONS $partitions " if $partitions;

          if ($merge_union ne "")
          {
            $create_sql .= $merge_union;
          }
          system("echo 'DROP TABLE IF EXISTS t' | $mysql_client"); # or die "Failed to create table t";
          my $start_create = Time::HiRes::time();
          system("echo '$create_sql' | $mysql_client"); # or die "Failed to create table t";
          my $end_create = Time::HiRes::time();
          my $diff_create = $end_create - $start_create;
          #print "CREATE Engine: $engine, partitions $partitions, time $diff_create.\n";
          print "$engine,$partitions,$pk,$key,CREATE,$diff_create\n";
        }

        if ($insert_init)
        {
          sleep $sleep_before if ($sleep_before);
          my $start_insert = Time::HiRes::time();
          #foreach my $i (0..1023) {
          #  $dbh->do("INSERT INTO t VALUES ($i,1023-$i)") or die "Failed to insert into table ";
          #}
          system("echo 'call insert_init(1024);' | $mysql_client"); # or die "Failed with insert_init(1024)";
          my $end_insert = Time::HiRes::time();
          my $diff_insert = $end_insert - $start_insert;
          #print "INIT INSERT Engine: $engine, partitions $partitions, time $diff_insert.\n";
          print "$engine,$partitions,$pk,$key,INSERT INIT,$diff_insert\n";
        }

        if ($truncate)
        {
          sleep $sleep_before if ($sleep_before);
          my $start_insert = Time::HiRes::time();
          system("echo 'TRUNCATE TABLE t' | $mysql_client"); # or die "Failed to load into table t";
          my $end_insert = Time::HiRes::time();
          my $diff_insert = $end_insert - $start_insert;
          #print "INSERT Engine: $engine, partitions $partitions, time $diff_insert.\n";
          print "$engine,$partitions,$pk,$key,TRUNCATE,$diff_insert\n";
          sleep $sleep_after_truncate if ($sleep_after_truncate);
        }

        if ($load_data)
        {
          sleep $sleep_before if ($sleep_before);
          my $start_insert = Time::HiRes::time();
          system("echo \"set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0;LOAD DATA INFILE 't4M.txt' INTO TABLE t\" | $mysql_client"); # or die "Failed to load into table t";
          my $end_insert = Time::HiRes::time();
          my $diff_insert = $end_insert - $start_insert;
#print "INSERT Engine: $engine, partitions $partitions, time $diff_insert.\n";
          print "$engine,$partitions,$pk,$key,LOAD,$diff_insert\n";
        }

        if ($update)
        {
          sleep $sleep_before if ($sleep_before);
          my $start_insert = Time::HiRes::time();
          system("echo 'UPDATE t set f1 = f1 + 6000000 where f1 between 1 and 11024' | $mysql_client"); # or die "Failed to load into table t";
          my $end_insert = Time::HiRes::time();
          my $diff_insert = $end_insert - $start_insert;
#print "INSERT Engine: $engine, partitions $partitions, time $diff_insert.\n";
          print "$engine,$partitions,$pk,$key,UPDATE,$diff_insert\n";
        }

        if ($insert)
        {
          sleep $sleep_before if ($sleep_before);
          my $start_insert = Time::HiRes::time();
          #foreach my $i (1024..11023) {
            #$dbh->do("INSERT INTO t VALUES ($i, 12047-$i)") or die "Failed to insert into table t";
          #}
          system("echo 'call insert_values(4000000,4050000)' | $mysql_client"); # or die "Failed with insert_values(1024,100000)";
          my $end_insert = Time::HiRes::time();
          my $diff_insert = $end_insert - $start_insert;
#print "INSERT Engine: $engine, partitions $partitions, time $diff_insert.\n";
          print "$engine,$partitions,$pk,$key,INSERT,$diff_insert\n";
        }

        sleep $sleep_before if ($sleep_before && $select_exact);
        my $loop_count = 0;
        while ($loop_count < $select_exact)
        {
          my $max_id = 50000;
          $max_id = 5 if (!$pk);
          my $start_insert = Time::HiRes::time();
          system("echo 'call select_exact($max_id)' | $mysql_client"); # or die "Failed with select_exact($max_id)";
          my $end_insert = Time::HiRes::time();
          my $diff_insert = $end_insert - $start_insert;
          print "$engine,$partitions,$pk,$key,SELECT EXACT,$diff_insert\n";
          $loop_count++;
        }

        sleep $sleep_before if ($sleep_before && $select_no_pruning);
        $loop_count = 0;
        while ($loop_count < $select_no_pruning)
        {
          if ($partitions <= 6400)
          {
            my $max_id = 5000;
            $max_id = 5 if (!$key);
            my $start_insert = Time::HiRes::time();
            system("echo 'call select_no_pruning($max_id)' | $mysql_client"); # or die "Failed with select_no_pruning($max_id)";
            my $end_insert = Time::HiRes::time();
            my $diff_insert = $end_insert - $start_insert;
            print "$engine,$partitions,$pk,$key,SELECT NO PRUNING,$diff_insert\n";
          }
          $loop_count++;
        }

        if ($drop_table)
        {
          sleep $sleep_before if ($sleep_before);
          my $start_insert = Time::HiRes::time();
          system("echo 'DROP TABLE IF EXISTS t' | $mysql_client"); # or die "Failed to create table t";
          my $end_insert = Time::HiRes::time();
          my $diff_insert = $end_insert - $start_insert;
          print "$engine,$partitions,$pk,$key,DROP,$diff_insert\n";
        }
      }
    }
  }
}
#my $qh = $dbh->prepare("show variables like 'max_open_partition_files'");
#$qh->execute();
#while (my @row = $qh->fetchrow_array)
#{
#  print "@row\n";
#}