osqsh-sqlplus

#!/usr/bin/perl

##########################################################################
# Oracle client CLI wrapper for SQLPlus that adds Readline support.
#
# This isn't likely to be any kind of full replacement for SQLPlus,
# but readline support should make it more user friendly (if a modern
# CLI is your friend).
#
##########################################################################

use warnings;
use strict;
use Term::ReadLine;

## load contents of rc file if any
#
my %rc_vars = &load_rcfile;

## assign needed variables (environment has precedence over rc file)
#
my @need_oravars = qw( 
    ORACLE_HOME
    ORACLE_SID
    ORACLE_USER
    ORACLE_PASS
    TMP_DIR
);
&load_neededvars;

## load optional settings from rc file
#
my @presets = &add_rc_settings(\%rc_vars);

my $oracle_home = $ENV{'ORACLE_HOME'};
my $oracle_user = $ENV{'ORACLE_USER'};
my $oracle_pass = $ENV{'ORACLE_PASS'};
my $tmp_dir     = $ENV{'TMP_DIR'};
my $sqlplus_full = "$oracle_home/bin/sqlplus";
my $sqlplus_args = '-L -S';
if ( ! -f $sqlplus_full ) { die "No sqlplus at $sqlplus_full\n"; }
if ( ! -x $sqlplus_full ) { die "Cannot execute $sqlplus_full\n"; }

my $temp_file = "$tmp_dir/osqsh-$$.tmp";
open ( TMP_FILE , ">$temp_file" ) || die "Unable to open $temp_file\n";
chmod 0600 , $temp_file; # minimal security

## optionally load tables for tab-completion
#
my @user_tables;
if ( ( defined $rc_vars{'ENABLE_TABS'} ) and ( $rc_vars{'ENABLE_TABS'} =~ /on|yes|true/i ) ) {
    @user_tables = &load_usertables;
}

## load tab-completion
#
my @nice_tabs = qw( 
    select
    desc
    tabs
    insert
    into
    create
    from
    where
    varchar2
    exists
    number
    timestamp
    dual
    between
    exists
);
push @nice_tabs , @user_tables;
my @tab_completes = &load_tabs(@nice_tabs);


## set up readline with tab completion
#
my $prompt = "OSQSH> ";
my $term = Term::ReadLine->new('OSqsh');
my $attribs = $term->Attribs;
$attribs->{completion_function} = sub { return @tab_completes; };


## now run the prompt
#
my @multiline_cmd;
while ( defined ( my $command = $term->readline($prompt)) ) {

    ## quit (^D also works)
    #
    if ( $command =~ /^quit\W*/i ) {
        exit 0;

    ## record settings
    #
    } elsif ( $command =~ /^set /i ) {
        push @presets , $command;

    ## add table column headers for tab completion
    #
    } elsif ( $command =~ /^tabs (\w+)/i ) {
        my $load_table = $1;
        my @column_headers = &get_headers($load_table);
        push @nice_tabs , @column_headers;
        my @tab_completes = &load_tabs(@nice_tabs);
        $attribs->{completion_function} = sub { return @tab_completes; };

    ## any line that contains a semicolon will be executed (anything after the semicolon is ignored)
    #
    } elsif ( $command =~ /;/ ) {
        $command =~ s/;.*$/;/;
        push @multiline_cmd , $command;
        my $multiline_cmd = join ' '  , @multiline_cmd;
        my $presets       = join "\n" , @presets;
        @multiline_cmd = ();
        # $term->addhistory($multiline_cmd) if ( $command =~ /\S/ );
        print TMP_FILE "$presets\n";
        print TMP_FILE "$multiline_cmd\nquit\n";
        my $full_cli = "$sqlplus_full $sqlplus_args $oracle_user/$oracle_pass \@$temp_file";
        my $read_out = `$full_cli`;
        truncate TMP_FILE,0;
        seek (TMP_FILE,0,0);
        print "$read_out";

    ## anything else is considered a multiline command
    #
    } else {
        push @multiline_cmd , $command;
    }

    ## show the buffer if anythings in it
    #
    if ( @multiline_cmd > 0 ) {
        print "@multiline_cmd\n";
    }

}

close TMP_FILE;
unlink $temp_file;
print "\n";


##########################################################################
# Subroutines
##########################################################################
sub load_rcfile {

    my %local_rc_vars;
    my $setcount = 1;
    if ( defined $ENV{'HOME'} ) {
        my $rc_file = "$ENV{'HOME'}/.osqshrc";
    
        if ( -f $rc_file ) {
            open ( RCFILE , "$rc_file" ) || die "Unable to open $rc_file\n";
            while (<RCFILE>) {
                chomp;              # no newline
                s/#.*//;            # no comments
                s/^\s+//;           # no leading white
                s/\s+$//;           # no trailing white
                next unless length; # anything left?
                my ($var, $value);
                if ( m/^(set\s+.*)$/i ) {
                    # print "found setting $1\n";
                    $var   = "set" . $setcount;
                    $value = $1;
                    $setcount++;
                } else {
                    ( $var , $value ) = split( /\s*=\s*/ , $_ , 2 );
                }
                $local_rc_vars{$var} = $value;
            }
        }
    }

    return %local_rc_vars;

}

sub load_neededvars {
    foreach my $this_oravar ( @need_oravars ) {
        if ( defined $ENV{"$this_oravar"} ) {
            # good
        } elsif ( defined $rc_vars{$this_oravar} ) {
            $ENV{"$this_oravar"} = $rc_vars{$this_oravar};
        } else {
            print "Enter $this_oravar: ";
            my $input_var = <STDIN>;
            chomp $input_var;
            $ENV{"$this_oravar"} = $input_var;
        }
    }
}

sub load_usertables {

    my @load_tables;
    print TMP_FILE "set head off\nselect table_name from user_tables;\nquit\n";
    my $full_cli  = "$sqlplus_full $sqlplus_args $oracle_user/$oracle_pass \@$temp_file";
    my @read_tabs = `$full_cli`;
    @load_tables = grep { /\w/ } @read_tabs;
    chomp @user_tables;
    truncate TMP_FILE,0;
    seek (TMP_FILE,0,0);
    return @load_tables;

}

sub load_tabs {
    my @these_words = @_;
    my @add_tabs;
    foreach my $this_tab ( @these_words ) {
        next unless ( $this_tab =~ /\w/ );
        $this_tab =~ s/^\s//;
        $this_tab =~ s/\s$//;
        my $this_tab_uc = uc( $this_tab );
        my $this_tab_lc = lc( $this_tab );
        push @add_tabs , $this_tab_uc , $this_tab_lc;
    }
    return @add_tabs;
}

sub get_headers {

    my $read_table = uc(shift);
    print TMP_FILE "
        set head off
        set feedback off
        select column_name from ALL_COL_COMMENTS where table_name = '$read_table';
        quit\n";
    my $full_cli  = "$sqlplus_full $sqlplus_args $oracle_user/$oracle_pass \@$temp_file";
    my @read_tabs = `$full_cli`;
    my @load_columns = grep { /\w/ } @read_tabs;
    chomp @load_columns;
    truncate TMP_FILE,0;
    seek (TMP_FILE,0,0);
    return @load_columns;

}

sub add_rc_settings {
    my @custom_settings;
    my(%all_settings) = %{$_[0]};
    my @all_settings = keys( %all_settings );
    foreach my $this_setting ( @all_settings ) {
        if ( $this_setting =~ m/^set\d+/ ) {
            my $set_setting = $all_settings{$this_setting};
            push @custom_settings , $set_setting;
        }
    }
    return ( @custom_settings );
}