#!/usr/bin/perl -w
#---------------------------------------------------------------------
#/* Copyright (C) 2000-2006 MySQL AB
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.

#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.

#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
# Version 1.1
#------------------------------------------------------------------------
# Purpose: This application test the performance of creating, dropping 
#          and loading simple and complex tables. 
#------------------------------------------------------------------------
#
#-----------------------------------------------
#             Perl Includes 
#-----------------------------------------------
use DBI;
use Getopt::Long;
use Time::Local;
use Time::localtime;
use IO::File;
#-----------------------------------------------
#   Globals Vars  
#-----------------------------------------------
our $loop_count = 6;
our $t1_count = 10000;
our $t2_count = 10000;
our $our_host='localhost'; 
our $our_port='3306';
our $our_user='root';
our $our_pass='';
our $our_database='perf';
our $dbhM='';
our $our_engine_type='NDB';
our $our_lfg_name='lg1';
our $our_ts_name='ts1'; 
our $our_diskData=0;
our $our_mixData=0;
our $our_num_undo=1;
our $our_skipLFG=0;
our $our_num_data=2;
our $our_cfiles=0;
our $our_sockPath='/tmp/mysql.sock';
our $our_useSock=0;
our $sth_t1_drop='';
our $sth_t2_drop='';
our $sth_t1_create='';
our $sth_t1_create_dd='';
our $sth_t2_create='';
our $sth_t2_create_dd='';
our $blob_data='';
our $blob_file='./ndb_cpcd';
#-------------------------------------------------
#   Sub Pototypes 
#-------------------------------------------------

sub main ();
sub CollectCommandPromptInfo ();
sub ConnectToDatabases ();
sub DisconnectFromDatabase ();
sub CreateDB ();
sub Create_Tables ();
sub Load_t1 (); 
sub Load_t2 ();
sub ShowVars ();
sub Usage ();
sub Prep_Statements ();
sub Simple_Table_Ops ();
sub Simple_Table_Load_Ops ();
sub Complex_Table_Ops ();
sub Complex_Table_Load_Ops ();

#--------------------------------------------------
#  Program Main 
#--------------------------------------------------

main();

sub main ()
{
  CollectCommandPromptInfo();
  ConnectToDatabases();
  CreateDB();
  Prep_Statements();
  #Simple_Table_Ops();
  #Simple_Table_Load_Ops();
  #Complex_Table_Ops();
  Complex_Table_Load_Ops();
  DisconnectFromDatabase;
}

#--------------------------------
# Usage
#--------------------------------
sub Usage ()
{
 ShowVars();
 die "\n\nProgram: Load perf tables;
       usage: load_tpcb.pl
         -he [--help]            :print usage

         -ho [--host=string]     :host name     (default localhost)

         -u  [--user=string]     :user's name   (default root)

         -po [--port=int]        :port          (default 3306)

         -sock [--socket]        :Connect using socket (default false)

         -sp [--spath=string]    :socket path and file name 
                                  (default /tmp/mysql.sock)

         -pa [--pass=string]     :password | Note: leave -pa empty or do not
                                             include for blank password
                                             (default null/BLANK)
         
         -e  [--engine=string]   :engine_type | See Below  
 
         -d  [--database=string] :database name (default perf)

         -lf  [--lfgname}         :Log File Group Name. (default lg1)

         -numL [--numLFGUndo]    :Number of LFG Undo Files. (default 1)

         -sk  [--skipLFGcreate]   :Skip creating the LFG. Use if the LFG
                                  already exists. (default false)
         
         -t  [--tsName]          :Table Space Name.(default ts1)

         -numT [--numTSData]     :Number of Table Space Data Files. (default 2).

         Notes: engine == ndb, ndbdd(Disk Data), ndbmix(mix of mem and DD),\n
                          ndbcluster, InnoDB, MyISAM\n";
}

#--------------------------------
# Show command line values 
#--------------------------------

