Chronological Change List

This file is the accumulated list of changes in the course of development of
hsqldb 1.8.0, including point releases, in reverse chronological order.

Fred Toussi (fredt@users.sourceforge.net)
Maintainer, HSQLDB Project

2008.06.02  1.8.0.10

Fixed deadlock in shutdown of Server
Fixed bug that in some circumstances could result in corrupted .data file and
cause Null Pointer or other exceptions.
New database property hsqldb.lock_file, true by default. When false, no lock
file is created when the database is opened.
Compile with JDK 6 is possible
New implementation of lock file
Fixed issue with org.hsqldb.Servlet
Fixed issue with scripting of views
Fixed issue with HsqlTimer
Minor improvement to WebServer
Minor corrections to utilities
Correction to build.xml to allow hsqldbmin target

2007.10.22  1.8.0.9

Internal changes to classloader for FileAccess classes for next OOo release.
new optional Java system property hsqldb.method_class_names allows preventing
access to classe other than org.hsqldb.Library and java.lang.Math or specifying
a semicolumn-separated list of allowed classes. For example,
java -Dhsqldb.method_class_names="org.me.MyClass;org.you.YourClass" 
allows access to methods in the two specified classes.

2007.09.06  1.8.0.8.1

Fixed regression that impacted performance of system table selects and JDBC 
DatabaseMetaData calls that use system tables.

2007.08.28  1.8.0.8

Fixed issue with some text table sources.
Fixed issue caused after adding columns to tables used in certain view 
definitions
Added sych call before closing .script files.
Minor bug fixes

New implementation of HsqlTimer to allow proper database shutdown while used 
within an application server or similar.

Improvements in TEXT TABLE source file handling. If source file is unavailable 
at
startup, the source specification is retained but its usage is turned off.
An SQL warning is added to the first session in the event.
Added SET TABLE <tablename> SOURCE {ON | OFF} enables/disables the usage of the
source file.

Some relaxation of use of LIMIT in subqueries

Improvements to SqlTool and DatabaseManager tools by Blaine Simpson.

2006.09.27  1.8.0.7

Fixed regression in 1.8.0.6 that prevented reuse of empty spaces in the .data 
file.
Fixed regression that prevented setting the textdb.allow_full_path property

2006.09.22  1.8.0.6

Fixed issue where missing dbname.0 in server properties files was not ignored
Fixed issue where a crash during logging of an update query may result in
incomplete logging and recovery
Fixed issue where a PK defined after another constraint would result in wrong
constraint name
Fixed issue with incorrect evaluation of null values in CASE WHEN in certain 
cases
Fixed issue where ResultSet column class names were misreported for functions
returning primitive types

2006.07.27 1.8.0.5

(3 fixed issues affecting durability)
Fixed serious reported issue in some JVM's zip libraries by adding a workaround.
Fixed serious issue when cache_scale and cache_size_scale are small but very 
large rows are inserted in cached tables.
Fixed bug where an automatic rollback of an UPDATE statement was not logged.

Fixed bug with nested JDBC escapes.
Fixed bug with logging of schema definition.
Fixed bug where DROP TABLE was allowed even when a view used the table.
Fixed bug where DISTINCT together with some GROUP BY lists were not allowed.
Fixed bug where Server.shutdown() threw spurious NPE.

Improved .data file persistence classes with better errror logging.
 
Improved client/server retrieval speed of large result sets.
Column declarations of TIME(0) are now accepted.
Named parameter in the form of :name are allowed in prepared statements.

2006.04.16 1.8.0.4

General improvements to all utilities, including SQLTool, DatabaseManager, etc.
General improvements to build.xml.
Improvement to fail-safe persistence by saving the .properties file first as a 
new file, before renaming.
Fixed bugs with text tables that use quoted source files and have a heading row.
Fixed bug which prevented dropping a column before an indexed column.
Fixed bug where DISTINCT was not allowed with SQL functions (BT 1400344)
Fixed bug where PreparedStatement calls were allowed with some unbound variable
(Bug Tracker 1399324) but commented out to allow compatibility with earlier 
versions (see org.hsqldb.jdbc.jdbcPreparedStatement).
VARCHAR_IGNORECASE size specification is now enforeced.
Fixed bugs where ORDER BY did not work in some cases (BT 1383218, BT 1344895).
Fixed bug where IFNULL was used with a function (BT 1315834).
Fixed bug where creation of a new table was not allowed if the old table with
the same name had been renamed.
Fixed bug where SYSTEM_INDEXINFO could not be read.
Fixed bug to allow running with JDK 1.1 when compiled for this target.
Fixed bug with COALESCE, et containing an aggregate function (BT 1242448).

Fixed bug with ResultSet.getTimestamp(int, Clandar).
Fixed bug with access non-admin access to tables was allowed when using 
PreparedStatement objects.
Added org.hsqldb.Library function, getDatabaseFullProductVersion to return the 
full version string, including the 4th digit (e.g 1.8.0.4).

2006.01.08

Fixed bug in WebServer
Added support for remote startup of databases in Server and WebServer

2005.12.22

Fixed bug where ON DELETE SET DEFAULT was accepted without a matching DEFAULT 
value
Fixed bug where column renaming was allowed in tables accessed in views
Fixed bug where TRUE or FALSE is used as search condition as WHERE .. AND FALSE
Fixed bug where dropping TEMPORARY tables was not logged
Fixed bug where negative values for IDENTITY columns were treated as positive


2005.11.02 1.8.0.3

Bug fixes

Fixed bug with tables formed with SELECT .. INTO that did not allow creation of 
indexes
Fixed bug with scripting of some VIEW definitions with quoted column names
Fixed bug in generation of some system tables with multi schema databases
Fixed bug with access to network result set data when UNION with different 
column
types is used
Fixed bug with scripting of some TIMESTAMP values

2005.08.18 1.8.0.2

Bug fixes and enhancements:

