#---------------------------------------------------------------------
#/* 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 creates and loads the database, tables and 
#          Stored procdures for the TPC-B. The Storage engine currently
#          can be NDBCluster, InnoDB, or MyISAM, but is easly exanded
#          to use other storage engines.
#------------------------------------------------------------------------
#
#-----------------------------------------------
#             Perl Includes 
#-----------------------------------------------
use DBI;
use Getopt::Long;
use IO::File;
#-----------------------------------------------
#   Globals Vars  
#-----------------------------------------------
our $host='localhost'; 
our $port='3306';
our $user='root';
our $pass='';
our $database='TPCB';
our $dbhM='';
our $aid=0;
our $bid=0;
our $tid=0;
our $balance=0;
our $accounts=100000; #/should be changed for bigger databases
our $branches=10000;   #/should be changed for bigger databases
our $c_only=0;
our $sp_only=0;
our $engine_type='NDB';
our $lfg_name='lg1';
our $lfgSize="250M";
our $lfgUndoBufSize="100M";
our $ts_name='ts1'; 
our $tsSize="500M";
our $diskData=0;
our $mixData=0;
our $num_undo=1;
our $skipLFG=0;
our $num_data=2;
our $cfiles=0;
our $loadFromFiles=0;
our $sockPath='/tmp/mysql.sock';
our $useSock=0;
our $logFile='./Load_tpcb.log';
our $fh='';
our $failed=1;
our $sth='';
#-------------------------------------------------
#   Sub Pototypes 
#-------------------------------------------------

sub main ();
sub CollectCommandPromptInfo ();
sub ConnectToDatabases ();
sub DisconnectFromDatabase ();
sub CreateDB ();
sub Create_Tables ();
sub Create_Procedures ();
sub Load_Tables (); 
sub ShowVars ();
sub Usage ();
sub LoadFromFiles ();
sub CreateFiles ();
sub FailedSQL ($);
sub FailedExit ($);
sub SQL_ExecuteError ($$);

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

main();

sub main ()
{
  CollectCommandPromptInfo();
  if (!$cfiles){
    ConnectToDatabases();
    if (!$sp_only){
      CreateDB();
      Create_Tables();
    }
    Create_Procedures();
    if (!$c_only && !$sp_only){
      if (!$loadFromFiles){
        Load_Tables();
      }
      else{
        LoadFromFiles();
      }
    }
    DisconnectFromDatabase();
  }
  else{
    CreateFiles();
  }
  $fh->close;
  exit 0
}