sub ShowVars ()
{
 ########## Show the user command line values for debugging #########
  print "*********************************************\n";
  print "************ Command-line values ************\n";
  print "*********************************************\n";
  print "Host ..................... $our_host\n";
  print "User ..................... $our_user\n";
  print "Password ................. $our_pass\n" if ($our_pass ne '');
  print "Password ................. BLANK\n" if ($our_pass eq '');
  print "Port ..................... $our_port\n";
  print "Use Socket To Connect .... TRUE\n" if ($our_useSock);
  print "Use Socket To Connect .... FALSE\n" if (!$our_useSock);
  print "Socket Path .............. $our_sockPath\n" if ($our_useSock);
  print "Database ................. $our_database\n";
  print "Engine Type .............. $our_engine_type\n";
  print "Use Disk Data ............ TRUE\n" if ($diskData || $mixData);
  print "Use Disk Data ............ FALSE\n" if (!$diskData && !$mixData);
  print "LFG Name ................. $our_lfg_name\n" if ($diskData || 
                                                         $mixData);
  print "Create Log File Group .... TRUE\n" if ($diskData ||
                                                $mixData &&
                                                !$our_skipLFG);
  print "Create Log File Group .... FALSE\n" if ($diskData ||
                                                $mixData &&
                                                $our_skipLFG);
  print "Number LFG Undo File(s)... $our_num_undo\n" if ($diskData ||
                                                         $mixData);
  print "Table Space Name ......... $our_ts_name\n" if ($diskData ||
                                                         $mixData);
  print "Number TS Data Files(s)... $our_num_data\n" if ($diskData ||
                                                         $mixData);
}

#--------------------------------
#  Collect Command Prompt Info 
#--------------------------------
sub CollectCommandPromptInfo ()
{

  if (!GetOptions("help"=>\$help,
                  "host:s"=>\$our_host,
                  "user:s"=>\$our_user,
                  "port:i"=>\$our_port,
                  "socket"=>\$our_useSock,
                  "spath:s"=>\$our_sockPath,
                  "pass:s"=>\$our_pass,
                  "engine:s"=>\$our_engine_type,
                  "database:s"=>\$our_database,
                  "lfgname:s"=>\$our_lfg_name,
                  "numLFGUndo:i"=>\$our_num_undo,
                  "skipLFGcreate"=>\$our_skipLFG,
                  "tsName:s"=>\$our_ts_name,
                  "numTSData:i"=>\$our_num_data))
  {
    Usage();
  }

  if ($help){Usage();}


  if ($our_useSock){
    $ENV{MYSQL_UNIX_PORT} = $our_sockPath;
  }

  if ($our_engine_type ne 'ndb' && $our_engine_type ne 'ndbcluster'
      && $our_engine_type ne 'InnoDB' && $our_engine_type ne 'MyISAM' 
      && $our_engine_type ne 'innodb' && $our_engine_type ne 'myisam' 
      && $our_engine_type ne 'NDBDD' && $our_engine_type ne 'NDBMIX' 
      && $our_engine_type ne 'ndbdd' && $our_engine_type ne 'ndbmix'
      && $our_engine_type ne 'NDB' && $our_engine_type ne 'NDBCLUSTER'){
    die Usage();
  } 
  if ($our_engine_type eq 'ndbdd'){$our_engine_type = "ndb"; $diskData = 1;}
  if ($our_engine_type eq 'ndbmix'){$our_engine_type = "ndb"; $mixData = 1;}
  if ($our_engine_type eq 'NDBDD'){$our_engine_type = "ndb"; $diskData = 1;}
  if ($our_engine_type eq 'NDBMIX'){$our_engine_type = "ndb"; $mixData = 1;}

  ShowVars();
}