Fixed bugs with GLOBAL TEMPORARY tables with PRESERVE ROWS or 
DELETE ROWS options.
Fixed bug with DESC ignored in ORDER BY when a correlation name was used to 
specify the column.
Fixed ResultSet.getTimestamp() and PreparedStatement.setTimestam() bug with null
timestamp parameters.
Enhancement to allow string contatenation in LIKE predicates.
Fixed two JDK 1.1.x incompatibilities.
Fixed issue with some SET TABLE <name> SOURCE <source> command.
Fixed issue with DDL not being logged immediatedly with WRITE_DELAY set to 0.
Fixed issue with the combination of CASE WHEN (or COALESCE, etc.) with
aggregate functions when the table contains no rows and the aggregate function
returns null.
Fixed issue with ALTER SCHEMA <name> RENAME TO <name>

New features

Support for RIGHT OUTER JOIN and CROSS JOIN in select statements 

ALTER TABLE <name> ALTER COLUMN <name> SET [NOT] NULL

Added DAY_OF_WEEK as an alternative for EXTRACT(XXX FROM <date time>)
 

2005.07.11 1.8.0.1

Bug fixes and enhancements:

Correlated subqueries with UNION (and other set operators) are allowed.
LEFT OUTER JOIN join statement can contain NOT expressions.
Set functions, SOME() and EVERY() implementation corrected.
Quoted empty strings in TEXT table sources are no-longer interpreted as null.
CHECKPOINT DEFRAG issued while there are uncommitted transactions works 
correctly.
build.xml updated to fix issue with JDK 1.3 build  

2005.07.01 1.8.0.0

First release.

2005.06.16 1.8.0.RC12

Introduced new connection property shutdown, which, if true on the first 
connection, will cause the engine to perform a SHUTDOWN on the database when
the last connection is closed.

Enhancements to TEXT table implementation to maintain the commit status of rows 
during recovery from an abrupt shutdown. Uncommitted rows will not appear in
TEXT tables.

2005.05.17 1.8.0.RC10

SCHEMAS

Support for SQL schemas. Each database can contain multiple schemas. The 
following commands have been introduced:

CREATE SCHMEA <schemaname> AUTHORIZATION DBA
DROP SCHEMA <schemaname> {CASCADE | RESTRICT}
ALTER SCHEMA <schemaname> RENAME TO <newname>
SET SCHEMA <schemaname>

Initially, the default user schema will be created with the name PUBLIC. This 
schema can be renamed or dropped. When the last user schema has been dropped, 
an empty default schema with the name PUBLIC is created.

System tables all belong to INFORMATION_SCHEMA. To access system tables, either
SET SCHEMA INFORMATION_SCHEMA should be used once or they should be referred to
by fully specified names, e.g. 
INFORMATION_SCHEMA.SYSTEM_TABLES

Similarly all database objects apart from columns can be referenced with fully
qualified schema names.

The CREATE SCHEMA command can be followed by other CREATE and GRANT commands 
without an intervening semicolon. All such commands are executed in the context
of the newly created schema. A semicolon terminates an extended CREATE SCHEMA
command.

ROLES

Support for SQL standard roles.

CREATE ROLE <role name>
GRANT ... TO <role name>
REVOKE ... FROM <role name>
GRANT <role name> TO <user name>
DROP ROLE <role name>

The GRANT and REVOKE commands are similar to those used for granting 
permissions on different objects to USER objects. A role can then be granted to
or revoked from different users, simplifying permission management.

GLOBAL TEMPORARY TABLES

The implementation of temporary tables has changed to conform to SQL standards.

The definition of a GLOBAL TEMPORARY table persists with the database. When a 
session (JDBC Connection) is started, an empty instance of the table is created.
Temporary tables can be created with (the default) ON COMMIT DELETE ROWS or 
ON COMMIT PRESERVE ROWS added to the end of table definition. 
With ON COMMIT PRESERVE ROWS, the table contents are not cleared when the 
session commits. In both cases, the contents are cleared when the session is
closed.


SCHEMA MANIPULATION COMMANDS

Several schema manipulation commands have been enhanced. 

Tables, views and sequences can be dropped with the CASCADE option. This 
silently drops all tables and views that reference the given database object.

DROP TABLE <table name> [IF EXISTS] [CASCADE];
DROP VIEW <view name> [IF EXISTS] [CASCADE];
DROP SEQUENCE <sequence name> [IF EXISTS] [CASCADE];

ALTER TABLE <table name> DROP COLUMN now silently drops any primary key or 
unique constraint declared on the column (excluding multi-column constraints).

ALTER TABLE <table name> ADD [COLUMN] now accepts primary key and identity 
attributes.

COLUMN MANIPULATION

Support for converting type, nullability and identity attributes of a column

ALTER TABLE <table name> ALTER COLUMN <column name> <column definition>

<column definition> has the same syntax as normal column definition. The new 
column definition replaces the old one, so it is possible to add/remove a 
DEFAULT expression, a NOT NULL constraint, or an IDENTITY definition. 
No change to the primary key is allowed with this command. 

- The column must be already be a PK column to accept an IDENTITY definition.
- If the column is already an IDENTITY column and there is no IDENTITY 
definition, the existing IDENTITY attribute is removed.
- The default expression will be that of the new definition, meaning an 
existing default can be dropped by ommission, or a new default added.
- The NOT NULL attribute will be that of the new definition, similar to above.
- Depending on the conversion type, the table may have to be empty for the
command to work. It always works when the conversion is possible in general and
the individual existing values can all be converted.

A different syntax can be used to change the next value for an IDENTITY column:

ALTER TABLE <table name> ALTER [COLUMN] <column name> RESTART WITH <n>


ALL and ANY expressions

Full support for ALL(SELECT ....) and ANY(SELECT ....) with comparison o
perators: =, >, <, <>, >=, <=
Example:

SELECT ... WHERE <value expression> >= ALL(SELECT ...)

LIMIT and OFFSET

New alternative syntax for LIMIT at the end of the query:

LIMIT L [OFFSET O]

It is now possible to use LIMIT combined with ORDER BY in subqueries and SELECT
statements in brackets that are terms of UNION or other set operations.

