This chapter explains how to quickly install, run, and use HSQLDB on UNIX.

HSQLDB has lots of great optional features. I intend to cover very few of them. I do intend to cover what I think is the most common UNIX setup: To run a multi-user database with permament data persistence. (By the latter I mean that data is stored to disk so that the data will persist across database shutdowns and startups). I also cover how to run HSQLDB as a system daemon.

Go to http://sourceforge.net/projects/hsqldb and click on the "files" link. You want the current version. This will be the highest numbered version under the plain black "hsqldb" heading. See if there's a distribution for the current HSQLDB version in the format that you want.

If you want an rpm, you should still find out the current version of HSQLDB as described in the previous paragraph. Then click "hsqldb" in the "free section" of http://www.jpackage.org/ and see if they have the current HSQLDB version built yet. Hopefully, the JPackage folk will document what JVM versions their rpm will support (currently they document this neither on their site nor within the package itself). (I really can't document how to download from a site that is totally beyond my control).

Binary installation depends on the package format that you downloaded.

Take a look at the files you installed. (Under hsqldb for zip file installations. Otherwise, use the utilities for your packaging system). The most important file of the hsqldb system is hsqldb.jar, which resides in the directory lib.

If the description of your distribution says that the hsqldb.jar file will work for your Java version, then you are finished with installation. Otherwise you need to build a new hsqldb.jar file.

If you followed the instructions above and you still don't know what Java version your hsqldb.jar supports, then read HSQLDB_HOME/readme.txt and HSQLDB_HOME/index.html. If that still doesn't help, then you can just try your hsqldb.jar and see if it works, or build your own.

To use the supplied hsqldb.jar, just skip to the next section of this document. Otherwise build a new hsqldb.jar.

See the Building HSQLDB appendix if you want to build anything other than hsqldb.jar with all default settings.

If you installed from an OS-specific package, you may already have a database instance and server pre-configured. See if your package includes a file named server.properties (make use of your packaging utilities). If you do, then I suggest that you still read this section while you poke around, in order to understand your setup.

Copy the file HSQLDB_HOME/src/org/hsqldb/sample/sqltool.rc to the HSQLDB_OWNER's home directory. Use chmod to make the file readable and writable only to HSQLDB_OWNER.

# $Id: sqltool.rc,v 1.22 2007/08/09 03:22:21 unsaved Exp $

# This is a sample RC configuration file used by SqlTool, DatabaseManager,
# and any other program that uses the org.hsqldb.util.RCData class.

# You can run SqlTool right now by copying this file to your home directory
# and running
#    java -jar /path/to/hsqldb.jar mem
# This will access the first urlid definition below in order to use a 
# personal Memory-Only database.
# "url" values may, of course, contain JDBC connection properties, delimited
# with semicolons.

# If you have the least concerns about security, then secure access to
# your RC file.
# See the documentation for SqlTool for various ways to use this file.

# A personal Memory-Only (non-persistent) database.
urlid mem
url jdbc:hsqldb:mem:memdbid
username sa
password

# A personal, local, persistent database.
urlid personal
url jdbc:hsqldb:file:${user.home}/db/personal;shutdown=true
username sa
password
# When connecting directly to a file database like this, you should 
# use the shutdown connection property like this to shut down the DB
# properly when you exit the JVM.

# This is for a hsqldb Server running with default settings on your local
# computer (and for which you have not changed the password for "sa").
urlid localhost-sa
url jdbc:hsqldb:hsql://localhost
username sa
password



# Template for a urlid for an Oracle database.
# You will need to put the oracle.jdbc.OracleDriver class into your 
# classpath.
# In the great majority of cases, you want to use the file classes12.zip
# (which you can get from the directory $ORACLE_HOME/jdbc/lib of any
# Oracle installation compatible with your server).
# Since you need to add to the classpath, you can't invoke SqlTool with
# the jar switch, like "java -jar .../hsqldb.jar..." or 
# "java -jar .../hsqlsqltool.jar...".
# Put both the HSQLDB jar and classes12.zip in your classpath (and export!)
# and run something like "java org.hsqldb.util.SqlTool...".

#urlid cardiff2
#url jdbc:oracle:thin:@aegir.admc.com:1522:TRAFFIC_SID
#username blaine
#password secretpassword
#driver oracle.jdbc.OracleDriver



# Template for a TLS-encrypted HSQLDB Server.
# Remember that the hostname in hsqls (and https) JDBC URLs must match the
# CN of the server certificate (the port and instance alias that follows 
# are not part of the certificate at all).
# You only need to set "truststore" if the server cert is not approved by
# your system default truststore (which a commercial certificate probably
# would be).

#urlid tls
#url jdbc:hsqldb:hsqls://db.admc.com:9001/lm2
#username blaine
#password asecret
#truststore /home/blaine/ca/db/db-trust.store


# Template for a Postgresql database
#urlid blainedb
#url jdbc:postgresql://idun.africawork.org/blainedb
#username blaine
#password losung1
#driver org.postgresql.Driver

# Template for a MySQL database.  MySQL has poor JDBC support.
#urlid mysql-testdb
#url jdbc:mysql://hostname:3306/dbname
#username root
#username blaine
#password hiddenpwd
#driver com.mysql.jdbc.Driver

# Note that "databases" in SQL Server and Sybase are traditionally used for
# the same purpose as "schemas" with more SQL-compliant databases.

