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_objects()
171           Displays a list of tables, schemas, catalogs or table-types
172           depending on the object argument passed.
173
174       $sqlsh->show_tablecounts()
175           Displays a list of tables with row counts.  Equivalent to "show
176           tablecounts".
177
178       $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
182       $sqlsh->describe($table)
183           Displays the columns in the table.  Equivalent to "describe
184           $table".
185
186       $sqlsh->run_query($sql)
187           Displays the rowset returned by the query.  Equivalent to
188           execute_cmd with a select or explain statement.
189
190   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
195       $sqlsh->begin_work()
196           Starts a transaction.  Equivalent to "begin work".
197
198       $sqlsh->commit()
199           Commits a transaction.  Equivalent to "commit".
200
201       $sqlsh->rollback()
202           Rolls back a transaction.  Equivalent to "rollback".
203
204       $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
209   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
215       $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
223       $sqlsh->show_charsets()
224           Lists the character sets supported by the recoding feature of "load
225           data".  Equivalent to "show charsets".
226

CUSTOMISING

228   INSTALLING CUSTOM COMMANDS
229       The coderef will be passed the $sqlsh object followed by each argument
230       captured by the regex.
231
232               my %additional_commands = (
233               qr/^hello from (\.*)/ => sub {
234                       my ($self, $name) = @_;
235                       print "hi there $name\n";
236               });
237
238       To install this:
239
240               $sqlsh->install_cmds(\%additional_commands)
241
242       Then in sqlsh:
243
244               > hello from John
245               hi there John
246
247   INSTALLING CUSTOM RENDERERS
248       Renderers are coderefs which are passed the following arguments:
249
250               $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
256       Here's an example to render data in CSV format:
257
258               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
269       To install this:
270
271               $sqlsh->install_renderers({'csv' => \&my_renderer});
272
273       Then in sqlsh:
274
275               > set display-mode csv
276
277   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
282       $arrayref = $GetHistorySub->()
283       $SetHistorySub->($arrayref)
284       $AddHistorySub->($string)
285
286       An example:
287
288               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

SETTINGS

297       The following settings can only be set through the constructor or the
298       "set" method:
299
300               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
309       The following are also affected by the "set_param" method or the "set"
310       command:
311
312               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

COMMANDS

325        show drivers
326        show datasources <driver>
327        connect <dsn> [<user> <pass>] - connect to DBI DSN
328        disconnect - disconnect from the DB
329
330        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
338        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
344        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
359        log (queries|commands|all) <filename> - start logging to <filename>
360        no log - stop logging
361
362        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
376        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
386        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

VERSION

393       Version 1.17
394

AUTHOR

396       John Alden with contributions by Simon Flack and Simon Stevenson <cpan
397       _at_ bbc _dot_ co _dot_ uk>
398
399       Miguel Gualdron maintainer.
400
402           SQL-Shell:  Interactive shell for DBI Databases
403           Copyright (C) 2006  BBC
404           Copyright (C) 2019  Miguel Gualdron
405
406           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
411           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
416           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
420       See the file COPYING in this distribution, or
421       https://www.gnu.org/licenses/gpl-2.0.html
422
423
424
425perl v5.38.0                      2023-07-21                     SQL::Shell(3)
Impressum