<?xml version="1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
Initial Developer: H2 Group
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /><title>
History
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
<script type="text/javascript" src="navigation.js"></script>
</head><body onload="frameMe();">
<table class="content"><tr class="content"><td class="content"><div class="contentDiv">
<h1>History and Roadmap</h1>
<a href="#history">
History of this Database Engine</a><br />
<a href="#changelog">
Change Log</a><br />
<a href="#roadmap">
Roadmap</a><br />
<a href="#supporters">
Supporters</a><br />
<br /><a name="history"></a>
<h2>History of this Database Engine</h2>
The development of H2 was started in May 2004,
but it was first published on December 14th 2005.
The author of H2, Thomas Mueller, is also the original developer of Hypersonic SQL.
In 2001, he joined PointBase Inc. where he created PointBase Micro.
At that point, he had to discontinue Hypersonic SQL, but then the HSQLDB Group was formed
to continued to work on the Hypersonic SQL codebase.
The name H2 stands for Hypersonic 2; however H2 does not share any code with
Hypersonic SQL or HSQLDB. H2 is built from scratch.
<br /><a name="changelog"></a>
<h2>Change Log</h2>
<h3>Version 1.0 (Current)</h3>
<h3>Version 1.0 / 2007-08-02</h3><ul>
<li>A new tool to help translation has been implemented: src/tools/org/h2/tools/i18n/PrepareTranslation.
This tool can detect delta changes in the original (English) and prepends '#' in translation if the original
text was changed. It can also extract text from the user documentation (however, it is incomplete).
</li><li>The error messages (src/main/org/h2/res/_*.*) can now be translated.
</li><li>Part of the documentation has been translated to Japanese by Yusuke Fukushima.
</li><li>Some Unicode characters where not supported as identifier name.
Thanks Yusuke Fukushima for reporting this problem.
</li><li>The default value DEFAULT_MAX_LENGTH_INPLACE_LOB has been changed from 128 to 1024.
</li><li>A server that implements the PostgreSQL protocol is now included and documented.
That means, the PostgreSQL ODBC driver can be used to access a H2 database.
See in the documentation for details.
</li><li>The experimental H2 ODBC driver has been removed.
</li><li>The default value for h2.defaultMaxMemoryUndo is now 50000.
This avoids out of memory problems when using large transactions,
however large transactions are slower because they are buffered to disk.
To disable, use -Dh2.defaultMaxMemoryUndo=2000000000.
</li><li>Support for regular expression function REGEXP_REPLACE(expression, regex, replacement)
and regular expression LIKE: expression REGEXP matchExpression. However, indexes are not yet used.
</li><li>The old view implementation has been removed.
</li><li>The SysTray tool has been removed, because JDK 1.6 has native support for system tray icons.
Use the Console tool (org.h2.tools.Console) automatically installs a system tray icon if JDK 1.6 is used.
</li><li>H2 Console: In the last release, the shutdown button did not work. Fixed.
</li><li>Referential integrity can now be disabled using
SET REFERENTIAL_INTEGRITY FALSE. It can also be disable only
for one table using ALTER TABLE SET REFERENTIAL_INTEGRITY FALSE.
</li><li>The Backup and Restore tools, and the BACKUP command did not back up LOBs when
h2.lobFilesInDirectories was enabled. Fixed.
</li><li>Calculation of cache memory usage has been improved.
</li><li>In some situations record were released too late from the cache. Fixed.
</li><li>The cache size is now measured in KB instead of blocks of 128 byte.
</li><li>CREATE TABLE ... AS SELECT now needs less memory. While inserting the rows, the undo
log is temporarily disabled. This avoid out of memory problems when creating large tables.
</li><li>The per session undo log can now be disabled. This setting is useful for bulk operations
that don't need to be atomic, like bulk delete or update.
</li><li>The database file could get corrupted when there was an OutOfMemoryException in the middle of inserting a row.
</li><li>Optimization for WHERE NOT(...) and WHERE [NOT] booleanFlagColumn.
This can be disabled using the system property h2.optimizeNot.
</li><li>Optimization for conditions like WHERE A=B AND B=X (A=X is added). This often appears in joins.
This can be disabled using the system property h2.optimizeTwoEquals.
</li><li>Documentation: the source code in 'Compacting a Database' was incorrect. Fixed.
</li><li>In the H2 Console, result sets could not be modified because the default result set type is now forward only.
For H2, now uses scrollable result sets. Also for other databases, but only when the query starts with @EDIT.
</li><li>Views using UNION did not work correctly. Fixed.
</li><li>Function tables did not work with views and EXPLAIN. Fixed.
</li></ul>
<h3>Version 1.0 / 2007-07-12 (Build 55)</h3><ul>
<li>Support for the system property baseDir. This works for embedded databases as well. The setting is supported
by the H2 Console using -Dh2.baseDir or -baseDir
</li><li>LIKE ESCAPE did not work correctly if the pattern was % or _, followed by an escape character, followed by %. Fixed.
</li><li>PostgreSQL compatibility: SET SEARCH_PATH, SERIAL, CURRENT_USER, E'text', $1.
</li><li>In some situations, when many tables with LOB columns were modified (ALTER TABLE), large objects were deleted. Fixed.
</li><li>CREATE TABLE AS SELECT .. UNION .. did not work. Fixed.
</li><li>New column ID for INFORMATION_SCHEMA.INDEXES, SEQUENCES, USERS, ROLES, RIGHTS,
FUNCTION_ALIASES, SCHEMATA, VIEWS, CONSTRAINTS, CONSTANTS, DOMAINS, TRIGGERS.
</li><li>If large result sets (backed by a temporary file) where not closed, the file was not deleted.
Now, the default result set type is FETCH_FORWARD. This means temp files are deleted
automatically (without having to close the result set explicitly). But it also means
ResultSet.beforeFirst can only be called for scrollable result sets. To create a scrollable result set,
use Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).
</li><li>PreparedStatement.getMetaData is now implemented.
</li><li>Now PreparedStatement.setBigDecimal(..) can be called with an object of a derived class
if the system property h2.allowBigDecimalExtensions is set to true.
</li><li>The default database name in the documentation is now jdbc:h2:~/test. Like this, the database
is stored in the user home directory (system property user.home). Of course storing the database
in the current working directory (as in jdbc:h2:test) still works. Using the user directory avoids
the problem that the database is not found if the application runs in another directory than
the console.
</li><li>The SQL statement SET ASSERT has been deprecated, because Constants.CHECK is now final.
To disable assertions (and improve performance), the system property h2.check can be used.
Executing SET ASSERT still works, but has no effect.
</li><li>In the READ_COMMITTED mode, when the multi-threaded kernel is enabled,
read locks are now acquired but released immediately after a query.
</li><li>The default trace level for JdbcDataSourceFactory was DEBUG, so when using data sources a trace file
was always created. Now the default trace level is ERROR, and the file is only created if necessary.
</li><li>PooledConnection.getConnection took a long time if only one connection was open at any time. Fixed.
</li><li>Referential integrity violation: Two different SQL states are now used for missing parent / existing child.
</li><li>DatabaseEventListener.exceptionThrown has a new parameter: SQL
</li><li>For compatibility reasons, the catalog name can now be used in queries: SELECT * FROM TESTDB.PUBLIC.TEST
</li><li>If SHUTDOWN IMMEDIATELY was called, then the connection was not closed and the database
opened from somebody else at the same time, in some cases this could result in errors with LOB files. Fixed.
</li><li>The new view implementation is now enabled by default.
To use the old implementation, set the system property 'h2.indexOld' to true
(java -Dh2.indexOld=true ..., or in source code Constants.INDEX_OLD = true).
If no problems are found, the old implementation will be removed in the next release.
The old implementation does not work with multi-level nested temporary views
(select * from (select * from (select * from test))).
</li><li>The new view implementation did not work with < and <= comparison,
and did not allow conditions on aggregates or grouped columns (HAVING). Fixed.. Fixed.
</li><li>Both view implementations did not work with multiple levels of nested temporary views (FROM (SELECT...)). Fixed.
</li><li>The H2 Console can now be run as a standalone web application,
or it can be embedded as a servlet into any existing web application. To build the
'H2 Console' web application, execute 'ant warConsole'.
See src/tools/org/h2/server/web and src/tools/WEB-INF for details.
This web application has been tested with Tomcat and Jetty.
</li><li>Deleting database files didn't work for Windows if the database was on the root directory of a drive.
</li><li>The Polish translation is available. Thanks a lot to Tomek!
</li><li>Windows service: the CLASSPATH was not included when starting the service. Fixed.
</li>
</ul>
<h3>Version 1.0 / 2007-06-17 (Build 50)</h3><ul>
<li>File names starting with ~ are now in the user directory (Java system property user.home)
</li><li>New Console starter application uses the JDK 1.6 system tray functionality if available,
or a simple AWT frame for other platforms. To try it out, execute
java org.h2.tools.Console. Feedback is welcome.
This console starter application is not the default yet,
but the plan is to remove the SysTray tool in the future.
</li><li>New method Csv.write(Writer writer, ResultSet rs)
</li><li>If a Reader or InputStream of a LOB is not closed, the LOB can not be deleted (embedded mode only).
The exception is typically 'Error while renaming file'). As a workaround, set the system property
'h2.lobCloseBetweenReads' to true to close the LOB files between read operations.
However this slows down reading.
</li><li>Server mode: the server stack trace was included in SQLException messages. Fixed.
</li><li>Views support has been partially re-implemented. Views are up to 6 times faster.
Compared to regular queries, only 20% overhead. Because this is a bigger change,
it is not enabled by default. To enable it, set the system property 'h2.indexNew' to true
(java -Dh2.indexNew=true ..., or in source code Constants.INDEX_NEW = true).
If no problems are found, this will be enabled by default in the next release.
</li><li>In INSERT and MERGE statements, each column may only be specified once now.
</li><li>For most IOExceptions now the file name is included in the error message.
</li><li>A java.util.Date object is now converted to a TIMESTAMP in the JDBC API. Previously it was converted to a DATE.
</li><li>After calling SHUTDOWN and closing the connection and a superfluous error message appeared in the trace file. Fixed.
</li><li>In many situations, views did not use an index if they could have. Fixed. Also the explain plan for views works now.
</li><li>The table id (important for LOB files) is now included in INFORMATION_SCHEMA.TABLES.
</li><li>When using DISTINCT, ORDER BY a function works now as long as it is in the column list.
</li><li>Support for the data type CHAR. The difference to VARCHAR is: trailing spaces are ignored. This
data type is supported for compatibility with other databases and older applications.
</li><li>The aggregate function COUNT(...) now returns a long instead of an int.
</li><li>The 'ordering' of data types was not always correct, for example an operation involving REAL and DOUBLE produced a result of type REAL. Fixed.
</li><li>CSV tool: If the same instance was used for reading and writing, the tool wrote the column names twice. Fixed.
</li><li>Compatibility: Support for the data type notation CHARACTER VARYING.
</li><li>Can now ORDER BY -1 (meaning order by first column, descending), and ORDER BY ? (parameterized column number).
</li><li>Databases with invalid linked tables (for example, because the target database is not accessible) can now be opened.
Old table links don't work however.
</li><li>There was a small memory leak in the trace module. One object per opened connection was kept in a hash map.
</li><li>Linked tables can now emit UPDATE statements if 'EMIT UPDATES' is specified in the CREATE LINKED
TABLE statement. So far, updating a row always deleted the old row and then inserted the new row.
</li><li>In the last release, the H2 Console opened two connection when logging into a database,
and only closed one connection when logging out. Fixed.
</li><li>New functions LEAST and GREATEST to get the smallest or largest value from a list.
</li>
</ul>
<h3>Version 1.0 / 2007-04-29 (Build 46)</h3><ul>
<li>Unnamed private in-memory database (jdbc:h2:mem:) were not 'private' as documented. Fixed.
</li><li>Autocomplete in the Console application: now the result frame scrolls to the top when the list is updated.
</li><li>GROUP BY expressions did not work correctly in subqueries. Fixed.
</li><li>New function TABLE to define ad-hoc (temporary) tables in a query.
This also solves problems with variable-size IN(...) queries:
instead of SELECT * FROM TEST WHERE ID IN(?, ?, ...) you can now write:
SELECT * FROM TABLE(ID INT=?) X, TEST WHERE X.ID=TEST.ID
In this case, the index is used.
</li><li>New data type ARRAY. Actually it was there before, but is now documented
and better tested (however it must still be considered experimental).
The java.sql.Array implementation is incomplete, but setObject(1, new Object[]{...})
and getObject(..) can be used. New functions ARRAY_GET and ARRAY_LENGTH.
</li><li>SimpleResultSet now has some basic data type conversion features.
</li><li>When using JDK 1.5 or later, and switching on h2.lobFilesInDirectories,
the performance for creating LOBs was bad. This has been fixed, however
creating lots of LOBs it is still faster when the setting is switched off.
</li><li>A problem with multiple unnamed dynamic tables (FROM (SELECT...)) has been fixed.
</li><li>Appending 'Z' to a timestamp did not have an effect. Now it is interpreted as +00:00 (GMT).
</li><li>The BACKUP command is better tested and documented.
This means hot backup (online backup) is now possible.
</li><li>The old 'Backup' tool is now called 'Script' (as the SQL statement).
</li><li>There are new 'Backup' and 'Restore' tools that work with database files directly.
</li><li>The complete syntax for referential and check constraints is now supported
when written as part of the column definition, behind PRIMARY KEY.
</li><li>CASE WHEN ... returned the wrong result when the condition evaluated to NULL.
</li><li>The new function LINK_SCHEMA simplifies linking all tables of a schema.
</li><li>SCRIPT DROP now also drops aliases (Java functions) if they exist.
</li><li>For encrypted databases, the trace option can no longer be enabled manually by creating a file.
</li><li>For linked tables, NULL in the unique key is now supported.
</li><li>For read-only databases, temp files are now created in the default temp directory instead
of the database directory.
</li><li>Sending CLOB data was slow in some systems when using the server version. Fixed.
</li><li>CSVWRITE now returns the number of rows written.
</li><li>The data type of NULLIF was NULL if the first expression was a column. Now the data type is set correctly.
</li><li>Indexes (and other related objects) for local temporary tables where not dropped
when the session was closed. Fixed.
</li><li>ALTER TABLE did not work for tables with computed columns.
</li><li>SQLException.getCause of the now works for JDK 1.4 and higher.
</li><li>If the index file was deleted, an error was logged in the .trace.db file. This is no longer done.
</li><li>The Portuguese (Europe) translation is available. Thanks a lot to Antonio Casqueiro!
</li><li>The error message for invalid views has been improved (the root cause is included in the message now).
</li><li>IN(SELECT ...) was not working correctly if the subquery returned a NULL value. Fixed.
</li><li>DROP ALL OBJECTS did not drop constants.
</li><li>DROP ALL OBJECTS dropped the role PUBLIC, which was wrong. Fixed.
</li><li>CASE was parsed as a function if the expression was in (). Fixed.
</li><li>When ORDER BY was used together with DISTINCT, it was required to type the column
name exactly in the select list and the order list exactly in the same way.
This is not required any longer.
</li></ul>
<h3>Version 1.0 / 2007-03-04 (Build 44)</h3><ul>
<li>System sequences (automatically created sequences for IDENTITY or AUTO_INCREMENT columns) are now
random (UUIDs) to avoid clashes when merging databases using RUNSCRIPT.
</li><li>The precision for linked tables was not correct for some data types, for example VARCHAR. Fixed.
</li><li>Many problems and bugs in the XA support (package javax.sql) have been fixed.
</li><li>Now the server tool (org.h2.tools.Server) terminates with an exit code if a problem occured.
</li><li>The JDBC driver is now loaded if the JdbcDataSource class is loaded.
</li><li>After renaming a user the password becomes invalid. This is now documented.
</li><li>XAResource.recover didn't work. Fixed.
</li><li>XAResource.recover did throw an exception with the code XAER_OUTSIDE if there
was no connection. Now the code is XAER_RMERR.
</li><li>SCRIPT did not work correctly with BLOB or CLOB data. Fixed.
</li><li>BACKUP TO 'test.zip' now works with encrypted databases and CLOB and BLOB data.
</li><li>The function CASE WHEN ... didn't convert the returned value to the same data type,
resulting in unexpected behavior in many cases. Fixed.
</li><li>Truncating a table is now allowed if the table references another table
(but still not allowed if the table is references by another table).
</li><li>ORDER BY picked the wrong column if the same column name (but with a different table name)
was used twice in the select list.
</li><li>When a subquery was used in the select list of a query, and GROUP BY was used at the same time,
a NullPointerException could occur. Fixed.
</li><li>ORDER BY did not work when DISTINCT was used at the same time in some situations. Fixed.
</li><li>When using IN(...) on a case insensitive column (VARCHAR_IGNORECASE),
an incorrect optimization was made and the result was wrong sometimes.
</li></ul>
<h3>Version 1.0 / 2007-01-30 (Build 41)</h3><ul>
<li>Experimental online backup feature using the SQL statement BACKUP TO 'fileName'.
This creates a backup in the form of a zip file. Unlike the SCRIPT TO command, the data tables are not locked.
</li><li>When using the server mode, temporary files for large LOB values are now deleted when the result set is closed.
This also means that LOBs become unavailable after closing the result, however this is according to the specs.
</li><li>It was possible that SUM throws a class cast exception if the parameter was a conditional expression.
</li><li>Benchmark: Added a multi-client test case, BenchB (similar to TPC-B).
</li><li>Compatibility: SCHEMA_NAME.SEQUENCE_NAME.NEXTVAL now works as expected.
</li><li>The Console is now translated to Hungarian thanks to Andras Hideg,
and to Indonesian thanks to Joko Yuliantoro
</li><li>XAConnection: A NullPointerException was thrown if addConnectionEventListener was called before opening the connection.
</li><li>In case the result set of a subquery was re-used, an exception was throws if the subquery result did not fit in memory.
Now the result is not re-used in this case. Generally, large subqueries should be avoided for performance reasons.
</li><li>The command "drop all objects delete files" did not work on linux if the database name was lower case.
</li><li>When setting the URL to an empty string the DataSource now throws an better exception.
</li><li>Parsing of LIKE .. ESCAPE did not stop at the expected point. Fixed.
</li><li>Can now use UUID columns as generated key values. However, the UUID column must be the primary key.
</li><li>Improved the Javadoc documentation. Now unsupported features are marked with [Not supported],
and partially supported features are [Partially supported].
</li><li>The forum subscriptions (the emails sent from the forum) now works.
</li></ul>
<h3>Version 1.0 / 2007-01-17 (Build 40)</h3><ul>
<li>Setting the collation (SET COLLATOR) was very slow on some systems (up to 24 seconds).
Thanks a lot to Martina Nissler for finding this problem!
</li><li>The Console is now translated to Japanese thanks to IKEMOTO, Masahiro (ikeyan (at) arizona (dot) ne (dot) jp)
</li><li>The database engine can now be compiled with JDK 1.3 using ant codeswitch.
There are still some limitations, and the ant script to build the jar does not work yet.
</li><li>Fixed a problem where data in the log file was not written to the data file (recovery failure) after a crash,
if an index was deleted previously.
</li><li>SCRIPT NODATA now writes the row count for each table (this simplifies comparing databases).
</li><li>Selecting a column using the syntax schemaName.tableName.columnName did not work in all cases.
</li><li>Can now parse timestamps with timezone information (Z or +/-hh:mm) and dates before year 1.
However dates before year 1 are not formatted correctly (this is a Java problem).
</li><li>When stopping the TCP server from an application and immediately afterwards staring it again
using a different TCP password, an exception was thrown sometimes.
</li><li>Now PreparedStatement.setBigDecimal(..) can only be called with an object
of type java.math.BigDecimal. Derived classes are not allowed any more. Many thanks to
Maciej Wegorkiewicz for finding this problem.
</li><li>It was possible to manipulate values in the byte array after calling PreparedStatement.setBytes, and this
could lead to problems if the same byte array was used again. Now the byte array is copied if required.
</li><li>Date, time and timestamp objects were cloned in cases where it was not required. Fixed.
</li></ul>
<h3>Version 1.0 / 2007-01-02 (Build 36)</h3><ul>
<li>It was possible to drop the sequence of a temporary tables with DROP ALL OBJECTS, resulting in a null pointer exception afterwards.
</li><li>Prepared statements with non-constant functions such as CURRENT_TIMESTAMP() did not get re-evaluated if the result of the function changed. Fixed.
</li><li>The (relative or absolute) directory where the script files are stored or read can now be changed using the system property h2.scriptDirectory
</li><li>Client trace files now created in the directory 'trace.db' and no longer the application directory.
This can be changed using the system property h2.clientTraceDirectory.
</li><li>In some situations the log file got corrupt if the process was terminated while the database was opening.
</li><li>Using ;RECOVER=1 in the database URL threw a syntax exception. Fixed.
</li><li>If a CLOB or BLOB was deleted in a transaction and the database crashed before the transaction was committed or rolled back,
the object was lost if it was large. Fixed.
</li><li>Now using ant-build.properties. The jdk is automatically updated when using ant codeswitch...
</li><li>Cluster: Now the server can detect if a query is read-only, and in this case the result is only read from the first cluster node.
However, there is currently no load balancing made to avoid problems with transactions / locking.
</li><li>Many settings are now initialized from system properties and can be changed on the command line without having
recompile the database. See Advances / Settings Read from System Properties.
</li><li>H2 is now available in Maven. The groupId is com.h2database, the artifactId h2 and the version 1.0.20061217.
To create the maven artifacts yourself, use 'ant mavenUploadLocal' and 'ant mavenBuildCentral'.
</li></ul>
<h3>Version 1.0 / 2006-12-17 (Build 34)</h3><ul>
<li>Can be compiled with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
</li><li>The unit test of OpenJPA works now.
</li><li>Unfortunately, the Hibernate dialect has changed due to a change in the meta data in the last release
(INFORMATION_SCHEMA.SEQUENCES).
</li><li>String.toUpperCase and toLowerCase can not be used to process SQL, as they depend on the current locale.
Now using toUpperCase(Locale.ENGLISH) or Character.toUpperCase(..)
</li><li>Table aliases are now supported in DELETE and UPDATE. Example: DELETE FROM TEST T0.
</li><li>The RunScript tool can now include other files using a new syntax: @INCLUDE fileName.
It was already possible to do that using embedded RUNSCRIPT statements, but not remotely.
</li><li>When the database URL contains ;RECOVER=1 then the index file is now deleted if it was not closed before.
</li><li>The scale of a NUMERIC(1) column is now 0. It used to be 32767.
</li><li>Deleting old temp files now uses a phantom reference queue. Generally, temp files should now be deleted
earlier.
</li><li>Opening a large database is now much faster when even when using the default log mode (LOG=1),
if the database was closed previously.
</li><li>Very large BLOB and CLOB data can now be used with the server and the cluster mode.
The objects will temporarily be buffered on the client side if they are larger than some
size (currently 64 KB).
</li><li>PreparedStatement.setObject(x, y, Types.OTHER) does now serialize the object in every case
(even for Integer).
</li><li>EXISTS subqueries with parameters were not re-evaluated when the prepared statement was
reused. This could lead to incorrect results.
</li><li>Support for indexed parameters in PreparedStatements: update test set name=?2 where id=?1
</li></ul>
<h3>Version 1.0 / 2006-12-03 (Build 32)</h3><ul>
<li>The SQL statement COMMENT did not work as expected. Many bugs have been fixed in this area.
If you already have comments in the database, it is recommended to backup and restore the database,
using the Backup and RunScript tools or the SQL commands SCRIPT and RUNSCRIPT.
</li><li>Mixing certain data types in an operation, for example VARCHAR and TIMESTAMP, now converts both expressions to TIMESTAMP.
This was a problem when comparing a string against a date.
</li><li>Improved performance for CREATE INDEX. This method is about 10% faster if the original order is random.
</li><li>Server: If an object was already closed on the server side because it was too 'old' (not used for a long time),
even calling close() threw an exception. This is not done any more as it is not a problem.
</li><li>Optimization: The left and right side of a AND and OR conditions are now ordered by the expected cost.
</li><li>There was a bug in the database encryption algorithm. The initialization vector was not correctly calculated, and
pattern of repeated encrypted bytes where generated for empty blocks in the file. This has been is fixed.
The security of the data itself was not compromised, but this was not the intended behavior.
If you have an encrypted database, you will need to decrypt the database using the org.h2.tools.ChangePassword
(using the old database engine), and encrypt the database using the new engine. Alternatively, you can
use the Backup and RunScript tools or the SQL commands SCRIPT and RUNSCRIPT.
</li><li>New connection time setting CACHE_TYPE=TQ to use the 2Q page replacement algorithm.
The overall performance seems to be a bit better when using 2Q. Also, 2Q should be more resistant to table scan.
</li><li>Change behavior: If both sides of a comparison are parameters with unknown data type, then an exception is thrown now.
The same happens for UNION SELECT if both columns are parameters.
</li><li>New system function SESSION_ID().
</li><li>Deeply nested views where slow to execute, because the query was parsed many times. Fixed.
</li><li>The service wrapper is now included in the default installation and documented.
</li><li>Java functions returning a result set (such as CSVREAD) now can be used in a SELECT statement like a table.
The function is still called twice (first to get the column list at parse time, and then at execute time).
The behavior has been changed: Now first call contains the values if set, but the connection URL is different
(jdbc:columnlist:connection instead of jdbc:default:connection).
</li></ul>
<h3>Version 1.0 / 2006-11-20 (Build 31)</h3><ul>
<li>SCRIPT: New option BLOCKSIZE to split BLOB and CLOB data into separate blocks, to avoid OutOfMemory problems.
</li><li>When using the READ_COMMITTED isolation level, a transaction now waits until there are no write locks
when trying to read data. However, it still does not add a read lock.
</li><li>INSERT INTO ... SELECT ... and ALTER TABLE with CLOB and/or BLOB did not work. Fixed.
</li><li>CSV tool: the methods setFieldSeparatorWrite and setRowSeparatorWrite where not accessible.
The API has been changed, there is now only one static method, getInstance().
</li><li>ALTER TABLE ADD did throw a strange message if the table contained views. Now the message is better,
but it is still not possible to do that if views on this table exist.
</li><li>Direct links to the Javadoc were not working.
</li><li>CURRVAL and NEXTVAL functions: New optional sequence name parameter.
</li><li>ALTER TABLE: If there was a foreign key in another table that references to the change table,
the constraint was dropped.
</li><li>The input streams returned from this database did not always read all data when calling
read. This was not wrong, but unexpected. Now changed that read behaves like readFully.
</li><li>The default cache size is now 65536 pages instead of 32768. Like this the memory used for caching
is about 10 MB. Before, it used to be about 6 MB.
</li><li>Inserting rows into linked tables did not work for HSQLDB when the value was NULL.
Now NULL values are inserted if the value for a column was not set in the insert statement.
</li><li>New optimization to reuse subquery results. Can be disabled with SET OPTIMIZE_REUSE_RESULTS 0.
</li><li>Oracle SYNONYM tables are now listed in the H2 Console.
</li><li>CREATE LINKED TABLE didn't work for Oracle SYNONYM tables. Fixed.
</li><li>Dependencies between the JDBC client and the database have been removed.
The h2client.jar is now 295 KB. There are still some classes included that should not be there.
The theoretical limit is about 253 KB currently.
</li><li>When using the server version, when not closing result sets or using nested DatabaseMetaData result sets,
the connection could break. This has been fixed.
</li><li>EXPLAIN... results are now formatted on multiple lines so it is easier to read them.
</li><li>The Spanish translation was completed by Miguel Angel. Thanks a lot! Translations to other languages are always welcome.
</li><li>New system function SCHEMA() to get the current schema.
</li><li>New SQL statement SET SCHEMA to change the current schema of this session.
</li><li>The Recovery tool has been improved. It now creates a SQL script file that can be executed directly.
</li><li>LENGTH now returns the precision for CLOB, BLOB, and BINARY (and is therefore faster).
</li><li>The built-in FTP server can now access a virtual directory stored in a database.
</li></ul>
<h3>Version 1.0 / 2006-11-03 (Build 30)</h3><ul>
<li>
Two simple full text search implementations (Lucene and native) are now included.
This is work in progress, and currently undocumented.
See test/org/h2/samples/fullTextSearch.sql.
To enable the Lucene implementation, you first need to install Lucene,
rename FullTextLucene.java.txt to *.java and call 'ant compile'.
</li><li>
Wide b-tree indexes (with large VARCHAR columns for example) could get corrupted. Fixed.
</li><li>
If a custom shutdown hook was installed, and the database was called at shutdown,
a NullPointException was thrown. Now, the following exception is thrown:
Database called at VM shutdown; add ";DB_CLOSE_ON_EXIT=FALSE" to the db URL to disable automatic database closing
</li><li>
If SHUTDOWN was called and DB_CLOSE_DELAY was set, the database was not closed.
This has been changed so that shutdown always closes the database.
</li><li>
Subqueries with order by outside the column list didn't work correctly. Example:
SELECT (SELECT NAME FROM TEST ORDER BY ID);
This is fixed now.
</li><li>
Linked Tables: Only the first column was linked when linking to PostgreSQL, or a subquery. Fixed.
</li><li>
Sequences: When the database is not closed normally, the value was not set correctly. Fixed.
</li><li>
The optimization for IN(SELECT...) was too aggressive; changes in the inner query where not accounted for.
</li><li>
Index names of referential constraints are now prefixed with the constraint name.
</li><li>
Blob.getBytes skipped the wrong number of bytes. Fixed.
</li><li>
Group by a function didn't work if a column list was specified in the select list. Fixed.
</li><li>
Improved search functionality in the HTML documentation.
</li><li>
Triggers can now be defined on a list of actions (for example: INSERT, UPDATE) instead of just one action per trigger.
</li><li>
On some systems (for example, some Linux VFAT and USB flash disk drivers), RandomAccessFile.setLength does not work correctly.
A workaround for this problem has been implemented.
</li><li>
DatabaseMetaData.getTableTypes now also returns SYSTEM TABLE and TABLE VIEW.
This was done for DbVisualizer. Please tell me if this breaks other applications or tools.
</li><li>
Java functions with Blob or Clob parameters are now supported.
</li><li>
LOCK_MODE 0 (READ_UNCOMMITTED) did not work when using multiple connections.
Fixed, however LOCK_MODE 0 should still not be used when using multiple connections.
</li><li>
New SQL statement COMMENT ON ... IS ...
</li><li>
Added a 'remarks' column to most system tables.
New system table INFORMATION_SCHEMA.TRIGGERS
</li><li>
PostgreSQL compatibility: Support for the date format 2006-09-22T13:18:17.061
</li><li>
MySQL compatibility: ResultSet.getString("PEOPLE.NAME") is now supported.
</li><li>
JDBC 4.0 driver auto discovery: When using JDK 1.6, Class.forName("org.h2.Driver") is no longer required.
</li></ul>
<h3>Version 1.0 / 2006-10-10 (Build 28)</h3><ul>
<li>
Redundant () in a IN subquery is now supported: where id in ((select id from test))
</li><li>
The multi-threaded kernel can not be enabled using SET MULTI_THREADED 1 or jdbc:h2:test;MULTI_THREADED=1.
A new tests has been written for this feature, but more tests are required, so this is still experimental.
</li><li>
Can now compile everything with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
</li><li>
A small FTP server is now included. Disabled by default. Intended as a simple mechanism to transfer data in ad-hoc networks.
</li><li>
GROUP BY an formula or function didn't work if the same expression was used in the select list. Fixed.
</li><li>
Reconnect didn't work after renaming a user if rights were granted for this user. Fixed.
</li><li>
Opening and closing connections in many threads sometimes failed because opening a session
was not correctly synchronized. Fixed.
</li><li>
Function aliases may optionally include parameter classes. Example:
CREATE ALIAS PARSE_INT2 FOR "java.lang.Integer.parseInt(java.lang.String, int)"
</li><li>
Support for UUID
</li><li>
Support for DOMAIN (user defined data types).
</li><li>
Could not re-connect to a database when ALLOW_LITERALS or COMPRESS_LOB was set. Fixed.
</li></ul>
<h3>Version 1.0 / 2006-09-24 (Build 27)</h3><ul>
<li>
New LOCK_MODE 3 (READ_COMMITTED). Table level locking, but only when writing (no read locks).
</li><li>
Connection.setTransactionIsolation and getTransactionIsolation now set / get the LOCK_MODE of the database.
</li><li>
New system function LOCK_MODE()
</li><li>
Optimizations for WHERE ... IN(...) and SELECT MIN(..), MAX(..) are now enabled by default, but can be disabled
in the application (Constants.OPTIMIZE_MIN_MAX, OPTIMIZE_IN)
</li><li>
LOBs are now automatically converted to files. Constants.AUTO_CONVERT_LOB_TO_FILES is now set to true by default,
however this can be disabled in the application.
</li><li>
Reading from compressed LOBs didn't work in some cases. Fixed.
</li><li>
CLOB / BLOB: Copying LOB data directly from one table to another, and altering a table with with LOBs did not work,
the BLOB data was deleted when the old table was deleted. Fixed.
</li><li>
Wide b-tree indexes (with large VARCHAR columns for example) with a long common prefix (where many
rows start with the same text) could get corrupted. Fixed.
</li><li>
For compatibility with Derby, the precision in a data type definition can now include K, M or G as in BLOB(10M).
</li><li>
CREATE TABLE ... AS SELECT ... is now supported.
</li><li>
DROP TABLE: Can now drop more than one column in one step: DROP TABLE A, B
</li><li>
CREATE SCHEMA: The authorization part is now optional.
</li><li>
Protection against SQL injection: New command SET ALLOW_LITERALS {NONE|ALL|NUMBERS}.
With SET ALLOW_LITERALS NONE, SQL injections are not possible because literals in SQL statements are rejected;
User input must be set using parameters ('?') in this case.
</li><li>
New concept 'Constants': New SQL statements CREATE CONSTANT and DROP CONSTANT. Constants can be used
where expressions can be used. New metadata table INFORMATION_SCHEMA.CONSTANTS.
Built-in constant function ZERO() to get the integer value 0.
</li><li>
New data type OTHER (alternative names OBJECT and JAVA_OBJECT). When using this data type,
Java Objects are automatically serialized and deserialized in the JDBC layer. Constants.SERIALIZE_JAVA_OBJECTS is
now true by default.
</li><li>
[NOT] EXISTS(SELECT ... EXCEPT SELECT ...) did not work in all cases. Fixed.
</li><li>
DatabaseMetaData.getProcedures and getProcedureColumns are implemented now.
INFORMATION_SCHEMA.FUNCTION_ALIASES was changed, and there is a new table
INFORMATION_SCHEMA.FUNCTION_COLUMNS.
</li><li>
As a workaround for a problem on (maybe misconfigured) Linux system, now use
InetAddress.getByName("127.0.0.1") instead of InetAddress.getLocalHost() to get the loopback address.
</li><li>
Functions returning a result set that are used like a table are now first called
(to get the column names) with null values (or 0 / false for primitive types)
as documented and not with any values even if they are constants.
</li><li>
Improved performance for MetaData calls. The table name is now indexed.
</li><li>
BatchUpdateException was not helpful, now includes the cause
</li><li>
Unknown setting in the database URL (which are most likely typos) are now detected and
an exception is thrown. Unknown settings in the connection properties however
(for example used by OpenOffice and Hibernate) are ignored.
</li><li>
Now the log size is automatically increased to at least 10% of the data file.
This solves a problem with 'Too many open files' for very large databases.
</li><li>
Backup and Runscript tools now support options (H2 only)
</li></ul>
<h3>Version 1.0 / 2006-09-10 (Build 26)</h3><ul>
<li>
Updated the performance test so that Firebird can be tested as well.
</li><li>
Now an exception is thrown when the an overflow occurs for mathematical operations (sum, multiply and so on)
for the data type selected.
</li><li>
Correlated subqueries: It is now possible to use columns of the outer query
in the select list of the inner query (not only in the condition).
</li><li>
The script can now be compressed. Syntax: SCRIPT TO 'file' COMPRESSION {DEFLATE|LZF|ZIP|GZIP}.
</li><li>
New setting SET COMPRESS_LOB {NO|LZF|DEFLATE} to automatically compress BLOBs and CLOBs.
This is helpful when storing highly redundant textual data (XML, HTML).
</li><li>
ROWNUM didn't always work as expected when using subqueries. This is fixed now.
However, there is no standard definition for ROWNUM, for situations where you want to limit
the number of rows (specially for offset), the standardized LIMIT [OFFSET] should be used
</li><li>
Deleting many rows from a table with a self-referencing constraint with 'on delete cascade' did not work.
Now referential constraints are checked after the action is performed.
</li><li>
The cross references in the SQL grammar docs where broken in the last release.
</li><li>
There was a bug in the default settings for the Console, the setting
;hsqldb.default_table_type=cached was added to the H2 database instead of the HSQLDB database.
</li><li>
Workaround for an OpenOffice.org problem: DatabaseMetaData calls with schema name pattern
now return the objects for the PUBLIC schema if the schema name pattern was an empty string.
</li><li>
Until now, unknown connection properties where ignored (for OpenOffice compatibility).
This is not a good solution because typos are not detected.
This behavior is still the default but it can be disabled by adding
;IGNORE_UNKNOWN_SETTINGS=FALSE to the database URL.
However this is not the final solution.
</li><li>
Workaround for an OpenOffice problem: OpenOffice Base calls DatabaseMetaData functions with "" as the catalog.
As the catalog in H2 is not an empty string, the result should strictly be empty; however to make OpenOffice work,
the catalog is not used as a criteria in this case.
</li><li>
New SQL statement DROP ALL OBJECTS [DELETE FILES] to drop all tables, sequences and so on. If DELETE FILES is added,
the database files are deleted as well when the last connection is closed. The DROP DATABASE statement found in other systems
means drop another database, while DROP ALL OBJECTS means remove all data from the current database.
</li><li>
When running a script that contained referential or unique constraints, and if the indexes for those constraints are not created by the user,
a internal exception could occur. This is fixed. Also, the script command will no longer write the internal indexes used into the file.
</li><li>
SET IGNORECASE is now supported for compatibility with HSQLDB, and because using collations (SET COLLATION) is very slow on JDKs (JDK 1.5)
</li><li>
ORDER BY an expression didn't work when using GROUP BY at the same time.
</li></ul>
<h3>Version 1.0 / 2006-08-31 (Build 25)</h3><ul>
<li>
In some situations, wide b-tree indexes (with large VARCHAR columns for example) could get corrupted. Fixed.
</li><li>
ORDER BY was broken in the last release when using table aliases. Fixed.
</li></ul>
<h3>Version 0.9 / 2006-08-28</h3><ul>
<li>
DATEDIFF on seconds, minutes, hours did return different results in certain timezones (half-hour timezones)
in certain situations. Fixed.
</li><li>
LOB files where not deleted when the table was truncated or dropped. This is now done.
</li><li>
When large strings or byte arrays where inserted into a LOB (CLOB or BLOB), or if the data was stored
using PreparedStatement.setBytes or setString, the data was stored in-place (no separate files where created).
This is now implemented (that means distinct files are created in every case), however disabled by default.
It is possible to enable this option with Constants.AUTO_CONVERT_LOB_TO_FILES = true
</li><li>
New setting MAX_LENGTH_INPLACE_LOB
</li><li>
When reading from a table with many small CLOB columns, in some situations
an ArrayIndexOutOfBoundsException was thrown. Fixed.
</li><li>
Optimization for MIN and MAX (but currently disabled by default until after release 1.0):
Queries such as SELECT MIN(ID), MAX(ID)+1, COUNT(*) FROM TEST now use an index if one is available.
To enable manually, set Constants.OPTIMIZE_MIN_MAX = true in your application.
</li><li>
Subqueries: Constant subqueries are now only evaluated once (like this was before).
</li><li>
Linked tables: Improved compatibility with other databases and improved error messages.
</li><li>
Linked tables: The table name is no longer quoted when accessing the foreign database.
This allows to use schema names, and possibly subqueries as table names (when used in queries).
</li><li>
Outer join: There where some incompatibilities with PostgreSQL and MySQL with more complex outer joins. Fixed.
</li></ul>
<h3>Version 0.9 / 2006-08-23 (Build 23)</h3><ul>
<li>
Bugfix for LIKE: If collation was set (SET COLLATION ...), it was ignored when using LIKE. Fixed.
</li><li>
Optimization for IN(value list) and IN(subquery). The optimization is disabled by default,
but can be switched on by setting Constants.OPTIMIZE_IN = true
(no compilation required). Currently, the IN(value,...) is converted to BETWEEN min AND max,
that means it is not converted to an inner join.
</li><li>
Arithmetic overflows in can now be detected for sql types TINYINT, SMALLINT, INTEGER, BIGINT.
Operations: addition, subtraction, multiplication, negation. By default, this detection is switched off
but can be switched on by setting Constants.OVERFLOW_EXCEPTIONS = true
(no compilation required).
</li><li>
Referential integrity: fixed a stack overflow problem when a deleted record caused (directly or indirectly)
deleting other rows in the same table via cascade delete.
</li><li>
Database opening: sometimes opening a database was very slow because indexes were re-created.
even when the index was consistent. This lead to long database opening times for some databases. Fixed.
</li><li>
Local temporary tables where not included in the meta data. Fixed.
</li><li>
Very large transactions are now supported. The undo log of large transactions is buffered to disk.
The maximum size of undo log records to be kept in-memory can be changed with SET MAX_MEMORY_UNDO.
Currently, this feature is disabled by default (MAX_MEMORY_UNDO is set to Integer.MAX_VALUE by default) because it needs more testing.
It will be enabled after release 1.0.
The current implementation has a limitation: changes to tables without a primary key can not be buffered to disk
</li><li>
Improvements in the autocomplete feature. Thanks a lot to James Devenish for his very valuable feedback and testing!
</li><li>
Bugfix for an outer join problem (too many rows where returned for a combined inner join / outer join).
</li><li>
Date and time constants outside the valid range (February 31 and so on) are no longer accepted.
</li></ul>
<h3>Version 0.9 / 2006-08-14 (Build 21)</h3><ul>
<li>
SET LOG 0 didn't work (except if the log level was set to some other value before). Fixed.
</li><li>
Outer join optimization. An outer join now evaluates expressions like (ID=1) in the where clause early.
However expressions like ID IS NULL or NOT ID IS NOT NULL in the where clause can not be optimized.
</li><li>
Autocomplete is now improved.
Schemas and quoted identifiers are not yet supported.
There are some browser incompatibilities,
for example Enter doesn't work in Opera (but clicking on the item does),
clicking on the item doesn't work in Internet Explorer (but Enter works).
However everything should work in Firefox. Please report any incompatibilities.
</li><li>
Source code to support H2 in Resin is included (see src/tools, package com.caucho.jdbc).
For the complete patch, see http://forum.caucho.com/node/61
</li><li>
Space is better re-used after deleting many records.
</li><li>
Umlauts and Chinese characters are now supported in
identifier names (table name, column names and so on).
</li><li>
Fixed a problem when comparing BIGINT values with constants.
</li><li>
NULL handling was wrong for: true IN (true, null). Fixed.
</li><li>
It was not possible to cancel a select statement with a (temporary) view. Fixed.
</li></ul>
<h3>Version 0.9 / 2006-07-29 (Build 18)</h3><ul>
<li>
ParameterMetaData is now implemented (mainly to support getParameterCount).
</li><li>
Improved performance for Statement.getGeneratedKeys().
</li><li>
SCRIPT: The system generated indexes are now not included in the script file because they will be created automatically.
Also, the drop statements for generated sequences are not included in the script any longer.
</li><li>
Bugfix: IN(NULL) didn't return NULL in every case. Fixed.
</li><li>
Bugfix: DATEDIFF didn't work correctly for hour, minute and second if one of the dates was before 1970. Fixed.
</li><li>
Shutdown TCP Server: The Server tool didn't throw / print a meaningful exception
if the server was not running or if the password was wrong. Fixed.
</li><li>
Experimental auto-complete functionality in the H2 Console.
Does not yet work for all cases. Press [Ctrl]+[Space] to activate, and [Esc] to deactivate it.
</li><li>
SELECT EXCEPT (or MINUS) did not work for some cases. Fixed.
</li><li>
DATEDIFF now returns a BIGINT and not an INT
</li><li>
1.0/3.0 is now 0.33333... and not 0.3 as before. The scale of a DECIMAL division is
adjusted automatically (up to current scale + 25).
</li><li>
'SELECT * FROM TEST' can now be written as 'FROM TEST SELECT *'
to enable improved autocomplete (column names can be suggested in the
SELECT part of the query because the tables are known if the FROM part comes first).
SELECT is now a keyword.
</li><li>
H2 Console: First version of an autocomplete feature.
</li><li>
DATEADD didn't work for milliseconds. Fixed.
</li><li>
New parameter schemaName in Trigger.init.
</li><li>
New method DatabaseEventListener.init to pass the database URL.
</li><li>
Opening a database that was not closed previously is now faster
(specially if using a database URL of the form jdbc:h2:test;LOG=2)
Applying the redo-log is buffered and writing to the file is ordered.
</li><li>
Could not connect to a database that was closing at the same time.
</li><li>
C-style block comments /* */ are not parsed correctly when they contain * or /
</li></ul>
<h3>Version 0.9 / 2005-12-13</h3><ul>
<li>
First public release.
</li></ul>
<br /><a name="roadmap"></a>
<h2>Roadmap</h2>
<h3>Highest Priority</h3>
<ul>
<li>Improve test code coverage
</li><li>More fuzz tests
</li><li>Test very large databases and LOBs (up to 256 GB)
</li><li>Test multi-threaded in-memory db access
</li></ul>
<h3>In Version 1.1</h3>
<ul>
<li>ALTER TABLE on a table with a LOB could result in 'Cannot delete file' on some systems. Fixed.
</li><li>Change Constants.DEFAULT_MAX_MEMORY_UNDO to 10000 (and change the docs). Test.
</li><li>Enable and document optimizations, LOB files in directories
</li><li>Special methods for DataPage.writeByte / writeShort and so on
</li><li>Index organized tables CREATE TABLE...(...) ORGANIZATION INDEX (store in data file) (probably file format changes are required for rowId)
</li><li>Change the default for NULL || 'x' to NULL
</li><li>Change the default isolation level to read committed
</li></ul>
<h3>Priority 1</h3>
<ul>
<li>MVCC (Multi Version Concurrency Control)
</li><li>RECOVER=1 should automatically recover, =2 should run the recovery tool if required
</li><li>More tests with MULTI_THREADED=1
</li><li>Improve performance for create table (if this is possible)
</li><li>Test with Spatial DB in a box / JTS (http://docs.codehaus.org/display/GEOS/SpatialDBBox)
</li><li>Document how to use H2 with PHP (generic database API)
</li><li>Optimization: result set caching (like MySQL)
</li><li>Server side cursors
</li><li>Row level locking
</li><li>Read-only databases inside a jar (splitting large files to speed up random access)
</li><li>System table: open sessions and locks of a database
</li><li>Function in management db: list open connections and databases of a (TCP) server
</li><li>Fix right outer joins
</li><li>Full outer joins
</li><li>Long running queries / errors / trace system table
</li><li>Migrate database tool (also from other database engines)
</li><li>Shutdown compact
</li><li>Optimization of distinct with index: select distinct name from test
</li><li>Forum: email notification doesn't work? test or disable or document
</li><li>Document server mode, embedded mode, web app mode, dual mode (server+embedded)
</li><li>Stop the server: close all open databases first
</li><li>SET variable { TO | = } { value | 'value' | DEFAULT }
</li><li>Running totals: select @running:=if(@previous=t.ID,@running,0)+t.NUM as TOTAL, @previous:=t.ID
</li><li>Option to globally disable / enable referential integrity checks
</li><li>Support SET REFERENTIAL_INTEGRITY {TRUE|FALSE}
</li><li>Better support large transactions, large updates / deletes: use less memory
</li><li>Better support large transactions, large updates / deletes: allow tables without primary key
</li><li>Support Oracle RPAD and LPAD(string, n[, pad]) (truncate the end if longer)
</li><li>Allow editing NULL values in the Console
</li><li>Updatable result sets: DatabaseMetaData.ownUpdatesAreVisible = true (for insert, delete, update)
Simple solution: automatically calls 'refresh' when the result was changed.
Compare with other databases.
</li></ul>
<h3>Priority 2</h3>
<ul>
<li>Support OSGi: http://oscar-osgi.sourceforge.net, http://incubator.apache.org/felix/index.html
</li><li>Change LOB mechanism (less files, keep index of lob files, point to files and row, delete unused files earlier, maybe bundle files into a tar file)
</li><li>Set the database in an 'exclusive' mode (restrict to one user at a time)
</li><li>Clustering: recovery needs to becomes fully automatic. Global write lock feature.
</li><li>System table / function: cache usage
</li><li>User defined aggregate functions
</li><li>Add a migration guide (list differences between databases)
</li><li>Optimization: automatic index creation suggestion using the trace file?
</li><li>Compression performance: don't allocate buffers, compress / expand in to out buffer
</li><li>Connection pool manager
</li><li>Start / stop server with database URL
</li><li>Rebuild index functionality (other than delete the index file)
</li><li>Don't use deleteOnExit (bug 4513817: File.deleteOnExit consumes memory)
</li><li>Console: add accesskey to most important commands (A, AREA, BUTTON, INPUT, LABEL, LEGEND, TEXTAREA)
</li><li>Feature: a setting to delete the the log or not (for backup)
</li><li>Test with Sun ASPE1_4; JEE Sun AS PE1.4
</li><li>Test performance again with SQL Server, Oracle, DB2
</li><li>Test with dbmonster (http://dbmonster.kernelpanic.pl/)
</li><li>Test with dbcopy (http://dbcopyplugin.sourceforge.net)
</li><li>Find a tool to view a text file >100 MB, with find, page up and down (like less)
</li><li>Implement, test, document XAConnection and so on
</li><li>Web site: meta keywords, description, get rid of frame set
</li><li>Pluggable data type (for compression, validation, conversion, encryption)
</li><li>CHECK: find out what makes CHECK=TRUE slow, move to CHECK2
</li><li>Improve recovery: improve code for log recovery problems (less try/catch)
</li><li>Log linear hash index changes, fast open / close
</li><li>Index usage for (ID, NAME)=(1, 'Hi'); document
</li><li>Suggestion: include jetty as Servlet Container (like LAMP)
</li><li>Trace shipping to server
</li><li>Performance / server mode: use UDP optionally?
</li><li>Version check: docs / web console (using javascript), and maybe in the library (using TCP/IP)
</li><li>Aggregates: support MEDIAN
</li><li>Web server classloader: override findResource / getResourceFrom
</li><li>Cost for embedded temporary view is calculated wrong, if result is constant
</li><li>Comparison: pluggable sort order: natural sort
</li><li>Count index range query (count(*) where id between 10 and 20)
</li><li>Eclipse plugin
</li><li>iReport to support H2
</li><li>Implement missing JDBC API (CallableStatement,...)
</li><li>Compression of the cache
</li><li>Run H2 Console inside servlet (pass-through servlet of fix the JSP / app)
</li><li>Groovy Stored Procedures (http://groovy.codehaus.org/Groovy+SQL)
</li><li>Include SMPT (mail) server (at least client) (alert on cluster failure, low disk space,...)
</li><li>Make the jar more modular
</li><li>Drop with restrict (currently cascade is the default)
</li><li>JSON parser and functions
</li><li>Option for Java functions: constant/isDeterministic to allow early evaluation when all parameters are constant
</li><li>Automatic collection of statistics (auto ANALYZE)
</li><li>Procedural language
</li><li>Server: client ping from time to time (to avoid timeout - is timeout a problem?)
</li><li>Copy database: Tool with config GUI and batch mode, extensible (example: compare)
</li><li>Document, implement tool for long running transactions using user defined compensation statements
</li><li>Support SET TABLE DUAL READONLY
</li><li>Don't write stack traces for common exceptions like duplicate key to the log by default
</li><li>Setting for MAX_QUERY_TIME (default no limit?)
</li><li>GCJ: what is the state now?
</li><li>Convert large byte[]/Strings to streams in the JDBC API (asap).
</li><li>Use Janino to convert Java to C++
</li><li>Reduce disk space usage (Derby uses less disk space?)
</li><li>Events for: Database Startup, Connections, Login attempts, Disconnections, Prepare (after parsing), Web Server (see http://docs.openlinksw.com/virtuoso/fn_dbev_startup.html)
</li><li>Optimization: Log compression
</li><li>Compatibility: in MySQL, HSQLDB, /0.0 is NULL; in PostgreSQL, Derby: Division by zero
</li><li>Functional tables should accept parameters from other tables (see FunctionMultiReturn)
SELECT * FROM TEST T, P2C(T.A, T.R)
</li><li>Custom class loader to reload functions on demand
</li><li>Test http://mysql-je.sourceforge.net/
</li><li>Close all files when closing the database (including LOB files that are open on the client side)
</li><li>Test Connection Pool http://jakarta.apache.org/commons/dbcp
</li><li>Implement Statement.cancel for server connections
</li><li>Profiler option or profiling tool to find long running and often repeated queries (using DatabaseEventListener API)
</li><li>Function to read/write a file from/to LOB
</li><li>Allow custom settings (@PATH for RUNSCRIPT for example)
</li><li>Performance test: read the data (getString) and use column names to get the data
</li><li>EXE file: maybe use http://jsmooth.sourceforge.net
</li><li>SELECT ... FOR READ WAIT [maxMillisToWait]
</li><li>Automatically delete the index file if opening it fails
</li><li>Performance: Automatically build in-memory indexes if the whole table is in memory
</li><li>H2 Console: The webclient could support more features like phpMyAdmin.
</li><li>The HELP information schema can be directly exposed in the Console
</li><li>Maybe use the 0x1234 notation for binary fields, see MS SQL Server
</li><li>KEY_COLUMN_USAGE (http://dev.mysql.com/doc/refman/5.0/en/information-schema.html, http://www.xcdsql.org/Misc/INFORMATION_SCHEMA%20With%20Rolenames.gif)
</li><li>Support Oracle CONNECT BY in some way: http://www.adp-gmbh.ch/ora/sql/connect_by.html, http://philip.greenspun.com/sql/trees.html
</li><li>SQL 2003 (http://www.wiscorp.com/sql_2003_standard.zip)
</li><li>http://www.jpackage.org
</li><li>Version column (number/sequence and timestamp based)
</li><li>Optimize getGeneratedKey: send last identity after each execute (server).
</li><li>Date: default date is '1970-01-01' (is it 1900-01-01 in the standard / other databases?)
</li><li>Test and document UPDATE TEST SET (ID, NAME) = (SELECT ID*10, NAME || '!' FROM TEST T WHERE T.ID=TEST.ID);
</li><li>Better space re-use in the files after deleting data (shrink the files)
</li><li>Max memory rows / max undo log size: use block count / row size not row count
</li><li>Index summary is only written if log=2; maybe write it also when log=1 and everything is fine (and no in doubt transactions)
</li><li>Support 123L syntax as in Java; example: SELECT (2000000000*2)
</li><li>Implement point-in-time recovery
</li><li>Memory database: add a feature to keep named database open until 'shutdown'
</li><li>Use the directory of the first script as the default directory for any scripts run inside that script
</li><li>Include the version name in the jar file name
</li><li>Optimize IN(...), IN(select), ID=? OR ID=?: create temp table and use join
</li><li>LIKE: improved version for larger texts (currently using naive search)
</li><li>Auto-reconnect on lost connection to server (even if the server was re-started) except if autocommit was off and there was pending transaction
</li><li>The Script tool should work with other databases as well
</li><li>Deferred integrity checking (DEFERRABLE INITIALLY DEFERRED)
</li><li>Automatically convert to the next 'higher' data type whenever there is an overflow.
</li><li>Throw an exception when the application calls getInt on a Long (optional)
</li><li>Default date format for input and output (local date constants)
</li><li>Cache collation keys for performance
</li><li>Convert OR condition to UNION or IN if possible
</li><li>ValueInt.convertToString and so on (remove Value.convertTo)
</li><li>Support custom Collators
</li><li>Document ROWNUM usage for reports: SELECT ROWNUM, * FROM (subquery)
</li><li>Clustering: Reads should be randomly distributed or to a designated database on RAM
</li><li>Clustering: When a database is back alive, automatically synchronize with the master
</li><li>Standalone tool to get relevant system properties and add it to the trace output.
</li><li>Support mixed clustering mode (one embedded, the other server mode)
</li><li>Support 'call proc($1=value)' (PostgreSQL, Oracle)
</li><li>HSQLDB compatibility: "INSERT INTO TEST(name) VALUES(?); SELECT IDENTITY()"
</li><li>Shutdown lock (shutdown can only start if there are no logins pending, and logins are delayed until shutdown ends)
</li><li>Automatically delete the index file if opening it fails
</li><li>DbAdapters http://incubator.apache.org/cayenne/
</li><li>JAMon (proxy jdbc driver)
</li><li>Console: Allow setting Null value; Alternative display format two column (for copy and paste as well)
</li><li>Console: Improve editing data (Tab, Shift-Tab, Enter, Up, Down, Shift+Del?)
</li><li>Console: Autocomplete Ctrl+Space inserts template
</li><li>Google Code http://code.google.com/p/h2database/issues/list#
</li><li>Simplify translation ('Donate a translation')
</li><li>Option to encrypt .trace.db file
</li><li>Write Behind Cache on SATA leads to data corruption
See also http://sr5tech.com/write_back_cache_experiments.htm
and http://www.jasonbrome.com/blog/archives/2004/04/03/writecache_enabled.html
</li><li>Functions with unknown return or parameter data types: serialize / deserialize
</li><li>Test if idle TCP connections are closed, and how to disable that
</li><li>Try using a factory for Row, Value[] (faster?), http://javolution.org/, alternative ObjectArray / IntArray
</li><li>Auto-Update feature for database, .jar file
</li><li>ResultSet SimpleResultSet.readFromURL(String url): id varchar, state varchar, released timestamp
</li><li>RANK() and DENSE_RANK(), Partition using OVER()
</li><li>ROW_NUMBER (not the same as ROWNUM)
</li><li>Partial indexing (see PostgreSQL)
</li><li>BUILD should fail if ant test fails
</li><li>http://rubyforge.org/projects/hypersonic/
</li><li>DbVisualizer profile for H2
</li><li>Add comparator (x === y) : (x = y or (x is null and y is null))
</li><li>Try to create trace file even for read only databases
</li><li>Add a sample application that runs the H2 unit test and writes the result to a file (so it can be included in the user app)
</li><li>Count on a column that can not be null would be optimized to COUNT(*)
</li><li>Table order: ALTER TABLE TEST ORDER BY NAME DESC (MySQL compatibility)
</li><li>Backup tool should work with other databases as well
</li><li>Console: -ifExists doesn't work for the console. Add a flag to disable other dbs
</li><li>Maybe use Fowler Noll Vo hash function
</li><li>Improved full text search (supports LOBs, reader / tokenizer / filter).
</li><li>Performance: Update in-place
</li><li>Check if 'FSUTIL behavior set disablelastaccess 1' improves the performance (fsutil behavior query disablelastaccess)
</li><li>Java static code analysis: http://pmd.sourceforge.net/
</li><li>Java static code analysis: http://www.eclipse.org/tptp/
</li><li>Compatibility for CREATE SCHEMA AUTHORIZATION
</li><li>Implement Clob / Blob truncate and the remaining functionality
</li><li>Maybe close LOBs after closing connection
</li><li>Tree join functionality
</li><li>Support alter table add column if table has views defined
</li><li>Add multiple columns at the same time with ALTER TABLE .. ADD .. ADD ..
</li><li>Support trigger on the tables information_schema.tables and ...columns
</li><li>Add H2 to Gem (Ruby install system)
</li><li>API for functions / user tables
</li><li>Order conditions inside AND / OR to optimize the performance
</li><li>Support linked JCR tables
</li><li>Make sure H2 is supported by Execute Query: http://executequery.org/
</li><li>Read InputStream when executing, as late as possible (maybe only embedded mode). Problem with re-execute.
</li><li>Full text search: min word length; store word positions
</li><li>FTP Server: Implement a client to send / receive files to server (dir, get, put)
</li><li>FTP Server: Implement SFTP / FTPS
</li><li>Add an option to the SCRIPT command to generate only portable / standard SQL
</li><li>Test Dezign for Databases (http://www.datanamic.com)
</li><li>Fast library for parsing / formatting: http://javolution.org/
</li><li>Updatable Views (simple cases first)
</li><li>Improve create index performance
</li><li>Support ARRAY data type
</li><li>Implement more JDBC 4.0 features
</li><li>H2 Console: implement a servlet to allow simple web app integration
</li><li>Support TRANSFORM / PIVOT as in MS Access
</li><li>Sequence: PostgreSQL compatibility (rename, create) (http://www.postgresql.org/docs/8.2/static/sql-altersequence.html)
</li><li>SELECT * FROM (VALUES (...), (...), ....) AS alias(f1, ...)
</li><li>Support updatable views with join on primary keys (to extend a table)
</li><li>File_Read / File_Store funktionen: FILE_STORE('test.sql', ?), FILE_READ('test.sql')
</li><li>Public interface for functions (not public static)
</li><li>Autocomplete: if I type the name of a table that does not exist (should say: syntax not supported)
</li><li>Autocomplete: schema support: "Other Grammar","Table Expression","{[schemaName.]tableName | (select)} [[AS] newTableAlias]
</li><li>Functions: options readonly, deterministic (pure, always return the same value)
</li><li>Document FTP server, including -ftpTask option to execute / kill remote processes
</li><li>Add jdbcx to the javadocs
</li><li>Shrink the data file without closing the database (if the end of the file is empty)
</li><li>Delay reading the row if data is not required
</li><li>Eliminate undo log records if stored on disk (just one pointer per block, not per record)
</li><li>Feature matrix like here: http://www.inetsoftware.de/products/jdbc/mssql/features/default.asp.
</li><li>Updatable result set on table without primary key or unique index
</li><li>Use LinkedList instead of ArrayList where applicable
</li><li>Optimization: (A=B AND B=C) > (A=B AND B=C AND A=C)
</li><li>Support % operator (modulo)
</li><li>Large subqueries: close them when the main query is closed, not earlier (so result can be reused)
</li><li>Support 1+'2'=3, '1'+'2'='12' (MS SQL Server compatibility)
</li><li>Support nested transactions
</li><li>Add a benchmark for big databases, and one for many users
</li><li>Compression in the result set (repeating values in the same column)
</li><li>Improve command line consistency (+/- options, or true false options)
</li><li>Allow to use the catalog name in statements: [[catalog.]schema.]object
</li><li>Support curtimestamp (like curtime, curdate)
</li><li>Support ANALYZE {TABLE|INDEX} tableName COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
</li><li>Support Sequoia (Continuent.org)
</li><li>Dynamic length numbers / special methods for DataPage.writeByte / writeShort / Ronni Nielsen
</li><li>Pluggable tracing system, ThreadPool, (AvalonDB / deebee / Paul Hammant)
</li><li>Recursive Queries (see details)
</li><li>Use index on boolean flag (see details)
</li><li>Add build for embedded database only
</li><li>Release locks (shared or exclusive) on demand
</li><li>Support catalog names
</li><li>Add object id to metadata tables
</li><li>Support OUTER UNION
</li><li>Support Parameterized Views (similar to CSVREAD, but using just SQL for the definition)
</li><li>Implement a command line SQL utility similar to HenPlus: http://henplus.sourceforge.net
</li><li>A way (JDBC driver) to map an URL (jdbc:h2map:c1) to a connection object
</li><li>Build script for the embedded functionality only (h2embedded.jar)
</li><li>Option for SCRIPT to only process one or a set of tables, and append to a file
</li><li>Linked schema using CSV files: one schema for a directory of files; support indexes for CSV files
</li><li>Support using a unique index for IS NULL (including linked tables)
</li><li>Support linked tables to the current database
</li><li>Support dynamic linked schema (automatically adding/updating/removing tables)
</li><li>Compatibility with Derby: VALUES(1), (2); SELECT * FROM (VALUES (1), (2)) AS myTable(c1)
</li><li>Compatibility: # is the start of a single line comment (MySQL) but date quote (Access). Mode specific
</li><li>Run benchmarks with JDK 1.5, JDK 1.6, java -server
</li><li>Optimizations: Faster hash function for strings, byte arrays, big decimal
</li><li>Improve trace feature: add replay functionality
</li><li>DatabaseEventListener: callback for all operations (including expected time, RUNSCRIPT) and cancel functionality
</li><li>H2 Console / large result sets: use 'streaming' instead of building the page in-memory
</li><li>Benchmark: add a graph to show how databases scale (performance/database size)
</li><li>Implement a SQLData interface to map your data over to a custom object
</li><li>Extend H2 Console to run tools (show command line as well)
</li><li>Make DDL (Data Definition) operations transactional
</li><li>Sequence: add features [NO] MINVALUE, MAXVALUE, CACHE, CYCLE
</li><li>Allow execution time prepare for SELECT * FROM CSVREAD(?, 'columnNameString')
</li><li>Support multiple directories (on different hard drives) for the same database
</li><li>Server protocol: use challenge response authentication, but client sends hash(user+password) encrypted with response
</li><li>Support EXEC[UTE] (doesn't return a result set, compatible to MS SQL Server)
</li><li>GROUP BY and DISTINCT: support large groups (buffer to disk), do not keep large sets in memory
</li><li>Support native XML data type
</li><li>Support triggers with a string property or option: SpringTrigger, OSGITrigger
</li><li>Clustering: adding a node should be very fast and without interrupting clients (very short lock)
</li><li>Support materialized views (using triggers)
</li><li>Store dates in local timezone (portability of database files)
</li><li>Ability to resize the cache array when resizing the cache
</li><li>Automatic conversion from WHERE X>10 AND X>20 to X>20
</li><li>Time based cache writing (one second after writing the log)
</li><li>Write a H2 driver for http://db.apache.org/ddlutils/
</li><li>Index usage for REGEXP LIKE.
</li><li>Add a role DBA (like ADMIN).
</li><li>Automatic mode: jdbc:h2:auto: (embedded mode if possible, if not use server mode).
Problem: what to do when server stops while others are connected to it.
</li><li>Access rights: remember the owner of an object. COMMENT: allow owner of object to change it.
</li><li>Implement INSTEAD OF trigger.
</li></ul>
<h3>Not Planned</h3>
<ul>
<li>HSQLDB (did) support this: select id i from test where i>0 (other databases don't)
</li><li>String.intern (so that Strings can be compared with ==) will not be used because some VMs have problems when used extensively
</li></ul>
<br /><a name="supporters"></a>
<h2>Supporters</h2>
Many thanks for those who helped by finding and reporting bugs, gave valuable feedback,
spread the word and have translated this project. Also many thanks to the donors who contributed
via PayPal:
<ul>
<li>Florent Ramiere, France
</li><li>Pete Haidinyak, USA
</li><li>Jun Iyama, Japan
</li><li>Antonio Casqueiro, Portugal
</li></ul>
</div></td></tr></table></body></html>