package DBIShell::Help;

#  dbishell: A generic database shell based on the Perl DBI layer
#  Copyright (C) 2000  Vivek Dasmohapatra (vivek@etla.org)

#  This program is free software; you can redistribute it and/or
#  modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2
#  of the License, or (at your option) any later version.

#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.

#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

use strict;
use Exporter ();

use vars qw($VERSION @EXPORT @EXPORT_OK %EXPORT_TAGS @ISA %HELP);

use constant H_DESCRIBE => <<'HelpDescribe';
describe <target>/

retrieves a description of the target object: the target object may be an unqualified name, or a name in the SCHEMA_OR_DB.NAME format, or, at the specific dr::DATABASE engine's option, any other format it knows how to resolve. The basic dr::DEFAULT engine only knows how to describe tables, since DB engines typically don't have enough in common for anything but this to be implemented generically. Note that the data types that you see in this generic 'describe' are the SQL9x standard names, not the database specific names implemented by various vendors.

see also: show
HelpDescribe

use constant H_VARIABLES => <<'HelpVariables';
variables:

Variables are of the form $NAME or $xxx:NAME, where xxx is one of:
'dbi' or 'env'

Variables are set with the syntax:

$VARIABLE=some value or other/

And are unset as follows:

$!VARIABLE/

variables are implemented in the shell layer, the dr::DATABASE engine
never sees them, and they are _not_ the same as any SQL variables that
your DB vendor might have implemented, thus they can be placed in any part
of a SQL query, even ones where the DB engine does not normall allow
variables.

The escape sequences \n \t \r are recognised, and quotes aren't special
in any way.

Variables are not the same as parameters, but they are related.
[parameters are more restricted, and yet morepowerful in some ways]

****************************************************************************
* NOTE: 
*
* If a variable containing the value undef [ie an unset variable]
* is interpolated into a query, it will strip any quotes immediately
* surrounding it from the query. This is so that you can do things like:
*
*  $!ARGH/
*  update FOO set BAR = '$ARGH' where SOMETHING = 'wibble'/
*
*  And it actually evaluates as:
*
*  update FOO set BAR = NULL where SOMETHING = 'wibble'/
*
****************************************************************************
see also: interpolation parameters formatting environment dbi_attr
HelpVariables


use constant H_ENVIRONMENT => <<'HelpEnv';
environment:

environment variables are gettable/settable as variables with names like
$env:HOME. Other than the namespace indicator, they can be used anywhere
that normal variables can be used, subject to the limitation that they
are stored in the environment, and are therfore bound by the same restrictions
that apply to the environment on your system, whatever they may be.

see also: variables interpolation parameters dbi_attr
HelpEnv

use constant H_DBIATTR => <<'HelpDbiAttr';
dbi_attr:

DBI attributes are accessible as variable of the form:

$dbi:LongTruncOk

Any and all DBI attributes implemented by the DBD driver you are using are
available, common ones include:

$dbi:Warn
$dbi:Active
$dbi:Kids
$dbi:ActiveKids
$dbi:PrintError           *
$dbi:RaiseError
$dbi:ChopBlanks           *
$dbi:LongTruncOk          *
$dbi:LongReadLen          *
$dbi:AutoCommit           *
$dbi:RowcacheSize

The interesting ones have been marked with a '*'. You are referred to the
DBI documentation, but I will note here that AutoCommit controls whether
you need to/are able to issue commits/rollbacks or not.

You may not unset dbi attributes, nor may you bind them as parameters. All
sorts of horrible things would happen if you did. Don't make me come over
there.

see also: variables parameters environment interpolation
HelpDbiAttr

use constant H_PARAMETERS => <<'HelpParameters';
parameters:

You may or may not be familiar with parametrised queries, it is beyond the
scope of this help system to discuss their uses and limitations: I shall
simply say here that they are similar to variabkes, but there insertion is
handled by the SQL engine itself: They are therefore subject to optimisation
by the SQL engine itself, but may not, [usually] be used anywhere where they
would prevent the database from validating the syntax of the query.

[ For example, you may not, in general, parametrise a table or field name
  in a query, as this would prevent the SQL engine from being able to tell
  if all your field names were valid. ]

Sybase and its derivative engines [eg MSSQL Server] impose the further
restriction that only where clause comparison values may be parametrised

dbishell stores its parametes in the same place as its variables: For in
parameters, the value is read from the variable of the same name as the
parameter, and for in/out parameters, the returned value is stored there
too.

In parameters take the form:      $>FOO
and out parameters take the form: $<FOO

parameters need not be [and should not be] quoted: 

eg:
$FOO=arglebargle/
select * from j_random_table where field_foo = $>FOO/

will work just fine. You may not bind DBI attributes as parameters.
Did I mention that you may not bind DBI attributes as parameters? Good.

For drivers that allow it, you may use in/out parameters to catch the return
values of out or in/out parameters from procedure calls.

see also: procedure_calls variables environment dbi_attr
HelpParameters

use constant H_LOGGING => <<'HelpLogging';
logging:

