#!/usr/bin/perl -w

# Written by Tim Ellis
#
# A simple MySQL DBI Skeleton. I'm always rewriting this stuff, so it'd
# be good to just have one so I can start from a useful base.

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 = "";

# variables used over and over
my $sql;
my $res;
my $tabName = 'mysql';
my $dbName = '';
my @extraArgs;

# process input arguments -- any arguments that aren't known switches
# are stuck onto @extraArgs
foreach my $arg (@ARGV) {
	if ($arg =~ /^-+h(elp)*$/)          { doArgs(); exit 0; }
	elsif ($arg =~ /^-+hostname=(.+)$/) { $dbMachine = $1; }
	elsif ($arg =~ /^-+dbname=(.+)$/)   { $dbName = $1; }
	elsif ($arg =~ /^-+user=(.+)$/)     { $dbUser = $1; }
	elsif ($arg =~ /^-+password=(.+)$/) { $dbPass = $1; }
	elsif ($arg =~ /^-+verbose$/)       { $verbose = 1; }
	elsif ($arg =~ /^-+logfile=(.+)$/)  { $logFile = $1; }
	else { push (@extraArgs, $arg); }
}

# 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");
}

# set unbuffered output. it is not an accident that
# i choose to select STDOUT last.
select LOG;    $| = 1;
select STDERR; $| = 1;
select STDOUT; $| = 1;

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

# ensure no other copy running. you may not want this. if not, just comment
# out this section
my $binName = "";
if ($0 =~ /^(.+)\.pl/) { $binName = $1; }
$0 .= " currentRunningCopy";
my $psNum = `ps auxww | grep $binName | grep currentRunningCopy | grep -v grep | wc -l`;
if ($psNum > 1) {
	doLog (" E Hmm. I see a copy of myself running. Perhaps kill it first?");
	exit 255;
}

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

# A query with a result set
$sql = "select * from mysql.user";
$res = sqlRes ($sql);
# checking scalar @{$res} is an easy way to be sure there actually
# was a resultset - if there were 0 rows, then this will not test true
if ($res && scalar @{$res}) {
	foreach my $rowHash (@{$res}) {
		print " - Sample Query Output for $rowHash->{Host}\n";
		foreach my $key (sort keys %{$rowHash}) {
			print "   . $key: $rowHash->{$key}\n";
		}
	}
}

# A query without a result set - but also quoting the user input
my $firstName = "John O'Donnell";
$firstName = &doQuote ($firstName);
$sql = "delete from test.exampleTable where first_name = '$firstName'";
sqlNoRes ($sql);

# show create table sort of output - if you're not sure and you need to figure
# out what the keys are, you can do this:
# foreach my $key (keys %{$res->[0]}) {
#	 print "key: $key\n";
# }
$sql = "show create table $tabName";
$res = sqlRes ($sql);
if ($DBI::errstr) {
	doLog (" E DBI Error is $DBI::errstr on sql of $sql");
} else {
	foreach my $rowHash (@{$res}) {
		print $rowHash->{"Create Table"} . "\n";
	} 
}

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


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

# take a SQL query as input, log it, return a resultset to caller 
sub sqlRes {
	my $query = shift;

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

	# how to get to this... $res->[rowNum]->{colName}
	if ($dbName) { $dbh->do ("use $dbName"); }
	my $res = $dbh->selectall_arrayref($query, { Columns=>{} });
	if ($DBI::errstr) {
		if ($verbose) { doLog (" E SQL failed: $query"); }
		return undef;
	}

	return $res;
}

# do a SQL statement without a resultset
sub sqlNoRes {
	my $query = shift;

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

	if ($dbName) { $dbh->do ("use $dbName"); }
	$dbh->do ($query);

	if ($DBI::errstr) {
		if ($verbose) { doLog (" E SQL failed: $query"); }
		return 0;
	}

	return 1;
}

# 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 "  --dbname	   database against which to run SQL for this script\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;
}