1SQL::Shell(3)         User Contributed Perl Documentation        SQL::Shell(3)
2
3
4

NAME

6       SQL::Shell - command interpreter for DBI shells
7

SYNOPSIS

9               use SQL::Shell;
10
11               #Initialise and configure
12               my $sqlsh = new SQL::Shell(\%settings);
13               $sqlsh->set($setting, $new_value);
14               $value = $sqlsh->get($setting);
15
16               #Interpret commands
17               $sqlsh->execute_command($command);
18               $sqlsh->run_script($filename);
19

DESCRIPTION

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
26       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
31       Features include:
32
33       ·   issuing common SQL statements by simply typing them
34
35       ·   command history
36
37       ·   listing drivers, datasources, tables
38
39       ·   describing a table or the entire schema
40
41       ·   dumping and loading data to/from delimited text files
42
43       ·   character set conversion when loading data
44
45       ·   logging of queries, results or all commands to file
46
47       ·   a number of formats for display/logging data (sql, xml, delimited,
48           boxed)
49
50       ·   executing a series of commands from a file
51
52       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
57       It's been developed and used in anger with Oracle and mysql but should
58       work with any database with a DBD:: driver.
59

METHODS

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
65       $sqlsh->set($setting, $new_value)
66           Changes a setting once the object has been constructed.  See
67           "SETTINGS" for a complete list.
68
69       $value = $sqlsh->get($setting)
70           Fetches a setting.  See "SETTINGS" for a complete list.
71
72   Commands
73       $sqlsh->execute_cmd($command)
74           Executes a command ($command is a string).
75
76           Returns 1 if the command was successful.  Returns 0 if the command
77           was unsuccessful.
78
79       $sqlsh->run_script($filename)
80           Executes a sequence of commands in a file.  Dies if there is a
81           problem.
82
83       $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
87       $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
92       $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
97   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
102       $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
107       $sqlsh->render_rowset(\@headers, \@data, $table)
108           Calls the current renderer (writes to STDOUT)
109
110       $sqlsh->log_rowset(\@headers, \@data, $table)
111           Calls the current logger
112
113   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
118       $sqlsh->disconnect()
119           Disconnects if connected.  Equivalent to issuing the "disconnect"
120           command.
121
122       $bool = $sqlsh->is_connected()
123           Check if we're connected to the database.
124
125       $string = $sqlsh->dsn()
126           The datasource we're currently connected as - undef if not
127           connected.
128
129   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
134       $sqlsh->clear_history()
135           Clears the command history.  Equivalent to "clear history".
136
137       $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
144       $sqlsh->show_history()
145           Displays the command history.  Equivalent to "show history".
146
147   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
152       $sqlsh->disable_logging()
153           Disables logging to a file.  Equivalent to "no log".
154
155   Querying
156       $sqlsh->show_drivers()
157           Outputs a list of database drivers. Equivalent to "show drivers".
158
159       $sqlsh->show_datasources($driver)
160           Outputs a list of datasources for a driver. Equivalent to "show
161           datasources $driver".
162
163       $sqlsh->show_dbh($property)
164           Outputs a property of a database handle.  Equivalent to "show \$dbh
165           $property".
166
167       $sqlsh->show_schema()
168           Equivalent to "show schema".
169
170       $sqlsh->show_tables()
171           Displays a list of tables with row counts.  Equivalent to "show
172           tables".
173
174       $sqlsh->describe($table)
175           Displays the columns in the table.  Equivalent to "describe
176           $table".
177
178       $sqlsh->run_query($sql)
179           Displays the rowset returned by the query.  Equivalent to
180           execute_cmd with a select or explain statement.
181
182   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
187       $sqlsh->begin_work()
188           Starts a transaction.  Equivalent to "begin work".
189
190       $sqlsh->commit()
191           Commits a transaction.  Equivalent to "commit".
192
193       $sqlsh->rollback()
194           Rolls back a transaction.  Equivalent to "rollback".
195
196       $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
201   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
207       $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
215       $sqlsh->show_charsets()
216           Lists the character sets supported by the recoding feature of "load
217           data".  Equivalent to "show charsets".
218

CUSTOMISING

220   INSTALLING CUSTOM COMMANDS
221       The coderef will be passed the $sqlsh object followed by each argument
222       captured by the regex.
223
224               my %additional_commands = (
225               qr/^hello from (\.*)/ => sub {
226                       my ($self, $name) = @_;
227                       print "hi there $name\n";
228               });
229
230       To install this:
231
232               $sqlsh->install_cmds(\%additional_commands)
233
234       Then in sqlsh:
235
236               > hello from John
237               hi there John
238
239   INSTALLING CUSTOM RENDERERS
240       Renderers are coderefs which are passed the following arguments:
241
242               $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
248       Here's an example to render data in CSV format:
249
250               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
261       To install this:
262
263               $sqlsh->install_renderers({'csv' => \&my_renderer});
264
265       Then in sqlsh:
266
267               > set display-mode csv
268
269   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
274       $arrayref = $GetHistorySub->()
275       $SetHistorySub->($arrayref)
276       $AddHistorySub->($string)
277
278       An example:
279
280               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

SETTINGS

289       The following settings can only be set through the constructor or the
290       "set" method:
291
292               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
301       The following are also affected by the "set_param" method or the "set"
302       command:
303
304               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

COMMANDS

317        show drivers
318        show datasources <driver>
319        connect <dsn> [<user> <pass>] - connect to DBI DSN
320        disconnect - disconnect from the DB
321
322        show tables - display a list of tables
323        show schema - display the entire schema
324        desc <table> - display schema of table
325
326        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
332        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
347        log (queries|commands|all) <filename> - start logging to <filename>
348        no log - stop logging
349
350        select ...
351        insert ...
352        update ...
353        create ...
354        alter ...
355        drop ...
356        grant ...
357        revoke ...
358        begin_work
359        commit
360        rollback
361
362        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
372        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

VERSION

379       $Revision: 1.14 $ on $Date: 2006/12/05 14:31:33 $ by $Author: andreww $
380

AUTHOR

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
389       See the file COPYING in this distribution, or
390       http://www.gnu.org/licenses/gpl.txt
391
392
393
394perl v5.28.1                      2016-02-23                     SQL::Shell(3)
Impressum