# -*-perl-*-

# $Id: GenDB.plm,v 1.12 2008/12/16 21:57:58 koczan Exp $

# database interface to the limits tables
# (su_limits, su_lim_active, su_lim_alias)

# $Log: GenDB.plm,v $
# Revision 1.12  2008/12/16 21:57:58  koczan
# Added the last bits for "quotetext" support
#
# Revision 1.11  2004/04/14 15:29:26  thomas
# enable quotetext for GenDB::add
#
# Revision 1.10  2004/03/02 20:37:09  thomas
# Add 'quotetext' support
#
# Revision 1.9  2003/06/12 21:57:33  thomas
# final save before 0.3.11 branch
#
# Revision 1.8  2003/06/04 17:30:53  thomas
# *** empty log message ***
#
# Revision 1.7  2002/10/30 19:41:49  thomas
# - Use gmtime, not ctime when converting to db values
# - Add errors for HostAtom checkDump
#
# Revision 1.6  2002/10/07 19:01:21  thomas
# sushi-0.3.3
#
# Revision 1.5  2002/08/14 17:13:07  thomas
# 0.2.9 release
#
# Revision 1.4  2002/07/30 13:49:50  thomas
# Add --like processing
#
# Revision 1.3  2002/07/23 16:02:27  thomas
# version 0.2.5
#
# Revision 1.2  2002/07/10 14:01:28  thomas
# sushi-0.2.4
#
# Revision 1.1  2002/07/08 15:56:33  thomas
# 0.2.3 release
#
# Revision 1.1.1.1  2002/05/21 14:13:28  thomas
# sushi - backup to disk
#

package Sushi::GenDB;

use strict;
use Time::localtime;
use Data::Dumper;


###############################################################################

sub add {
  my $parms = shift;
  my $opts = shift;
  my $table_def = shift;

  my $col;

  if (!defined $table_def->{table}) {
    $::log->error ("GenDB add called with no DB table defined!\n");
    return 0;
  }

  # Check for all required values
  foreach $col (@{$table_def->{columns}}) {
    if ($col->{add_required} && !defined ($parms->{$col->{col}})) {
      $main::log->error ("GenDB add: Error - required column $col->{col} missing.\n");
      return 0;
    }
    if (defined ($col->{add_default_value}) && !defined ($parms->{$col->{col}})) {
      $parms->{$col->{col}} = $col->{add_default_value};
    }
  }

  if (defined $table_def->{add_check_function}) {
    my $func = eval "\\\&$table_def->{add_check_function}";
    # print Dumper ($func);
    if (!&$func($parms, $opts, $table_def)) {
      return 0;
    }
  }

  # Build insert
  my (@cols, @vals);
  foreach $col (@{$table_def->{columns}}) {
    if (defined $parms->{$col->{col}}) {
      push @cols, $col->{col};
      if ($col->{type} eq 'text' ||
	  $col->{type} eq 'boolean' ||
	  $col->{type} eq 'quotetext') {
	push @vals,  $::sudbh->quote ($parms->{$col->{col}});
      } elsif ($col->{type} eq 'bigint' ||
	       $col->{type} eq 'integer'||
	       $col->{type} eq 'mfloat' ||
	       $col->{type} eq 'kint' ) {
	push @vals, $parms->{$col->{col}};
      } elsif ($col->{type} eq 'datetime') {
	push @vals, $::sudbh->quote (Sushi::Misc::secs2utc ($parms->{$col->{col}}));
      } else {
	$::log->error ("GenDB add: Don't know about data type $col->{type}!\n");
	return 0;
      }
    }
  }

  my $cmd = "insert into  $table_def->{table} (" . join (', ', @cols) . ") values (". join (', ', @vals) . ")";
  $main::log->log (4, "GenDB addrow: $cmd\n");
  my $rows = $::sudbh->do ($cmd);
  $::sudbh->commit;
  return $rows;

}

