#!/usr/bin/perl -w

use strict;
use DBI qw(:sql_types);

use vars ( qw/ $dbh $sth_sel_domain $sth_ins_domain $sth_upd_domain_time $sth_ins_scount $sth_upd_scount $LAST_ERROR $finish /);

my $dbh = DBI->connect( "DBI:mysql:test:hostname", "user", "password",
                        { AutoCommit => 1, RaiseError => 0, PrintError => 0 }
                       ) or die "cannot connect to DB: $DBI::errstr";

my @domains = ("a".."z");
my $qcnt = 0;
my $scnt = 0;
my $icnt = 0;
my $ucnt = 0;
my $fcnt = 0;

$SIG{'TERM'} = 'finish';
$SIG{'HUP'}  = 'finish';
$SIG{'INT'}  = 'finish';
$finish = 0;

db_start();

# this first piece will just populate the database unless the data is already there.
print "select items that should not exist\n";
foreach ( @domains ) {
  if ($finish) {
    print_stats_and_quit();
  }

  my ($id, $t, $s) = cache_exists($_);
  if (!$id) {
    print "NEW -> $_";
    cache_store($_);
  } 
  ($id, $t, $s) = cache_exists($_);
  if ($id) {
    cache_update_time($id,$s);
  }
  
}

# loop until we get a sigterm
my $loop = 0;
while(1) {
  $loop++;
  print "now select items that should exist - loop $loop (queries $qcnt, failures $fcnt)\n";

  if ($finish) {
    print_stats_and_quit();
    exit;
  }

  foreach ( @domains ) {

    if ($LAST_ERROR) {
      $fcnt++;
      print "ERROR #$fcnt - $LAST_ERROR\n";
    }

    $LAST_ERROR = undef;
    my ($id, $t, $s) = cache_exists($_);
    if ($id) {
        cache_update_time($id,$s);
    }
    else {
      print "FAILED -> $_\n";
    }
  }
}

db_finish();
exit;



sub print_stats_and_quit {

  db_finish();

  print "Selects: $scnt\n";
  print "Inserts: $icnt\n";
  print "Updates: $ucnt\n";
  print "Queries: $qcnt\n";
  print "Failures: $fcnt\n";

  print "$LAST_ERROR\n" if ($LAST_ERROR);

  exit;

}

sub finish {
  print "Caught a signal..\n";
  $finish=1;
}

sub db_start {

  $sth_sel_domain = $dbh->prepare  ( "SELECT uri.id,UNIX_TIMESTAMP(uri.t) as ut,uri_scrape_count.value " .
                                     " FROM test.uri  LEFT JOIN test.uri_scrape_count ON " .
                                     " (uri.id=uri_scrape_count.id) WHERE uri.value = ? LIMIT 1");
  $sth_ins_domain = $dbh->prepare ( "INSERT INTO test.uri (value,a) VALUES (?,NOW())" );
  $sth_upd_domain_time = $dbh->prepare ( "UPDATE test.uri SET t=NOW() WHERE id=?" );
  $sth_ins_scount = $dbh->prepare ( "INSERT INTO test.uri_scrape_count (id,value) VALUES (?,?)" );
  $sth_upd_scount = $dbh->prepare ( "UPDATE test.uri_scrape_count SET value=value+1 WHERE id=? " );

}

sub db_finish {

  $sth_sel_domain->finish();
  $sth_ins_domain->finish();
  $sth_upd_domain_time->finish();
  $sth_ins_scount->finish();
  $sth_upd_scount->finish();

  $dbh->disconnect();
}

sub cache_exists {
  my $domain = shift;

  my ($id,$ut,$sc);

  $sth_sel_domain->bind_param (1, $domain);
  $qcnt++; $scnt++;
  if ($sth_sel_domain->execute) {
    ($id,$ut,$sc) = $sth_sel_domain->fetchrow_array();
  }
  else {
    $LAST_ERROR = "ERROR: cache_exists SELECT $domain failure - SQL Said: " . $sth_sel_domain->errstr . "\n";
    return;
  }

  if (!$id) {
    $LAST_ERROR = "ERROR: cache_exists SELECT $domain returned no ID - SQL Said: " . $sth_sel_domain->errstr;
  }

  if ($id && !defined $sc) {
    print "ERROR: $domain id not found or scrape_count=0!  id=$id sc=$sc\n";

    $sth_ins_scount->bind_param (1, $id);
    $sth_ins_scount->bind_param (2, 0);
    $qcnt++; $icnt++;
    my $ret = $sth_ins_scount->execute();
    if (!$ret) {
      $LAST_ERROR = "ERROR: cache_exists INSERT scount on $id - SQL Said: " . $sth_upd_scount->errstr;
      return;
    }
  }
  return ($id,$ut,$sc);
}

sub cache_store {
  my $domain = shift;

  $sth_ins_domain->bind_param (1, $domain);
  $qcnt++; $icnt++;

  if (my $eff = $sth_ins_domain->execute() ) {
    my ($id,$ut,$sc) = cache_exists($domain);
    return $eff;
  }
  else {
    $LAST_ERROR = "ERROR: cache_store INSERT on $domain - SQL Said: " . $sth_ins_domain->errstr;
    return;
  }
}

sub cache_update_time {
  my ($id,$scrape_count) = @_;
  $sth_upd_domain_time->bind_param (1, $id);
  my $eff = $sth_upd_domain_time->execute;
  if (!$eff) {
    $LAST_ERROR = "ERROR: cache_update_time UPDATE on id $id - SQL Said: " . $sth_upd_domain_time->errstr . "\n";
    return;
  }
  $qcnt++; $ucnt++;

  if ($eff) {
    $sth_upd_scount->bind_param (1, $id);
    my $ret = $sth_upd_scount->execute();
    if (!$ret) {
      $LAST_ERROR = "ERROR: cache_update_time UPDATE scount on $id - SQL Said: " . $sth_upd_scount->errstr . "\n";
      return;
    }
    $qcnt++; $ucnt++;
  }
  return $eff;
}