Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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__
Add Comment
Please, Sign In to add comment