###############################################################################
sub del {
  my $parms = shift;
  my $opts = shift;
  my $table_def = shift;

  my $col;

  if (!defined $table_def->{table}) {
    $::log->error ("GenDB add called with no DB table defined!\n");
    return 0;
  }

  # Check for all required values
  foreach $col (@{$table_def->{columns}}) {
    if ($col->{del_required} && !defined ($parms->{$col->{col}})) {
      $main::log->error ("GenDB del: Error - required column $col->{col} missing.\n");
      return 0;
    }
  }

  # Build column lists
  my (@pairs);
  foreach $col (@{$table_def->{columns}}) {
    if (defined $parms->{$col->{col}}) {
      my $pair = "$col->{col} = ";
      if ($col->{type} eq 'text' ||
          $col->{type} eq 'boolean' ||
          $col->{type} eq 'quotetext') {
	$pair .= $::sudbh->quote ($parms->{$col->{col}});
      } elsif ($col->{type} eq 'bigint' ||
	       $col->{type} eq 'integer' ||
	       $col->{type} eq 'kint' ) {
	$pair .= $parms->{$col->{col}};
      } elsif ($col->{type} eq 'datetime') {
	$pair .= $::sudbh->quote (Sushi::Misc::secs2utc ($parms->{$col->{col}}));
      } else {
	$::log->error ("GenDB del: Don't know about data type $col->{type}!\n");
	return 0;
      }
      push @pairs, $pair;
    }
  }

  my $cmd = "delete from $table_def->{table} ";
  @pairs and $cmd .= "where " . join (' and ', @pairs);

  $main::log->log (4, "GenDB del: $cmd\n");
  my $rows = $::sudbh->do ($cmd);
  $::sudbh->commit;
  if ($opts->{print_results}) {
    if ($rows == 1) {
      print "($rows row deleted)\n";
    } else {
      print "($rows rows deleted)\n";
    }
  }
  return $rows;
}

###############################################################################

sub update {
  my $where = shift;
  my $opts = shift;
  my $set = shift;
  my $table_def = shift;

  my ($key, $col, @query, @set);

  # If opts->{like} was specified, change the operator for any parameters that
  # have an embedded '%' to 'like'
  if (defined $opts->{like}) {
    foreach my $key (keys %$where) {
      if ($where->{$key} =~ /\%/ &&
	  !defined ($opts->{opers}->{$where->{$key}})) {
	$opts->{opers}->{$key} = 'like';
      }
    }
  }

  foreach $col (@{$table_def->{columns}}) {
    # Where clause
    if (defined $where->{$col->{col}}) {
      my ($expr, $oper) = (undef, undef);
      if ($col->{type} eq 'text'      ||
	  $col->{type} eq 'boolean'   ||
          $col->{type} eq 'quotetext') {
	$expr = $::sudbh->quote ($where->{$col->{col}});
      } elsif ($col->{type} eq 'integer' ||
	       $col->{type} eq 'bigint'  ||
	       $col->{type} eq 'mfloat'  ||
	       $col->{type} eq 'kint' ) {
	$expr = $where->{$col->{col}};
      } elsif ($col->{type} eq 'datetime') {
	$expr = $::sudbh->quote (Sushi::Misc::secs2utc ($where->{$col->{col}}));
      } else {
	print STDERR "GenDB::update: Don't know about data type $col->{type}!!\n";
      }
      if (defined $opts->{opers}->{$col->{col}}) {
	$oper = $opts->{opers}->{$col->{col}};
      } else {
	$oper = '=';
      }
      defined $expr and push @query, "$col->{col} $oper $expr";
    }
    # Columns and values
    if (defined $set->{$col->{col}}) {
      my ($expr, $oper) = (undef, undef);
      if ($col->{type} eq 'text'      ||
	  $col->{type} eq 'boolean'   ||
          $col->{type} eq 'quotetext') {
	$expr = $::sudbh->quote ($set->{$col->{col}});
      } elsif ($col->{type} eq 'integer' ||
	       $col->{type} eq 'bigint'  ||
	       $col->{type} eq 'mfloat'  ||
	       $col->{type} eq 'kint' ) {
	$expr = $set->{$col->{col}};
      } elsif ($col->{type} eq 'datetime') {
	$expr = $::sudbh->quote (Sushi::Misc::secs2utc ($set->{$col->{col}}));
      } else {
	print STDERR "Don't know about data type $col->{type}!!\n";
      }
      defined $expr and push @set, "$col->{col} = $expr";
    }
  }
  @set or return 0;
  my $cmd = "update $table_def->{table} set " . join ( ", ", @set);
  @query and $cmd .= " where " . join (" and ", @query);

  $main::log->log (4, "GenDB::update: $cmd\n");
  my $rows = $::sudbh->do ($cmd);
  $::sudbh->commit;
  return $rows;
}

