|
|
HSQLDB example source code file (unix.xml)
This example HSQLDB source code file (unix.xml) is included in the DevDaily.com
"Java Source Code
Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.
The HSQLDB unix.xml source code
<!-- $Id: unix.xml,v 1.29 2005/06/01 22:45:26 unsaved Exp $ -->
<chapter id='unix-chapter'>
<title id='unix-title'>UNIX Quick Start
<subtitle>
How to quickly get Hsqldb up and running on UNIX, including Mac OS X
</subtitle>
<chapterinfo>
<author>
<firstname>BlaineSimpson
<email>&blaineaddr;
<affiliation>
<orgname>HSQLDB Development Group
</affiliation>
</author>
<edition>$Revision: 1.29 $
<pubdate>$Date: 2005/06/01 22:45:26 $
<keywordset>
<keyword>HSQLDB
<keyword>UNIX
<keyword>HOWTO
</keywordset>
</chapterinfo>
<section>
<title>Purpose
<simpara>
This chapter explains how to quickly install, run, and
use HSQLDB on UNIX.
</simpara>
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.
</simpara>
</section>
<section>
<title>Installation
<simpara>
Go to <ulink url="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.
</simpara>
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
<ulink url='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).
</simpara>
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.
</simpara>
Binary installation depends on the package format that you
downloaded.
</simpara>
<varlistentry>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
<informalexample>
uncompress filename.pkg.Z</screen>
</informalexample>
You can read about the package by running
<informalexample>
pkginfo -l -d filename.pkg</screen>
</informalexample>
Run pkgadd as root to install.
</para>
pkgadd -d filename.pkg</screen>
</informalexample>
<varlistentry>Installing from a .rpm file
This is a Linux rpm package.
After you download the rpm, you can read about it by running
<informalexample>
rpm -qip /path/to/file.rpm</screen>
</informalexample>
Rpms can be installed or upgraded by running
<informalexample>
rpm -Uvh /path/to/file.rpm</screen>
</informalexample>
as root.
Suse users may want to keep Yast aware of installed packages by
running rpm through Yast:
<literal>yast2 -i /path/to/file.rpm.
</para>
</listitem>
<varlistentry>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
<emphasis role='bold'>HSQLDB_HOME directory because
the extraction will create it for you with the right name)
</simpara>
cd parent/of/new/hsqldb/home
unzip /path/to/file.zip</screen>
</informalexample>
All the files in the zip archive will be extracted to underneath
a new <filename>hsqldb directory.
</simpara>
</listitem>
</variablelist>
<simpara>
Take a look at the files you installed.
(Under <filename>hsqldb for zip file installations.
Otherwise, use the utilities for your packaging system).
The most important file of the hsqldb system is
<filename>hsqldb.jar, which resides in the directory
<filename>lib.
</simpara>
For the purposes of this chapter, I define
<emphasis role='bold'>HSQLDB_HOME to be the parent
directory of the lib directory that contains
<filename>hsqldb.jar.
E.g., if your path to <filename>hsqldb.jar is
<filename>/a/b/hsqldb/lib/hsqldb.jar, then your
<emphasis role='bold'>HSQLDB_HOME is
<filename>/a/b/hsqldb.
</simpara>
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.
</simpara>
If you followed the instructions above and you still don't know
what Java version your <filename>hsqldb.jar supports,
then read
<emphasis role='bold'>HSQLDB_HOME/readme.txt
and <emphasis role='bold'>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.
</simpara>
To use the supplied <filename>hsqldb.jar, just skip to
the <link linkend='instance_setup-section'> next section of this
document</link>.
Otherwise build a new <filename>hsqldb.jar.
</simpara>
<title>Building hsqldb.jar
<step>
If you don't already have Ant, download the latest stable
binary version from <ulink url='http://ant.apache.org'/>.
cd to where you want Ant to live, and extract from the archive
with
<informalexample>
unzip /path/to/file.zip</screen>
</informalexample>or
tar -xzf /path/to/file.tar.gz</screen>
</informalexample>or
bunzip2 -c /path/to/file.tar.bz2 | tar -xzf -</screen>
</informalexample>
Everything will be installed into a new subdirectory named
<filename>apache-ant- + version.
You can rename the directory after the extraction if you wish.
</para>
Set the environmental variable <literal>JAVA_HOME to
the base directory of your Java JRE or SDK, like
<informalexample>
export JAVA_HOME; JAVA_HOME=/usr/java/j2sdk1.4.0</screen>
</informalexample>
The location is entirely dependent upon your variety of UNIX.
Sun's rpm distributions of Java normally install to
<filename>/usr/java/something.
Sun's System V package distributions of Java (including those
that come with Solaris) normally install to
<filename>/usr/something, with a sym-link from
<filename>/usr/java to the default version (so for
Solaris you will usually set JAVA_HOME to
<filename>/usr/java).
</para>
Remove the existing file
<emphasis role='bold'>HSQLDB_HOME/lib/hsqldb.jar.
</simpara>
cd to
<emphasis role='bold'>HSQLDB_HOME/build.
Make sure that the bin directory under your Ant home is in your
search path.
Run the following command.
<informalexample>
ant hsqldb</screen>
</informalexample>
This will build a new
<emphasis role='bold'>HSQLDB_HOME/lib/hsqldb.jar.
</para>
</step>
</procedure>
<simpara>
See the <link linkend='building-appendix' endterm='building-title'/>
appendix if you want to build anything other than
<filename>hsqldb.jar with all default settings.
</simpara>
</section>
<section id='instance_setup-section'>
<title>
Setting up a Hsqldb Persistent Database Instance and a Hsqldb
Server
</title>
<titleabbrev>Setting up Database Instance and Server
<simpara>
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
<filename>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.
</simpara>
<step>
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 <literal>hsqldb.
In this chapter, I refer to this user as the
<emphasis role='bold'>HSQLDB_OWNER, since that user
will own the database instance files and processes.
</simpara>
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
<informalexample>
useradd -c 'HSQLDB Database Owner' -s /bin/bash -m hsqldb</screen>
</informalexample>
(BSD-variant users can use a similar
<literal>pw useradd hsqldb... command).
</para>
</step>
Become the <emphasis role='bold'>HSQLDB_OWNER.
Copy the sample file
<emphasis role='bold'>HSQLDB_HOME/src/org/hsqldb/sample/sample-server.properties
to the <emphasis role='bold'>HSQLDB_OWNER's home
directory and rename it to
<filename>server.properties.
</simpara>
<programlisting>&sample-server.properties-cdata;
<simpara>
Since the value of the first database
(<property>server.database.0) begins with
<literal>file:, the database instance will be
persisted to a set of files in the specified directory with
names beginning with the specified name.
Set the path to whatever you want (relative paths will be
relative to the directory containing the properties file).
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
<link linkend='advanced-chapter' endterm='advanced-title'/>
chapter.
</simpara>
Set and export the environmental variable
<literal>CLASSPATH to the value of
<emphasis role='bold'>HSQLDB_HOME (as described
above) plus "/lib/hsqldb.jar", like
<informalexample>
export CLASSPATH; CLASSPATH=/path/to/hsqldb/lib/hsqldb.jar</screen>
</informalexample>
In <emphasis role='bold'>HSQLDB_OWNER's home
directory, run</para>
<informalexample>
nohup java org.hsqldb.Server &</screen>
</informalexample>
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
<literal>HSQLDB server... is online.
<literal>nohup just makes sure that the command
will not quit when you exit the current shell (omit it
if that's what you want to do).
</simpara>
</step>
</procedure>
</section>
<section>
<title>Accessing your Database
<simpara>
Copy the file
<emphasis role='bold'>HSQLDB_HOME/src/org/hsqldb/sample/sqltool.rc
to the
<emphasis role='bold'>HSQLDB_OWNER's home directory.
Use <literal>chmod to make the file readable and
writable only to <emphasis role='bold'>HSQLDB_OWNER.
</simpara>
<programlisting>&sqltool.rc-cdata;
<simpara>
We will be using the "localhost-sa" sample urlid definition from
the config file.
The JDBC URL for this urlid is
<literal>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
<link linkend='advanced-chapter' endterm='advanced-title'/>
chapter.
</simpara>
Run <classname>SqlTool.
<informalexample>
java -jar path/to/hsqldb.jar localhost-sa</screen>
</informalexample>
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
<link linkend='set_password-section'>SET PASSWORD
command to change SA's password.
<informalexample>
set password "newpassword";</programlisting>
</informalexample>
<simpara>
When you're finished playing, exit with the command
<literal>\q.
</simpara>
If you changed the SA password, then you need to
fix the password in the <filename>sqltool.rc file
accordingly.
</simpara>
You can, of course, also access the database with any JDBC client
program.
See the
<link linkend='firstclient-appendix' endterm='firstclient-title'/>
appendix.
You will need to modify your classpath to include
<filename>hsqldb.jar as well as your client class(es).
You can also use the other HSQLDB client programs, such as
<classname>org.hsqldb.util.DatabasManagerSwing,
a graphical client with a similar purpose to
<classname>SqlTool.
</simpara>
You can use any normal UNIX account to run the JDBC clients,
including <classname>SqlTool, as long as the account
has read access to the <filename>hsqldb.jar file and to
an <filename>sqltool.rc file.
See the <link linkend='sqltool-chapter' endterm='sqltool-title'/>
chapter about where to put <filename>sqltool.rc, how to
execute sql files, and other <classname>SqlTool
features.
</simpara>
</section>
<section>
<title>Create additional Accounts
<simpara>
Connect to the database as SA (or any other Administrative user)
and run <link linkend='create_user-section'>CREATE USER
to create new accounts for your database instance.
HSQLDB accounts are database-instance-specific, not
<classname>Server-specific.
</simpara>
For the current version of HSQLDB, only users with Role of
<literal>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
<link linkend='create_user-section'>CREATE USER command,
or GRANT the DBA Role to the account after creating it.
</simpara>
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.
</simpara>
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).
</simpara>
</section>
<section>
<title>Shutdown
<para>
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
<informalexample>
java -jar path/to/hsqldb.jar --sql shutdown localhost-sa</screen>
</informalexample>
You don't have to worry about stopping the
<classname>Server because it shuts down automatically when
all served database instances are shut down.
</para>
</section>
<section>
<title>Running Hsqldb as a System Daemon
<simpara>
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 <emphasis>Server will run
as root-- it usually should not).
</simpara>
The main purpose of the init script is to start up a Server with
the database instances specified in your
<filename>server.properties file; and to shut down all
of those instances <emphasis>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
<filename>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).
</simpara>
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).
</simpara>
<section>
<title>
Portability of <filename>hsqldb init script
</title>
<simpara>
The primary design criterion of the init script is portability.
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.
</simpara>
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.
</simpara>
</section>
<section>
<title>Init script Setup Procedure
<simpara>
The strategy taken here is to get the init script to run your
single Server or WebServer first (as specified by TARGET_CLASS).
After that's working, you can customize the JVM that is run
by running additional Servers in it, running your own
application in it (embedding), or even overriding HSQLDB
behavior with your own overriding classes.
</simpara>
<procedure>
<step>
Copy the init script <filename>hsqldb from
<emphasis role='bold'>HSQLDB_HOME/bin
into the directory where init scripts live on your variety of
UNIX.
The most common locations are <filename>/etc/init.d
or <filename>/etc/rc.d/init.d on System V style
UNIXes, <filename>/usr/local/etc/rc.d on BSD style
UNIXes, and <filename>/Library/StartupItems/hsqldb
on OS X (you'll need to create the directory for the last).
</simpara>
Look at the comment towards the top of the init script which
lists recommended locations for the configuration file for
various UNIX platforms.
Copy the sample config file
<emphasis role='bold'>HSQLDB_HOME/src/org/hsqldb/sample/sample-hsqldb.cfg
to one of the listed locations (your choice).
Edit the config file according to the instructions in it.
</simpara>
<programlisting>&sample-hsqldb.cfg-cdata;
</step>
Either copy <emphasis role='bold'>HSQLDB_OWNER's
<filename>sqltool.rc file into root's home
directory, or set the value of AUTH_FILE to the absolute path
of <emphasis role='bold'>HSQLDB_OWNER's
<filename>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 <literal>chmod
to restrict permissions on the new copy.
(The init script now enforces permissions on this file).
</simpara>
Edit your <filename>server.properties file.
For every <literal>server.database.X that you have
defined, set a property of name
<literal>server.urlid.X to the urlid for an
Administrative user for that database instance.
</simpara>
<example>server.properties fragment
<programlisting>
server.database.0=file://home/hsqldb/data/db1
server.urlid.0=localhostdb1</programlisting>
</example>
<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.
</simpara>
<simpara>
For this example, you would need to define the urlid
<literal>localhostdb1 in your
<filename>sqltool.rc file.
</simpara>
<example>example sqltool.rc stanza
<programlisting>
urlid localhostdb1
url jdbc:hsqldb:hsql://localhost
username sa
password secret</programlisting>
</example>
</step>
<simpara>
<emphasis role='bold'>Verify that the init script
works.</emphasis>
</simpara>
Just run
<informalexample>
/path/to/hsqldb</screen>
</informalexample>
as root to see the arguments you may use.
Notice that you can run
</para>
/path/to/hsqldb status</screen>
</informalexample>
at any time to see whether your HSQLDB
<classname>Server is running.
</para>
Re-run the script with each of the possible arguments to really
test it good.
If anything doesn't work right, then see the
<link linkend='initscriptTrouble-section'
endterm='initscriptTrouble-section-title'/> section.
</simpara>
Tell your OS to run the init script upon system startup and
shutdown.
If you are using a UNIX variant that has
<filename>/etc/rc.conf or
<filename>/etc/rc.conf.local (like BSD variants
and Gentoo), you must set "hsqldb_enable" to "YES" in either
of those files.
(Just run <literal>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 <literal>chkconfig or
<literal>insserv) or Gui's (like run level editors).
</simpara>
This paragraph is for Mac OS X users only.
If you followed the instructions above, your init script
should reside at
<filename>/Library/StartupItems/hsqldb/hsqldb.
Now copy the file <filename>StartupParameters.plist
from the directory <filename>src/org.hsqldb/sample
of your HSQLDB distribution to the same directory as the
init script.
As long as these two files reside in
<filename>/Library/StartupItems/hsqldb, your
init script is active (for portability reasons, it doesn't
check for a setting in <filename>/etc/hostconfig).
You can run it as a <emphasis>Startup Item by running
<screen>
SystemStarter {start|stop|restart} Hsqldb</screen>
Hsqldb is the service name. See the man page for
<literal>SystemStarter.
To disable the init script, wipe out the
<filename>/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.
</para>
</procedure>
<simpara>
Follow the examples in the config file to add additional
classes to the server JVM's classpath and to execute
additional classes in your JVM.
(See the SERVER_ADDL_CLASSPATH and INVOC_ADDL_ARGS items).
</simpara>
</section>
<section id='initscriptTrouble-section'>
<title id='initscriptTrouble-section-title'>
Troubleshooting the Init Script
</title>
<simpara>
Do a <literal>ps to look for processes containing
the string <literal>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
<filename>server.properties or
<filename>webserver.properties are correct.
and verify that you can run
<classname>SqlTool as root to connect to the
instances.
(For the latter test, use the <literal>--rcfile
switch if you are setting <literal>AUTH_FILE in the
init script config file).
</simpara>
If your database really is not starting, then verify that
you can su to the database owner account and start the
database.
The command <literal>su USERNAME -c ... won't work
on most UNIXes unless the target user has a real login shell.
Therefore, if you try to tighten up security by disabling
this user's login shell, you will break the init script.
If these possibilities don't pan out, then debug the init
script or seek help, as described below.
</simpara>
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
<literal>sh with the -x or
<literal>-v switch, like
<screen>
sh -x path/to/hsqldb start</screen>
See the man page for <literal>sh if you don't know
the difference between <literal>-v and
<literal>-x.
</para>
If you want troubleshooting help, use the HSQLDB lists/forums
or email me at
<ulink url='mailto:&blaineaddr;?Subject=hsqldb-unix'>
&blaineaddr;</ulink>.
If you email me, make sure to include the revision number
from your <filename>hsqldb init script (it's
towards the top in the line that starts like "# $Id:"), and
the output of a run of
<screen>
sh -x path/to/hsqldb start > /tmp/hstart.log 2>&1</screen>
</para>
</section>
</section>
</chapter>
Other HSQLDB examples (source code examples)
Here is a short list of links related to this HSQLDB unix.xml source code file:
|
The search page
Other HSQLDB source code examples at this package level
Click here to learn more about this project