An ORDER BY or LIMIT clause applies to the complete result of the UNION and 
other set operations or alternatively to one of its components depending on how 
parentheses are used. In the first example the scope is the second SELECT, 
while in the second query, the scope is the result of the UNION.

SELECT ... FROM ... UNION 
(SELECT ... FROM ... ORDER BY .. LIMIT)

SELECT ... FROM ... UNION 
SELECT ... FROM ... ORDER BY .. LIMIT

Support for ORDER BY, LIMIT and OFFSET in CREATE VIEW statements


COLLATIONS

Added support for collations. Each database can have its own collation. The SQL 
command below sets the collation from the set of collations in the source 
for org.hsqldb.Collation:

SET DATABASE COLLATION <double quoted collation name>

Once this command has been issued, the database can be opened in any JVM and 
will retain its collation.

The property sql.compare_in_locale=true is no longer supported. If the line 
exists in a .properties file, it will switch the database to the collation for 
the current default.  

ENHANCEMENTS

Support for the res: connection protocol (database files in a jar) has been
extended to allow CACHED tables.

Support for correct casting of TIME into TIMESTAMP, using CURRENT_DATE 

Symmetrical handling of setTimestamp() and getTimestamp() with Calendar
parameters.

BUG FIX

Fixed bug where two indexes where switched, causing wrong results in some 
queries in the following circumstances:

CREATE TABLE is executed.
ALTER TABLE ADD FORIEGN KEY is used to create an FK on a different table that 
was already present when the first command was issued.
CREATE INDEX is used to add an index.
Data is added to the table.
Database is shutdown.
Database is restarted.
At this point the indexes are switched and queries that use either of the 
indexes will not return the correct set of rows.

BUG FIXES

Fixed reported bug with NOT LIKE and null values

Fixed bug with OR conditions in OUTER JOIN

Fixed bug with duplicated closing of prepared statements

Fixed bug with scalar subqueries. If the result of a scalar subquery is empty 
(no rows returned), the value is NULL.

Fixed various parsing anomalies where SQL commands were accepted when quoted, 
double-quoted or prefixed with an identifier, or identifiers were accepted in 
single quotes. Example of a command that is no-longer tolerated: 

Malformed query: MY. "SELECT" ID FROM 'CUSTOMER' IF.WHERE ID=0;   
Actual query: SELECT ID FROM CUSTOMER WHERE ID=0;

Fixed bug that prevented adding a primary key with no name to a table.

Fixed bug that resulted in an error when the type of a column was changed to 
the same type as before. E.g. 
ALTER TABLE <table name> ALTER COLUMN <column name> INTEGER

BUG FIX

Fixed bug in ROLLBACK. If several updates were made to a row of a table that 
has no primary key, then ROLLBACK was issued, there would remain multiple 
versions of the row. E.g.

CREATE TABLE T (I INT);
INSERT INTO T VALUES (0);
SET AUTOCOMMIT FALSE;
UPDATE T SET I=1 WHERE I = 0;
UPDATE T SET I=2 WHERE I = 1;
UPDATE T SET I=3 WHERE I = 2;
UPDATE T SET I=4 WHERE I = 3;
ROLLBACK

After the rollback the table would contain more than one row.


2005.03.02 1.8.0.RC9

Fixed unreported bug in recovery when first column contains null.
Fixed unreported issue with possible PK and FK name duplication
Fixed reported bug in recovery when there is no primary key.
Fixed reported issue with deleting self referencing row (with foreign key).
Fixed reported issue with illegal user names
Fixed jdk 1.3 compilation issues
Applied submitted patch to Server

Applied submitted patch for setting the default table type when CREATE TABLE is
used. The connection property, hsqldb.default_table_type=cached will set the
default to CACHED tables, or the SET PROPERTY command can be used. Values,
"cached" and "memory" are allowed.
 
The database property sql.enforce_strict_size=true has now a wider effect
Previously CHAR /VARCHAR lengths could be checked and padding performed only 
when inserting / updating rows. Added support for CHAR(n), VARCHAR(n), 
NUMERIC(p,s) and DECIMAL(p,s) including SQL standard cast and convert 
semantics.  
Explicit CAST(c AS VARCHAR(2)) will always truncate the string. Explicit 
CAST(n AS NUMERIC(p,s)) will always perform the conversion or throw if n is out
of bounds. All other implicit and explicit conversions to CHAR(n) and 
VARCHAR(n) 
are subject to SQL standard rules.
 
Bob has improved support for text tables. Newline support in quoted fields is
now complete. It is now possible to save and restore the first line header of a
CSV file when ignore_first=true is specified. When a text table is created with 
a new source (CSV) file, and ignore_first=true has been specified the following
command can be used to set a user defined string as the first line:
SET TABLE <tablename> SOURCE HEADER <double quoted string>.

When embedded in OOo, several defaults and properties are set automatically:

CREATE TABLE ... defaults to CREATE CACHED TABLE ...
hsqldb.cache_scale=13
hsqldb.cache_size_scale=8
hsqldb.log_size=10
SET WRITE DELAY 2
sql.enforce_strict_size=true
hsqldb.first_identity=1


2005.02.11 1.8.0.RC8

Fixed minor bugs

Added public shutdown() method to Server.

A new application log has been introduced as an optional feature. The
property/value pair "hsqldb.applog=1" can be used in the first connection 
string 
to log some important messages. The default is "hsqldb.applog=0", meaning no
logging. A file with the ending ".app.log" is generated alongside the rest of
the database files for this purpose. 

In the current version only the classes used for file persistence (different in
OpenOffice.org), plus any error encountered while processing the .log file after
an abnormal end is logged.

Bob Preston has updated the text table support to accept new line characters
inside quoted strings. It is still not possible to create / modify a row 
containing a new line inside the engine. This will be a simple upgrade and will
be included in the next RC.

Note that the JDBC driver and the engine for 1.8.0 cannot be mixed with those of
earlier versions in client/server setup. Check your classpaths and use the same
version of the engine for both client and server.