###############################################################################
# Returns an array of limits in the db that match the passed list.

sub fetch {
  my $parms = shift;
  my $opts = shift;
  my $table_def = shift;

  my $col;
  my @order_by = ();
  my @query = ();

  my $list = [];


  # If opts->{like} was specified, change the operator for any parameters that
  # have an embedded '%' to 'like'
  if (defined $opts->{like}) {
    foreach my $key (keys %$parms) {
      if ($parms->{$key} =~ /\%/ &&
	  !defined ($opts->{opers}->{$parms->{$key}})) {
	$opts->{opers}->{$key} = 'like';
      }
    }
  }

  foreach $col (@{$table_def->{columns}}) {
    if (defined $parms->{$col->{col}}) {
      my ($expr, $oper) = (undef, undef);
      if ($col->{type} eq 'text'      ||
	  $col->{type} eq 'boolean'   ||
          $col->{type} eq 'quotetext') {
	$expr = $::sudbh->quote ($parms->{$col->{col}});
      } elsif ($col->{type} eq 'integer' ||
	       $col->{type} eq 'bigint'  ||
	       $col->{type} eq 'kint' ) {
	$expr = $parms->{$col->{col}};
      } elsif ($col->{type} eq 'datetime') {
	$expr = $::sudbh->quote (Sushi::Misc::secs2utc ($parms->{$col->{col}}));
      } else {
	print STDERR "Don't know about data type $col->{type}!!\n";
      }
      if (defined $opts->{opers}->{$col->{col}}) {
	$oper = $opts->{opers}->{$col->{col}};
      } else {
	$oper = '=';
      }
      defined $expr and push @query, "$col->{col} $oper $expr";
    }
    if ($col->{order_by}) {
      push @order_by, $col->{col};
    }
  }

  my $query = "select * from $table_def->{table}";

  @query and $query .= " where " . join (" and ", @query);
  @order_by and $query .= " order by " . join (", ", @order_by);

  $main::log->log (4, "GenDB::fetch: $query\n");
  my $sth = $::sudbh->prepare ($query) or return $list;
  $sth->execute or return $list;
  while (defined (my $row = $sth->fetchrow_hashref)) {
    foreach $col (@{$table_def->{columns}}) {
      if ($col->{type} eq 'datetime' && defined ($row->{$col->{col}})) {
	$row->{$col->{col}} = &Sushi::Misc::date2secs ($row->{$col->{col}});
      }
    }
    push @$list, $row;
  }
  return $list;
}

###############################################################################

sub display {
  my $parms = shift;
  my $opts = shift;
  my $table_def = shift;

  my $rows = 0;
  my $row;

  # Print the headings if needed
  Sushi::GenDB::displayHeader ($opts, $table_def);

  # Get the rows and print them
  foreach $row (@{Sushi::GenDB::fetch ($parms, $opts, $table_def)}) {
    Sushi::GenDB::displayRow ($opts, $table_def, $row);
    $rows++;
  }

  Sushi::GenDB::displayTrailer ($opts, $table_def, $rows);

  return $rows;
}

###############################################################################

sub displayHeader {
  my $opts = shift;
  my $table_def = shift;

  my $line;
  my $rows = 0;
  my ($row, $col, $fmt, $dashes);

  $dashes = "";
  if ($opts->{print_headings}) {
    # Count number of heading lines
    my $nheads = 1;
    foreach $col (@{$table_def->{columns}}) {
      if ((defined $col->{list_heading}) &&
	  !((defined $col->{only_if_opts}) &&
	    !$opts->{$col->{only_if_opts}})) {
	(length $dashes) and $dashes .= "  ";
	$dashes .= "-" x $col->{list_width};
	@{$col->{list_heading}} > $nheads and $nheads = @{$col->{list_heading}}
      }
    }
    for (my $i=0; $i<$nheads; $i++) {
      $line = "";
      foreach $col (@{$table_def->{columns}}) {
	if ((defined $col->{list_heading}) &&
	    !((defined $col->{only_if_opts}) &&
	      !$opts->{$col->{only_if_opts}})) {
	  length($line) and $line .= "  ";
	  $fmt = sprintf "%%-%ds", $col->{list_width};
	  $line .= sprintf $fmt, ref ($col->{list_heading}->[$i]) ? " " : $col->{list_heading}->[$i];
	}
      }
      $line .= "\n";
      defined ($table_def->{indent}) and $line = " " x $table_def->{indent} . $line;
      Sushi::Misc::printToFile ($opts, $line);
    }
    $dashes .= "\n";
    defined ($table_def->{indent}) and $dashes = " " x $table_def->{indent} . $dashes;
    Sushi::Misc::printToFile ($opts, $dashes);
  }
}

