#!/usr/bin/perl

#
# This program is Copyright 2005 by Hans Poo.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the Perl Artistic License or the
# GNU General Public License as published by the Free Software
# Foundation; either version 2 of the License, or (at your option) any
# later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# If you do not have a copy of the GNU General Public License write to
# the Free Software Foundation, Inc., 675 Mass Ave, Cambridge,
# MA 02139, USA.
#

use Getopt::Long;
use Text::CSV_XS;
use DBI;

use strict;

my ($dbname, $sql, $host, $user, $pass, $help);
my $result = GetOptions ("dbname=s" => \$dbname, "sql:s" => \$sql, "help" => \$help, "host:s" => \$host, "user:s" => \$user, "pass:s" => \$pass);

if ($help) {
	&do_help;
	exit 0;
}

unless ($dbname) {
	print STDERR "You must suply a database name\n";
	&do_help;
	exit 1;
}

my $connect_string = "dbi:Pg:dbname=$dbname";
$connect_string .= ";host=$host" if $host;

my $dbh = DBI->connect($connect_string, $user, $pass) or die $DBI::errstr;
my $csv = Text::CSV_XS->new({binary => 1});

unless ($sql) {
	chomp($sql = <>);
}

# litle query cleanup
$sql =~ s/^.*?\bselect\b/select/i;
$sql =~ s/;.*//i;
unless ($sql =~ /select/i) {
	print STDERR "Your query don't looks good\n";
	exit 1;
}

my $sth = $dbh->prepare($sql);
$sth->execute;

my $names = $sth->{NAME};
$csv->combine(@$names);
print $csv->string, "\n";

while (my @cols = $sth->fetchrow_array) {
	if ($csv->combine(@cols)) {
		print $csv->string, "\n";
	} else {
		my $err = $csv->error_input;
		print STDERR "combine() failed on argument: ", $err, "\n";
	}
}

$sth->finish;
$dbh->disconnect;

sub do_help {

print STDERR <<EOF;
query2csv: Print a sql query result in csv format.

Usage: query2csv --dbname=mydatabase [--sql="select * from ..."] [--host=localhost --user=postgres --pass=xxxx ]

If sql sentence is not specified, it's read from standard input.

Prerequisite Perl Modules: 
- Text::CSV_XS, csv library
- DBI and DBD::Pg, perl modules to access postgresql

To install these modules try: 
perl -MCPAN -e 'install Text::CSV_XS'
perl -MCPAN -e 'install DBI'
perl -MCPAN -e 'install DBD::Pg'

EOF

}

=head1 query2csv

query2csv: Print a sql query result in csv format.

Usage: query2csv --dbname=mydatabase [--sql="select * from ..."] [--host=localhost --user=postgres --pass=xxxx ]

If sql sentence is not specified, it's read from standard input.

Prerequisite Perl Modules: 

	Text::CSV_XS, csv library
	DBI and DBD::Pg, perl modules to access postgresql

To install these modules try: 

	perl -MCPAN -e 'install Text::CSV_XS'
	perl -MCPAN -e 'install DBI'
	perl -MCPAN -e 'install DBD::Pg'

=cut