#!/usr/bin/perl
#
# sqlvalid:  validate SQL expressions
# Written by Stephen S. Flitman, M.D.
# requires a test database
#######################################################################

use strict 'vars';
use vars qw/$dbh $db %opts/;
use DBI;
use Getopt::Std;

$db='test';

sub error {
   my $text=shift;
   print STDERR "$app: $text\n";
   exit 1;
}

getopts('cd:h',\%opts);

if ($opts{h} or !@ARGV) {
   print <<"EOT";
Usage: sqlvalid [-chv] [-d database] [expr|file ...]
  Determine if files contain valid SQL; statements are never committed
  Switches:
    -c      Take expression from command line (joins spaces)
    -d DB   Alternative database than [$db]
    -h      This help
EOT
   exit 1;
}
$db=$opts{d} if $opts{d};
error "No database selected" unless $db;
$dbh=DBI->connect("dbi:Pg:dbname=$db","","");
unless ($dbh) { error "Couldn't select database $db"; }
$dbh->{RaiseError}=0;
$dbh->{PrintError}=0;

my $expr;
if ($opts{c}) {
   $expr=join(' ',@ARGV);
   print valid_sql($expr);
} else {
   local($/)=undef;
   for my $file (@ARGV) {
      if (open(FILE,$file)) {
         $expr=<FILE>;
         close FILE;
         print valid_sql($expr);
      } else {
         error "Cannot read $file: $!";
      }
   }
}
$dbh->disconnect();
exit;

sub valid_sql {
   my $expr=shift;
   my $ret;
   $dbh->begin_work();
   eval {
      my $sth=$dbh->prepare($expr);
      die $dbh->errstr."\n" if $sth->err;
      $sth->execute;
      die $dbh->errstr."\n" if $sth->err;
      $sth->finish;
   };
   if ($@) {
      $ret=$@;
   } else {
      $ret="SQL syntax OK\n";
   }
   $dbh->rollback();   # never commit
   $ret;
}