package SQL::Shell::Manual;

use vars qw($VERSION);
$VERSION = sprintf"%d.%03d", q$Revision: 1.6 $ =~ /: (\d+)\.(\d+)/;

1;

=head1 NAME

SQL::Shell::Manual - user guide for sql shell

=head1 SYNOPSIS

sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger

=head1 DESCRIPTION

This is a guide to using sqlsh.  sqlsh is an interactive shell run from the command-line for workling with databases.
It can also be run in "batch mode" taking a list of commands from stdin (using the -i switch) or you can pass a single command to it on the command-line.

=head2 Connecting

Either set a DSN in the environment as DBI_DSN, supply with the -d option or use the connect command:

	unixbox% sqlsh
	unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger

You can also connect from inside sqlsh:

	unixbox% sqlsh
	> connect DBI:Oracle:IFLDEV scott tiger
	DBI:Oracle:IFLDEV> show $dbh Name
	+--------+
	| Name   |
	+--------+
	| IFLDEV |
	+--------+

and disconnect:

	DBI:Oracle:IFLDEV> disconnect                                 
	> show $dbh Name
	Not connected to database.

If you don't supply a password, sqlsh will prompt you:

	unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott
	Enter password for scott: 

You can specify a blank password by passing -p:

	unixbox% sqlsh -d DBI:Oracle:IFLDEV -u guest -p

From within sqlsh you can get a list of DBI drivers:

	unixbox% sqlsh
	> show drivers
	
	  CSV
	  DBM
	  ExampleP
	  Excel
	  File
	  Multiplex
	  Oracle
	  Proxy
	  SQLite
	  Sponge
	  mysql
	
and a list of possible data sources for a driver:

	unixbox% sqlsh
	> show datasources Oracle
	
	  dbi:Oracle:GISCPS
	  dbi:Oracle:IFL1
	  dbi:Oracle:IFLDEV
	  dbi:Oracle:IFLTEST

Common DBI DSNs include:

	DBI:Oracle:<SID>
	DBI:mysql:<DB>
	DBI:ADO:<DSN>
	DBI:Excel:file=<xls>
	DBI:CSV:f_dir=<dir>
	DBI:SQLite:dbname=<filename>

=head2 Exploring the schema

=head3 show tables

This lists the tables with a rowcount for each:

	DBI:SQLite:dbname=test.db> show tables                    
	+----------------------+------+
	| table                | rows |
	+----------------------+------+
	| "sqlite_master"      | 1    |
	| "sqlite_temp_master" | 0    |
	| "commands"           | 11   |
	+----------------------+------+

For some database drivers this may include some system tables.
	
=head3 desc

Lists the columns in a table:
	
	DBI:Oracle:IFLDEV> desc commands
	+-------------+----------------+------+
	| Field       | Type           | Null |
	+-------------+----------------+------+
	| COMMAND     | VARCHAR2(200)  | YES  |
	| DESCRIPTION | VARCHAR2(1020) | YES  |
	+-------------+----------------+------+
	
=head3 show schema

Lists the columns in a table, for each table in the schema:

	DBI:Oracle:IFLDEV> show schema
	
	schema dump
	COMMANDS:
	+-------------+----------------+------+
	| Field       | Type           | Null |
	+-------------+----------------+------+
	| COMMAND     | VARCHAR2(200)  | YES  |
	| DESCRIPTION | VARCHAR2(1020) | YES  |
	+-------------+----------------+------+

=head2 Querying the database

	DBI:SQLite:dbname=test.db> select * from commands
	+------------------+--------------------------------------------------------------+
	| command          | desc                                                         |
	+------------------+--------------------------------------------------------------+
	| show drivers     | Displays a list of DBI drivers                               |
	| show datasources | Displays a list of available data sources for a driver       |
	| connect          | Connects to a data source                                    |
	| disconnect       | Disconnects from a data source                               |
	| show tables      | List the tables in the schema with a rowcount for each table |
	| show schema      | Lists the columns in each table in the schema                |
	| desc             | List the columns in a table                                  |
	| set              | Set a parameter                                              |
	| help             | Displays sqlsh help in your $PAGER                           |
	| reload           | Reloads sqlsh                                                |
	| exit             | Quits sqlsh                                                  |
	+------------------+--------------------------------------------------------------+

=head3 BLOB values

You can control the amount of BLOB data fetched by setting the C<longreadlen> parameter.

	
	DBI:Oracle:IFLDEV> set longreadlen 4096
	LongReadLen set to '4096'
	
	DBI:Oracle:IFLDEV> show $dbh LongReadLen
	+-------------+
	| LongReadLen |
	+-------------+
	| 4096        |
	+-------------+

 Note that the C<longtruncok> parameter should also be set (it is by default):
 
	DBI:Oracle:IFLDEV> show $dbh LongTruncOk
	+-------------+
	| LongTruncOk |
	+-------------+
	| 1           |
	+-------------+