# Template for a Microsoft SQL Server database
#urlid msprojsvr
#url jdbc:microsoft:sqlserver://hostname;DatabaseName=DbName;SelectMethod=Cursor
# The SelectMethod setting is required to do more than one thing on a JDBC
# session (I guess Microsoft thought nobody would really use Java for 
# anything other than a "hello world" program).
# This is for Microsoft's SQL Server 2000 driver (requires mssqlserver.jar
# and msutil.jar).
#driver com.microsoft.jdbc.sqlserver.SQLServerDriver
#username myuser
#password hiddenpwd

# Template for a Sybase database
#urlid sybase
#url jdbc:sybase:Tds:hostname:4100/dbname
#username blaine
#password hiddenpwd
# This is for the jConnect driver (requires jconn3.jar).
#driver com.sybase.jdbc3.jdbc.SybDriver

# Template for Embedded Derby / Java DB.
#urlid derby1
#url jdbc:derby:path/to/derby/directory;create=true
#username ${user.name}
#password any_noauthbydefault
#driver org.apache.derby.jdbc.EmbeddedDriver
# The embedded Derby driver requires derby.jar.
# There'a also the org.apache.derby.jdbc.ClientDriver driver with URL
# like jdbc:derby://<server>[:<port>]/databaseName, which requires
# derbyclient.jar.
# You can use \= to commit, since the Derby team decided (why???)
# not to implement the SQL standard statement "commit"!!
# Note that SqlTool can not shut down an embedded Derby database properly,
# since that requires an additional SQL connection just for that purpose.
# However, I've never lost data by not shutting it down properly.
# Other than not supporting this quirk of Derby, SqlTool is miles ahead of ij.

We will be using the "localhost-sa" sample urlid definition from the config file. The JDBC URL for this urlid is jdbc:hsqldb:hsql://localhost. That is the URL for the default database instance of a HSQLDB Server running on the default port of the local host. You can read about URLs to connect to other instances and other servers in the Advanced Topics chapter.

Run SqlTool.

    java -jar path/to/hsqldb.jar localhost-sa

    set password "newpassword";

When you're finished playing, exit with the command \q.

If you changed the SA password, then you need to fix the password in the sqltool.rc file accordingly.

You can, of course, also access the database with any JDBC client program. See the First JDBC Client Example appendix. You will need to modify your classpath to include hsqldb.jar as well as your client class(es). You can also use the other HSQLDB client programs, such as org.hsqldb.util.DatabasManagerSwing, a graphical client with a similar purpose to SqlTool.

You can use any normal UNIX account to run the JDBC clients, including SqlTool, as long as the account has read access to the hsqldb.jar file and to an sqltool.rc file. See the SqlTool chapter about where to put sqltool.rc, how to execute sql files, and other SqlTool features.

Connect to the database as SA (or any other Administrative user) and run CREATE USER to create new accounts for your database instance. HSQLDB accounts are database-instance-specific, not Server-specific.

For the current version of HSQLDB, only users with Role of DBA may create or own database objects. DBA members have privileges to do anything. Non-DBAs may be granted some privileges, but may never create or own database objects. (Before long, non-DBAs will be able to create objects if they have permission to do so in the target schema). When you first create a hsqldb database, it has only one database user-- SA, a DBA account, with an empty string password. You should set a password (as described above). You can create as many additional users as you wish. To make a user a DBA, you can use the "ADMIN" option to the CREATE USER command, or GRANT the DBA Role to the account after creating it.

If you create a user without the ADMIN tag (and without granting the DBA role to them) this user will be able to read the data dictionary tables, but will be able unable to create or own his own objects. He will have only the rights which the pseudo-user PUBLIC has. To give him more permissions, even rights to read objects, you can GRANT permissions for specific objects, grant Roles (which encompass a set of permissions), or grant the DBA Role itself.

Since only people with a database account may do anything at all with the database, it is often useful to permit other database users to view the data in your tables. To optimize performance, reduce contention, and minimize administration, it is often best to grant SELECT to PUBLIC on any object that needs to be accessed by multiple database users (with the significant exception of any data which you want to keep secret).

Do a clean database shutdown when you are finished with the database instance. You need to connect up as SA or some other Admin user, of course. With SqlTool, you can run

    java -jar path/to/hsqldb.jar --sql shutdown localhost-sa

You can, of course, run HSQLDB through inittab on System V UNIXes, but usually an init script is more convenient and manageable. This section explains how to set up and use our UNIX init script. Our init script is only for use by root. (That is not to say that the Server will run as root-- it usually should not).

The main purpose of the init script is to start up a Server with the database instances specified in your server.properties file; and to shut down all of those instances plus additional urlids which you may (optionally) list in your init script config file. These urlids must all have entries in a sqltool.rc file. If, due to firewall issues, you want to run a WebServer instead of a Server, then make sure you have a healthy WebServer with a webserver.properties set up, adjust your URLs in sqltool.rc, and set TARGET_CLASS in the config file. (By following the commented examples in the config file, you can start up any number of Server and/or WebServer listeners with or without TLS ecryption).

After you have the init script set up, root can use it anytime to start or stop HSQLDB. (I.e., not just at system bootup or shutdown).

    sh -x path/to/hsqldb start

    sh -x path/to/hsqldb start > /tmp/hstart.log 2>&1