Version 1.7.1 improves performance and fixes several bugs that have come to light since the release of 1.7.0. It does not feature any major new functionality in the database engine.

*Over ten-fold speedup of function calls in queries introduced in 1.7.1.

*Up to 30% reduction in memory used by indexes and table rows, resulting in smaller overall memory footprint in 1.7.1.

*Linux scripts for starting and shutting down server instances in 1.7.1.

*Linux build scripts in 1.7.1.

*The Transfer Tool was enhanced for this version by Nicolas Bazin and Bob Preston.

HSQLDB version 1.7.0 CHANGELOG SINCE VERSION 1.60

* Enhancements to SQL

LIMIT and TOP keywords

The LIMIT keyword was introduced in 1.61 with limited functionality. In 1.7.0 the functionality has been fully extended:

SELECT LIMIT <n> <m> DISTINCT is now allowed.

SELECT LIMIT <n> <m> ... is now treated as a complete SELECT statement and can be used anywhere a SELECT is used, so statements such as the following return meaningful results:

SELECT LIMIT <n> <m> ... WHERE <x> IN (SELECT LIMIT <o> <p> ... )

SELECT LIMIT <n> <m> ...  UNION SELECT LIMIT <o> <p>

TOP <m> is synonymous with LIMIT 0 <m>

GROUP BY keyword

GROUP BY had several problems and bugs that have now been resolved. The results are now correct regardless of the order of selected columns or when sorting by multiple columns.

DEFAULT keyword

This keyword has been introduced with full functionality for defining default values for table columns. Special cases 'CURRENT_DATE','CURRENT_TIME','CURRENT_TIMSTAMP', 'TODAY' and 'NOW' can set DATE, TIME and TIMESTAMP columns with current values when an INSERT statement is executed.

SIZE and PRECISION

Maximum column sizes for CHAR, VARCHAR, and other types are now stored in the database. The CHAR and VARCHAR sizes can be enforced when INSERT, UPDATE and SELECT ... INTO table operations are performed. The enforcement is optional and turned off by default.

SAVEPOINT for TRANSACTION

New feature to allow transactions to rollback to a specified point.

CONSTRAINTS and INDEXES

Primary keys declared as CONSTRAINT <name> PRIMARY KEY, either on a single column or on multiple columns, are now accepted and used. All constrains are named, with those generated by the system given unique names as SYS_CT_<n>.

Each INDEX must have a unique name within the database. This conforms to SQL standards.

FOREIGN KEYS, ON DELETE CASCADE

Foreign key constraints referencing the same table are now allowed. New, stricter rules require a unique index to exist on the referenced column (this is the recommended behaviour for new databases but is turned off by default for existing databases). 

ON DELETE CASCADE qualifiers are now supported for foreign keys in data definition scripts. These qualifiers are enforced with fast searches on underlying indexes. 

New support for ALTER TABLE allows 'forward-referencing' foreign keys where the referencing table is created before the referenced table.

TRIGGERS

New multiple-row trigger queues are introduced to improve performance. Enhancements to prevent deadlocks are also added.

TEMP tables

Full support for session-specific temporary tables is introduced. TEMP tables can be used in any statement the same way as ordinary tables, the differences are they are not visible to other session and are dropped when the session is closed. Also, FOREIGN KEY constraints for ordinary tables cannot reference a TEMP table for obvious reasons (TEMP tables can have foreign keys that reference any ordinary or temp table).

TEXT tables

Full support for delimited text files (such as CSV) as the data source for SQL tables.

VIEWS

Views are supported as global objects. Internally, each time the view is used, the underlying select statement is performed. Views can be created and dropped using the following statements.


CREATE VIEW <viewname> AS SELECT ... FROM ... WHERE ....
DROP VIEW <viewname> {IF EXISTS}

ALTER

The following statements are now supported:

ALTER TABLE <name> RENAME TO <newname>

All tables can now be renamed. 

ALTER TABLE <name> ADD COLUMN <column definition> [BEFORE <existing name>]
ALTER TABLE <name> DROP COLUMN <colname>

A column can be added to the column list, so long as its definition does not include PRIMARY KEY or IDENTITY keywords. If a DEFAULT clause is used in the definition, its value is used in all the existing rows. Any column can be dropped unless it is part of the column list for a constraint or index.

ALTER TABLE <name> DROP CONSTRAINT <constname>

Any constraint apart from primary key can be dropped. The index backing the constraint is dropped if not referenced by a foreign key.

ALTER TABLE <name> ADD CONSTRAINT <constname> UNIQUE <column list>

Unique constraints can be added. A new index is created if necessary.

ALTER TABLE <name> ADD CONSTRAINT <constname> FOREIGN KEY <foreign key definition>