=head3 Values containing non-word characters

Suppose we have values in our database which contain whitespace characters (e.g. tabs):

	DBI:Oracle:IFLDEV> set enter-whitespace on
	Whitespace may be entered as \n, \r and \t

	DBI:Oracle:IFLDEV> insert into commands(command,description) values('test', 'one\ttwo')
	INSERT commands: 1 rows affected

When we query the table we see these as literal values:

	DBI:Oracle:IFLDEV> select * from commands
	+---------+-------------+
	| COMMAND | DESCRIPTION |
	+---------+-------------+
	| test    | one two     |
	+---------+-------------+

We can instead chose to display them escaped:

	DBI:Oracle:IFLDEV> set escape show-whitespace
	DBI:Oracle:IFLDEV> select * from commands
	+---------+-------------+
	| COMMAND | DESCRIPTION |
	+---------+-------------+
	| test    | one\ttwo    |
	+---------+-------------+

Alternatively we can use uri-escaping:

	DBI:Oracle:IFLDEV> set escape uri-escape on
	DBI:Oracle:IFLDEV> select * from commands
	+---------+-------------+
	| COMMAND | DESCRIPTION |
	+---------+-------------+
	| test    | one%09two   |
	+---------+-------------+

=head3 Entering multi-line statements

To enable multiline mode:

	DBI:Oracle:IFLDEV> set multiline on 

You can then build up statements over multiple lines, ending with a semicolon, e.g.:

	DBI:Oracle:IFLDEV> select 
	DBI:Oracle:IFLDEV> count(*) 
	DBI:Oracle:IFLDEV> from 
	DBI:Oracle:IFLDEV> commands
	DBI:Oracle:IFLDEV> ;
	+----------+
	| COUNT(*) |
	+----------+
	| 11       |
	+----------+

To disable multiline mode, remember you need to end the statement in a semicolon:

	DBI:Oracle:IFLDEV> set multiline off;

=head3 Altering the display mode

The default (C<box>) display mode is similar to that used by the mysql client - it works well for tables of fairly short values. 
The C<record> display mode is good for viewing single records:

	DBI:SQLite:dbname=test.db> set display-mode record
	DBI:SQLite:dbname=test.db> select * from commands where command='desc'
	--------------------------------------------------------------------------------
	command | desc
	desc    | List the columns in a table
	--------------------------------------------------------------------------------