2005.01.28 1.8.0.RC7

Fixed minor bugs

2005.01.24 1.8.0.RC5

Issues with SET CHECKPOINT DEFRAG fixed

New property for larger data file limits is introduced. Once set, the limit will
go up to 8GB. The property can be set with the following SQL command only when 
the database has no tables (new database).

SET PROPERTY "hsqldb.cache_file_scale" 8

To apply the change to an existing database, SHUTDOWN SCRIPT should be 
performed 
first, then the property=value line below should be added to the .properties
file before reopening the database:

hsqldb.cache_file_scale=8

It is now possible to add or drop a primary key.

An existing primary key that is to be removed should not be referenced in a
FOREIGN KEY constraint. If a table has an IDENTITY column, removing a primary 
key will remove the identity attribute of the column but leave the actual data.

When adding a primary key, a NOT NULL constraint is automatically added to the 
column definitions. The table data for the columns of a newly declared primary
key should not contain null values. 

ALTER TABLE <name> ADD CONSTRAINT <cname> PRIMARY KEY(collist);
ALTER TABLE <name> DROP CONSTRAINT <cname>;
ALTER TABLE <name> DROP PRIMARY KEY; // alternative syntax

2005.01.20 1.8.0.RC4

More minor bug fixes
ResultSetMetaData reports identical precision/scale in embedded and 
client/server modes

2005.01.16 1.8.0.RC3

Regression bug fixes.
New property allows a CHECKPOINT DEFRAG to be performed automatically whenever
CHECKPOINT is performed internally or via a user command.

SET CHECKPOINT DEFRAG n

The parameter n is the megabytes of abandoned space in the .data file. When
a CHECKPOINT is performed either as a result of the .log file reaching the 
limit set by "SET LOGSIZE m", or by the user issuing a CHECKPOINT command, the
amount of space abandoned during the session is checked and if it is larger 
than n, a CHECKPOINT DEFRAG is performed instead of a checkpoint.

2005.01.14 1.8.0.RC2

Regression bug fixes.


2005.01.10 1.8.0.RC1

Allows embedding into OpenOffice.org
Rewrite of log and cache handling classes, including:
New deleted block manager with more effective deleted block reuse.
Faster log processing after an abnormal termination.
Better checks when maximum data file size is reached.
Better recovery when maximum data file size is reached.

Changes to row access to use iterators. More code clarity.

Various refactorings and new package for improved modularity. 

Enhancements to DatabaseManagerSwing

In all other respects, 1.8.0.RC1 is identical to 1.7.3.1


================================================================================

Chronological Change List

This file is the accumulated list of changes in the course of development of
hsqldb 1.7.3 in reverse chronological order.

Fred Toussi (fredt@users.sourceforge.net)

2004.02.23 1.7.3.4

Fixed NPE when defining self referencing FK's with no primary key

2004.02.07 1.7.3.3

Fixed bug with loggin of deletes / updates from tables without primary keys. If 
the a row
from such tables contained a null value and was deleted / updated, the 
operation was not 
logged correctly. Subsequently, if there was an abnormal termination after such 
changes and
the database was restarted, the logged delete statement would not have any 
effect and result
in duplicate rows. This bug did not affect tables with primary keys or the 
normal operation
(without abnormal termination) of other tables.


2004.01.31 1.7.3.2

Allow conversion from BIGINT to SAMMLINT and TYNYINT (all other legal 
conversions worked fine)
Fixed issue with null values in calls to ResultSet methods with a Calendar
argument.
Enhancement to allow INSERT INTO <T> SELECT (....) where no column list
list for the table T is specified.

2004.12.24 1.7.3.1

Corrected scripting of CHECK constraints with NOT NULL and IS NULL
Fixed NPE error when functions in subqueries.
Added support for STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, EVERY and SOME 
set functions.
Fixed potential issues with logging DELETE statements for tables with multi-key 
primary keys.

Apart from the BOOLEAN type, 1.7.3.1 is identical to 1.7.2.11

2004.12.05 First release 1.7.3.0

Changed handling of the BOOLEAN type to allow the undefined state.
This involves changes to the behariour of NOT and NULL, and also CHECK 
conditions,
regarding NULL values.

In all other respects, 1.7.3.0 is identical to 1.7.2.10

================================================================================

Chronological Change List

This file is the accumulated list of changes in the course of development of
hsqldb 1.7.2 in reverse chronological order. A list of changes by subject 
area is in changelog_1_7_2.txt.

Fred Toussi (fredt@users.sourceforge.net)

2004.12.24 UPDATED RELEASE 1.7.2.11

Fixed NPE error when functions in subqueries.
Added support for STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, EVERY and SOME 
set functions.
Fixed potential issues with logging DELETE statements for tables with multi-key 
primary keys.

2004.12.05 UPDATED RELEASE 1.7.2.10

Fixed minor type conversion bugs.
Fixed JDK 1.1 compile issue
Fixed minor bug relating to server thread startup

2004.11.25 UPDATED RELEASE 1.7.2.8

Fixed bug LIMIT and TOP with UNION
Fixed bug running Database Manager as applet
Enhancement to allow a table with a label used in DELETE and UPDATE
Enhancement to reduce memory use when restoring long log with too many 
connections
Enhancement to persist values set by SET LOGSIZE
Minor internal enhancements and refactoring

2004.10.28 UPDATED RELEASE 1.7.2.7

Fixed new bug with TEXT TABLES without primary key

2004.10.25 UPDATED RELEASE 1.7.2.6

Fixed new bug with DATETIME literal default values

2004.10.22 UPDATED RELEASE 1.7.2.5

