#!/usr/bin/env perl
#
# $Id$

use strict;
use warnings;

use Getopt::Std;
use Text::CSV;
use DBI;
use DBD::CSV;

# I/O is assumed to be in UTF-8
use open ':encoding(UTF-8)';
binmode( STDIN,  ':utf8' );
binmode( STDOUT, ':utf8' );

my %opt;
getopts( 'alhe:q:st:u:v', \%opt );

HELP_MESSAGE() if $opt{h};

my $add_column = $opt{a} || 0;

my $in_quote = $opt{q} // '"';
length($in_quote) eq 1
 or puke('-q must be followed by a single character');
my $in_sep = $opt{t} // ',';
length($in_sep) eq 1
 or puke('-t must be followed by a single character');
my $out_sep = $opt{u} // $in_sep;
length($out_sep) eq 1
 or puke('-u must be followed by a single character');
my $escape = $opt{e} // '\\';
my $use_sqlite = $opt{l} // 0;
my $sql = shift(@ARGV)
 or puke('you must supply a SQL statement as the (first) argument');

sub HELP_MESSAGE
{
  print STDERR <<ZZ;

Usage: $0 [-t sep] [-u sep] [-a] [-l] [-q char] [-e char] [-s] [-v] sqlstmt [csvfile1 [csvfile2 [...]]]

to execute the SQL statement sqlstmt on the tables
formed by the given CSV files.

In the statement, the names of the tables are t1, t2, ...
The result is written to standard output in CSV format.

The SQL engine used (and hence, the language supported)
is that of Perl's SQL::Statement module.

Options:

  -e char     The escape character used in input fields.
  -q char     The quote character used in input fields.
  -t sep      The CSV field separator; if omitted, a comma is used.
  -u sep      The CSV output field separator; if omitted, the input field separator is used.
  -a          The input is headerless; add column names col0, col1, col2, ...
  -l          Use SQLite as a backend: the query is executed on a
              temporary SQLite database; to/from which in/output are copied.
  -s          Replace all double quotes with single quotes.
              (The query engine requires single quotes around strings, e.g.,
               SELECT * FROM t1 WHERE x LIKE "y%" won't work without this.)
  -v          Print the query to standard error prior to execution.

Arguments:

  The SQL statement to execute.  Required.
  All other arguments are assumed to be the names of input files.

ZZ
  exit(0);
}

sub puke
{
  die join( ' ', 'fatal error:', @_ ), $DBI::errstr ? ": $DBI::errstr" : '',
   "\n";
}

if ($use_sqlite)
{
  require DBD::SQLite
   or puke("the -l option requires DBB::SQLite to be installed");
}

my $csvdbh = DBI->connect(
  'dbi:CSV:',
  undef, undef,
  {
    f_lockfile   => 0,
    csv_sep_char => $in_sep,
    csv_quote_char => $in_quote,
    csv_escape_char => $escape,
    #csv_tables => { map { 't' . ($_+1) => { file => $ARGV[$_] } } 0 .. $#ARGV }
  }
) or puke('cannot create CSV reader or open input file(s)');

# setting csv_tables there doesn't work well enough, so
#$csvdbh->{csv_tables}{io} = { file => '-' };  # does that work??  untested
$csvdbh->{csv_tables}{ 't' . ( $_ + 1 ) } = {
  file => $ARGV[$_],
  $add_column ? ( col_names => [] ) : ()
 }
 for 0 .. $#ARGV;

my $csvw =
 Text::CSV->new( { quote_space => 0, sep_char => $out_sep, eol => $/ } )
 or puke('CSV writer creation failed');
# not quite correct: we should take all settings from $csvdbh, e.g. encoding

my $qdbh;

if ( !$use_sqlite )
{
  $qdbh = $csvdbh;
}
else
{
  $qdbh =
   DBI->connect( 'dbi:SQLite:dbname=', '', '',
    { RaiseError => 1, AutoCommit => 0 } )
   or puke('cannot open a temporary SQLite database - retry without -l?');

  # copy the input to the database
  foreach my $tablename ( keys %{ $csvdbh->{csv_tables} } )
  {
    my $sth = $csvdbh->prepare("SELECT * FROM $tablename")
     or puke("unexpected error: cannot prepare import of table $tablename");
    $sth->execute()
     or puke("unexpected error: cannot execute import of table $tablename");
    my $rows = $sth->fetchall_arrayref()
     or puke("unexpected error: cannot fetch import of table $tablename");

    insert_table( $qdbh, $tablename, $sth->{NAME}, $rows );
  }
}

sub insert_table
{
  my ( $dbh, $tablename, $colnames, $rows ) = @_;

  my @colnames = @$colnames;

  my $sth = $dbh->do(
    sprintf(
      'CREATE TABLE "%s" (%s)',
      $tablename,
      join( ',',
        map { sprintf( '"%s" %s', $colnames[$_], 'TEXT' ) } 0 .. $#colnames )
    )
  ) or puke("cannot create table $tablename");
  my $inserter = $dbh->prepare(
    sprintf(
      'INSERT INTO "%s" (%s) VALUES (%s)',
      $tablename,
      join( ',', map { sprintf( '"%s"', $_ ) } @colnames ),
      join( ',', map { '?' } @colnames )
    )
  ) or puke("cannot prepare insertion of table $tablename");

  $dbh->commit();

  foreach my $row (@$rows)
  {

    if ( !$inserter->execute(@$row) )
    {
      puke( "cannot insert a row into $tablename: ", $dbh->errstr );
    }
  }

  $dbh->commit();
}

$sql =~ s/"/'/g if $opt{s};

warn "$sql\n" if $opt{v};

my $qsth = $qdbh->prepare($sql)
 or puke('SQL statement preparation failed');

$qsth->execute
 or puke('SQL statement execution failed');
#$qdbh->commit();  AutoCommit is on

$csvw->print( *STDOUT, $qsth->{NAME} );

while ( my $r = $qsth->fetchrow_arrayref )
{
  $csvw->print( *STDOUT, $r );
}
$qsth->finish();
$qdbh->disconnect();