The C<spaced> display mode (despite sounding like a description of sqlsh's author) provides a minimum clutter view of the data.
The C<tabbed> display mode generally looks horrendous but is useful for a quick cut+paste of delimited values.
The C<sql> display mode generates insert statements using a $table placeholder for where the data is to be inserted.
The C<xml> display mode generates element-only XML which can be parsed into a list of hashes with XML::Simple.

=head2 Altering the database

By default transactions are not automatically committed so you must explicitly commit them:

	DBI:Oracle:IFLDEV> insert into commands(command, description) values ('dump','Writes a table or query results to a delimited file')
	INSERT commands: 1 rows affected
	
	DBI:Oracle:IFLDEV> commit

and you can roll back mistakes:
	
	DBI:Oracle:IFLDEV> delete from commands
	DELETE commands: 11 rows affected
	
	DBI:Oracle:IFLDEV> rollback
	DBI:Oracle:IFLDEV> select count(*) from commands         
	+----------+
	| COUNT(*) |
	+----------+
	| 11       |
	+----------+

If you prefer to live dangerously you can switch autocommit on:
	
	set autocommit on
	insert ...
	update ...

=head3 Clearing the database

The C<wipe tables> command can be used to remove all the data each of the tables in the database:

	DBI:Oracle:IFLDEV> wipe tables       
	Wipe all data from:
	
	  COMMANDS
	
	Are you sure you want to do this? (type 'yes' if you are) yes
	
	Wiped all data in database

It prompts you to confirm before anihilating your database.
			
=head2 Dumping delimited data

C<dump> can either be used to dump an entire table:

	dump mytable into export.txt
	
or the rowset resulting from a query:
	
	dump select type, count(*) from mytable group by type into histogram.txt delimited by :

An example:

	DBI:SQLite:dbname=test.db> dump commands into commands.csv delimited by ,
	Dumping commands into commands.csv
	Dumped 11 rows into commands.csv
	
	DBI:SQLite:dbname=test.db> more commands.csv 
	command,desc
	show drivers,Displays a list of DBI drivers
	show datasources,Displays a list of available data sources for a driver
	connect,Connects to a data source
	disconnect,Disconnects from a data source
	show tables,List the tables in the schema with a rowcount for each table
	show schema,Lists the columns in each table in the schema
	desc,List the columns in a table
	set,Set a parameter
	help,Displays sqlsh help in your $PAGER
	reload,Reloads sqlsh
	exit,Quits sqlsh

You can also dump all the tables in a database into a directory:

	dump all tables into dumpdir/

=head2 Logging

You can chose to log commands:

	log commands logfile.txt

or query results:
	
	log queries dumpfile.txt

or both:

	log all history.log

=head2 Exporting data as XML

	DBI:Oracle:IFLDEV> set log-mode xml 
	
	DBI:Oracle:IFLDEV> log queries export.xml
	Logging queries to export.xml
	
	DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
	+------------------+--------------------------------------------------------------+
	| COMMAND          | DESCRIPTION                                                  |
	+------------------+--------------------------------------------------------------+
	| show drivers     | Displays a list of DBI drivers                               |
	| show datasources | Displays a list of available data sources for a driver       |
	| show tables      | List the tables in the schema with a rowcount for each table |
	| show schema      | Lists the columns in each table in the schema                |
	+------------------+--------------------------------------------------------------+

	DBI:Oracle:IFLDEV>> more export.xml
	<rowset>
	        <record>
	                <COMMAND>show drivers</COMMAND>
	                <DESCRIPTION>Displays a list of DBI drivers</DESCRIPTION>
	        </record>
	        <record>
	                <COMMAND>show datasources</COMMAND>
	                <DESCRIPTION>Displays a list of available data sources for a driver</DESCRIPTION>
	        </record>
	        <record>
	                <COMMAND>show tables</COMMAND>
	                <DESCRIPTION>List the tables in the schema with a rowcount for each table</DESCRIPTION>
	        </record>
	        <record>
	                <COMMAND>show schema</COMMAND>
	                <DESCRIPTION>Lists the columns in each table in the schema</DESCRIPTION>
	        </record>
	</rowset>
	
	DBI:Oracle:IFLDEV>> no log
	Stopped logging queries

=head2 Exporting data as SQL

	DBI:Oracle:IFLDEV> set log-mode sql

	DBI:Oracle:IFLDEV> log queries export.sql                           
	Logging queries to export.sql

	DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
	+------------------+--------------------------------------------------------------+
	| COMMAND          | DESCRIPTION                                                  |
	+------------------+--------------------------------------------------------------+
	| show drivers     | Displays a list of DBI drivers                               |
	| show datasources | Displays a list of available data sources for a driver       |
	| show tables      | List the tables in the schema with a rowcount for each table |
	| show schema      | Lists the columns in each table in the schema                |
	+------------------+--------------------------------------------------------------+

	DBI:Oracle:IFLDEV>> more export.sql                                  
	INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show drivers','Displays a list of DBI drivers');
	INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show datasources','Displays a list of available data sources for a driver');
	INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show tables','List the tables in the schema with a rowcount for each table');
	INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show schema','Lists the columns in each table in the schema');
	
	DBI:Oracle:IFLDEV>> no log
	Stopped logging queries

You can then replace $table with the table name you want the INSERT stataments to be issued against:
	
	unixbox% perl -p -i -e 's/\$table/show_commands/' export.sql

=head2 Loading data

Loading a tab-delimited text file is simple:

	load export.txt into mytable

Here's an example:

	DBI:SQLite:dbname=test.db> create table commands(command varchar(50), desc varchar(255))                
	CREATE table commands: 0 rows affected

	DBI:SQLite:dbname=test.db> load commands.tsv into commands
	Loaded 11 rows into commands from commands.tsv

As with C<dump> you can change the delimiter character:

	load export.csv into mytable delimited by ,
	
You can also specify character set translations:	

	load export.txt into mytable from CP1252 to UTF-8

if your database engine cannot do the character set conversions itself.
See L<Locale::Recode> for a list of character set names.

=head2 Manipulating the command history

You can dump out the history to a file:

	save history to history.txt
	
You can also load in a set of commands into the history:
	
	load history from handy_queries.sql

This can be useful in conjunction with C<log commands>.
You can clear the history at any time with:

	clear history

and display it with:
	
	show history
	
=head2 Running batches of commands

You can execute a sequence of sqlsh commands from a file:

	> execute commands.sqlsh

that might have been generated by C<save history> or C<log commands>.
You can also pipe commands into sqlsh on STDIN if you call it with the C<-i> switch:

	unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger -i < commands.sqlsh

=head1 VERSION

$Revision: 1.6 $ on $Date: 2006/08/02 12:01:15 $ by $Author: johna $

=head1 AUTHOR

John Alden

=cut