You can log the input, error [info] and output [data, results] streams
into multiple files, independently of one another:

spool error,output /tmp/dbishell.log on/
# error and output go to /tmp/dbishell.log
spool error,output /tmp/dbishell.log off/
# error and output no longer logged

spool input /tmp/dbilog2.txt on/
# input goes to /tmp/dbilog2.txt
spool input,output,error /tmp/dbilog3.txt on/
# all 3 go to /tmp/dbilog3.txt
spool error off/
# all error logging stopped
spool input /tmp/dbilog2.txt off/
# input no longer goes to /tmp/dbilog2.txt
# input,output still going to /tmp/dbilog3.txt

I hope you get the idea...
I intend to add logfile filters/transformations, but I haven't decided on the
exact mechanism/infrastructure yet. more later.
HelpLogging


use constant H_INTERP_ESCP => <<'HelpInterpEscp';
interpolation on
interpolation off
escaping on
escaping off

Thes commands turn on and off the interpolation of variables and the
interpretation of \X style escape sequences by the dbishell: they have no
effect on any \X escaping done/undersood by the SQL engine itself, or any
SQL variables as implemented by the SQL dialect your database speaks

see also: variables parameters environment dbi_attr
HelpInterpEscp

use constant H_DISCONNECT => <<'HelpDisconnect';
disconnect/

disconnects from the database you are currently connected to. Drops you back
to a dr::DEFAULT engine driving a conection to a NullP database [NullP is a
dummy/test SQL engine provided with the perl DBI for testing]

see also: connect
HelpDisconnect

use constant H_CONNECT => <<'HelpConnect';
connect [driver [dsn  [user [pass]]]]/

drops your current connection like a hot rock and connects you [if possible]
to the new DB specified by your parameters. Any parameters omitted will be
collected interactively [and if the password is so collected, it will not
br echoed as you type it]

see also: disconnect
HelpConnect

use constant H_SUBSHELL => <<'HelpSubshell';
!<shell command>/
  
Executes a cmmand in a subshell. If the command is empty, a subshell is
spawned. ^D or 'exit' to et back to dbishell.
HelpSubshell

use constant H_READ => <<'HelpRead';
read <filename>/

Reads and executes the file specified as if each line therein wre entered
manually by you at the prompt. Hooray. Now I have scripts for mysql. Shiny.
HelpRead

use constant HELP_STRING => <<'HelpMessage';

   --driver                 # DBIShell driver
   --dsn                    # dsn [The bit after 'dbi:<DRIVER>:' ]
   --user|u                 # database user name
   --pass|passwd|password|p # database password
   --help|h                 # print the help and exit
   --shell-driver           # real DBIShell driver to use if
                            # using driver=Proxy or driver=ODBC
   --dotdir                 # location of your dbishell dotfiles

All of these are implemented to some extent, some might be problematic...

1) Commands must be terminated with a trailing /
   Or, if you want to, you can configure it by setting the $EOL variable.
   No, it can't default to ';', that would cause problems w. dr::Oracle.
   So stop complaining already. [You know who you are]

2) Tab completion should be available. The mysql tab completer is reasonably
   smart, the Oracle one will be once I get some more work done on it.

3)Special variables:
	  
   $FIELD_SEPARATOR
   
   Contains the character sequence used to separate fields in output.
   If unset, you get '|' as the separator.
   
   $PRESCAN_FORMAT
   
   If true, [cf. Perl "What is truth"] then the whole of the dataset
   returned by all queries is prescanned, and the output formatted accordingly
   This can be helpful when you have large columns which typically hold
   values much shorter than they have the capacity for, since it shrinks
   the display columns down to the size of the largest output value on a per
   column basis. On the other hand, if you're fetching large numbers of rows,
   This may hurt, since you'll be prefetching the whole dataset into memory,
   scanning it, and then printing it. 
   
   $TRUNCATE_COLUMN_NAMES
   
   If false, a column will never be narrower than its name in the output.
   Othewise, column names will be shortened to fit their columns display
   widths [if PRESCAN_ROWS is set]
   
   $CASE_SENSITIVE
   
   Is the DB case sensitive or not? [mainly effects tab completion]
   
   $PAGER 
   
   The command to open a pipe to, to use as a pager. If $PAGER is unset,
   $env:PAGER is tried instead, and if there's nothing there, 'less -S'
   is used instead. If that doesn't work, then bad things probably 
   happen. Like paging not working, and maybe your output disappearing.
   
   $PAGING
   
   If this is true, then output from certain commands [just selects at 
   the moment] will be paged, depending on the value of $PAGE_SIZE and 
   $PRESCAN_FORMAT

   $PAGE_SIZE

   The number of rows above which dbishell will try to page output.
   Paging is decided as follows:

   If $PAGING is true, and $PRESCAN_FORMAT is true, output exceeding
   $PAGE_SIZE [or exceeding the size of the terminal, if $PAGE_SIZE
   is 0 or unset] will be paged.

   If $PRESCAN_FORMAT is false, and $PAGING is true, then output will 
   be paged regardless of the amount of data returned, unless the DBI 
   driver can determine the number of rows before they are all fetched.

   $EOL

   The end of line character used by dbishell to determine when you want
   a command executed. If unset [the default] then '/' is used.
   [except that the '/' that terminates a comment will be ignored].
   Otherwise, whatever you put in $EOL will be scanned for.