Both backward and forward referencing foreign keys are supported.

ALTER INDEX <name> RENAME TO <newname>

Any index can be renamed, so long as it is not a primary key index or an automatically created index on the referencing columns of a foreign key.

DROP INDEX <name>

Any index can be dropped, so long as it is not a primary key index or an automatically created index on the referencing columns of a foreign key or an index backing a constraint.

SQL NAMES

Full support for quoted SQL object names which can include any Unicode character. This applies to TABLE, COLUMN, CONSTRAINT and INDEX names.

* Functions

SET FUNCTION such as COUNT AVG SUM now accept the DISTICT parameter. This feature is
supported only when GROUP BY is not used in the query.

A number of bugs in functions such as ABS, CONCAT and SUBSTR have been fixed.

A small number of new functions have been added.

The SOUNDEX function has been rewritten according to standards.

* Expressions

Arithmetic expressions in SQL statements are now fully supported. Previously, when an arithmetic expression was used to INSERT or UPDATE a value, the first term had to be of the same type as the columns. For example 2*1.2 could not be inserted into a DECIMAL or DOUBLE column. Now all expressions are evaluated and the resulting value can be inserted into a column so long as it can be converted without narrowing.

More lenient treatment of values for TIMESTAMP columns in INSERT and UPDATE now allows a DATE or part TIMESTAMP string to be used instead of the full pattern.

BIT columns can be initialised with numeric values in addition to true and false. Numeric value 0 represents false; all other values represent true.

* Locales

CHAR, VARCHAR and LONGVARCHAR columns can now be sorted according to the current JVM locale. This feature is optional and turned off by default. If turned on, it can slow down database operations, especially where indexes on CHAR columns and its variant are used.

* Error Trapping

Some errors in SQL statements that were not reported before and resulted in potentially incorrect data are now caught.

Dropping tables that are referenced by foreign key constraints from other tables is now disallowed regardless of the system-wide REFERENTIAL_INTEGRITY setting.

Duplicate column names in one table are no longer allowed.

Duplicate index names are no longer allowed.

Using system table names for user tables is disallowed.

INSERT INTO table (a,b,c,...) VALUES (x,y,z,...) statements require the count of names and values to match.

* Enhancements to JDBC

JDBC ResultSet objects are now scrollable and fully support absolute and relative positioning.

Extra getXXX() methods are now supported, including getAsciiStream(), getUnicodeStream(), getCharacterStream().

Optional use of column labels (aliases) as the return value for getColumnName(int i) calls. This is turned off by default.

JDBC PreparedStatement support has been enhanced with support for setting and reading Date, Time and Timestamp values with a given Calendar object. This allows, among others, specifying different time zones for values that are set or read.

Extra setXXX() methods are now supported, including setAsciiStream(), setUnicodeStream(), setCharacterStream().

setObject() methods of PreparedStatement have been improved to allow storage of any serializable object in columns of SQL type, OTHER.

JDBC DatabaseMetaData getImportedKeys(), getExportedKeys(), getCrossReference(), getBestRowIdentifier() are now supported.

* Interoperability Enhancements

HsqlServerFactory allows interoperability with application servers.

An embedded server (an HSQLDB server started inside another application) can be directed to avoid calling System.exit() when the SHUTDOWN command is issued to the database for admin purposes.

javax.jdbc.DataSource interface has been introduced as an experimental feature. In order to include this support, the source code should be compiled with JDK 1.4 or with appropriate J2EE jars present in the class path.

* Performance Enhancements

Very large CHAR, VARCHAR, LONGVARCHAR values are now supported in all types of tables.

Major overhaul of internal operations to drastically reduce creation of temporary objects and type conversion.

Code enhancements to reduce unnecessary method calls in loops.

Enhancements to avoid duplicate indexes (in source code but not activated).

Over ten-fold speedup of function calls in queries intoduced in 1.7.1.

Up to 30% reduction in memory used by indexes and table rows, resulting in smaller overall memory footprint in 1.7.1.

* Reliability Enhancements

Many bugs that existed in 1.43 have been fixed.

Better catching of error conditions and special cases such as null values.

Correct treatment of large binary data.

Better enforcement of internal data consistency in a number of areas.

* Utilities

New Script Tool class allows executing an SQL script.

Enhancements to Transfer Tool to allow the user to save the transfer settings and load them in future sessions. Additional enhancements to support different database source and targets in a modular way.

Swing version of Database Manager.

Linux scripts for starting and shutting down server instances in 1.7.1.

* Build

Better Ant build with automatic detection of JDK version

Linux build scripts in 1.7.1

List of patches and fixes numbered by SourceForge Tracker ID's.

