#!/usr/bin/env perl

# $Id: do_sql_command.pl,v 1.21 2003/01/29 23:27:03 mokrejs Exp $

=head1 NAME 

do_sql_command.pl - a perl program to execute user requested SQL commands per each database and table

=head1 SYNOPSIS

do_sql_command.pl --options --arguments

=head1 DESCRIPTION

This program allows you to execute same command for any database.table
specified on a command-line or listed in a file containing a list of
databasenames. You can specify multiple commands which will be executed
for each table in each database. You can omit specified tables or databases
to work on. If you do not specify any --db option, a list of databases
will be fetched from database host. If you do not specify any --table ,
list of tables will be fetched for each database from mysql host.

The strong advantage of this program is to selectively include or exclude
some databases or tables which you do or do not want to modify. Hence
we offer --omit-(table|database) and --table and --db arguments.
The program is clever enough to figure out what tables or databases
are present on the system and operate on all of them, if no --db
or --table was specified.

Note that this program will take the advantage of ~/.my.cnf file, so you
do not have to specify username, password, hostname, port on a commandline.

Thus, this script you can use to:
 do command for 2 tables in all databases except databases foo and bar
 do command for all tables in all databases except databases foo and bar
 do command for all tables in databases foo and bar
 do command for all tables except table baz in databases foo and bar
 do command for all tables except table baz in all databases

See example below.


=head1 OPTIONS

=over 1

=item B< --dbs-in-file=$file>

Filename (might be specified with full path), which contains line by
line list of databases, on which this script should perform (un)compression.
If --dbs-in-file=- is pecified, list of databases is read from STDIN.


=item B< --host=$hostname>              [default: 127.0.0.1]

Hostname of the mysql host. Note that this program will take the advantage of ~/.my.cnf file.


=item B< --omit-database>

Enumerate a list of databases we should not modify. 


=item B< --omit-table>

Enumerate a list of tables we should not modify. This option is used
to selectively exclude tables, which were specified using --table 
option or, more importantly, if --table was left out, the algorithm would
perform on all tables present in a given database. --omit-table prevents
this program from modifying such tables.


=item B< --cmd=some sql command with $var # comment $var>

Specify any SQL command you wish. After the `#' hash mark you can specify
any comments, which might be logged in mysqld logfile along with your
query and also will be visible in crash output from mysqld.
Of course, your version of mysql must support them. I think it is since
4.0.x.

What are those variables? They are internal variables in this perl program.
If $user/$host/$port/$db/$table is present there, it will be replaced
by do_sql_command.pl before it get's sent to mysqld. As you execute this program
most probably from shell, you should use double quotes and protect the `$' sign with backslash.
For example:

--cmd="some sql command with \$var # comment \$var"


=item B< --password=$password>          [default:  ]

Password to be used during sql connection. Note that this program will take the advantage of ~/.my.cnf file.


=item B< --port=$port>          [default: 3306]

Port number where mysqld is listening to our requests.


=item B< --table=$tablename>

Enumerate a list of tables, for which we should perform specified SQL command. If no --table
option is specified, the script will perform such command on all
tables present in a given database. You can combine --table with
--omit-table options. The script will ignore those tables specified via
--omit-table although they were also specified using --table.


=item B< --user=$user>          [default:  ]

Username to be used during sql connection. Note that this program will take the advantage of ~/.my.cnf file.


=item B< --verbose>

Verbose output. Specifying this option more times increases verbosity.
Maximum is currently 3, although you may specify it more times.


=item B< --count=$number>	[default: 1]

How many time we should repeat every SQL command. Specify -1 if you want never ending loop.


=head1 EXAMPLES

do_sql_command.pl --db=db1 --db=db2 --table=table1 --table=table2 --cmd="show create table \$table"

The command above will execute `SHOW CREATE TABLE $tablename' for table1 and table2 in databases db1 and db2.

do_sql_command.pl --db=db1 --db=db2 --omit-table=table1 --cmd="show create table \$table"

Execute `SHOW CREATE TABLE $tablename' for every table except table1 in databases db1 and db2.

do_sql_command.pl --dbs-in-file=- --omit-table=table1 --cmd="show create table \$table #User: \$user Db: \$db Table: \$table" --no-show-sql-cmds < list.txt

Execute same command, but read a list of databases from STDIN and do not show the SQL command we executed.
You may then take advantage of this sql command being logged with the appended comment text.


=back

=head1 AUTHOR

Martin Mokrejs <mmokrejs@natur.cuni.cz> http://www.natur.cuni.cz/~mmokrejs/mysql-tools

=cut



use DBI;
use Getopt::Long;

my $db;
my $dblist;
my @db;
my $table;
my @table;
my @existingtables;
my @cmds;
my $cmd;
my $Cmd;
my $dbh;
my @omittable;
my @omitdatabase;
my $user="";
my $password="";
my $host="";
my $port="3306";
my $verbose=0;
my $noshowsqlcmds=0;
my $dbh;
my $count=1;

