inital sanity checks for logs

3 files changed, 182 insertions, 68 deletions

diff --git a/lib/RHRD/rddb.pm b/lib/RHRD/rddb.pm
index 02cd457..c5e0a1a 100755
--- a/lib/RHRD/rddb.pm
+++ b/lib/RHRD/rddb.pm
@@ -191,7 +191,7 @@ sub check_log_exists
   $sth->execute($logname)
     or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
 
-  my $log_exists = $sth->fetchrow_array();
+  my ($log_exists) = $sth->fetchrow_array();
   $sth->finish();
 
   if(!defined $log_exists || $log_exists ne 'Y') {
@@ -202,16 +202,43 @@ sub check_log_exists
 
 sub get_log_table_name
 {
-  my ($ctx, $logname) = @_;
+  my ($logname) = @_;
   $logname=~s/ /_/g;
-  return $ctx->{'dbh'}->quote_identifier($logname . '_LOG');
+  return $logname . '_LOG';
+}
+
+sub get_log_table_name_escaped
+{
+  my ($ctx, $logname) = @_;
+  return $ctx->{'dbh'}->quote_identifier(get_log_table_name($logname));
+}
+
+sub check_log_table_exists
+{
+  my ($ctx, $logname) = @_;
+
+  my $sql = qq{select count(*) from information_schema.tables where table_schema = DATABASE() and table_name = ?;};
+  my $sth = $ctx->{'dbh'}->prepare($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  my $log_tabname = get_log_table_name($logname);
+  $sth->execute($log_tabname)
+    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
+
+  my ($cnt) = $sth->fetchrow_array();
+  $sth->finish();
+
+  unless($cnt) {
+    return ($cnt, 'OK', 'log table does not exist');
+  }
+  return ($cnt, 'OK', 'log table exists');
 }
 
 sub create_log_table
 {
   my ($ctx, $logname) = @_;
 
-  $logname = get_log_table_name($ctx, $logname);
+  $logname = get_log_table_name_escaped($ctx, $logname);
   my $sql = qq{
     create table if not exists $logname
     (ID INT NOT NULL PRIMARY KEY,
@@ -275,7 +302,7 @@ sub fill_log_table
   my $logname = shift;
   my @carts = @_;
 
-  $logname = get_log_table_name($ctx, $logname);
+  $logname = get_log_table_name_escaped($ctx, $logname);
   my $sql_log = qq{insert into $logname (ID, COUNT, TYPE, START_TIME, CART_NUMBER, TRANS_TYPE) values (?, ?, ?, ?, ?, ?);};
   my $sql_cart_type = qq{select TYPE from CART where NUMBER = ?;};
 
@@ -307,7 +334,7 @@ sub drop_log_table
 {
   my ($ctx, $logname) = @_;
 
-  $logname = get_log_table_name($ctx, $logname);
+  $logname = get_log_table_name_escaped($ctx, $logname);
   my $sql = qq{drop table $logname;};
 
   $ctx->{'dbh'}->do($sql)
@@ -910,6 +937,28 @@ sub get_dropboxes
   return @allowed_dbs;
 }
 
+###########################  Log handling  ######################
+
+sub list_logs
+{
+  my ($ctx) = @_;
+
+  my $sql = qq{select NAME from LOGS;};
+  my $sth = $ctx->{'dbh'}->prepare($sql)
+    or return (undef, 'ERROR', "Database Error: " . $ctx->{'dbh'}->errstr);
+
+  $sth->execute()
+    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);
+
+  my @logs;
+  while(my ($log) = $sth->fetchrow_array()) {
+    push @logs, $log;
+  }
+  $sth->finish();
+
+  return @logs;
+}
+
 ###########################  SHOW handling  ###########################
 
 sub get_shows_cart_range
@@ -1174,7 +1223,7 @@ sub get_show_carts
     return (undef, 'ERROR', "Log with name '$log' does not exist")
   }
 
-  $log = get_log_table_name($ctx, $log);
+  $log = get_log_table_name_escaped($ctx, $log);
   my $sql = qq{select COUNT,CART_NUMBER from $log order by COUNT;};
 
   my $sth = $ctx->{'dbh'}->prepare($sql)
diff --git a/utils/rhrd-sanity-check b/utils/rhrd-sanity-check
index 0929523..640c304 100755
--- a/utils/rhrd-sanity-check
+++ b/utils/rhrd-sanity-check
@@ -21,6 +21,7 @@
 #
 
 use strict;
+use lib "../lib/";
 use RHRD::rddb;
 
 sub print_usage
@@ -33,7 +34,91 @@ if($num_args > 0) {
   print_usage();
   exit(1);
 }
-my $ret = 0;
+
+
+
+sub check_showids
+{
+  my ($ctx) = @_;
+
+  my $errors = 0,
+  print "showids:\n";
+
+  my @show_ids = RHRD::rddb::list_showids($ctx);
+  if(!defined $show_ids[0] && defined $show_ids[1]) {
+    print STDERR "$show_ids[1]: $show_ids[2]";
+    return -1;
+  }
+  for my $show_id (@show_ids) {
+    my @carts = RHRD::rddb::get_show_carts($ctx, $show_id);
+    if(!defined $carts[0] && defined $carts[1]) {
+      print " showid '" . $show_id . "': $carts[2]\n";
+      $errors++;
+    }
+    if(scalar @carts == 0) {
+      print " showid '" . $show_id . "': log is empty\n";
+      $errors++;
+    }
+
+    my ($group, $status, $errorstring) = RHRD::rddb::get_show_group($ctx, $show_id);
+    unless(defined($group)) {
+      print " showid '" . $show_id . "': has no dropbox assigned\n";
+      $errors++;
+    } else {
+    }
+  }
+  print "\n " . $errors . " errors found\n";
+
+  return $errors;
+}
+
+
+
+sub check_logs
+{
+  my ($ctx) = @_;
+
+  my $errors = 0,
+  print "logs:\n";
+
+  my @logs = RHRD::rddb::list_logs($ctx);
+  if(!defined $logs[0] && defined $logs[1]) {
+    print STDERR "$logs[1]: $logs[2]";
+    return -1;
+  }
+  for my $log (@logs) {
+    my ($log_exists, $status, $errorstring) = RHRD::rddb::check_log_exists($ctx, $log);
+    if(!defined $log_exists) {
+      print STDERR "$status: $errorstring";
+      return -1;
+    }
+    unless($log_exists) {
+      print " log '" . $log . "': does not exist\n";
+      $errors++;
+    }
+
+    (my $log_tab_exists, $status, $errorstring) = RHRD::rddb::check_log_table_exists($ctx, $log);
+    if(!defined $log_tab_exists) {
+      print STDERR "$status: $errorstring";
+      return -1;
+    }
+    if($log_tab_exists) {
+      unless($log_exists) {
+        print " log '" . $log . "': this log shouldn't not exist but there is a table named after it\n";
+        $errors++;
+      }
+    } else {
+      if($log_exists) {
+        print " log '" . $log . "': this log should exist but there is no table named after it\n";
+        $errors++;
+      }
+    }
+
+  }
+  print "\n " . $errors . " errors found\n";
+
+  return $errors;
+}
 
 
 
@@ -102,7 +187,7 @@ sub check_groups__check_musicpool_group
 
   my $errors = 0;
 
-  # TODO: check whether enough evergreens are imported: should be > 3
+  # TODO: check for pool size: should be > 150
 
   my ($nownext, $traffic, $music) = RHRD::rddb::get_group_reports($ctx, $group);
   unless(defined $nownext) {
@@ -131,7 +216,7 @@ sub check_groups__check_jingle_group
 
   my $errors = 0;
 
-  # TODO: check for pool size: should be > 150
+  # TODO: check whether enough evergreens are imported: should be > 3
 
   my ($nownext, $traffic, $music) = RHRD::rddb::get_group_reports($ctx, $group);
   unless(defined $nownext) {
@@ -256,6 +341,7 @@ sub check_groups
 }
 
 
+
 sub check_dropboxes
 {
   my ($ctx) = @_;
@@ -269,69 +355,47 @@ sub check_dropboxes
 }
 
 
-sub check_showids
-{
-  my ($ctx) = @_;
-
-  my $errors = 0,
-  print "showids:\n";
-
-  my @show_ids = RHRD::rddb::list_showids($ctx);
-  if(!defined $show_ids[0] && defined $show_ids[1]) {
-    print STDERR "$show_ids[1]: $show_ids[2]";
-    return -1;
-  }
-  for my $show_id (@show_ids) {
-    my @carts = RHRD::rddb::get_show_carts($ctx, $show_id);
-    if(!defined $carts[0] && defined $carts[1]) {
-      print " showid '" . $show_id . "': $carts[2]\n";
-      $errors++;
-    }
-    if(scalar @carts == 0) {
-      print " showid '" . $show_id . "': has no log assigned or log is empty\n";
-      $errors++;
-    }
-
-    my ($group, $status, $errorstring) = RHRD::rddb::get_show_group($ctx, $show_id);
-    unless(defined($group)) {
-      print " showid '" . $show_id . "': has no dropbox assigned\n";
-      $errors++;
-    } else {
-    }
-  }
-  print "\n " . $errors . " errors found\n";
-
-  return $errors;
-}
-
-
-sub check_logs
-{
-  my ($ctx) = @_;
-
-  my $errors = 0,
-  print "logs:\n";
-
-  print "\n " . $errors . " errors found\n";
-
-  return $errors;
-}
-
 
+my $errors = 0;
 my ($ctx, $status, $errorstring) = RHRD::rddb::init();
 if(defined $ctx) {
-  check_showids($ctx);
-  print "\n";
-  check_logs($ctx);
-  print "\n";
-  check_groups($ctx);
-  print "\n";
-  check_dropboxes($ctx);
-
+  for(;;) {
+    my $ret = check_showids($ctx);
+    if($ret < 0) {
+      $errors = $ret;
+      last;
+    } else { $errors += $ret }
+
+    print "\n";
+
+    $ret = check_logs($ctx);
+    if($ret < 0) {
+      $errors = $ret;
+      last;
+    } else { $errors += $ret }
+
+    print "\n";
+
+    $ret = check_groups($ctx);
+    if($ret < 0) {
+      $errors = $ret;
+      last;
+    } else { $errors += $ret }
+
+    print "\n";
+
+    $ret = check_dropboxes($ctx);
+    if($ret < 0) {
+      $errors = $ret;
+      last;
+    } else { $errors += $ret }
+
+    last;
+  }
   RHRD::rddb::destroy($ctx);
 } else {
   print STDERR "$errorstring\n";
-  $ret = 1;
+  $errors = -1;
 }
 
-exit $ret;
+exit $errors;
diff --git a/utils/rhrd-show b/utils/rhrd-show
index 0aaac01..e7c434e 100755
--- a/utils/rhrd-show
+++ b/utils/rhrd-show
@@ -21,6 +21,7 @@
 #
 
 use strict;
+use lib "../lib/";
 use RHRD::rddb;
 use RHRD::utils;