Fixed bugs OUTER support.
Fixed bug that resulted in slowdown when JDBC metadata calls are called many 
times.
Fixed bug related to Statement.setMaxRows(int). The maximum row count will no
longer apply to result sets returned and used internally.
Fixed bug to allow ResultSetMetaData methods to be used inside stored 
procedures.
Fixed bugs related to some OUTER queries.
Performance improvements to some operations on CACHED tables.
Improved compatibility with older database files, allowing old style DEFAULT
clauses to compile.
Improvements to startup after a machine crash. The .log file is now read only
until the first malformed line.
Corrections to build scripts and #idfef blocks to allow jars to be compiled to
run under JDK 1.1.
 

2004.08.26 UPDATED RELEASE 1.7.2.4

Fixed bug in data file enlargement.
Fixed bug where temp tables were removed after another connection closed.

2004.08.12 UPDATED RELEASE 1.7.2.3

Fixed bug preventing the use of hsqldb.files_read_only property
Minor enhancements allowing use of SELECT ALL and COUNT(ALL colname) etc.
Corrections to the documentation

2004.07.25 UPDATED RELEASE 1.7.2.2

Fixed bug that prevented ALTER TABLE ADD FOREIGH KEY, ADD UNIQUE and ADD CHECK
(unnamed constraints) from executing.

Fixed excessive logging of SEQUENCE values.


2004.07.20 UPDATED RELEASE 1.7.2.1

Fixed bug where a foreign key with ON DELETE CASCADE action on a table masked
other foreign keys on the table and resulted in completion of the 
cascading action when referential integrity was violated.

2004.07.15 RC_6e INITIAL RELEASE 1.7.2

Enhancements to ALTER TABLE ADD COLUMN and DROP COLUMN.
Fixed bug with closed HTTP connections.
Fixed bug with large .data files switching to random access from nio. 

2004.06.28 RC_6d

The implemntation of UNION, EXCEPT and INTERSECT set operations has been 
rewritten to comply with SQL standards.
When multiple set operations are present in a query, they are now evaluated
from left to right, with INTERSECT taking precedence over the rest.
It is now possible to use parentheses around multiple (or single) select
statements with set operations to change the order of evalation.E.g.:

((SELECT * FROM A EXCEPT SELECT * FROM B) INTERSECT SELECT * FROM D UNION 
SELECT * FROM E)

Timestamp, date and time values in TEXT TABLE source files are treated more
leniently, allowing spaces before values.   

2004.06.19 RC_6c 

Fixed bug in default value definition.
Fixed bug in getURL() for internal connections
Fixed bug in evaluation of BOOLEAN values
Fixed bug in Statement.executeBatch()
Improvements in CHECKPIONT DEFRAG
Improvements in nio .data file handling
Added a procedural language to SqlTool.  Made error handling and control more
robust.  Implemented several new features.
Updated documentation

2004.05.31 RC_6b

Fixed CHECKPOINT DEFRAG bug with empty tables
Fixed ORDER BY with HAVING
Fixed SELECT .. INTO table issue with outer joins etc.
Fixed issue with NOT used in WHERE clause of OUTER select
Fixed issue with DAYOFWEEK() etc. library calls with NULL parameter
Fixes for TRIGGER thread termination
Fixes for minor bugs
Updated documentation

2004.05.18 RC_6a

Fix for two bugs, some doc updates, etc.

2004.05.17 RC_6
Final Release Candidate

This is the final release candidate for 1.7.2.

More tests are required to verify the improvements. Please perform tests and 
report any issues. Testers are needed for build scripts with different JVM's 
using both ANT and batch builds.

Documentation has been updated and is available in HTML and PDF formats.

More bug fixes to engine and JDBC classes.

Bug fixes and minor enhancements to Database Manager and Transfer tools.

Database Manager Swing table view is sortable.

Fixed bug with different values in one row with multiple CURRENT_TIMESTAMP or
NOW() calls.

Introduction of CURRENT_USER, can be used as column default value as well.

CURRENT_TIMESTAMP, NOW, TODAY etc. should not be quoted when used as column
default values.

Improvements to PreparedStatement set stream methods.

Improvements to Clob and Blob implementations.

Much of the UNIX init script redesigned.  Now supports TLS and WebServer.

2004.05.01

Small improvements in different areas

CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP and their aliases can now
be used in views and prepared statements.

Some bug fixes and updates to utility programs. 

Improvements to cascading update operations, allowing full capability with
FK's referencing the same table.
Improved parametric INSERT .... SELECT.
CHECKPOINT DEFRAG now works with nio mode .data files.
DATEDIFF function has been added.


2004.04.01 RC_5

Some bug fixes.

2004.03.22 RC_4

Included hsqldb.jar has been compiled with JDK 1.4 and will not work with JRE 
1.3.x or
earlier. Please recompile with JDK 1.3 for use with this or earlier JDK's.

Improvements to ALTER TABLE when VIEWS reference the table
Enhancements to Server

Bug fixes for: 
BIGINT values in IN predicate value lists.
Creation of self ref foreign keys.
Corrupted log file with SET AUTOCOMMIT
SET LOGSIZE
 

Bug fixes for LIKE with default local and ALTER TABLE with CHECK

2004.03.13 RC_3

Various bug fixes.


2004.02.22 RC_2

Release Candidate 2 for HSQLDB version 1.7.2 includes almost all of the 
features of the release
version. It is possible that by the time of the release, a small number of 
minor features will
be added or changed.

One area that is likely to change is the SQL_STATE values for errors returned by
JDBC.

Changes since RC_1

Constraint names must be unique within each database.

Refactoring groups of classes into new packages. This change is internal and 
does not affect the
use of hsqldb.

New SqlTool and SqlFile utilities by Blaine Simpson. See hsqlSqlTool.html for 
details.

Support for new system tables for constraints, views and sequences.

Support for increments in GENERATED BY DEFAULT AS IDENTITY(START WITH <n>, 
INCREMENT BY <m>>)

Support for table aliases in UPDATE

BOOLEAN is now the default SQL type for boolean values. BIT is treated as an 
alias
for BOOLEAN in table definition and queries.

Library function CURTIME() changed to return CURRENT_TIME instead of 
CURRENT_TIMESTAMP

Added logging for sequence generation

Bug Fixes since RC_1

