Chapter 3. UNIX Quick Start

## Chapter 3. UNIX Quick Start

### How to quickly get Hsqldb up and running on UNIX, including Mac OS X

### Blaine Simpson

HSQLDB Development Group
`<blaine.simpson@admc.com>`

$Date: 2004/07/16 13:13:07 $

## Purpose

This chapter explains how to quickly install, run, and use HSQLDB version 1.7.2 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.

## Installation

Go to http://sourceforge.net/projects/hsqldb and click on the "files" link. Look for "hsqldb_1_7_2" under lower-case "hsqldb". Click on "show only this release" link right after "hsqldb_1_7_2".

Click the "hsqldb_1_7_2" link to find out what version of Java this binary HSQLDB distribution was built with. Don't hold me to it, but if you get your distro from us at SourceForge, it will probably be built with Java 1.4. Choose a binary package format that will work with your UNIX variant and which supports your Java version. Otherwise choose the hsqldb_1_7_2.zip file. Click the filename to download it. (If you have an older version of Java and there's nothing preventing you from upgrading it, you'll probably be happier in the end if you upgrade Java rather than downgrading HSQLDB).

If you want an rpm, then click "hsqldb" in the "free section" of http://www.jpackage.org/. 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). Download the package you want, making sure that you get version 1.7.2 of HSQLDB. (I really can't document how to download from a site that is totally beyond my control).

### Note

It could very well happen that some of the file formats which I discuss here are not in fact offered. If so, then we have not gotten around to building them.

Binary installation depends on the package format that you downloaded.

Installing from a .pkg.Z file

  This package is only for use by a Solaris super-user. It's a System V package. Download then uncompress the package with uncompress or gunzip

          uncompress filename.pkg.Z

  You can read about the package by running

          pkginfo -l -d filename.pkg

  Run pkgadd as root to install.

          pkgadd filename.pkg

Installing from a .rpm file

  This is a Linux rpm package. After you download the rpm, you can read about it by running

          rpm -qip /path/to/file.rpm

  Rpms can be installed or upgraded by running

          rpm -Uvh /path/to/file.rpm

  as root.
Installing from a .zip file

  Extract the zip file to the parent directory of the new HSQLDB home. You don't need to create the HSQLDB_HOME directory because the extraction will create it for you with the right name)

          cd parent/of/new/hsqldb/home
          unzip /path/to/file.zip

  All the files in the zip archive will be extracted to underneath a new `hsqldb` directory.

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`.

### Important

For the purposes of this chapter, I define HSQLDB_HOME to be the parent directory of the lib directory that contains `hsqldb.jar`. E.g., if your path to `hsqldb.jar` is `/a/b/hsqldb/lib/hsqldb.jar`, then your HSQLDB_HOME is `/a/b/hsqldb`.

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`.

Procedure 3.1. Building hsqldb.jar

1. If you don't already have Ant, download the latest stable binary version from http://ant.apache.org. cd to where you want Ant to live, and extract from the archive with

           unzip /path/to/file.zip

   or

           tar -xzf /path/to/file.tar.gz

   or

           bunzip2 -c /path/to/file.tar.bz2 | tar -xzf -

   Everything will be installed into a new subdirectory named `apache-ant- + version`. You can rename the directory after the extraction if you wish.

2. Set the environmental variable `JAVA_HOME` to the base directory of your Java JRE or SDK, like

           export JAVA_HOME; JAVA_HOME=/usr/java/j2sdk1.4.0

   The location is entirely dependent upon your variety of UNIX. Sun's rpm distributions of Java normally install to `/usr/java/something`. Sun's System V package distributions of Java (including those that come with Solaris) normally install to `/usr/something`, with a sym-link from `/usr/java` to the default version (so for Solaris you will usually set JAVA_HOME to `/usr/java`).

3. Remove the existing file HSQLDB_HOME`/lib/hsqldb.jar`.

4. cd to HSQLDB_HOME`/build`. Make sure that the bin directory under your Ant home is in your search path. Run the following command.

           ant jar

   This will build a new HSQLDB_HOME`/lib/hsqldb.jar`.

See the Building Hsqldb version 1.7.2 appendix if you want to build anything other than `hsqldb.jar` with all default settings.

## Setting up a Hsqldb Persistent Database Instance and a Hsqldb Server

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.

1. Select a UNIX user to run the database as. If this database is for the use of multiple users, or is a production system (or to emulate a production system), you should dedicate a UNIX user for this purpose. In my examples, I use the user name `hsqldb`. In this chapter, I refer to this user as the HSQLDB_OWNER, since that user will own the database instance files and processes.

   If the account doesn't exist, then create it. On all system-5 UNIXes and most hybrids (including Linux), you can run (as root) something like

           useradd -m -c 'HSQLDB Database Owner' -s /bin/ksh -m hsqldb

   (BSD-variant users can use a similar `pw useradd hsqldb...` command).

