#!/usr/bin/env perl
#
# sqlexec2csv - execute SQL and return the result as CSV
#
# $Id$

# see also ./dbinstkeys

use warnings;
use strict;

use Getopt::Std;
use File::Spec::Functions 'rel2abs';
use DBI;
use Text::CSV;

my %opt;
getopts( 'hc:d:n:p:t:u:v', \%opt );

my $driver = $opt{d} // 'SQLite';
my $dsn    = $opt{n} // '';
my $sep    = $opt{t} // ',';
my $catalog = $opt{c};
my $verbose = $opt{v} // 0;

my @drivers = DBI->available_drivers();
push( @drivers, 'MDB' )
 if grep { $_ eq 'ODBC' } @drivers;    # guessing

$opt{h} and HELP_MESSAGE();

grep { $driver eq $_ } @drivers
 or die "invalid value for -d option, must be one of ",
 join( ' ', @drivers ), "\n  see -h for help\n";

my $user     = $opt{u} // '';
my $password = $opt{p} // '';

my $dsn_prefix = '';

if ( $driver eq 'MDB' )
{
  # not really a driver, mostly a shorthand
  $driver     = 'ODBC';
  $dsn_prefix = 'Driver=Microsoft Access Driver (*.mdb);Dbq=';
}

my $csvh = new Text::CSV(
  { quote_space => 0, eol => $/, sep_char => ( $opt{t} // ',' ) } )
 or usage("the CSV composer doesn't work (not installed?)");

process_dsn($dsn);

exit(0);

#--- auxiliaries ---#
#

sub HELP_MESSAGE
{
  my $driver_names = join( ', ', sort @drivers );

  print <<ZZ;

Usage: $0 [-d driver] [-n dsn] [-t sep] [-v] [sqlfile [sqlfile [...]]

to execute SQL statements found in the given files
or on standard input and print the results as headered CSV tables.

Note: the contents of each file (or standard input)
is taken to be a single statement.

Options:

  -d driver

    Which DBI driver to use; SQLite is the default.
    Must be one of $driver_names.
    Please note that only SQLite, CSV and MDB have been tested
    and only once, and that CSV does not correctly report primary keys.

  -n dsn

    The DSN to use.  Required.

  -t sep

    The CSV column separator to use; a colon by default.

  -c catalog

    The catalog to use; implemented by executing 'USE catalog'
    prior to the SQL supplied on input.

  -v

    Issue some progress messages; useful for debugging.

ZZ
  exit(0);
}

sub warn_v
{
  warn @_, "\n" if $verbose;
}

sub process_dsn
{
  my ($dsn) = @_;

  if ( -f $dsn )
  {
    $dsn = cyg2win( rel2abs($dsn) );    # uses forward slashes
  }

  if (
    my $dbh = eval {
      DBI->connect( "dbi:$driver:$dsn_prefix$dsn", $user, $password,
        { RaiseError => 1, LongTruncOk => 1 } );
    }
   )
  {
    warn_v "connected to database dbi:$driver:$dsn_prefix$dsn";
    process_dbh($dbh);
    $dbh->disconnect;
  }
  else
  {
    warn_v "cannot open the $driver database $dsn: $DBI::errstr";
  }
}

sub process_dbh
{
  my ($dbh) = @_;

  local $/;

  while (<>)
  {
    execute_sql( $dbh, "USE $catalog" ) if defined $catalog;
    my @result = execute_sql_and_yield_table( $dbh, $_ );
    $csvh->print( *STDOUT, $_ ) for @result;
  }
}

sub cyg2win
# convert paths starting with /cygdrive to Win32 equivalents
# this implementation is experimental; avoiding chomp($_=`cygpath -w $_`)
{
  local ($_) = $_[0];
  s#^/cygdrive/(\w+)#\U$1:#;
  $_;
}

sub execute_sql
{
  my $dbh = shift(@_);
  warn_v "preparing: @_";
  my $qh = $dbh->prepare(@_);
  warn "executing: @_\n";
  my $result = eval { $qh->execute() };
  $@ and do { warn_v "execute failed: $@"; return undef; };
  $result;
}

sub execute_sql_and_yield_table
{
  my $dbh = shift(@_);
  warn_v "preparing: @_";
  my $qh = $dbh->prepare(@_);
  warn "executing: @_\n";
  my $result = eval { $qh->execute() };
  $@ and do { warn_v "execute failed: $@"; return undef; };
  my @result = ( $qh->{NAME}, @{ $qh->fetchall_arrayref() } );
  $qh->finish();
  @result;
}