Values for BOOLEAN expressions containing AND, OR, LIKE and used in columns
returned by queries are now correct.

Fixed issue with single value selects in UPDATE and SELECT statements.

Fixed issue with Turkish Locale.

Fixed issue with compiled statements.

Added mutable object cloning and serialization on PrepredStatement parameter 
entry points
for in-process connections. Isolates the stored objects (DATETIME, BINARY and 
OTHER) from
subsequent changes made to the copy used for setting the parameter. To do same 
for Result.

More extensive error message when internal asserts fail.

Various other bug fixes.


2003.12.28 RC_1

Release Candidate 1 for HSQLDB version 1.7.2 includes most of the features of 
the release
version. It is likely that by the time of the release, a small number of 
features will
be added to the engine.

One area that is likely to change is the SQL_STATE values for errors returned by
JDBC.

Changes since ALPHA_T


Added referential integrity checks when a foreign key is added
to a populated table.

support for SQL style SEQUENCE objects.

support for DROP INDEX <IDX> IF EXISTS

Changes to SET PROPERTY command with better error checking.

Added support for LIMIT ? ? and TOP ? (prepared statement)

Fixed reported bugs including order by with UNION.

Added support for aggregate functions in CASEWHEN, IFNULL, etc.

Added support for aggregate functions as arguments to non-aggregate functions.

Views and subqueries can appear in a view definition.


2003.10.26

HSQLDB 1.7.2 ALPHA_T

Improved index use for multiple key joins.

2003.10.24

Fixed some reported and unreported bugs.

View definitions can now have a column name list:

CREATE VIEW <viewname> [(<colnmae>, ...)] AS SELECT ....


2003.10.21

HSQLDB 1.7.2 ALPHA_S

Fixed name resolution issues in some IN (SELECT ...) and EXISTS (SELECT ...)
queries. Optimisation of these queries when not correlated.

Speed optimisation of joins with views and subqueries.

Fixed LIKE parameter bug.

Support for CHECK constraints that apply to the modified/inserted row only.
This can be added via ALTER TABLE ADD CHECK() or in table definition.

Fixed the DATETIME normalisation issues.
Fixed ResultSet.isAfterLast() issue.

Reverted column name case in ResultSet methods to JDBC compliance.


2003.10.12

HSQLDB 1.7.2 ALPHA_R

This release features full support for prepared statements with parameters, 
including
those in IN lists.

Query processing has been extensively enhanced in this release and better 
standards
compliance has been achieved. Major implications of the recent changes are as 
follows:

- Columns in correlated subqueries are resolved independently first. If there 
is an
unresolved column, then the surrounding context is used to resolve it. This is 
opposite the resolution order previously enforced in the engine.

- Some ambiguities and errors in ORDER BY clauses are now caught.

- UNION and other set queries accept only one ORDER BY clause at the end. In 
this
clause, only column indexes are allowed as sort specification e.g.:

SELECT .. UNION SELECT .. ORDER BY 1 DESC, 5, 3

- Outer join conditions can now include most comparison operators, as well as OR
logical operators , e.g

LEFT OUTER JOIN atable ON a=b AND c>d OR a=2 ...

Illegal forward table references are no longer allowed in join conditions.

There are many other small improvements and fixes, including:

- IN value lists can now contain column values or expressions. See 
TestSelfQueries.txt
for an example.

- LIKE has been optimised when possible.

- COALESCE() can now take several arguments


2003.09.24

HSQLDB 1.7.2 ALPHA_Q

The problem with PreparedStatement parameters in IN lists still remains to be 
fixed.

This release includes some bug fixes and improvements. In the /doc directory
there is a new how-to document on using HSQLDB in OpenOffice.org.


HSQLDB 1.7.2 ALPHA_P

Apart from the bugs and limitations of PreparedStatement, all the bugs reported 
since
the release of ALPHA_O have been fixed in this alpha version. Some minor 
enhancements
have been made to increase compatibility with OpenOffice.org applications.

In addition, it is now possible to execute triggers in the main execution 
thread.
This allows uses for triggers that were not possible before.


HSQLDB 1.7.2 ALPHA_O

Due to the large number of small bug fixes since the release of ALPHA_N,
a new ALPHA_0 version is now released. 

Please see the bug tracker for a list of some of the bugs that have been fixed.
This version does not yet support prepared statements with IN(?,?) parameters.

Please report any bugs or suggestions through the normal channels. Bugs with 
test
cases should be posted to the bug tracker on the HSQLDB Sourceforge site.

A new JDBC connection property "toupper_column_name" can be used when the 
application
program submits lowercase / mixedcase column names to Result.getXXX(String 
column)
methods when the names are in fact UPPERCASE as they were not quoted when the
table was created. The default value is false. See related documentation in
hsqldbAdvancedGuide.html on how to set this property value. (this property was 
subsequently dropped)

The rest of the information for ALPHA_N below applies to this version too.


HSQLDB 1.7.2 ALPHA_N

ALPHA_N is the ultimate ALPHA version for the 1.7.2 release cycle. Known
deficiencies and reported bugs will be addressed for the first Release Candidate
to be issued in the next few weeks.

Please report any bugs or suggestions through the normal channels. Bugs with 
test
cases should be posted to the bug tracker on the HSQLDB Sourceforge site.

The included documentation has been updated. The JavaDoc for the jdbcXXX classes
covers the JDBC interface. You can make the JavaDoc via the build scripts.

The jar has been compiled with JDK 1.4. To run under earlier JDK's recompile 
using
JDK 1.3.x.

Changes since 1.7.2 are listed here in the reverse chronological order.

2003.09.02

ADDED JDBC SUPPORT FOR SAVPOINTS

2003.08.22

FURTHER SPEED IMPROVEMENTS IN ALL LOGGED MODES
INSERT and UPDATE ops are faster by 5-20% in MEMORY tables, less in CACHED 
tables.


ADDED SUPPORT FOR A RANGE OF SQL STYLE FUNCTIONS:

