HSQLDB (Hyper SQL Database)

From Bauman National Library
This page was last modified on 22 June 2016, at 15:53.
HSQLDB
Hypersql.png
Developer(s) The hsqldb Development Group
Repository {{#property:P1324}}
Written in Java
Platform Platform independent
Type RDBMS
Website www.hsqldb.org

HSQLDB (Hyper SQL Database) is a relational database management system written in Java.

General

HSQLDB (HyperSQL DataBase) is the leading SQL relational database software written in Java. It offers a small, fast multithreaded and transactional database engine with in-memory and disk-based tables and supports embedded and server modes. It includes a powerful command line SQL tool and simple GUI query tools.

HSQLDB supports the widest range of SQL Standard features seen in any open source database engine: SQL:2011 core language features and an extensive list of SQL:2011 optional features. It supports nearly full Advanced ANSI-92 SQL (BNF format). Many extensions to the Standard, including syntax compatibility modes and features of other popular database engines, are also supported.

To use HSQLDB, you only need to include the HSQLDB Jar File in your classpath (i.e., the dependency has to be declared in the Maven POM file). A call to the JDBC driver will then create an instance of the database.

HSQLDB can keep tables in various modes (independent of the runing mode: server, or embedded): By default, tables will keep all the data in-memory (but persist it to a file). This is obviously a very fast method, but not useful for larger tables. Alternatively, tables can be kept "only" on disk, which is the usual operation mode for other relational databases. Please check out the HSQLDB documentation (create table command) for details. This setting can be done on a "per-table" level, hence some tables could be kept in memory, others only on disk. This allows e.g., fine-tuning of performance. (Additionally there are also temporary tables, that are not persisted to disk at all).

The JDBC driver is initialized with a URL, e.g.

jdbc:hsqldb:data/pruefungen

This will tell the hsqldb instance to search for a directory data/ and use the configuration pruefungen from there. This configuration name will be used for 4 files:

  • NAME.properties: A configuration file for HSQLDB
  • NAME.script: A startup script
  • NAME.lck: A file that stores the lock information (so that only one HSQLDB instance will use this configuration at a given time)
  • NAME.log: A logfile of the last run

Especially interesting is the startup script. In the startup script, you can enter multiple SQL commands that will be run during startup. This way, you can create a certain state in your database by creating tables, adding data etc.

Background

HyperSQL is developed and published by the The HSQL Development Group. The group was formed in 2001 and has released several major new versions of the database over the years. Version 2.0 was released in 2010 with a brand new transactional core engine and JDBC implementation. The engine has been developed much further in version 2.3.4. Direct downloads from SourceForge exceed 2,000,000 copies, with hundreds of millions of copies distributed as part of other software packages.

HyperSQL was selected as the SourceForge Project of the Month for January 2012 and was featured on SourceForge.net home page. An interview with core developers is published here.

Features

General

  • Original code, based on in-depth study of database theory and the SQL Standard
  • Ahead of other open-source solutions in supported SQL features
  • Extensive syntax compatibility modes for porting from other database systems
  • The fastest overall open-source SQL implementation for small and medium sized databases
  • Three transaction control models, including lock based and MVCC models
  • Fully multithreaded
  • Compact code footprint

JAVA

  • 100% Java
  • Supports JDK 5, 6, 7 and 8 in HyperSQL 2.x (JDK 1.1.x, 1.2.x, 1.3.x, 1.4.x, 5, etc. in HyperSQL 1.8.1)
  • Extensive JDBC interface support with batch statement and scrollable ResultSet functionality
  • Updatable, insertable ResultSet functionality
  • Full JDBC DatabaseMetaData and ResultSetMetaData support
  • User-defined Java stored procedures and functions, including aggregate functions
  • Supports Java procedures with multiple INOUT params, returning multiple ResultSets, and functions returning ResultSet and Array objects
  • SQL triggers and Java triggers, including synchronous and asynchronous execution
  • Full support for CallableStatement and PreparedStatement, including batch execution to speed up data processing

SQL

  • Relational Database Management System, which can store Serializable Java objects
  • Very extensive support for SQL:2011 Standard syntax, including most optional features
  • Supports all base data types of the SQL Standard, including TIMESTAMP WITH TIME ZONE, BINARY, BIT, BOOLEAN, date-time, INTERVAL, BLOB, CLOB
  • Supports datetime types and arithmetic with and without time zone
  • Supports user-defined DOMAIN types, including type constraints
  • Fast SELECT, INSERT, DELETE, UPDATE operations
  • MERGE statement allows single or multiple INSERT, UPDATE, DELETE depending on existing data
  • INNER, LEFT OUTER, RIGHT OUTER and FULL joins
  • NATURAL, USING and UNION joins
  • Scalar (single value) SELECTS, row, table and correlated subqueries including IN, EXISTS, ANY, ALL
  • Supports recursive queries
  • Views and temporary tables
  • Updatable, insertable into, deletable views
  • Primary key, unique and check constraints on single or multiple columns
  • Referential Integrity (foreign keys) on multiple columns with full cascading options (delete, update, set null, set default)

Persistence

  • In-memory tables for fastest operation
  • Disk based tables for large data sets
  • Text tables with external file data sources such as CSV files can be used as SQL tables
  • Fast CLOB and BLOB storage up to 64 TB with no memory limit on individual LOB size
  • Disk tables (CACHED TABLE) up to 8TB and text tables up to 256GB each
  • Size of each string or binary item only limited by memory
  • Fast startup and shutdown with internal incremental backup feature
  • Online and offline backup capability
  • Database dump as SQL script with or without data

Deployment

  • Embedded (into Java applications) and Client-Server operating modes
  • Three client server protocols: HSQL, HTTP and HSQL-BER - can run as an HTTP web server - all with SSL option
  • Can be used in applets, read-only media (CD), inside jars, webstart and embedded applications
  • Multiple databases per JVM
  • ODBC support

Utilities

  • SQLTool, powerful and compact Java command line tools for database management, supports most leading database systems
  • Two simple GUI database management tools
  • Transfer tool for conversion of databases to / from other popular database management systems
  • Test / script utility that can run SQL scripts with user-defined checks


Links