2. Become the HSQLDB_OWNER. Copy the sample file HSQLDB_HOME`/src/org/hsqldb/sample/sample-server.properties` to the HSQLDB_OWNER's home directory and rename it to `server.properties`.

       # Hsqldb Server cfg file.
       # See the Advanced Topics chapter of the Hsqldb User Guide.

       server.database.0   file:db0/db0

   Since the value of the first database (server.database.0) begins with `file:`, the database instance will be persisted to a set of files in the specified directory with names beginning with the specified name. You can read about how to specify other database instances of various types, and how to make settings for the listen port and many other things, in the Advanced Topics chapter.

3. Set and export the environmental variable `CLASSPATH` to the value of HSQLDB_HOME (as described above) plus "/lib/hsqldb.jar", like

           export CLASSPATH; CLASSPATH=/path/to/hsqldb/lib/hsqldb.jar

   In HSQLDB_OWNER's home directory, run

           java org.hsqldb.Server &

   This will start the Server process in the background, and will create your new database instance "db0". Continue on when you see the message containing "HSQLDB server... is online".

## Accessing your Database

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.11 2004/07/18 21:34:35 unsaved Exp $

    # This is a sample SqlTool configuration file, a.k.a. rc file.

    # 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.

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

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

    # 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 urlid for a Postgresql database.
    # You will need to put the org.postgresql.Driver class into your
    # classpath.
    # The postgresql jar will be named postgresql.jar (if you built Postgresql
    # from source), or something like pg73b1jdbc3.jar or jdbc7.2x-1.2.jar.
    # You can obtain it from a client or server Postgresql installation, or
    # download it from http://jdbc.postgresql.org/download.html.
    # Notice that the jar file names (other than "postgresql.jar") contain both
    # the target Postgresql server version and the client-side JDBC level (which
    # is determined by your client-side Java version, as explained at
    # http://jdbc.postgresql.org/download.html).
    # I recommend the latest production version for your JDBC version.  The
    # later JDBC drivers work better even with older Postgresql servers.
    # (E.g. \dt won't list owners with an older driver).
    # N.b.: Suse Linux 9.1 users should download a new driver from the PG site,
    # since Suse distributes the 7.3 drivers with Postgresql 7.4 (why???).
    # 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 the Postgresql jar in your classpath (and
    # export!) and run something like "java org.hsqldb.util.SqlTool...".
    # N.b.:  I notice that Postgresql is unusual in that it does not do an
    # implicit commit before DDL commands.  If you get an error message
    # "... cannot run inside a transaction block", just run "commit;" and retry.

    #urlid commerce
    #url jdbc:postgresql://dbsvr2/commercedb
    #username blaine
    #password obscured
    #driver org.postgresql.Driver
    ###########################################################################

    ###########################################################################
    # 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
    ###########################################################################

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

If you get a prompt, then all is well. If security is of any concern to you at all, then you should change the privileged password in the database. Use the command SET PASSWORD command to change SA's password.

        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.

## Create additional Accounts

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.

There are two classes of database accounts, Admin accounts and non-Admin accounts. Admins have privileges to do anything, non-Admins may be granted some privileges, but may never create or own database objects. When you first create a hsqldb database, it has only one database user-- SA, an Admin account, with no password set. You should set a password (as described above). You can create as many additional Admin users as you wish. Each Admin user has a unique user name (and optional password), but these accounts are otherwise indistinguishable. These accounts are created by appending the keyword "ADMIN" to the CREATE USER command.

If you create a user without the ADMIN tag, it will be a Non-Admin account. These users can not create or own objects, and, by default, they can't use any database objects. The user will then be able to perform operations which have been granted to the pseudo-user PUBLIC. To give the user additional privileges (even the privilege to read data), an Admin user must grant those rights to the user (or to PUBLIC).

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).

## Shutdown

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 --noinput --sql 'shutdown;' localhost-sa

You don't have to worry about stopping the `Server` because it shuts down automatically when all served database instances are shut down.

## Running Hsqldb as a System Daemon

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.

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).

### Portability of `hsqldb` init script

The primary design criterion of the init script is portabiliity. It does not print pretty color startup/shutdown messages as is common in late-model Linuxes and HPUX; and it does not keep subsystem state files or use the startup/shutdown functions supplied by many UNIXes, because these features are all non-portable.

Offsetting these limitations, this one script does it's intended job great on the UNIX varieties I have tested, and can easily be modified to accommodate other UNIXes. While you don't have tight integration with OS-specific daemon administration guis, etc., you do have a well tested and well behaved script that gives good, utilitarian feedback.

### Init script Setup Procedure

