#!/usr/bin/perl
use DBI;
use Getopt::Std;

my %opts;
getopt('dDFupe', \%opts);
my $dbd = $opts{D} || "Pg";
my $field_sep = $opts{F} || '\s+';
my $database = $opts{d};
my $user = $opts{u};
my $password = $opts{p};
my $sql = $opts{e} || shift;

die "No database specified\n" unless $database;
die "No SQL specified\n" unless $sql;

my $cs = "dbi:$dbd:dbname=$database";
my $dbh = DBI->connect($cs, $user, $password)
    or die "Cannot connect to database $cs\n";

$sql =~ s/^{//; $sql =~ s/}$//;

my @order = 0;
push @order, $1 while $sql =~ s/(?<!\\)\$(\d+)/?/;

$sth = $dbh->prepare($sql) or die "Couldn't prepare SQL: ".$dbh->errstr."\n";

while (<>) {
    chomp;
    my @args = split $field_sep, $_;
    $sth->execute(map {$args[$order[$_]-1]} 1..$#order);
}
$sth->finish;
$dbh->disconnect;

=head1 NAME

squalk - SQL AWK (of sorts)

=head1 USAGE

    squalk -d <database> [...] [-e] <sql> [file ...]

=head1 DESCRIPTION

C<squalk> is a quick way of manipulating SQL databases in terms of flat
text files. For example:

    squalk -d courses -e 'update courses set category=$2 where code=$1'

expects a file with two columns, a course code and its respective
category, and updates the database to set the category for each course.

Another example:

    squalk -d machines 
        -e 'insert into systems (make, os, revision) values ($1,$2,$3)'

Works like just another Unix utility - takes input from standard input
or files specified on command line.

=head1 OPTIONS

    -d <database>

Mandatory option. (In which case, is it really an option?) Determines which 
database to connect to.

    -u <user> -p <password>

Username and password to connect as.

    -D <dbd>

Database backend - defaults to C<Pg> for PostGres.

    -F <fieldsep>

Field separator, a la C<awk>. This can be a regular expression.

=head1 BUGS

There are undoubtedly no bugs whatsoever lurking somewhere in code this
simple. :-)

This was a quick hack, hope you find it useful, but please don't ask for
support/enhancements, it's unsupported.

=head1 AUTHOR

Simon Cozens, C<simon@cpan.org>