1SQL::Shell(3) User Contributed Perl Documentation SQL::Shell(3)
2
6 SQL::Shell - command interpreter for DBI shells
7
9 use SQL::Shell;
10 #Initialise and configure
12 my $sqlsh = new SQL::Shell(\%settings);
13 $sqlsh->set($setting, $new_value);
14 $value = $sqlsh->get($setting);
15 #Interpret commands
17 $sqlsh->execute_command($command);
18 $sqlsh->run_script($filename);
19
21 SQL::Shell is a command-interpreter API for building shells and batch
22 scripts. A command-line interface with readline support - sqlsh.pl -
23 is included as part of the CPAN distribution. See <SQL::Shell::Manual>
24 for a user guide.
25 SQL::Shell offers features similar to the mysql or sql*plus client
27 programs but is database independent. The default command syntax is
28 arguably more user-friendly than dbish not requiring any go, do or
29 slashes to fire SQL statements at the database.
30 Features include:
32 • issuing common SQL statements by simply typing them
34 • command history
36 • listing drivers, datasources, tables
38 • describing a table or the entire schema
40 • dumping and loading data to/from delimited text files
42 • character set conversion when loading data
44 • logging of queries, results or all commands to file
46 • a number of formats for display/logging data (sql, xml, delimited,
48 boxed)
49 • executing a series of commands from a file
51 You can also install custom commands, rendering formats and command
53 history mechanisms. All the commands run by the interpreter are
54 available via the API so if you don't like the default command syntax
55 you can replace the command regexes with your own.
56 It's been developed and used in anger with Oracle and mysql but should
58 work with any database with a DBD:: driver.
59
61 $sqlsh = new SQL::Shell(\%settings);
62 Constructs a new object and initialises it with a set of settings.
63 See "SETTINGS" for a complete list.
64 $sqlsh->set($setting, $new_value)
66 Changes a setting once the object has been constructed. See
67 "SETTINGS" for a complete list.
68 $value = $sqlsh->get($setting)
70 Fetches a setting. See "SETTINGS" for a complete list.
71 Commands
73 $sqlsh->execute_cmd($command)
74 Executes a command ($command is a string).
75 Returns 1 if the command was successful. Returns 0 if the command
77 was unsuccessful.
78 $sqlsh->run_script($filename)
80 Executes a sequence of commands in a file. Dies if there is a
81 problem.
82 $sqlsh->install_cmds(\%additional_commands)
84 %additional_commands should contain a mapping of regex to coderef.
85 See "INSTALLING CUSTOM COMMANDS" for more information.
86 $sqlsh->uninstall_cmds(\@commands)
88 @additional_commands should contain a list of regexes to remove.
89 If uninstall_cmds is called with no arguments, all commands will be
90 uninstalled.
91 $sqlsh->set_param($param, $value)
93 Equivalent to the "set <param> <value>" command. In many cases
94 this will affect the internal settings accessible through the "set"
95 and "get" methods.
96 Renderers
98 $sqlsh->install_renderers(\%additional_renderers)
99 %additional_renderers should contain a mapping of renderer name to
100 coderef. See "INSTALLING CUSTOM RENDERERS" for more information.
101 $sqlsh->uninstall_renderers(\@renderers)
103 @renderers should contain a list of renderer names to remove. If
104 uninstall_renderers is called with no arguments, all renderers will
105 be uninstalled.
106 $sqlsh->render_rowset(\@headers, \@data, $table)
108 Calls the current renderer (writes to STDOUT)
109 $sqlsh->log_rowset(\@headers, \@data, $table)
111 Calls the current logger
112 Database connection
114 $dsn = $sqlsh->connect($dsn, $user, $pass)
115 Connects to a DBI datasource. Equivalent to issuing the "connect
116 $dsn $user $pass" command.
117 $sqlsh->disconnect()
119 Disconnects if connected. Equivalent to issuing the "disconnect"
120 command.
121 $bool = $sqlsh->is_connected()
123 Check if we're connected to the database.
124 $string = $sqlsh->dsn()
126 The datasource we're currently connected as - undef if not
127 connected.
128 History manipulation
130 $arrayref = $sqlsh->load_history($filename)
131 Loads a sequence of commands from a file into the command history.
132 Equivalent to "load history from $filename".
133 $sqlsh->clear_history()
135 Clears the command history. Equivalent to "clear history".
136 $sqlsh->save_history($filename, $size)
138 Saves the command history to a file in a format suitable for
139 "load_history" and "run_script". Equivalent to "save history to
140 $filename", except the maximum number of items can be specified.
141 $size is optional - if not specified defaults to the MaxHistory
142 setting.
143 $sqlsh->show_history()
145 Displays the command history. Equivalent to "show history".
146 Logging
148 $sqlsh->enable_logging($level, $file)
149 Enables logging to a file. $level should be all, queries or
150 commands. Equivalent to "log $level $file".
151 $sqlsh->disable_logging()
153 Disables logging to a file. Equivalent to "no log".
154 Querying
156 $sqlsh->show_drivers()
157 Outputs a list of database drivers. Equivalent to "show drivers".
158 $sqlsh->show_datasources($driver)
160 Outputs a list of datasources for a driver. Equivalent to "show
161 datasources $driver".
162 $sqlsh->show_dbh($property)
164 Outputs a property of a database handle. Equivalent to "show \$dbh
165 $property".
166 $sqlsh->show_schema()
168 Equivalent to "show schema".
169 $sqlsh->show_objects()
171 Displays a list of tables, schemas, catalogs or table-types
172 depending on the object argument passed.
173 $sqlsh->show_tablecounts()
175 Displays a list of tables with row counts. Equivalent to "show
176 tablecounts".
177 $sqlsh->show_settings()
179 Displays a list of internal "sqlsh" settings. Equivalent to "show
180 settings". Not all internal settings are included here yet.
181 $sqlsh->describe($table)
183 Displays the columns in the table. Equivalent to "describe
184 $table".
185 $sqlsh->run_query($sql)
187 Displays the rowset returned by the query. Equivalent to
188 execute_cmd with a select or explain statement.
189 Modifying data
191 $sqlsh->do_sql($sql)
192 Executes a SQL statement that modifies the database. Equivalent to
193 execute_cmd with a DML or DDL statement.
194 $sqlsh->begin_work()
196 Starts a transaction. Equivalent to "begin work".
197 $sqlsh->commit()
199 Commits a transaction. Equivalent to "commit".
200 $sqlsh->rollback()
202 Rolls back a transaction. Equivalent to "rollback".
203 $sqlsh->wipe_tables()
205 Blanks all the tables in the database. Will prompt for
206 confirmation if the Interactive setting is enabled. Equivalent to
207 "wipe tables".
208 Loading and dumping data
210 $sqlsh->dump_data($source, $filename, $delimiter)
211 Dumps data from a table or query into a delimited file. $source
212 should either be a table name or a select query. This is
213 equivalent to the "dump data" command.
214 $sqlsh->load_data($filename, $table, $delimiter, $uri_decode,
216 $charset_from, $charset_to)
217 Loads data from a delimited file into a database table.
218 $uri_decode is a boolean value - if true the data will be URI-
219 decoded before being inserted. $charset_from and $charset_to are
220 character set names understood by Locale::Recode. This is
221 equivalent to the "load data" command.
222 $sqlsh->show_charsets()
224 Lists the character sets supported by the recoding feature of "load
225 data". Equivalent to "show charsets".
226
228 INSTALLING CUSTOM COMMANDS
229 The coderef will be passed the $sqlsh object followed by each argument
230 captured by the regex.
231 my %additional_commands = (
233 qr/^hello from (\.*)/ => sub {
234 my ($self, $name) = @_;
235 print "hi there $name\n";
236 });
237 To install this:
239 $sqlsh->install_cmds(\%additional_commands)
241 Then in sqlsh:
243 > hello from John
245 hi there John
246 INSTALLING CUSTOM RENDERERS
248 Renderers are coderefs which are passed the following arguments:
249 $sqlsh - the SQL::Shell object
251 $fh - the filehandle to render to
252 $headers - an arrayref of column headings
253 $data - an arrayref of arrays containing the data (row major)
254 $table - the name of the table being rendered (not defined in all contexts)
255 Here's an example to render data in CSV format:
257 sub my_renderer {
259 my ($sqlsh, $fh, $headers, $data, $table) = @_;
260 my $delim = ",";
261 print $fh "#Dump of $table" if($table); #Assuming our CSV format support #-style comments
262 print $fh join($delim, @$headers)."\n";
263 foreach my $row (@$data)
264 {
265 print $fh join($delim, @$row)."\n";
266 }
267 }
268 To install this:
270 $sqlsh->install_renderers({'csv' => \&my_renderer});
272 Then in sqlsh:
274 > set display-mode csv
276 INSTALLING A CUSTOM HISTORY MECHANISM
278 You can install a custom history recording mechanism by overriding the
279 GetHistory, SetHistory and AddHistory callbacks which should take the
280 following arguments and return values:
281 $arrayref = $GetHistorySub->()
283 $SetHistorySub->($arrayref)
284 $AddHistorySub->($string)
285 An example:
287 my $term = new Term::ReadLine "My Shell";
289 my $autohistory = $term->Features()->{autohistory};
290 my $sqlsh = new SQL::Shell({
291 'GetHistory' => sub {[$term->GetHistory()]});
292 'SetHistory' => sub {my $history = shift; $term->SetHistory(@$history)});
293 'AddHistory' => sub {my $cmd = shift; $term->addhistory($cmd) if !$autohistory});
294 });
295
297 The following settings can only be set through the constructor or the
298 "set" method:
299 NAME DESCRIPTION DEFAULT
301 GetHistory Callback to fetch history sub {return \@history}
302 SetHistory Callback to set history sub {my $n = shift; @history = @$n}
303 AddHistory Callback to add cmd to history sub {push @history, shift()}
304 MaxHistory Maximum length of history to save $ENV{HISTSIZE} || $ENV{HISTFILESIZE} || 50
305 Interactive Should SQL::Shell ask questions? 0
306 Verbose Should SQL::Shell print messages? 0
307 NULL How to display null values NULL
308 The following are also affected by the "set_param" method or the "set"
310 command:
311 NAME DESCRIPTION DEFAULT
313 Renderer Current renderer for screen \&_render_box
314 Logger Current renderer for logfile \&_render_delimited
315 Delimiter Delimiter for delimited format \t
316 Width Width used for record display 80
317 LogLevel Log what? all|commands|queries undef
318 EscapeStrategy UriEscape|EscapeWhitespace|ShowWhitespace undef
319 AutoCommit Commit each statement 0
320 LongTruncOk OK to truncate LONG datatypes? 1
321 LongReadLen Amount read from LONG datatypes 512
322 MultiLine Allows multiline sql statements 0
323
325 show drivers
326 show datasources <driver>
327 connect <dsn> [<user> <pass>] - connect to DBI DSN
328 disconnect - disconnect from the DB
329 show tables - display a list of tables
331 show catalogs - display a list of catalogs
332 show schemas - display a list of schemas
333 show tabletypes - display a list of tabletypes
334 show schema - display the entire schema
335 show settings - display some internal settings
336 desc <table> - display schema of table
337 show $dbh <attribute> - show a database handle object.
339 some examples:
340 show $dbh Name
341 show $dbh LongReadLen
342 show $dbh mysql_serverinfo (mysql only)
343 set display-mode delimited|spaced|box|record|sql|xml - query display mode
345 set log-mode delimited|spaced|box|record|sql|xml - set the query log mode
346 set delimiter <delim> - set the column delimiter (default is tab)
347 set escape show-whitespace|escape-whitespace|uri-escape|off
348 - show-whitespace is just for looking at
349 - escape-whitespace is compatible with enter-whitespace
350 - uri-escape is compatible with uri-decode (load command)
351 set enter-whitespace on|off - allow \r \n and \t in SQL statements
352 set uri-encode on|off - allow all non ascii characters to be escaped
353 set auto-commit on|off - commit after every statement (default is OFF)
354 set longtruncok on|off - See DBI/LongTruncOk (default is on)
355 set longreadlen <int> - See DBI/LongReadLen (default is 512)
356 set multiline on|off - multiline statements ending in ; (default is off)
357 set tracing on|off|deep - debug sqlsh using Log::Trace (default is off)
358 log (queries|commands|all) <filename> - start logging to <filename>
360 no log - stop logging
361 select ...
363 insert ...
364 update ...
365 create ...
366 alter ...
367 drop ...
368 grant ...
369 revoke ...
370 begin_work
371 commit
372 rollback
373 send ...
374 recv ...
375 load <file> into <table> (delimited by foo) (uri-decode) (from bar to baz)
377 - load delimited data from a file
378 - use uri-decode if file includes uri-encoded data
379 - from, to can take character set to recode data e.g. from CP1252 to UTF-8
380 show charsets - display available character sets
381 dump <table> into <file> (delimited by foo) - dump delimited data
382 dump <sql> into <file> (delimited by foo) - dump delimited data
383 dump all tables into <directory> (delimited by foo) - dump delimited data
384 wipe tables - remove all data from DB (leaving tables empty)
385 show history - display command history
387 clear history - erases the command history
388 save history to <file> - saves the command history
389 load history from <file> - loads the command history
390 execute <file> - run a set of SQL or sqlsh commands from a file
391
393 Version 1.17
394
396 John Alden with contributions by Simon Flack and Simon Stevenson <cpan
397 _at_ bbc _dot_ co _dot_ uk>
398 Miguel Gualdron maintainer.
400
402 SQL-Shell: Interactive shell for DBI Databases
403 Copyright (C) 2006 BBC
404 Copyright (C) 2019 Miguel Gualdron
405 This program is free software; you can redistribute it and/or modify
407 it under the terms of the GNU General Public License as published by
408 the Free Software Foundation; either version 2 of the License, or
409 (at your option) any later version.
410 This program is distributed in the hope that it will be useful,
412 but WITHOUT ANY WARRANTY; without even the implied warranty of
413 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
414 GNU General Public License for more details.
415 You should have received a copy of the GNU General Public License
417 along with this program; if not, write to the Free Software
418 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
419 See the file COPYING in this distribution, or
421 https://www.gnu.org/licenses/gpl-2.0.html
422perl v5.38.0 2023-07-21 SQL::Shell(3)