HSQLDB Beginner's Guide
$Revision: 1.1 $

WARNING: Don't skip the introduction section nor the definitions section. They contain material necessary for an understanding of the rest of this document.

TABLE OF CONTENTS
INTRODUCTION This document is written by Blaine Simpson of Axis Data Management Corp. Distribute freely. Email me if you have any criticisms, suggestions, corrections, additions, etc. Even if you are only running hsqldb embedded in another product, I recommend that you work through this document so that you will understand hsqldb enough to use it effectively. This is a Beginner's Guide, so don't expect to find the answers to all of your Hsqldb questions here. See the other docs in $HSQLDB_HOME-- they're really good.

I believe that everything in this document applies to versions 1.7.x of hsqldb (unless stated otherwise).

In order to make my examples brief, in most cases I just provide the UNIX usage. If you are a Windows user, you may need to change forward slashes to back-slashes and rework quotes and line-continuations to satisfy your command shell.

My tact is to avoid hsqldb scripts for the most part, because running the Java commands from the command line gives you a real understanding of how hsqldb works. For example, if you run /path/to/runUtil.sh WebServer or \path\to\runUtil.bat WebServer you may have no idea that you are actually instantiating a Java instance of org.hsqldb.WebServer from the hsqldb.jar file. Assuming that you have a basic understanding of Java, if you run the Java commands directly, you will know exactly what is happening and will gain insights into how you can set up hsqldb for your specific needs. (Another reason I am avoiding the scripts is, hsqldb did not have production quality scripts for Linux before version 1.7.1).

For some commands I show how to specify optional arguments. When I do, I use square brackets [like this] around the optional argument(s). You do not type the square brackets, and you can omit the entire [expression] completely. DEFINITIONS These definitions are purely stipulative and are made for the purpose of making this document easy to understand.

dataset identifier

I hereby define "dataset identifier" to be the root filename of the database files for any database. By root filename, I mean the part of the database filenames before the . + extension (this is the only part of the filename that they all have in common). The exceptional case of a memory-only database has a dataset name of ".", by (my) definition.

$HSQLDB_HOME

The base directory in which hsqldb lives (this is not necessariliy where your data or databases reside).

NODENAME

A name that your resolver can resolve to an IP address. This could be, for example, a hostname, the name of floating IP address, localhost, or a dotted-quad IP address string like 127.0.0.1. INSTALLING HSQLDB Go to http://sourceforge.net/projects/hsqldb and click on the files link. Look over the versions of hsqldb which are listed under the lowercase heading hsqldb. Click on the non-bold .zip file link for the version of hsqldb that you want. (Linux users please see the paragraph following this one). Extract the zip file to wherever you want the hsqldb system to reside (the $HSQLDB_HOME). The contents of production hsqldb archives contain a version-specific base directory (like hsqldb_v.1.61) as is customary. However, at least some non-production archives do not contain a base directory (i.e., the contents are all relative to an unnamed $HSQLDB_HOME base directory). So, after you download the archive, do a unzip -v.

This paragraph is only for Solaris users interested in installing with a Solaris package. You have it especially easy. If you have root privileges on the target server, I highly recommend the Solaris package. Just download the package, do a pkgadd, and you will have a running database that lives in directory /usr/hsqldb/data/db1, as soon as you reboot or run /etc/init.d/hsqldb start. See the file /usr/src/hsqldb/readme.solarispkg for details on how to add new databases, change the default configuration, etc.

This paragraph is only for Linux users interested in installing with an rpm. As I write this, a Linux RPM is being built for hsqldb. Users of Linux versions that support RPMs should look for them at the same location as the zip files. Rpms can be installed or upgraded by running rpm -Uvh /path/to/file.rpm as root. You can also read about the package before installing it by running rpm -qip /path/to/file.rpm. You will have to check the download location yourself to see what Java versions, Linux versions, etc. are supported by the supplied rpm(s).

Look through $HSQLDB_HOME/readme.txt and/or $HSQLDB_HOME/index.html. (They may or may not be there, depending on whether you are working with a production release, etc.). The most important file of the hsqldb system is hsqldb.jar, which resides at $HSQLDB_HOME/lib/hsqldb.jar. Your index.html or readme.txt file should say what version of Java your hsqldb.jar was built with/for (Rpm users should be able to run rpm -qi hsqldb, though I can't test that now because the rpm isn't built yet). If they don't say, or it is not your JRE version, then I suggest that you rebuild hsqldb.jar to avoid problems due to JRE version conflicts. The rest of the section is only for those people who want to rebuild hsqldb.

First 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" in UNIX, or "set JAVA_HOME=C:\Program Files\Java\J2re1.4.0" in Windows). (For Solaris, you would normally set JAVA_HOME to /usr/java, which is a sym-link to the default java home).

You can run run the scripts $HSQLDB/build/switchToJDK*.bat and $HSQLDB/build/buildJDK*.bat (Windows users, where the * is your Java version number). or the script $HSQLDB/build/buildjar.sh (no need to specify Java version). If that works, you're finished with the installation. If you have any problems with that, then use the following instructions to perform the build yourself using Ant.

