Chronological Change List

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

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

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
