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 I had no Solaris machine at home -- and the only SQLPlus I could use back then was running on some Redmond operating system that lacked (and still lacks) the environment to seriously develop software.
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 GNU Public License .
This is where HenPlus steps in. It supports:
connect
command.
describe
command for database tables, that work all
JDBC-Drivers that unveil the appropriate MetaData (Oracle, PostgreSQL,
MySQL, DB2...)
start
,
@
,
@@
,
spool
) 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
me
know.
As of March 2005, HenPlus is included in some distributions, for example Debian testing and unstable, Ubuntu and Gentoo . Use the respective package management tool to install HenPlus on these distributions.
For an installation on fink for Mac have a look at Bodo Tasche's blog . If you have a hard time to get the java-readline compiled on Mac OS X, then you might want to check out Nathan Toone's blog for an installation description.
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 >= java-readline 0.7.3 . ( compilation on Mac OS X )
To build HenPlus the
ant build
tool
(Version >= 1.4) is required. To compile HenPlus, make sure that the
libreadline-java.jar
is in the classpath (RedHat and Debian installs the
java-readline package in
/usr/share/java/libreadline-java.jar
).
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).
Now, just type
$ ant jarIf you are root, then you can install it with:
# ant installwhich will install henplus in
/usr/share/henplus/henplus.jar | 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). |
/usr/bin/henplus | shellscript to start henplus |
'prefix'
:
$ ant -Dprefix=/usr/local install
(For package providers: the build.xml provides as well
the
DESTDIR
parameter with a similar meaning as in usual
Makefiles)
If you've created packages for other operating systems (like Solaris) or Windows, please let me know .
I haven't compiled this on Windows, but it shouldn't be a big deal .. if you manage to compile the java-readline. This is an JNI shared library and thus platform dependant. If you did it, please post your experience, so that we can include it in this documentation.
henplus
shell script
with or without an jdbc-url on the command line.
$ henplus jdbc:mysql://localhost/foobar
If the first line, henplus writes reads:
no readline found (no JavaReadline in java.library.path). Using simple stdin... 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
/usr/bin/henplus
shellscript so that the
LD_LIBRARY_PATH
contains the
directory where
libJavaReadline.so
resides.
help
and
connect
. The help command gives an overview, what commands
are supported:
Hen*Plus> 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 : handle Plugins list-drivers | register | unregister : handle JDBC drivers list-aliases | alias | unalias : handle Aliases list-key-bindings | bind-key-cmd-string : handle function key bindings load | start | @ | @@ : load file and execute commands connect | disconnect | rename-session | switch | sessions: manage sessions status : show status of this connection tables | views | rehash : list available user objects describe <tablename> : describe a database object tree-view <tablename> : tree representation of connected tables dump-out | dump-in | verify-dump | dump-conditional: handle table dumps system | ! : execute system commands set-var | unset-var : set/unset variables set-property | reset-property : set global HenPlus properties set-session-property | reset-session-property: set SQL-connection specific properties config read from [/home/hzeller/.henplus]
You exit the HenPlus shell by typing the
exit
or
quit
command or by just typing EOF-Character (CTRL-D).
For almost all commands, there is more detailed help available. You can
just explore the commands by typing
help [commandname]
and
learn what the built-in commands are all about; all help entries have
a synopsis and a more detailed description like in the following example:
Hen*Plus> help system SYNOPSIS system <system-shell-commandline> DESCRIPTION Execute a system command in the shell. You can only invoke commands, that do not expect anything from stdin: the interactive input from HenPlus is disconnected from the subprocess' stdin. But this is useful to call some small commands in the middle of the session. There are two syntaxes supported: system <command> or even shorter with the exclamation mark: !<command>. Example: !ls
But for now, we are interested how to get connected, so start with the
connect
command
Hen*Plus> help connect(more details on connection to the database in the Getting connected section).
register
,
unregister
and
list-drivers
to manage the drivers
(you know it already: the
help
command tells you more details).
The JDBC-Driver must be in the classpath to be used by HenPlus. Therefore, you can either add it to the Classpath manually:
$ CLASSPATH=$CLASSPATH:/path/to/my/driver.jar $ export CLASSPATH $ henplus
The simplest way, however, is to put the jar file with the driver in the
/usr/share/henplus
directory:
# cp /path/to/my/driver.jar /usr/share/henplus $ henplus
Since you need to be root to copy the jar-file to that directory, it is
as well possible to add the driver to the
.henplus/lib
directory
in your home-directory
$ mkdir -p ~/.henplus/lib $ cp /path/to/my/driver.jar ~/.henplus/libIf you use different
.henplus
directories for the
per project configuration
, you need to put the
driver in that directory.
By registering the driver, you allow HenPlus to associate the JDBC-Urls with the appropriate JDBC-Drivers.
Some of the common drivers are already registered by default. To see, what drivers are registered and which of them are actually found in the CLASSPATH, use thelist-drivers
command.
Hen*Plus> list-drivers loaded drivers are marked with '*' (otherwise not found in CLASSPATH) ------------+---------------------------------+---------------------------------------+ for | driver class | sample url | ------------+---------------------------------+---------------------------------------+ 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 | ------------+---------------------------------+---------------------------------------+
With the
list-drivers
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 (see
Put the JDBC-Driver in the CLASSPATH
).
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:
Hen*Plus> help register Hen*Plus> help unregister
When the driver is loaded, you can connect to the database using the JDBC-URL:
Hen*Plus> 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>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 found here ; thanks so Alec Noronha for the link. If the the redrawing causes trouble with your installation, please let me know).
Note, that many JDBC-Drivers allow for a URL-syntax, that already includes the name and the password; in that case, you can connect directly without typing the user/password; for example in Oracle:
Hen*Plus> connect jdbc:oracle:thin: foo/bar @localhost:1521:BLUE HenPlus II connecting url 'jdbc:oracle:thin:foo/bar@localhost:1521:BLUE' driver version 1.0 Oracle - Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning option JServer Release 9.2.0.1.0 - Production read committed * serializable FOO@oracle:localhost>
Typing JDBC-URLs is tedious ? That'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
Hen*Plus> connect <TAB> jdbc:oracle:thin:@localhost:1521:ORCL jdbc:oracle:thin:@database.my.net:1521:BLUE Hen*Plus> connect jdbc:oracle:thin:@ d<TAB> Hen*Plus> connect jdbc:oracle:thin:@database.my.net:1521:BLUE.. and connecting with long URLs is a piece of cake.
Define an easy to remember alias for a connection On connect, you can provide a short name for that session as second parameter after the JDBC-URL:
Hen*Plus> connect jdbc:oracle:thin:@database.my.net:1521:BLUE myora [.. connect to jdbc:oracle:thin:@database.my.net:1521:BLUE ..] myora>This will do two things: first the session name will be renamed after this alias and this name can be used as a shortcut for later connections:
Hen*Plus> connect myora [.. connect to jdbc:oracle:thin:@database.my.net:1521:BLUE ..] myora>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:
henman@oracle:localhost> rename-session hello hello>
You can disconnect with the
disconnect
command or by
simply pressing CTRL-D.
echo "*** The build-in help ***" ; help
The SQL commands however (you guess it: 'select', 'update', 'create' .. ) are not complete after the newline; you always have to close them with a semicolon -- so it is possible to write statements on multiple lines:
oracle:localhost> create table foobar ( id number(10) primary key, text varchar(127) ); ok. (70 msec) oracle:localhost>
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):
oracle:localhost> 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>(The current keywords that trigger requiering the '/' as command separator are "CREATE" followed by PROCEDURE, TRIGGER, FUNCTION or PACKAGE later. If this is not enough, please let me know).
load
-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:
select * from foobar; -- this is a comment
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
/* some comment */
.
/* This is a longer comment that goes across several lines */ select * from foobar;
Another style of comments, allowed for instance in the MySQL-Shell is the UNIX-shell like '#' endline comment; however, this character is only allowed as first character of a line to be a comment -- otherwise using the Hash-Symbol in normal SQL-Statements (e.g. column names), would not work.
# this is a comment. select * from foobar; create table foo (id# number); -- the hash here does not start a commentOne beta tester requested an additional type of comment: Two semicolons at the beginning 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 only regarded as comment, if they are the first on a line:
;; 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"
To make a long story short -- the supported types of comments are
--
(two dashes) comment until end of line
#
comment until end of line
;;
comment until end of line
/* range comment */
Not
supported is the C++/Java like endline comment that starts with
two slashes
//
. The reason is, that many JDBC-URLs contain
these two slashes, and we don't want to comment these out, right ? ;-)
If you encounter some other reasonable style of comments you want that it is supported by HenPlus, please let me know.
Switching off comment removal... Usually, all comments are removed in HenPlus before sending commands to the database, because many JDBC-drivers or databases cannot handle comments sufficiently. However, sometimes it is necessary not to remove comments, since some databases use comments to convey hinting in statements. In Oracle, for instance, you give hints to the query optimizer in the form
select /*+ index(foo,foo_a_idx) */ a from foo where ...For this reason, it is possible to switch off automatic comment removal. This is the global HenPlus property
comments-remove
; it allows to switch off comment
removal, so strings are sent to the database as-is. The command is
set-property comments-remove off
connect
command multiple times. You can list the sessions you are connected to
the
sessions
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.
Hen*Plus> connect jdbc:oracle:thin:@localhost:1521:ORCL [... enter user/password ...] henman@oracle:localhost> connect jdbc:mysql://localhost/test mysql:localhost> sessions current session is marked with '*' ------------------------------+--------+---------------------------------------+ session | user | url | ------------------------------+--------+---------------------------------------+ henman@oracle:localhost | henman | jdbc:oracle:thin:@localhost:1521:ORCL | * mysql:localhost | [NULL] | jdbc:mysql://localhost/test | ------------------------------+--------+---------------------------------------+ mysql:localhost> switch henman@oracle:localhost henman@oracle:localhost>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
switch
without parameter (so in the example above, this would
have been sufficient).
set
command instead of a simple assignment:
henman@oracle:localhost> set-var tabname footab henman@oracle:localhost> select count(*) from ${tabname};
NOTE: 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.
Setting variables with values that span multiple lines have to be embraced by single or double-quotes; in the next example we use a variable to define storage options once and reuse it for multiple tables in Oracle (note the quotes):
henman@oracle:localhost> set-var STORAGE_OPTIONS " pctused 40 pctfree 10 storage ( initial 512k NEXT 1024k PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS UNLIMITED ) TABLESPACE users " henman@oracle:localhost> create table foo (id number(10), text varchar2(1024)) ${STORAGE_OPTIONS}; henman@oracle:localhost> create table bar (id number(10), description varchar2(1024)) ${STORAGE_OPTIONS};
Variables can be expanded with or without curly braces: $FOO and ${FOO} is the same.
If you don't want a variable to be expanded, double the dollar-sign:
$$FOO
;
the Unix-shell way to embed it in single-quotes (like '$FOO') does
not
work.
Unlike the shell, variables that are not set, are not expanded to an empty string, but left as they are. So an unset variable $FOOBAR expands to .. $FOOBAR. This is, because some databases (especially Oracle in system views) use names with dollar characters; thus character sequences starting with '$' that cannot be recognized as HenPlus-variable have to be left as they are.
Special variables are variables that have a special meaning to HenPlus. Currently there is only one special variable, that is set automatically.
Variable | Description |
---|---|
_HENPLUS_LAST_COMMAND
|
This variable contains the last command executed by HenPlus |
All variable settings can be shown with the
set-var
command
without any parameters. Unsetting variables works with
unset-var
.
henman@oracle:localhost> set-var -----------------------+------------------------------------------------------------------------------------+ Name | Value | -----------------------+------------------------------------------------------------------------------------+ STORAGE_OPTIONS | pctused 40 pctfree 10 | | storage ( initial 512k NEXT 1024k PCTINCREASE 0 | | MINEXTENTS 1 MAXEXTENTS UNLIMITED ) | | TABLESPACE users | _HENPLUS_LAST_COMMAND | create table bar (id number(10), description varchar2(1024)) pctused 40 pctfree 10 | | storage ( initial 512k NEXT 1024k PCTINCREASE 0 | | MINEXTENTS 1 MAXEXTENTS UNLIMITED ) | | TABLESPACE users; | tabname | footab | -----------------------+------------------------------------------------------------------------------------+ henman@oracle:localhost> unset-var STORAGE_OPTIONS tabname henman@oracle:localhost> set-var -----------------------+-----------------------------------+ Name | Value | -----------------------+-----------------------------------+ _HENPLUS_LAST_COMMAND | unset-var STORAGE_OPTIONS tabname | -----------------------+-----------------------------------+
All non-special variables that are set at the end of the session are stored, so that they are available on next startup.
set-property
command. Connection session specific properties are handled with the
set-session-property
command. Corresponding
reset-*
commands reset
the property to its default.
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:
sa@hsqldb> set-property -- no parameters: show list -----------------------+-------+-------------------------------------------------------+ Name | Value | Description | -----------------------+-------+-------------------------------------------------------+ 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 -- property name parameter: show detailed description DESCRIPTION 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 "****" -- property name and value: set property
Note, that the property names are TAB-completed and even the values, if they are restricted (like 'on', 'off').
Global properties are stored in your project preferences , so that you don't have to retype them on next startup.
sa@hsqldb> select * from FOO ; ---+---+---+ X | Y | Z | ---+---+---+ 1 | 2 | 3 | ---+---+---+ 1 row in result (first row: 4 msec; total: 7 msec) sa@hsqldb> set-property column-delimiter "****" -- property name and value: set property sa@hsqldb> select * from FOO ; ------+------+------+ X **** Y **** Z **** ------+------+------+ 1 **** 2 **** 3 **** ------+------+------+ 1 row in result (3 msec) sa@hsqldb> reset-property column-delimiter
For supported properties of the SQL-connection session just type
set-session-property
. At present, the properties
auto-commit
,
isolation-level
and
read-only
are supported.
Unlike the global properties, this set of properties is specific per session and is not stored.
The values that are possible for the transaction isolation are provided by the JDBC-driver and are conveniently provided by the TAB-completion:
henman@oracle:localhost> set-session-property -----------------+----------------+-----------------------------------------------+ Name | Value | Description | -----------------+----------------+-----------------------------------------------+ auto-commit | off | Switches auto commit | isolation-level | read-committed | sets the transaction isolation level | read-only | off | Switches on read only mode for optimizations. | -----------------+----------------+-----------------------------------------------+ henman@oracle:localhost> set-session-property i <TAB> henman@oracle:localhost> set-session-property isolation-level <TAB><TAB> read-committed serializable henman@oracle:localhost> set-session-property isolation-level s <TAB> henman@oracle:localhost> set-session-property isolation-level serializable henman@oracle:localhost> set-session-property -----------------+--------------+-----------------------------------------------+ Name | Value | Description | -----------------+--------------+-----------------------------------------------+ auto-commit | off | Switches auto commit | isolation-level | serializable | sets the transaction isolation level | read-only | off | Switches on read only mode for optimizations. | -----------------+--------------+-----------------------------------------------+
HenPlus provides the common amenities for command line editing and history (cursor-up/down, CTRL-R) that are already known from bash (See the Bash Command Line Editing documentation for details).
In addition to that, HenPlus provides TAB-completion for virtually everything that can be completed according to the context: tables in select statements, column-names in where-clauses, variable-names in set-var/unset-var command, variable names after typing '$
' or
'${'
, Function Key Names
in the bind-key-cmd command, property names and its values ..
~/.henplus
.
Whenever you start henplus, this configuration directory is used by
default.
If you have multiple projects you work on, the settings of all
these projects would collected in the
~/.henplus
directory, thus
your command history would fill up with commands not needed in that
project or you need switch settings all the time.
So if you work in multiple projects with specific connect strings,
variables, properties etc. a per project separation of this information is
desirable.
HenPlus
supports per project storage of this information.
To store only information you use in some project, just create an empty
.henplus
directory in the directory you usually start the
HenPlus
utility -- then
HenPlus
will use this directory to
store its configuration. More, if it does not find the
.henplus
directory within the current working dir, it goes up the directory tree
until it finds the
.henplus
directory. If it still does
not find the directory, it falls back to the
~/.henplus
in your
home directory.
As an example consider these directories:
/home/hzeller/.henplus/ /home/hzeller/projects/project-a/.henplus/ /home/hzeller/projects/project-b/.henplus/Starting HenPlus in any directory below
/home/hzeller/projects/project-a/
will use
the
/home/hzeller/projects/project-a/.henplus/
configuration directory, same for any directory below
/home/hzeller/projects/project-b/
for instance
$ cd /home/hzeller/projects/project-b/src/henplus/commands $ henplus using GNU readline (Brian Fox, Chet Ramey), Java wrapper by Bernhard Bablok henplus config at /home/hzeller/projects/project-b/.henplus [...]
Starting henplus anywhere else will use the configuration in the
home directory in
/home/hzeller/
If you type 'help', then HenPlus tells you in the last line , where it has loaded its initial configuration from.
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
plug-in
,
plug-out
and
list-plugins
for details.
Basically, you just need to write a class that implements
the
henplus.Command
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
help
command.
There is one cool plugin shipped since version 0.9.4, provided by Martin Grotzke . 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 remember this for future starts.
Hen*Plus> plug-in henplus.plugins.tablediff.TableDiffCommand adding commands: tablediff Hen*Plus> list-plugins loaded plugins are marked with '*' ----------------------------------------------+-----------+ plugin class | commands | ----------------------------------------------+-----------+ * henplus.plugins.tablediff.TableDiffCommand | tablediff | ----------------------------------------------+-----------+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.
(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?)
henman@oracle:localhost> alias ls tablesThis would alias the 'tables' command so that it can be called by simply typing 'ls'.
Parameters given to aliases are appended to the original command, so that you can define aliases for commands that need parameters:
henman@oracle:localhost> alias size select count(*) from henman@oracle:localhost> size footab execute alias: select count(*) from footab ----------+ count(*) | ----------+ 9 | ----------+ 1 row in result (first row: 3 msec; total: 3 msec)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'.
All aliases can be shown with
list-aliases
; they are stored, so that they
are available on next startup.
(TODO: cool would be aliases with parameters,
like
select * from ${0} where ${1}=${2}
)
In HenPlus it is possible to bind function keys to commands to be executed (a bit depending on the terminal, but should work on usual Unix terminals). It is possible to bind an arbitrary string to a function key; the string is issued whenever the function key is pressed. This can be used as a shortcut to excute commands; so instead of pressing help<RETURN> you could bind the same command to a function key:
bind-key-cmd F1 "help\n"
The function keys F1..F12 and Shift-F1..Shift-F12 are available.
With the keybinding this is simple to do. Consider, you want to set a bookmark with Shift-F2 and execute it with F2. So what we want to do on Shift-F2 is to bind the command we just executed to F2. With a little quoting and use of the special variable ${_HENPLUS_LAST_COMMAND} we can do the trick:
bind-key-cmd Shift-F2 "bind-key-cmd F2 \"$${_HENPLUS_LAST_COMMAND};\\n\"\n"
to bind the last command executed to F2 (Note the double $$ and the \\ to prevent certain elements bo be expanded prematurely). So executing Shift-F2 will now execute the command
bind-key-cmd F2 "${_HENPLUS_LAST_COMMAND};\n"
that binds F2 to the command that has been just executed.
So pressing Shift-F2 sets a bookmark and F2 executes it.
dump-out
and
dump-in
. You even can dump only selected
values of a certain table; see
dump-conditional
for this.
henman@oracle:localhost> dump-out mytables.dump.gz student addresses;.. dumps out the tables
student
and
addresses
to the file
mytables.dump.gz
. Note, that the file is gzipped automatically
on-the-fly if the file contains a
.gz
-suffix.
The format that is written must be database independent, thus it is not possible to store
them as simple
'INSERT INTO..'
statements, as the different databases have different
assumption how some data types should be parsed (esp. dates..) :-( 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; it is sufficiently simply to create
'INSERT INTO'-scripts with simple Shell scripts:
(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))
Note, not all data types are supported yet: the CLOB and BLOB data type are handled too differently even though JDBC should allow access database independently. This will be included in a future
awk
,
sed
.. or your
favourite editor)
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 format with only minimal editing required like:
insert into student (name, sex, student_id) values ('Joseph','M',2) ;
Ok, XML would be great for many other tools (as long as humans don't have to deal with it ) .. but I don't use them. So if you want XML export/import, you are free to do it: just go ahead and write a plugin that does this and let me know for integration.
describe
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:
henman@oracle:localhost> tree-view BT_TRACKERUSER BT_TRACKERUSER |-- BT_BUGHISTORY | |-- BT_BUG | | `-- (BT_BUGHISTORY) | `-- BT_BUGCOMMENTATTACHMENT `-- BT_USERPERMISSION 265 msec
Q:
When I am connected to a postgreSQL database, sometimes the
connection seems to stop working. No select works.
A:
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 after 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:
set-session-property auto-commit on
).
Q:
Is it possible to have henplus use the vi-editing mode rather than
the default emacs like editing mode ?
A:
Create/edit the ~/.inputrc configuration file to influence the behaviour of
readline. Add a line
set editing-mode viTo this file. Then set the environment-variable INPUTRC to point to this file:
export INPUTRC=~/.inputrc(thanks to Scott Plante)