CASE .. WHEN ... THEN .. ELSE ... END
CASE WHEN .. THEN .. ELSE ... END
NULLIF(.. , ..)
SUBSTRING(.. FROM .. FOR ..)
POSITION(.. IN ..)
TRIM( {LEADING | TRAILING .. } FROM ..)
EXTRACT({DAY | TIME |..} FROM ..)
COALESCE(.. , ..)


2003.08.17

FURTHER IMPROVEMENTS IN PREPARED STATEMENT EXECUTION SPEED

SUPPORT FOR JDBC BATCH EXECUTION

SUPPORT FOR MULTIPLE ROWS WITH NULL FIELDS UNDER UNIQUE CONSTRAINTS

IMPROVEMENTS TO TestSelf

IMPROVEMENTS TO ON UPDATE CASCADE / SET DEFAULT / SET NULL

2003.08.11

REWRITE OF LEFT OUTER JOIN SUPPORT

- Complete rewrite of join processing abolishes the requirement for 
an index on any joined columns. All expressions with AND are supported
in the join condition (JOIN atable ON ....)

IMPROVEMENTS TO ON DELETE CASCADE / SET DEFAULT / SET NULL

- Self referencing foreign keys and multiple foreign keys now support
ON DELETE CASCADE.

2003.08.02

NIO ACCESS FOR .data FILES

New nio access layer for .data files speeds up most CACHED TABLE related
operations very significantly. 90% speedups in TestCacheSize tests
have been observed. The program must be compiled with JDK 1.4 and run
in a 1.4 JRE to use the new access mode.

IMPROVEMENTS TO UPDATE AND INSERT

Certain types of UPDATES and INSERTS that previously failed due to
blanket application of UNIQUE constraints now work.

Examples include UPDATE ... SET col = col + 1 where col is an identity
column or INSERT a self referencing row under FOREIGN key constraints.

2003.07.30

IMPORTANT FIX IN SELECT QUERY PROCESSING

Problems with OUTER and INNER joins returning incorrect results 
have been reported over the last couple of years. A new fix is 
intended to ensure correct results in all cases.

- When two tables are joined, rows resulting from joining null
values in the joined columns are no longer returned.

- Use of OUTER requires the existence of an index on a joined OUTER column
- There are still limitations on the conditions used in OUTER joins.

AGGREGATES

- enhancements to aggregate functions: aggregates of different numeric
types are now supported

SUM returns a BIGINT for TINYINT, SMALLINT and INTEGER columns. It
returns a DECIMAL for BIGINT columns (scale 0) and DECIMAL columns 
scale equals to that of the column).

AVG returns the same type as the column or the expression in its 
argument.

- aggregates with GROUP BY do not return any rows if table is empty

OLDER ENHANCEMENTS NOT PREVIOUSLY REPORTED

- IDENTITY columns can now be of BIGINT type:

CREATE TABLE <name> (<column> BIGINT IDENTITY, ...)

- With contributed patch, TEXT TABLES encoding of the source file can
now be specified. UTF-8 and other encodings can be used.

- Two new options for databases: files_readonly and files_in_jar
were added based on submitted patches.

FILE READ-ONLY

If the property files_readonly=true is set in the database 
.properties file, no attempt is made to write the changes to data to
file. Default, memory tables can be read/write but TEXT and CACHED
tables are treated as read-only.

FILES IN JAR

This option allows database files to be distributed in the 
application jar. We have changed the original contribution so
that a special URL is used for this mode in the form of:

jdbc:hsqldb:res:<path in jar>

The URL type 'res' determines that the path that follows is a path
into the JAR.

The database can be readonly or files_readonly, depending on the
value set in .properties file.

2003.07.09

SCRIPT FORMAT

- change to command previously named SET LOGTYPE (discussed below for
earlier alphas) new form is:
SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED }

The new binary and compressed formats are not compatible with previous
ones, so you should change any old 1.7.2 ALPHA_? database to text
mode with SET LOGTYPE 0 before opening with the new version.

'OTHER' DATA TYPE

- change to handling of OTHER columns. It is no longer required that
the classes for objects stored in OTHER columns to be available on
the path of an HSQLDB engine running as a server. Classes must be
available on the JDBC client's path.

OBJECT POOLING

- the new Object pool has been incorporated. This reduces memory
usage to varying degrees depending on the contents of database
tables and speeds up the database in most cases. Currently the
size of the pool is hard-coded but it will be user adjustable in the
release version.

CONNECTION PROPERTY

- a new property, ifexists={true|false} can be specified for connections.
It has an effect only on connections to in-process databases. The default
is false and corresponds to current behaviour. 

If set true, the connection is opened only if
the database files have already been created -- otherwise no new database
is created and the connection attempt will fail. Example:

jdbc:hsqldb:hsql:mydb;ifexists=true

This property is intended to reduce problems resulting from wrong URL's 
which get translated to unintended new database files. It is recommended
to use this property for troubleshooting.

2003.07.04

PREPARED STATEMENTS

- support for real PreparedStatements - major speedup. Current limitations
include lack of support for parameters in IN() predicates of queries.

TRANSACTIONS VIA WEB SERVER

- uniform support for transactions via HSQL and HTTP (WebServer and Servlet)
protocols

MULTIPLE IN-MEMORY AND SERVERS DATABASES

- support for multiple memory-only databases within the same JVM

- support for simultaneous multiple servers, multiple internal
connections and multiple databases within the same JVM


NEW CONVENTIONS FOR URL'S AND .properties FILES

Each HSQLDB server or webserver can now serve up to 10 different databases.

The server.properties and webserver.properties method for defining the
databases has changed. The following properties should be used:

server.database.0   path_of_the_first_database
server.dbname.0 alias_for_the_first_database

Up to 10 databases can be defined but they must start from 0 

The same applies to command line arguments for Server and WebServer.

The urls for connecting to servers should have the alias of the database
at the end.

For example, to connect to the HSQL protocol server on the localhost use:

jdbc:hsqldb:hsql://localhost/alias_for_the_database