#---------------------------------------------
#  Connect to database & Read from file
#---------------------------------------------
sub ConnectToDatabases ()
{
  if (!$our_useSock){
    $dbhM = DBI->connect("dbi:mysql:database=mysql;host=$our_host;port=$our_port", 
                       "$our_user", "$our_pass")
     or die "Can't connect to MySQL process! Error: $DBI::errstr\n";
  }
  else{
    $dbhM = DBI->connect("DBI:mysql:database=mysql;host=$our_host", 
                        "$our_user", "$our_pass", {'RaiseError' => 1})
      or die "Can't connect to MySQL process! Error: $DBI::errstr\n";
  } 
  print " Connected to MySQLD.\n";
  open(my $fh, $blob_file ) or die $!;
  read( $fh, $blob_data, -s $fh );
  close($fh);
}

#-----------------------------------------------
#  Disconnect from database 
#-----------------------------------------------
sub DisconnectFromDatabase ()
{
  $sth_t1_drop->finish();
  $sth_t2_drop->finish();
  $sth_t1_create->finish();
  $sth_t1_create_dd->finish();
  $sth_t2_create->finish();
  $sth_t1_create_dd->finish();
  $dbhM->disconnect
    or warn " Disconnection failed: $DBI::errstr\n";
  print "Disconnected from MySQLD.\n";
}

