ADVANCED TOPICS

PROPERTIES FILES

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:

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:

server.database 

test 

server.silent 

true 

server.trace 

false 

Values specific to server.properties are:

server.port 

9001

server.no_system_exit 

false

Values specific to webserver.properties are:

server.port 

80 

server.default_page 

index.html 

server.root 

./ 

.<extension> 

? 

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.

Individual Database Properties

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:

sql.month

true

sql.enforce_size 

false

sql.compare_in_locale 

false

sql.strict_fk

true (false for existing db's)

sql.strong_fk

true

hsqldb.cache_scale

14

hsqldb.log_size

200

hsqldb.gc_interval

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

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.get_column_name 

true

CONSTRAINTS AND INDEXES

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.

FOREIGN KEYS

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).

TYPES AND ARITHMETIC OPERATIONS

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.

INTEGRAL TYPES

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.

OTHER NUMERIC TYPES

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 TYPE

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.

IDENTITY AUTO-INCREMENT TYPE

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.

HANDLING OF JAVA OBJECTS

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.

JDBC STREAM BASED METHODS

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 PreparedStatement, 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.

MANAGING DATABASE CONNECTIONS

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 AND DISK USE

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.

ISSUES WITH TRANSACTIONS

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.

UPGRADING DATABASES

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.

1. Make a backup of your database.
2. Open the database using the old version of the Database Manager software. Use the SHUTDOWN COMPACT command.
3. Open with the old version Database Manager again and check the data is consistent, then close it.
4. Open the database with the new version 1.7.1 of Database Manager. Check the data is consistent.
5. Issue the SHUTDOWN COMPACT command.
6. Start using the database.

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.

Some Potential Problems

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.

Manual Changes to the .script File

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.

The SCRIPT command

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.

Backing Up 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.

http://hsqldb.sourceforge.net