HyperSQL 2.0 - New Release 5 Years In the Making

Posted by Josh Long on Jun 18, 2010

Sections

Operations & Infrastructure,

Enterprise Architecture,

Development,

Architecture & Design

Topics

Java ,

Persistence ,

Open Source ,

Data Access ,

Unit Testing ,

Modularity

Tags

Database Management ,

Database Replication ,

Database ,

Apache Derby ,

Hypersonic DB

HyperSQL 2.0 was released on the 7th of June. This release marks the first since HSQLDB 1.8 came out more than 5 years ago. HSQLDB 1.8 has since been integrated with many tools, frameworks and applications, including the ubiquitous OpenOffice 3.2 productivity suite where it provide an embedded database option to handle use cases where one might otherwise use Microsoft Access. HyperSQL is 100% Java, and provided under the BSD license.

The new version boasts more features than any other open source database engine available. It debuts new SQL capabilities, increased scalability, query optimization, as well as some net-new features.

The core of HyperSQL is fully multi-threaded. It supports two-phased locking and MVCC (multi-version concurrency control), both of which are mechanisms for contending with concurrent access to the database's resources. MVCC, for example, is also employed by other databases like PostgreSQL. In addition to the already supported transaction control modes SERIALIZABLE and READ COMMITTED, HyperSQL 2.0 adds support for two new isolation levels equivalent to the REPEATABLE READ and READ UNCOMMITTED isolation levels.

Major components – including the SQL scanners and parsers – have been rewritten, featuring nearly complete support for ANSI-92 SQL and SQL:2003, as well as many SQL:2008 features (including many optional extensions). This support permits many new expressions and constructs like the BETWEEN clause, OVERLAPS predicate, multi column IN expressions, MERGE statement, and MATCHES predicates. In addition, HyperSQL 2.0 supports the full syntax for trigger definitions in both SQL and Java, as well as read-only and updateable views. Support for generation of SEQUENCE types has been added to ease unique number generation. The new version includes support for various data types, including BIT types, CLOB, BLOB, and the SQL standard INTERVAL type. TIME support has been enhanced and includes a fractional second parts, as well as support for timezones. Array types are available and can comprise most of the other types available. Additionally, the engine will make use of all available indexes where possible, in JOIN clauses, WHERE clauses, IN queries, and aggregate functions like MIN, MAX, and ORDER BY expressions. The new version supports stored procedures and functions. The stored procedure language also includes support for procedural constructs like WHILE loops, IF, CASE WHEN, and exception handling statements, as well as aggregate functions. These functions can be defined in SQL, or in Java. If they're written in Java, then niceties like polymorphism are available.

HyperSQL 2.0 features many smaller changes that will make management and usage easier. The new version increases storage sizes for general types - up to 256GB – and adds support for BLOB and CLOB data, supporting up to a total of 64 terabytes. It sports an updated JDBC 4.0-compatible driver, and Java 1.6 support. Notably, the getGeneratedKeys() method on the Statement and PreparedStatement interfaces are now supported, which will – among other things - make working with some ORM tools easier.

All in all, a feature-packed release. The project has proven very popular as a testable environment and a solid embedded database option. HyperSQL seems to have the most mindshare, but faces competition from the likes of H2 – created by Thomas Mueller, the original author of HyperSQL - and Apache Derby (formerly IBM's CloudScape), which is included with JDK 6 as JavaDB.

To find out more, consult the HyperSQL web site.

1. Back to top

   HyperSQL 2.0

   by Andy Jefferson

   Some very welcome improvements ... and all supported in current DataNucleus AccessPlatform for those people wanting flexible well performing JDO / JPA persistence.
   
   --Andy (DataNucleus)
   
   PS, think you have a typo in para 3, should be READ_UNCOMMITTED as being added.

   Reply

2. Back to top

   H2 and Hypersql 2.0

   by Matt Giacomini

   Does Thomas Mueller still work on both code bases?

   Reply

3. Back to top

   Re: H2 and Hypersql 2.0

   by Thomas Mueller

   No, I stopped working on Hypersonic SQL / HSQLDB since quite a long time. I fully work on the H2 database now (well, 'fully' is the wrong word - it's still a hobby mainly).

   Reply

• Older >