dbishell - a generic database shell based on the perl DBI layer

  dbishell [ --driver drivername   ]
           [ --dsn dsn_name        ]
           [ --user username       ]
           [ --pass password       ]
           [ --shell-driver Driver ]
           [ --dotdir directory    ]

dbishell is a generic database shell based on the perl DBI layer. It provides all the functionality of a database specific shell such as sqlplus or mysql, but in a database independant manner. In some cases, it will be significantly more advanced than the command line database shell supplied by your database vendor.

This is the perl DBD driver you want to use. If you do not supply this, dbishell will ask you for one. Some examples are:

mysql

Oracle

Sybase

ODBC

Pg

This is the DSN you want to use. The DSN is the 'thing' which contains the information required by the database client libraries in order to locate and attempt to connect to the database server. The syntax of these DSNs is database specific and sometimes confusing - for example, the 'hostname' in Sybase DSN syntax does not specify the host to which you wish to connect. It specifies the name which Sybase will use as the client's name when it needs it.

If the perl DBI DSN for a database is 'dbi:mysql:FOO' then the dbishell dsn is just 'FOO'.

If a dsn is not supplied, dbishell will ask for one.

The username you wish to supply to the database while connecting

If a username is not supplied, dbishell will ask for one.

The password you wish to supply while connecting. This is provided for convenience, but I do not recommend using it. If no password is supplied, one will be asked for.

If the name of the required dbishell driver cannot be inferred from the --driver option [eg if you are using the DBD::ODBC driver, or are using the Sybase driver to connect to an MS SQL database] then you may specify the name of the dbishell driver here. Not used often.

The path to the directory where dbishell should look for its dotfiles.

dbishell considers SQL commands to be ready for interpretation when a '/' character is encountered at the end of a line. This character was chosen for compatability with Oracle reasons, but can be configured.

Context sensetive tab completion is available, dbishell examines the preceding word to establish a context, so in cases where this provides insufficient information, dbishell falls back to 'dumb' completion.

dbishell allows you to set and use variables and environment variables in your commands [no scripting yet, though].

Setting Variables:

To set a variable, use the following syntax:

  prompt> $FOO=some value here

Quotes will not be treated specially, '\n' and '\t' will be expanded to newlines and tabs, and variables on the RHS will be interpolated before setting the LHS.

String Interpolation:

Variables of the form CW$NAME are interpolated directly into your SQL command before being sent to the database for interpretation, thus the database will be unaware that a variable was even used.

Bound Parameters:

Variables in your SQL statement in the forms:

   $>NAME
   $<NAME

Will be interpreted as input and input/output parameters respectively. All parameters wil be bound as type VARCHAR. Note that your database [or at least your DBD driver] must support bound parameters for this to work, and not all parts of a query can have parameters bound into them.

There are 2 special categories of variables:

Environment Variables:

These take the form CW$env:NAME, and allow access to the environment. They carry no special restrictions that I am aware of, over and above any imposed by the environment itself.

DBI Connection Parameters:

These take the form CW$dbi:NAME, and correspond to the DBI connection parameters, such as AutoCommit and LongReadLen. You probably shouldn't use them for anything else, such as bound parameters.

Other Important Variables:

$FIELD_SEPARATOR

Contains the character sequence used to separate fields in output. If unset, you get '|' as the separator.

$PRESCAN_FORMAT

If true, [cf. Perl What is truth] then the whole of the dataset returned by all queries is prescanned, and the output formatted accordingly This can be helpful when you have large columns which typically hold values much shorter than they have the capacity for, since it shrinks the display columns down to the size of the largest output value on a per column basis. On the other hand, if you're fetching large numbers of rows, This may hurt, since you'll be prefetching the whole dataset into memory, scanning it, and then printing it.

$TRUNCATE_COLUMN_NAMES

If false, a column will never be narrower than its name in the output. Othewise, column names will be shortened to fit their columns display widths [if PRESCAN_ROWS is set]

$CASE_SENSITIVE

Is the DB case sensitive or not? [mainly effects tab completion]

$PAGER

The command to open a pipe to, to use as a pager. If CW$PAGER is unset, CW$env:PAGER is tried instead, and if there's nothing there, 'less -S' is used instead. If that doesn't work, then bad things probably happen. Like paging not working, and maybe your output disappearing.

$PAGING

If this is true, then output from certain commands [just selects at the moment] will be paged, depending on the value of CW$PAGE_SIZE and CW$PRESCAN_FORMAT

$PAGE_SIZE

The number of rows above which dbishell will try to page output. Paging is decided as follows: If CW$PAGING is true, and CW$PRESCAN_FORMAT is true, output exceeding CW$PAGE_SIZE [or exceeding the size of the terminal, if CW$PAGE_SIZE is 0 or unset] will be paged. If CW$PRESCAN_FORMAT is false, and CW$PAGING is true, then output will be paged regardless of the amount of data returned, unless the DBI driver can determine the number of rows before they are all fetched.

$EOL

The end of line character used by dbishell to determine when you want a command executed. If unset [the default] then '/' is used. [except that the '/' that terminates a comment will be ignored]. Otherwise, whatever you put in CW$EOL will be scanned for.

dbishell implements a number of commands, which although not part of the SQL standard, are very useful to have:

help

help TOPIC

Display the help for a particular command or keyword, or the dfault help if nothing appropriate is found.

describe

describe THING

Display a description of the database object [usually a table] specified. Some drivers can also describe indices, views, procedures etc...

read

read FILEPATH

Read in the file specified, as if the user was typing its contents in at the prompt.

interpolation

interpolation on|off

Turn on or off variable interpolation.

escaping

escaping on|off

Turn on or off backslash escape interpretation.

show

show tables|views|THING|etc...

Give some information about the keyword in question [eg list the tables, dump a create statement, show the SQL source for a procedure, that sort of thing]

subshell

!SUBSHELL COMMAND

If the first non whitespace is a '!', then your command is passed to a subshell for interpretation. You can even launch dbishell from within itself this way.

cd

Change the working directory.

spool

spool input|output|error|stderr|stdin|stdout FILEPATH on|off

Turn on or off logging of the relevant data stream to the file specified by FILEPATH.

license

license

Display the license.

quit

quit

Some examples of starting DBIShell are given here:

  dbishell --driver Oracle             \
           --dsn host=foobar\;sid=argh \
           --user=scott

  dbishell --driver mysql                 \
           --dsn    host=narf\;database=argh \
           --user   vivek

  dbishell --driver Sybase                    \
           --dsn hostname=narf\;database=argh \
           --user=sa

  dbishell --driver ODBC          \
           --dsn FOO:             \
           --shell-driver=Sybase  \
           --user sa

The README file in the DBIShell distribution.

Vivek Dasmohapatra (vivek@etla.org)