#!/usr/bin/perl
# Created 30 Jul 2001 by Rob Hudson <rob@euglug.net>
#
# TODO: Work on command line arguments
#     : Database locking?
#
# Usage: rendb perlexpr database table [fields]
$|++;

# Check for args
my $op     = shift @ARGV || die "Usage: rendb perlexpr database table";
my $db     = shift @ARGV || die "No database specified: $!\n";
my $tbl    = shift @ARGV || die "No table specified: $!\n";
my @fields = @ARGV;

# load in packages
use DBI;

# connect to database
my $dbh = DBI->connect ("DBI:mysql:$db", 'root', '') || die "Failed DBI connect(): $!\n";

# set up query and execute
my $query = "SELECT ";
if (@fields <= 0) { 
  $query .= "*"; 
} else { 
  $query .= join(',', @fields); 
}
$query .= " FROM $tbl";

my $sth = $dbh->prepare($query);
my $rc = $sth->execute();

# get field names if none specified
if (@fields <= 0) {
  @fields = @{ $sth->{NAME} };
}

# loop thru results
while (my $hrow = $sth->fetchrow_hashref) {

  my $set   = '';
  my $where = '';

  foreach $field (@fields) {

    $_ = $hrow->{$field};
    eval $op;

    # add slashes to data
    $hrow->{$field} =~ s/'/\\'/g;
    $hrow->{$field} =~ s/"/\\"/g;
    s/'/\\'/g;
    s/"/\\"/g;

    # build query pieces
    $where .= "$field='$hrow->{$field}' AND ";
    if ($hrow->{$field} ne $_) {
      $set .= "$field='$_',";
    }

  }
  $set =~ s/,$//;
  $where =~ s/AND $//;

  unless ($set =~ /^\s*$/ ) {
    my $row_query = "UPDATE $tbl SET $set WHERE $where";
    print $row_query . "\n";

    unless ($rv = $dbh->do($row_query)) {
      print "\n==> Unable to perform the following query:\n\t$row_query\n" . $dbh->errstr . "\n";
    }
  }
  
}

undef $sth;
$dbh->disconnect;

0;

=head1 NAME

B<rendb> - use a perl expression to rename the contents of fields in a MySQL table

=head1 SYNOPSIS

B<rendb> perl_expression database table [fields]

=head1 DESCRIPTION

B<rendb> uses a perl expression to rename multiple files.  For more information about
perl expressions, consult the perl man pages, F<Learning Perl> by Randal
Schwartz, F<Programming Perl> by Larry Wall, or your local friendly
Perl programmer.

For example, to search and replace one keyword to another in all fields in a table:

  rendb 's/foo/bar/;' database table

Similarly, to limit the search and replacement to only 2 fields:

  rendb 's/foo/bar;' database table field1 field2

=head1 AUTHOR

Rob Hudson <rob@euglug.net>

=head1 DATE

Jul 30, 2001

=cut