HSQLDB 1.7.2 CHANGE LOG

The development of 1.7.2 began in late 2002 with a plan to release the new version within 6 months. Several alpha versions, culminating in ALPHA_M, were released in the first three months and featured most of the intended enhancements. However, when newly written code for system tables was introduced, many changes had to be made to internal code to accommodate the reporting requirements. This was followed from around April 2003 with efforts to separate query compilation from execution. Other developments to allow multiple databases, better query processing, more advanced SQL keywords, etc. took place simultaneously, resulting in an extended scope for 1.7.2 and extensive addition and rewrite of several key classes. The next alpha version was released in September, followed by a further six, until the first Release Candidate became available at the end of 2003. Since then, all efforts have been focused on fixing bugs and inconsistencies, with 7 additional Release Candidates issued.

As a result, 1.7.2 features major changes to the database engine. Existing applications that work with previous versions may need modifications to work with the new version. Changes are listed here and in the rest of the documentation.

I would like to thank all developers, testers and users who have contributed to this effort.

June 2004

Fred Toussi

Maintainer, HSQLDB Project
http://hsqldb.sourceforge.net

----------------------------------------------------
----------------------------------------------------

SQL ENHANCEMENTS AND CHANGES

----------------

DDL

Some new restrictions have been imposed:

CONSTRAINT names must be unique within each database (same with INDEX names).
Size / precision / scale values for several types are now disallowed. Allowed types are: CHAR(n), VARCHAR(n), DOUBLE(n), DECIMAL(n,m)

In column default value specification, functions such as CURRENT_TIME, CURRENT_DATA, etc. should not be enclosed in quotes. New function CURRENT_USER can also be used.

SQL keywords are not allowed as table or column names unless they are double-quoted.

----------------

VIEW

View definitions can now have a column name list:

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

ALTER TABLE commands take into account the views that reference the table and prevent illegal changes.

----------------

CHECK

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.

----------------

QUERY PROCESSING

Query processing has been extensively enhanced in this release and better SQL standard 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, column indexes or column names in the first select are allowed as sort specification e.g.:

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

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)

The above applies to all cases where a SELECT can be used except a single value select inside another select. Eg.

SELECT a, b, SELECT asinglevalue FROM anothertable WHERE ..., FROM atable JOIN ...

----------------

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.

----------------

AGGREGATES, GROUP BY, HAVING

DISTINCT aggregates are now supported.

Aggregates on all numeric type columns are now supported. Expressions are allowed as aggregate function parameter.

SUM returns a BIGINT for TINYINT, SMALLINT and INTEGER columns. It returns a DECIMAL for BIGINT columns (scale 0). SUM of a DECIMAL column has the same scale as 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

Fully enforced GROUP BY rules including support for HAVING conditions

----------------

JOINS

Extensive rewrite of join processing abolishes the requirement for an index on any joined columns.

Problems with OUTER and INNER joins returning incorrect results have been fixed and results are correct in all cases.

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

Most expressions are supported in the join condition (JOIN <table> ON ....).

Outer join conditions can now include most comparison operators, as well as OR logical operators. For example:

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:

----------------

NULLS

Support for multiple rows with null fields under unique constraints.

Exclusion of NULL values from results of queries with range conditions.

----------------

FOREIGN KEY

Full support for triggered actions, including foreign keys that reference the same table.

FORIEGN KEY ... ON UPDATE { CASCADE | SET NULL | SET DEFAULT } ON DELETE { CASCADE | SET NULL | SET DEFAULT }

Strict treatment of foreign key constraint requirements is now enforced. A foreign key declaration _requires_ a unique constraint 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.

----------------

SEQUENCE

Support for sequences. Identity columns are now automatic sequence primary key columns that can be defined as INTEGER or BIGINT as follows:

GENERATED BY DEFAULT AS IDENTITY (START WITH <n>, INCREMENT BY <m>)

Named sequence objects can be defined with:

CREATE SEQUENCE <sequencename> [AS {INTEGER | BIGINT}] [START WITH <startvalue>] [INCREMENT BY <incrementvalue>];

And the next value can be retrieved with the following expression in SELECT, INSERT and UPDATE queries:

NEXT VALUE FOR <sequencename>

----------------

SQL FUNCTIONS

Added support for a range of SQL style functions:

CASE .. WHEN .. THEN .. [WHEN .. THEN ..] ELSE .. END

CASE WHEN .. THEN .. [WHEN .. THEN ..] ELSE ... END

NULLIF(.. , ..)

SUBSTRING(.. FROM .. FOR ..)

POSITION(.. IN ..)

TRIM( {LEADING | TRAILING .. } FROM ..)

EXTRACT({DAY | TIME |..} FROM ..)

COALESCE(.. , ..)

----------------

TRIGGER

It is now possible to execute triggers in the main execution thread. This allows uses for triggers that were not possible before, especially checking and modifying inserted values.

----------------

DATETIME

Fixed the TIMESTAMPS, TIME and DATE normalisation issues.

----------------

OTHER

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

LIKE has been debugged and optimised when possible.

----------------------------------------------------
----------------------------------------------------

JDBC ENHANCEMENTS

JDBC support for savepoints.

Support for JDBC batch execution with multiple-results. Both Statement and PreparedStatement batch modes are supported.

SSL support for server mode operation.

----------------

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:file: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.

Database properties can be specified for the first connection to a new file: or mem: database. This allows properties such as enforce_strict_size to be specified for mem: databases, or for a new file: database.

jdbc:hsqldb:hsql:mem:test;sql.enforce_strict_size=true

----------------

PREPARED STATEMENTS

Support for real PreparedStatements - major speedup.

Bug fixes ensure date / time, java object and binary values stored in in-process databases via prepared statements will not be altered if the object is modified outside the engine.

Full support for ResultSetMetaData

Full support for ParameterMetaData

Support for CLOB methods in ResultSet

----------------

TRANSACTIONS VIA WEB SERVER

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

----------------------------------------------------
----------------------------------------------------

OTHER ENHANCEMENTS

----------------------------------------------------

SPEED

Speed optimisation of joins with views and subqueries, using indexes to optimise join performance.

Improved index use for multiple key joins.

Further speed improvements in all logged modes

INSERT and UPDATE ops are faster by 5-20% in MEMORY tables, less in CACHED tables.

General speedup of CACHED table operation due to better management of the memory cache.

----------------------------------------------------

DATABASE PACKAGING AND MODES

Two new options for databases: files_readonly and files_in_jar were added:

FILE READ-ONLY

If the property hsqldb.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 path must be all lowercase and begin with a forward slash.

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

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

----------------------------------------------------

MULTIPLE IN-MEMORY AND SERVER DATABASES

Support for multiple memory-only databases within the same JVM

Support for simultaneous multiple servers on different ports, multiple internal connections and multiple databases within the same JVM

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 URL's 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.dbname.n

If not explicitly set, 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 connection type, 'file:', can be used for file database connections. Example below:

jdbc:hsqldb: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/

----------------------------------------------------

DATABASE METADATA

System table support and java.sql.DatabaseMetadate have been overhauled.

Use SELECT * FROM SYSTEM_TABLES to see the full list.

----------------------------------------------------

TEXT TABLES

Enhanced TEXT table handling and reporting of errors in CSV (source) files

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

----------------------------------------------------

MEMORY USE AND OBJECT POOLING

An 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 a future version.

----------------------------------------------------

PERSISTENCE

----------------

CHECKPOINT DEFRAG

Defragments the *.data file without shutting down the database

----------------

SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED }

Changes the format of the *.script file and performs a checkpoint.

Database script can be stored in binary or compressed binary formats, resulting in smaller size and faster loading.

----------------

The *.script file now contains only the DDL and data that is written at CHECKPOINT or SHUTDOWN. The COMPRESSED format has the side benefit of hiding the DDL and the admin password.

