Bash database shell

Ovid on 2004-07-09T21:36:33

Sometimes it gets very frustrating searching through the metadata of Oracle to find what what I want, particularly since I don't have the shell handy. Getting tired of this, I wrote a simple Perl program, qsql which allows me to execute a lot of queries without using Oracle's pitiful SQLPlus. After fine-tuning it, I realized that most of the things I need to use it for are amenable to further tweaking. I can now do things like:

$ desc ti_releases | grep COUNTRY_ID
ti_releases.COUNTRY_ID                NOT NULL  VARCHAR2(5)

Want a quick description of every table whose name contains the word "product"?

$ for table in `tabs product`; do desc $table; done;

And then I can grep for the fields I am really looking for. It's the full power of bash with most of my most commonly needed database search tools.

---

URL

melopt on 2004-07-09T22:01:35

So where do we download this? ;)

Re:URL

Ovid on 2004-07-09T22:27:04

It still has work a fair amount of work that it needs and it's specific to Oracle, but if you want to see the core of it (fair warning, it's a sloppy hack):

use Text::CSV_XS;;

my $dbh = DBI->connect(...);
my $sql = @ARGV > 1
    ? join ' ' => @ARGV
    : shift;

$sql = munge_sql($dbh, $sql) || die "No sql supplied";

if ($sql =~ /^\s*desc\s*(\w+)/) {
    desc_tables_and_exit($dbh, $sql, $1);
}

print "Executing ($sql)\n" if $ENV{DEBUG};
my $sth;
{
    local $^W; # stop some silly internal warning
    $sth = $dbh->prepare($sql);
}

$sth->execute;
                                                                                                                                                             my $csv = Text::CSV_XS->new
while (my @data = $sth->fetchrow_array) {
    my $status = $csv->combine(@data);
    print $csv->string(), "\n";
}

$dbh->disconnect;

sub desc_tables_and_exit {
    my ($dbh, $sql, $table) = @_;
    my $sth = $dbh->column_info({TABLE_NAME => uc($1)});

    my $format = "$table.%-30s %-8s %-20s\n";
    my $description = '';
    while (my $data = $sth->fetchrow_arrayref) {
        my ($name, $type, $length1, $length2, $null) = @{$data}[3,5,6,7,17];
        $null = $null eq 'YES' ? 'NULL' : 'NOT NULL';
        $type =  "$type($length1)" if $length1 == $length2;
        $description .= sprintf $format => $name, $null, $type;
    }
    print $description;
    exit;
}

sub munge_sql {
    my $dbh = shift;
    my $sql = shift;
    if ($sql =~ /^\s*tabs?\s+(\S+)/) {
        my $table_name = $1;
        $table_name =~ s/\W//g;
        $sql = "SELECT table_name FROM tabs WHERE table_name LIKE upper('%$table_name%')";
    }
    return $sql;
}

Then it's a simple matter of writing shell utilities around it. I call the above program qsql, so my desc and tabs (table names) utilities are:

#/bin/sh
$HOME/bin/qsql desc $1

and

#/bin/sh
$HOME/bin/qsql tabs $1

You can also write simple select statements from the command line with my sel utility:

sel name from users where login_id = \'ovid\'

Implemented with:

#/bin/sh
$HOME/bin/qsql "select $*"

Again, everything is very specific to Oracle and it's not rocket science, but it's very, very useful to me. Note that the desc utility will only describe tables, not views. That can be confusing if you don't know what you're looking for isn't a real table.

Re:URL

runrig on 2004-07-11T00:42:51

This doesn't need to be Oracle specific if you use the 'table_info' method. In fact, you're calling 'column_info' in a deprecated style (though in past docs, I can only tell that the style was once valid for 'table_info'). Since the 'table_info' and 'column_info' methods allow wildcards for the table name, you could do all the tricky parts with just those methods.

it's a sloppy hack...

Yeah, there is a bit of slop in that you pass the table name to the desc_tables_and_exit function, but use the captured match from the previous function instead of the passed arg, and that the dbh is passed but not used in munge_sql :-)

Re:URL

runrig on 2004-07-11T00:50:18

I should also admit that I've never actually used the table_info OR the column_info methods :) But then, I do most all my SQL from vi(m)? anyway.

Re:URL

Ovid on 2004-07-11T20:20:40

Yeah, there is a bit of slop in that you pass the table name to the desc_tables_and_exit function, but use the captured match from the previous function instead of the passed arg...

Well, that bit of dreck can easily be explained away by the fact that I was busy doing a refactor I later abandoned and when I posted here I noticed I was using $1 so I tried to fix that on the fly and failed miserably :)

Re:URL

Ovid on 2004-07-11T20:28:06

Ack. I hit submit instead of preview. Sigh.

I was trying to thank you for the information, but I didn't get that far :)

What did you dislike about existing shells?

btilly on 2004-07-12T23:38:58

What existing shells you ask? Well several co-workers use a hacked up version of piqt. I suspect that dbishell is going to provide more functionality out of the box. The latter page points at several more to look at.

Perhaps another way to state this question is, "What features are you really looking for in a project like this?"

Re:What did you dislike about existing shells?

Ovid on 2004-07-13T00:01:01

I didn't know about those others but it looks like they might provide what I was looking for. Basically, I wanted to interact with the database without leaving Bash. By building a few small utilities that interacted with one another, I have the database directly in the shell. If these other projects provide that as seamlessly, I'll probably go with one of them. Thanks for the heads up.