File: HenPlus.html

package info (click to toggle)
henplus 0.9.5-2
links: PTS
area: main
in suites: sarge
size: 696 kB
ctags: 1,136
sloc: java: 8,346; xml: 130; sh: 84
file content (736 lines) | stat: -rw-r--r-- 35,881 bytes
parent folder | download | duplicates (2)
<html>
<head><title>HenPlus documentation</title></head>
<body bgcolor="white">
<!-- $Id: HenPlus.html,v 1.21 2004/02/01 20:57:26 hzeller Exp $ -->
<h1 align="center">HenPlus</h1>
<h3 align="center">JDBC SQL-shell</h3>

<table align="right" bgcolor="#DDDDFF">
<tr><th>Table of Contents</th></tr>
<tr><td><a href="#overview">Overview</a></td></tr>
<tr><td><a href="#getting-started">Getting Started</a></td></tr>
<tr><td><a href="#getting-connected">Getting Connected</a></td></tr>
<tr><td><a href="#multiple-open-sessions">Multiple Open Sessions</a></td></tr>
<tr><td><a href="#variables">Using Variables</a></td></tr>
<tr><td><a href="#properties">Setting Properties</a></td></tr>
<tr><td><a href="#commandline-completion">Commandline Completion</a></td></tr>
<tr><td><a href="#per-project-config">Per Project Configuration</a></td></tr>
<tr><td><a href="#plugins">Plugins</a></td></tr>
<tr><td><a href="#aliases">Aliases</a></td></tr>
<tr><td><a href="#table-dumps">Table Dumps</a></td></tr>
<tr><td><a href="#tree-view">Tree View</a></td></tr>
<tr><td><hr></td></tr>
<tr><td><a href="#faq">FAQ</a></td></tr>
<tr><td><a href="http://sourceforge.net/projects/henplus">Download</a></td></tr>
</table>

<h3>History</h3>
Know this ? You need a small little tool for your everyday work and
you wonder why nobody did this before -- and then you write it yourself.
HenPlus is the result of such an effort. Its name reflects the original 
intent: it started as a platform independent replacement for the 
Oracle SQLPlus tool. 
I wrote the first version of HenPlus in 1997 (before
Oracle was available for Linux) since I had to develop an application that 
connected to an Oracle database -- but the only SQLPlus I could use
in the environment back then was running on some Redmond operation
system that lacked (and still lacks) the environment to develop serious software.
<br>Since then I've rewritten
this tool, added command line completion and history -- but it still
remained small and useful. It is freely distributable in source 
and binary form because it is protected by the 
<a href="http://www.gnu.org/licenses/gpl.txt">GNU Public License</a>.

<h3><a name="overview"/>Overview</h3>
HenPlus is a SQL shell written in Java that works for any database 
that offers JDBC support. So
basically any database. Why do we need this ? Any database comes with some
shell, but all of them have missing features (and several shells are simply 
unusable).
And if you work with several
databases at once (if you are a developer, then you do this all the time),
switching between these tools is tedious.
<p>
This is where <em>HenPlus</em> steps in. It supports:
<ul>
 <li>Any JDBC aware database.</li>
 <li>Has context sensitive command line completion for commands 
     and tables / columns / variables within SQL-commands. </li>
 <li>Multiple open connection-sessions can be handled in parallel. You can just
     switch between them. So you can be connected to different databases to
     experiment with the same statement in all of them, for example.<br>
     All JDBC-Urls of opened sessions are stored for
     later command line completion in the <code>connect</code> command.</li>
 <li>Command line history just like in the bash (with cursor-up/down, CTRL-R, 
     ...)
     </li>
 <li>A <code>describe</code> command for database tables, that work all
     JDBC-Drivers that unveil the appropriate MetaData (Oracle, PostgreSQL,
     MySQL, DB2...)</li>
 <li>supports variables that are expanded like shell variables
     with $VARIABLENAME or ${VARIABLENAME} (with completion of their names,
     just like in bash)</li>
 <li>loading files</li>
 <li>supports several built-in commands (<code>start</code>, <code>@</code>,
     <code>@@</code>, <code>spool</code>) and
     syntax from the Oracle SQL-plus utility (like the single '/' on a line
     to close a statement). Most Oracle SQL-plus scripts will run directly, so
     its simple to switch to HenPlus. Except if you can't stand, that your
     life will become much simpler, then ;-) If you have problems
     running your old scripts, please let 
     <a href="mailto:H.Zeller@acm.org">me</a> know.</li>
 <li>allows a per-project storage of the commandline history, 
     variables, plugins and connections.
 <li>allows to add your own plugins that behave just like the built-in 
     commands.</li>
 <li>Is provided as Free Software. You are free to modify, copy, share and sell this software under the <a href="http://www.gnu.org/licenses/gpl.txt">GNU Public License</a>
