HSQLDB Changelog

What's new in HSQLDB 2.2.5

Jul 25, 2011

NEW CORE:
Fully multithreaded core supports 2PL (two-phased locking) and MVCC (multiversion concurrency control), plus a hybrid 2PL+MVCC transaction control mode. Transactions can be SERIALIZABLE or READ COMMITTED, using strict 2PL concurrency control. Version 2.0 also adds the MVCC modes, SNAPSHOT ISOLATION and READ CONSISTENCY, which are comparable to REPEATABLE READS and READ COMMITTED isolation levels, but with higher concurrency. Many enhancements are introduced to allow maximum multi-threaded concurrency in different isolation modes.
SCALABILITY:
Massive high performance LOB store for BLOBs and CLOBs up to multi-gigabyte size, with total storage capacity of 64 terabytes.
Increased default storage space of 16GB for ordinary data, with fast startup and shutdown. Storage space can be extended to 2TB.
Large result sets, views and subqueries can now be stored on disk (on the server side) while being generated and accessed. The threshold to store a result on disk, as well as the actual fetch size in client-server configurations can be specified per connection.
Internal and external commands for backing up databases to TAR and GZIP archives.
QUERY OPTIMISATION:
All query conditions, whether in a JOIN or WHERE clause, are now allocated to an index if possible. IN queries are now optimised to use an index if possible. Conditions with OR are optimised if indexes can be used. MAX(), MIN() and ORDER BY with or without LIMIT and OFFSET expressions can use indexes. All indexes can be used in reverse order for these operations.
SQL STORED PROCEDURES AND FUNCTIONS:
HyperSQL supports schema-based stored procedures and functions written entirely in SQL. Procedural SQL language includes WHILE loops, IF, CASE WHEN, and exception handling statements. User defined aggregate functions are supported. SQL procedures can return multiple result sets and return values. SQL functions can return single values, arrays, or tables that can be used in SQL queries. User defined aggregate functions are supported.
JAVA STORED PROCEDURES AND FUNCTIONS:
HyperSQL supports schema-based stored procedures and functions written entirely in JAVA. Polymorphism is supported. User defined aggregate functions are supported. Java procedures can return multiple result sets and return values. Java functions can can return single values, arrays or even tables that can be used in SQL queries.
NEW DATA TYPES:
Support for BIT, BIT VARYING, CLOB, BLOB, INTERVAL according to the SQL Standards. TIME can now have a fractional second part. TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE are supported. The full range of combinations of datetime and interval types is supported. Support for DOMAIN objects with constraints and DISTINCT types.
ARRAY TYPES:
Arrays of most types can be used in table definitions, expressions, function parameters and return types.
COLLATIONS:
Multiple collations can be used for different tables or columns. A collation can be specified for ORDER BY.
NEW EXPRESSION TYPES:
Complete rewrite of scanner and parser classes. Supports all SQL standard identifier and character string literals (Unicode strings and escapes, etc.).
Supports extended CASE WHEN conditions such as CASE X WHEN IN (,,), BETWEEN A AND B, 21, 56, IS NULL THEN ..
UNIQUE(SELECT ..) predicate.
(A,B) OVERLAPS(X,Y) predicated.
Supports Z BETWEEN [SYMMETRIC | ASYMMETRIC] (X, Y) predicate.
Multi-column (A,B,C) IN ((,,), (,,), ) both with literals and queries
Supports (A, B, C) {= | > | < | |…}(W,X,Y,Z) predicates.
Supports (A,B,C) IS [NOT] DISTINCT FROM (W,X,Y,Z) predicates
Supports (A,B,C) MATCHES [UNIQUE] [SIMPLE]|[PARTIAL]|[FULL] (SELECT …) predicates.
Supports (A,B,C) {= | < | > | =} {ANY | ALL} SELECT … predicates.
Full Standard syntax and semantics (arithmetic and other operations) of expressions involving INTERVAL types. Supports type casts to INTERVAL types modifiers (e.g. DAY). Supports WITH TIME ZONE data types, including zone modifiers (i.e. AT LOCAL | AT TIME ZONE …).
Supports standard SQL grammar, including IS [NOT] {NULL | TRUE | FALSE | UNKNOWN}.
Supports expressions in all LIKE arguments.
NEW DATA MANIPULATION LANGUAGE FEATURES:
Both INSERT and UPDATE command have been enhanced to support multi-row inserts, omission of parentheses, DEFAULT keyword, mix of subquery and row expressions. The powerful MERGE command is fully supported.
INSERT INTO … VALUES (expr,expr, ..), (expr,expr, …), …
INSERT INTO … VALUES expr
INSERT INTO .. DEFAULT VALUES
INSERT INTO … VALUES (expr, DEFAULT, ..)
UPDATE … SET A = DEFAULT, ..
UPDATE … SET (A, B, ..) = (expr, DEFAULT,…), C = expr, (D,E) = (SELECT …)
MERGE statement with full Standard compliant syntax is supported
TRUNCATE TABLE is fully supported
NEW DATA QUERY LANGUAGE FEATURES:
SELECT has been extensively enhanced, supporting all Standard join types.
The scope of column labels in SELECT queries is now treated according to the SQL Standard. Labels are visible in the ORDER BY expression but not in GROUP BY
Supports TABLE X to introduce the equivalent of SELECT * FROM TABLE X in set operations.
Supports VALUES (,,,), (,,,) as table constructor in joins.
Supports TABLE, LATERAL and UNNEST table constructor in joins.
Supports column name list after correlation name SELECT .. FROM A AS B (X,Y,Z) JOIN C…
[LEFT | RIGHT | FULL {OUTER}] JOIN
UNION JOIN
[LEFT | RIGHT | FULL {OUTER}] NATURAL JOIN
[LEFT | RIGHT | FULL {OUTER}] JOIN … USING (A, B, ..)
SELECT * with the above joins now returns the correct column sequence as per SQL Standard
JOIN conditions can now contain any valid boolean expressions.
Support for UNION {ALL|DISTINCT}, INTERSECT {ALL|DISTINCT} and EXCEPT {ALL|DISTINCT}
Support for all the above with CORRESPONDING[()}
Support for , e.g. SELECT … FROM (table1 OUTER JOIN table2) JOIN table3
Support for NULLS FIRST, NULLS LAST in ORDER BY
Full support for inclusion of set functions (e.g. COUNT, AVG) in subquery conditions contained within a HAVING clause. Includes support for user-defined aggregate functions
Supports FETCH ROWS ONLY as SQL Standard alternative to LIMIT at the end of query expression
DATA DEFINITION LANGUAGE ENHANCEMENTS:
Supports full syntax of SQL TRIGGER definition for row level triggers using SQL procedure statements (as well as Java classes)
CREATE TRIGGER {BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OF (, ..)]
[ REFERENCING OLD [ROW] [AS] |
NEW [ROW] [AS] ]
[FOR EACH ROW]
[WHEN ()]
Supports full set of SEQUENCE generator options ([NO] MAXVALUE, [NO] MINVALUE, [NO] CYCLE, etc.) and data types including SMALLINT, INT, BIGINT, DECIMAL, NUMERIC. These are supported in IDENTITY sequences and in normal sequences, including all relevant ALTER COLUMN and ALTER SEQUENCE commands.
Supports GENERATED {BY DEFAULT | ALWAYS} AS IDENTITY in a different column than the PRIMARY KEY column.
A user supplied value or a value returned from a SELECT statement can always be inserted into an identity column. If GENERATED ALWAYS has been specified, then OVERRIDING SYSTEM VALUE must be included in the INSERT statement.
Supports GENERATED ALWAYS AS for derivative columns that are based on other column values.
Extended CREATE SCHEMA …. statements can include cross references between FOREIGN KEY constraints in different tables.
Supports CREATE TABLE .. () AS () WITH [NO] DATA
CREATE TABLE can have mixed column and constraint creation elements. A column definition can include a PRIMARY KEY, UNIQUE, FOREIGN KEY or CHECK constraint,
Similarly, ALTER TABLE … ADD COLUMN can feature constraint definitions.
ALTER TABLE … ALTER COLUMN … SET DATA TYPE … supported.
Supports CASCADE with ALTER TABLE .. DROP COLUMN and ALTER TABLE … DROP CONSTRAINT.
Named NOT NULL constraints now supported in column definition CONSTRAINT C NOT NULL. All NOT NULL constraints are treated as CHECK (C IS NOT NULL) type constraints and listed as such in system tables.
Supports VIEW definitions including tables and sequences in other schemas.
Supports updatable views, including WITH {LOCAL | CASCADED} CHECK OPTION
SQL FUNCTIONS:
A wide set of SQL Standard functions, including correct type handling and application to all supported types (e.g. BINARY, BLOB, CLOB), is now supported.
SUBSTRING for character (CHAR, VARCHAR, CLOB) and binary (BINARY, VARBINARY, BLOB) types.
UPPER, LOWER for all character types
TRIM for all character types
OVERLAY for all character types
POSITION
CHAR_LENGTH, CHARACTER_LENGTH, OCTET_LENGTH
EXTRACT
ABS for all number types
An extended set of extra functions is also supported, including:
TO_CHAR, TO_DATE, TRUNCATE
REGXP_MATCHES, REGXP_SUBSTRING, REGX_SUBSTRING_ARRAY
DECODE, GREATEST, etc.
OTHER SQL FEATURES:
Supports column level SELECT, INSERT and UPDATE access rights, with GRANT and REVOKE on individual columns of tables, including WITH GRANT OPTION. GRANT SELECT(A, D) ON X TO U
Supports SQL STATE with messages defined by the standard
COMPATIBILITY FEATURES:
Supports several syntax and operation compatibility flags to ease testing and porting applications written against a different database engine. These include PostgreSQL, MySQL, Oracle, MS SQL Server
JDBC FEATURES:
Supports getGeneratedKeys() calls in Statement and PreparedStatement.
Supports CallableStatement with multiple result sets and IN and OUT parameters
Supports POSIX functions TIMESTAMPDIFF and TIMESTAMPADD.
Supports Java 1.6 and JDBC4. All applicable new methods are supported.
INFORMATION SCHEMA:
Supports SQL Standard INFORMATION SCHEMA views, together with additional views used in JDBC. Altogther some 90 view are supported. Standard views are supported correctly and reflect the user's permissions on database objects.
OTHER FEATURES:
The Server now supports remote opening of databases.
Supports advanced external user authentication and password complexity checks.
Supports SQL statement logging as well as external framework logging via Log4J and JRE logging.