#--------------------------------
# Usage
#--------------------------------
sub Usage ()
{
 ShowVars();
 die "\n\nProgram: Load TPC-B Database\n
       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 TPCB)

         -numA [--numAcc]        :Number of rows for accounts table.
                                  (default == 100,000)
         -ce  [--createOnly]      :creates the database and tables 
                                  but loads no data. (default false)

         -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).

         -i [--installSPonly]    :Only create Stored Procedures (default false)

         -lo [--loadFromFiles]   :Load data from files (default false)

         -cf [--cfiles]          :Create data files

         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 ..................... $host\n";
  print "User ..................... $user\n";
  print "Password ................. $pass\n" if ($pass ne '');
  print "Password ................. BLANK\n" if ($pass eq '');
  print "Port ..................... $port\n";
  print "Use Socket To Connect .... TRUE\n" if ($useSock);
  print "Use Socket To Connect .... FALSE\n" if (!$useSock);
  print "Socket Path .............. $sockPath\n" if ($useSock);
  print "Database ................. $database\n";
  print "Engine Type .............. $engine_type\n";
  print "Create Tables Only ....... TRUE\n" if ($c_only);
  print "Create Tables Only ....... FALSE\n" if(!$c_only);
  print "Program Load Data ........ TRUE\n" if(!$c_only &&
                                               !$loadFromFiles);
  print "Load Data From File ...... TRUE\n" if(!$c_only &&
                                               $loadFromFiles);
  print "Number of rows Account.... $accounts\n" if(!$c_only);
  print "Number of rows Branches... $branches\n" if(!$c_only);
  $teller=$branches*2;
  print "Number of rows Teller .... $teller\n" if(!$c_only);
  print "Use Disk Data ............ TRUE\n" if ($diskData || $mixData);
  print "Use Disk Data ............ FALSE\n" if (!$diskData && !$mixData);
  print "LFG Name ................. $lfg_name\n" if ($diskData || 
                                                         $mixData);
  print "Create Log File Group .... TRUE\n" if ($diskData ||
                                                $mixData &&
                                                !$skipLFG);
  print "Create Log File Group .... FALSE\n" if ($diskData ||
                                                $mixData &&
                                                $skipLFG);
  print "Number LFG Undo File(s)... $num_undo\n" if ($diskData ||
                                                         $mixData);
  print "Table Space Name ......... $ts_name\n" if ($diskData ||
                                                         $mixData);
  print "Number TS Data Files(s)... $num_data\n" if ($diskData ||
                                                         $mixData);
}

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

  if (!GetOptions("help"=>\$help,
                  "host:s"=>\$host,
                  "user:s"=>\$user,
                  "port:i"=>\$port,
                  "socket"=>\$useSock,
                  "spath:s"=>\$sockPath,
                  "pass:s"=>\$pass,
                  "engine:s"=>\$engine_type,
                  "database:s"=>\$database,
                  "createOnly"=>\$c_only,
                  "lfgName:s"=>\$lfg_name,
                  "lfgSize:s"=>\$lfgSize,
                  "lfgUndoBufSize:s"=>$lfgUndoBufSize,
                  "numLFGUndo:i"=>\$num_undo,
                  "skipLFGcreate"=>\$skipLFG,
                  "tsName:s"=>\$ts_name,
                  "tsSize:s"=>\$tsSize,
                  "numTSData:i"=>\$num_data,
                  "numAcc:i"=>\$accounts,
                  "installSPonly"=>\$sp_only,
                  "loadFromFiles"=>\$loadFromFiles,
                  "cfiles"=>\$cfiles,
                  "logfile:s"=>\$logFile))
  {
    Usage();
  }
  if ($help){Usage();}
  $branches=$accounts/10;
  if ($useSock){$ENV{MYSQL_UNIX_PORT} = $sockPath;}
  if ($engine_type ne 'ndb' && $engine_type ne 'ndbcluster'
      && $engine_type ne 'InnoDB' && $engine_type ne 'MyISAM' 
      && $engine_type ne 'innodb' && $engine_type ne 'myisam' 
      && $engine_type ne 'NDBDD' && $engine_type ne 'NDBMIX' 
      && $engine_type ne 'ndbdd' && $engine_type ne 'ndbmix'
      && $engine_type ne 'NDB' && $engine_type ne 'NDBCLUSTER')
  {
    die Usage();
  } 
  if ($engine_type eq 'ndbdd'){$engine_type = "ndb"; $diskData = 1;}
  if ($engine_type eq 'ndbmix'){$engine_type = "ndb"; $mixData = 1;}
  if ($engine_type eq 'NDBDD'){$engine_type = "ndb"; $diskData = 1;}
  if ($engine_type eq 'NDBMIX'){$engine_type = "ndb"; $mixData = 1;}
  $fh = IO::File->new("$logFile", O_WRONLY|O_APPEND)
    or die "ERROR: could not open $logFile: $!\n";
  print $fh "TPCB_Loader -> CollectCommandPrompt Complete\n";
  #ShowVars();
}

#---------------------------------------------
#  Connect to database 
#---------------------------------------------
sub ConnectToDatabases ()
{
  if (!$useSock){
    $dbhM = DBI->connect("dbi:mysql:database=mysql;host=$host;port=$port",
                       "$user", "$pass",{RaiseError => 0,PrintError =>0})
    or FailedSQL("Can't connect to Master process!\n");
  }
  else{
    $dbhM = DBI->connect("DBI:mysql:database=mysql;host=$host",
                  "$user", "$pass", {RaiseError => 0,PrintError =>0})
    or FailedSQL("Can't connect to Master process!");
  }
  print $fh "TPCB_Loader -> Connected to Master!\n";
}

#-----------------------------------------------
#  Disconnect from database 
#-----------------------------------------------
sub DisconnectFromDatabase ()
{
  $dbhM->disconnect
    or warn " Disconnection failed: $DBI::errstr\n";
  print $fh "TPCP_Loader -> Disconnected from MySQLD on $host\n";
}