The *.log file now contains the statements executed since the last startup or CHECKPOINT. This file is in plain text format.

----------------

SET WRITE_DELAY {TRUE | FALSE}

SET WRITE_DELAY <n>

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. Under heavy INSERT/DELETE/UPDATE test conditions, the performance impact of SET WRITE_DELAY 1 is probably about 15% over that of SET WRITE_DELAY 300.

Crash recovery has been modified so that any line in the *.log file 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.

----------------

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.

There must be enough available memory in the machine to allow a memory-mapped buffer for the entire *.data file. Beyond this size, the engine reverts to non-nio access.

----------------------------------------------------

BUILD

Reduction in JDK / JRE dependencies (see readmebuild.txt)

The supplied JAR is now compiled with JDK 1.4 and requires a JRE 1.4 or later to work. You can rebuild the JAR with JDK 1.3.x in order to use it with JRE 1.3 or earlier.

----------------------------------------------------

DOCUMENTATION

Documentation is now maintained mainly in XML format with HTML and PDF versions available.

----------------------------------------------------

UTILITIES

The SQL Tool is a powerful new utility in 1.7.2 and allows processing SQL commands via the shell or scripts.

The Transfer Tool utility saw a major upgrade just before the 1.7.2 release cycle. In this release some minor updates and bug fixes, together with significant speedup, have been intoduced.

The Database Manager has been enhanced slightly to include a command to save the result set in CSV format. The Swing version of Database Manager allows the user to sort the rows of the result set on any column.

The connection dialogue in the AWT version of Database Manager and Transfer Tool allows saving and recalling connection URL's together with the user name and password.

----------------------------------------------------

END

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

HSQLDB version 1.7.1 CHANGELOG SINCE VERSION 1.7.0

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.

* Build

Linux build scripts in 1.7.1

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

HSQLDB version 1.7.0 CHANGELOG

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

* 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 versions of Transfer Tool and Database Manager.

* Build

Better Ant build with automatic detection of JDK version

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

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

HSQLDB v. 1.61

Changes 20 July 2001

The following enhancements and bug fixes have been introduced:
Patch or Bug tracking numbers are included where one exists.

Compatibility with version 1.43 databases.
Database files created with 1.43 can be opened with version 1.61. The
database is converted into the 1.6x format. As a precaution against future
problems, databases created with later versions cannot be opened by this
version(1.60 and 1.61, 1.6x are considered the same version but 1.6x and
1.7x are different versions).

The LIMIT keyword for fetching only part of the result set.

SELECT LIMIT n m { selectExpression | table.* | * } [, ... ] .........

The LIMIT keyword can be used immediately after the SELECT keyword and has
two numbers as parameters. The query is executed as if LIMIT and its
parameters were not present, then the rows after the offset position (n)
and specified by count (m) are returned. Offset n is counted from one.
"SELECT LIMIT 10 20 * FROM mytable" returns rows 11-30 only
Currently LIMIT cannot be used together with DISTINCT

418019 Timestamp format

418022 Char comparison

418023 SUBSTR

416144 416146 430615 GROUP BY used with aggregate functions:
a new patch, without side-effects, has been used

425495 setObject

Fred Toussi
fredt@users

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

Changes 06 July 2001

I have checked the bug reports and patches and indicated my opinion as to
which patches / bug fixes should be done for the 1.61 point release,
stating NO or OK. I have incorporated some of these and fixed one myself.
In addition, the patch for database migration from 1.43 to 1.6x that I
submitted on 16 April should be incorporated into the point release.

There were 10 patches up to 16 April, which according to Mark, have been
incorporated. Except:

OK-------416149 Swing interface for DatabaseManager - ok - but file is missing

Patches since 16 April:

OK-------418014 Trace specific exceptions - ok
INCORPORATED fredt

NO-------418017 Month format
fredt: - patch is ok - do we want this as it can break existing code

OK-------418019 Timestamp format - ok - won't break existing code
INCORPORATED fredt