</ul>
</body>

<h3><a name="getting-started"/>Getting started</h3>
<h4>Download</h4>
You can download source and binary packages of HenPlus at the 
<a href="http://sourceforge.net/project/showfiles.php?group_id=47945">HenPlus SourceForge Download Page</a>.
<p>
If you are using <a href="http://www.debian.org/">Debian</a>, you can install HenPlus by adding the following
line to your <code>/etc/apt/sources.list</code>
<pre>
  deb http://osdn.dl.sourceforge.net/henplus ./
</pre>
After that, run
<pre>
  apt-get update
</pre> 
then
<pre>
  apt-get install henplus
</pre>

<h4>Compilation</h4>
If you have <a href="http://sourceforge.net/project/showfiles.php?group_id=47945">downloaded</a>
HenPlus as a binary RPM or debian package, you can
<a href="#running">skip</a> this part.
<p>
Ok, you are still here, so you have to compile it first. First you need
an additional library.
HenPlus uses the features of the GNU-readline library and therefore
needs the JNI java wrapper library &gt;=
<a href="http://sourceforge.net/project/showfiles.php?group_id=48669">java-readline 0.7.3</a>.
<p>
To build HenPlus the <a href="http://jakarta.apache.org/ant">ant build
tool</a> (Version >= 1.4) is required. To compile HenPlus, make sure that the 
<code>libreadline-java.jar</code> is in the classpath. By default, the 
java-readline package installs this in <code>/usr/share/java/libreadline-java.jar</code>. Compilation needs to be done with JDK >= 1.3, but the resulting 
jar file works with old JDK 1.2.2 (the Runtime.addShutdownHook() method is
used in compilation).
<p>Now, just type
<pre>
$ ant jar
</pre>
If you are root, then you can install it with:
<pre>
$ ant install
</pre>
which will install henplus in
<table bgcolor="#DDDDFF" border=1>
<tr valign="top">
   <td>/usr/share/henplus/henplus.jar</td><td>The jar-file containing
                         the HenPlus classes. You can add additional
			 jar files in this directory. All of them are
			 added to the classpath in the henplus shellscript
			 (use this for JDBC-drivers).
			 </td>
<tr><td>/usr/bin/henplus</td><td>shellscript to start henplus</td></tr>
</table>
If you want another installation base (default: /usr), you 
provide this with the parameter <code>'prefix'</code>:
<pre>
$ ant -Dprefix=/usr/local install
</pre>
<font size="-1">(For package providers: the build.xml provides as well
the <code>DESTDIR</code> parameter)</font>
<p>
I haven't compiled this on Windows, but it shouldn't be a big deal .. if you manage to compile
the java-readline. If you did it, just post your experience, so that we
can include it in this documentation.

<h4><a name="running"/>Running</h4>
You can start HenPlus with the <code>henplus</code> shell script
with or without an jdbc-url on the command line.
<pre>
$ henplus jdbc:mysql://localhost/foobar
</pre>

<p>
<h5>Make sure, command line editing is enabled</h5>
If the first line, henplus writes reads:
<pre>
no readline found (no JavaReadline in java.library.path). Using simple stdin.
</pre>
.. then, the JNI-part of the readline library could not be found, so command line
editing is disabled because henplus then reads from stdin as fallback.
This happens if the LD_LIBRARY_PATH does not point to the JNI library; edit the
<code>/usr/bin/henplus</code> shellscript so that the LD_LIBRARY_PATH contains the
directory where <code>libJavaReadline.so</code> resides.