where alias_for_the_database is the same string as defined in
server.properties as server.dbnam.n

The default for server.dbname.0 is "" (empty string) so that
old URL types continue to work.

Multiple memory-only database are supported by the use of:

jdbc:hsqldb:mem:alias_for_the_first_database
jdbc:hsqldb:mem:alias_for_the_second_database

Examples: jdbc:hsqldb:mem:db1 jdbc:hsqldb:mem:mydb

The conneciton type, 'file', can be used for file database
connections. example below:

jdbc:hsqldb:hsql:file:mydb;ifexists=true


The URL for connecting to a Servlet HTTP server must have a 
forward-slash at the end. Servlet serves only one database.

jdbc:hsqldb:hsql://localhost:8080/servlet/HsqlServlet/



2003.03.10

DATABASE METADATA

-system table support and java.sql.DatabaseMetadate results have been
overhauled by Campbell.

STRICT FOREIGN KEYS

-strict treatment of foreign key index requirements is now enforced.
A foreign key declaration _requires_ a unique constraint or index to exist
on the columns of the referenced table. This applies both to old and
new databases. Duplicate foreign keys (with exactly the same column sets)
are now disallowed.

TEXT TABLES

-further improvements to TEXT table support. Smaller cache sizes are
now the default and the default properties can be specified in the 
*.properties file.



HSQLDB 1.7.2 ALPHA_M

2003.01.23

-fixed reported bugs in SHUTDOWN COMPACT
-fixed reported bugs in GRANT statements on system tables
-fixed bug that prevented UPDATE in some circumstances

some enhancements that appeared in previous versions but not reported:

-enhanced handling of DATE objects - comparability and normalisation
-support for CLOB methods in ResultSet
-fixed bug in afterLast() followed by previous() in ResultSet


HSQLDB 1.7.2 ALPHA_L

2003.01.16

various patches and fixes

-fixes new issues reported with ALPHA_K
-fixes old issues related to uncommitted transactions in
abnormal shutdown
-fixes old issues with SAVEPOINT names
-enhanced TEXT table handling and reporting of errors in
CSV (source) files


HSQLDB 1.7.2 ALPHA_K

2003.01.10

various patches and fixes

-OUTER with multiple column joins is now supported while OR is
disallowed in the join expression. It is now possible to specify
a range or equality condition on the OUTER columns returned.

-submitted patch for exclusion of NULL values from results of
range queries has been applied. e.g. 
WHERE columnvalue < 3
will exclude from the result all rows with NULL in columnvalue.

-a number of small enhancements and bug fixes.

further enhancements to logging. 
-The *.script file now contains only the DDL and data that is 
written at CHECKPOINT or SHUTDOWN.
The statements logged while the engine is running are stored
in a *.log file.
-The format of the *.script file can now be one of TEXT,
BINARY or COMPRESSED. The SET LOGTYPE {0|1|3} will reset the 
format. A checkpoint is performed at this point if the format
is different from the existing one.
The COMPRESSED format has the side benefit of hiding the DDL
and the admin password.
-The behaviour of SET WRITE_DELAY has changed with the
introduction of the sync() method to force the log to be
written out completely to disk at given intervals.
SET WRITE_DELAY {TRUE | FALSE} is interpreted as synch every
60 seconds or 1 second. SET WRITE_DELAY <n> where n is an integer
is interpreted as synch every n seconds. The current default is
60 seconds which seems to provide the right balance. The
performance impact of SET WRITE_DELAY 1 is probably about 15% over that
of SET WRITE_DELAY 300.

-The recovery from crash has been modified so that any line in the log
that is not properly written (and causes an error) ends the redo process.
A message is reported to the user, instead of stopping engine operation.

HSQLDB 1.7.2 ALPHA_J

2002.12.24
CVS hsqldb-dev module tag: HSQLDB_1_7_2_ALPHA_J

-More work on text tables. See hsqlTextTables.html.
-Some refactoring of Table.java and Parser.java


HSQLDB 1.7.2 ALPHA_I

2002.12.16
CVS hsqldb-dev module tag: HSQLDB_1_7_2_ALPHA_I

More work on core classes to consolidate previous changes
-correction of reported bug with text tables and primary keys
-reducing data file writes esp. with big rows
-preventing Cache related error in ON DELETE CASCADE 


HSQLDB 1.7.2 ALPHA_H

2002.12.11
CVS hsqldb-dev module tag: HSQLDB_1_7_2_ALPHA_H


Reduction in JDK / JRE dependencies (see readmebuild.txt)
Extensive refactoring of some of the larger classes.
More changes to core classes Node, Row, Index, Cache, Log ...
Some minor bug fixes and enhancements.
Enhancements to text table support, including binary columns.
Bug fixes to User.java and UserManager.java.
Support for ON UPDATE / DELETE SET NULL / SET DEFAULT
SSL support for Server mode operation.

2002.10.30

HSQLDB 1.7.2 ALPHA_G

CVS hsqldb-dev module tag: HEAD data: 2002.10.30

New features include:

Tony Lai's enhancements to aggratates and support for HAVING.

Tony's new UnifiedTable class (provisional name) which he kindly wrote
at my request and provides sorted arrays with minimal storage
requirements.

My refactoring work on DatabaseRowInput/Output subclasses and Cache.java.

This is still ongoing and has several features aimed at a more robust 
database engine, including:
- reduction in object creation;
- possibility of resizing the cache while the engine is running;
- possibility of exporting and backing up the DB files while the engine is
  running.

I have implemented the new feature, CHECKPOINT DEFRAG to defragment a
*.data file without shutting down the engine, in two different versions,
taking advantage of the above improvements.

I have implemented binary logging of MEMORY table inserts in the *.script 
file, resulting in much smaller startup and shutdown times with large 
memory tables. Use SET LOGTYPE {0|1} to set the log type to text (0) or
binary (1). This performs a checkpoint if the type needs changing.

Also further performance optimisations resulting in faster CACHED table
operations have been made.

Sebastian has implemented ON UPDATE CASCADE support for foreign keys.