OK-------418022 Char comparison
fredt: - patch is ok - do we want this as it can break existing code
fredt: - now I think we should add it anyway. Side effects would be minimal.
INCORPORATED fredt

OK-------418023 SUBSTR - ok
INCORPORATED fredt

Bugs

OK-------416138 Resolved: The user should use switchtojdk11 before the build

NO-------416139 Multiple result sets
fredt: High Hopes?

-------416140 PATCH for getMoreResults() - ok

ALREADY IN VERSION 1.60 fredt

OK-------416141 DatabaseMetaData.getExportedKeys() does not work :
fredt: I know this one from Hypersonic. May be able to fix it.

fredt: In DatabaseInformation.java, getSystemTable(,,) creates a table on the
fly for metadata information and then fills it with information from tTable.
Code has been written to fill the SYSTEM_TABLES, SYSTEM_TYPES, etc.
but not FOREIGN_KEY etc. as these would be more complicated.
I hope to get a bit of extra time in the near future to implement
this and related calls, getExportedKeys(), getCrossReference(), etc.

-------416142 SAME AS ABOVE

OK-------416143 PATCH LONGVARBINARY - ok

ALREADY IN VERSION 1.60

OK-------416144 PATCH for GROUP BY - has side effect
-------416146 SAME AS ABOVE

INCORPORATED fredt: fixed with different code without side effect

OK-------416325 complaint by user -
fredt: If they submit their changes we will incorporate into the CVS.

OK-------416357 DB transition - documentation issue - If anyone has a
half-finished doc we can put it up and ask users / developers to
contribute and finish it. Mike seems to have done something about this.

OK-------423114 Timestamp - Mike and Mark were looking into this.
fredt: If no results I can have a go.

OK-------423964 Single Quote in string fields - says it has been fixed in HQSLDB
fredt: has it been fixed?

??-------424027 DELETE FROM ... problem with cached tables
NO-------424759 CREATE TABLE ... request for extended SQL support (worked with 1.43)
fredt: it works if NOT NULL is placed before PRIMARY KEY

NO-------424763 canonizing column names request for case-insensitive names (worked with 1.43)
NO-------424767 Request for support for 0 and 1 as bit values

OK-------424784 now() parsed as timestamp

OK-------425495 PATCH setObject - ok
INCORPORATED fredt

-------430615 SAME AS 416144
OK-------432855 shutdown compact

Fred Toussi
fredt@users

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

Changes to HSQLDB v. 1.60 directory structure and introduction of an
Ant build.xml

The original directory structure of HypersonicSQL had evolved around a set of
demos and the hsql.jar file was located in the /demo directory.

There was no /lib directory and the javax.servlet classes were in the same
folder as the source code. This arrangement was somewhat useful with jdk1.1.x
as it did not require a classpath argument for some of the compile and run tasks.
But it has no major advantage with jdk1.3.

In HSQLDB, all classes are in the org.hsqldb. tree, compared to HypersonicSQL's
org.hsql. This change already dictates that any existing .html, .bat, .sh and
other command files that are in use for running the database, or its utility
classes, have to be edited to reflect the new tree name.

HypersonicSQL only included .bat files for development and running tasks. The
development of Apache Ant has made it possible to perform compile and build tasks
in a platform-independent manner. So a build.xml script for Ant is highly
desirable.

These circumstances justify the introduction a new directory structure which
follows the standards used by Sun and others to distribute Java programs.

Changes:

Summary of Changes:

/bin /lib and /classes directories have been added to the root directory of the
installation. Some existing files have been moved to these directories. All
command files have been updated to work within the new structure.

Details of Changes:

/bin is almost empty now but will hold .bat and .sh files used for creating
databases and running different configuration of the server. Unlike the
versions in /demo, these will not create a test database and will expect the
user to specify a database.
(.sh files will be added after some work to make them independent of java
runtime location)

/lib contains the javax.servlet precompiled classes (moved from /src) and is
the location of the hsqldb.jar file when it is created.