#-------------------------------------------------
#  Create database 
#-------------------------------------------------
sub CreateDB ()
{
  print "Creating database!\n";

  $sth = $dbhM->prepare("DROP DATABASE IF EXISTS $our_database;")
	or die "Prepare error: ", $dbhM->errstr;
  $sth->execute();#/Don't care if returns success. No "die" needed
  $sth->finish();

  $sth = $dbhM->prepare("CREATE DATABASE $our_database;")
    or die "Prepare error: ", $dbhM->errstr;
  $sth->execute()
    or die "Create DB Error: ", $sth->errstr;
  $sth->finish();

  $sth = $dbhM->prepare("USE $our_database;")
    or die "Prepare error: ", $dbhM->errstr;
  $sth->execute()
    or die "USE DB Error: ", $sth->errstr;
  $sth->finish();

  print "here 1\n";
  if($diskData || $mixData && !$our_skipLFG){
    print "Creating LFG $our_lfg_name....\n";
    $sth = $dbhM->prepare("CREATE LOGFILE GROUP $our_lfg_name
                           ADD UNDOFILE './$our_lfg_name/undofile.dat'
                           INITIAL_SIZE 250M
                           UNDO_BUFFER_SIZE = 4M
                           ENGINE=NDB;")
      or die "Prepare CREATE LOGFILE GROUP error: ", $dbhM->errstr;
    $sth->execute()
      or die "CREATE LOGFILE GROUP error: ", $sth->errstr;
    $sth->finish();
   
  print "here 2\n";
    if($our_num_undo > 1){
      $undo_count = $our_num_undo - 1;
      print "Adding additional undo files, please stand by....\n";
      while ( $undo_count > 0){
        $file="undofile".$undo_count.".dat";
        print "Adding $file..\n";
        $sth = $dbhM->prepare("ALTER LOGFILE GROUP $our_lfg_name
                               ADD UNDOFILE './$our_lfg_name/$file'
                               INITIAL_SIZE 250M
                               ENGINE=NDB;")
          or die "Prepare ALTER LOGFILE GROUP error: ", $dbhM->errstr;
        $sth->execute()
          or die "ALTER LOGFILE GROUP error: ", $sth->errstr;
        $sth->finish();
        $undo_count--;
      }
    }
  }
  print "here 3\n";
   
  if($diskData || $mixData){
    print "Creating TS $our_ts_name....\n";
    $sth = $dbhM->prepare("CREATE TABLESPACE $our_ts_name
                           ADD DATAFILE './$our_ts_name/datafile.dat'
                           USE LOGFILE GROUP $our_lfg_name
                           INITIAL_SIZE 500M
                           ENGINE=NDB;")
      or die "Prepare CREATE TABLESPACE error: ", $dbhM->errstr;
    $sth->execute()
      or die "CREATE TABLESPACE error: ", $sth->errstr;
    $sth->finish();
  
    if($our_num_data > 1){
      $data_count = $our_num_data - 1;
      while  ($data_count > 0){
        $file="datafile".$data_count.".dat";
        print "Add data file $file\n";
        $sth = $dbhM->prepare("ALTER TABLESPACE $our_ts_name
                               ADD DATAFILE './$our_ts_name/$file'
                               INITIAL_SIZE 500M
                               ENGINE=NDB;")
          or die "Prepare ALTER TABLE SPACE error: ", $dbhM->errstr;
        $sth->execute()
          or die "ALTER TABLE SPACE error: ", $sth->errstr;
        $sth->finish();
        $data_count--;
      }
    }
  }
  print "Database Created.\n";
}

#------------------------------------------------------
#  Prepare Statements 
#------------------------------------------------------
sub Prep_Statements ()
{

  $sth_t1_drop = $dbhM->prepare("DROP TABLE IF EXISTS t1;")
	or die "Prepare drop account error: ", $dbhM->errstr;

  $sth_t2_drop = $dbhM->prepare("DROP TABLE IF EXISTS t2;")
	or die "Prepare drop branch error: ", $dbhM->errstr;


  $sth_t1_create = $dbhM->prepare("CREATE TABLE t1 (c1 INT PRIMARY KEY)
                                   ENGINE=$our_engine_type;")
      or die "Prepare Create t1 Error: ", $dbhM->errstr;
    
  $sth_t2_create = $dbhM->prepare("CREATE TABLE t2 
                                   (c1 int Primary Key, 
                                    c2 decimal(8,2),
                                    c3 bit, c4 text, c5 BLOB,
                                    c6 TEXT, c7 double, c8 float)
                                    ENGINE=$our_engine_type;")
      or die "Prepare Create t2 Error: ", $dbhM->errstr;

  $sth_t1_create_dd = $dbhM->prepare("CREATE TABLE t1 (c1 INT PRIMARY KEY)
                                   STORAGE DISK ENGINE=$our_engine_type;")
      or die "Prepare Create t1 DD Error: ", $dbhM->errstr;
   
  $sth_t2_create_dd = $dbhM->prepare("CREATE TABLE t2 (c1 int Primary Key, 
                                      c2 decimal(8,2), c3 CHAR, c4 text, c5 BLOB,
                                      c6 VARCHAR(20), c7 double, c8 float)
                                      TABLESPACE $our_ts_name
                                      STORAGE DISK ENGINE=$our_engine_type;")
      or die "Prepare Create t2 DD Error: ", $dbhM->errstr;
}

#-----------------------------------------------
#  Load t1 
#-----------------------------------------------
sub Load_t1 ()
{
  print "Loading t1 table -- Please wait\n";
  $sth = $dbhM->prepare("INSERT into t1 VALUES(?);")
     or die "Prepare insert into t1 error: ", $dbhM->errstr;
  $i = 0;
  $startTime = time;
  while($i < $t1_count){
    $i++;
    $sth->bind_param(1, $i);
    $sth->execute()
      or die "insert into t1 Error: ", $sth->errstr;
  }
  $sth->finish();
  $end_time = time - $startTime;
  print "Time to load took $end_time seconds\n";
  $end_time = 0;
}

#-----------------------------------------------
#  Load t2
#-----------------------------------------------
sub Load_t2 ()
{
  print "Loading t2 table -- Please wait\n";
  $sth = $dbhM->prepare("INSERT into t2 values(?, ?, 
                                               ?, ?,
                                               ?, ?,
                                               ?, ? );")
    or die "Prepare insert into t2 error: ", $dbhM->errstr;
  $i = 0;
  $startTime = time;
  while($i < $t2_count){
    $i++;
    $sth->bind_param(1, $i);
    $sth->bind_param(2, 3.00);
    $sth->bind_param(3, "G");
    $sth->bind_param(4, "GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG
                         GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG
                         _G_G_G_G_G");
    $sth->bind_param(5, $blob_data);
    $sth->bind_param(6, "Good testing");
    $sth->bind_param(7, 88888888);
    $sth->bind_param(8, 32.0009);
    $sth->execute()
      or die "insert into t2 Error: ", $sth->errstr;
  }
  $sth->finish();
  $end_time = time - $startTime;
  print "Time to load t2 = $end_time seconds\n";
  $end_time = 0;
}

#-----------------------------------------------
#  Simple Table Operations
#-----------------------------------------------
sub Simple_Table_Ops ()
{
  $loop = 0;
  while ($loop < $loop_count){
    if (!$diskData){
      $loop++;
      $startTime = time;
      $sth_t1_create->execute();
      $end_time = time - $startTime;
      print "Time to create t1 = $end_time seconds\n";
      $end_time = 0;
      $startTime = time;
      $sth_t1_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t1 = $end_time seconds\n";
      $end_time = 0;
    }
    else{
      $loop++;
      $startTime = time;
      $sth_t1_create_dd->execute();
      $end_time = time - $startTime;
      print "Time to create t1 DD = $end_time seconds\n";
      $end_time = 0;
      $startTime = time;
      $sth_t1_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t1 DD = $end_time seconds\n";
      $end_time = 0;
    }
  }
}

#-----------------------------------------------
#  Simple Table Load Operations
#-----------------------------------------------
sub Simple_Table_Load_Ops ()
{
  $loop = 0;
  while ($loop < $loop_count){
    if (!$diskData){
      $loop++;
      $startTime = time;
      $sth_t1_create->execute();
      $end_time = time - $startTime;
      print "Time to create t1 = $end_time seconds\n";
      $end_time = 0;
      Load_t1();
      $startTime = time;
      $sth_t1_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t1 = $end_time seconds\n";
      $end_time = 0;
    }
    else{
      $loop++;
      $startTime = time;
      $sth_t1_create_dd->execute();
      $end_time = time - $startTime;
      print "Time to create t1 DD = $end_time seconds\n";
      $end_time = 0;
      Load_t1();
      $startTime = time;
      $sth_t1_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t1 DD = $end_time seconds\n";
      $end_time = 0;
    }
  }
}

#-----------------------------------------------
#  Complex Table Operations
#-----------------------------------------------
sub Complex_Table_Ops ()
{
  $loop = 0;
  while ($loop < $loop_count){
    if (!$diskData){
      $loop++;
      $startTime = time;
      $sth_t2_create->execute();
      $end_time = time - $startTime;
      print "Time to create t2 = $end_time seconds\n";
      $end_time = 0;
      $startTime = time;
      $sth_t2_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t2 = $end_time seconds\n";
      $end_time = 0;
    }
    else{
      $loop++;
      $startTime = time;
      $sth_t2_create_dd->execute();
      $end_time = time - $startTime;
      print "Time to create t2 DD = $end_time seconds\n";
      $end_time = 0;
      $startTime = time;
      $sth_t2_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t2 DD = $end_time seconds\n";
      $end_time = 0;
    }
  }
}

#-----------------------------------------------
#  Complex Table Load Operations
#-----------------------------------------------
sub Complex_Table_Load_Ops ()
{
  $loop = 0;
  while ($loop < $loop_count){
    if (!$diskData){
      $loop++;
      $startTime = time;
      $sth_t2_create->execute();
      $end_time = time - $startTime;
      print "Time to create t2 = $end_time seconds\n";
      $end_time = 0;
      Load_t2();
      $startTime = time;
      $sth_t2_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t2 = $end_time seconds\n";
      $end_time = 0;
    }
    else{
      $loop++;
      $startTime = time;
      $sth_t2_create_dd->execute();
      $end_time = time - $startTime;
      print "Time to create t2 DD = $end_time seconds\n";
      $end_time = 0;
      Load_t2();
      $startTime = time;
      $sth_t2_drop->execute();
      $end_time = time - $startTime;
      print "Time to drop t2 DD = $end_time seconds\n";
      $end_time = 0;
    }
  }
}
###################  End of script #############