#!/usr/bin/perl -w ####################################################################### # # # yasql # # # ####################################################################### # # # Description: I always liked the Sybase isql and MySQL mysql # # command-line shells for Unix. I have often # # regretted the lack of a Unix port to osql, the # # command-line interface to M$-SQL. Eventually # # I wrote one. # # # # Author: Mark V. Zieg # # Date: October 25, 2000 # # Version: 1.0 # # # # Instructions: see Help() below # # # # Bugs/To-Do: needs field headers atop result lists # # # # Notes: # # # # This assumes that you have an NT SQL Server running somewhere, # # and that you've already setup DBI::Proxy on that NT box, which is # # is now running through something like this: # # # # c:\perl\bin\dbiproxy.bat --configfile c:\perl\conf\dbiproxy.ini # # # # dbiproxy.ini: # # ------------- # # { # # 'localport' => 6666, # # 'pidfile' => 'c:\perl\conf\dbiproxy.pid', # # 'logfile' => 1, # # 'debug' => 1, # # 'mode' => 'single', # # 'timeout' => 60, # # 'clients' => [ # # { # # 'mask' => 'my.unix.server.com', # # 'accept' => 1 # # }, # # { # # 'mask' => '192.291.129.219', # # 'accept' => 1 # # } # # ] # # } # # # # For more information, check out: # # http://tlowery.hypermart.net/perl_dbi_dbd_faq.html \ # # #HowDoIAccessMSWindowsDB # # # ####################################################################### ############################ Dependencies ############################# use strict; use DBI; use Term::ReadLine; use Getopt::Long; use Data::Dumper; use Data::ShowTable; ############################## Globals ################################ my $PAGE_SIZE = 24; my $MAIN_PROMPT = 'yasql> '; my $CONT_PROMPT = ' --> '; my $TRANSCRIPT_FILE; my $Term; # Data::ShowTable vars my $ra_Data; my $ShowTable_RowIndex; # default options (should DBI_PROXY_PORT have a default?) my $rh_Opts = { 'QUIET_LAUNCH' => 0, 'TRANSCRIPT_FILE' => "", 'FIELD_DELIMITER' => "\t", 'ROW_DELIMITER' => "\n", 'RESULT_MODE' => "table" }; # these arguments must be specified through the command-line or otherwise my @REQUIRED_ARGS = ( 'DBI_PROXY_HOST', 'DBI_PROXY_PORT', 'DBI_PROXY_DSN', 'DBI_PROXY_USER' ); # these args are allowed to be specified my @ALLOWED_ARGS = ( @REQUIRED_ARGS, 'QUIET_LAUNCH', 'DBI_PROXY_PASS', 'TRANSCRIPT_FILE', 'ROW_DELIMITER', 'FIELD_DELIMITER', 'RESULT_MODE' ); # these queries undergo automatic translation to make things easier # for people used to other databases my $rh_TranslatedQueries = { # SHOW DATABASES '^\s*show\s+databases;?\s*$' => 'select name from master..sysdatabases order by name', # SHOW TABLES '^\s*show\s+tables;?\s*$' => "select substring(name, 1, 30) Name " . "from sysobjects " . "where xtype = 'U' " . "order by name", # SHOW PROCS '^\s*show\s+procs;?\s*$' => "select substring(name, 1, 30) Name " . "from sysobjects " . "where xtype = 'P' " . "order by name", # SHOW VIEWS '^\s*show\s+views;?\s*$' => "select substring(name, 1, 30) Name " . "from sysobjects " . "where xtype = 'V' " . "order by name", # DESCRIBE '^\s*describe\s+([^ ;]+);?\s*$' => 'select syscolumns.name, systypes.name, ' . ' syscolumns.length, syscolumns.xprec ' . 'from sysobjects, syscolumns, systypes ' . 'where sysobjects.name = \'$1\' ' . ' and sysobjects.id = syscolumns.id ' . ' and syscolumns.xtype = systypes.xtype ' . 'order by syscolumns.name', # SELECT [...] LIMIT n '^\s*select\s+(.*)\s+limit\s+(\d+)\s*(.*)$' => 'select top $2 $1 $3', }; ############################### Main ################################ MAIN: { # initialize console I/O $Term = new Term::ReadLine( 'yasql' ); $| = 1; # check for envionmental options ParseEnvOptions(); # grab any options specified on the command-line ParseCommandLineOptions(); # print header Display( "\nyasql -- v1.1, (c) 2001 Mark Zieg \n" ) unless $rh_Opts->{QUIET_LAUNCH}; # get any missing options by interactively prompting the user PromptForMissingOptions(); # connect to the database Display( "\nConnecting to the database..." ) unless $rh_Opts->{QUIET_LAUNCH}; my $DB = GetDBHandle(); Display( "done.\n" ) unless $rh_Opts->{QUIET_LAUNCH}; # print help message if not suppressed Help() unless $rh_Opts->{QUIET_LAUNCH}; # infinite loop: process user-typed queries while( 1 ) { # build query from multiple lines my $Query = BuildQuery(); # all done? last if( $Query =~ /^exit|bye|quit$/i ); # add to readline history $Term->addhistory( $Query ); # translate queries $Query = TranslateQuery( $Query ); # prepare the query my $Statement; eval { $Statement = $DB->prepare( $Query ); }; if( $@ ) { Display( "Error while preparing query:\n\n$@" ); next; } # execute the query eval { $Statement->execute(); }; if( $@ ) { Display( "Error while executing query:\n\n$@" ); next; } # get data from database $ra_Data = $Statement->fetchall_arrayref(); # was data returned for display? if( $#{$ra_Data} != -1 ) { # are we printing results to term or disk? if( !$rh_Opts->{TRANSCRIPT_FILE} ) { DisplayDataToTerm( $Statement ); } else { WriteDataToDisk( $Statement ); } } $Statement->finish(); } $DB->disconnect(); } ############################ Func Impl ############################# #################### sub DisplayDataToTerm { my $Statement = shift; if( $rh_Opts->{RESULT_MODE} eq "table" ) { $ShowTable_RowIndex = 0; # rewind GetNextRow() my $ra_FieldWidths = GetFieldWidths( $ra_Data ); my $ra_ColumnNames = $Statement->{NAME}; my $ra_ColumnTypes = BuildFakeColumnTypes( $ra_ColumnNames ); ShowBoxTable( $ra_ColumnNames, $ra_ColumnTypes, $ra_FieldWidths, \&GetNextRow ); } else { DisplayRecordView( $Statement ); } Display( $#{$ra_Data} + 1 . " row(s) returned.\n" ); } #################### sub DisplayRecordView { my $Statement = shift; my $ra_Row; my $ra_ColumnNames = $Statement->{NAME}; my $MaxColNameLen = 0; my $i; for( $i = 0; $i <= $#{$ra_ColumnNames}; $i++ ) { if( length( $ra_ColumnNames->[$i] ) > $MaxColNameLen ) { $MaxColNameLen = length( $ra_ColumnNames->[$i] ); } } foreach $ra_Row (@{$ra_Data}) { my $str = ""; if( $rh_Opts->{RESULT_MODE} eq "html" ) { $str = "\n"; } for( $i = 0; $i <= $#{$ra_Row}; $i++ ) { if( $rh_Opts->{RESULT_MODE} eq "html" ) { if( defined( $ra_Row->[$i] ) ) { $str .= "\t\n"; } else { $str .= "\t\n"; } } else { if( defined( $ra_Row->[$i] ) ) { $str .= sprintf( "%-*s : %s\n", $MaxColNameLen, $ra_ColumnNames->[$i], $ra_Row->[$i] ); } else { $str .= sprintf( "%-*s :\n", $MaxColNameLen, $ra_ColumnNames->[$i] ); } } } if( $rh_Opts->{RESULT_MODE} eq "html" ) { $str .= ""; } $str .= "\n"; if( $rh_Opts->{GREP} ) { next unless ( $str =~ /$rh_Opts->{GREP}/i ); } print $str; } } #################### sub WriteDataToDisk { my $ra_Row; if( !open( OUTFILE, ">>$rh_Opts->{TRANSCRIPT_FILE}" ) ) { warn( "Can't append to $rh_Opts->{TRANSCRIPT_FILE}: $!" ); return; } foreach $ra_Row (@{$ra_Data}) { my $Field; my $str; foreach $Field (@{$ra_Row}) { $Field ||= ""; } if( $rh_Opts->{RESULT_MODE} =~ /html/i ) { $str = "\n\t\n\n" . $rh_Opts->{ROW_DELIMITER}; } else { $str = ( join( $rh_Opts->{FIELD_DELIMITER}, @{$ra_Row} ) ) . $rh_Opts->{ROW_DELIMITER}; } if( $rh_Opts->{GREP} ) { next unless ( $str =~ /$rh_Opts->{GREP}/i ); } print OUTFILE $str; } close( OUTFILE ); Display( $#{$ra_Data} + 1 . " row(s) written to disk.\n" ); } #################### sub GetNextRow { my $Rewind = shift; if( $Rewind ) { return; } else { if( $ShowTable_RowIndex <= $#{$ra_Data} ) { if( $rh_Opts->{GREP} ) { my @data; while( 1 ) { @data = ( @{$ra_Data->[$ShowTable_RowIndex++]} ); my $str = join( "\t", @data ); return( @data ) if( $str =~ /$rh_Opts->{GREP}/i ); return if( $ShowTable_RowIndex > $#{$ra_Data} ); } } else { return ( @{$ra_Data->[$ShowTable_RowIndex++]} ); } } else { return; } } } #################### sub GetDBHandle { #print Dumper( $rh_Opts ); my $ConnectStr = "dbi:Proxy:hostname=$rh_Opts->{DBI_PROXY_HOST};" . "port=$rh_Opts->{DBI_PROXY_PORT};" . "dsn=dbi:ODBC:$rh_Opts->{DBI_PROXY_DSN}"; my $DB; eval { $DB = DBI->connect( $ConnectStr, $rh_Opts->{DBI_PROXY_USER}, $rh_Opts->{DBI_PROXY_PASS}, { RaiseError => 1, PrintError => 1 } ); }; if( $@ ) { die( "Error: failed to connect to the the database\n" . "Result was:\n$DBI::errstr\n" . $ConnectStr ); } else { return( $DB ); } } #################### sub ParseCommandLineOptions { GetOptions( 'host=s' => \$rh_Opts->{DBI_PROXY_HOST}, 'port=i' => \$rh_Opts->{DBI_PROXY_PORT}, 'dsn=s' => \$rh_Opts->{DBI_PROXY_DSN}, 'user=s' => \$rh_Opts->{DBI_PROXY_USER}, 'pass=s' => \$rh_Opts->{DBI_PROXY_PASS}, 'quiet!' => \$rh_Opts->{QUIET_LAUNCH}, 'outfile=s' => \$rh_Opts->{TRANSCRIPT_FILE}, 'field=s' => \$rh_Opts->{FIELD_DELIMITER}, 'row=s' => \$rh_Opts->{ROW_DELIMITER}, 'result_mode=s' => \$rh_Opts->{RESULT_MODE} ); } #################### sub ParseEnvOptions { my $Arg; foreach $Arg ( @ALLOWED_ARGS ) { if( defined( $ENV{$Arg} ) ) { $rh_Opts->{$Arg} = $ENV{$Arg}; } } } #################### sub PromptForMissingOptions { my $Arg; foreach $Arg ( @REQUIRED_ARGS ) { if( !defined( $rh_Opts->{$Arg} ) ) { my $Prompt = "$Arg > "; $rh_Opts->{$Arg} = $Term->readline( $Prompt ); } } # prompt for password, if necessary if( !$rh_Opts->{DBI_PROXY_PASS} ) { print "password: "; # can this be done with Term? Probably. system( "stty -echo" ); $rh_Opts->{DBI_PROXY_PASS} = ; chomp( $rh_Opts->{DBI_PROXY_PASS} ); system( "stty echo" ); Display( "\n" ); } } #################### sub Display { my $str = shift; my $OUT = $Term->OUT; if( $OUT ) { print $OUT $str; } else { print STDOUT $str; } } #################### sub GetFieldWidths { my $ra_Data = shift; my $ra_Row; my $i; my $Width; my $ra_Widths; # initialize widths $ra_Row = $ra_Data->[0]; for( $i = 0; $i <= $#{$ra_Row}; $i++ ) { $ra_Widths->[$i] = 0; } # grow widths by walking data foreach $ra_Row (@{$ra_Data}) { for( $i = 0; $i <= $#{$ra_Row}; $i++ ) { if( !defined( $ra_Row->[$i] ) ) { $ra_Row->[$i] = ""; } $Width = length( $ra_Row->[$i] ); if( $ra_Widths->[$i] < $Width ) { $ra_Widths->[$i] = $Width; } } } return $ra_Widths; } #################### sub BuildQuery { my $Prompt = $MAIN_PROMPT; my $Query = ""; while( 1 ) { # get next line my $Line = $Term->readline( $Prompt ); next if( $Line =~ /^\s*$/ ); if( $Line =~ /;$/ ) { $Query .= " $Line "; last; } elsif( $Line =~ /^:?go$/i ) { last; } elsif( $Line =~ /^\\g$/i ) { last; } elsif( $Line =~ /^:?(exit|bye|quit)$/i ) { $Query = $Line; last; } elsif( $Line =~ /^:?help/i ) { Help(); } elsif( $Line =~ /^:set\s+(.*)$/ ) { ProcessSetCommand( $1 ); } elsif( $Line =~ /^:?(reset|clear)$/i ) { $Query = ""; $Prompt = $MAIN_PROMPT; } elsif( $Line =~ /^:?view$/i ) { Display( $Query . "\n" ); } else { $Query .= " $Line "; $Prompt = $CONT_PROMPT; } } return $Query; } #################### sub Help { my $Help = <' prompt. Multiline commands may be wrapped and continued at the '-->' prompt. To execute a command, end it with ';', or type 'go' or '\\g' at the next line. Commands: (all may be prefixed by ":", ie ":view", for clarity) bye exit program clear clear/reset current query exit synonym for "bye" help this screen go run current query (shortcut: \\g) quit synonym for "bye" reset synonym for "clear" set set internal variable (see below) view print current query Set variables: (set with ":set var value"; unset with ":set novar" or empty value) column_delimiter string to separate fields when output is directed to a transcript file; defaults to tab ("\\t") column_separator synonym for "column_delimiter" field_delimiter synonym for "column_delimiter" field_separator synonym for "column_delimiter" format synonym for "result_mode" grep perl regexp applied to result set; only matching rows are displayed/written. log redirects result sets from console to a file logfile synonym for "log" outfile synonym for "log" output synonym for "log" mode synonym for "result_mode" result_mode When set to "data, rec, record, list, detail, or zoom," all result sets are shown in a "name: value" list of lists. Default behavior can be restored with "tab, table, col, column, columns, row, or rows". Can also take a value of "HTML", which outputs data in ()+ sets. row_delimitor string to separate records when output is directed to a transcript file; defaults to newline ("\\n") row_separator synonym for row_delimiter transcript synonym for "log" Translated queries: The following queries are completely re-written by the yasql engine from their mysql-like representations to M\$SQL-compatible versions. The translated versions are displayed at runtime for your edification. SHOW TABLES SHOW DATABASES SHOW VIEWS SHOW PROCS DESCRIBE [TABLE] SELECT ... LIMIT n EOP Display( $Help ); } #################### sub ProcessSetCommand { my $Command = shift; my ($Name, undef, $Value) = ( $Command =~ /^(\S+)(\s+(.*))?$/ ); if( $Name =~ /^(no)?outfile|log|logfile|output|transcript$/ ) { if( !$1 && $Value =~ /\S/ ) { $rh_Opts->{TRANSCRIPT_FILE} = $Value; if( open( OUTFILE, ">$rh_Opts->{TRANSCRIPT_FILE}" ) ) { close( OUTFILE ); } else { warn( "Can't create $rh_Opts->{TRANSCRIPT_FILE}: $!" ); $rh_Opts->{TRANSCRIPT_FILE} = ""; } } else { $rh_Opts->{TRANSCRIPT_FILE} = ""; } } elsif( $Name =~ /^(no)?grep$/i ) { if( !$1 && $Value =~ /\S/ ) { $rh_Opts->{GREP} = $Value; } else { $rh_Opts->{GREP} = ""; } } elsif( $Name =~ /^(no)?(field|col|column)_(delim|delimiter|sep|separator)$/i ) { if( !$1 && $Value =~ /\S/ ) { $rh_Opts->{FIELD_DELIMITER} = $Value; $rh_Opts->{FIELD_DELIMITER} = Normalize( $Value ); } else { $rh_Opts->{FIELD_DELIMITER} = ""; } } elsif( $Name =~ /^(no)?row_(delim|delimiter|sep|separator)$/i ) { if( !$1 && $Value =~ /\S/ ) { $rh_Opts->{ROW_DELIMITER} = Normalize( $Value ); } else { $rh_Opts->{ROW_DELIMITER} = ""; } } elsif( $Name =~ /^(result(_mode)?)|format|mode$/ ) { if( $Value =~ /^tab|table|col|column|columns|row|rows$/i ) { $rh_Opts->{RESULT_MODE} = 'table'; } elsif( $Value =~ /^data|rec|record|list|detail|zoom$/i ) { $rh_Opts->{RESULT_MODE} = 'record'; } elsif( $Value =~ /^html$/i ) { $rh_Opts->{RESULT_MODE} = 'html'; } else { warn( "Invalid value for result_mode: $Value" ); } } else { warn( "Unknown :set option ('$Name')" ); } } #################### sub Normalize { my $Value = shift; $Value =~ s/\\t/\t/g; $Value =~ s/\\n/\n/gs; return $Value; } #################### sub TranslateQuery { my $Query = shift; my $regexp; foreach $regexp (keys %{$rh_TranslatedQueries}) { if( $Query =~ /$regexp/i ) { my $Save_1 = $1; my $Save_2 = $2; my $Save_3 = $3; $Query = $rh_TranslatedQueries->{$regexp}; $Query =~ s/\$1/$Save_1/g; $Query =~ s/\$2/$Save_2/g; $Query =~ s/\$3/$Save_3/g; Display( "translated query: $Query\n" ); last; } } return $Query; } #################### sub BuildFakeColumnTypes { my $ra_Names = shift; my $ra_Types = []; my $Name; foreach $Name (@{$ra_Names}) { push( @{$ra_Types}, "char" ); } return $ra_Types; }
$ra_Row->[$i]
" . ( join( "<\/td>\n\t", @{$ra_Row} ) ) . "