Usage: mdbget -db database -s 'select sql' [options] [query_param.list]
        or:  [-db database] -f sql.file    [options] [query_param.list]

  options:
    -l : label columns (print list of column names as first line of output)

    -d delim : set field delimiter to 'delim' (default delim is tab)
       "-d sp" and "-d ' '" both work to produce single-space delimited data
       "-d csv" will produce "true CSV" output, adding double-quotes as needed
       NOTE: only "-d csv" will quote fields containing the delimiter character
             (csv also turns on "-l", so column names are listed on first line)

    -w {norm|mark|keep} : control the treatment of whitespace within fields:
       "-w norm" (default) convert all whitespace strings to single space
       "-w mark" keep spaces as-is, convert newline to '\n', tab to '\t'
       "-w keep" make no alterations to white space at all

 query_param.list is needed if sql contains "?" placeholder(s) for param(s)

##</code><code>##

#!/usr/bin/perl

# Program:      mdbget
# Written by:   David Graff
# Purpose:      general utility for running sql select statements

# Command-line args give the mysql database name and an sql-statement
# for doing a selection.  Selection output is sent to stdout.

# The sql-statement can be stored in a file and used by means of the
# "-f sql.file" option; if the environment variable SQL_PATH is
# defined, this will be searched for the sql.file if the file is not
# found in the current directory, or in the directory that holds mdbget.

# An sql.file may contain comment lines beginning with "#"; it is
# possible to include a database name in the file as well (so
# it won't be needed on the command line) on a line starting with
# "##", e.g.:
#
## -a account_name

$Usage = <<ENDUSE;
Usage: mdbget -db database -s 'select sql' [options] [query_param.list]
        or:  [-db database] -f sql.file    [options] [query_param.list]

  (-a database is a synonym for -db database)

  options:
    -l : label columns (print list of column names as first line of output)

    -d delim : set field delimiter to 'delim' (default delim is tab)
       "-d sp" and "-d ' '" both work to produce single-space delimited data
       "-d csv" will produce "true CSV" output, adding double-quotes as needed
       NOTE: only "-d csv" will quote fields containing the delimiter character
             (csv also turns on "-l", so column names are listed on first line)

    -w {norm|mark|keep} : control the treatment of whitespace within fields:
       "-w norm" (default) convert all whitespace strings to single space
       "-w mark" keep spaces as-is, convert newline to '\\n', tab to '\\t'
       "-w keep" make no alterations to white space at all

 query_param.list is needed if sql contains "?" placeholder(s) for param(s)

ENDUSE

use Getopt::Long;
use MysqlFunc;   ### this is where user-ids/passwds are kept

die $Usage
    unless ( &GetOptions( \%opt, 'db=s', 'a=s', 'd=s', 'f=s', 's=s', 'w=s', 'l' ) &&
            ( $opt{f} ne "" ^ ( $opt{s} =~ /^\s*(select|show|describe)\s+/i &&
                              ( $opt{a} =~ /^\w+$/ or $opt{db} =~ /^\w+$/ ))));

die $Usage if ( $opt{w} ne "" && $opt{w} !~ /norm|mark|keep/ );
$opt{w} ||= "norm";
$opt{db} ||= $opt{a};

