HSQLDB relies on a set of properties files for different settings. Version 1.7.0 streamlines property naming and introduces a number of new properties (in this document, all references to versions 1.7.0 also apply to version 1.7.1 unless stated otherwise) . This process will continue with future versions and the properties will be used in a hierarchical manner.
The properties files and the settings stored in them are as follows:
FILE NAME | LOCATION | FUNCTION |
server.properties |
the directory where the command to run the Server class is issued |
settings for running HSQLDB as a database server communicating with the HSQL protocol |
webserver.properties |
the directory where the command to run the WebServer class is issued |
settings for running HSQLDB as a database server communicating with the HTTP protocol |
<dbname>.properties |
the directory where all the files for a database are located | settings for each particular database |
Properties files for running the servers do not already exist. You should create your own files that contain server.property=value
pairs for each property. The properties file for each database is generated by the database engine. This file can be edited after closing the database. 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).
In both server.properties
and webserver.properties
files, supported values and their defaults are as follows:
VALUE |
DEFAULT |
DESCRIPTION |
server.database |
test |
the path and file name of the database file to use |
server.silent |
true |
no extensive messages displayed on console |
server.trace |
false |
JDBC trace messages displayed on console |
Values specific to server.properties
are:
VALUE |
DEFAULT |
DESCRIPTION |
server.port |
9001 |
TCP/IP port used for talking to clients |
server.no_system_exit |
false |
no |
Values specific to webserver.properties
are:
VALUE |
DEFAULT |
DESCRIPTION |
server.port |
80 |
TCP/IP port used for talking to clients |
server.default_page |
index.html |
the default page for server |
server.root |
./ |
the location of served pages |
.<extension> |
? |
multiple entries such as |
All the above values can be specified on the command line by omitting the server.
prefix.
Upgrading: If you have existing custom properties files, change the values to the new naming convention.
Each database has its own <dbname>.properties
file as part
of a small group of files which also includes <dbname>.script
and
<dbname>.data
. The properties files contain key/value pairs
for some important settings. The file can be edited after closing the database. Only the user-defined values listed below should
ever be modified. Changing any other value will result in unexpected malfunction
in database operations. Most of these values have been introduced for the new features in 1.7.0 and are listed
below with their default values in different contexts:
VALUE |
DEFAULT |
DESCRIPTION |
|
|
whole database is read-only |
When true, the database cannot be modified in use. This setting can be
changed to |
||
sql.month |
true |
month(Date) return value |
When true, returns month 1-12 with the Library function, When false, returns 0-11 as it did before 1.7.0 |
||
sql.enforce_size |
false |
trimming and padding string columns |
When true, all CHARACTER and VARCHAR values that are in a row affected by an INSERT INTO or UPDATE statement are trimmed to the size specified in the SQL table definition. Also all char strings that are shorter than the specified size are padded with spaces. When false (default), stores the exact string that is inserted. |
||
sql.compare_in_locale |
false |
locale used for sorting |
CHARACTER and VARCHAR columns are by default sorted according to POSIX standards. Setting the value to true will result in sorting in the character set of the current JRE locale. Changing this value for an existing database that contains cached tables will break the indexing and result in inconsistent operation. To avoid this, first change the value in the properties file, then open the database and issue the SHUTDOWN COMPACT command to recreate all the indexes. |
||
sql.strict_fk |
true (false for existing db's) |
require unique index for foreign keys |
The default is false when opening a database created with an older version of HSQLDB; true when creating a new database. If true, requires a pre-existing unique index for the column(s) referenced by a foreign key constraint and returns an error if the index does not already exist. If false, creates an index if no index exists, based on the Changing this value to true on an existing database may result in errors when the database is opened. Deprecated: In versions after 1.7.1,
|
||
sql.strong_fk |
true |
make unique index for foreign keys |
Has no effect if If you have an existing database which relies on an automatic index (there is no primary key or unique constraint on the referenced columns) and has duplicate values in the columns, then 1.7.0 will report an exception when you attempt to open the database. If this happens, you can manually add a line to the .properties file with:
in order to allow the old database to be opened. It is a good idea to
modify the data and remove the duplicate values, as these would cause
data integrity problems. You can then change the value to Deprecated: In versions after 1.7.1 no automatic index will be created on referenced columns and this property will have no effect.
|
||
hsqldb.cache_scale |
14 |
memory cache exponent |
Indicates the size of memory cache used with cached tables, calculated as 2^value. The default results in up to 3*16000 rows from all cached tables being held in memory at any time. The value can range between 8-16. |
||
hsqldb.log_size |
200 |
size of log when checkpoint is performed |
The value is the size in megabytes that the .script file can reach before an automatice checkpoint occurs to rewrite the file. The value can be changed via the SET LOGSIZE nnn SQL command. |
||
hsqldb.gc_interval |
|
forced garbage collection |
This setting forces garbage collection each time a set number of result set row or cache row objects are created. The default, "0" means no garbage collection is forced by the program. This should not be set when the database engine is acting as a server. The setting can be useful, when the database is used in-process with some Java Runtime Environments (JRE’s). Some JRE’s increase the size of the memory heap before doing any automatic garbage collection. This setting would prevent any unnecessary enlargement of the heap. Typical values for this setting would probably be between 10,000 to 100,000. |
Upgrading: The location of the database files can no longer be overridden by paths defined in the properties file. All files belonging to a database should reside in the same directory.
Connection properties are not files. In version 1.7.1, each JDBC connection to the DB can specify the following optional properties for special purposes. This is done by establishing the connection via the:
DriverManager.getConnection (String url, Properties info);
method call.
jdbc.strict_md | false | throw exceptions for unsupported methods |
When true, several ResultSetMetaData.isXXX(int column) methods throw an SQLException to indicate they are not supported. In the default mode, these methods return true/false values that are not always correct for the specific column. The default mode allows compatibility with known implementations of the javax.sql.RowSet interface. | ||
jdbc.get_column_name |
true |
column name in ResultSet |
When true, When false, the above method returns the same value as |
Version 1.7.0 features major improvements on previous versions in this area.
Previously a CONSTRAINT <name> PRIMARY KEY
was translated
internally to a unique index and, in addition, a hidden column was added to
the table with an extra unique index. In 1.7.0 both single-column and multi-column
PRIMARY KEY constraints are supported. They are supported by a unique index
on the primary key column(s) specified and no extra hidden column is added.
CONSTRAINT <name> UNIQUE
always creates a unique index on the columns, as with previous
versions.
Note that in HSQLDB a unique index on multiple columns is used internally as
a non-unique index on the first column in the list. For example: CREATE
UNIQUE INDEX name1 ON atable(c1, c2, c3);
means there is the equivalent
of CREATE INDEX name2 ON atable(c1);
So you do not need to specify
an extra index if you require one on the first column of the list.
A multi-column index will not speed up queries that contain joins on any column other than the first. You should declare individual indexes (or unique constraints) on those columns if they appear in joins or query conditions.
In multiple key indexes, the order of declared columns can affect the speed of searches. If the column that contains more diverse values appears first, the searches will be faster.
Version 1.7.0 features single and multiple column foreign keys. A foreign key can also be specified to reference a target table without naming the target column(s). In this case the primary key column(s) of the target table is used as the referenced column(s). Each pair of referencing and referenced columns in any foreign key should be of identical type. In the default mode, when a foreign key is declared, a unique index (or primary key index) must exist on the referenced columns in the primary key table. A non-unique index is automatically created on the referencing columns. For example:
CREATE TABLE child(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2));
There must be a UNIQUE CONSTRAINT or UNIQUE INDEX on columns (p1,p2)
in the table named "parent". A non-unique index is automatically created on
columns (c1, c2)
in the table named "child". Columns p1
and c1
must be of the same type (INTEGER). Columns p2
and c2
must be of the same type (VARCHAR).
Table columns of all types supported by HSQLDB can be indexed and can feature in comparisons. Types can be explicitly converted using the CONVERT() library function, but in most cases they are converted automatically.
Previous versions of HSQLDB featured poor handling of arithmetic operations.
For example, it was not possible to insert 10/2.5
into any DOUBLE or DECIMAL
column. In 1.7.0, full operations are possible with the following rules:
TINYINT, SMALLINT, INTEGER, BIGINT, NUMBER and DECIMAL (without a decimal point) are supported integral types and map to byte, short, int, long and BigDecimal in Java. The SQL type dictates the maximum and minimum values that can be held in a field of each type. For example the value range for SMALLINT is –128 to +127, although the actual Java type used for handling SMALLINT is java.lang.Integer.
REAL, FLOAT, DOUBLE are all mapped to double in Java.
DECIMAL and NUMERIC are mapped to java.math.BigDecimal
and can
have very large numbers of digits before or after the decimal point.
TINYINT, SMALLINT, INTEGER, BIGINT, NUMBER and DECIMAL (without a decimal point)
are fully interchangeable internally, and no data narrowing takes place. Depending
on the types of the operands, the result of the operations is returned in a
JDBC ResultSet
in any of related Java types: Integer
, Long
or BigDecimal
.
The ResultSet.getXXXX()
methods can be used to retrieve the value
work so long as the returned value can be represented by the resulting type.
If the SELECT statement refers to a simple column or function, then the return type is the type corresponding to the column or the return type of the function. For example:
CREATE TABLE t(a INTEGER, b BIGINT); SELECT a, MAX(b) FROM t;
Would return a result set where the type of the first column is java.lang.Integer
and the second column is java.lang.Long
. However,
SELECT a + 0, MAX(b) + 0 FROM t;
would return java.lang.Long
and BigDecimal
values,
generated as a result of uniform type promotion for all the return values.
There is no built-in limit on the size of intermediate integral values in expressions.
As a result, you should check for the type of the ResultSet
column and choose
an appropriate getXXXX()
method to retrieve it. Alternatively,
you can use the getObject()
method, then cast the result to java.lang.Number
and use the intValue()
or longValue()
methods on the
result.
When the result of an expression is stored in a column of a database table,
it has to fit in the target column, otherwise an error is returned. For example
when 1234567890123456789012 / 12345687901234567890
is evaluated,
the result can be stored in any integral type column, even a SMALLINT column,
as it is a small value.
In SQL statements, numbers with a decimal point are treated as DECIMAL unless
they are written with an exponent. Thus 0.2
is considered a DECIMAL value but
0.2E0
is considered a DOUBLE value.
When PreparedStatement.setDouble()
or setFloat()
is used, the value is treated as a DOUBLE automatically.
When a REAL, FLOAT or DOUBLE (all synonymous) is part of an expression, the type of the result is DOUBLE.
Otherwise, when no DOUBLE value exists, if a DECIMAL or NUMBER value is part
an expression, the type of the result is DECIMAL. The result can be retrieved
from a ResultSet
in the required type so long as it can be represented.
This means DECIMAL values can be converted to DOUBLE unless they are beyond
the Double.MIN_VALUE - Double.MAX_VALUE
range. Similar to integral
values, when the result of an expression is stored in a table column, it has
to fit in the target column, otherwise an error is returned.
The distinction between DOUBLE and DECIMAL is important when a division takes
place. When the terms are DECIMAL, the result is a value with a scale (number
of digits to the right of the decimal pint) equal to the larger of the scales
of the two terms. With a DOUBLE term, the scale will reflect the actual result
of the operation. For example, 10.0/8.0
(DECIMAL) equals 1.2
but 10.0E0/8.0E0
(DOUBLE) equals 1.25
. Without division operations, DECIMAL values represent
exact arithmetic; the resulting scale is the sum of the scales of the two terms
when multiplication is performed.
REAL, FLOAT and DOUBLE values are all stored in the database as java.lang.Double objects. Special values such as NaN and +-Infinity are also stored and supported. These values can be submitted to the database via JDBC PreparedStatement methods and are returned in ResultSet objects.
BIT columns in HSQLDB are in fact boolean columns. BIT columns can be initialised
using values of any numeric type. In this case 0
is translated to false
and any other value is translated to true
. Note that the primary
representation of BIT column is 'true'
or 'false'
either as strings or as the boolean type when used from JDBC.
Each table can contain one auto-increment column, known as the IDENTITY column. An IDENTITY column is always treated as the primary key for the table. Support has been added for CREATE TABLE <tablename>(<colname>
IDENTITY, ...)
to allow IDENTITY used on it's own as a type. This is
translated internally to (<colname> INTEGER IDENTITY, ...)
which is the default syntax.
When you add a new row to such a table using
an INSERT INTO <tablename> ...;
statement, you can use the
NULL value for the IDENTITY column, which results in an auto-generated value
for the column. The IDENTITY()
function returns the last value inserted into any IDENTITY column by this connection. Use CALL IDENTITY();
as an SQL statement to retrieve this value. If you want to use the value for a field in a child table, you can use INSERT INTO <childtable> VALUES (...,IDENTITY(),...);
Both types of call to IDENTITY()
must be made before any additional update or insert statements are issued on the database.
In version 1.7.0 any serializable JAVA Object can be inserted directly into
an OTHER column using any variation of PreparedStatement.setObject()
methods
or its appropriate setXXX()
method. The exception is with String
and byte[]
Objects: setString()
will not act as expected, nor setObject(int
index Object object)
. The setObject(int index, Object object, int
type)
with the type parameter Types.OTHER
should be explicitly
called to insert a String
or byte[]
into an OTHER column.
This is because when the execute
methods of PreparedStatement
are called, everything is passed to the database as an SQL query string. As
a result, the system needs to distinguish between a user supplied string and
a string generated by PreparedStatement
. The string generated by
PreparedStatement
for an Object consists of hex digits only, with
each pair of digits representing a byte of a byte array formed by calling the
serialize()
method of the object. The internal HSQLDB method, org.hsqldb.ByteArray.serializeToString(Object
o)
performs the conversion.
For comparison purposes and in indexes, any two Java Objects are considered equal unless one of them is NULL.
The ResultSet
interface methods, getAsciiStream()
,
getUnicodeStream()
and getCharacterStream()
are now
supported to return byte or char values from CHARACTER columns and its variants.
Complementary methods in PreparedStatemen
t, setAsciiStream()
, setUnicodeStream()
and setCharacterStream()
are also supported. Unlike most other
databases, the getString()
methods can be used to retrieve very
long character strings and is faster than the stream based methods.
Upgrading: Several JDBC methods that previously returned incorrect values now return correct values. Some will throw an SQLException if the relevant connection property, 'strict' has been set to true. All these changes have been documented in the Javadoc for the jdbcXXX classes.
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. With other database engines, connection pools are used for reasons that may not apply to HSQLDB.
(a) To allow new queries to be performed while a time-consuming query is being performed in the background. This is not possible with HSQLDB as it blocks while performing the first query and deals with the next query once it has finished it.
(b) To limit the maximum number of simultaneous connections to the database for performance reasons. With HSQLDB this can be useful only if your application is designed in a way that opens and closes connections for each small task.
(c) To control transactions in a multi-threaded application. This can be useful with HSQLDB as well. For example, in a web application, a transaction may involve some processing between the queries or user action across web pages. A separate connection should be used for each session so that the work can be committed when completed or rolled back otherwise.
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 the application program should be set up so that it always keeps at least one connection to the database open, otherwise the database will be closed and further attempts in creating connections can fail. If connection pooling software is used which cannot be set up to keep the open connection, a 'guardian' connection to the database should be made and kept open before starting the connection pooling.
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. Filure to observe this will result in unsuccessful connection attempts when the application is under heavy load and will slow down all operations even when the load is light.
Memory used by the program can be thought of as two distinct pools: memory used for table data, and memory used for building result sets. In addition, when transactions are used, memory is utilised for storing the information needed for a rollback.
In version 1.7.1, memory use has been drastically reduced compared to previous versions. 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 slots for int or reference variables (10 slots in previous versions). 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 slots for int or reference variables (12 slots in previous versions).
As a result, a table with just one column of type INTEGER will have four objects
per row, with a total of 10 slots 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.
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 3*16000 rows. The hsqldb.cache_scale
database
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 48,000 of the larger rows. If memory is limited, the hsqldb.cache_scale
database
property should be reduced to 13 or less (corresponding to 3*8,000 rows or less).
The memory used for a result set row has fewer overheads (fewer slots and no
index nodes) but still uses a lot of memory. In server mode databases, the result set memory is released
once the database server has returned the result set. In-process
databases release the memory when the application program releases the java.sql.ResultSet
object. Server modes require additional memory for returning result sets, as they convert the full result set into an array of bytes which is then transmitted to the client.
When transactions support is enabled with SET AUTOCOMMIT OFF, lists of all insert, delete or update operations are stored in memory so that they can be undone when ROLLBACK is issued. Transactions that span hundreds of modification to data will take up a lot of memory until the next COMMIT or ROLLBACK clears the list.
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 HSQLDB. For example, with Sun JVM version 1.3.0 the parameter -Xmx256m increases the amount to 256 MB.
HSQLDB supports transactions at the READ_UNCOMMITTED level, also known as level 0 transaction isolation. This means that during the lifetime of a transaction, other connections to the database can see the changes made to the data. Transaction support works well in general. Reported bugs concerning transactions being committed if the database is abruptly closed have been fixed. However, the following issues may be encountered only with multiple connections to a database using transactions:
When an ALTER TABLE .. INSERT COLUMN
or DROP COLUMN
command results in changes
to the table structure, the current session is committed. If an uncommitted
transaction started by another connections has changed the data in the affected
table, it may not be possible to roll it back after the ALTER TABLE
command.
This may also apply to ADD INDEX
or ADD CONSTRAINT
commands. It is recommended
to use these ALTER
commands only when it is known that other connections are
not using transactions.
After a CHECKPOINT command is issued, uncommitted transactions can be continued, committed, or rolled back. However, if the database is not subsequently closed properly with the SHUTDOWN command, any such transaction that still remains uncommitted, is committed at the next startup. It is recommended not to use the CHECKPOINT command when there are any uncommitted transactions.
Databases created with Hypersonic version 1.43 and HSQLDB 1.6x can be seamlessly upgraded to the new version. However, there may be cases where due to data inconsistencies, the upgrade may not be so simple. The following procedure is suggested for making the upgrade in the safest possible way.
Once a database is upgraded, it can no longer be used with Hypersonic or HSQLDB 1.6x.
The new ALTER commands allow you to improve and streamline the design of your database after a successful import.
Version 1.7.0 does not accept duplicate names for indexes.
Version 1.7.0 does not accept duplicate names for table columns.
Version 1.7.0 does not create the same type of index for foreign keys as previous
versions.
These problems can be resolved only by editing the old database script slightly
to conform to the new restrictions. So long as you just rename index or column
names, you can perform this action on the .script
file of the old
database between stages 2 and 3 above in the upgrade process. Use a programming
editor that is capable of handling very large files and does not wrap long lines of text.
In addition to changing the names of columns or indexes in a .script file
mentioned above, the following changes can be applied so long as they do not affect the integrity of existing data.
CREATE UNIQUE INDEX ...
to CREATE INDEX ...
and vice versa
A unique index on columns of a MEMORY tables can always be converted into a normal index so long as it is not referenced by a foreign key from another table or the same table. A non-unique index can only be converted into a unique index if the table data for the column(s) is unique in each row. This conversion is not allowed on CACHED tables.
NOT NULL
A not-null constraint can always be removed. It can only be added if the table data for the column has no null values.
DEFAULT 'defaultvalue'
A default value can always be added, modified or removed altogether. If a default value is removed, future inserts must include a value for the column if there is a NOT NULL
constraint on the same column.
PRIMARY KEY
A primary key constraint can be removed or added only in MEMORY tables (the default type of table). It cannot be removed if there is a foreign key referencing the column(s). It cannot be added or removed at all in CACHED tables.
COLUMN TYPES
Some changes to column types are possible with MEMORY tables only. For example an INTEGER column can be changed to BIGINT.
Any other changes to data structures should be made only through the supported ALTER
commands.
In all versions of HSQLDB and Hypersonic 1.43, the SCRIPT 'filename'
command (used as an SQL query) allows you to save a full record of your database,
including database object definitions and data, to a file of your choice. If
you encounter any problems upgrading your database with the normal method, you
can export a script file using the old version of the database engine and import
it into an empty new database created with 1.7.1. The only potential issue involved
with this is with non-ASCII characters in strings. Please let us know if you
encounter any problems. We will be releasing updated versions of DatabaseManager
and Transfer Tool in the near future to facilitate moving data between old and
new databases.
Each database consists of up to 4 files in the same directory. These should be backed up together. The files can be backed up while the engine is running but care should be taken that a CHECKPOINT or SHUTDOWN operation does not take place during the backup. It is more efficient to perform the backup soon after a CHECKPOINT. If backup takes place while the engine is running, the *.data file can be excluded from the backup (the *.backup file will be used to replace this file if the full backup is restored) . Normal backup methods, such as archiving the files in a compressed bundle can be used.
Author: Fred Toussi - 14 July 2002 - updated 18 Oct 2002
Copyright 2002 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 HSQLDB Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.