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_tables()
171 Displays a list of tables with row counts. Equivalent to "show
172 tables".
173 $sqlsh->describe($table)
175 Displays the columns in the table. Equivalent to "describe
176 $table".
177 $sqlsh->run_query($sql)
179 Displays the rowset returned by the query. Equivalent to
180 execute_cmd with a select or explain statement.
181 Modifying data
183 $sqlsh->do_sql($sql)
184 Executes a SQL statement that modifies the database. Equivalent to
185 execute_cmd with a DML or DDL statement.
186 $sqlsh->begin_work()
188 Starts a transaction. Equivalent to "begin work".
189 $sqlsh->commit()
191 Commits a transaction. Equivalent to "commit".
192 $sqlsh->rollback()
194 Rolls back a transaction. Equivalent to "rollback".
195 $sqlsh->wipe_tables()
197 Blanks all the tables in the database. Will prompt for
198 confirmation if the Interactive setting is enabled. Equivalent to
199 "wipe tables".
200 Loading and dumping data
202 $sqlsh->dump_data($source, $filename, $delimiter)
203 Dumps data from a table or query into a delimited file. $source
204 should either be a table name or a select query. This is
205 equivalent to the "dump data" command.
206 $sqlsh->load_data($filename, $table, $delimiter, $uri_decode,
208 $charset_from, $charset_to)
209 Loads data from a delimited file into a database table.
210 $uri_decode is a boolean value - if true the data will be URI-
211 decoded before being inserted. $charset_from and $charset_to are
212 character set names understood by Locale::Recode. This is
213 equivalent to the "load data" command.
214 $sqlsh->show_charsets()
216 Lists the character sets supported by the recoding feature of "load
217 data". Equivalent to "show charsets".
218
220 INSTALLING CUSTOM COMMANDS
221 The coderef will be passed the $sqlsh object followed by each argument
222 captured by the regex.
223 my %additional_commands = (
225 qr/^hello from (\.*)/ => sub {
226 my ($self, $name) = @_;
227 print "hi there $name\n";
228 });
229 To install this:
231 $sqlsh->install_cmds(\%additional_commands)
233 Then in sqlsh:
235 > hello from John
237 hi there John
238 INSTALLING CUSTOM RENDERERS
240 Renderers are coderefs which are passed the following arguments:
241 $sqlsh - the SQL::Shell object
243 $fh - the filehandle to render to
244 $headers - an arrayref of column headings
245 $data - an arrayref of arrays containing the data (row major)
246 $table - the name of the table being rendered (not defined in all contexts)
247 Here's an example to render data in CSV format:
249 sub my_renderer {
251 my ($sqlsh, $fh, $headers, $data, $table) = @_;
252 my $delim = ",";
253 print $fh "#Dump of $table" if($table); #Assuming our CSV format support #-style comments
254 print $fh join($delim, @$headers)."\n";
255 foreach my $row (@$data)
256 {
257 print $fh join($delim, @$row)."\n";
258 }
259 }
260 To install this:
262 $sqlsh->install_renderers({'csv' => \&my_renderer});
264 Then in sqlsh:
266 > set display-mode csv
268 INSTALLING A CUSTOM HISTORY MECHANISM
270 You can install a custom history recording mechanism by overriding the
271 GetHistory, SetHistory and AddHistory callbacks which should take the
272 following arguments and return values:
273 $arrayref = $GetHistorySub->()
275 $SetHistorySub->($arrayref)
276 $AddHistorySub->($string)
277 An example:
279 my $term = new Term::ReadLine "My Shell";
281 my $autohistory = $term->Features()->{autohistory};
282 my $sqlsh = new SQL::Shell({
283 'GetHistory' => sub {[$term->GetHistory()]});
284 'SetHistory' => sub {my $history = shift; $term->SetHistory(@$history)});
285 'AddHistory' => sub {my $cmd = shift; $term->addhistory($cmd) if !$autohistory});
286 });
287
289 The following settings can only be set through the constructor or the
290 "set" method:
291 NAME DESCRIPTION DEFAULT
293 GetHistory Callback to fetch history sub {return \@history}
294 SetHistory Callback to set history sub {my $n = shift; @history = @$n}
295 AddHistory Callback to add cmd to history sub {push @history, shift()}
296 MaxHistory Maximum length of history to save $ENV{HISTSIZE} || $ENV{HISTFILESIZE} || 50
297 Interactive Should SQL::Shell ask questions? 0
298 Verbose Should SQL::Shell print messages? 0
299 NULL How to display null values NULL
300 The following are also affected by the "set_param" method or the "set"
302 command:
303 NAME DESCRIPTION DEFAULT
305 Renderer Current renderer for screen \&_render_box
306 Logger Current renderer for logfile \&_render_delimited
307 Delimiter Delimiter for delimited format \t
308 Width Width used for record display 80
309 LogLevel Log what? all|commands|queries undef
310 EscapeStrategy UriEscape|EscapeWhitespace|ShowWhitespace undef
311 AutoCommit Commit each statement 0
312 LongTruncOk OK to truncate LONG datatypes? 1
313 LongReadLen Amount read from LONG datatypes 512
314 MultiLine Allows multiline sql statements 0
315
317 show drivers
318 show datasources <driver>
319 connect <dsn> [<user> <pass>] - connect to DBI DSN
320 disconnect - disconnect from the DB
321 show tables - display a list of tables
323 show schema - display the entire schema
324 desc <table> - display schema of table
325 show $dbh <attribute> - show a database handle object.
327 some examples:
328 show $dbh Name
329 show $dbh LongReadLen
330 show $dbh mysql_serverinfo (mysql only)
331 set display-mode delimited|spaced|box|record|sql|xml - query display mode
333 set log-mode delimited|spaced|box|record|sql|xml - set the query log mode
334 set delimiter <delim> - set the column delimiter (default is tab)
335 set escape show-whitespace|escape-whitespace|uri-escape|off
336 - show-whitespace is just for looking at
337 - escape-whitespace is compatible with enter-whitespace
338 - uri-escape is compatible with uri-decode (load command)
339 set enter-whitespace on|off - allow \r \n and \t in SQL statements
340 set uri-encode on|off - allow all non ascii characters to be escaped
341 set auto-commit on|off - commit after every statement (default is OFF)
342 set longtruncok on|off - See DBI/LongTruncOk (default is on)
343 set longreadlen <int> - See DBI/LongReadLen (default is 512)
344 set multiline on|off - multiline statements ending in ; (default is off)
345 set tracing on|off|deep - debug sqlsh using Log::Trace (default is off)
346 log (queries|commands|all) <filename> - start logging to <filename>
348 no log - stop logging
349 select ...
351 insert ...
352 update ...
353 create ...
354 alter ...
355 drop ...
356 grant ...
357 revoke ...
358 begin_work
359 commit
360 rollback
361 load <file> into <table> (delimited by foo) (uri-decode) (from bar to baz)
363 - load delimited data from a file
364 - use uri-decode if file includes uri-encoded data
365 - from, to can take character set to recode data e.g. from CP1252 to UTF-8
366 show charsets - display available character sets
367 dump <table> into <file> (delimited by foo) - dump delimited data
368 dump <sql> into <file> (delimited by foo) - dump delimited data
369 dump all tables into <directory> (delimited by foo) - dump delimited data
370 wipe tables - remove all data from DB (leaving tables empty)
371 show history - display command history
373 clear history - erases the command history
374 save history to <file> - saves the command history
375 load history from <file> - loads the command history
376 execute <file> - run a set of SQL or sqlsh commands from a file
377
379 $Revision: 1.14 $ on $Date: 2006/12/05 14:31:33 $ by $Author: andreww $
380
382 John Alden with contributions by Simon Flack and Simon Stevenson <cpan
383 _at_ bbc _dot_ co _dot_ uk>
384
386 (c) BBC 2006. This program is free software; you can redistribute it
387 and/or modify it under the GNU GPL.
388 See the file COPYING in this distribution, or
390 http://www.gnu.org/licenses/gpl.txt
391perl v5.28.0 2016-02-23 SQL::Shell(3)