#-------------------------------------------------
#  Create database 
#-------------------------------------------------
sub CreateDB ()
{
  print $fh "TPCB_Loader -> Creating database $database!\n";
  $sth = $dbhM->prepare("DROP DATABASE IF EXISTS $database;")
	or FailedSQL("Prepare DROP DB error: ");
  $sth->execute();#/Don't care if returns success. No "die" needed
  $sth->finish();

  $sth = $dbhM->prepare("CREATE DATABASE $database;")
    or FailedSQL("Prepare Create DB error: ");
  $sth->execute()
    or FailedSQL("Create DB Error: ");
  $sth->finish();

  $sth = $dbhM->prepare("USE $database;")
    or FailedSQL("Prepare USE error: ");
  $sth->execute()
    or FailedSQL("USE DB Error: ");
  $sth->finish();

  if($diskData || $mixData && !$skipLFG){
    print $fh "TPCB_Loader -> Creating LFG $lfg_name....\n";
    $sth = $dbhM->prepare("CREATE LOGFILE GROUP $lfg_name
                           ADD UNDOFILE 'undofile.dat'
                           INITIAL_SIZE $lfgSize
                           UNDO_BUFFER_SIZE = $lfgUndoBufSize
                           ENGINE=NDB;")
      or FailedSQL("Prepare CREATE LOGFILE GROUP error: ");
    $sth->execute()
      or FailedSQL("CREATE LOGFILE GROUP error: ");
    $sth->finish();
    if($num_undo > 1){
      $undo_count = $num_undo - 1;
      print $fh "TPCB_Loader -> Adding additional undo files, please stand by....\n";
      while ( $undo_count > 0){
        $file="undofile".$undo_count.".dat";
        print $fh "TPCB_Loader -> Adding undo file $file..\n";
        $sth = $dbhM->prepare("ALTER LOGFILE GROUP $lfg_name
                               ADD UNDOFILE '$file'
                               INITIAL_SIZE $lfgSize
                               ENGINE=NDB;")
          or FailedSQL("Prepare ALTER LOGFILE GROUP error: ");
        $sth->execute()
          or FailedSQL("ALTER LOGFILE GROUP error: ");
        $sth->finish();
        $undo_count--;
      }
    }
  }
  if($diskData || $mixData){
    print $fh "TPCB_Loader -> Creating TS $ts_name....\n";
    $sth = $dbhM->prepare("CREATE TABLESPACE $ts_name
                           ADD DATAFILE 'datafile.dat'
                           USE LOGFILE GROUP $lfg_name
                           INITIAL_SIZE $tsSize
                           ENGINE=NDB;")
      or FailedSQL("Prepare CREATE TABLESPACE error: ");
    $sth->execute()
      or FailedSQL("CREATE TABLESPACE error: ");
    $sth->finish();
  
    if($num_data > 1){
      $data_count = $num_data - 1;
      while  ($data_count > 0){
        $file="datafile".$data_count.".dat";
        print $fh "TPCB_Loader -> Add data file $file\n";
        $sth = $dbhM->prepare("ALTER TABLESPACE $ts_name
                               ADD DATAFILE '$file'
                               INITIAL_SIZE $tsSize
                               ENGINE=NDB;")
          or FailedSQL("Prepare ALTER TABLE SPACE error: ");
        $sth->execute()
          or FailedSQL("ALTER TABLE SPACE error: ");
        $sth->finish();
        $data_count--;
      }
    }
  }
  print $fh "TPCB_Loader -> $database Created.\n";
}

#------------------------------------------------------
#  Create tables 
#------------------------------------------------------
sub Create_Tables ()
{

  $sth = $dbhM->prepare("DROP TABLE IF EXISTS account;")
	or FailedSQL("Prepare drop account error: ");
  $sth->execute();#/Don't care if returns success. No die needed

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

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

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

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

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

  if(!$diskData){
    print $fh "TPCB_Loader -> Creating Account Table\n";
    
    my $loopCounter = 0;
    $failed = 1;

    while ($loopCounter < 4 && $failed == 1){
      $failed = 0;
      $loopCounter++;
      $sth = $dbhM->prepare("CREATE TABLE account (aid INT, bid INT, 
                                                   balance DECIMAL(8,2), 
                                                   filler CHAR(80), 
                                                   PRIMARY KEY (aid))
                                              ENGINE=$engine_type;")
        or FailedSQL("Prepare Create Account Error:");
      $sth->execute()
        or SQL_ExecuteError("Create Account Table Error:",1);
      $sth->finish();
    }
    if($loopCounter == 4){FailedExit("Create Account Table Error:");}
   
    print $fh "TPCB_Loader -> Creating Branch Table\n";
    $sth = $dbhM->prepare("CREATE TABLE branch (bid INT, balance DECIMAL(8,2),
                                                filler CHAR(80), 
                                                PRIMARY KEY (bid))
                                            ENGINE=$engine_type;")
      or FailedSQL("Prepare Create Branch Error: ");
    $sth->execute()
      or FailedSQL("Create Branch Table Error: ");
    $sth->finish();

    print $fh "TPCB_Loader -> Creating Teller Table\n";
    $sth = $dbhM->prepare("CREATE TABLE teller (tid INT, bid INT,  
                                                balance DECIMAL(8,2), 
                                                filler CHAR(80), 
                                                PRIMARY KEY (tid))
                                            ENGINE=$engine_type;")
       or FailedSQL("Prepare Create Teller Error: ");
     $sth->execute()
       or FailedSQL("Create Teller Table Error: ");
     $sth->finish()
  }  
  else{
    print $fh "TPCB_Loader -> Creating Account Table\n";
    $sth = $dbhM->prepare("CREATE TABLE account (aid INT, bid INT, 
                                                 balance DECIMAL(8,2), 
                                                 filler CHAR(80), 
                                                 PRIMARY KEY (aid)) 
                                                 TABLESPACE $ts_name 
                                    STORAGE DISK ENGINE=$engine_type;")
      or FailedSQL("Prepare Create Account DD Error: ");
    $sth->execute()
      or FailedSQL("Create DD Account Table Error: ");
    $sth->finish();
   
    print $fh "TPCB_Loader -> Creating Branch Table\n";
    $sth = $dbhM->prepare("CREATE TABLE branch (bid INT, balance DECIMAL(8,2),
                                                filler CHAR(80), 
                                                PRIMARY KEY (bid)) 
                                                TABLESPACE $ts_name
                                            STORAGE DISK ENGINE=$engine_type;")
      or FailedSQL("Prepare Create Branch DD Error: ");
    $sth->execute()
      or FailedSQL("Create DD Branch Table Error: ");
    $sth->finish();
   
     print $fh "TPCB_Loader -> Creating Teller Table\n";
     $sth = $dbhM->prepare("CREATE TABLE teller (tid INT, bid INT,  
                                                 balance DECIMAL(8,2), 
                                                 filler CHAR(80), 
                                                 PRIMARY KEY (tid)) 
                                                 TABLESPACE $ts_name
                                              STORAGE DISK ENGINE=$engine_type;")
       or FailedSQL("Prepare Create Teller Error: ");
     $sth->execute()
       or FailedSQL("Create DD Teller Table Error: ");
     $sth->finish();
  }

  print $fh "TPCB_Loader -> Creating History Table\n";
  if(!$diskData && !$mixData){
    $sth = $dbhM->prepare("CREATE TABLE history (id BIGINT NOT 
                                                 NULL AUTO_INCREMENT, 
                                                 aid INT, tid INT, 
                                                 bid INT, 
                                                 TransTime DATETIME,
                                                 scriptId CHAR(10),
                                                 filler CHAR(80), 
                                    PRIMARY KEY (id))ENGINE=$engine_type;")
      or FailedSQL("Prepare Create History Error: ");
    $sth->execute()
      or FailedSQL("Create History Table Error: ");
    $sth->finish();
  }
  else{
    $sth = $dbhM->prepare("CREATE TABLE history (id BIGINT NOT 
                                                 NULL AUTO_INCREMENT, 
                                                 aid INT, tid INT, 
                                                 bid INT, 
                                                 TransTime DATETIME,
                                                 scriptId CHAR(10),
                                                 filler CHAR(80), 
                                                 PRIMARY KEY (id)) 
                                                 TABLESPACE $ts_name 
                                      STORAGE DISK ENGINE=$engine_type;")       
       or FailedSQL("Prepare Create History DD Error: ");
    $sth->execute()
      or FailedSQL("Create History DD Table Error: ");
    $sth->finish();
  }
  
  print $fh "TPCB_Loader -> Creating Trans Table\n";
  if(!$mixData){
    $sth = $dbhM->prepare("CREATE TABLE trans (scriptId CHAR(20) NOT NULL, 
                                               count INT, tps FLOAT, 
                                               lastTransTime DATETIME, 
                                               startTime DATETIME,
                                               PRIMARY KEY (scriptId))
                                               ENGINE=$engine_type;")
      or FailedSQL("Prepare Trans Table Error: ");
    $sth->execute()
      or FailedSQL("Create Trans Table Error: ");
    $sth->finish();
  }
  else{
    $sth = $dbhM->prepare("CREATE TABLE trans (scriptId CHAR(20) NOT NULL, 
                                               count INT, tps FLOAT, 
                                               lastTransTime DATETIME, 
                                               startTime DATETIME, 
                                               PRIMARY KEY (scriptId))
                                               TABLESPACE $ts_name
                                         STORAGE DISK ENGINE=$engine_type;")
      or FailedSQL("Prepare Trans Table Create DD Error: ");
    $sth->execute()
      or FailedSQL("Create DD Trans Table Error: ");
    $sth->finish();
  }
 
  print $fh "TPCB_Loader -> Creating Sync Table\n";
  $sth = $dbhM->prepare("CREATE TABLE sync ( host CHAR(20),
                                              numberOfTests INT, 
                                              tm INT,
                                              type CHAR(10),
                                              user CHAR(10),
                                              pass CHAR(10),
                                              db   CHAR(10),
                                              useSock INT,
                                              socp VARCHAR(25),
                                              socp2 VARCHAR(25),
                                              port CHAR(10),
                                              port2 CHAR(10),
                                              perf INT,
                                              sp   INT,
                                              status CHAR(10),
                                              runTime INT,
                                              PRIMARY KEY (host))
                                              ENGINE=NDB;")
     or FailedSQL("Prepare Sync Table Error: ");
   $sth->execute()
     or FailedSQL("Create Sync Table Error: ");
   $sth->finish();
  
   print $fh "TPCB_Loader -> All Tables Created!\n";
}

#--------------------------------------------------------
#  Create Stored Procedures 
#---------------------------------------------------------
sub Create_Procedures ()
{
  print $fh "TPCB_Loader -> Creating Stored Procedures, please wait...\n";
  $sth = $dbhM->prepare("DROP PROCEDURE IF EXISTS $database.ExTrans;") 
         or FailedSQL("Prepare drop ExTrans error: ");
  $sth->execute()
        or FailedSQL("Drop SP  Error: ");
  $sth->finish();

  $sth = $dbhM->prepare("
    CREATE PROCEDURE $database.ExTrans(IN amt DECIMAL(10,2))
    BEGIN
     DECLARE bal DECIMAL(10,2) DEFAULT 0.0;
     DECLARE bbal DECIMAL(10,2) DEFAULT 0.0;
     DECLARE tbal DECIMAL(10,2) DEFAULT 0.0;

     DECLARE ran INT;
     SELECT RAND() * 100 INTO ran;

     START TRANSACTION;
     SELECT account.balance INTO bal FROM $database.account WHERE aid = ran;
     SELECT teller.balance INTO tbal FROM $database.teller WHERE tid = ran;
     SELECT branch.balance INTO bbal FROM $database.branch WHERE bid = ran;

     SET bal = bal + amt;
     SET bbal = bbal + amt;
     SET tbal = tbal + amt;

     UPDATE $database.account SET balance = bal, filler = 'account updated' 
     WHERE aid = ran;
     UPDATE $database.branch SET balance = bbal, filler = 'branch updated'
     WHERE bid = ran;
     UPDATE $database.teller SET balance = tbal, filler = 'teller updated'
     WHERE tid = ran;
     COMMIT;

    END;")or FailedSQL("Prepare ExTrans error: ");

  $sth->execute()
        or FailedSQL("Create ExTrans Procedure Error: ");
  $sth->finish();

  $sth = $dbhM->prepare("DROP PROCEDURE IF EXISTS $database.HistInsert;") 
         or FailedSQL("Prepare Drop HistInsert error: ");
  $sth->execute()
        or FailedSQL("Drop HI SP  Error: ");
  $sth->finish();

 $sth = $dbhM->prepare("CREATE PROCEDURE $database.HistInsert()
    BEGIN
      DECLARE ran INT;
      SELECT RAND() * 100 INTO ran;
        START TRANSACTION;
          INSERT into $database.history (aid,tid,bid,TransTime,scriptID,filler) 
                                VALUES(ran,ran,ran,now(),ran,'MoreMoney');
        COMMIT;
    END;") or FailedSQL("Prepare History Insert SP error: ");

  $sth->execute()
        or FailedSQL("Create HI Procedure Error: ");
  $sth->finish();
  
 $sth = $dbhM->prepare("DROP PROCEDURE IF EXISTS $database.UpdateTrans;") 
         or FailedSQL("Prepare Drop Update Trans  error: ");
  $sth->execute()
        or FailedSQL("Drop Update Trans SP  Error: ");
  $sth->finish();

 $sth = $dbhM->prepare("CREATE PROCEDURE $database.UpdateTrans(IN counter INT, 
                                                          IN tps FLOAT, 
                                                          IN id CHAR(10))
    BEGIN
     START TRANSACTION;
     UPDATE $database.trans set count = counter, lastTransTime = now(), 
                           TPS = tps where scriptId = id;
     COMMIT;
    END;") or FailedSQL("Prepare Update Trans SP error: ");

  $sth->execute()
        or FailedSQL("Create Update Trans Procedure Error: ");
  $sth->finish();

  $sth = $dbhM->prepare("CREATE PROCEDURE $database.TruncateH()
    BEGIN
     truncate TPCB.history;
    END;") or FailedSQL("Prepare History Insert SP error: ");

  $sth->execute()
        or FailedSQL("Create TH Procedure Error: ");
  $sth->finish();

  
  print $fh "TPCB_Loader -> Stored Procedures created!\n";
}
#-----------------------------------------------
#  Load tables 
#-----------------------------------------------
sub Load_Tables ()
{
  print $fh "TPCB_Loader -> Loading tables\n";
  #print "Loading accounts table -- Please wait\n";
  $sth = $dbhM->prepare("INSERT into $database.account VALUES(?, ?, ?, ?);")
	or FailedSQL("Prepare insert into account error: ");
  
  while($bid < $branches){
    $bid++;
    $i = 0;
    while($i < ($accounts/$branches)){
      $i++;
      $aid++;
#      print "aid = $aid\n";
#      print "bid = $bid\n";
      $sth->bind_param(1, $aid);
      $sth->bind_param(2, $bid);
      $sth->bind_param(3, $balance);
      $sth->bind_param(4, "Going fishing");
      $sth->execute()
        or FailedSQL("insert into account Error: ");
    }
  }
  $sth->finish();

  $sth = $dbhM->prepare("INSERT into $database.branch values(?, ?, ?);")
	or FailedSQL("Prepare insert into account error: ");
  $bid = 0;
  #print "\nLoading Branch Table ";
  while($bid < $branches){
    $bid++;
    $sth->bind_param(1, $bid);
    $sth->bind_param(2, $balance);
    $sth->bind_param(3, "Going on Vacation");
    $sth->execute()
      or FailedSQL("insert into branch Error: ");
  }	
  $sth->finish();

  $sth = $dbhM->prepare("INSERT into $database.teller values(?, ?, ?, ?);")
	or FailedSQL("Prepare insert into account error: ");
  $bid=0;
  #print "\nLoading Teller Table ";
  while($bid < $branches){
    $bid++;
    $i=0;
    while($i < 2){
      $i++;
      $tid++;
      $sth->bind_param(1, $tid);
      $sth->bind_param(2, $bid);
      $sth->bind_param(3, $balance);
      $sth->bind_param(4, "I have to work");
      $sth->execute()
 	or FailedSQL("insert into teller Error: ");
    }
  }
  $sth->finish();
  print $fh "TPCB_Loader -> All tables have been loaded!\n";
}

#-----------------------------------------------
# Create Files
#-----------------------------------------------
sub CreateFiles ()
{
  #print "Creating file account.data, please stand by...\n";
  
  $bid = 0;
  $fh = IO::File->new("./account.dat", O_WRONLY|O_CREAT)
     or die "ERROR: could not open ./account.dat: $!\n";
  if (defined $fh){
    while($bid < $branches){
      $bid++;
      $i = 0;
      while($i < ($accounts/$branches)){
        $i++;
        $aid++;
#       print "aid = $aid\n";
#       print "bid = $bid\n";
        print $fh "$aid,$bid,$balance,\"Going fishing\"\n";
      }
    }
    $fh->close;
  }
 
  #print "Creating file branch.data, please stand by...\n";
  
  $bid = 0;
  $fh = IO::File->new("./branch.dat", O_WRONLY|O_CREAT)
     or die "ERROR: could not open ./branch.dat: $!\n";
  if (defined $fh){
    while($bid < $branches){
      $bid++;
      print $fh "$bid,$balance,\"Going on Vacation\"\n";
    }
    $fh->close;
  }


  #print "Creating file teller.data, please stand by...\n";
  
  $bid = 0;
  $fh = IO::File->new("./teller.dat", O_WRONLY|O_CREAT)
     or die "ERROR: could not open ./teller.dat: $!\n";
  if (defined $fh){
    while($bid < $branches){
      $bid++;
      $i=0;
      while($i < 2){
        $i++;
        $tid++;
        print $fh "$tid,$bid,$balance,\"I have to work\"\n";
      }
    }
    $fh->close;
  }
}

#-----------------------------------------------
# Load Data From Files
#-----------------------------------------------
sub LoadFromFiles ()
{
  #print "Loading Database from files\n";
  
  $sth = $dbhM->prepare("USE $database;")
    or die "Prepare error: ", $dbhM->errstr;
  $sth->execute()
    or die "USE DB Error: ", $sth->errstr;
  $sth->finish();

  if (-e "./account.dat"){
    #print "Loading account table\n";
    $sth = $dbhM->prepare("LOAD DATA INFILE './account.dat' INTO TABLE account
                           FIELDS TERMINATED BY ','
                           ENCLOSED BY '\"';")
       or die "Prepare Load account error: ", $dbhM->errstr;
    $sth->execute()
       or die "Load account error: ", $sth->errstr;
    $sth->finish();
  }
  else{
   die "Cant load from file ./account.dat";
  }
  
  if (-e "./branch.dat"){
    #print "Loading branch table..\n";
    $sth = $dbhM->prepare("LOAD DATA INFILE './branch.dat' INTO TABLE branch
                           FIELDS TERMINATED BY ','
                           ENCLOSED BY '\"';")
       or die "Prepare Load branch error: ", $dbhM->errstr;
    $sth->execute()
       or die "Load branch error: ", $sth->errstr;
    $sth->finish();
  }
  else{
   die "Cant load from file ./branch.dat";
  }

  if (-e "./teller.dat"){
    #print "Loading teller table..\n";
    $sth = $dbhM->prepare("LOAD DATA INFILE './teller.dat' INTO TABLE teller
                           FIELDS TERMINATED BY ','
                           ENCLOSED BY '\"';")
       or die "Prepare Load teller error: ", $dbhM->errstr;
    $sth->execute()
       or die "Load teller error: ", $sth->errstr;
    $sth->finish();
  }
  else{
   die "Cant load from file ./teller.dat";
  }
}

sub FailedSQL ($)
{
  $msg = shift;
  print $fh "TPCB_load -> $msg\n";
  $msg = $DBI::err;
  print $fh "Err#: $msg\n";
  $msg = $DBI::errstr;
  print $fh "Err-> $msg\n";
  $fh->close;
  print 1;
  exit 1;
}

sub SQL_ExecuteError($$)
{
  $msg = shift;
  $show = shift;
  print $fh "$msg\n";
  my $showResults=$sth->err;
  my $showResults2=$sth->errstr;
  print $fh "Err#: $showResults\n";
  print $fh "Err-> $showResults2\n";
  if($show){
    my $showResults3='';
    my $ShowNDB_sth = $dbhM->prepare("SHOW ENGINE NDB STATUS;")
      or die "Prepare SHOW ENGINE NDB STATUS: ", $dbhM->errstr;
    $ShowNDB_sth->execute();
    $ShowNDB_sth->bind_col (1, \$showResults);
    $ShowNDB_sth->bind_col (2, \$showResults2);
    $ShowNDB_sth->bind_col (3, \$showResults3);
    print $fh "**** SHOW ENGINE STATUS ****\n";
    while($ShowNDB_sth->fetch()){
      print $fh "------------------------------------\n";
      print $fh "$showResults\n";
      print $fh "$showResults2\n";
      print $fh "$showResults3\n";
    }
  }
  sleep 10;
  $failed = 1;
}

sub FailedExit ($)
{
  $msg = shift;
  print $fh "TPCB_load -> $msg\n";
  $fh->close;
  print 1;
  exit 1;
}

###################  End of script #############