Table of Contents
As described in the Running and Using HyperSQL chapter, network listeners (servers) provide connectivity to catalogs from different JVM processes. The HyperSQL listeners support both ipv4 and ipv6 network addressing.
This is the preferred way of running a database server and the fastest one. This mode uses the proprietary hsql: communications protocol. The following example of the command for starting the server starts the server with one (default) database with files named "mydb.*" and the public name (alias) of "xdb".
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb
Alternatively, a server.properties file can be used for passing the arguments to the server. This file must be located in the directory where the command is issued.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server
Alternatively, you can specify the path of the server.properties file on the command line. In this case, the properties file can have any name or extension, but it should be a valid properties file.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --props myserver.props
Use the --help argument to see the list of available arguments.
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --help
The contents of the server.properties file is described in the next section.
This method of access is used when the computer hosting the database server is restricted to the HTTP protocol. The only reason for using this method of access is restrictions imposed by firewalls on the client or server machines and it should not be used where there are no such restrictions. The HyperSQL HTTP Server is a special web server that allows JDBC clients to connect via HTTP. The server can also act as a small general-purpose web server for static pages.
To run an HTTP server, replace the main class for the server in the example command line above with the following:
java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer
The contents of the server.properties file is described in the next section.
This method of access also uses the HTTP protocol. It is used when
a separate servlet engine (or application server) such as Tomcat or
Resin provides access to the database. The Servlet Mode cannot be
started independently from the servlet engine. The
Servlet
class, in the HSQLDB jar, should be
installed on the application server to provide the connection. The
database is specified using an application server property. Refer to the
source file
src/org/hsqldb/server/Servlet.java
to see the details.
Both HTTP Server and Servlet modes can only be accessed using the JDBC driver at the client end. They do not provide a web front end to the database. The Servlet mode can serve only a single database.
Please note that you do not normally use this mode if you are using the database engine in an application server. In this situation, connections to a catalog are usually made in-process, or using an external HSQL Server instance.
Properties files for running the servers are not created
automatically. You should create your own files that contain
server.property=value
pairs for
each property. The server.properties
or
webserver.properties
files must be located in the
directory where the command to run the
org.hsqldb.server.Server
class is issued.
In all properties files, values are case-sensitive. All values apart
from names of files or pages are required in lowercase (e.g.
server.silent=FALSE
will have no
effect, but server.silent=false
will work). Supported properties and their default values (if any) are as
follows:
Table 13.1. common server and webserver properties
| Value | Default | Description |
|---|---|---|
| server.database.0 | file:test | the catalog type, path and file name of the first database file to use |
| server.dbname.0 | "" | lowercase server alias for the first database file |
| server.database.n | NO DEFAULT | the catalog type, path and file name of the n'th database file in use |
| server.dbname.n | NO DEFAULT | lowercase server alias for the n'th database file |
| server.silent | true | no extensive messages displayed on console |
| server.trace | false | JDBC trace messages displayed on console |
| server.address | NO DEFAULT | IP address of server |
| server.tls | false | Whether to encrypt network stream. If this is set to
true , then in normal situations you will also
need to set properties
system.javax.net.ssl.keyStore and
system.javax.net.ssl.keyStorePassword , as
documented elsewhere. The value of server.tls
impacts the default value of
server.port . |
| server.daemon | false | Whether the server is run as a daemon |
| server.remote_open | false | Allows opening a database path remotely when the first connection is made |
In HyperSQL version 2.0, each server can serve an unlimited number of databases simultaneously. The server.database.0 property defines the filename / path whereas the server.dbname.0 defines the lowercase alias used by clients to connect to that database. The digit 0 is incremented for the second database and so on. Values for the server.database.n property can use the mem:, file: or res: prefixes and connection properties as discussed under CONNECTIONS. For example,
database.0=mem:temp;sql.enforce_strict_size=true;
Properties or default values specific to
server.properties
are:
Table 13.2. server properties
| Value | Default | Description |
|---|---|---|
| server.port | 9001 (normal) or 554 (if TLS
encrypted) | TCP/IP port used for talking to clients. All databases are served on the same port. |
| server.no_system_exit | true | no System.exit() call when the database
is closed |
Properties or default values specific to
webserver.properties
are:
Table 13.3. webserver properties
| Value | Default | Description |
|---|---|---|
| server.port | 80 (normal) or 443 (if TLS
encrypted) | TCP/IP port used for talking to clients |
| server.default_page | index.html | the default web page for server |
| server.root | ./ | the location of served pages |
| .<extension> | NO DEFAULT | multiple entries such as .html=text/html
define the mime types of the static files served by the web
server. See the source for
src/org/hsqldb/server/WebServer.java for a
list. |
An example of the contents of a
server.properties
file is given below:
server.database.0=file:/opt/db/accounts server.dbname.0=accounts server.database.1=file:/opt/db/mydb server.dbname.1=enrolments server.database.2=mem:adatabase server.dbname.2=quickdb
In the above example, the server.properties
file indicates that the server provides access to 3 different databases.
Two of the databases are file-based, while the third is all-in-memory. The
aliases for the databases that the users connect to are
accounts
, enrolments
and
quickdb
.
All the above properties and their values can be specified on the
command line to start the server by omitting the
server.
prefix. If a property/value pair is specified
on the command line, it overrides the property value specified in the
server.properties
or
webserver.properties
file.
| Note | |
|---|---|
|
Upgrading: If you have existing custom properties files, change the values to the new naming convention. Note the use of digits at the end of server.database.n and server.dbname.n properties. |
If you want to start the server from within your application, as
opposed to the command line or batch files, you should create an instance
of Server or Web Server, then assign the properties and start the Server.
An working example of this can be found in the org.hsqldb.test.TestBase
source. The example below sets the same properties as in the
server.properties file example.
HsqlProperties p = new HsqlProperties(); p.setProperty("server.database.0","file:/opt/db/accounts"); p.setProperty("server.dbname.0","an_alias"); // set up the rest of properties // alternative to the above is Server server = new Server(); server.setProperties(p); server.setLogWriter(null); // can use custom writer server.setErrWriter(null); // can use custom writer server.start();
The Server object has several alternative methods for setting databases and their public names. The server should be shutdown using the shutdown() method.
If the server.remote_open
property is true, the
Server works differently from the normal mode. In this mode, it is not
necessary to have any databases listed as server.database.0 etc. in the
Server startup properties. If there are databases listed, they are opened
as normal. The server does not shutdown when the last database is
closed.
In this mode, a connection can be established to a database that is not open or does not exist. The server will open the database or create it, then return a connection to the database.
The connection URL must include the path to the database, separated
with a semicolon from the alias. In the example below, the database path
specified as file:C:/files/mydatabase
is opened and the
database alias xdb
is assigned to the database. After
this, the next connection to the specified alias will connect to the same
database. Any database path on the URL is ignored if the alias is serving
a database.
The database path can point to a file:
or
mem:
database.
If you use database properties on the URL, these properties are used
when the new database is created. If no database properties are used on
the URL, you can also specify the path with
filepath=<path>
. Examples below:
Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;file:C:/files/mydatabase", "SA", ""); Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;mem:test;sql.enforce_types=true", "SA", ""); Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;filepath=file:C:/files/mydatabase", "SA", "");
Each database started by a Server has its own URL. When new databases are created by the server, the database properties for each of the new database can be appended to the database URL. Examples below:
// example in server.propertie file server.database.0=file:/opt/db/accounts;hsqldb.default_table_type=cached;sql.enforce_names=true server.dbname.0=accounts // example for setting the property programatically HsqlProperties p = new HsqlProperties(); p.setProperty("server.database.0","file:/opt/db/accounts;hsqldb.default_table_type=cached;sql.enforce_names=true");
The specified properties apply only to a new database. They have no
effect on an existing database apart from a few properties such as
readonly
listed in the Properties
chapter.
This section explains how to encrypt the stream between JDBC network clients and HyperSQL Listeners. If you are running an in-process (non-Listener) setup, this chapter does not apply to you.
Hsqldb TLS Support Requirements
Sun Java 2.x and up. (This is probably possible with IBM's Java, but I don't think anybody has attempted to run HSQLDB with TLS under IBM's Java, and I'm sure that nobody in the HSQLDB Development Group has documented how to set up the environment).
If Java 2.x or 3.x, then you will need to install JSSE. Your server and/or client will start up much slower than that of Java 4.x users. Client-side users will not be able to use the https: JDBC protocol (because the https protocol handler is not implemented in 2.x/3.x Java JSSE; if there is demand, we could work around this).
A JKS keystore containing a private key, in order to run a Listener.
If you are running the listener side, then you'll need to run a HSQLDB Server or WebServer Listener instance. It doesn't matter if the underlying database catalogs are new, and it doesn't matter if you are making a new Listener configuration or encrypting an existing Listener configuration. (You can turn encryption on and off at will).
You need a HSQLDB jar file that was built with JSSE present. If you obtained your HSQLDB 1.7.2-or-later distribution from us, you are all set, because we build with Java 1.4 or later (which contains JSSE). If you build your own jar file with Java 1.3, make sure to install JSSE first.
At this time, only 1-way, server-cert encryption is tested.
Just use one of the following protocol prefixes.
Hsqldb TLS URL Prefixes
jdbc:hsqldb:hsqls://
jdbc:hsqldb:https://
At this time, the latter will only work for clients running with Java 1.4 or later.
If the listener you wish to connect to is using a certificate approved by your default trust keystore, then there is nothing else to do. If not, then you need to tell Java to "trust" the server cert. (It's a slight over-simplification to say that if the server certificate was purchased, then you are all set; if somebody "signed their own" certificate by self-signing or using a private ca certificate, then you need to set up trust).
First, you need to obtain the cert (only the "public" part of it). Since this cert is passed to all clients, you could obtain it by writing a Java client that dumps it to file, or perhaps by using openssl s_client. Since in most cases, if you want to trust a non-commercial cert, you probably have access to the server keystore, I'll show an example of how to get what you need from the server-side JKS keystore.
You may already have an X509 cert for your server. If you have a server keystore, then you can generate a X509 cert like this.
Example 13.1. Exporting certificate from the server's keystore
keytool -export -keystore server.store -alias existing_alias -file server.cer
In this example, server.cer
is the
X509 certificate that you need for the next step.
Now, you need to add this cert to one of the system trust
keystores or to a keystore of your own. See
the Customizing Stores section in JSSERefGuide.html to see
where your system trust keystores are. You can put private keystores
anywhere you want to. The following command will add the cert to an
existing keystore, or create a new keystore if
client.store
doesn't exist.
Example 13.2. Adding a certificate to the client keystore
keytool -import -trustcacerts -keystore trust.store -alias new_alias -file server.cer
If you are making a new keystore, you probably want to start
with a copy of your system default keystore which you can find
somewhere under your JAVA_HOME
directory (typically
jre/lib/security/cacerts
for a JDK, but I forget
exactly where it is for a JRE).
Unless your OS can't stop other people from writing to your files, you probably do not want to set a password on the trust keystore.
If you added the cert to a system trust store, then you are
finished. Otherwise you will need to specify your custom trust
keystore to your client program. The generic way to set the trust
keystore is to set the system property
javax.net.ssl.trustStore
every time that you
run your client program. For example
Example 13.3. Specifying your own trust store to a JDBC client
java -Djavax.net.ssl.trustStore=/home/blaine/trust.store -jar /path/to/hsqldb.jar dest-urlid
This example runs the program SqlTool. SqlTool has built-in TLS
support however, so, for SqlTool you can set
truststore
on a per-urlid basis in the SqlTool
configuration file.
Note: The hostname in your database URL must match the
Common Name of the server's certificate exactly.
That means that if a site certificate is admc.com
,
you can not use jdbc:hsqldb:hsqls://localhost
or
jdbc:hsqldb:hsqls://www.admc.com:1100
to connect to
it.
If you want more details on anything, see JSSERefGuide.html on
Sun's site, or in the subdirectory
docs/guide/security/jsse
of your Java SE
docs.
Get yourself a JKS
keystore containing a private key. Then set properties
server.tls
,
system.javax.net.ssl.keyStore
and
system.javax.net.ssl.keyStorePassword
in your
server.properties
or
webserver.properties
file. Set
server.tls
to true
,
system.javax.net.ssl.keyStore
to the path of the
private key JKS keystore, and
system.javax.net.ssl.keyStorePassword
to the
password (of both the keystore and the private key record-- they must
be the same). If you specify relative file path values, they will be
resolved relative to the ${user.dir}
when the JRE
is started.
| Caution | |
|---|---|
|
If you set any password in a .properties (or any other) file, you need to restrict access to the file. On a good operating system, you can do this like so: chmod 600 path/to/server.properties |
The values and behavior of the system.*
settings above match the usage documented for
javax.net.ssl.keyStorePassword
and
javax.net.ssl.keyStore
in the JSSE docs.
| Note | |
|---|---|
|
Before version 2.0, HyperSQL depended on directly setting the corresponding JSSE properties. The new idiom is more secure and easier to manage. If you have an old password in a UNIX init script config file, you should remove it. |
If you are running Java 4.x or later, then you are all set. Java 1.x users, you are on your own (Sun does not provide a JSSE that will work with 1.x). Java 2.x and 3.x users continue...
Go to http://java.sun.com/products/jsse/index-103.html If
you agree to the terms and meet the requirements, download the domestic
or global JSSE software. All you need from the software distro is the
three jar files. If you have a JDK installation, then move the 3 jar
files into the directory $JAVA_HOME/jre/lib/ext
. If
you have a JRE installation, then move the 3 jar files into the
directory $JAVA_HOME/lib/ext
.
Pretty painless.
There are two main ways to do this. Either you can use a certificate signed by a certificate authority, or you can make your own. One thing that you need to know in both cases is, the Common Name of the cert has to be the exact hostname that JDBC clients will use in their database URL.
I'm not going to tell you how to get a CA-signed SSL certificate. That is well documented at many other places.
Assuming that you have a standard pem-style private key
certificate, here's how you can use openssl and the program
DERImport
to get it into a JKS keystore.
Because I have spent a lot of time on this document already, I am just giving you an example.
Example 13.4. Getting a pem-style private key into a JKS keystore
openssl pkcs8 -topk8 -outform DER -in Xpvk.pem -inform PEM -out Xpvk.pk8 -nocrypt openssl x509 -in Xcert.pem -out Xcert.der -outform DER java DERImport new.keystore NEWALIAS Xpvk.pk8 Xcert.der
| Important | |
|---|---|
|
Make sure to set the password of the key exactly the same as the password for the keystore! |
You need the program DERImport.class
of
course. Do some internet searches to find
DERImport.java
or
DERImport.class
and download it.
If DERImport has become difficult to obtain, I can write a program to do the same thing-- just let me know.
Run man keytool
or see
the Creating a Keystore section of JSSERefGuide.html.
If you are on UNIX and want to automatically start and stop a
Server or WebServer running with encryption, set the
system.javax.net.ssl.keyStore
and
system.javax.net.ssl.keyStorePassword
properties as
instructed above, and follow the instructions in the HyperSQL on UNIX chapter, paying
close attention to the TLS-related comments in the template config
file.
If you are using a private server certificate, make sure to also set the trust store filepath for relevant urlids in your RC file, as explained in the sample config file.
JDBC connections will always be denied if the supplied user and password are not found in the target catalog. But an HyperSQL listener can also restrict access at the listener level, even protecting private catalogs which have insecure (or default) passwords. If you have an in-process setup, this section of the Guide doesn't apply to you.
Many (in fact, most) distributed database applications don't have application clients connect directly to the database, but instead encapsulate access in a controlling process. For example, a web app will usually access the data source on behalf of users, with end-user web browsers never accessing the database directly. In these cases and others, the security benefits of restricting listener access to specific source addresses is well worth the effort. ACLs work by restricting access according to the source address of the incoming connection request. This is efficient because the database engine never even gets the request until it is approved by the ACL filter code.
The sample file sample/acl.txt
in your HyperSQL
distribution explains how to write an ACL file.
# $Id: acl.txt 826 2009-01-17 05:04:52Z unsaved $ # Sample HyperSQL Network Listener ACL file. # Specify "allow" and "deny" rules # For address specifications, individual addresses, host names, and # network addresses with /bit suffix are allowed, but read the caveat about # host names below, under the sample "localhost" rule. # Blank lines ignored. # Lines with # as the first non-whitespace character are ignored. allow 2001:db8::/32 # Allow this 32-bit ipv4 subnet allow localhost # You should use numerical addresses in ACL files, unless you are certain that # the name will always be known to your network address resolution system # (assume that you will lose Internet connectivity at some time). # With a default name resolution setup on UNIX, you are safe to use names # defined in your /etc/hosts file. deny <IP_ADDRESS> # Deny a single IP address. # In our example, <IP_ADDRESS>/24 is our local, organizational network. # <IP_ADDRESS> is the IP address of our Intern's PC. # The Intern does not have permission to access our databases directly. allow <IP_ADDRESS>/24 # Any ipv4 or ipv6 candidate address not matched above will be denied
You put your file wherever it is convenient for you, and specify that path
with the property server.acl
or
webserver.acl
in your
server.properties
or
webserver.properties
file (depending on whether your
listener instance is a Server
or
WebServer
). You can specify the ACL file path with
an absolute or relative path. If you use a relative path, it must be
relative to the .properties
file. It's often
convenient to name the ACL file acl.txt
, in the same
directory as your .properties
file and specify the
property value as just acl.txt
. This file name is
intuitive, and things will continue to work as expected if you move or
copy the entire directory.
| Warning | |
|---|---|
|
If your |
When you edit your ACL file, it is both more convenient and more secure to test it as explained here before activating it. You could, of course, test an ACL file by editing it in-place, then trying to connect to your listener with JDBC clients from various source addresses. Besides being mightily laborious and boring, with this method it is very easy to accidentally open access to all source addresses or to deny access to all users until you fix incorrect ACL entries.
The suggested method of creating or changing ACLs is to work with an
inactive file (for new ACL files, just don't enable the
*.acl
property yet; for changing an existing file, just
copy it to a temporary file and edit the temporary file). Then use the
ServerAcl
class to test it.
Example 13.5. Validating and Testing an ACL file
java -cp path/to/hsqldb.jar org.hsqldb.server.ServerAcl path/to/acl.txt
If the specified ACL file fails validation, you will be given
details about the problem. Otherwise, the validated rules will be
displayed (including the implicit, default deny rules). You then type in
host names and addresses, one-per-line. Each name or address is tested as
if it were a HyperSQL network client address, using the same exact method
that the HyperSQL listener will use. (HyperSQL listeners use this same
ServerAcl
class to test incoming source addresses).
ServerAcl
will report the rule which matches and
whether access is denied or allowed to that address.
If you have edited a copy of an existing ACL file (as suggested above), then overwrite your live ACL file with your new, validated ACL file. I.e., copy your temp file over top of your live ACL file.
ServerAcl
can be run in the same exact way
described above, to troubleshoot runtime access issues. If you use an ACL
file and a user or application can't get a connection to the database, you
can run ServerAcl
to quickly and definitively find
if the client is being prohibited by an ACL rule.
Table of Contents
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.
|
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 -d filename.pkg |
|
Installing from a BSD Port or Package | You're on your own. I find everything much easier when I install software to BSD without their package management systems. |
|
Installing from a .rpm file |
Just skip this section if you know how to install an RPM. If you found the RPM using a software management system, then just have it install it. The remainder of item explains a generic command-line method which should work with any Linux variant. 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. Suse users may want to keep Yast aware
of installed packages by running rpm through Yast: |
|
Installing from a .zip file |
Extract the zip file in an ancestor directory of the new
HSQLDB home. You don't need to create the
cd ancestor/of/new/hsqldb/home unzip /path/to/file.zip All the files in the zip archive will be extracted to
underneath a new subdirectory named like
|
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-<IP_ADDRESS>.jar
.
| Important | |
|---|---|
|
For the purposes of this chapter, I define
Furthermore, unless I state otherwise, all local file paths
that I give are relative to the
|
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
.
Procedure 14.1. Building hsqldb.jar
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.
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
).
Remove the existing file HSQLDB_HOME
/lib/hsqldb.jar
.
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 hsqldb
This will build a new
HSQLDB_HOME
/lib/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.
Select a UNIX user to run the database process (JVM) 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 catalog files and the JVM 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 -c 'HSQLDB Database Owner' -s /bin/bash -m hsqldb
(BSD-variant users can use a similar pw
useradd hsqldb...
command).
Become the HSQLDB_OWNER
. Copy the sample
file
sample/server.properties
to the
HSQLDB_OWNER
's home directory and rename it to
server.properties
. (As a final reminder,
"sampleserver.properties" is a relative path, so it is understood to
be relative to your HSQLDB_HOME
).
# Hsqldb Server cfg file. # See the HyperSQL Network Listeners chapter of the HyperSQL User Guide. # Each server.database.X setting defines a database "catalog". # I.e., an independent set of data. # Each server.database.X setting corresponds exactly to the jdbc:hsqldb:* # JDBC URL you would use if you wanted to get a direct (In-Process) # Connection to the catalog instead of "serving" it. server.database.0=file:db0/db0 # I suggest that, for every file: catalog you define, you add the # connection property "ifexists=true" after the database instance # is created (which happens simply by starting the Server one time). # Just append ";ifexists=true" to the file: URL, like so: # server.database.0=file:db0/db0;ifexists=true # server.dbname.0 defaults to "" (i.e. server.dbname.n for n==0), but # the catalog definition n will be entirely ignored for n > 0 if you do not # set server.dbname.n. I.e. dbname setting is required for n > 0, though it # may be set to blank (e.g. "server.dbname.3=")
Since the value of the first database (server.database.0) begins with file:, the catalog 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 catalogs of various types, and how to make settings for the listen port and many other things in other chapters of this guide.
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
nohup java org.hsqldb.server.Server &
This will start the Listener process in the background, and
will create your new database catalog "db0". Continue on when you see
the message containing HSQLDB server... is online
.
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).
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-<IP_ADDRESS>.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
|
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).
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.
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.
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 Listener instances in it, running your own application in it (embedding), or even overriding HSQLDB behavior with your own overriding classes.
Copy the init script sample/hsqldb.init
to
hsqldb
in 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).
View your server.properties
file. Make a
note of every catalog define by a
server.database.X
setting. A couple steps down,
you will need to set up administrative access for each of these
catalogs. If you are using our sample server.properties
file, you will just need to set up access for the catalog specified
with file:db0/dbo
.
| Note | |
|---|---|
|
Pre-2.0 versions of the hsqldb init script required use
of .properties settings of the
form |
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 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.
The init script will abort with an appropriate exhortation if you
have the permissions set incorrectly.
You need to set up a urlid stanza in your
sqltool.rc
file for network access (i.e. JDBC
URL with hsql:, hsqls:, http:, or https:) for each catalog in your
server.properties
file. For our example, you
need to define a stanza for the file:db0/db0
catalog. You must supply for this catalog, a hsql: JDBC URL, an
administrative user name, and the password.
Example 14.1. example sqltool.rc stanza
urlid localhostdb1 url jdbc:hsqldb:hsql://localhost username SA password secret
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 sample/hsqldb.cfg
to one of
the listed locations (your choice). Edit the config file according
to the instructions in it. For our example, you will set the value
of URLIDS
to localhostdb1
,
since that is the urlid name that we used in the
sqltool.rc
file.
# $Id: hsqldb.cfg 3583 2010-05-16 01:49:52Z unsaved $ # Sample configuration file for HyperSQL Server Listener. # See the "HyperSQL on UNIX" chapter of the HyperSQL 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. # The URLIDS setting below is new and REQUIRED. This setting replaces the # server.urlid.X settings which used to be needed in your Server's # properties file. # -- Blaine (blaine dot simpson at admc dot com) JAVA_EXECUTABLE=/usr/bin/java # Unless you copied the jar files from another system, this typically # resides at $HSQLDB_HOME/lib/sqltool.jar, where $HSQLDB_HOME is your HSQLDB # software base directory. # The file name may actually have a version label in it, like # sqltool-1.2.3.jar (in which case, you must specify the full name here). # A 'hsqldb.jar' file (with or without version label) must reside in the same # directory as the specified sqltool.jar file. SQLTOOL_JAR_PATH=/opt/hsqldb-2.0.0/hsqldb/lib/sqltool.jar # For the sample value above, there must also exist a file # /opt/hsqldb-2.0.0/hsqldb/lib/hsqldb*.jar. # Where the file "server.properties" or "webserver.properties" resides. SERVER_HOME=/opt/hsqldb-2.0.0/hsqldb/data # What UNIX user the server 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. HSQLDB_OWNER=hsqldb # The HSQLDB jar file specified in HSQLDB_JAR_PATH above will automatically # be in the class path. This arg specifies additional classpath elements. # To embed your own application, add your jar file(s) or class base # directories here, and add your main class to the INVOC_ADDL_ARGS setting # below. Another common use-case for adding to your class path is to make # classes available to the DB engines for SQL/JRT functions and procedures. #SERVER_ADDL_CLASSPATH=/usr/local/dist/currencybank.jar # For startup or shutdown failures, you can save a lot of debugging time by # temporarily adjusting down MAX_START_SECS and MAX_TERMINATE_SECS to a # little over what it should take for successful startup and shutdown on # your system. # 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 # Max time to allow for JVM to die after all HSQLDB instances stopped. # Defaults to 60. Set high because the script will always continue as soon as # the process has stopped. The importance of this setting is, how long until # a non-stopping-JVM-problem will be detected. #MAX_TERMINATE_SECS=0 # NEW AND IMPORTANT!!! # As noted at the top of this file, this setting replaces the old property # settings server.urlid.X. # Simply list the URLIDs for all DB instances which your *Server starts. # Usually, these will exactly mirror the server.database.X settings in your # server.properties or webserver.properties file. # Each urlid listed here must be defined to a NETWORK url with Admin privileges # in the AUTH_FILE specified below. (Network type because we use this for # inter-process communication) # Separate multiple values with white space. NO OTHER SPECIAL CHARACTERS! # Make sure to quote the entire value if it contains white space separator(s). URLIDS='localhostdb1' # These are urlids # ** IN ADDITION TO URLIDS **, for instances which the init # script should stop but not start. # Most users will not need this setting. If you need it, you'll know it. # Defaults to none (i.e., only URLIDS will be stopped). #SHUTDOWN_URLIDS='ondemand' # 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 HyperSQL Utilities Guide if you don't # understand this). #AUTH_FILE=/home/blaine/sqltool.rc # Typical users will leave this unset and it will default to # org.hsqldb.server.Server. If you need to run the HSQLDB WebServer class # instead, due to a firewall or routing impediment, set this to # org.hsqldb.server.WebServer, see the docs about running WebServr, and # set up a "webserver.properties" file instead of a "server.properties". # The JVM that is started can invoke many classes (see the following item # about that), but this is the server that is used (1) to check status, # (2) to shut down the JVM. #TARGET_CLASS=org.hsqldb.server.WebServer # This is where you may specify both command-line parameters to TARGET_CLASS, # plus any number of additional progams to run (along with their command-line # parameters). The MainInvoker program is used to embed these multiple # static main invocations into a single JVM, so see the API spec for # org.hsqldb.util.MainInvoker if you want to learn more. # N.b. You should only use this setting to set HSQLDB Server or WebServer # parameters if you run multiple instances of this class, since you can use the # server/webserver.properties file for a single instance. # Every additional class (in addition to the TARGET_CLASS) # must be preceded with an empty string, so that MainInvoker will know # you are giving a class name. MainInvoker will invoke the normal # static main(String[]) method of each such class. # By default, MainInvoker will just run TARGET_CLASS with no args. # Example that runs just the TARGET_CLASS with the specified arguments: #INVOC_ADDL_ARGS='-silent false' #but use server.properties property instead! # Example that runs the TARGET_CLASS plus a WebServer: #INVOC_ADDL_ARGS='"" org.hsqldb.server.WebServer' # Note the empty string preceding the class name. # Example that starts TARGET_CLASS with an argument + a WebServer + # your own application with its args (i.e., the HSQLDB Servers are # "embedded" in your application). (Set SERVER_ADDL_CLASSPATH too).: #INVOC_ADDL_ARGS='-silent false "" org.hsqldb.server.WebServer "" com.acme.Stone --env prod localhost' # but use server.properties for -silent option instead! # Example to run a non-TLS server in same JVM with a TLS server. In this # case, TARGET_CLASS is Server which will run both in TLS mode by virtue of # setting the tls, keyStore, and keyStorePassword settings in # server*.properties, as described below; plus an "additional" Server with # overridden 'tls' and 'port' settings: #INVOC_ADDL_ARGS="'' org.hsqldb.server.Server --port 9002 --tls false" # This is an important use case. If you run more than one Server instance, # you can specify different parameters for each here, even though only one # server.properties file is supported. # Note that you use nested quotes to group arguments and to specify the # empty-string delimiter. # The TLS_* settings have been obsoleted. # To get your server running with TLS, set # system.javax.net.ssl.keyStore=/path/to/your/private.keystore # system.javax.net.ssl.keyStorePassword=secretPassword # server.ssl=true # IN server.properties or webserver.properties, and # MAKE THE FILE OWNER-READ-ONLY! # See the TLS Encryption section of the HyperSQL 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). # Any JVM args for the invocation of the JDBC client used to verify DB # instances and to shut them down (SqlToolSprayer). # Server-side System Properties should normally be set with system.* # settings in the server/webserver.properties file. # This example specifies the location of a private trust store for TLS # encryption. # For multiple args, put quotes around entire value. # If you are starting just a TLS_encrypted Listener, you need to uncomment # this so the init scripts uses TLS to connect. # If using a private keystore, you also need to set "truststore" settings in # the sqltool.rc file. #CLIENT_JVMARGS=-Djavax.net.debug=ssl # This sample value displays useful debugging information about TLS/SSL. # Any JVM args for the server. # For multiple args, put quotes around entire value. #SERVER_JVMARGS=-Xmx512m # You can set the "javax.net.debug" property on the server side here, in the # same exact way as shown for the client side above.
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 Listener
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.
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.
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).
Definitely look at the init script log file, which is at an
OS-sependent location, but is usually at
/var/log/hsqldb.log
.
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 your
config file assigns a urlid for each catalog defined in
server.properties
or
webserver.properties
, then verify that you can run
SqlTool
as root to connect to the catalogs with
these urlids. (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. The command 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.
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.
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
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.
$Revision: 5256 $
Copyright 2002-2013 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.
2013-07-08 15:31:27+0100
Table of Contents
Memory used by the program can be thought of as two distinct pools: memory used for table data which is not released unless the data is deleted and memory that can be released or is released automatically, including memory used for caching, building result sets and other internal operations such as storing the information needed for a rollback a transaction.
Most JVM implementations allocate up to a maximum amount of memory (usually 64 MB by default). This amount is generally not adequate when large memory tables are used, or when the average size of rows in cached tables is larger than a few hundred bytes. The maximum amount of allocated memory can be set on the Java command line that is used for running HyperSQL. For example, with Sun JVM, parameter -Xmx256m increases the amount to 256 MB.
The memory used for a MEMORY table is the sum of memory used by
each row. Each MEMORY table row is a Java object that has 2 int or
reference variables. It contains an array of objects for the fields in
the row. Each field is an object such as Integer
,
Long
, String
, etc. In
addition each index on the table adds a node object to the row. Each
node object has 6 int or reference variables. As a result, a table with
just one column of type INTEGER will have four objects per row, with a
total of 10 variables of 4 bytes each - currently taking up 80 bytes per
row. Beyond this, each extra column in the table adds at least a few
bytes to the size of each row.
By default, all the rows in the result set are built in memory, so
very large result sets may not be possible to build. A server mode
databases releases the result set from the server memory once the
database server has returned the result set. An
in-process database releases the memory when the
application program closes the java.sql.ResultSet
object. A server mode database requires additional memory for returning
result sets, as it converts the full result set into an array of bytes
which is then transmitted to the client.
HyperSQL 2.0 supports disk-based result sets. The commands,
SET SESSION RESULT MEMORY ROWS <integer>
and
SET DATABASE DEFAULT RESULT MEMORY ROWS
<integer>
specify a threshold for the number of rows.
Results with row counts above the threshold are stored on disk. These
settings also apply to temporary tables, views and subquery
tables.
Disk-based result sets slow down the database operations and should be used only when absolutely necessary, perhaps with result sets that are larger than tens of thousands of rows.
In a server mode database, when the setFetchSize() method of the Statement interface is used to limit the number rows fetched, the whole result is held by the engine and is returned to the JDBC ResultSet in blocks of rows of the specified fetch size.
When UPDATE and DELETE queries are performed on CACHED tables, the full set of rows that are affected, including those affected due to ON UPDATE actions, is held in memory for the duration of the operation. This means it may not be possible to perform deletes or updates involving very large numbers of rows of CACHED tables. Such operations should be performed in smaller sets. This memory is released as soon as the DELETE or UPDATE is performed.
When transactions support is enabled with SET AUTOCOMMIT FALSE, lists of all insert, delete or update operations are stored in memory so that they can be undone when ROLLBACK is issued. For CACHED tables, only the transaction information is held in memory, not the actual rows that have changed. Transactions that span thousands of modification to data will take up a lot of memory until the next COMMIT or ROLLBACK clears the list. Each row modification uses less than 100 bytes until COMMIT.
When subqueries or views are used in SELECT and other statements,
transient tables are created and populated by the engine. If the
SET SESSION RESULT MEMORY ROWS <integer>
statement has been used, these transient tables are stored on disk when
they are larger than the threshold.
With CACHED tables, the data is stored on disk and only up to a maximum number of rows are held in memory at any time. The default is up to 50,000 rows. The SET FILES CACHE ROWS command or the hsqldb.cache_rows connection property can be set to alter this amount. As any random subset of the rows in any of the CACHED tables can be held in the cache, the amount of memory needed by cached rows can reach the sum of the rows containing the largest field data. For example if a table with 100,000 rows contains 40,000 rows with 1,000 bytes of data in each row and 60,000 rows with 100 bytes in each, the cache can grow to contain 50,000 of the smaller rows, but as explained further, only 10,000 or the large rows.
An additional property, hsqldb.cache_size is used in conjunction with the hsqldb.cache_rows property. This puts a limit in bytes on the total size of rows that are cached. The default values is 10,000KB. (This is the size of binary images of the rows and indexes. It translates to more actual memory, typically 2-4 times, used for the cache because the data is represented by Java objects.)
If memory is limited, the hsqldb.cache_rows or hsqldb.cache_size database properties can be reduced. In the example above, if the hsqldb.cache_size is reduced from 10,000 to 5,000, it will allow the number of cached rows to reach 50,000 small rows, but only 5,000 of the larger rows.
Data for CLOB and BLOB columns is not cached and does not affect the CACHED table memory cache.
The use of Java NIO file access method also increases memory usage. Access with NIO improves database update speed and is used by default for data files up to 256 MB. For minimal memory use, nio access should be disabled.
The operating system usually allocates a large amount of buffer memory for speed up file read operations. Therefore when a lot of memory is available to the operating system, all database operations perform faster.
HyperSQL uses a set of fast pools for immutable objects such as
Integer, Long and short String objects that are stored in the database.
In most circumstances, this reduces the memory footprint still further
as fewer copies of the most frequently-used objects are kept in memory.
The object pools are shared among all databases in the JVM. The size of
each pool can be modified only by altering and recompiling the
org.hsqldb.store.ValuePool
class.
Access to lobs is always performed in chunks, so it is perfectly possible to store and access a CLOB or BLOB that is larger than the JVM memory allocation. The actual total size of lobs is almost unlimited. We have tested with over 100 GB of lobs without any loss of performance.
By default, HyperSQL 2 uses memory-based tables for the lob schema (not the actual lob data). Therefore it is practical to store about 100,000 individual lobs in the database with the default JVM memory allocation. More lobs can be stored with larger JVM memory allocations. In order to store more than a few hundreds of thousands of lobs, you can change the lob schema storage to CACHED tables with the following statements:
Example 15.1. Using CACHED tables for the LOB schema
SET TABLE SYSTEM_LOBS.BLOCKS TYPE CACHED SET TABLE SYSTEM_LOBS.LOBS TYPE CACHED SET TABLE SYSTEM_LOBS.LOB_IDS TYPE CACHED
With file: database, the engine uses the disk for storage of data and any change. For safely, the engine backs up the data internally during operation. Spare space, at least equal to the size of the .data and .script file is needed. The .lobs file is not backed up during operation as it is not necessary for safety.
In all running modes (server or in-process) multiple connections to the database engine are supported. in-process (standalone) mode supports connections from the client in the same Java Virtual Machine, while server modes support connections over the network from several different clients.
Connection pooling software can be used to connect to the database but it is not generally necessary. Connection pools may be used for the following reasons.
To allow new queries to be performed while a time-consuming query is being performed in the background. In HyperSQL, blocking depends on the transaction control model, the isolation level, and the current activity by other sessions.
To limit the maximum number of simultaneous connections to the database for performance reasons. With HSQLDB this can be useful if your application is designed in a way that opens and closes connections for each small task. Also, the overall performance may be higher when fewer simultaneous connections are used. If you want to reduce the number of simultaneous sessions, you can use a connection pool with fewer pooled connections.
An application that is not both multi-threaded and transactional, such as an application for recording user login and logout actions, does not need more than one connection. The connection can stay open indefinitely and reopened only when it is dropped due to network problems.
When using an in-process database, when the last connection to the database is closed, the database still remains open, waiting for the next connection to be made. From version 2.2.9, each time the last connection is closed all the data changes are logged and synched to disk.
An explicit SHUTDOWN command, with or without an argument, is required to close the database. A connection property, shutdown=true, can be used on the connection URL or in a properties object to shutdown the database when the last connection is closed.
When using a server database (and to some extent, an in-process database), care must be taken to avoid creating and dropping JDBC Connections too frequently. Failure to observe this will result in poor performance when the application is under heavy load.
A common error made by users in load-test simulations is to use a single client machine to open and close thousands of connections to a HyperSQL server instance. The connection attempts will fail after a few thousand because of OS restrictions on opening sockets and the delay that is built into the OS in closing them.
First thing to be aware of is the SQL conformance settings of HyperSQL. By default version 2 applies stricter conformance rules than version 1.8 and catches long strings or decimal values that do not fit within the specified length or precision settings. However, there are several conformance settings that are turned off by default. This is to enable easier migration from earlier versions, and also greater compatibility with databases such as MySQL that are sometimes very liberal with type conversions. The conformance settings are listed in the System Management chapter and their connection property equivalents are listed in the Database Properties chapter. Ideally, all the settings that are not for syntax compatibility with other databases should have a true value for best error checking. You can turn on the settings for syntax compatibility with another database if you are porting or testing applications targeted at the other database.
For application unit testing you can use an all-in-memory, in-process database.
If the tests are all run in one process, then the contents of a mem: database survives between tests. To release the contents you can use the SHUTDOWN command (an SQL command). You can even use multiple mem: databases in your tests and SHUTDOWN each one separately.
If the tests are in different processes and you want to keep the data between the tests, the best solution is to use a Server instance that has a mem: database. After the tests are done, you can SHUTDOWN this database, which will shutdown the server.
The Server has an option that allows databases to be created as needed by making a connection (see the Listeners Chapter).  Each time you connect to the mem: database that is served by the Server, the database is created if it does not exist (i.e. has been previously shut down).
If you do not want to run a Server instance, and you need
persistence between tests in different processes, then you should use a
file: database. From version 2.2.9 when the last
existing connection to the database is closed, the latest changes to the
database are persisted fully with fsync. The database is still in an open
state until it is shut down. You can use the
shutdown=true
connection property to close the database
automatically after the connections are closed. The automatic sync and
shutdown are mainly for test environment. In production environments you
should execute the SHUTDOWN statement before your application is closed.
This ensurs a quick start next time you connect to the database.
An alternative option is to use
hsqldb.write_delay=false
connection property, but this
is slightly slower than the other option and should be used in situations
where the test application does not close the connections. This option
uses fsync after each commit. Even if the test process is aborted without
shutting down the connections, all committed data is saved. It has been
reported that some data access frameworks do not close all their
connection to the database after the tests. In such situations, you need
to use this option if you want the data to persist at the end of the
tests
You may actually want to use a file: database, or a server instance that serves a file: database in preference to a mem: database. As HyperSQL logs the DDL and DML statements in the .log file, this file can be used to check what is being sent to the database. Note that UPDATE statements are represented by a DELETE followed by an INSERT statement. Statements are written out when the connection commits. The write delay also has an effect on how soon the statements are written out. By default, the write delay is 0.5 second.
The new SQL logging feature in version 2.2.x and later records all executed statements and can be used for debugging your application.
Some types of tests start with a database that already contains the tables and data, and perform various operations on it during the tests. You can create and populate the initial database then set the property "files_readonly=true" in the .properties file of the database. The tests can then modify the database, but these modifications are not persisted after the tests have completed.
Databases with "files_readonly=true" can be placed within the classpath and in a jar file. In this case, the connection URL must use the res: protocol, which treats the database as a resource.
Different modes of operation and settings are used for different purposes. Some scenarios are discussed below:
In this usage, the amount of data change is often limited and
there is often a requirement to persist the data immediately. The
default write delay of 0.5 second is fine for many applications. You can
also use the property hsqldb.write_delay_millis=100
to reduce it to 0.1 second, or the property
hsqldb.write_delay=false
to force a disk fsync after
each commit. Before the application is closed, you should perform the
SHUTDOWN command to ensure the database is opened instantly when it is
next opened. Note you don't need to use SHUTDOWN COMPACT as
routine.
This usage involves a server application, such as a web application, connecting to an embedded HyperSQL instance. In this usage, the database is often accessed heavily, therefore performance and latency is a consideration. If the database is updated heavily, the default value of the WRITE DELAY property (0.5 sec) is often enough, as it is assumed the server or the application does not go down frequently. If it is necessary, you can reduce the WRITE DELAY to a small value (20 ms) without impacting the update speed. If you reduce WRITE DELAY to zero, performance drops to the speed of disk file sync operation.
Alternatively, a server application can use an all-in-mem database instance for fast access, while sending the data changes to a persistent, disk based instance either periodically or in real time.
Since you won't be able to access
in-process database instances from other
processes, you will often want to run a Listener in your applications
that use embedded databases. You can do this by starting up a Server or
WebServer instance programmatically, but you could also use the class
org.hsqldb.util.MainInvoker
to start up your
application and a HyperSQL Server or WebServer without any programming.
MainInvoker is a general-purpose utility class to invoke the main
methods of multiple classes. Each main class is followed by its
arguments (if any), then an empty string to separate it from the next
main class.
Example 15.2. MainInvoker Example
java -cp path/to/your/app.jar:path/to/hsqldb.jar org.hsqldb.util.MainInvoker com.your.main.App "" org.hsqldb.server.Server
(Use ; instead of : to delimit classpath elements on
Windows). The empty string separates your com.your.main.App invocation
from the org.hsqldb.server.
Specify the same in-process JDBC URL to
your app and in the server.properties
file. You can
then connect to the database from outside using a JDBC URL like
jdbc:hsqldb:hsql://hostname
, while connecting from
inside the application using something like
jdbc:hsqldb:file:<filepath of database>
.
This tactic can be used to run off-the-shelf server applications with an embedded HyperSQL Server, without doing any coding.
MainInvoker
can be used to run any
number of Java class main method invocations in a single JVM. See the
API spec for
MainInvoker
for details on its usage.
All file database that are not readonly, write changes to the .log
file. There are scenarios where writing to the .log file can be turned
off to improve performance, especially with larger databases. For these
applications you can set the property
hsqldb.log_data=false
to disable the recovery log and
speed up data change performance. The equivalent SQL command is SET
FILES LOG FALSE.
With this setting, no data is logged, but all the changes to cached tables are written to the .data file. To persist all the data changes up to date, you can use the CHECKPOINT command. If you perform SHUTDOWN, the data is also persisted correctly. If you do not use CHECKPOINT or SHUTDOWN when you terminate the application, all the changes are lost and the database reverts to its original state when it is opened without losing any of the original data.
Your server applications can use a database as a temporary disk data cache which is not persisted past the lifetime of the application. For this usage, delete the database files when the application ends.
On some platforms, such as embedded devices which have a reliable
storage device, this is also a useful option. Your application issues
CHECKPOINT to save the changes made so far. This method of use reduces
write operations on SSD devices. For this usage, the lock file should
also be disabled with the connection property
hsqldb.lock_file=false
.
Bulk inserts, deletes and updates are performed with the best performance with the following method. The database remains safe and consistent using this method. In the event of a machine crash during the operation, the database can be recovered to the point just before the bulk operation.
Before the operation, execute the SET FILES LOG FALSE statement.
Execute the CHECKPOINT statement.
Perform all the bulk operations, using batched prepared statements. A batch size of 1000 to 10000 is adequate. Perform commit after each batch.
After all the bulk operations are complete, execute the SET FILES LOG TRUE statement.
Finally execute the CHECKPOINT statement.
If you have performed many thousands of updates or deletes (not just inserts), it is a good idea to execute CHECKPOINT DEFRAG, instead of CHECKPOINT at the end.
If things go wrong during the bulk operation, for example when a unique constraint violation aborts the operation, and you want to redo the whole operation, just use SHUTDOWN IMMEDIATELY instead of CHECKPOINT. When you restart the database it will revert to the state at the first CHECKPOINT and the bulk operation can be redone.
This method of file access uses the operating system's
memory-mapped file buffer for the .data
file. For
larger databases with CACHED tables, use of nio improves database access
speed significantly. Performance improvements can be tenfold or even
higher. By default, NIO is used for .data files from 16 MB up to 256 MB.
You can increase the limit with the SET FILES NIO SIZE
<value>
statement. There should be enough RAM available
to accommodate the memory mapped buffers. For vary large nio usage, a 64
bit JVM must be used. The memory is not taken from the JVM memory
allocation, therefore there is no need to increase the -Xmx parameter of
the JVM. If not enough memory is available for the specified value, nio
is not used.
Any database that is not produced with the release version of HyperSQL 2.0 must be upgraded to this version.
Procedure 15.1. Upgrading Databases Created with Version 1.8.x
Open the database with the jar that created it and perform the SHUTDOWN statement as an SQL statement.
Open the database with the HyperSQL 2.0 jar.
Perform the SHUTDOWN COMPACT statement..
The first step is to guarantee there is no .log file for the database. When upgrading an application that has been deployed on a large scale, it is sometimes not practical to perform the first step of this procedure (with the old jar). You can ignore the first step but you may lose part of the database statements that are stored in the .log file. Therefore you need to test with databases created with your application to make sure typical statements that are logged in the .log file are compatible with the new version. Examples of known incompatible statements are some DDL statements used for changing the data type or default values of column.
A note about SHUTDOWN modes. SHUTDOWN COMPACT is equivalent to SHUTDOWN SCRIPT plus opening the database and then performing a simple SHUTDOWN.
After upgrading a database, you may want to change some of its settings. For example, the new SET FILES BACKUP INCREMENT TRUE statement can improve the shutdown and checkpoint times of larger databases.
Once a database is upgraded to 2.0, it can no longer be used with previous versions of HyperSQL.
To upgrade from version 1.8.x with the default TEXT format script files, follow the instructions above. If the 1.8.x files have database script format set to BINARY or COMPRESSED (ZIPPED) you must issue the SET SCRIPTFORMAT TEXT and SHUTDOWN SCRIPT commands with the old version, then open with the new version of the engine. In most cases the upgrade is successful and complete.
It is strongly recommended to execute SHUTDOWN COMPACT after an automatic upgrade from previous versions.
If your database has been created with version 1.7.2 or 1.7.3, first upgrade to version 1.8.1 and perform a SHUTDOWN SCRIPT with this version. You can then upgrade the database to version 2.0.
To upgrade from older version database files (1.7.1 and older)
that contain CACHED tables, use the SCRIPT procedure below. In all
versions of HyperSQL, the SCRIPT 'filename'
command
(used as an SQL statement) allows you to save a full record of your
database, including database object definitions and data, to a file of
your choice. You can export a script file using the old version of the
database engine and open the script as a database with 2.0.
Procedure 15.2. Upgrade Using the SCRIPT Procedure for Very Old Versions
Open the original database in the old version of DatabaseManager
Issue the SCRIPT command, for example SCRIPT
'newversion.script'
to create a script file containing a
copy of the database.
SHUTDOWN this database.
Copy the original *.properties
file into
newversion.properties
in the same directory as
newversion.script
Try to open the new database newversion
using DatabaseManager of version 1.8.1.
If there is any inconsistency in the data, the script line
number is reported on the console and the opening process is
aborted. Edit and correct any problems in the
newversion.script
before attempting to open
again. Use the guidelines in the next section (Manual Changes to the
.script
File). Use a programming editor that is
capable of handling very large files and does not wrap long lines of
text.
In HyperSQL 2.0 the full range of ALTER TABLE commands is
available to change the data structures and their names. However, if an
old database cannot be opened due to data inconsistencies, or it uses
index or column names that are not compatible with 2.0, manual editing
of the *.script
file can be performed and can be
faster.
Version 2.0 does not accept duplicate names for indexes that were allowed before 1.7.2.
Version 2.0 does not accept some table or column names that are SQL reserved keywords without double quoting.
Version 2.0 does not accept unquoted table or column names
which begin with an underscore, unless the connection
sql.regular_names
is set false.
Version 2.0 is more strict with check conditions and default values.
Other manual changes are also possible. Note that the
*.script
file must be the result of a SHUTDOWN SCRIPT
and must contain the full data for the database. The following changes
can be applied so long as they do not affect the integrity of existing
data.
Names
Names of tables, columns and indexes can be changed. These changes must be consistent regarding foreign key constraint references.
CHECK
A check constraint can always be removed.
NOT NULL
A not-null constraint can always be removed.
PRIMARY KEY
A primary key constraint can be removed. It cannot be removed if there is a foreign key referencing the column(s).
UNIQUE
A UNIQUE constraint can be removed if there is no foreign key referencing the column(s).
FOREIGN KEY
A FOREIGN KEY constraint can always be removed.
COLUMN TYPES
Some changes to column types are possible. For example an INTEGER column can be changed to BIGINT.
After completing the changes and saving the modified
.script
file, you can open the database as
normal.
HyperSQL 2 conforms to the SQL Standard better than previous versions and has many more features. For these reasons, there may be some compatibility issues when converting old database, or using applications that were written for version 1.8.x or earlier. Some of the potential issues (and enhancements) are listed here. See the full list of connection properties for alternatives.
By default, when comparison strings, the shorter string is
padded with spaces. This has an effect on comparing
'test'
and 'test '
which are now
considered equal, despite the length difference. This behaviour is
controlled by the default PAD SPACE property of collations, which can
be changed to NO PAD. See the statement SET DATABASE
COLLATION <name> [ PAD SPACE | NO PAD ]
.
User names and passwords are case-sensitive. Check the .script file of a database for the correct case of user name and password and use this form in the connection properties or on connection URL.
It is now possible to specify the admin username and password for a new database (instead of SA and the empty password).
HyperSQL 2.0 has several settings that relax its conformance to the SQL Standard in the areas of type conversion and object names. These settings can be turned on for maximum conformance.
Check constraints must conform to the SQL Standard. A check constraint is rejected if it is not deterministic or retrospectively deterministic. When opening an old database, HyperSQL silently drops check constraints that no longer compile. See under check constraints for more detail about what is not allowed.
Type declarations in column definition and in cast expressions must have the necessary size parameters.
In connection with the above, an old database that did not have
the enforce_strict_size
property, is now converted
to version 2.0 with the engine supplying the missing size parameters.
For example, a VARCHAR column declaration that has no size, is given a
32K size, a LONGVARCHAR column is given a 16MB size. Check these sizes
are adequate for your use, and change the column definition as
necessary.
Column names in a GROUP BY clause were previously resolved to the column label. They are now resolved to column name first, and if the name does not match, to the column label.
If two or more tables in a join contain columns with the same
name, the columns cannot be referenced in join and where conditions.
Use table names before column names to qualify the references to such
columns. The SET DATABASE SQL REFERENCES { TRUE | FALSE
}
statement enables or disables this check.
If the unqualified wild card is used, as in the statement SELECT * FROM ... no additional column references are allowed. A table-qualified wild card allows additional column references in the SELECT list
Table definitions containing GENERATED BY DEFAULT AS
IDENTITY
but with no PRIMARY KEY
do not
automatically create a primary key. Database .script files made with
1.8 are fine, as the PRIMARY KEY
clause is always
included. But the CREATE TABLE
statements in your
application program may assume an automatic primary key is created.
The old shortcut, IDENTITY, is retained with the same meaning. So
CREATE TABLE T (ID IDENTITY, DAT VARCHAR(20))
is
translated into CREATE TABLE T(ID INTEGER GENERATED BY
DEFAULT AS IDENTITY PRIMARY KEY, DAT VARCHAR(20))
. This last
form is the correct way of defining both autoincrement and primary key
in versions 1.8 and 2.0.
CREATE ALIAS is now obsolete. Use the new function definition
syntax. The org.hsqldb.Library
class no longer
exists. You should use the SQL form of the old library functions. For
example, use LOG(x)
rather than the direct form,
"org.hsqldb.Library.log"(x)
.
The names of some commands for changing database and session properties have changed. See the list of statements in this chapter.
Computed columns in SELECT statements which did not have an alias: These columns had no ResultMetaData label in version 1.8, but in version 2.0, the engine generates labels such as C1, C2.
The issue with the JDBC ResultSetMetaData methods,
getColumnName(int column)
and
getColumnLabel(int column)
has been clarified by
the JDBC 4 specification. getColumName()
returns
the underlying column name, while getColumnLabel()
returns any specified or generated alias. HyperSQL 1.8 and 2.0 have a
connection property, get_column_name
, which
defaults to true in version 2.0, but defaulted to false in some
releases of version 1.8.x. You have to explicitly specify this
property as false if you want (non-standard behaviour)
getColumnName()
to return the same value as
getColumnLabel()
.
This section is about building applications that have build-time dependencies upon HyperSQL, and for executions that use a dependency library system. Examples of the second type are unit test runs, job runs triggered by a build system, or systems like Grape that pull libraries from the network at end-user run time.
The best option for most developers is to use our
snapshot repository, or at least to depend upon the
latest public version of HyperSQL with a range pattern like
[2,)
. Here are exceptional cases where you should
depend on a static version.
If none of these situations apply to you, then follow
the suggestions in the appropriate sections below. If you need to
specify a specific version, follow the instructions in the
range-versioning section but change the version range specifications to
literal versions like 2.2.9
.
For application testing, you may want to use the latest HSQLDB snapshot jar instead of the latest release jar. Snapshot jars contain fixes for reported bugs and the new features as they are being tested for the next release version. You can use the snapshot jars where you would normally include a dependency to a release jar as a Maven artifact. The HyperSQL Snapshot repository resides at http://hsqldb.org/repos/
Where you insert the <repository> element depends on whether you want the definition to be personal, shared, or project-specific, so see the Maven documentation about that. But you can paste this element verbatim:
Example 15.3. HyperSQL Snapshot Repository Definition
<repository> <releases> <enabled>false</enabled> </releases> <snapshots> <enabled>true</enabled> <updatePolicy>always</updatePolicy> <checksumPolicy>fail</checksumPolicy> </snapshots> <id>hsqldb_snapshots</id> <name>HyperSQL Snapshots</name> <url>http://hsqldb.org/repos</url> <layout>default</layout> </repository>
Example 15.4. Sample Snapshot Ivy Dependency
<dependency org="org.hsqldb" name="hsqldb" rev="SNAPSHOT" conf="buildOnly"/>
Example 15.5. Sample Snapshot Maven Dependency
<dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>SNAPSHOT</version> <!-- Scope defaults to "compile": <scope>test</scope> --> </dependency>
Example 15.6. Sample Snapshot Gradle Dependency
dependencies.compile (group: 'org.hsqldb', name: 'hsqldb', version:'SNAPSHOT') dependencies { runtime 'org.hsqldb:hsqldb:SNAPSHOT', 'org.hsqldb:sqltool:SNAPSHOT' }
If you want to use an ivy.xml
file with a
Gradle build, you will need use the Ivyxml
Gradle Plugin. It just takes a few links of code in your
build.gradle
file to hook in
ivyxml
. See the
Ivyxml documentation to see exactly how.
Example 15.7. Sample Snapshot ivy.xml loaded by Ivyxml plugin
<ivy-module version="2.0"> ... <dependency org="org.hsqldb" name="hsqldb" rev="SNAPSHOT"/>
Example 15.8. Sample Snapshot Groovy Dependency, using Grape
@Grab(group='org.hsqldb', module='hsqldb', version='SNAPSHOT')
| Limitation of Maven Version Range Specifiers | |
|---|---|
|
Note that Ivy (and the many systems that use Ivy underneath,
like Grape and Gradle) supports the opening exclusive
|
| Important | |
|---|---|
|
For all examples below, when a range pattern is given, it
means the latest version equal or greater than version
|
Example 15.9. Sample Range Ivy Dependency
<dependency org="org.hsqldb" name="hsqldb" rev="[2,)" conf="j6->default"/>
I give no example here of specifying a
classifier in ivy.xml
because I have so far failed to get that to succeed. Classifiers in
in ivy.xml
are supported if using Gradle, as
covered below.
Example 15.10. Sample Range Maven Dependency
See note above about Maven range specifications.
<dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>[2,)</version> <!-- Scope defaults to "compile": <scope>test</scope> Use a classifier to pull one of our alternative jars: <classifier>jdk5</classifier> --> </dependency>
Example 15.11. Sample Range Gradle Dependency
dependencies.compile (group: 'org.hsqldb', name: 'hsqldb', version:'[2,)', classifier: 'jdk5') dependencies { runtime 'org.hsqldb:hsqldb:[2,):jdk6debug@jar', 'org.hsqldb:sqltool:[2,):jdk6debug@jar' }
If you want to use an ivy.xml
file with a
Gradle build, you will need use the Ivyxml
Gradle Plugin. It just takes a few links of code in your
build.gradle
file to hook in
ivyxml
. See the
Ivyxml documentation to see exactly how.
Example 15.12. Sample Range ivy.xml loaded by Ivyxml plugin
<ivy-module version="2.0" xmlns:m="http://ant.apache.org/ivy/maven"> ... <dependency org="org.hsqldb" name="hsqldb" rev="[2,)" m:classifier="jdk5"/>
Example 15.13. Sample Range Groovy Dependency, using Grape
@Grab(group='org.hsqldb', module='hsqldb', version='[2,)', classifier='jdk6debug')
Table of Contents
According to the SQL Standard, the SQL Language keywords cannot be used as identifiers (names of database objects such as columns and tables) without quoting.
HyperSQL has two modes of operation, which are selected with the
SET DATABASE SQL NAMES { TRUE | FALSE }
to allow or
disallow the keywords as identifiers. The default mode is FALSE and allows
the use of most keywords as identifiers. Even in this mode, keywords
cannot be used as USER or ROLE identifiers. When the mode is TRUE, none of
the keywords listed below can be used as identifiers.
All keywords can be used with double quotes as identifiers. For example
CREATE TABLE "ALL" ("AND" INT, "WHEN" INT) SELECT "AND" FROM "ALL" WHERE "WHEN" = 2020
ABS • ALL • ALLOCATE • ALTER • AND • ANY • ARE • ARRAY • AS • ASENSITIVE • ASYMMETRIC • AT • ATOMIC • AUTHORIZATION • AVG
BEGIN • BETWEEN • BIGINT • BINARY • BLOB • BOOLEAN • BOTH • BY
CALL • CALLED • CARDINALITY • CASCADED • CASE • CAST • CEIL • CEILING • CHAR • CHAR_LENGTH • CHARACTER • CHARACTER_LENGTH • CHECK • CLOB • CLOSE • COALESCE • COLLATE • COLLECT • COLUMN • COMMIT • COMPARABLE • CONDITION • CONNECT • CONSTRAINT • CONVERT • CORR • CORRESPONDING • COUNT • COVAR_POP • COVAR_SAMP • CREATE • CROSS • CUBE • CUME_DIST • CURRENT • CURRENT_CATALOG • CURRENT_DATE • CURRENT_DEFAULT_TRANSFORM_GROUP • CURRENT_PATH • CURRENT_ROLE • CURRENT_SCHEMA • CURRENT_TIME • CURRENT_TIMESTAMP • CURRENT_TRANSFORM_GROUP_FOR_TYPE • CURRENT_USER • CURSOR • CYCLE
DATE • DAY • DEALLOCATE • DEC • DECIMAL • DECLARE • DEFAULT • DELETE • DENSE_RANK • DEREF • DESCRIBE • DETERMINISTIC • DISCONNECT • DISTINCT • DO • DOUBLE • DROP • DYNAMIC
EACH • ELEMENT • ELSE • ELSEIF • END • END_EXEC • ESCAPE • EVERY • EXCEPT • EXEC • EXECUTE • EXISTS • EXIT • EXP • EXTERNAL • EXTRACT
FALSE • FETCH • FILTER • FIRST_VALUE • FLOAT • FLOOR • FOR • FOREIGN • FREE • FROM • FULL • FUNCTION • FUSION
GET • GLOBAL • GRANT • GROUP • GROUPING
HANDLER • HAVING • HOLD • HOUR
IDENTITY • IN • INDICATOR • INNER • INOUT • INSENSITIVE • INSERT • INT • INTEGER • INTERSECT • INTERSECTION • INTERVAL • INTO • IS • ITERATE
JOIN
LAG
LANGUAGE • LARGE • LAST_VALUE • LATERAL • LEAD • LEADING • LEAVE • LEFT • LIKE • LIKE_REGEX • LN • LOCAL • LOCALTIME • LOCALTIMESTAMP • LOOP • LOWER
MATCH • MAX • MAX_CARDINALITY • MEMBER • MERGE • METHOD • MIN • MINUTE • MOD • MODIFIES • MODULE • MONTH • MULTISET
NATIONAL • NATURAL • NCHAR • NCLOB • NEW • NO • NONE • NORMALIZE • NOT • NTH_VALUE • NTILE • NULL • NULLIF • NUMERIC
OCCURRENCES_REGEX • OCTET_LENGTH • OF • OFFSET • OLD • ON • ONLY • OPEN • OR • ORDER • OUT • OUTER • OVER • OVERLAPS • OVERLAY
PARAMETER • PARTITION • PERCENT_RANK • PERCENTILE_CONT • PERCENTILE_DISC • POSITION • POSITION_REGEX • POWER • PRECISION • PREPARE • PRIMARY • PROCEDURE
RANGE • RANK • READS • REAL • RECURSIVE • REF • REFERENCES • REFERENCING • REGR_AVGX • REGR_AVGY • REGR_COUNT • REGR_INTERCEPT • REGR_R2 • REGR_SLOPE • REGR_SXX • REGR_SXY • REGR_SYY • RELEASE • REPEAT • RESIGNAL • RESULT • RETURN • RETURNS • REVOKE • RIGHT • ROLLBACK • ROLLUP • ROW • ROW_NUMBER • ROWS
SAVEPOINT • SCOPE • SCROLL • SEARCH • SECOND • SELECT • SENSITIVE • SESSION_USER • SET • SIGNAL • SIMILAR • SMALLINT • SOME • SPECIFIC • SPECIFICTYPE • SQL • SQLEXCEPTION • SQLSTATE • SQLWARNING • SQRT • STACKED • START • STATIC • STDDEV_POP • STDDEV_SAMP • SUBMULTISET • SUBSTRING • SUBSTRING_REGEX • SUM • SYMMETRIC • SYSTEM • SYSTEM_USER
TABLE • TABLESAMPLE • THEN • TIME • TIMESTAMP • TIMEZONE_HOUR • TIMEZONE_MINUTE • TO • TRAILING • TRANSLATE • TRANSLATE_REGEX • TRANSLATION • TREAT • TRIGGER • TRIM • TRIM_ARRAY • TRUE • TRUNCATE
UESCAPE • UNDO • UNION • UNIQUE • UNKNOWN • UNNEST • UNTIL • UPDATE • UPPER • USER • USING
VALUE • VALUES • VAR_POP • VAR_SAMP • VARBINARY • VARCHAR • VARYING
WHEN • WHENEVER • WHERE • WIDTH_BUCKET • WINDOW • WITH • WITHIN • WITHOUT • WHILE
YEAR
When the default SET DATABASE SQL NAMES FALSE
mode is used, only a subset of SQL Standard keywords cannot be used as
HyperSQL identifiers. The keywords are as follows:
ALL • AND • ANY • AS • AT • AVG
BETWEEN • BOTH • BY
CALL • CASE • CAST • COALESCE • CORRESPONDING • CONVERT • COUNT • CREATE • CROSS
DEFAULT • DISTINCT • DROP
ELSE • EVERY • EXISTS • EXCEPT
FOR • FROM • FULL
GRANT • GROUP
HAVING
IN • INNER • INTERSECT • INTO • IS
JOIN
LEFT • LEADING • LIKE
MAX • MIN
NATURAL • NOT • NULLIF
ON • ORDER • OR • OUTER
PRIMARY
REFERENCES • RIGHT
SELECT • SET • SOME • STDDEV_POP • STDDEV_SAMP • SUM
TABLE • THEN • TO • TRAILING • TRIGGER
UNION • UNIQUE • USING
VALUES • VAR_POP • VAR_SAMP
WHEN • WHERE • WITH
HyperSQL supports SQL Standard functions that are called without
parentheses. These functions include CURRENT_DATE, LOCALTIMESTAMP,
TIMEZONE_HOUR, USER, etc. When the default SET DATABASE SQL NAMES
FALSE
mode is used, keywords that are names of SQL functions can
be used as column names without double quotes in CREATE TABLE statements .
But when the identifier is a column name and is referenced in SELECT or
other statements, the keywords must be double quoted. Otherwise the result
of the SQL function is returned instead of the column value.
HyperSQL also supports non-standard functions SYSTIMESTAMP, CURDATE, CURTIME, TODAY, SYSDATE and NOW which can be called with or without parentheses ( e.g. NOW() or NOW ). These names can be used as column names, but the names must be double quoted in SELECT and other statements.
Table of Contents
From version 2.0, the supplied hsqldb.jar
file is built with Java 1.6. If you want to run with a 1.5 or older JVM,
or if you want to use an alternative jar
(hsqldb-min.jar
, etc.) you must build the desired jar
with a Java SDK.
The Gradle task / Ant target explainjars
reports the versions of Java and Ant actually used.
As noted above, Java SDK 5 or later is required.
Unlike most software build systems, you do not need to have the
Gradle system installed on your computer to use it. You don't need to
understand the details to use it, but this is the purpose of the
gradlew
wrapper scripts that you
can see in HyperSQL's build
directory. If you want or
need to learn more about Gradle, you can start on the Gradle Documentation
page on the Gradle web
site.
Gradle honors JAVA_HOME |
|
|---|---|
|
Gradle can find the Java to use by finding out where
|
| Rare Gotcha | |
|---|---|
|
Depending on your operating system, version, and how you
installed your JDK, Gradle may not be able to find the JDK. Gradle will
inform you if this happens. The easiest way to fix this problem is to
set environmental variable |
Whether from an IDE, a shortcut or launch icon, to run a Gradle
graphical build you just need to execute either the file
gradle-gui.cmd
(on Windows) or
gradle-gui
(all other platforms), both of which
reside in the build
directory of your HyperSQL
distribution.
I will explain how to invoke a graphical Gradle build from Windows Explorer and from Eclipse IDE. Users of other operating systems should be able to infer how to use their own file manager in the same way as shown for Internet Explorer. Users who want a desktop shortcut, quick-launch icon should first get Gradle working from a file manager (like Windows Explorer), then seek out instructions for making shortcuts, etc. for your operating system or desktop manager. (Try a web search).
Some IDEs, like IntelliJ have direct support for Gradle. The Spring Framework team is working on a sophisticated plugin for using Gradle with their IDE. But I'm going to document a very basic setup done with Eclipse because it's serviceable and a very similar procedure is likely to work with all other IDEs.
If you do use and enjoy Gradle, then I urge you to make the product better by registering a free account for yourself at the Gradle Jira site and vote for critical usability issues like GRADLE-427, GRADLE-1855, GRADLE-1870, GRADLE-1871, to help to improve the product.
Procedure B.1. Invoking Gradle GUI from Windows Explorer
Start up Windows explorer. Depending on your Windows version, it will be in the Start Menu, or in the menu you get when you right-click
.Navigate Windows Explorer to the build
directory within your HyperSQL installation.
Find an icon or line (depending on your Windows Explorer
view) for the file gradle-gui.cmd
. If there is
no listing for gradle-gui.cmd
, but two listings
for gradle-gui
, then you want the one signified
by text, icon, or mouse-over tooltip, as a batch or CMD
file. Double-click this item.
Procedure B.2. Setting up Gradle Graphical Builds from Eclipse IDE
From Eclipse, use pulldown menu
/ / .Right-click on Program in the left navigator Right-click Project in the left navigator panel and select . (Depending on the state of your workspace, instead of in the context-sensitive menu, there may be a New_configuration or similar item nested under Program, in which case you should select that).
To the right, change the value in the
Name: field to HSQLDB Gradle
(or whatever name you want for this launcher config (this Gradle
launcher is only for your HSQLDB project).
Make sure that the Main tab is selected.
For the gradle-gui.cmd
(Windows) or
gradle-gui
(other) file in the
build
directory of your HyperSQL project.
Depending on your Eclipse version and workspace setup, the value populated into the Location: field after you select the program may appear very differently than in this screen shot.
Click the Run button. The Gradle Gui should run. (If you just Apply and Close here instead of Run, the new Gradle launch item will not be added to the pulldown and toolbar menus).
After doing the Eclipse setup, you can use pulldown menu
/ or the equivalent tool bar button button to launch the Gradle Gui.You can do this and close it after each use, or, to avoid startup lag, minimize it when it's not in use.
You can invoke graphical and non-graphical Gradle builds from the command-line.
Get a command-line shell. Windows users can use either
cmd
".
Non-windows users will know how to get a shell.
In the shell, cd to the build
directory under the root directory where you extracted or installed
HyperSQL to. (Operating system search or find functions can be used
if you can't find it quickly by poking around on the command line or
with Windows Explorer, etc.).
Windows users can ignore this step. UNIX shell users should
ensure that the current directory (.
) is in their
search path, or prefix their gradlew
or
gradle-gui
command in the next step with
./
(e.g., like
./gradlew
).
In the shell, run either gradle-gui
for
a graphical build; or gradlew
for a text-based
build.
The gradle-gui
file is our own wrapper
script for gradlew --gui
. Be aware that both
gradle-gui
and gradlew --gui
suffer from the limitation that the --gui
switch is
mutually exclusive with most or all other arguments (including tasks). I
have registered GRADLE bugs 1861 and 1863 about this.
If you ran just gradlew
or
gradlew.bat
, then you will be presented with simple
instructions for how to do everything that you want to do. Basically,
you will run the same gradlew
or
gradle.bat
command repeatedly, with different
switches and arguments.
| Note | |
|---|---|
|
Gradle's -v switch reports version details more directly
than the |
Procedure B.3. First Time using Gradle Gui
It takes the Gradle gui a while to start up, because, similar to an IDE, it is generating a list of details about available tasks.
In the main window, in the top panel, with the
Task Tree tab selected, you have the list of
public tasks, sorted alphabetically. Down bottom is displayed the
output of the last task(s) execution. (After startup it will show
the output of the task tasks
).
Scroll to the help
task and click it
once to select it, then click the green
Execute toolbar button above. (You could also
have double-clicked the item, but you can use the selection
procedure to pick multiple tasks with Control or Shift keys to
execute multiple tasks in a single run-- and the tasks will
execute in the same order that you had selected them).
Scroll through and read the output of the
help
task in the bottom panel. Where this help
screen speaks about verbosity switches, you can accomplish the
same thing by using the tab.
Whenever Gradle output (in the bottom panel) talks about running
gradlew <sometask>...
, you can execute
the specified task(s) by selecting and executing them like we just
did.
| Gradle GUI Limitations | |
|---|---|
|
The Gradle GUI is fairly new and lacks some of the power
available to text-based users. Most significantly, in my opinion, is
the following item for which I have opened Gradle
issues 1855. There is no convenient way to set
build properties. If you want to change Ant or Gradle build
settings, edit the text file |
You should use version 1.7.x of Ant (Another Neat Tool) to do Ant builds with HyperSQL.
Ant is a part of the Jakarta/Apache Project.
Once you have unpacked the zip package for hsqldb, under the
/hsqldb
folder, in /build
there is a build.xml
file that builds the
hsqldb.jar
with Ant (Ant must be already
installed). To use it, change to /build
then
type:
ant -projecthelp
This displays the available ant targets, which you can supply as command line arguments to ant. These include
|
hsqldb | to build the hsqldb.jar
file |
|
explainjars | Lists all targets which build jar files, with an explanation of the purposes of the different jars. |
|
clean | to clean up the /classes directory that is created |
|
clean-all | to remove the old jar and doc files as well |
|
javadoc | to build javadoc |
|
hsqldbmain | to build a smaller jar for HSQLDB that does not contain utilities |
|
hsqljdbc | to build an extremely small jar containing only the client-side JDBC driver (can connect only to a HyperSQL Server). |
|
hsqldbmin | to build a small jar that supports in-process catalogs, but neither running nor connecting to HyperSQL Servers. |
|
sqltool | to build sqltool.jar, which contains only the SqlTool classes. |
|
... | Many more targets are available. Run ant
-p and ant explainjars . |
HSQLDB can be built in any combination of two JRE (Java Runtime Environment) versions and many jar file sizes.
A jar built with an older JRE is compatible for use with a newer JRE (you can compile with Java 1.5 and run with 1.6). But the newer JDBC capabilities of the JRE will be not be available.
The client jar (hsqljdbc.jar
) contains
only the HSQLDB JDBC Driver client. The smallest engine jar
(hsqldbmin.jar
) contains the engine and the HSQLDB
JDBC Driver client. The default size (hsqldb.jar
)
also contains server mode support and the utilities. The largest size
(hsqldbtest.jar
)includes some test classes as well.
Before building the hsqldbtest.jar
package, you
should download the junit jar from http://www.junit.org and put it in the
/lib
directory, alongside
servlet.jar
, which is included in the .zip
package.
If you want your code built for high performance, as opposed to
debugging (in the same way that we make our production distributions),
make a file named build.properties
in your build
directory with the contents
build.debug: false
The resulting Java binaries will be faster and smaller, at the cost of exception stack traces not identifying source code locations (which can be extremely useful for debugging).
After installing Ant on your system use the following command
from the /build
directory. Just run ant
explainjars
for a concise list of all available jar
files.
ant explainjars
The command displays a list of different options for building different sizes of the HSQLDB Jar. The default is built using:
The Ant method always builds a jar with the JDK that is used by Ant and specified in its JAVA_HOME environment variable.
HyperSQL version 2.0 cannot be directly compiled or used with JDK 1.4. It may be possible to use the RetroTranslator tool to achieve this. The suggested procedure is as follows: First use Gradle or Ant with JDK 1.5 and build the jar. Then translate the jar using RetroTranslator with backport (which bundles replacement classes for concurrency control). This translation should cover the concurrency features that are specific to version 1.5 and later.
ant switchtojdk14 ant hsqldb -- translate the jar
All HyperSQL source files are supplied ready to compile. There is no complex pre-compile stage. It is therefore possible to compile the sources with an IDE, without using Gradle or Ant. Only if compilation with Java 1.5 is required, you should first run the Gradle task (or Ant target) before compiling and remove from the source directories a few source files that are specific to Java 6 (these are listed in the build.xml file).
CodeSwitcher is a tool to manage different version of Java source code. It allows to compile HyperSQL for different JDKs. It is something like a precompiler in C but it works directly on the source code and does not create intermediate output or extra files.
CodeSwitcher is used internally in the Ant build. You do not have to invoke it separately to compile HyperSQL.
CodeSwitcher reads the source code of a file, removes comments where appropriate and comments out the blocks that are not used for a particular version of the file. This operation is done for all files of a defined directory, and all subdirectories.
Example B.2. Example source code before CodeSwitcher is run
... //#ifdef JAVA2 properties.store(out,"hsqldb database"); //#else /* properties.save(out,"hsqldb database"); */ //#endif ...
The next step is to run CodeSwitcher.
The '.' means the program works on the current directory (all
subdirectories are processed recursively). -JAVA2
means
the code labelled with JAVA2 must be switched off.
Example B.4. Source code after CodeSwitcher processing
... //#ifdef JAVA2 /* pProperties.store(out,"hsqldb database"); */ //#else pProperties.save(out,"hsqldb database"); //#endif ...
For detailed information on the command line options run
java org.hsqldb.util.CodeSwitcher
. Usage examples
can be found in the build.xml file in the /build
directory.
The JavaDoc can be built simply by invoking the javadoc task/target with Gradle or Ant.
The two Guides (the one you are reading now plus the Utilities user
guide) are in DocBook XML source format. To rebuild to PDF or one of the
HTML output formats from the XML source, run the Gradle target
gen-docs
(or the Ant target
gen-docs
). Instructions will be displayed. In
particular
gen-docs
task/target will tell you of a Gradle
task that you can use to download and install them automatically.
This Gradle task, installDbImages
, will tell you
how to edit a properties text file to tell it what directory to
install the files into. (Command-line, as opposed to GUI, builders,
can use the Gradle -P
switch to set the property,
instead of editing, if they prefer).build.xml
in the HyperSQL
build
directory about where to obtain these
things and how to hook them in. The same Gradle task
installDbImages
explained above can download and
install the entire stylesheet bundle (this option is offered the
first time that you run the installDbImages
task).| Tip | |
|---|---|
|
If running Gradle, you probably want to turn logging up to level info for generation and validation tasks, because the default warn/lifecycle level doesn't give much feedback. |
The task/target validate-docs
is also very
useful to DocBook builders.
The documentation license does not allow you to post
modifications to our guides, but you can modify them for internal use by
your organization, and you can use our DocBook system to write new DocBook
documents related or unrelated to HyperSQL. To create new DocBook
documents, create a subdirectory off of doc-src
for
each new document, with the main DocBook source file within having same
name as the directory plus .xml
. See the peer directory
util-guide
or guide
as an
example. If you use the high-level tasks/target
gen-docs
or validate-docs
, then copy
and paste to add new stanzas to these targets in file
build.xml
.
Editors of DocBook documents (see previous paragraph for motive)
may find it useful to have a standalone XML validator so you can do your
primary editing without involvement of the build system. Use the Gradle
target standaloneValidation
for this. It will tell you
how to set a build property to tell it where to install the validator, and
will give instructions on how to use it.
There are several properties that can be used to dramatically
decrease run times for partial doc builds. Read about these properties in
comment at the top of the file build-docbook.xml
in
the build
directory.
See the file doc-src/readme-docauthors.txt
for details about our DocBook build system (though as I write this it is
somewhat out of date).
Table of Contents
OpenOffice.org / LibreOffice / ApacheOpenOffice includes HyperSQL and uses it for embedded databases. Our collaboration with OpenOffice.org developers over the last few years has benefited the development and maturity of HyperSQL. Before integration into OOo, HSQLDB was intended solely for application-specific database access. The application developer was expected to resolve any integration issues. Because OpenOffice.org is used by a vast range of users, from schoolchildren to corporate developers, a much higher level of quality assurance has been required. We have achieved it with constant help and feedback from OOo users and developers.
Apart from embedded use, you may want to use OpenOffice / LibreOffice with a HyperSQL server instance. The typical use for this is to allow multiple office users access to the same database.
There is also a strong case for using OpenOffice to develop your database schema and application, even if the database is intended for your own application, rather than OpenOffice.
HSQLDB version 1.8.0 is included in OOo, ApacheOpenOffice and LibreOffice 3.x. You can simply replace the jar with an HSQLDB version 2.2.9 jar to use the latest capabilities with external databases. It is not yet possible to create and use embedded databases with this version.
HSQLDB version 2.x jar will hopefully be included in the future versions of ApacheOpenOffice and LibreOffice.
OpenOffice is a powerful database front end. If you want to create schemas, edit tables, edit the database contents manually, design and produce well-formatted reports, then OpenOffice is probably the best open source tools currently available.
To connect from OpenOffice to your database, first run a local server instance for the database. This is describes in the Network Listeners chapter of this guide.
When you connect from OpenOffice.org, you must specify connection to an external database and use the URL property "default_schema=true". For example, the URL to connect the local database may be like
jdbc;hsqldb:hsql://localhost/mydb;default_schema=true
The only current limitation is that OpenOffice only works with the PUBLIC schema. This limitation will hopefully disappear in the future versions of OOo.
There wil hopefuly be a version 2.x jar in the future versions of OpenOffice.
You may already have an OOo database file, which you want to use outside OOo, or as a server database. The file is in fact in the standard ZIP format and contains the normal HyperSQL database files. Just use a utility such as 7Zip to expand the .odb file. In the /db directory, there are files such as .script, .data, etc. Just rename these files into mydb.script, mydb.data, etc. You can now open the mydb database directly with HyperSQL as an embedded database or as a server instance.
HyperSQL files referred to in the text may be retrieved from the canonical HyperSQL documentation site, http://hsqldb.org/doc/2.0, or from the same location you are reading this page from.
| Note | |
|---|---|
|
If you are reading this document with a standalone PDF reader, only the http://hsqldb.org/doc/2.0/... links will function. |
Pairs of local + http://hsqldb.org/doc/2.0 links for referenced files.
Local: ../apidocs/org/hsqldb/jdbc/JDBCConnection.html
http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCConnection.html
Local: ../apidocs/org/hsqldb/jdbc/JDBCDriver.html
http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCDriver.html
Local: ../apidocs/org/hsqldb/jdbc/JDBCDatabaseMetaData.html
http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCDatabaseMetaData.html
Local: ../apidocs/org/hsqldb/jdbc/JDBCResultSet.html
http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCResultSet.html
Local: ../apidocs/org/hsqldb/jdbc/JDBCStatement.html
http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCStatement.html
Local: ../apidocs/org/hsqldb/jdbc/JDBCPreparedStatement.html
http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jdbc/JDBCPreparedStatement.html
Local: ../apidocs/org/hsqldb/util/MainInvoker.html
http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/util/MainInvoker.html
Local: ../apidocs/index.html
Local: ../verbatim/src/org/hsqldb/server/Servlet.java
http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/server/Servlet.java
Local: ../verbatim/src/org/hsqldb/Tokens.java
http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/Tokens.java
Local: ../verbatim/src/org/hsqldb/server/WebServer.java
http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/server/WebServer.java
Local: ../verbatim/src/org/hsqldb/test/TestBase.java
http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/test/TestBase.java
Local: ../verbatim/src/org/hsqldb/Trigger.java
http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/Trigger.java
Local: ../verbatim/src/org/hsqldb/sample/TriggerSample.java
http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/test/sample/TriggerSample.java
Local: ../verbatim/src/org/hsqldb/util/MainInvoker.java
http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/util/MainInvoker.java
Local: ../verbatim/sample/acl.txt
$Revision: 5206 $