2009-03-17

Comparison of HSQLDB, H2, SQLite

Item HSQLDB (1.8) H2(1.1.109) SQLite (3.6.22)
Lanaguage Java Java C
Supports Client/Server Mode Yes Yes No
Supports Embedded Mode Yes Yes Yes
Supports In-Memory Databases Yes Yes Yes
Provided Transaction Isolation Mode Read Uncommitted Read Committed, Serializable Unclear
Supports Foreign Key Constraint Yes Yes Yes
(≥ 3.6.19)
Supports View Object Yes Yes Yes
Supports Sequence Object Yes Yes No
Supports User-defined Procedure/Function Yes No No
Supports Trigger Yes Yes Yes
Supports Inner Join Yes Yes Yes
Supports Left Outer Join Yes Yes Yes
Supports Right Outer Join Yes Yes No
Supports Full Outer Join Yes Yes No
Supports ORDER BY Clause Yes Yes Yes
Supports GROUP BY/HAVING Clause Yes Yes Yes
Supports Paginated Query Yes
(offet m fetch n lows)
Yes
(limit n offset m)
Yes
(limit n offset m)
Provides C API No No Yes
Provides JDBC Driver Yes Yes by 3rd party
Provides ODBC Driver under development Yes No
Explicitly Supported by Hibernate 3.3 Yes Yes No
Explicitly Supported by DbUnit 2.4 Yes Yes No
Explicitly Supported by Power*Architect 0.9.13-RC1 Yes No No

Other comparisons



Features of HSQLDB (1.8)

from http://hsqldb.org/web/hsqlFeatures.html
JAVA
  • 100% Java
  • Support JDK 1.1.x, 1.2.x, 1.3.x, 1.4.x, 1.5.x, 1.6.x
  • Extensive JDBC interface support with batch statement and scrollable ResultSet functionality
  • Full JDBC DatabaseMetaData and ResultSetMetaData support
  • Java stored procedures and functions
  • Full support for PreparedStatement objects to speed up query processing
SQL
  • Relational Database Management System, with the object capabilities of Java
  • Very extensive support for SQL:2003 Standard syntax, including most optional features
  • Supports all base data types of the SQL Standard, including BINARY, BIT, BOOLEAN, date-time, INTERVAL, BLOB, CLOB
  • Supports user-defined DOMAIN types, including type constraints
  • Fast SELECT, INSERT, DELETE, UPDATE and MERGE operations
  • INNER, LEFT OUTER, RIGHT OUTER and FULL joins
  • UNION, EXCEPT, INTERSECT, including use of parentheses, limits and offsets
  • Scalar (single value) SELECTS, row and correlated subqueries including IN, EXISTS, ANY, ALL
  • Views, Temp tables and sequences
  • 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)
  • ORDER BY, GROUP BY and HAVING
  • COUNT, SUM, MIN, MAX, AVG and statistical aggregate functions
  • Full support for SQL expressions such as CASE .. WHEN .. ELSE .. , NULLIF etc.
  • SQL standard autoincrement column support plus sequences
  • Transaction COMMIT, ROLLBACK and SAVEPOINT support
  • Multiple schemata per database
  • Triggers, implemented as Java classes, or as SQL statements
  • Database security with passwords, user rights and roles with GRANT and REVOKE
  • Extensive set of ALTER TABLE commands, including change of table column type
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
  • Disk tables (CACHED TABLE) up to 8GB and text tables up to 2GB each
  • Size of each string or binary item only limited by memory
  • 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
Utilities
  • Powerful and compact java command line and GUI tools for database management
  • Tranfer tool for conversion of databases to / from other popular database managment systems


Features of H2 (1.1.109)

from http://www.h2database.com/html/features.html
Main Features
  • Very fast database engine
  • Free, with source code
  • Written in Java
  • Supports standard SQL, JDBC API
  • Embedded and Server mode, Clustering support
  • Strong security features
  • The PostgreSQL ODBC driver can be used
  • Multi version concurrency
Additional Features
  • Disk based or in-memory databases and tables, read-only database support, temporary tables
  • Transaction support (read committed and serializable transaction isolation), 2-phase-commit
  • Multiple connections, table level locking
  • Cost based optimizer, using a genetic algorithm for complex queries, zero-administration
  • Scrollable and updatable result set support, large result set, external result sorting, functions can return a result set
  • Encrypted database (AES or XTEA), SHA-256 password encryption, encryption functions, SSL
SQL Support
  • Support for multiple schemas, information schema
  • Referential integrity / foreign key constraints with cascade, check constraints
  • Inner and outer joins, subqueries, read only views and inline views
  • Triggers and Java functions / stored procedures
  • Many built-in functions, including XML and lossless data compression
  • Wide range of data types including large objects (BLOB/CLOB) and arrays
  • Sequence and autoincrement columns, computed columns (can be used for function based indexes)
  • ORDER BY, GROUP BY, HAVING, UNION, LIMIT, TOP
  • Collation support, users, roles
  • Compatibility modes for IBM DB2, Apache Derby, HSQLDB, MS SQL Server, MySQL, Oracle, and PostgreSQL.
Security Features
  • Includes a solution for the SQL injection problem
  • User password authenticated uses SHA-256 and salt
  • User passwords are never transmitted in plain text over the network (even when using insecure connections)
  • All database files (including script files that can be used to backup data) can be encrypted using AES-256 and XTEA encryption algorithms
  • The remote JDBC driver supports TCP/IP connections over SSL/TLS
  • The built-in web server supports connections over SSL/TLS
  • Passwords can be sent to the database using char arrays instead of Strings
Other Features and Tools
  • Small footprint (smaller than 1 MB), low memory requirements
  • Multiple index types (b-tree, tree, hash)
  • Support for multi-dimensional indexes
  • CSV (comma separated values) file support
  • Support for linked tables, and a built-in virtual 'range' table
  • EXPLAIN PLAN support, sophisticated trace options
  • Database closing can be delayed or disabled to improve the performance
  • Web-based Console application (English, German, partially French and Spanish) with autocomplete
  • The database can generate SQL script files
  • Contains a recovery tool that can dump the contents of the data file
  • Support for variables (for example to calculate running totals)
  • Automatic re-compilation of prepared statements
  • Uses a small number of database files
  • Uses a checksum for each record and log entry for data integrity
  • Well tested (high code coverage, randomized stress tests)

0 comments:

Post a Comment