Version 1.7.1 improves performance and fixes several bugs that have come to light since the release of 1.7.0. It does not feature any major new functionality in the database engine. *Over ten-fold speedup of function calls in queries introduced in 1.7.1. *Up to 30% reduction in memory used by indexes and table rows, resulting in smaller overall memory footprint in 1.7.1. *Linux scripts for starting and shutting down server instances in 1.7.1. *Linux build scripts in 1.7.1. *The Transfer Tool was enhanced for this version by Nicolas Bazin and Bob Preston. HSQLDB version 1.7.0 CHANGELOG SINCE VERSION 1.60 * Enhancements to SQL LIMIT and TOP keywords The LIMIT keyword was introduced in 1.61 with limited functionality. In 1.7.0 the functionality has been fully extended: SELECT LIMIT DISTINCT is now allowed. SELECT LIMIT ... 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 ... WHERE IN (SELECT LIMIT

... ) SELECT LIMIT ... UNION SELECT LIMIT

TOP is synonymous with LIMIT 0 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 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_. 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 AS SELECT ... FROM ... WHERE .... DROP VIEW {IF EXISTS} ALTER The following statements are now supported: ALTER TABLE RENAME TO All tables can now be renamed. ALTER TABLE ADD COLUMN [BEFORE ] ALTER TABLE DROP COLUMN 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 DROP CONSTRAINT 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 ADD CONSTRAINT UNIQUE Unique constraints can be added. A new index is created if necessary. ALTER TABLE ADD CONSTRAINT FOREIGN KEY Both backward and forward referencing foreign keys are supported. ALTER INDEX RENAME TO 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 Any index can be dropped, so long as it is not a primary key index or an automatically created index on the referencing columns of a foreign key or an index backing a constraint. SQL NAMES Full support for quoted SQL object names which can include any Unicode character. This applies to TABLE, COLUMN, CONSTRAINT and INDEX names. * Functions SET FUNCTION such as COUNT AVG SUM now accept the DISTICT parameter. This feature is supported only when GROUP BY is not used in the query. A number of bugs in functions such as ABS, CONCAT and SUBSTR have been fixed. A small number of new functions have been added. The SOUNDEX function has been rewritten according to standards. * Expressions Arithmetic expressions in SQL statements are now fully supported. Previously, when an arithmetic expression was used to INSERT or UPDATE a value, the first term had to be of the same type as the columns. For example 2*1.2 could not be inserted into a DECIMAL or DOUBLE column. Now all expressions are evaluated and the resulting value can be inserted into a column so long as it can be converted without narrowing. More lenient treatment of values for TIMESTAMP columns in INSERT and UPDATE now allows a DATE or part TIMESTAMP string to be used instead of the full pattern. BIT columns can be initialised with numeric values in addition to true and false. Numeric value 0 represents false; all other values represent true. * Locales CHAR, VARCHAR and LONGVARCHAR columns can now be sorted according to the current JVM locale. This feature is optional and turned off by default. If turned on, it can slow down database operations, especially where indexes on CHAR columns and its variant are used. * Error Trapping Some errors in SQL statements that were not reported before and resulted in potentially incorrect data are now caught. Dropping tables that are referenced by foreign key constraints from other tables is now disallowed regardless of the system-wide REFERENTIAL_INTEGRITY setting. Duplicate column names in one table are no longer allowed. Duplicate index names are no longer allowed. Using system table names for user tables is disallowed. INSERT INTO table (a,b,c,...) VALUES (x,y,z,...) statements require the count of names and values to match. * Enhancements to JDBC JDBC ResultSet objects are now scrollable and fully support absolute and relative positioning. Extra getXXX() methods are now supported, including getAsciiStream(), getUnicodeStream(), getCharacterStream(). Optional use of column labels (aliases) as the return value for getColumnName(int i) calls. This is turned off by default. JDBC PreparedStatement support has been enhanced with support for setting and reading Date, Time and Timestamp values with a given Calendar object. This allows, among others, specifying different time zones for values that are set or read. Extra setXXX() methods are now supported, including setAsciiStream(), setUnicodeStream(), setCharacterStream(). setObject() methods of PreparedStatement have been improved to allow storage of any serializable object in columns of SQL type, OTHER. JDBC DatabaseMetaData getImportedKeys(), getExportedKeys(), getCrossReference(), getBestRowIdentifier() are now supported. * Interoperability Enhancements HsqlServerFactory allows interoperability with application servers. An embedded server (an HSQLDB server started inside another application) can be directed to avoid calling System.exit() when the SHUTDOWN command is issued to the database for admin purposes. javax.jdbc.DataSource interface has been introduced as an experimental feature. In order to include this support, the source code should be compiled with JDK 1.4 or with appropriate J2EE jars present in the class path. * Performance Enhancements Very large CHAR, VARCHAR, LONGVARCHAR values are now supported in all types of tables. Major overhaul of internal operations to drastically reduce creation of temporary objects and type conversion. Code enhancements to reduce unnecessary method calls in loops. Enhancements to avoid duplicate indexes (in source code but not activated). Over ten-fold speedup of function calls in queries intoduced in 1.7.1. Up to 30% reduction in memory used by indexes and table rows, resulting in smaller overall memory footprint in 1.7.1. * Reliability Enhancements Many bugs that existed in 1.43 have been fixed. Better catching of error conditions and special cases such as null values. Correct treatment of large binary data. Better enforcement of internal data consistency in a number of areas. * Utilities New Script Tool class allows executing an SQL script. Enhancements to Transfer Tool to allow the user to save the transfer settings and load them in future sessions. Additional enhancements to support different database source and targets in a modular way. Swing version of Database Manager. Linux scripts for starting and shutting down server instances in 1.7.1. * Build Better Ant build with automatic detection of JDK version Linux build scripts in 1.7.1 List of patches and fixes numbered by SourceForge Tracker ID's. 416437 by deforest@users - JDBC DataSource 418017 by deforest@users - made optional 437174 by hjb@users - cache enhancements 442993 by fredt - mixed type arithmetic expressions 448121 by sma@users - large binary values - part implemented 489917 by jytou@users - optional getColumnName(int) return value 450412 by elise@users - jdk 1.1 compatibility 450455 by kibu@users - platform specific line endings in .scrip file 455757 by galena@users (Michiel de Roo) 455785 by hjbusch@users - large DECIMAL inserts 456679 by hiep256@users - TOP keyword 460907 by fredt - soundex 461556 by paul-h@users - compatibility with application servers 471710 by fredt - LIMIT rewritten 473613 by thertz@users - error trapping for joins 475199 by fredt - duplicate column 475586 by wreissen@users - servlet database name 476650 by johnhobs@users - GROUP BY aggregates 476694 by velichko@users - transaction savepoints 478657 by peterhudson@users - improved TRIGGER support 481239 by xponsard@users - enhancement to Transfer Tool 481239 by yfl@users - SQL script execution 485704 by boucherb@users - null binary objects 488200 by xclay@users - throw exception 489184 by xclay@users - thread safety 489777 by fredt - protection of system tables 491987 by jimbag@users - column size enforcement and preservation 495484 by boucherb@users - exception at shutdown 495938 by johnhobs@users - GROUP BY sorting 497714 by lakuhns@users - scrollable JDBC result sets 497872 by Nitin Chauhan - performance enhancements 497872 by Nitin Chauhan - default labels for aggregates 500767 by adjbirch@users - TransferTool enhancements 505356 by daniel_fiser@users - sorting according to Locale 509002 by fredt - correct parsing of primary key constraints 513005 by sqlbob@users - ABS function 513005 by sqlbob@users - HOUR function 513005 by sqlbob@users - TEMP tables 513005 by sqlbob@users - TEXT TABLE support 514111 by fredt - CONCAT function 517028 by peterhudson@users - JDBC setDate(), etc. with Calendar 521078 by boucherb@users - DROP TABLE checks 523880 by leptitpre@users - VIEW SUPPORT 544754 by sqlbob@users - RAWTOHEX and HEXTORAW functions 549741 by velichko@users - RENAME for tables and indexes 550970 by boucherb@users - fewer StringBuffers 552830 by rougier@users - COUNT(DISTICT column) 580430 by thomasm@users - wrong column name in getIndexInfo() List of enhancements without patch number 1.7.0 by boucherb@users - maintaining constraint names 1.7.0 by boucherb@users - self referenced foreign keys 1.7.0 by boucherb@users - stubs for JDBC3 methods and updated Javadoc 1.7.0 by boucherb@users - Javadoc for a number of classes 1.7.0 by deforest@users - create database directory if not exists 1.7.0 by fredt - full support for quoted identifiers 1.7.0 by fredt - ON DELETE CASCADE qualifier for foreign keys 1.7.0 by fredt - foreign keys without target column list 1.7.0 by fredt - DEFAULT keyword in column definitions 1.7.0 by fredt - GROUP BY with multiple columns in groups 1.7.0 by fredt - JDBC getDate, etc. with Calendar 1.7.0 by fredt - lenient handling of Timestamp inserts 1.7.0 by fredt - JDK 1.1 compatible deque object backed by an array 1.7.0 by fredt - new HsqlProperties class 1.7.0 by fredt - preservation of column size, etc. with SELECT INTO 1.7.0 by fredt - ensuring internal consistency of Result objects 1.7.0 by fredt - resolving some deprecation warnings 1.7.0 by fredt - ALTER TABLE for adding and dropping columns and constraints 1.7.0 by fredt - revamped storage of cached table with very large strings 1.7.0 by fredt - management of server connections with optional no system exit 1.7.0 by fredt - revamped management of server and database properties 1.7.0 by fredt - JDBC getUnicodeStream(), getAsciiStream(), getCharacterStream() 1.7.0 by fredt - JDBC setAsciiStream(), setUnicodeStream(), setCharacterStream() 1.7.0 by fredt - JDBC getCrossReference(), getImportedKeys(), getExportedKeys() 1.7.0 by nbazin@users - enhancements to Transfer Tool 1.7.0 by sqlbob@users - reengineering of Database Manager and Transfer Tool 1.7.0 by sqlfredt - 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