Rechercher une page de manuel
SQL::Shell.3pm
Langue: en
Version: 2006-12-05 (fedora - 01/12/10)
Section: 3 (Bibliothèques de fonctions)
Sommaire
NAME
SQL::Shell - command interpreter for DBI shellsSYNOPSIS
use SQL::Shell; #Initialise and configure my $sqlsh = new SQL::Shell(\%settings); $sqlsh->set($setting, $new_value); $value = $sqlsh->get($setting); #Interpret commands $sqlsh->execute_command($command); $sqlsh->run_script($filename);
DESCRIPTION
SQL::Shell is a command-interpreter API for building shells and batch scripts. A command-line interface with readline support - sqlsh.pl - is included as part of the CPAN distribution. See <SQL::Shell::Manual> for a user guide.SQL::Shell offers features similar to the mysql or sql*plus client programs but is database independent. The default command syntax is arguably more user-friendly than dbish not requiring any go, do or slashes to fire SQL statements at the database.
Features include:
- *
- issuing common SQL statements by simply typing them
- *
- command history
- *
- listing drivers, datasources, tables
- *
- describing a table or the entire schema
- *
- dumping and loading data to/from delimited text files
- *
- character set conversion when loading data
- *
- logging of queries, results or all commands to file
- *
- a number of formats for display/logging data (sql, xml, delimited, boxed)
- *
- executing a series of commands from a file
You can also install custom commands, rendering formats and command history mechanisms. All the commands run by the interpreter are available via the API so if you don't like the default command syntax you can replace the command regexes with your own.
It's been developed and used in anger with Oracle and mysql but should work with any database with a DBD:: driver.
METHODS
- $sqlsh = new SQL::Shell(\%settings);
- Constructs a new object and initialises it with a set of settings. See ``SETTINGS'' for a complete list.
- $sqlsh->set($setting, $new_value)
- Changes a setting once the object has been constructed. See ``SETTINGS'' for a complete list.
- $value = $sqlsh->get($setting)
- Fetches a setting. See ``SETTINGS'' for a complete list.
Commands
- $sqlsh->execute_cmd($command)
- Executes a command ($command is a string).
Returns 1 if the command was successful. Returns 0 if the command was unsuccessful.
- $sqlsh->run_script($filename)
- Executes a sequence of commands in a file. Dies if there is a problem.
- $sqlsh->install_cmds(\%additional_commands)
- %additional_commands should contain a mapping of regex to coderef. See ``INSTALLING CUSTOM COMMANDS'' for more information.
- $sqlsh->uninstall_cmds(\@commands)
- @additional_commands should contain a list of regexes to remove. If uninstall_cmds is called with no arguments, all commands will be uninstalled.
- $sqlsh->set_param($param, $value)
- Equivalent to the ``set <param> <value>'' command. In many cases this will affect the internal settings accessible through the "set" and "get" methods.
Renderers
- $sqlsh->install_renderers(\%additional_renderers)
- %additional_renderers should contain a mapping of renderer name to coderef. See ``INSTALLING CUSTOM RENDERERS'' for more information.
- $sqlsh->uninstall_renderers(\@renderers)
- @renderers should contain a list of renderer names to remove. If uninstall_renderers is called with no arguments, all renderers will be uninstalled.
- $sqlsh->render_rowset(\@headers, \@data, $table)
- Calls the current renderer (writes to STDOUT)
- $sqlsh->log_rowset(\@headers, \@data, $table)
- Calls the current logger
Database connection
- $dsn = $sqlsh->connect($dsn, $user, $pass)
- Connects to a DBI datasource. Equivalent to issuing the ``connect $dsn $user $pass'' command.
- $sqlsh->disconnect()
- Disconnects if connected. Equivalent to issuing the ``disconnect'' command.
- $bool = $sqlsh->is_connected()
- Check if we're connected to the database.
- $string = $sqlsh->dsn()
- The datasource we're currently connected as - undef if not connected.
History manipulation
- $arrayref = $sqlsh->load_history($filename)
- Loads a sequence of commands from a file into the command history. Equivalent to ``load history from $filename''.
- $sqlsh->clear_history()
- Clears the command history. Equivalent to ``clear history''.
- $sqlsh->save_history($filename, $size)
- Saves the command history to a file in a format suitable for "load_history" and "run_script". Equivalent to ``save history to $filename'', except the maximum number of items can be specified. $size is optional - if not specified defaults to the MaxHistory setting.
- $sqlsh->show_history()
- Displays the command history. Equivalent to ``show history''.
Logging
- $sqlsh->enable_logging($level, $file)
- Enables logging to a file. $level should be all, queries or commands. Equivalent to ``log $level $file''.
- $sqlsh->disable_logging()
- Disables logging to a file. Equivalent to ``no log''.
Querying
- $sqlsh->show_drivers()
- Outputs a list of database drivers. Equivalent to ``show drivers''.
- $sqlsh->show_datasources($driver)
- Outputs a list of datasources for a driver. Equivalent to ``show datasources $driver''.
- $sqlsh->show_dbh($property)
- Outputs a property of a database handle. Equivalent to ``show \$dbh $property''.
- $sqlsh->show_schema()
- Equivalent to ``show schema''.
- $sqlsh->show_tables()
- Displays a list of tables with row counts. Equivalent to ``show tables''.
- $sqlsh->describe($table)
- Displays the columns in the table. Equivalent to ``describe $table''.
- $sqlsh->run_query($sql)
- Displays the rowset returned by the query. Equivalent to execute_cmd with a select or explain statement.
Modifying data
- $sqlsh->do_sql($sql)
- Executes a SQL statement that modifies the database. Equivalent to execute_cmd with a DML or DDL statement.
- $sqlsh->begin_work()
- Starts a transaction. Equivalent to ``begin work''.
- $sqlsh->commit()
- Commits a transaction. Equivalent to ``commit''.
- $sqlsh->rollback()
- Rolls back a transaction. Equivalent to ``rollback''.
- $sqlsh->wipe_tables()
- Blanks all the tables in the database. Will prompt for confirmation if the Interactive setting is enabled. Equivalent to ``wipe tables''.
Loading and dumping data
- $sqlsh->dump_data($source, $filename, $delimiter)
- Dumps data from a table or query into a delimited file. $source should either be a table name or a select query. This is equivalent to the ``dump data'' command.
- $sqlsh->load_data($filename, $table, $delimiter, $uri_decode, $charset_from, $charset_to)
- Loads data from a delimited file into a database table. $uri_decode is a boolean value - if true the data will be URI-decoded before being inserted. $charset_from and $charset_to are character set names understood by Locale::Recode. This is equivalent to the ``load data'' command.
- $sqlsh->show_charsets()
- Lists the character sets supported by the recoding feature of ``load data''. Equivalent to ``show charsets''.
CUSTOMISING
INSTALLING CUSTOM COMMANDS
The coderef will be passed the $sqlsh object followed by each argument captured by the regex.my %additional_commands = ( qr/^hello from (\.*)/ => sub { my ($self, $name) = @_; print "hi there $name\n"; });
To install this:
$sqlsh->install_cmds(\%additional_commands)
Then in sqlsh:
> hello from John hi there John
INSTALLING CUSTOM RENDERERS
Renderers are coderefs which are passed the following arguments:$sqlsh - the SQL::Shell object $fh - the filehandle to render to $headers - an arrayref of column headings $data - an arrayref of arrays containing the data (row major) $table - the name of the table being rendered (not defined in all contexts)
Here's an example to render data in CSV format:
sub my_renderer { my ($sqlsh, $fh, $headers, $data, $table) = @_; my $delim = ","; print $fh "#Dump of $table" if($table); #Assuming our CSV format support #-style comments print $fh join($delim, @$headers)."\n"; foreach my $row (@$data) { print $fh join($delim, @$row)."\n"; } }
To install this:
$sqlsh->install_renderers({'csv' => \&my_renderer});
Then in sqlsh:
> set display-mode csv
INSTALLING A CUSTOM HISTORY MECHANISM
You can install a custom history recording mechanism by overriding the GetHistory, SetHistory and AddHistory callbacks which should take the following arguments and return values:- $arrayref = $GetHistorySub->()
- $SetHistorySub->($arrayref)
- $AddHistorySub->($string)
An example:
my $term = new Term::ReadLine "My Shell"; my $autohistory = $term->Features()->{autohistory}; my $sqlsh = new SQL::Shell({ 'GetHistory' => sub {[$term->GetHistory()]}); 'SetHistory' => sub {my $history = shift; $term->SetHistory(@$history)}); 'AddHistory' => sub {my $cmd = shift; $term->addhistory($cmd) if !$autohistory}); });
SETTINGS
The following settings can only be set through the constructor or the "set" method:NAME DESCRIPTION DEFAULT GetHistory Callback to fetch history sub {return \@history} SetHistory Callback to set history sub {my $n = shift; @history = @$n} AddHistory Callback to add cmd to history sub {push @history, shift()} MaxHistory Maximum length of history to save $ENV{HISTSIZE} || $ENV{HISTFILESIZE} || 50 Interactive Should SQL::Shell ask questions? 0 Verbose Should SQL::Shell print messages? 0 NULL How to display null values NULL
The following are also affected by the "set_param" method or the ``set'' command:
NAME DESCRIPTION DEFAULT Renderer Current renderer for screen \&_render_box Logger Current renderer for logfile \&_render_delimited Delimiter Delimiter for delimited format \t Width Width used for record display 80 LogLevel Log what? all|commands|queries undef EscapeStrategy UriEscape|EscapeWhitespace|ShowWhitespace undef AutoCommit Commit each statement 0 LongTruncOk OK to truncate LONG datatypes? 1 LongReadLen Amount read from LONG datatypes 512 MultiLine Allows multiline sql statements 0
COMMANDS
show drivers show datasources <driver> connect <dsn> [<user> <pass>] - connect to DBI DSN disconnect - disconnect from the DB show tables - display a list of tables show schema - display the entire schema desc <table> - display schema of table show $dbh <attribute> - show a database handle object. some examples: show $dbh Name show $dbh LongReadLen show $dbh mysql_serverinfo (mysql only) set display-mode delimited|spaced|box|record|sql|xml - query display mode set log-mode delimited|spaced|box|record|sql|xml - set the query log mode set delimiter <delim> - set the column delimiter (default is tab) set escape show-whitespace|escape-whitespace|uri-escape|off - show-whitespace is just for looking at - escape-whitespace is compatible with enter-whitespace - uri-escape is compatible with uri-decode (load command) set enter-whitespace on|off - allow \r \n and \t in SQL statements set uri-encode on|off - allow all non ascii characters to be escaped set auto-commit on|off - commit after every statement (default is OFF) set longtruncok on|off - See DBI/LongTruncOk (default is on) set longreadlen <int> - See DBI/LongReadLen (default is 512) set multiline on|off - multiline statements ending in ; (default is off) set tracing on|off|deep - debug sqlsh using Log::Trace (default is off) log (queries|commands|all) <filename> - start logging to <filename> no log - stop logging select ... insert ... update ... create ... alter ... drop ... grant ... revoke ... begin_work commit rollback load <file> into <table> (delimited by foo) (uri-decode) (from bar to baz) - load delimited data from a file - use uri-decode if file includes uri-encoded data - from, to can take character set to recode data e.g. from CP1252 to UTF-8 show charsets - display available character sets dump <table> into <file> (delimited by foo) - dump delimited data dump <sql> into <file> (delimited by foo) - dump delimited data dump all tables into <directory> (delimited by foo) - dump delimited data wipe tables - remove all data from DB (leaving tables empty) show history - display command history clear history - erases the command history save history to <file> - saves the command history load history from <file> - loads the command history execute <file> - run a set of SQL or sqlsh commands from a file
VERSION
$Revision: 1.14 $ on $Date: 2006/12/05 14:31:33 $ by $Author: andreww $AUTHOR
John Alden with contributions by Simon Flack and Simon Stevenson <cpan _at_ bbc _dot_ co _dot_ uk>COPYRIGHT
(c) BBC 2006. This program is free software; you can redistribute it and/or modify it under the GNU GPL.See the file COPYING in this distribution, or http://www.gnu.org/licenses/gpl.txt
Contenus ©2006-2024 Benjamin Poulain
Design ©2006-2024 Maxime Vantorre