1. Copy the init script `hsqldb` from HSQLDB_HOME`/bin` into the directory where init scripts live on your variety of UNIX. The most common locations are `/etc/init.d` or `/etc/rc.d/init.d` on System V style UNIXes, `/usr/local/etc/rc.d` on BSD style UNIXes, and `/Library/StartupItems/hsqldb` on OS X (you'll need to create the directory for the last).

2. Look at the init script and see what the value of CFGFILE is for your UNIX platform. You need to copy the sample config file HSQLDB_HOME`/src/org/hsqldb/sample/sample-hsqldb.cfg` to that location. Edit the config file according to the instructions in it.

       # $Id: sample-hsqldb.cfg,v 1.9 2004/07/15 18:54:47 unsaved Exp $

       # Sample configuration file for HSQLDB database server.
       # See the "UNIX Quick Start" chapter of the Hsqldb User Guide.

       # N.b.!!!!  You must place this in the right location for your type of UNIX.
       # See the init script "hsqldb" to see where this must be placed and
       # what it should be renamed to.

       # This file is "sourced" by a Bourne shell, so use Bourne shell syntax.

       # This file WILL NOT WORK until you set (at least) the non-commented
       # variables to the appropriate values for your system.
       # Life will be easier if you avoid all filepaths with spaces or any other
       # funny characters.  Don't ask for support if you ignore this advice.

       # Thanks to Meikel Bisping for his contributions.  -- Blaine

       JAVA_EXECUTABLE=/usr/java/j2sdk1.4.2_02/bin/java

       # Unless you copied a hsqldb.jar file from another system, this typically
       # resides at $HSQLDB_HOME/lib/hsqldb.jar, where $HSQLDB_HOME is your HSQLDB
       # software base directory.
       HSQLDB_JAR_PATH=/home/blaine/hsqldb-dev/lib/hsqldb.jar

       # Where the file "server.properties" (or "webserver.properties") resides.
       SERVER_HOME=/home/blaine/db

       # What UNIX user the Server/WebServer process will run as.
       # (The shutdown client is always run as root or the invoker of the init script).
       # Runs as root by default, but you should take the time to set database file
       # ownerships to another user and set that user name here.
       # You do need to run as root if your Server/WebServer will run on a privileged
       # (< 1024) port.
       # If you really do want to run as root, comment out the HSQLDB_OWNER setting
       # completely.  I.e., do not set it to root.  This will run Server/Webserver
       # without any "su" at all.
       HSQLDB_OWNER=blaine

       # We require all Server/WebServer instances to be accessible within
       # $MAX_START_SECS from when the Server/WebServer is started.
       # Defaults to 60.
       # Raise this is you are running lots of DB instances or have a slow server.
       #MAX_START_SECS=200
       # Ditto for this one
       #SU_ECHO_SECS=1

       # Time to allow for JVM to die after all HSQLDB instances stopped.
       # Defaults to 1.
       #MAX_TERMINATE_SECS=0

       # These are "urlid" values from a SqlTool authentication file
       # ** IN ADDITION TO THOSE IN YOUR server.properties OR webserver.properties **
       # file.  All server.urlid.X values from your properties file will automatically
       # be started/stopped/tested.  $SHUTDOWN_URLIDS is for additional urlids which
       # will stopped.  (Therefore, most users will not set this at all).
       # Separate multiple values with white space.  NO OTHER SPECIAL CHARACTERS!
       # Make sure to quote the entire value if it contains white space separator(s).
       # Defaults to none (i.e., only urlids set in properties file will be stopped).
       #SHUTDOWN_URLIDS='sa mygms'

       # SqlTool authentication file used only for shutdown.
       # The default value will be sqltool.rc in root's home directory, since it is
       # root who runs the init script.
       # (See the SqlTool chapter of the HSQLDB User Guide if you don't understand
       # this).
       #AUTH_FILE=/home/blaine/sqltool.rc

       # Set to 'WebServer' to start a HSQLDB WebServer instead of a Server.
       # Defaults to 'Server'.
       #TARGET_CLASS=WebServer

       # Server-side classpath IN ADDITION TO the HSQLDB_JAR_PATH set above.
       # The classpath here is *earlier* than HSQLDB_JAR_PATH, to allow you
       # override classes in the HSQLDB_JAR_PATH jar file.
       # In particular, you will want to add classpath elements to give access of
       # all of your store procedures (store procedures are documented in the
       # HSQLDB User Guide in the SQL Syntax chapter.
       # SERVER_ADDL_CLASSPATH=/home/blaine/storedprocs.jar:/usr/dev/dbutil/classes

       # For TLS encryption for your Server, set these two variables.
       # N.b.:  If you set these, then make this file unreadable to non-root users!!!!
       # See the TLS chapter of the HSQLDB User Guide, paying attention to the
       # security warning(s).
       # If you are running with a private server cert, then you will also need to
       # set "truststore" in the your SqlTool config file (location is set by the
       # AUTH_FILE variable in this file, or it must be at the default location for
       # HSQLDB_OWNER).
       #TLS_KEYSTORE=/path/to/jks/server.store
       #TLS_PASSWORD=password

       # Any JVM args for the invocation of the JDBC client used to verify DB
       # instances and to shut them down (SqlToolSprayer).
       # For multiple args, put quotes around entire value.
       #CLIENT_JVMARGS=-Djavax.net.debug=ssl

       # Any JVM args for the server.
       # For multiple args, put quotes around entire value.
       #SERVER_JVMARGS=-Xmx512m

3. Either copy HSQLDB_OWNER's `sqltool.rc` file into root's home directory, or set the value of AUTH_FILE to the absolute path of HSQLDB_OWNER's `sqltool.rc` file. This file is read (for stops) directly by root, even if you run hsqldb as non-root (by setting HSQLDB_OWNER in the config file). If you copy the file, make sure to use `chmod` to restrict permissions on the new copy.

4. Edit your `server.properties` file. For every `server.database.X` that you have defined, set a property of name `server.urlid.X` to the urlid for an Administrative user for that database instance.

   Example 3.1. server.properties fragment

           server.database.0=file://home/hsqldb/data/db1
           server.urlid.0=localhostdb1

   ### Warning

   Make sure to add a urlid for each and every database instance. If you don't then the init script will never know about databases that become inaccessible and will give false diagnostics.

   For this example, you would need to define the urlid `localhostdb1` in your `sqltool.rc` file.

   Example 3.2. example sqltool.rc stanza

           urlid localhostdb1
           url jdbc:hsqldb:hsql://localhost
           username sa
           password secret

5. Verify that the init script works.

   Just run

           /path/to/hsqldb

   as root to see the arguments you may use. Notice that you can run

           /path/to/hsqldb status

   at any time to see whether your HSQLDB `Server` is running.

   Re-run the script with each of the possible arguments to really test it good. If anything doesn't work right, then see the Troubleshooting the Init Script section.

6. Tell your OS to run the init script upon system startup and shutdown. If you are using a UNIX variant that has `/etc/rc.conf` or `/etc/rc.conf.local` (like BSD variants and Gentoo), you must set "hsqldb_enable" to "YES" in either of those files. (Just run `cd /etc; ls rc.conf rc.conf.local` to see if you have one of these files). For good UNIXes that use System V style init, you must set up hard links or soft links either manually or with management tools (such as `chkconfig` or `insserv`) or Gui's (like run level editors).

   This paragraph is for Mac OS X users only. If you followed the instructions above, your init script should reside at `/Library/StartupItems/hsqldb/hsqldb`. Now copy the file `StartupParameters.plist` from the directory `src/org.hsqldb/sample` of your HSQLDB distribution to the same directory as the init script. As long as these two files reside in `/Library/StartupItems/hsqldb`, your init script is active (for portability reasons, it doesn't check for a setting in `/etc/hostconfig`). You can run it as a Startup Item by running

           SystemStarter {start|stop|restart} Hsqldb

   Hsqldb is the service name. See the man page for `SystemStarter`. To disable the init script, wipe out the `/Library/StartupItems/hsqldb` directory. Hard to believe, but the Mac people tell me that during system shutdown the Startup Items don't run at all. Therefore, if you don't want your data corrupted, make sure to run "SystemStarter stop Hsqldb" before shutting down your Mac.

### Troubleshooting the Init Script

Do a `ps` to look for processes containing the string `hsqldb`, and try to connect to the database from any client. If the init script starts up your database successfully, but incorrectly reports that it has not, then your problem is with specification of urlid(s) or SqlTool setup. If your database really did not start, then skip to the next paragraph. Verify that the urlid(s) listed in the `server.properties` or `webserver.properties` are correct. and verify that you can run `SqlTool` as root to connect to the instances. (For the latter test, use the `--rcfile` switch if you are setting `AUTH_FILE` in the init script config file).

If your database really is not starting, then verify that you can su to the database owner account and start the database. If these don't pan out, then debug the init script or seek help, as described below.

To debug the init script, run it in verbose mode to see exactly what is happening (and perhaps manually run the steps that are suspect). To run an init script (in fact, any sh shell script) in verbose mode, use `sh` with the `-x` or `-v` switch, like

        sh -x path/to/hsqldb start

See the man page for `sh` if you don't know the difference between `-v` and `-x`.

If you want troubleshooting help, use the HSQLDB lists/forums or email me at blaine.simpson@admc.com. If you email me, make sure to include the revision number from your `hsqldb` init script (it's towards the top in the line that starts like "# $Id:"), and the output of a run of

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

Chapter 2. SQL Issues |  | Chapter 4. Advanced Topics