if ( $opt{s} ne "" ) {
    $sql = $opt{s};
} 
else {
    unless ( -r $opt{f} ) {
        $base = $0;
        $base =~ s%[^/]*$%%;
        $sfile = "";
        if ( $ENV{SQL_PATH} ne "" ) {
            foreach $p ( split( /:/, $ENV{SQL_PATH} )) {
                if ( -r "$p/$opt{f}" ) {
                    $sfile = "$p/$opt{f}";
                    last;
                }
            }
        }
        if ( $sfile eq "" ) {
            if ( -r "$base$opt{f}" ) {
                $sfile = "$base$opt{f}";
            } else {
                $sqldir = ( $ENV{SQL_PATH} ne "" ) ? $ENV{SQL_PATH} : "not set";
                die "No script file $opt{f} in $ENV{PWD}, $base or SQL_PATH ($sqldir)\n";
            }
        }
        $opt{f} = $sfile;
    }
    open( SCRPT, "<$opt{f}" );
    while (<SCRPT>)
    {
        if ( /^(?:\#|--)/ && / -(db|a) (\w+)/ ) {
            $opt{db} = $1;
        }
        next if ( /^(?:\#|--)|^\s*$/ );
        s/^\s*//;   # remove leading whitespace
        s/\s*$/ /;  # normalize trailing whitespace
        $sql .= $_;
    }
    close SCRPT;
    die "No suitable sql statement found in $opt{f}\n"
        unless ( $sql =~ /^\s*(select|show|describe)\s+/i );
    die "No database name has been provided\n"
        unless ( $opt{db} =~ /^\w+$/ );
}
$delim = "\t";
if ( $opt{d} ) {
    $delim = ( $opt{d} eq "sp" ) ? " " : $opt{d};
}

@qm = ( $sql =~ /\?/g );
$nq = scalar(@qm);
if ( $nq ) {
    $infile = ( @ARGV ) ? join( " ", @ARGV) : "stdin";
    print STDERR "looking for $nq params per line from $infile...\n";
    while (<>) {
        s/[\r\n]+$//;
        if ( $nq > 1 ) {
            @row = split( /$delim/ );
            if ( scalar( @row ) != $nq ) {
                warn scalar(@row)," fields instead of $nq at line $. - skipped: $_\n";
                next;
            }
            push( @data, [ @row ] );
        } else {
            push( @data, [ $_ ] );
        }
    }
    die "No usable input data for query parameters\n" unless (@data);
}

my $db = MysqlFunc->new( $opt{db} );
my $hdrline = ( $delim eq "csv" or $opt{l} );

if ( $hdrline and $sql =~ /\s*describe/i ) {
    @fields = qw/Field Type Null Key Default Extra/;
    printline( \@fields );
}

if ( @data ) {
    $sqlop = $db->sqlPrep( $sql );
    foreach $param ( @data )
    {
        $grid = $db->queryGrid( $sqlop, $hdrline, $param );
            ### (just a wrapper method for DBI::prepare/execute/fetchall_arrayref)
        foreach $row ( @$grid ) {
            printline( $row );
        }
    }
    $db->sqlDone( $sqlop );
} else {
    $grid = $db->queryGrid( $sql, $hdrline );
    foreach $row ( @$grid ) {
        printline( $row );
    }
}

# In case output delimiter is set to CSV, implement these rules
# (from www.perlmonks.org node 151784):
#
# Here is a description of the basic CSV spec as implemented in most Microsoft products: 
#
# 1. Rows are delimited by returns. (\r\n or \n depending on the platform, binmode, etc.)
# 2. Fields within a row are delimited by ",".
# 3. Fields may be quoted or unquoted. 
# 4. Quoted fields are literal text that start and end with an unpaired ".
#    Separators, returns, etc can appear within a quoted field, and " can appear doubled. 
# 5. Unquoted fields cannot contain the separator, returns, or quotation marks. 
#    They are also subject to some interpretation. For instance numbers may appear
#    in floating point, and an empty field is a null (represented within Perl by undef
#    - very few parsers get this right). 
# 6. It is customary for the first row to be the field names, and for all rows to have
#    the same number of fields. 

sub printline
{
    my @cols = @{$_[0]};
    my $sep = $delim;
    if ( $opt{w} eq "norm" ) {
        foreach ( @cols ) {
            s/\s+/ /g;
        }
    }
    elsif ( $opt{w} eq "mark" ) {
        foreach ( @cols ) {
            s/\n/\\n/g;
            s/\r/\\r/g;
            s/\t/\\t/g;
        }
    }
    if ( $delim eq "csv" ) {
        $sep = ",";
        foreach ( @cols ) {
            s/\"/\"\"/g;  # double all quotation marks present in field content
            $_ = '"' . $_ . '"' if ( /[\"\'\,]/ ); # bracket with "..." if necessary
        }
    }
    print join( $sep, @cols ) . "\n";
}