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) programs, 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:

showMode

This variable controls the output of an SQL result table. Possible values are Box, Export, List, Table and HTML. These correspond to modes of the Data::ShowTable module with the exception of Export: This is handled by dbimon internally, as Data::ShowTable doesn't offer such a mode. The Export mode is well suited for exporting data to other database systems. See Data::ShowTable(3).

less

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

listWrapMargin

maxTableWidth

noEscape

These correspond to the variables $List_Wrap_Margin, $Max_Table_Width and $No_Escape of the Data::ShowTable module. See Data::ShowTable(3).

escapeChar

quoteChar

sepChar

For Export mode 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 and dbimon doesn't depend on them: All requests for these variables are braced with eval's so that dbimon can either insert defaults or refuse to execute the respective commands.

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.)

The array ref is passed to Data::ShowTable::ShowTable as the widths attribute.

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.

These values are displayed in the relshow database tables command.

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.

This array is passed to Data::ShowTable::ShowTable as the types argument.

@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 0 <> 1

(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),