Chapter 14. HyperSQL on UNIX

This chapter explains how to quickly install, run, and use a HyperSQL Listener (aka Server) on UNIX.

Note that, unlike a traditional database server, there are many use cases where it makes sense to run HyperSQL without any listener. This type of setup is called in-process, and is not covered here, since there is no UNIX-specific setup in that case.

I intend to cover what I think is the most common UNIX setup: To run a multi-user, externally-accessible catalog with permanent data persistence. (By the latter I mean that data is stored to disk so that the catalog data will persist across process shutdowns and startups). I also cover how to run the Listener as a system daemon.

When I give sample shell commands below, I use commands which will work in Bourne-compatible shells, including Bash and Korn. Users who insist on using the inferior C-shells will need to convert.

Go to http://sourceforge.net/projects/hsqldb and click on the "files" link. You want the current version. I can't be more specific because SourceForge/Geeknet are likely to continue changing their interface. See if there's a distribution for the current HSQLDB version in the format that you want.

If you want a binary package and we either don't provide it, or you prefer somebody else's build, you should still find out the current version of HyperSQL available at SourceForge. It's very likely that you can find a binary package for your UNIX variant with your OS distributor, http://www.jpackage.org/, http://sunfreeware.com/, etc. Nowadays, most UNIXes have software package management systems which check Internet repositories. Just search the repositories for "hsqldb" and "hypersql". The challenge is to find an up-to-date package. You will get better features and support if you work with the current stable release of HyperSQL. (In particular, HyperSQL version 2.0.0 added tons of new features). Pay attention to what JVM versions your binary package supports. Our builds (version 2.0 and later) document the Java version it was built with in the file doc/index.html, but you can't depend on this if somebody else assembled your distribution. Java jar files are generally compatible with the same or greater major versions. For example,if your hsqldb.jar was built with Java 1.3.6-11, then it is compatible with Java versions 1.3.* and greater.

	Note
	It could very well happen that some of the file formats which I discuss below 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.

    uncompress filename.pkg.Z

    pkginfo -l -d filename.pkg

    pkgadd -d filename.pkg

    rpm -qip /path/to/file.rpm

    rpm -Uvh /path/to/file.rpm

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

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 HyperSQL system is hsqldb.jar, which resides in the subdirectory lib. Depending on who built your distribution, your file name may have a version label in it, like hsqldb-1.2.3.4.jar.

	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. Furthermore, unless I state otherwise, all local file paths that I give are relative to the HSQLDB_HOME.

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 try reading documentation files like readme.txt, README.TXT, INSTALL.txt etc. (As I said above, our newer distributions always document the Java version for the build, in the file doc/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 HyperSQL Jars 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 catalog and listener 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.

We're going to use SqlTool to access the database, so you will need the file sqltool.jar in addition to hsqldb.jar. If sqltool.jar isn't already sitting there beside hsqldb.jar (they both come pre-built), build it exactly as you would build hsqldb.jar, except use ant target sqltool. If your distribution came with a sqltool jar file with a version label, like sqltool-1.2.3.4.jar, that's fine-- use that file whenever I say sqltool.jar below.

Copy the file 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 4313 2011-06-06 02:19:38Z unsaved $

# This is a sample RC configuration file used by SqlTool, DatabaseManager,
# and any other program that uses the org.hsqldb.lib.RCData class.
# See the documentation for SqlTool for various ways to use this file.

# If you have the least concerns about security, then secure access to
# your RC file.

# You can run SqlTool right now by copying this file to your home directory
# and running
#    java -jar /path/to/sqltool.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.
# As of revision 3347 of SqlFile, you can also connect to datasources defined
# here from within an SqlTool session/file with the command "\j urlid".

# You can use Java system property values in this file like this:  ${user.home}

# The only feature added recently is the optional "transiso" setting,
# which may be set to an all-caps transaction isolation level as listed
# in the Java API Spec for java.sql.Connection.
# Windows users are advised to use forward slashes instead of reverse slashes,
# and to avoid paths containing spaces or other funny characters.  (This
# recommendation applies to any Java app, not just SqlTool).

# 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
transiso TRANSACTION_READ_COMMITTED
# 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 .../sqltool.jar...".
# Put both the SqlTool 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 ${user.home}/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
#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 using Microsoft's Driver
# (I find that the JTDS driver is much more responsive than Microsoft's).
# OLDER JDBC Driver:
#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
# Current 2011 JDBC Driver for Microsoft SQL Server:
# Requires just the new sqljdbc4.jar.  (Microsoft just loves back-slashes)
#url jdbc:sqlserver://hostname\instanceName;DatabaseName=dbname
#driver com.microsoft.jdbc.sqlserver.SQLServerDriver

# Template for Microsoft SQL Server database using the JTDS Driver
# http://jtds.sourceforge.net  Jar file has name like "jtds-1.2.5.jar".
#urlid nlyte
#username myuser
#password hiddenpwd
#url jdbc:jtds:sqlserver://myhost/nlyte;instance=MSSQLSERVER
#driver net.sourceforge.jtds.jdbc.Driver

# 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 catalog of a HyperSQL Listener running on the default port of the local host. You can read about URLs to connect to other catalogs with and without listeners in other chapters of this guide.

Run SqlTool.

    java -jar path/to/sqltool.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';

Set a strong password!

	Note
	If, like most UNIX System Administrators, you often need to make up strong passwords, I highly suggest the great little program pwgen. You can probably get it where you get your other OS packages. The command pwgen -1 is usually all you need.

Note that with SQL-conformant databases like HyperSQL 2.0, user names and passwords are case sensitive. If you don't quote the name, it will be interpreted as upper-case, like any named SQL object. (Only for backwards compatibility, we do make an exception for the special user name SA, but you should always use upper-case "SA" nevertheless).

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

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

You can, of course, also access the database with any JDBC client program. 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 sqltool.jar file and to an sqltool.rc file. See the Utilities Guide 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 catalog. HSQLDB accounts are database-catalog-specific, not Listener-specific.

In SQL-compliant databases, all database objects are created in a schema. If you don't specify a schema, then the new object will be created in the default schema. To create a database object, your account (the account that you connected with) must have the role DBA, or your account must have authorization for the target schema (see the CREATE SCHEMA command about this last). When you first create a HyperSQL catalog, 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, command, or GRANT the DBA Role to the account after creating it.

Once an object is created, the object creator and users with the DBA role will have all privileges to work with that object. Other users will have only the rights which the pseudo-user PUBLIC has. To give specific users 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 table-like objects that need to be accessed by multiple database users, with the significant exception of any data which you want to keep secret. (Similarly with EXECUTE priv for routines and USAGE priv for other object types). Note that this is not at all equivalent to giving the world or the Internet read access to your tables-- you are giving read access to people that have been given accounts for the target database catalog.

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

    java -jar path/to/sqltool.jar --sql 'shutdown;' localhost-sa

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

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 Listener will run as root-- it usually should not).

