#!/usr/bin/perl -w

# Script to hammer a MySQL server. Mostly tests index reads during
# the DELETE portion.

use strict;
use DBI;
use POSIX qw/setsid/;
use POSIX qw/strftime/;

# database login information
my $dbUser = "root";
my $dbPass = "";
my $dbMachine = "localhost";

# other arguments that may be passed in
my $verbose = "";
my $logFile = "";

# process input arguments
foreach my $arg (@ARGV) {
	if ($arg =~ /^-+h(elp)*$/)       { doArgs(); exit 0; }
	if ($arg =~ /^-+hostname=(.+)$/) { $dbMachine = $1; }
	if ($arg =~ /^-+user=(.+)$/)     { $dbUser = $1; }
	if ($arg =~ /^-+password=(.+)$/) { $dbPass = $1; }
	if ($arg =~ /^-+verbose$/)       { $verbose = 1; }
	if ($arg =~ /^-+logfile=(.+)$/)  { $logFile = $1; }
}

# if they want to log to a file, open it, later doLog() will
# write into this file
if ($logFile) {
	open (LOG, ">> $logFile") || die "Can't open $logFile for writing.";
	close (STDERR);
	open (STDERR, ">> $logFile");
}

# we'll overwrite this from time to time
my $currTime = strftime ('%Y-%m-%d %H:%M:%S', localtime);
&doLog ("\n ----- Starting $0 at $currTime");

# make database connection
my $dbh;
my $sth;
my $res;
&connectDb();

## # start doing queries
## $res = &doSql ("select * from mysql.user");
## foreach my $rowHash (@{$res}) {
## 	print "Sample Output: Got host = $rowHash->{Host} and user = $rowHash->{User}\n";
## }

$dbh->do ("drop table if exists test.hammerMe");
$dbh->do ("CREATE TABLE test.hammerMe (
  `id` int(11) NOT NULL default '0',
  `fname`    varchar(128) default NULL,
  `lname`    varchar(128) default NULL,
  `bigwords` varchar(255) default NULL,
  `age` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `nameidx` (`fname`, `lname`),
  KEY `bwordidx` (`bigwords`)
) TYPE=InnoDB");

my $inserts = 20000;
my $deletes = 30000;
my $showEvery = 500;
my $sql = "";
my $id = 1;
my $age;
my ($i, $j);
my $wordNum;
my @wordList = split (/\n/, `cat /usr/share/dict/words`);
my $bigWord;
 
while (1) {
	# initialise the table data
	$dbh->do ("delete from test.hammerMe");

	# do a buncha inserting
	for ($i = 1; $i<$inserts; $i++) {
		$bigWord = "";
		for ($j = 1; $j<30; $j++) {
			$bigWord .= $wordList[int (rand (scalar @wordList)) + 1];
		}

		$age = int(rand(32))+10;
		my $firstName = $wordList [int (rand (scalar @wordList)) + 1];
		my $lastName = $wordList [int (rand (scalar @wordList)) + 1];
		$sql = "insert into test.hammerMe (id,fname,lname,bigwords,age) "
		     . "values ($id, '" . &doQuote ($firstName) . "', '" . &doQuote ($lastName) . "', "
		     . "'" . &doQuote ($bigWord) . "', $age);";
		$currTime = strftime ('%Y-%m-%d %H:%M:%S', localtime);
		if ($id % $showEvery == 0) { print "$currTime :: did $showEvery inserts much like this: $sql\n"; }
		$dbh->do ("$sql");
		$id++;
	}

	# do a buncha deleting
	for ($i = 1; $i<$deletes; $i++) {
		$wordNum = int (rand (scalar @wordList)) + 1;
		$sql = "delete from test.hammerMe where fname = '" . &doQuote ($wordList[$wordNum]) . "';";
		$currTime = strftime ('%Y-%m-%d %H:%M:%S', localtime);
		if ($i % $showEvery == 0) { print "$currTime :: did $showEvery deletes much like this one: $sql\n"; }
		$dbh->do ("$sql");
	}
}

$dbh->disconnect();
if ($logFile) { close LOG; }
exit 0;


# -----------------------------------------------------------------
# -----------------------------------------------------------------
#                           subroutines
# -----------------------------------------------------------------
# -----------------------------------------------------------------

# take a SQL query as input, log it, then 
sub doSql {
	my $query = shift;

	if ($verbose) { &doLog (" + $query"); }

	# how to get to this... $res->[rowNum]->{colName}
	my $res = $dbh->selectall_arrayref($query, { Columns=>{} });

	return $res;
}

# uhm. connect to the db. :)
sub connectDb {
	&doLog (" - Attempting to connect to database $dbMachine.");
	$dbh = DBI->connect("dbi:mysql::$dbMachine", "$dbUser", "$dbPass", { RaiseError => 0});

	# we try to prepare a statement and if we fail, time to give up
	eval { $sth = $dbh->prepare("select 1"); };
	if ($@) {
		&doLog (" E ERROR: Hmm. Something went Very Wrong connecting to database.");
		exit 255;
	}
}

# usage
sub doArgs {
	print "\n";
	print "usage: $0 [--hostname=<hostName>] [--user=<user>] [--password=<password>] [--logfile=<logFileName>]\n";
	print "  --hostname     host to use to report the stats\n";
	print "  --user         username to login as\n";
	print "  --password     username's password\n";
	print "  --logfile      send log output to logFileName rather than STDERR\n";
	print "  --help         get help\n";
	print "  --verbose      be verbose\n";
}

# output something for logging -- if the input is " X yyyyyyy" then stick
# a timestamp in the whitespace between X and yyyy, otherwise, just output
# what we were given.
sub doLog {
	my $toLog = shift;
	my $toPrint;
	$currTime = strftime ('%Y-%m-%d %H:%M:%S', localtime);

	if ($toLog =~ /^\s([^\s])\s(.+)$/) {
		$toPrint = " $1 $currTime :: $2\n";
	} else {
		$toPrint = " - $currTime :: $toLog\n";
	}

	# send output to STDERR or log file, whichever user specified
	if ($logFile) {
		print LOG $toPrint;
	} else {
		print STDERR $toPrint;
	}
}

# run a system command. exit on failure. log the command if $verbose
sub doSystem {
	my $cmd = shift;
	if ($verbose) { &doLog (" + $cmd"); }

	system ($cmd);

	if ($? >> 8) {
		&doLog (" E Child process returned " . $? >> 8 . " exit status. Dying.");
		exit 255;
	}
}

# change single quotes to doubled-up single quotes
sub doQuote {
	my $arg = shift;
	$arg =~ s/'/''/g;
	return $arg;
}