NAME

dbimon - interactive shell with readline for DBI

SYNOPSIS

dbimon <dsn> [<user> [<password>]]

DESCRIPTION

dbimon lets you talk to a running SQL server via the database independent Perl interface DBI. dbimon was inspired by Andreas Koenig's pmsql and borrows both design ideas and code from it. Thus the look and feel is almost identical to pmsql, in particular the following holds:

• The output is formatted much in the same way as by the msql or mysql monitor (see below), the msqlexport command and the relshow (mysqlshow) program, which are coming with msql or mysql.

• The additional capability is a connection to a readline interface (if available) and a pipe to your favorite pager.

• Additionally you may switch between hosts and databases within one session and you don't have to type the nasty \g or ; (a trailing \g, \q, and \p will be ignored).

If a command starts with one of the following reserved words, it's treated specially, otherwise it is passed on verbatim to the DBMS. Output from the daemon is piped to your pager specified by either the DBIMON_PAGER or the PAGER environment variable. If both are undefined, the PATH is searched for either "less" or "more" and the first program found is taken. If no pager can be determined or your pager variable is empty or set to stdout, the program writes to unfiltered STDOUT.

?

print usage summary

dsn <dsn>

Connects to the given <dsn>, the old connection is closed.

q[uit]

Leave dbimon.

re[lshow] [<dsn> [<table>]]

Without arguments this lists possible data sources by calling DBI's data_sources method. Data sources are driver dependent, the driver of the last connection will be used. Unfortunately DBI offers no possibilities of specifying a hostname or similar dsn attributes, so you can hardly list a remote hosts dsns, for example.

If a dsn is given, dbimon will connect to the given dsn and list its tables. If both dsn and table are present, dbimon will list the tables fields.

The latter possibilities are not supported by the DBI - these work with private methods. Currently they are implemented for DBD::mSQL and DBD::mysql.

se[t] [<var> [<val>]]

This command displays and modifies dbimon's internal variables. Without arguments, all variables and their current settings are listed. With a variable name only you query the variables value. The two argument form modifies a variable. Supported variables are:

fancyOutput

This variable controls the look of SQL results. By default dbimon creates fancy, formatted output with table borders. The alternative is an output format which is well suited for exporting data to other database systems.

less

This is the pager variable. You can turn off paging by setting this to 'stdout'.

maxColumnLength

maxRows

If the DBI driver does not offer information about the column sizes of a query result, dbimon will try to find these out. The first maxRows columns will be fetched and the output will be formatted according to the maximum column sizes that dbimon found so far. Columns will be truncated if they exceed maxColumnLength characters.

If you want dbimon to use the complete result for determining maximum column sizes, you can set maxRows to 0. (Default are 100 rows.) Likewise you can disable truncation by setting maxColumnLength to 0. (Default are 1024 characters.)

escapeChar

quoteChar

sepChar

For non-fancy output dbimon will use these variables. Columns are surrounded by the quoteChar, separated by the sepChar and the escapeChar is used for inserting these special characters. The defaults are well suited for Excel (escapeChar = ", quoteChar = " and sepChar = ;), thus a row with the values 1, 'walrus' and 'Nat "King" Cole' will be displayed as

"1";"walrus";"Nat ""King"" Cole"

! EXPR

Eval the EXPR in perl

Completion

dbimon comes with some basic completion definitions that are far from being perfect. Completion means, you can use the TAB character to run some lookup routines on the current dsn or table and use the results to save a few keystrokes.

The completion mechanism is very basic, and I'm not intending to refine it in the near future. Feel free to implement your own refinements and let me know, if you have something better than what we have here.

DRIVER REQUIREMENTS

dbimon should in theory work with any DBI driver. However, DBI is too restricted for really comfortable work. Thus I decided to use some very basic possibilities of the DBD::mSQL and DBD::mysql drivers. It should be easy to add these to any driver.

Anyways, all uses of the private methods is surrounded by eval's, thus most things should work. And the program is still usable.

I'll be happy to drop these additional requirements as soon as the DBI gets extended in an appropriate way and adopt my sources, but for now, here's what I suggest:

format_default_size

A statement handle attribute; reference to an array of values, one for each column. The values contain the actual maximum size of all values to be distinguished from the theroretical maximum. For example, if you have a column of type char(64), but the values actually have 30 characters or less, then the value 30 will be supplied by the driver.

The driver is free, not to implement this attribute or to implement it only for certain columns, but he must supply undef for other values.

DBD::mysql implements this by reading the max_length attribute of a result. DBD::mSQL does not offer such an attribute, but it holds the complete result in memory, so I could easily add the attribute by scanning all rows. (Perhaps faster than relying on dbimons builtin possibility.)

format_max_size

Similar to format_default_size, but this is the theoretical maximum size. Again, the driver is free not to implement this attribute or implement it for certain columns only, but he must supply undef for all other columns.

format_right_justify

Again, a statement handles attribute, reference to an array. It tells dbimon what kind of justification to use for a column. DBD::mSQL and DBD::mysql simply map this to IS_NUM. The driver should implement this attribute.

format_type_name

Reference to an array of type names; for example DBD::mSQL will insert the type name int for a column of type INT_TYPE. The driver should implement this attribute.

@list = $dbh->func("_ListTables")

Lists the tables of the dsn corresponding to $dbh.

$sth = $dbh->func($table, "_ListFields")

Returns a statement handle describing the fields of a table. This could, for example, be implemented by a

SELECT * FROM table WHERE column <> column

if you only know a column name. (Of course I can imagine better solutions ... :-)

SEE ALSO

You need a readline package installed to get the advantage of a readline interface. If you don't have it, you won't be able to use the arrow keys in a meaningful manner. Term::ReadKey and Term::ReadLine do not come with the perl distribution but are available from CPAN (see http://www.perl.com/CPAN).

See pmsql (1), DBI (3), Term::ReadKey (3), Term::ReadLine (3),