$| = 1;

GetOptions ('db=s' => \@db, 'dblist=s' => \$dblist, 'table=s' => \@table, 'host=s' => \$host, 'user=s' => \$user, 'port=s' => \$port, 'cmd=s' => \@cmds, 'verbose+' => \$verbose, 'omit-table=s' => \@omittable, 'omit-database=s' => \@omitdatabase, 'password=s' => \$password, 'no-show-sql-cmds!' => \$noshowsqlcmds, 'count=i' => \$count );

if ( !@db || ( $db[0] eq "" ) and !$dblist ) {
  print "--db not used, connecting to database mysql at $user\@$host:$port.\n" if ( $verbose >= 2);
  @db = GetDatabases("mysql", $host, $port, $user);
}

if ( scalar($dblist) ) {
  if ( "$dblist" ne "-" ) {
    open (DB,"$dblist") or die "Cannot open file with list of databases: $!\n";
  } elsif ( "$dblist" eq "-" ) {
    open(DB,"<&STDIN") or die "Cannot dup STDIN: $!\n";
  }
  while ( <DB> ) {
    chomp($_);
    push(@db,$_);
    print "using $_\n" if ( $verbose >= 1 );
  }
}
close(DB);

my @cmdsbackup = @cmds;
my @tablebackup = @table;
my $countbackup = $count;

if ( scalar(@omitdatabase) ) {
  @db = RemoveOmittables(\@db, \@omitdatabase);
}

if (ConnectDb("$initdb","$user","$password","$host","$port")) {
  die "Error: Cannot get initial connection to mysql server $host:$port to $initdb database.\n";
}

foreach $db ( @db ) {
  print "DB IS $db\n" if ( $verbose >= 1 );
  @table = @tablebackup;
  if (UseDb("$user","$host","$port","$db")) {
    warn "Error: Cannot use database $db on $host:$port. Skipping.\n";
    next;
  }
  if ( !scalar(@table) ) {
    @table = GetTables( "$db", "$db" );
    @existingtables = @table;
  } else {
    @existingtables = GetTables( "$db", "$db" );
  }
  if ( scalar(@omittable) ) {
    @table = RemoveOmittables(\@table, \@omittable);
  }
  foreach $table ( @table ) {
    print "TABLE IS $table\n" if ( $verbose >= 1 );
    if ( !ExactMatch($table,@existingtables) ) {
      warn "Warning: $db.$table does not exist, skipping command execution\n";
      next;
    }
    $count = $countbackup;
    while ( $count != 0 ) {
      $count=$count-1 if not ($count == -1);
      $cmd = "";
      $Cmd = "";
      @Cmd = ();
      @cmds = @cmdsbackup;
      foreach $cmd ( @cmds ) {
        $Cmd = $cmd;
        $Cmd =~ s/^\ *//g;
        $Cmd =~ s/\ *$//g;
        # evaluate $table
        $Cmd =~ s/\$table/$table/ge;
        # evaluate $db
	$Cmd =~ s/\$db/$db/ge;
        # evaluate $host
        $Cmd =~ s/\$host/$host/ge;
        # evaluate user
	$Cmd =~ s/\$user/$user/ge;
        # evaluate port
	$Cmd =~ s/\$port/$port/ge;
        $_ = $Cmd;
        my $text = "";
        @Cmd = ();
        while ( /("[^"]+"|\S+)/g ) {
          $text = $1;
          $text =~ s/"//g;
          push(@Cmd,$text);
        }
        $cmd = "";
        # restore the SQL commandline in variable
        foreach ( @Cmd ) {
          $cmd = $cmd . " " . "$_";
        }
        $cmd =~ s/^\ *//g;
        $statement = $cmd;
        print "[$user\@$host:$port $db.$table]: $cmd" if not ( $noshowsqlcmds == 1 );
        $sth = $dbh->prepare($statement);
        if ( $dbh->errstr ) {
          print "... failed\n";
          warn "Error: [$user\@$host:$port $db.$table]: (prepare failed): " . $dbh->errstr . "\n";
          next;
        }

        $sth->execute();
        if ( $dbh->errstr ) {
          print "... failed\n" if not ( $noshowsqlcmds == 1 );
          warn "Error: [$user\@$host:$port $db.$table]: (execute failed): " . $dbh->errstr . "\n";
          next;
        }

        if ( $cmd =~ /^drop\ /i ) {
          $sth->finish();
          if ( $dbh->errstr ) {
            print "... failed\n" if not ( $noshowsqlcmds == 1 );
            warn "Error: [$user\@$host:$port $db.$table]: (finish failed): " . $dbh->errstr . "\n";
          } else {
            print "... succeeded.\n" if not ( $noshowsqlcmds == 1 );
          }
        } else {
          my @row_ary = ();
          while (1) {
            @row_ary = $sth->fetchrow_array;
            if ( $dbh->errstr ) {
              print "... succeeded?\n" if not ( $noshowsqlcmds == 1 );
              warn "Error: [$user\@$host:$port $db.$table]: (fetchrow_array failed): " . $dbh->errstr . "\n";
            }
            last if !scalar @row_ary;
            print "... succeeded.\n" if not ( $noshowsqlcmds == 1 );
            print "[$user\@$host:$port $db.$table]: @row_ary\n";
          }
        }

        $statement = "";
        $cmd = "";
        $Cmd = "";
      }
      $cmd = "";
      $Cmd = "";
   } # end of while loop

  }
  $statement = "";
  $cmd = "";
  $Cmd = "";
  @existingtables = ();
  @table = ();
}
$dbh->disconnect;


