#!/usr/local/bin/perl
# -*- perl -*-

# Copyright (c) 2001 by Jeff Weisberg
# Author: Jeff Weisberg <jaw @ tcp4me.com>
# Function:  SQL "shell"

# connect to SQL database, execute SQL
# punts to groff for pretty printing


use DBI;

($dsn, $user, $pass) = @ARGV;
die "usage:\n\tsqlsh dsn user passwd\n" unless ($dsn && $user && $pass );

$dbh = DBI->connect($dsn, $user, $pass, {AutoCommit => 1}) ||
    die "could not connect to database\n";

$ps_mode = 0;

while( 1 ){

    $st = '';
    while( $st !~ /;/ ){
	print STDERR ( $st ? "...> " : "sql> " );
	$st .= <STDIN>;
	$st =~ s/\s+--\s+.*//;
	exit unless $st !~ /^\s*$/;
    }
    
    $st =~ s/;//;
    if( $st =~ /[^\\](>|\|)/ ){
	# output redirection
	($st, $out) = $st =~ /(.*)\s*([^\\](?:>|\|).*)/s;
    }else{
	$out = '';
    }

    $st =~ s/\\//g;
    
    if( $st =~ /^set\s+ps/ ){
	$ps_mode = 1;
	next;
    }elsif( $st =~ /^set\s+nops/ ){
	$ps_mode = 0;
	next;
    }
    
    $sth = $dbh->prepare( $st ) || do { print STDERR "statement error\n"; next };
    $x = $sth->execute;
    do { print STDERR "error\n"; next } unless $x;
    next unless $sth;
    next unless $st =~ /^\s*select/i;
    

    $hdr = 0;
    while( $a = $sth->fetchrow_arrayref ){
	if( !$hdr ){
	    my $groff = $ps_mode ? "groff" : "gnroff | uniq";
	    open( TBL, "| gtbl | $groff $out" );
	    print TBL ".TS\n";
	    @name = @{$sth->{NAME}};
	    @type = @{$sth->{TYPE}};

	    # print TBL "box ;\n";
	    print TBL "lB " foreach (@name);
	    print TBL "\n";
	    print TBL "l " foreach (@name);
	    print TBL ".\n";
	    print TBL join("\t", @name), "\n";
	    print TBL "=\n";
	    $hdr = 1;
	}
	print TBL join("\t", @{$a}), "\n";

    }
    if( $hdr ){
	print TBL ".TE\n";
	close TBL;
    }
    
}


sub dh {
    my $h = shift;
    my $k;

    print STDERR "$h\n";
    foreach $k (keys %{$h}){
	print STDERR "$k \t->$h->{$k}\n";
    }
}