Editing HSQLDB

{{short description|Java-based database engine}}
{{Multiple issues|
{{More citations needed|date=February 2008}}
{{Primary sources|date=February 2008}}
}}

{{Infobox software
| name                   = HSQLDB (HyperSQL DataBase)
| logo                   =
| screenshot             = Hsql.png
| caption                = HSQL Database Manager
| collapsible            =
| developer              =
| released               = {{Start date and age|2001}}
| latest release version = {{wikidata|property|Q669257|P348}}
| latest release date    = {{start date and age|{{wikidata|qualifier|Q669257|P348|P577}}}}
| latest preview version =
| latest preview date    = 
| size                   = 10.8&nbsp;MB (2.6.0 ZIP file)<ref>{{cite web|url=https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_6/|title=Home / hsqldb / hsqldb_2_6|work=HyperSQL Database Engine (HSQLDB)|publisher=SourceForge|date=21 March 2021|access-date=1 April 2021}}</ref>
| operating system       = [[Cross-platform]]
| programming language   = [[Java (programming language)|Java]]
| genre                  = [[Relational database management system|RDBMS]]
| license                = [[BSD licenses|BSD]]
}}
'''HSQLDB''' ('''''H'''yper '''SQL''' '''D'''ata'''b'''ase'') is a [[relational database management system]] written in [[Java (programming language)|Java]]. It has a [[JDBC driver]] and supports a large subset of [[SQL-92]], [[SQL:2008]], [[SQL:2011]], and [[SQL:2016]] standards.<ref>{{cite web | title=HSQLDB SQL Syntax | publisher=hsqldb.org | url=http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html}}</ref> It offers a fast,<ref>{{cite web | title=PolePosition Performance Comparison | publisher=polepos.org | url=http://polepos.sourceforge.net/results/html/index.html}}</ref> small (around 1300 kilobytes in version 2.2) [[database engine]] which offers both [[In-memory database|in-memory]] and disk-based tables. Both embedded and server modes are available.

Additionally, it includes tools such as a minimal [[Web server]], command line and GUI management tools (can be run as applets), and a number of demonstration examples. It can run on Java runtimes from version 1.1 upwards, including [[free Java implementations]] such as [[Kaffe]].

HSQLDB is available under a [[BSD licenses|BSD license]]. It is used as a database and persistence engine in many [[open-source software|open source]] software projects, such as descendants of [[OpenOffice.org Base]] (i.e., [[Apache OpenOffice Base]], [[LibreOffice Base]], etc.), and the [[Jitsi]] VoIP and video-conference client since version 2.6.<ref>{{cite web |url=https://download.jitsi.org/jitsi/windows/updates/ |title=Jitsi build 5390 release notes |publisher=Jitsi.org |access-date=2015-02-01 |archive-date=2015-02-02 |archive-url=https://web.archive.org/web/20150202001841/https://download.jitsi.org/jitsi/windows/updates/ |url-status=dead }}</ref> It is also used in commercial products, such as [[Mathematica]] and InstallAnywhere (starting with version 8.0).<ref>{{cite web | title=Software using HSQLDB |publisher=hsqldb.org | url=http://hsqldb.org/web/hsqlUsing.html }}</ref>

== Transaction support ==
HSQLDB version 2.0 has three transaction control modes. It supports read committed and serializable [[Isolation (database systems)#Isolation levels|transaction isolation levels]] with table level locks or with [[multiversion concurrency control]] (MVCC), or a combination of locks and MVCC. Version 1.8.1 supports transaction isolation level 0 (read uncommitted) only.<ref>{{cite web | title = HSQLDB Documentation | url = http://hsqldb.org/doc/2.0/guide/sessions-chapt.html#sqlgeneral_trans_cc-sect  | publisher=hsqldb.org}}</ref>

== Data storage ==
HSQLDB has two main table types used for durable read-write data storage, i.e., if a transaction has been successfully committed, it is guaranteed that the data will survive system failure and will keep their integrity.

The default MEMORY type stores all data changes to the disk in the form of a [[SQL]] script. During engine start-up, these commands are executed and data are reconstructed into the memory.

Another table type is CACHED, which allows one to store more data, at the cost of the slower performance. The HSQLDB engine loads them only partially and synchronizes the data to the disk on transaction commits. However, the engine always loads all rows affected during an update into the memory. This renders very large updates impossible without splitting the work into smaller parts.<ref>{{cite web | title = HSQLDB Documentation | url = http://hsqldb.org/doc/guide/ch05.html#N10DED | publisher = hsqldb.org | access-date = 2007-11-11 | archive-url = https://web.archive.org/web/20150502215807/http://www.hsqldb.org/doc/guide/ch05.html#N10DED | archive-date = 2015-05-02 | url-status = dead }}</ref>

Other table types allow access to [[comma-separated values]] (CSV) files. These tables can participate, for example, in queries with JOINs and simplify spreadsheet processing and read-write non-durable in-memory data storage.

== SQL features ==
HSQLDB 2.0 supports all the core features and many optional features of [[SQL:2008]]. Advanced features include user-defined SQL procedures and functions, schemas, datetime intervals, updatable views, arrays, lobs, full and lateral joins and [[Set operations (SQL)|set operations]]. Many non-standard functions such as TO_CHAR and DECODE are also supported. Extensions to standard SQL include user-defined [[aggregate function]]s.

== Releases ==
Several versions of HSQLDB have been released since 2001. Early versions were based on the discontinued HypersonicSQL database engine. Version 2.0, released in 2010, is mostly new code, written to conform to Standard [[SQL]] and [[Java Database Connectivity|JDBC]] 4 Specification.<ref>{{cite web | title=The new HSQLDB | publisher=hsqldb.org | url=http://hsqldb.org/web/features200.html}}</ref>

Version 2.3.2 (released in 2014) is fully multi-threaded and supports high performance [[two-phase locking]] and [[multiversion concurrency control|MVCC (multiversion concurrency control)]] transaction control models.

==See also==
{{Portal|Free and open-source software}}
* [[List of relational database management systems]]
* [[Comparison of relational database management systems]]
* [[Apache Derby]]
* [[H2 (database)|H2]]

==References==
{{Reflist|2}}

==External links==
*{{official website}}

[[Category:Free database management systems]]
[[Category:Java platform software]]
[[Category:SQL]]
[[Category:Relational database management systems]]
[[Category:Relational database management software for Linux]]
[[Category:Software using the BSD license]]
[[Category:Embedded databases]]
[[Category:Database engines]]