HSQLDB Changelog

What's new in HSQLDB 2.2.0

• 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
• 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
• JDBC FEATURES
• Supports getGeneratedKeys() calls in Statement and PreparedStatement.
• Supports POSIX functions TIMESTAMPDIFF and TIMESTAMPADD.
• Supports Java 1.6 and JDBC4. All applicable new methods are supported.
• SQL STANDARD FEATURE LIST
• SQL-92 direct SQL is supported fully to Intermediate Level, while most extra features of Advanced Level are also supported. The only features of Advanced Level not supported are deferrable constraint enforcement, ASSERTION, and CHECK constraints that contain subqueries.
• All SQL:2003 - SQL:2008 Standard CORE features are supported.
• HSQLDB 2.0.0 also supports the following set of SQL:1999, SQL:2003 and SQL:2008 NON-CORE features. (some features not yet added to this list)
• B031 Direct SQL
• C071-01 Read-only scrollable cursors (via JDBC ResultSet)
• F032 CASCADE drop behaviour
• F033 ALTER TABLE statement: DROP COLUMN clause
• F034 Extended REVOKE statement
• F034-1 REVOKE statement performed by other than the owner of a schema object
• F034-2 REVOKE statement: GRANT OPTION FOR clause
• F034-3 REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION
• F052 Interval and datetime arithmetic
• F053 OVERLAPS predicate
• F171 Multiple schemas per user
• F191 Referential delete actions
• F200 TRUNCATE TABLE statement
• F202 TRUNCATE TABLE: identity column restart option
• F222 INSERT statement: DEFAULT VALUES clause
• F231 Privilege tables
• F231-01 TABLE_PRIVILEGES view
• F231-02 COLUMN PRIVILEGES view
• F231-03 USAGE PRIVILEGES view
• F251 Domain Support
• F262 Extended CASE expression
• F263 Comma-separated predicates in simple CASE expression
• F271 Compound character literals
• F291 UNIQUE predicate
• F301 CORRESPONDING in query expressions
• F302 INTERSECT table operator
• F302-1 INTERSECT DISTINCT table operator
• F302-2 INTERSECT ALL table operator
• F304 EXCEPT ALL
• F312 MERGE statement
• F321 User authorization
• F381 Extended schema manipulation
• F381-01 ALTER TABLE statement: ALTER COLUMN clause
• F381-02 ALTER TABLE statement: ADD CONSTRAINT clause
• F381-03 ALTER TABLE statement: DROP CONSTRAINT clause
• F382 Alter column data type
• F391 Long identifiers
• F392 Unicode escapes in identifiers
• F393 Unicode escape in literals
• F401 Extended joined table
• F401-01 NATURAL JOIN
• F401-02 FULL OUTER JOIN
• F401-04 CROSS JOIN
• F402 Named column joins for LOBs, arrays, and multisets
• F411 Time zone specification
• F421 National character
• F431 Read-only scrollable cursor
• F441 Extended set function support
• F442 Mixed column references in set functions
• F451 Character set definition
• F461 Named character sets
• F491 Constraint management
• F502 Enhanced documentation tables
• F502-01 SQL_SIZING_PROFILES view
• F502-02 SQL_IMPLEMENTATION_INFO view
• F502-03 SQL_PACKAGES view
• F511 BIT data type
• F531 Temporary tables
• F555 Enhanced seconds precision
• F561 Full value expressions
• F571 Truth value tests
• F591 Derived table
• F641 Row and table constructors
• F651 Catalog name qualifiers
• F661 Simple Tables
• F672 Retrospective check constraints
• F690 Collation support (limited)
• F701 Referential update actions
• F711 ALTER domain
• F731 INSERT column privileges
• F751 View CHECK enhancements
• F762 CURRENT_CATALOG
• F763 CURRENT_SCHEMA
• F781 Self-referencing operations
• F791 Insensitive cursors
• F801 Full set function
• F831 Full cursor update
• F850 Top-level in
• F851 in subqueries
• F852 Top-level in views
• F855 Nested in
• F856 Nested in
• F857 Top-level in
• F858 in subqueries
• F859 Top-level in views
• F860 in
• F861 Top-level in
• F862 in subqueries
• F863 Nested in
• F864 Top-level in views
• F821 Local table references
• F865 in
• S091 Basic array support
• S091-01 Arrays of built-in data types
• S091-02 Arrays of distinct types
• S091-03 Array expressions
• S095 Array constructors by query
• S096 Optional array bounds
• S097 Array element assignment
• S201 SQL-invoked routines on arrays
• S201-01 Array parameters
• S201-02 Array as result type of functions
• T011 Timestamp in Information Schema
• T021 BINARY and VARBINARY data types
• T022 Advanced BINARY and VARBINARY data type support
• T023 Compound binary literals
• T024 Spaces in binary literals
• T031 BOOLEAN data type
• T041 Basic LOB data type support
• T041-01 BLOB data type
• T041-02 CLOB data type
• T041-03 POSITION, LENGTH etc functions for LOB data types
• T021-04 Concatenation of LOB data types
• T071 BIGINT data type
• T151 DISTINCT predicate
• T152 DISTINCT predicate with negation
• T172 AS subquery clause in table definition
• T174 Identity columns
• T176 Sequence generator support
• T177 Sequence generator support: simple restart option
• T178 Identity columns: simple restart option
• T191 Referential action RESTRICT
• T201 Comparable data types for referential constraints
• T211 Basic trigger capability
• T211-01 Triggers activated on UPDATE, INSERT, or DELETE of one base table.
• T211-02 BEFORE triggers
• T211-03 AFTER triggers
• T211-04 FOR EACH ROW triggers
• T211-05 Ability to specify a search condition that shall be True before the trigger is invoked.
• T211-06 Support for run-time rules for the interaction of triggers and constraints.
• T211-07 TRIGGER privilege
• T211-08 Multiple triggers for the same event are executed in the order in which they were created in the catalog.
• T271 Savepoints
• T281 SELECT privilege with column granularity
• T285 Enhanced derived column names
• T301 Functional dependencies
• T312 OVERLAY function
• T326 Table functions
• T331 Basic roles
• T332 Extended roles (partial)
• T351 Bracketed SQL comments
• T441 ABS and MOD functions
• T461 Symmetric BETWEEN predicate
• T471 Result sets return value
• T491 LATERAL derived table
• T501 Enhanced EXISTS predicate
• T551 Optional key words for default syntax
• T571 Array-returning external SQL-invoked functions
• T591 UNIQUE constraints of possibly null columns
• T621 Enhanced numeric functions
• T641 Multiple column assignment