#!/usr/bin/perl -w

# Written by Tim Ellis released under the terms of the GPL
#
# This is a database skeleton script. It has the basics to do more complicated
# tasks. Enjoy.

use strict;
use DBI;
use POSIX qw/setsid/;
use POSIX qw/strftime/;
use Time::HiRes qw/gettimeofday usleep/;

# 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 @extraArgs;
my ($startTime, $endTime, $queryMillis);

my $slowM = 2;
my $slowN = 0.5;
my $tabName = "";
my $startId = 0;
my $endId = 0;
my $iter = 1000;
my $dbName = "";

# 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 =~ /^-+dbname=(.+)$/)   { $dbName = $1; }
        elsif ($arg =~ /^-+hostname=(.+)$/) { $dbMachine = $1; }
        elsif ($arg =~ /^-+user=(.+)$/)     { $dbUser = $1; }
        elsif ($arg =~ /^-+password=(.+)$/) { $dbPass = $1; }
        elsif ($arg =~ /^-+verbose$/)       { $verbose = 1; }
        elsif ($arg =~ /^-+logfile=(.+)$/)  { $logFile = $1; }
        elsif ($arg =~ /^-+iter=(.+)$/)     { $iter = $1; }
        elsif ($arg =~ /^-+slow=(.+),(.+)$/) { $slowM = $1; $slowN = $2}
        elsif ($arg =~ /^-+table=(.+)$/)    { $tabName = $1; }
        elsif ($arg =~ /^-+start=(.+)$/)    { $startId = $1; }
        else { push (@extraArgs, $arg); }
}

# We require a DB to work on. If your task doesn't do
# something to a particular DB, you can remove this.
unless ($dbName) {
        doArgs(); exit 255;
}

# 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;

# connect, get the max(id), then disconnect, if they didn't
# pass us an endid
unless ($endId) {
        &connectDb();
        $sql = "select max(id) id from $tabName";
        $res = sqlRes ($sql);
        $endId = $res->[0]->{id};
        $dbh->disconnect();

        unless (defined $endId) { $endId = 0; }
        unless (defined $startId) { $startId = 0; }

        doLog (" + Set startId = $startId, endId = $endId");
}

my $startRange = $startId;
my $endRange = $startRange + $iter;
if ($endRange > $endId) { $endRange = $endId; }
doLog (" - Doing initial range $startRange .. $endRange.");
while ($startRange < $endId) {
    $startTime = gettimeofday();
    &connectDb();

    $sql = "select id,txt from $tabName where id > $startRange and id <= $endRange and deleted=0";
    my $rangeRes = sqlRes ($sql);
    foreach my $oneRow (@{$rangeRes}) {
        # select id from another table where txt is this one
        $sql = "select id from tableTwo where tabTwoTxt = '" . doQuote($oneRow->{'txt'}) . "'" ;
        my $tabTwoRes = sqlRes ($sql);

        # report how many matches
        if (scalar @{$tabTwoRes} > 0) {
            doLog (" + tableTwo has " . scalar @{$tabTwoRes} . " matches.");
            foreach my $tabTwoRow (@{$tabTwoRes}) {
				# do whatever
            }
        }
    }

    $dbh->disconnect();
    $endTime = gettimeofday();
    $queryMillis = int(($endTime - $startTime) * 1000);

    # next section up!
    $startRange = $startRange + $iter;
    $endRange = $startRange + $iter;
    if ($endRange > $endId) { $endRange = $endId; }

    # sleep portion of the script - do nothing to be nice to the DB
    my $sleepTime = $queryMillis * 1000 * $slowM + $slowN * 1000000;
    my $sleepSecs = sprintf ('%0.1f', $sleepTime / 1000000);
    doLog (" - Sleeping ${slowM}x+$slowN=$sleepSecs seconds (range took ${queryMillis}ms) -- will do $tabName:$startRange to $endRange next.");
    # 2000 * milliseconds == 2x microseconds
    usleep ($sleepTime);
}

if ($logFile) { close LOG; }
exit 0;


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

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

        $dbh->do ("use $dbName");
        if ($verbose) { doLog (" + $dbName :: $query"); }

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

        return $res;
}

sub sqlNoRes {
    my $query = shift;
    $dbh->do ("use $dbName");
    $dbh->do ($query);

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


# 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 --dbname=D --iter=I [--table=T] [--slow=M,N --start=Q]\n";
        print "  [--hostname=<hostName>] [--user=<user>] [--password=<password>] [--logfile=<logFileName>]\n";
        print "  --dbname       Table to work with\n";
        print "  --iter         How many rows to do at a time\n";
        print "  --table        Table to work with\n";
        print "  --slow         Sleep Mx+N seconds between queries, where x=[how long last query took]\n";
        print "  --start        What ID to start with (will work BACKWARD from that ID)\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";
        exit 255;
}

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

# quote the input, or return empty string if input is undefined
sub doQuote {
    my $arg = shift;

    if (defined $arg) {
		$arg =~ s/'/''/g;
    } else {
        $arg = "";
    }

    return $arg;
}

# take a non-numeric input and quote the quotes, backtick the backticks,
# and then put quotes around the result, or return '' if the arg is
# entirely undefined
sub doSanity {
    my $arg = shift;

    if (defined $arg) {
        if ($arg !~ /^[\d]+$/) {
            $arg =~ s/'/''/g;
            $arg =~ s/\\/\\\\/g;
            $arg = "'$arg'";
        }
    } else {
        $arg = "''";
    }

    return $arg;
}