/classes contains the org.hsqldb classes while the .jar is being built and
CodeSwitcher.java is used to convert source code. It is normally deleted after
the operation.

In /src there is an Ant build.xml that builds the hsqldb.jar. (Ant must be
already installed). To use it, change to /src then type:

ant

This displays the command line options which include

ant jar to make the hsqldb.jar
ant clean to clean up the /classes directory that is created
ant cleanall to remove the old jar as well
ant switchtojdk11 to process and change the code for jdk1.1.x
ant switchtojdk12 to process and change the code for jdk1.2.x and 1.3.x

In /demo all the html and bat files have been changed to reflect the
new relative location of the hsqldb.jar file. The .bat files all work.

In .html files, the codebase argument was added to the Applet tag. The
in-memory and client-server .html demos all work

/src/build.bat works with jdk1.1x and above. It puts the hsqldb.jar file in
/lib and takes account of the new location of javax classes.

All the other .bat files work, except:

switchtoprofile.bat
switchtoruntime.bat

There is a problem with the same .bat files in HypersonicSQL 1.43. The new format
of the 1.60 code and the addition of comments in 1.60 just result in extra
errors.

THESE .bat FILES CORRUPT THE SOURCES, so that if they are performed one followed
by the other, the sources no longer compile correctly (tested with jdk1.3).

***I suggest not to include them in the distribution until there is a
resolution.

I think the new directory structure can be introduced as the remaining minor
problems are definitely not related to the change of directory structure.

Tests have been performed on Win NT 4 SP6A with jdk1.3.0_02 and jdk1.1.8. In
both test environments, a minimal classpath was used. Internet Explorer 5.5 was
used for testing .html Applets

Fred Toussi
fredt@users

======================================================================================
Code Changes in 1.60

Several minor fixes, code formatted to Sun standards, Javadoc comments started.

Issues Addressed:

Problem: LONGVARBINARY error using setObject.
When using setObject to set the value of a binary column a class cast
error occured. Added methods to resolve this, bug and fix submitted by ?.

Problem: BINARY data types were limited to 32K.
Added support for larger objects. Bug and fix submitted by Jonathan Tappan.

Problem: Cached table corruption
Modified Cache class to update free space list correctly. Bug and fix
submitted by Jonathan Tappan.

Problem: readonly property causes stack dump in Log class
Bug and fix submitted by Donatella Mori and Richard Hoad.

Problem: getMoreResults fails to follow JDBC spec.
Bug and fix submitted by Brent Boyer.

Problem: Subselect bugs
Bug and fix submitted by Mark Muday.

Problem: hsql connection type converted to http by jdbcConnection class.
Bug and fix submitted by Elize Jensen.

Problem: INTERNAL connection type being closed incorrectly, causing, among other
things, CALL IDENTITY() to fail. Bug and fix provided by Mauro Canal.

New Features:

Extended SQL syntax for DROP TABLE to allow 'DROP TABLE IF EXISTS <table>'.

Logicscope Realisations Ltd. graciously sponsored Peter Hudson who developed
the trigger implementation for HSQLDB.

Postponed Features:

Reworked transactions mechanism. The problem with the old mechanism
(as I understand it) was that during a transaction, the uncommitted records were
available to other channels, i.e. they can read uncommitted changes that may be
rolled back in the case of an aborted transaction. The modification
allows 2 phase transactions, where the updates are not visible outside of the
Channel until the transaction is committed. Patch submitted by Valerio Aimale.
The concept is sound, but the changes caused problems, perhaps due to other changes
in this release.

Mark Tutt
mtutt@users

======================================================================================
Code Changes in 1.44

1.44 is a retrospective package that can be used for comparison with
later versions of HSQLDB.
It consists of the code base for Hypersonic 1.43 with
the following modifications only:

Code formatted to Sun standards.
Package names changed from hsql to hsqldb

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

Previous work (hsql/Hypersonic SQL)