4) History:

   Command history is stored in ~/.dbishell/<driver>.history

5) Examples:

dbishell --driver Oracle --dsn host=foobar\;sid=argh        --user=scott
dbishell --driver mysql  --dsn host=narf\;database=argh     --user vivek
dbishell --driver Sybase --dsn hostname=narf\;database=argh --user=sa
dbishell --driver ODBC   --dsn FOO:  --shell-driver=Sybase  --user sa

6) DSN strings

dbishell and DBI have slightly different views of the meaning of the
term 'DSN'.

DBI expects a DSN in the following forms:

  dbi:DriverName:database_name
  dbi:DriverName:database_name@hostname
  dbi:DriverName:database_name~hostname!port
  dbi:DriverName:database=database_name;host=hostname;port=port

(examples from DBI man page).

dbishell requires you to specify the driver seperately, and the 
above examples would be handed as:

  database_name
  database_name@hostname
  database_name~hostname!port
  database_name;host=hostname;port=port

These strings would be arguments to the --dsn parameter, with the
actual database driver to use specified with --driver and --shell-driver.

The 'dbi' string is dropped, as it is implicit that if you are using
dbishell you are connecting to a dbi data source.

7) Help topics:

help
show
read
describe
interpolation
escaping
parameters
dbi_attr
environment
procedure_calls
logging
connect
disconnect
subshell
quit

Vivek Dasmohapatra <vivek@etla.org>


HelpMessage

@ISA         = qw(Exporter);
@EXPORT      = ();
@EXPORT_OK   = qw(HELP_STRING);
%EXPORT_TAGS = ();
$VERSION     = 0.01_11;

%HELP = (
	 interpolation => H_INTERP_ESCP,
	 escaping      => H_INTERP_ESCP,
	 parameters    => H_PARAMETERS,
	 environment   => H_ENVIRONMENT,
	 dbi_attr      => H_DBIATTR,
	 variables     => H_VARIABLES,
	 connect       => H_CONNECT,
	 disconnect    => H_DISCONNECT,
	 subshell      => H_SUBSHELL,
	 read          => H_READ,
	 logging       => H_LOGGING,
	 quit          => "quit/\n\nquits dbishell"
	);

sub help_map ($) { return ref($_[0]) ? $_[0]->{MAP} : \%HELP }

sub help ($$)
{
    my $help     = $_[0];
    my $hcontext = $_[1]; # don't do anything w. this yet...
    my $map      = $help->help_map();
    
    $hcontext =~ /(\S+)/;
    $hcontext =  lc($1);

    if ($map->{$hcontext})
    {
	return join('',$1,":\n",$map->{$hcontext});
    }
    
    return HELP_STRING;
}

sub topics ($$)
{
    my $l;
    my $help  = $_[0];
    my $topic = lc($_[1]);
    my $map   = $help->help_map();
    
    if($l = length($topic))
    {
	return (grep { substr($_, 0, $l) eq $topic } keys(%{$map}));
    }
    else
    {
	return keys(%{$map});
    }
}

sub name ($) { return ref($_[0]) ? $_[0]->{NAME} : __PACKAGE__ }

sub new ($$)
{
    my $help;
    my $db_help;
    my @db_help;
    my $package  = ref($_[0]) || $_[0];
    my $database = $_[1];
    
    $help = { MAP => {} };
    
    push(@db_help, $package);
    
    $db_help = join('::','DBIShell::help',$database);
    $db_help = DBIShell::UTIL::dynaload($db_help);
    push(@db_help, $db_help);
    
    foreach $db_help (grep {$_} @db_help)
    {
	warn("######\nLoading help from $db_help\n#####\n");
	$help->{NAME} = $db_help;
	my $map = $db_help->help_map();
	foreach my $topic (keys(%{$map}))
	{
	    $help->{MAP}{$topic} = $map->{$topic};
	}
    }
    
    return bless($help, $package);
}

__END__
#TLF: Nikola Tesla died for you...

=pod
 
=head1 NAME

DBIShell::Help - DBIShell help messages

=head1 SYNOPSIS

  use DBIShell::Help;

=head1 DESCRIPTION

This module provides help text for dbishell

=head1 METHODS

=head2 DBIShell::Help->new(DATABASE)

Create a new help object, for the specified DATABASE, if possible
[will be loaded from DBIShell::help::<DATABASE> if it is there]

=head2 $help->name()

Return the name of the package from which help is actually being
harvested

=head2 $help->topics(FRAG)

Return a list of topics that match fragment FRAG

=head2 $help->help(TOPIC)

Return the help for topic TOPIC

=head2 help_map

Return a reference to the hash containing the topic -> text map

=head1 SEE ALSO

dbishell

=head1 AUTHOR

Vivek Dasmohapatra <vivek@etla.org>

=cut