sub ConnectDb {
  my ($db,$user,$password,$host,$port) = @_;
  $dbh = DBI->connect("DBI:mysql:database=$db;mysql_read_default_file=$ENV{HOME}/.my.cnf;host=$host;port=$port","$user","$password",{ RaiseError => 0, AutoCommit => 1, PrintError => 0 });
  if ( $DBI::errstr ) {
    warn "Error: [$user\@$host:$port]: $DBI::errstr\n";
    return 1;
  } else {
    print "Info: Connected to MySQL server $user\@$host:$port. The rest of setting read from $ENV{HOME}/.my.cnf.\n";
    return 0;
  }
}


sub UseDb {
  my ($user,$host,$port,$db) = @_;
  # we cannot lock READ-ONLY COMPRESSED tables in write mode
  my $statement = "use $db";
  if ( $dryrun == 0 ) {
    $| = 1;
    print "Info: [$user\@$host:$port $db]: Executing $statement ..." if ($verbose >= 2);
    $dbh->do($statement);
    if ( $DBI::errstr ) {
       print " failed.\n" if ($verbose >= 2);
       warn "Error: [$user\@$host:$port $db]: $DBI::errstr\n";
       return 255;
    } else {
       print " succeeded.\n" if ($verbose >= 2);
       print "Info: Using database $db at $user\@$host:$port\n";
       return 0;
    }
  } else {
    print "DRY-RUN: $statement skipped.\n";
    return 0;
  }
}


sub GetTables {
  my ($dbb, $dbb1) = @_ ;
  my @tables = map { $_ =~ s/.*\.//; $_ } $dbh->tables();
    foreach ( @tables ) {
      print "HERE we are in GetTables() with $dbb" . ".Table: $_\n" if ( $verbose >= 3 );
    }
return @tables;
}

sub RemoveOmittables {
  my ($table, $omittable) = @_;
  my %unique;
  my @unique;

  foreach ( @$table ) {
    chomp($_);
    $unique{$_}++;
  }
  foreach ( @$omittable ) {
    chomp($_);
    $unique{$_}++;
    $unique{$_}++;
  }
  foreach ( sort keys %unique ) {
    push (@unique, $_) if ( $unique{$_} == 1 );
  }
  @unique = sort @unique;
  return @unique;
}


sub GetDatabases {
  my ($db, $hostname, $port, $user) = @_;
  $dbh = DBI->connect("DBI:mysql:database=$db;mysql_read_default_file=$ENV{HOME}/.my.cnf;host=$hostname;port=$port","$user","$password") || die "\nFATAL ERROR: Unable to open database $db on $hostname: $DBI::errstr";
  $statement = "show databases;";
  $sth = $dbh->prepare($statement);
  if ( $dbh->errstr ) {
     print STDERR "Error: prepare failed: $dbh->errstr\n";
     $dbh->disconnect;
     die "Disconnected.\n";
  }
  $sth->execute;
  if ( $dbh->errstr ) {
    print STDERR "Cannot list databases: " . $dbh->errstr . "\n";
    $dbh->disconnect;
    die "Disconnected.\n";
  }
  @row_ary = ();
  my @db;
  while (1) {
    @row_ary = $sth->fetchrow_array;
    last if !scalar @row_ary;
    push(@db,$row_ary[0]);
  }
  $dbh->disconnect;
  return @db;
}

sub RemoveOmitDatabases {
  my ($db, $omitdatabase) = @_;
  my %unique;
  my @unique;

  foreach ( @$db ) {
    chomp($_);
    $unique{$_}++;
  }
  foreach ( @$omitdatabase ) {
    chomp($_);
    $unique{$_}++;
    $unique{$_}++;
  }
  foreach ( sort keys %unique ) {
    push (@unique, $_) if ( $unique{$_} == 1 );
  }
  @unique = sort @unique;
  return @unique;
}

# because `grep /string/, @array' matches partial strings, we need exact match here
sub ExactMatch {
  my ($string, @array) = @_;
  my $match = 0;
  foreach my $entry ( @array ) {
    $match = $match + 1 if ( $entry eq $string );
  }
  print "String $string matched $match times.\n" if ( $verbose >=3 );
  return $match;
}