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 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 RENAME TO New features Support for RIGHT OUTER JOIN and CROSS JOIN in select statements ALTER TABLE ALTER COLUMN SET [NOT] NULL Added DAY_OF_WEEK as an alternative for EXTRACT(XXX FROM ) 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 AUTHORIZATION DBA DROP SCHEMA {CASCADE | RESTRICT} ALTER SCHEMA RENAME TO SET SCHEMA 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 GRANT ... TO REVOKE ... FROM GRANT TO DROP ROLE 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 [IF EXISTS] [CASCADE]; DROP VIEW [IF EXISTS] [CASCADE]; DROP SEQUENCE [IF EXISTS] [CASCADE]; ALTER TABLE
DROP COLUMN now silently drops any primary key or unique constraint declared on the column (excluding multi-column constraints). ALTER TABLE
ADD [COLUMN] now accepts primary key and identity attributes. COLUMN MANIPULATION Support for converting type, nullability and identity attributes of a column ALTER TABLE
ALTER COLUMN 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
ALTER [COLUMN] RESTART WITH ALL and ANY expressions Full support for ALL(SELECT ....) and ANY(SELECT ....) with comparison o perators: =, >, <, <>, >=, <= Example: SELECT ... WHERE >= 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 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
ALTER COLUMN 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 SOURCE HEADER . 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 ADD CONSTRAINT PRIMARY KEY(collist); ALTER TABLE DROP CONSTRAINT ; ALTER TABLE 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