#!/usr/bin/perl

# This is a skeleton file for a Perl script that uses MySQL.
# License goes here.
# This skeleton file is free.  You can do whatever you want with it.
# It was originally created by Baron Schwartz (baron at xaprb).

use strict;
use warnings FATAL => 'all';

use DBI();
use English qw(-no_match_vars);
use Getopt::Long;
use Term::ReadKey;

# Make the file's Perl version the same as its CVS revision number.
our $VERSION = sprintf "%d.%03d", q$Revision$ =~ /(\d+)/g;

# ############################################################################
# Get configuration information.
# ############################################################################

# Define cmdline args; each is spec, desc.  Add more hash entries as needed.
my %opt_spec = (
   d => { s => 'database|d=s',  d => 'Database' },
   h => { s => 'host|h=s',      d => 'Database server hostname' },
   l => { s => 'help',          d => 'Show this help message' },
   o => { s => 'port|P=i',      d => 'Database server port' },
   p => { s => 'pass|p=s',      d => 'Database password' },
   u => { s => 'user|u=s',      d => 'Database username' },
);

# Define the order cmdline opts will appear in help output.  Add any extra ones
# defined above.
my @opt_keys = qw( h d o u p l );

# This is the container for the command-line options' values to be stored in
# after processing.  Initial values are defaults.
my %opts = (
   d => '',
   h => '',
   o => 0,
   p => 0,
   u => '',
);

Getopt::Long::Configure('no_ignore_case', 'bundling');
GetOptions( map { $opt_spec{$_}->{'s'} => \$opts{$_} }  @opt_keys );

# Choose one of the following...
# If a filename or other argument(s) is required after the other arguments:
# if ( $opts{'l'} || !@ARGV ) {
# If only the arguments defined above in the hash are required:
# if ( $opts{'l'} ) {
if ( $opts{'l'} ) {
   print "Usage: $PROGRAM_NAME <options> batch-file\n\n  Options:\n\n";
   foreach my $key ( @opt_keys ) {
      my ( $long, $short ) = $opt_spec{$key}->{'s'} =~ m/^(\w+)(?:\|([^=]*))?/;
      $long  = "--$long" . ( $short ? ',' : '' );
      $short = $short ? " -$short" : '';
      printf("  %-13s %-4s %s\n", $long, $short, $opt_spec{$key}->{'d'});
   }
   print <<USAGE;

$PROGRAM_NAME does something or other.

If possible, database options are read from your .my.cnf file.
For more details, please read the documentation:

   perldoc $PROGRAM_NAME

USAGE
   exit(1);
}

# This will end up containing what's needed to connect to MySQL.
my $conn = {
   h  => $opts{'h'},
   db => $opts{'d'},
   u  => $opts{'u'},
   p  => $opts{'p'},
   o  => $opts{'o'},
};

if ( grep { !$conn->{$_} } keys %$conn ) {
   # Try to use the user's .my.cnf file.
   eval {
      my $homedir = $ENV{HOME} || $ENV{HOMEPATH} || $ENV{USERPROFILE};
      open my $conf_file, "<", "$homedir/.my.cnf" or die $OS_ERROR;
      while ( my $line = <$conf_file> ) {
         next if $line =~ m/^#/;
         my ( $key, $val ) = split( /=/, $line );
         next unless defined $val;
         chomp $val;
         if ( $key eq 'host' )     { $conn->{'h'}  ||= $val; }
         if ( $key eq 'user' )     { $conn->{'u'}  ||= $val; }
         if ( $key =~ m/^pass/ )   { $conn->{'p'}  ||= $val; }
         if ( $key eq 'database' ) { $conn->{'db'} ||= $val; }
         if ( $key eq 'port' )     { $conn->{'o'}  ||= $val; }
      }
      close $conf_file;
   };
   if ( $EVAL_ERROR && $EVAL_ERROR !~ m/No such file/ ) {
      print "I tried to read your .my.cnf file, but got '$EVAL_ERROR'\n";
   }
}

# Fill in defaults for some things
$conn->{'o'} ||= 3306;
$conn->{'h'} ||= 'localhost';
$conn->{'u'} ||= getlogin() || getpwuid($UID);
$conn->{'p'} ||= '';

my %prompts = (
   o  => "\nPort number: ",
   h  => "\nDatabase host: ",
   u  => "\nDatabase user: ",
   p  => "\nDatabase password: ",
   db => "\nDatabase: ",
);

# If anything remains, prompt the terminal
while ( my ( $thing ) = grep { !$conn->{$_} } keys %$conn ) {
   $conn->{$thing} = prompt($prompts{$thing}, $thing eq 'p');
}

# ############################################################################
# Get ready to do the main work.
# ############################################################################

# Connect to the database
my $dbh = DBI->connect(
   "DBI:mysql:database=$conn->{db};host=$conn->{h};port=$conn->{o}",
   $conn->{'u'}, $conn->{'p'}, { AutoCommit => 1, RaiseError => 1, PrintError => 0 } )
   or die("Can't connect to DB: $!");