simple SQL client to Oracle

oraclient.pl

#!/usr/bin/perl
use strict;
use warnings;
use utf8;
use DBD::Oracle;
use Text::ASCIITable;
use Term::ReadLine;

my $dbh;
BEGIN {
	my $dsn = 'dbi:Oracle:'.(shift || 'default dsn'), 
	my $user = (shift || 'default user');
	my $pass = (shift || 'default pass');

	$dbh = DBI->connect(
        $dsn, $user, $pass,
		{
			AutoCommit => 0, 
			RaiseError => 0, 
			PrintError => 1, 
		},
	) or die $DBI::errstr;
}
END { $dbh->disconnect() if $dbh }

my $history;
my $term;
BEGIN { 
	$history = "$ENV{HOME}/.oraclient_history";
	$term = new Term::ReadLine 'OraClient' or die "can't load term";
	$term->StifleHistory(500);
	$term->Features->{readHistory} and
	$term->ReadHistory($history) and
	print "loaded $history\n";
}
END {
	print "\n";
	$term and
	$term->Features->{writeHistory} and
	$term->WriteHistory($history) and
	print "saved $history\n";
}


my $interactive = -t STDIN;
my @bold = $interactive ? ("\e[1m","\e[m") : ('','');
sub getline() { $interactive ? $term->readline('sql>') : <STDIN> }

my $attribs = $term->Attribs;
$attribs->{completion_word} = [qw(
	delete from group order having insert join merge 
	prepare select truncate union update where rownum
)];
$attribs->{completion_append_character} = '';
$attribs->{completion_entry_function} =
	$attribs->{list_completion_function};

my $database_object_match = [];
sub database_object_generator($$@)
{
	my ($text, $state, $sql, @bind) = @_;
	unless($state) {
		$database_object_match = $dbh->selectcol_arrayref($sql, undef, @bind);
	}
	while(defined($_ = pop @$database_object_match)) {
		return lc $_ if /^$text/i
	}
	return undef;
}

# TODO collect table aliases and map them
sub column_generator($$)
{
	my ($text, $state) = @_;
	database_object_generator($text, $state, qq{
		SELECT table_name || '.' || column_name
		FROM all_tab_columns
		WHERE table_name = ? AND column_name LIKE ?
		ORDER BY table_name
		}, uc($1), uc($2).'%') if $text =~ /^(\w+)\.(\w*)/
}
sub table_generator($$)
{
	my ($text, $state) = @_;
	database_object_generator($text, $state, qq{
		SELECT table_name
		FROM all_tables
		WHERE table_name LIKE ?
		ORDER BY table_name
		}, uc($text).'%')
}

$attribs->{attempted_completion_function} = sub {
	my ($text, $line, $start, $end) = @_;
	my @match = ();
	my @try = ();
	push @try, \&table_generator if $text =~ /^tb/;
	push @try, \&column_generator if $text =~ /^\w+\.\w*/;
	push @try, $attribs->{list_completion_function};
	push @try, \&table_generator unless grep {$_ eq \&table_generator} @try;
	while(@try && !(@match = $term->completion_matches($text, pop @try))) { ;}
	@match
};

my $sth;
while ( defined (my $input = getline) ) {
	for my $cmd (split /;/, $input) {
		next unless $cmd =~ /\S/;
		print "$cmd\n";
		$sth = $dbh->prepare($cmd) and
		do { 
			$sth->execute() and 
			@{$sth->{NAME}} and
			do {
				my $table = Text::ASCIITable->new({
					allowANSI => 1,
					undef_as => "$bold[0]NULL$bold[1]",
				});
				my @col = 
					map {"$bold[0]$_$bold[1]"} 
					@{$sth->{NAME_uc}};
				$table->setCols(@col);
				my $i = 0;
				for my $row (@{$sth->fetchall_arrayref}) {
					$table->addRow(@$row);
					if(++$i % 20 == 0) {
						$table->addRowLine();
						$table->addRow(@col);
						$table->addRowLine();
					}
				}
				print $table;
			} or
			print $sth->rows, " row(s)\n\n";
		}
	}
}

__END__