###############################################################################

sub displayRow {
  my $opts = shift;
  my $table_def = shift;
  my $row = shift;

  my ($col, $fmt, $dashes, $string);

  # Get the rows and print them
  my $line = "";
  foreach $col (@{$table_def->{columns}}) {
    if ((defined $col->{list_heading}) &&
	!((defined $col->{only_if_opts}) &&
	  !$opts->{$col->{only_if_opts}})) {
      length($line) and $line .= "  ";
      if ($col->{type} eq "text") {
	$fmt = sprintf "%%-%ds", $col->{list_width};
	$line .= sprintf $fmt, $row->{$col->{col}};
      } elsif ($col->{type} eq "quotetext") {
	$fmt = sprintf "%%-%ds", $col->{list_width};
	$line .= sprintf $fmt, '"' . $row->{$col->{col}} . '"';
      } elsif ($col->{type} eq "mfloat") {
	$fmt = sprintf "%%%d.2f", $col->{list_width}-3;
	my $val= sprintf $fmt, $row->{$col->{col}}/(1024*1024);
	$val =~ s/^\s*//;
	$val =~ s/\s*$//;
	$fmt = sprintf "%%%ds", $col->{list_width};
	$line .= sprintf $fmt, $val;
      } elsif ($col->{type} eq "kfloat") {
	$fmt = sprintf "%%%d.2f", $col->{list_width}-3;
	my $val= sprintf $fmt, $row->{$col->{col}}/1024;
	$val =~ s/^\s*//;
	$val =~ s/\s*$//;
	$fmt = sprintf "%%%ds", $col->{list_width};
	$line .= sprintf $fmt, $val;
      } elsif ($col->{type} eq "float") {
	$fmt = sprintf "%%%d.2f", $col->{list_width}-3;
	my $val= sprintf $fmt, $row->{$col->{col}};
	$val =~ s/^\s*//;
	$val =~ s/\s*$//;
	$fmt = sprintf "%%%ds", $col->{list_width};
	$line .= sprintf $fmt, $val;
      } elsif ($col->{type} eq "integer" ) {
	$fmt = sprintf "%%%dd", $col->{list_width};
	$line .= sprintf $fmt, $row->{$col->{col}};
      } elsif ($col->{type} eq "kint" ) {
	$fmt = sprintf "%%%dd", $col->{list_width};
	$line .= sprintf $fmt, $row->{$col->{col}}/1024;
      } elsif ($col->{type} eq "boolean") {
	$fmt = sprintf "%%-%ds", $col->{list_width};
	$line .= sprintf $fmt, ($row->{$col->{col}}?'True':'False');
      } elsif ($col->{type} eq "datetime" && $col->{list_width} == 28) {
	$line .= sprintf "%15s (%10d)",
	  Sushi::Misc::shortTime ($row->{$col->{col}}), $row->{$col->{col}};
      } elsif ($col->{type} eq "datetime" && $col->{list_width} == 15) {
	$line .= sprintf "%15s", Sushi::Misc::shortTime ($row->{$col->{col}});
      } else {
	print STDERR "Don't know about data type $col->{type}!!\n";
      }
    }
  }
  $line .= "\n";
  defined ($table_def->{indent}) and $line = " " x $table_def->{indent} . $line;
  Sushi::Misc::printToFile ($opts, $line);
}

###############################################################################

sub displayTrailer {
  my $opts = shift;
  my $table_def = shift;
  my $rows = shift;

  if ($opts->{print_headings}) {
    if ($rows==1) {
      Sushi::Misc::printToFile ($opts, "\n($rows row)\n\n");
    } else {
      Sushi::Misc::printToFile ($opts, "\n($rows rows)\n\n");
    }
  }
}

###############################################################################

1;