<h5>Getting started</h5>
The important commands you need to know to get it running are <code>help</code> and
<code>connect</code>. The help command gives an overview, what commands
are supported:
<hr>
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
Hen*Plus&gt; help
 help | ?                                 : provides help for commands
 about | version | license                : about HenPlus
 exit | quit                              : exits HenPlus
 echo | prompt                            : echo argument
 list-plugins | plug-in | plug-out        : <a href="#plugins">handle Plugins</a>
 list-drivers | register | unregister     : <a href="#getting-connected">handle JDBC drivers</a>
 list-aliases | alias | unalias           : <a href="#aliases">handle Aliases</a>
 load | start | @ | @@                    : load file and execute commands
 connect | disconnect | rename-session | switch | sessions: <a href="#connect">manage sessions</a>
 status                                   : show status of this connection
 tables | views | rehash                  : list available user objects
 describe &lt;tablename&gt;                     : describe a database object
 tree-view &lt;tablename&gt;                    : <a href="#tree-view">tree representation of connected tables</a>
 dump-out | dump-in | verify-dump | dump-conditional: <a href="#table-dumps">handle table dumps</a>
 system | !                               : execute system commands
 set-var | unset-var                      : <a href="#variable">set/unset variables</a>
 set-property | reset-property            : <a href="#properties">set global HenPlus properties</a>
 set-session-property | reset-session-property: <a href="#properties">set SQL-connection specific properties</a>
<a name="config-dir"/><a href="#per-project-config">config read from [/home/hzeller/.henplus]</a>
</pre></td></tr></table>
<hr>
You exit the HenPlus shell by typing the <code>exit</code> or <code>quit</code>
command or by just typing EOF-Character (CTRL-D).
<p>
Just explore the commands by typing <code>help [commandname]</code> and
learn what the built-in commands are all about. Start with the 
<code>connect</code> command
<pre>
Hen*Plus&gt; help connect
</pre>
(more details on connection to the database in the 
<a href="#getting-connected">Getting connected</a> section).

<h5>Semicolon as Separator</h5>
Usually there is one command per line. However, you can have multiple
commands on one line if you separate them with a semicolon:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
echo "*** The build-in help ***" ; help
</pre></td></tr></table>
<p>
The SQL commands however (you guess it: 'select', 'update', 'create' .. ) are
not complete after the newline; you <em>always</em> have to close them
with a semicolon -- so it is possible to write statements on multiple lines:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
oracle:localhost&gt; create table foobar (
                    id number(10) primary key,
                    text varchar(127)
                  );
ok. (70 msec)
oracle:localhost&gt;
</pre></td></tr></table>
<p>
Some commands are not even complete, if there is a semicolon -- these are
'create procedure' and 'create trigger'. These commands contain 
some more complex SQL-operations that are each separated by a semicolon. 
The whole
command is then completed with a single slash at the beginning of a new line 
(this syntax is the same that SQLPlus supports):

<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
oracle:localhost&gt; create or replace trigger foobar_autoinc
                  before insert on foobar
                  for each row
                  begin
                    select foobar_seq.nextval into :new.id from dual;
                  end foobar_autoinc;
                  /
ok. (320 msec)
oracle:localhost&gt;
</pre></td></tr></table>

<h5>SQL-Comments</h5>
That should be enough to start working with HenPlus. But especially if you are
running scripts (with the <code>load</code>-command)
there is one additional piece of information you might
need to know: the types of comments that are ignored. AFAIK, the
SQL standard defines only an ANSI-endline comment, that starts with two dashes;
this is supported by HenPlus:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
  select * from foobar; -- this is a comment
</pre></td></tr></table>
<p>
However, other non-standard types of comments have come to use in
several SQL-shells so HenPlus ignores these as well. One style are
the C/C++/Java style comments, that comment out a range between 
<code>/*&nbsp;some&nbsp;comment&nbsp;*/</code>.
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
/*
   This is a longer comment that goes
   across several lines
 */
 select * from foobar;