416437 by deforest@users - JDBC DataSource
418017 by deforest@users - made optional
437174 by hjb@users - cache enhancements
442993 by fredt - mixed type arithmetic expressions
448121 by sma@users - large binary values - part implemented
489917 by jytou@users - optional getColumnName(int) return value
450412 by elise@users - jdk 1.1 compatibility
450455 by kibu@users - platform specific line endings in .scrip file
455757 by galena@users (Michiel de Roo)
455785 by hjbusch@users - large DECIMAL inserts
456679 by hiep256@users - TOP keyword
460907 by fredt - soundex
461556 by paul-h@users - compatibility with application servers
471710 by fredt - LIMIT rewritten
473613 by thertz@users - error trapping for joins
475199 by fredt - duplicate column
475586 by wreissen@users - servlet database name
476650 by johnhobs@users - GROUP BY aggregates
476694 by velichko@users - transaction savepoints
478657 by peterhudson@users - improved TRIGGER support
481239 by xponsard@users - enhancement to Transfer Tool
481239 by yfl@users - SQL script execution
485704 by boucherb@users - null binary objects
488200 by xclay@users - throw exception
489184 by xclay@users - thread safety
489777 by fredt - protection of system tables
491987 by jimbag@users - column size enforcement and preservation
495484 by boucherb@users - exception at shutdown
495938 by johnhobs@users - GROUP BY sorting
497714 by lakuhns@users - scrollable JDBC result sets
497872 by Nitin Chauhan - performance enhancements
497872 by Nitin Chauhan - default labels for aggregates
500767 by adjbirch@users - TransferTool enhancements
505356 by daniel_fiser@users - sorting according to Locale
509002 by fredt - correct parsing of primary key constraints
513005 by sqlbob@users - ABS function
513005 by sqlbob@users - HOUR function
513005 by sqlbob@users - TEMP tables
513005 by sqlbob@users - TEXT TABLE support
514111 by fredt - CONCAT function
517028 by peterhudson@users - JDBC setDate(), etc. with Calendar
521078 by boucherb@users - DROP TABLE checks
523880 by leptitpre@users - VIEW SUPPORT
544754 by sqlbob@users - RAWTOHEX and HEXTORAW functions
549741 by velichko@users - RENAME for tables and indexes
550970 by boucherb@users - fewer StringBuffers
552830 by rougier@users - COUNT(DISTICT column)
580430 by thomasm@users - wrong column name in getIndexInfo()

List of enhancements without patch number

1.7.0 by boucherb@users - maintaining constraint names
1.7.0 by boucherb@users - self referenced foreign keys
1.7.0 by boucherb@users - stubs for JDBC3 methods and updated Javadoc
1.7.0 by boucherb@users - Javadoc for a number of classes
1.7.0 by deforest@users - create database directory if not exists
1.7.0 by fredt - full support for quoted identifiers
1.7.0 by fredt - ON DELETE CASCADE qualifier for foreign keys
1.7.0 by fredt - foreign keys without target column list
1.7.0 by fredt - DEFAULT keyword in column definitions
1.7.0 by fredt - GROUP BY with multiple columns in groups
1.7.0 by fredt - JDBC getDate, etc. with Calendar
1.7.0 by fredt - lenient handling of Timestamp inserts
1.7.0 by fredt - JDK 1.1 compatible deque object backed by an array
1.7.0 by fredt - new HsqlProperties class
1.7.0 by fredt - preservation of column size, etc. with SELECT INTO
1.7.0 by fredt - ensuring internal consistency of Result objects
1.7.0 by fredt - resolving some deprecation warnings
1.7.0 by fredt - ALTER TABLE for adding and dropping columns and constraints
1.7.0 by fredt - revamped storage of cached table with very large strings
1.7.0 by fredt - management of server connections with optional no system exit
1.7.0 by fredt - revamped management of server and database properties
1.7.0 by fredt - JDBC getUnicodeStream(), getAsciiStream(), getCharacterStream()
1.7.0 by fredt - JDBC setAsciiStream(), setUnicodeStream(), setCharacterStream()
1.7.0 by fredt - JDBC getCrossReference(), getImportedKeys(), getExportedKeys()
1.7.0 by nbazin@users - enhancements to Transfer Tool
1.7.0 by sqlbob@users - reengineering of Database Manager and Transfer Tool
1.7.0 by sqlbob@users - TEMP, TEXT and CACHED table targets for SELECT INTO
1.7.0 by sqlbob@users - improvements to cache size management
1.7.0 by David Moles - tests for subselect
1.7.0 by jrmaher@users - improvements to Script Tool

also a large number of error-trapping and other fixes not documented