The main purpose of the init script is to start up a Listener for the database catalogs specified in your server.properties file; and to gracefully shut down these same catalogs. For each catalog defined by a server.database.X setting in your .properties file, you must define an administrative "urlid" in your sqltool.rc (these are used to access the catalogs for validation and shutdown purposes). Finally, you list the urlid names in your init script config 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 listener instances with or without TLS encryption, and each listener instance can serve any number of HyperSQL catalogs (independent data sets), all with optimal efficiency from a single JVM process. There are instructions in the init script itself about how to run multiple, independently-configured JVM processes. Most UNIX installations, however, will run a single JVM with a single Listener instance which serves multiple catalogs, for easier management and more efficient resource usage.

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

This section is for users who are using our UNIX init script, and who are upgrading their HyperSQL installation.

Most users will not have customized the init script itself, and your customizations will all be encapsulated in the init script configuration file. These users should just overwrite their init script with a new one from the HyperSQL installation, and manually merge config file settings. First, just copy the file /sample/hsqldb.init over top of of your init script (wherever it runs from). Then update your old config file according to the instructions in the new config file template at sample/hsqldb.cfg. You will have to change very few settings. If you are upgrading from a pre-2.0 installation to a post-2.0 installation, you will need to (1) add the setting URLIDS, as described above and in the inline comments, and (2) replace variable HSQLDB_JAR_PATH with SQLTOOL_JAR_PATH which (if you haven't guessed) should be set to the path to your sqltool.jar file.

Users who customized their init script will need to merge their customizations into the new init script.