</pre></td></tr></table>
<p>
Another style of comments, allowed for instance in the 
MySQL-Shell is the UNIX-shell like '#' endline comment; however, this character
is <b>only</b> allowed as first character to be a comment -- otherwise
using the Hash-Symbol in normal SQL-Statements (e.g. column names), would not work.
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
# this is a comment.
 select * from foobar;
 create table foo (id# number); -- the hash here is not a comment
</pre></td></tr></table>

One beta tester <!-- Andi G. --> requested an additional type of 
comment: Two semicolons at 
<em>the beginning</em> of a line to comment out the whole line. The problem
with semicolon is, that it is as well used as separator between commands and
as such may of course occur twice in a row. Therefore, two 
semicolons are <em>only</em> regarded as comment, if they are the
first on a line:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
;; This line is commented out
   ;; this one as well, since there are only whitespaces on the left
select * from foobar ;; echo "this echo is executed as usual"
</pre></td></tr></table>
<p>
To make a long story short -- the supported types of comments are
<ul>
  <li>Ansi-Style <b><code>--</code></b> comment until end of line</li>
  <li>UNIX-Shell Style <b><code>#</code></b> comment until end of line</li>
  <li>Lisp-like <b><code>;;</code></b> comment until end of line</li>
  <li>C/C++/Java like <b><code>/*&nbsp;range&nbsp;comment&nbsp;*/</code></b>
</ul>
<em>Not</em> supported is the C++/Java like endline comment that starts with
two slashes <b><code>//</code></b>. The reason is, that many JDBC-URLs contain
these two slashes, and we don't want to comment these out, right ? ;-)
<p>
If you encounter some other reasonable style of comments you want that it is
supported by HenPlus, please let <a href="mailto:H.Zeller@acm.org">me</a> know.
<p>
<b>Switching off comment removal...</b><a name="remove-comment"/>
The comment removal is done in HenPlus, since some JDBC-Drivers have problems
to remove comments or do not remove the same set of comment types given here.
However, sometimes it is necessary <em>not</em> to remove comments, since some
datases use comments to convey hinting in statements. In Oracle, for instance,
you give hints to the query optimizer in the form
<pre>
    select <b>/*+ index(foo,foo_a_idx) */</b> a from foo where ...
</pre>
For this reason, the <a href="#properties">property</a> <code>comments-remove</code>
is provided; it allows to switch off comment removal, so the string is sent to the
database as-is. The command is <code>set-property comments-remove off</code>.

<h3><a name="getting-connected"/>Getting connected</h3>
Getting connected to a database is simple: you need the JDBC driver
from your database vendor, put it in the classpath, register the driver
-- that&apos;s it. HenPlus provides the commands register, unregister and 
list-drivers to manage the drivers (you know it already: the <code>help</code>
command tells you more details). Some of the common drivers
are already registered by default .. however, they still need to be in 
the CLASSPATH still.
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
Hen*Plus&gt; list-drivers
loaded drivers are marked with '*' (otherwise not found in CLASSPATH)
------------+---------------------------------+---------------------------------------+
    <b>for</b>     |          <b>driver class</b>           |              <b>sample url</b>               |
------------+---------------------------------+---------------------------------------+
   Adabas   | de.sag.jdbc.adabasd.ADriver     | jdbc:adabasd://localhost:7200/work    |
   DB2      | COM.ibm.db2.jdbc.net.DB2Driver  | jdbc:db2://localhost:6789/foobar      |
 * MySQL    | org.gjt.mm.mysql.Driver         | jdbc:mysql://localhost/foobar         |
 * Oracle   | oracle.jdbc.driver.OracleDriver | jdbc:oracle:thin:@localhost:1521:ORCL |
 * Postgres | org.postgresql.Driver           | jdbc:postgresql://localhost/foobar    |
   SAP-DB   | com.sap.dbtech.jdbc.DriverSapDB | jdbc:sapdb://localhost/foobar         |
------------+---------------------------------+---------------------------------------+
</pre></td></tr></table>
<p>
With the <code>list-drivers</code>
command you can see what driver classes are registered and loaded. If you
cannot connect to some JDBC URL, check first, if the appropriate driver is
actually loaded. If it is not loaded, then it is probably not found in the
CLASSPATH. You can explicitly copy your frequently used drivers
into the installation directory (<code>/usr/share/henplus/</code>)</a></code>
-- all the jar/zip files
found there are added to the CLASSPATH on startup in
the <code>henplus</code> shellscript.
<p>
Drivers once registered are remembered by HenPlus, so that they are loaded
automatically on next startup. For registering and unregistering drivers,
see the online help:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
Hen*Plus&gt; help register
Hen*Plus&gt; help unregister
</pre></td></tr></table>
<p>
<a name="connect"/>
When the driver is loaded, you can connect to the database using the
JDBC-URL:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
Hen*Plus&gt; connect jdbc:oracle:thin:@localhost:1521:ORCL
HenPlus II connecting
 url 'jdbc:oracle:thin:@localhost:1521:ORCL'
 driver version 1.0
============ authorization required ===
Username: henman
Password: 
 Oracle - Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
JServer Release 8.1.7.0.1 - Production
 read committed *
 serializable
henman@oracle:localhost&gt;
</pre></td></tr></table>
This will then ask for the username and the password and you are
connected.
Since it is not possible to set the terminal to non-echo mode while
typing the password, a thread constantly redraws the prompt (This 
is after a hack
<a href="http://java.sun.com/features/2002/09/pword_mask.html">found here</a>; 
thanks so Alec Noronha for the link.
If the the redrawing causes trouble with your installation, please
let <a href="mailto:H.Zeller@acm.org">me</a> know).
<p>
Typing JDBC-URLs is tedious ? That&apos;s right, so HenPlus remembers all the
connection URLs you were connected to and provides it in the context
sensitive commandline completion for the connect command. So next time you
connect, you just type
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
Hen*Plus&gt; connect <b><font color="#3333FF">&lt;TAB&gt;</font></b>
jdbc:oracle:thin:@localhost:1521:ORCL  jdbc:oracle:thin:@database.my.net:1521:BLUE
Hen*Plus&gt; connect jdbc:oracle:thin:@<b><font color="#3333FF">d &lt;TAB&gt;</font></b>
Hen*Plus&gt; connect jdbc:oracle:thin:@database.my.net:1521:BLUE
</pre></td></tr></table>
.. and connecting with long URLs is a piece of cake.
<p>
On connection, the prompt changes to a string that reflects the current 
connection. By
default, this prompt is automatically extracted from the JDBC-URL, but you
can provide another name as second parameter in the connection command (see
help for the 'connect' command). Or just rename the session:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
henman@oracle:localhost&gt; rename-session hello
hello&gt;
</pre></td></tr></table>
<p>
You can disconnect with the <code>disconnect</code> command or by
simply pressing CTRL-D.

<h3><a name="multiple-open-sessions"/>Multiple Open Sessions</h3>
You can be connected to multiple databases at once; just issue the <code>connect</code>
command multiple times. You can list the sessions you are connected to
the <code>sessions</code> command.
Of course, the shell provides only access to one
session at a time so you can switch between the sessions with the 'switch' command.
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
Hen*Plus&gt; connect jdbc:oracle:thin:@localhost:1521:ORCL
<i>[... enter user/password ...]</i>
henman@oracle:localhost&gt; connect jdbc:mysql://localhost/test
mysql:localhost&gt; sessions
current session is marked with '*'
------------------------------+--------+---------------------------------------+
           <b>session</b>            |  <b>user</b>  |                 <b>url</b>                   |
------------------------------+--------+---------------------------------------+
    henman@oracle:localhost   | henman | jdbc:oracle:thin:@localhost:1521:ORCL |
  * mysql:localhost           | [NULL] | jdbc:mysql://localhost/test           |
------------------------------+--------+---------------------------------------+
mysql:localhost&gt; switch henman@oracle:localhost
henman@oracle:localhost&gt;
</pre></td></tr></table>
Of course, the switch command provides command line completion for the other
sessions names. If you only have two sessions, then its even easier: just
type <code>switch</code> without parameter (so in the example above, this would
have been sufficient).

<h3><a name="variables"/>Using Variables</h3>
You can use Variables, that can be used as text replacement everywhere. The
replacement works similar to shellscripts, however setting must be done
explicitly with the <code>set</code> command instead of a simple assignment:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
henman@oracle:localhost&gt; set-var tabname footab
henman@oracle:localhost&gt; select count(*) from ${tabname};
</pre></td></tr></table>

<p><b><!-- remove this note in later versions again -->
The command for setting variables has changed since Version 0.9 of HenPlus.
Previously, this command was 'set'; it changed to 'set-var', since several
Databases understand 'set' as a build in-command that otherwise would be
shadowed by HenPlus' set.
</b></p>

Variables can be expanded with or without curly braces: $FOO and ${FOO} is the same.
<p>
If you don't want a variable to be expanded, double the dollar-sign: <code>$$FOO</code>.
It does <em>not</em> help to embed it in single-quoted strings '$FOO'.
<p>
Unlike the shell, variables that are not set, are <em>not</em> expanded to an empty
string, but left as they are. So an unset variable $FOOBAR expands to .. $FOOBAR. This
is, because some strange scripts (esp. Oracle scripts) contain names with
dollar characters and thus would behave strange if the behaviour would be the
same as in shellscripts.
<p>
All variable settings can be shown with the <code>set-var</code> command 
without any parameters. The settings are stored, so that they are 
available on next startup.
<p>



<h3><a name="properties"/>Setting Properties</h3>
There are some global properties, that can be modified by the <code>set-property</code>
command. Connection session specific properties are handled with the 
<code>set-session-property</code> command. Corresponding <code>reset-*</code> commands reset
the property to its default.

<p>The usage of these commands is simple. If you just type the set-* command, then the
list of supported properties with short description is shown. With the property name given as
single parameter, the detailed help for that property is given. With an additional parameter the
property is actually set to that value:

<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
sa@hsqldb> select * from FOO ;
---+---+---+
 <b>X</b> | <b>Y</b> | <b>Z</b> |
---+---+---+
 1 | 2 | 3 |
---+---+---+
1 row in result (first row: 4 msec; total: 7 msec)
sa@hsqldb> set-property                      <b><font color="#3333FF">-- no parameters: show list</font></b>
-----------------------+-------+-------------------------------------------------------+
         <b>Name</b>          | <b>Value</b> |                      <b>Description</b>                      |
-----------------------+-------+-------------------------------------------------------+
 column-delimiter      | |     | modify column separator in query results              |
 comments-remove       | on    | switches the removal of SQL-comments                  |
 echo-commands         | off   | echo commands prior to execution.                     |
 sql-result-limit      | 2000  | set the maximum number of rows printed                |
 sql-result-showfooter | on    | switches if footer in selected tables should be shown |
 sql-result-showheader | on    | switches if header in selected tables should be shown |
-----------------------+-------+-------------------------------------------------------+
sa@hsqldb> set-property column-delimiter <b><font color="#3333FF">-- property name parameter: show detailed description</font></b>
<b>DESCRIPTION</b>
        Set another string that is used to separate columns in
        SQL result sets. Usually this is a pipe-symbol '|', but
        maybe you want to have an empty string ?
sa@hsqldb> set-property column-delimiter "****"       <b><font color="#3333FF">-- property name and value: set property</font></b>
sa@hsqldb> select * from FOO ;
------+------+------+
 <b>X</b> **** <b>Y</b> **** <b>Z</b> ****
------+------+------+
 1 **** 2 **** 3 ****
------+------+------+
1 row in result (3 msec)
sa@hsqldb> reset-property column-delimiter
</pre></td></tr></table>
<p>
Global properties are stored in your <a href="#per-project-config">project preferences</a>, so
that you don't have to retype them on next startup.
<p>
For supported properties of the SQL-connection session just type 
<code>set-session-property</code>. At present, the properties <code>auto-commit</code> and
<code>isolation-level</code> are supported. Unlike the global properties, this set of properties
is specific per session and is not stored.

<h3><a name="commandline-completion"/>Commandline Completion</h3>
HenPlus provides a commandline completion for virtually everything: tables in
select statements, column-names in where-clauses, variable-names in set/unset command,
variable names after typing '<code>$</code>'. Just try it ..


<h3><a name="per-project-config"/>Per Project Configuration</h3>
<em>HenPlus</em> stores the commandline history, the connection-URLs and
the variable settings persistently in the filesystem. This is written
to a directory <code>~/.henplus</code>, that is created in your
home directory.
<p>
If you have multiple projects you work on, all this information about any
of these projects is collected in the <code>~/.henplus</code> directory.
Since a per project separation of this information is desirable,
<em>HenPlus</em> supports per project storage of this information.
<p>
To store only information you use in some project, just create an empty 
<code>.henplus</code> directory in the directory you usually start the
<em>HenPlus</em> utility -- then <em>HenPlus</em> will use this directory to
store its configuration. More, if it does not find the <code>.henplus</code>
directory within the current working dir, it goes up the directory tree
until it finds the <code>.henplus</code> directory. If it still does
not find the directory, it falls back to the <code>~/.henplus</code> in your
home directory.
<p>
If you type 'help', then <em>HenPlus</em> tells you
in the <a href="#config-dir">last line</a>, where it has loaded its initial 
configuration from.

<h3><a name="plugins"/>Plugins</h3>
Need some command that creates some fancy statistics or report from your
database tables ? Or need a command
that you are missing from some other database tool ? Or want to pop up
some Swing-window to do database record editing <!-- Armin H. --> ?
<p>
HenPlus does not limit you to the features already provided.
It is very simple to write
plug-ins, that can be added and removed at run time. See the help for
<code>plug-in</code>, <code>plug-out</code> and <code>list-plugins</code> for details.
Basically, you just need to write a class that implements 
the <code>henplus.Command</code> interface. A plugin will register one (or a set of)
new commands that behave just like internal commands. They are, for instance,
available in the <code>help</code> command.
</p>
<p>
<p>There is one cool plugin shipped with version 0.9.4, provided by
<a href="mailto:martin.grotzke@javakaffee.de">Martin Grotzke</a>. It resides in the
henplus.jar, so if you have installed henplus, it is already there, but you have
to plug-in this command yourself; after plugging it in, HenPlus will 
<a href="#per-project-config">remember</a> this for future starts.

<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
Hen*Plus&gt; plug-in henplus.plugins.tablediff.TableDiffCommand
adding commands: tablediff
Hen*Plus&gt; list-plugins
loaded plugins are marked with '*'
----------------------------------------------+-----------+
                 plugin class                 | commands  |
----------------------------------------------+-----------+
 * henplus.plugins.tablediff.TableDiffCommand | tablediff |
----------------------------------------------+-----------+
</table>
What does this command do ? By providing two session names and a list of tables, 
you get the meta difference for corresponding tables in both sessions. So you
get whether columns have been added/removed or datatypes that are different for columns 
with the same name. This is particularly useful if you have multiple installations of
database schemas and wonder if they match (e.g. for test/productive environments);
a way to automatically create 'alter table'-scripts is planned.
</p><p>

<em>(TODO: This feature needs a way to load the classes from an URLClassloader,
 so its possible to dynamically reload the classes when they changed. Anyone?)</em>

<h3><a name="aliases"/>Aliases</h3>
For certain repeating tasks its tedious to write the same command over and over again.
Therefore it is possible to store aliases.
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
henman@oracle:localhost&gt; alias ls tables
</pre></td></tr></table>
This would alias the 'tables' command so that it can be called by simply typing
'ls'.
<p>
Parameters given to aliases are appended to the original command, so that you can 
define aliases for commands that need parameters:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
henman@oracle:localhost&gt; alias size select count(*) from
henman@oracle:localhost&gt; size footab
execute alias: select count(*) from footab
----------+
 <b>count(*)</b> |
----------+
        9 |
----------+
1 row in result (first row: 3 msec; total: 3 msec)
</pre></td></tr></table>
Note, that even TAB-completion (in this case: of the tablename) works: the alias
command peeks into the original command that is executed when you type 'size'.
<p>
All aliases can be shown with <code>list-aliases</code>; they are stored, so that they
are available on next startup.

<h3><a name="table-dumps"/>Database independent table dumps</h3>
You can dump out tables in a database independent format. See the online help for
<code>dump-out</code> and <code>dump-in</code>. You even can dump only selected
values of a certain table; see <code>dump-conditional</code> for this.
</p><p>
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
henman@oracle:localhost&gt; dump-out mytables.dump.gz student addresses;
</pre></td></tr></table>
.. dumps out the tables <code>student</code> and <code>addresses</code> to the file
<code>mytables.dump.gz</code>. The file is gzipped on-the-fly due to the <code>.gz</code>-suffix.
<p>
The format that is written must be database independent, thus it is not possible to store
them as simple <code>'INSERT INTO..'</code> statements, as the different databases have different
assumption how some data types should be parsed :-( Thus the dump format is a
canonical text format that resembles the original insert-statement arguments; it is easily
parseable for the human eye and external tools:
<hr noshade='noshade' size="1" />
<pre>
(tabledump 'student'
   (file-encoding 'UTF-8')
   (dump-version 1 1)
   (henplus-version '0.9.1')
   (database-info 'MySQL - 3.23.47')
   (meta ('name',   'sex',    'student_id')
         ('STRING', 'STRING', 'INTEGER'   ))
   (data ('Megan','F',1)
         ('Joseph','M',2)
         ('Kyle','M',3)
         ('Mac Donald\'s','M',4))
   (rows 4))
</pre>
<hr noshade='noshade' size="1" />
</p>

<h4>XML ?</h4>
One could argue, that XML would be an idea here, since it is hype and should
therefore be used everywhere :-) ... But seriously, I decided against it because it 
blows up the file size (and database export tend not to be small) and is not 
very human readable due to the 'noise'. The normal use-case of dumps like this is
to 
<ul>
  <li>Process them with line oriented tools (like <code>awk</code>, <code>sed</code> .. or your
      favourite editor)</li>
  <li>Construct insert/update scripts for databases manually.
</ul>
Both use-cases are addressed with this format: each data record is on a single line and is almost
compatible with the typical set-syntax of SQL. You can simply construct an insert-Statement
with this with minimal editing required 
(like <code>insert&nbsp;into&nbsp;student&nbsp;((name,&nbsp;sex,&nbsp;student_id)&nbsp;values&nbsp;('Joseph','M',2);</code>).
</p><p>
If you <em>want</em> XML export/import, just go ahead write a <a href='#plugins'>plugin</a>
that does this -- this would be great for many tools, as long as humans 
<a href="http://www.w3.org/XML/1999/XML-in-10-points#text">don't have to deal with it</a>.
</p>

<h3><a name="tree-view"/>Tree View of connected tables</h3>
Know this ? To just find out the datastructure of foreign-key connected
tables in your database you have to <code>describe</code> manually through
all tables. This is quite tedious. Thus, HenPlus provides a tree view of
your tables. Cyclic references are resolved by printing the recursive
entity in parenthesis.  See the example for some bugtracker database:
<table width="100%" bgcolor="#DDDDFF"><tr><td><pre>
henman@oracle:localhost&gt; tree-view BT_TRACKERUSER

BT_TRACKERUSER
|-- BT_BUGHISTORY
|   |-- BT_BUG
|   |   `-- (BT_BUGHISTORY)
|   `-- BT_BUGCOMMENTATTACHMENT
`-- BT_USERPERMISSION
265 msec
</pre></td></tr></table>
<hr noshade='noshade' size="1" />

<h3>Quotes</h3>
<dl>
  <dt>Kolja F. after installing HenPlus for a colleague (German)</dt>
  <dd>"Henplus ist wirklich dermassen geil. Der Macker, der mit sqlplus arbeiten musste, htte vor Freude fast geheult."</dd>
</dl>

<h3><a name="faq"/>FAQ</h3>
This will eventually be the FAQ. But for now, there is only one question..

<p>
<b>Q: </b><em>When I am connected to a postgreSQL database, sometimes the
             connection seems to stop working. No select works.</em>
<br>
<b>A: </b>This is primarily not a problem with HenPlus, but in general with
         Postgres when it encounters an error in some SQL-statement. Since
	 everything in Postgres is done within a transaction, a
	 transaction is regarded invalid on some error; any subsequent 
	 commands are
	 ignored, until you finish the transaction with 'commit' or 'rollback'.
	 This might be annoying if you run SQL-scripts -- if there is only
	 one error, all subsequent commands are ignored. One solution
	 might be to switch on autocommit 
	 (HenPlus command: <code>set-session-property auto-commit on</code>).
<br>
<hr noshade="noshade" size="1"/>
Thanks Rebecca for proof reading.
<div align="right"><font size="-2">$Id: HenPlus.html,v 1.21 2004/02/01 20:57:26 hzeller Exp $ &copy; Henner Zeller &lt;<a href="mailto:H.Zeller@acm.org">H.Zeller@acm.org</a>&gt;</font></div>