If you don't already have Ant, download any recent production version from Apache's Jakarta site, cd to where you want Ant to live, and extract from the archive with unzip or tar -xzf.

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 builds $HSQLDB_HOME/lib/hsqldb.jar. HSQLDB ARCHITECTURE Database files, when there are any, reside in the directory you were in when you issued the Java command that started the database engine (starting the Server, or the Tools in the case of Standalone engines). Four of the files consist of the dataset identifier ("test" is used as the identifier in this example) and an extension. Example: 
    test.data
    test.script
    test.properties
    test.backup (in some cases)
    server.properties (in some cases)
    webserver.properties (in some cases)
The exception to the claim about the files residing in the startup directory is when the dataset identifier consists of a file path (less the filename extensions), instead of just a base filename. For example, /home/databases/test or C:/TEMP/DBS/test or databases/test instead of just test. In that case, the directory is determined by the path given. RUNNING HSQLDB Add $HSQLDB_HOME/lib/hsqldb.jar to your $CLASSPATH. In perhaps the majority of cases, you will have no pre-existing $CLASSPATH, and will want only the hsqldb classes and the current directory (in order to run your own .class files, etc.) in your classpath. In this trivial case, the command in UNIX would be
export CLASSPATH; CLASSPATH=$HSQLDB_HOME/lib/hsqldb.jar:.

If you already have a classpath exported (echo $CLASSPATH), then run

CLASSPATH=$CLASSPATH:$HSQLDB_HOME/lib/hsqldb.jar:.
(substitute your real $HSQLDB_HOME, unless you have really set the environmental variable. In the second case, type $CLASSPATH literally).

TOOLS

You can use DatabaseManager or QueryTool with any hsqldb database (assuming that you can reach it). (I refer to QueryTool and DatabaseManager below as the Tools) I am writing the Java commands as single long lines because I don't know how wide your browser window is. Be aware that your browser may be folding these single commands into multiple lines (you can check it out by streteching your browser really wide). You can, of course, use any shell functionalities to break any given command into multiple lines. Don't forget to add $HSQLDB_HOME/lib/hsqldb.jar to your $CLASSPATH as explained above. These examples use the sample URL jdbc:hsqldb:hsql://skipper.admc.com.

java org.hsqldb.util.DatabaseManager ['-?'] [-url jdbc:hsqldb:hsql://skipper.admc.com]

(Don't forget to refresh the tree after data changes). IMPORTANT: Choose your Engine type FIRST, because this will change the URL field!

java org.hsqldb.util.QueryTool ['-?'] [-url jdbc:hsqldb:hsql:] [-test false] [-database //skipper.admc.com]1

(Loads up a goofy query by default that in most cases has no relationship to your data) Beware that you need to use "-test false" if you don't want to add test data to your target database. The url arg is actually the URL up-to-and-including the last colon. The database arg is actually just the last part of the URL, which, in the case of Server engines, is not a dataset identifier.

These samples show what I consider to be the most widely useful options-- there are other options too. Use the '-?' to get a syntax message. Note that they run in the foreground, so "> /somewhere 2>&1 &" if you want your shell back.

Default data sources (if you don't give an Applet property of "database" and do not give a command-line argument).

Specify database with Applet param of ".", arg of -database for QueryTool, full -url for DatabaseManager (you can't just give the db to DatabaseManager).

HSQL Engine Modes

The data source URL is dependent on the Engine Mode, but the data source Driver is not. The data source Driver is always org.hsqldb.jdbcDriver.

Database Preparation If security is of any concern to you at all, then immediately after starting your database for the first time, you should change the privileged password in the databse. Connect up as user "sa" with no password, perhaps using one of the utilities described above. Execute the command set password "newpassword". If you will ever make use of non-Admin user account(s), then you should grant SELECT privilege on the following objects to PUBLIC If you don't, then you will have to grant these permissions to every new user every time you add a new user. (New users will still be unable to even read data in tables that you have created, unless you also grant privileges on those objects). See the section below about using additional database users.

Resources Do check out the docs in $HSQLDB_HOME/doc.

1 Note the ambiguity with database arguments between QueryTool and Server. QueryTool url arg always ends with : (which is dumb if you ask me), and the database starts with / for Servers engines. java Server... and java WebServer... database arg is just the dataset identifier, like "test". CHARACTER DELIMITED FLAT DATABASE FILES For database engines other than In-Memory engines, you can have specific tables use a text-char-delimited table-specific file to store the data.

Specific tables may be text tables. You can not change the default mode so that CREATE TABLE commands will create text tables. You must use the special CREATE TEXT TABLE command to create a text table (or use the special SELECT command described below).

CREATE TEXT TABLE mynewtable...

At this point, the table is read-only and can not store data, because there is no file associated with the text table.

SET TABLE mynewtable SOURCE "filename.csv"
Now you are all set. The default delimiter is ",". You can change that by specifying it along with the text file like
SET TABLE mynewtable SOURCE "filename.csv;fs=|"
where fs means Field Separator. This and several other useful settings can be made either with the SET TABLE command or in your datasource properties file (like "test.properties").

To create a new text table from the definition and data of an existing normal table, use a command like

SELECT * INTO TEXT newtable FROM existingtable
The file name is created automatically (I don't know why the behavior of the SELECT...INTO command is so radically different from the CREATE command). The filename is a lower-cased and normalized version of the table name plus suffix "csv". The file will reside in the same directory as the other database files.

To persist changes to your csv file after modifications, use the CHECKPOINT command. ADDITIONAL DATABASE USERS 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 (see the Syntax doc).

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. You should have already granted the permissions listed above in the database preparation part of the Running hsqldb section. 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 usually 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).