history.html 86.3 KB
Newer Older
1 2 3 4 5
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<!--
Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
Initial Developer: H2 Group
-->
6
<html><head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /><title>
7
History
8
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
9 10 11 12 13 14
<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">
15
    History of this Database Engine</a><br />
16
<a href="#changelog">
17
    Change Log</a><br />
18
<a href="#roadmap">
19
    Roadmap</a><br />
20
<a href="#supporters">
21
    Supporters</a><br />
22

23
<br /><a name="history"></a>
24 25 26 27 28 29 30 31 32 33
<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.

34
<br /><a name="changelog"></a>
35 36 37
<h2>Change Log</h2>

<h3>Version 1.0 (Current)</h3>
38

39 40 41 42
<h3>Version 1.0 / 2007-04-20 (Build 45) TODO</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.
43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
</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.
78 79 80 81 82 83 84 85
</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
86 87
    name exactly in the select list and the order list exactly in the same way. 
    This is not required any longer.
88 89
</li></ul>

90
<h3>Version 1.0 / 2007-03-04 (Build 44)</h3><ul>
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
<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).
107
</li><li>ORDER BY picked the wrong column if the same column name (but with a different table name) 
108
    was used twice in the select list.
109
</li><li>When a subquery was used in the select list of a query, and GROUP BY was used at the same time,
110 111
    a NullPointerException could occur. Fixed.
</li><li>ORDER BY did not work when DISTINCT was used at the same time in some situations. Fixed.
112
</li><li>When using IN(...) on a case insensitive column (VARCHAR_IGNORECASE), 
113
    an incorrect optimization was made and the result was wrong sometimes.
114
</li></ul>
115

116
<h3>Version 1.0 / 2007-01-30 (Build 41)</h3><ul>
117 118
<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.
119
</li><li>When using the server mode, temporary files for large LOB values are now deleted when the result set is closed.
120
    This also means that LOBs become unavailable after closing the result, however this is according to the specs.
121 122 123 124
</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, 
125
    and to Indonesian thanks to Joko Yuliantoro
126 127
</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.
128
    Now the result is not re-used in this case. Generally, large subqueries should be avoided for performance reasons.
129 130 131 132 133
</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], 
134
    and partially supported features are [Partially supported].
135 136
</li><li>The forum subscriptions (the emails sent from the forum) now works.
</li></ul>
137

138
<h3>Version 1.0 / 2007-01-17 (Build 40)</h3><ul>
139
<li>Setting the collation (SET COLLATOR) was very slow on some systems (up to 24 seconds). 
140
    Thanks a lot to Martina Nissler for finding this problem!
141 142
</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_jdk13. 
143
    There are still some limitations, and the ant script to build the jar does not work yet.
144
</li><li>Fixed a problem where data in the log file was not written to the data file (recovery failure) after a crash, 
145
    if an index was deleted previously.
146 147 148
</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.
149
    However dates before year 1 are not formatted correctly (this is a Java problem).
150
</li><li>When stopping the TCP server from an application and immediately afterwards staring it again
151
    using a different TCP password, an exception was thrown sometimes.
152
</li><li>Now PreparedStatement.setBigDecimal(..) can only be called with an object
153 154
    of type java.math.BigDecimal. Derived classes are not allowed any more. Many thanks to
    Maciej Wegorkiewicz for finding this problem.
155
</li><li>It was possible to manipulate values in the byte array after calling PreparedStatement.setBytes, and this
156
    could lead to problems if the same byte array was used again. Now the byte array is copied if required.
157 158
</li><li>Date, time and timestamp objects were cloned in cases where it was not required. Fixed.
</li></ul>
159

160
<h3>Version 1.0 / 2007-01-02 (Build 36)</h3><ul>
161
<li>It was possible to drop the sequence of a temporary tables with DROP ALL OBJECTS, resulting in a null pointer exception afterwards.
162 163 164
</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. 
165
    This can be changed using the system property h2.clientTraceDirectory.
166 167 168
</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,
169
    the object was lost if it was large. Fixed.
170 171
</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.
172
    However, there is currently no load balancing made to avoid problems with transactions / locking.
173
</li><li>Many settings are now initialized from system properties and can be changed on the command line without having
174
    recompile the database. See Advances / Settings Read from System Properties.
175
</li><li>H2 is now available in Maven. The groupId is com.h2database, the artifactId h2 and the version 1.0.20061217.
176
    To create the maven artifacts yourself, use 'ant mavenUploadLocal' and 'ant mavenBuildCentral'.
177
</li></ul>
178

179
<h3>Version 1.0 / 2006-12-17 (Build 34)</h3><ul>
180
<li>Can be compiled with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
181 182
</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
183
    (INFORMATION_SCHEMA.SEQUENCES).
184
</li><li>String.toUpperCase and toLowerCase can not be used to process SQL, as they depend on the current locale.
185
    Now using toUpperCase(Locale.ENGLISH) or Character.toUpperCase(..)
186 187
</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.
188
    It was already possible to do that using embedded RUNSCRIPT statements, but not remotely.
189 190 191
</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
192
    earlier.
193
</li><li>Opening a large database is now much faster when even when using the default log mode (LOG=1),
194
    if the database was closed previously.
195
</li><li>Very large BLOB and CLOB data can now be used with the server and the cluster mode.
196 197
    The objects will temporarily be buffered on the client side if they are larger than some
    size (currently 64 KB).
198
</li><li>PreparedStatement.setObject(x, y, Types.OTHER) does now serialize the object in every case
199
    (even for Integer).
200
</li><li>EXISTS subqueries with parameters were not re-evaluated when the prepared statement was
201
    reused. This could lead to incorrect results.
202 203
</li><li>Support for indexed parameters in PreparedStatements: update test set name=?2 where id=?1
</li></ul>
204

205
<h3>Version 1.0 / 2006-12-03 (Build 32)</h3><ul>
206
<li>The SQL statement COMMENT did not work as expected. Many bugs have been fixed in this area.
207
    If you already have comments in the database, it is recommended to backup and restore the database,
208
    using the Backup and RunScript tools or the SQL commands SCRIPT and RUNSCRIPT.
209
</li><li>Mixing certain data types in an operation, for example VARCHAR and TIMESTAMP, now converts both expressions to TIMESTAMP.
210
    This was a problem when comparing a string against a date.
211 212
</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),
213
    even calling close() threw an exception. This is not done any more as it is not a problem.
214 215
</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 
216
    pattern of repeated encrypted bytes where generated for empty blocks in the file. This has been is fixed. 
217
    The security of the data itself was not compromised, but this was not the intended behavior.
218 219 220
    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.
221
</li><li>New connection time setting CACHE_TYPE=TQ to use the 2Q page replacement algorithm.
222
    The overall performance seems to be a bit better when using 2Q. Also, 2Q should be more resistant to table scan.
223
</li><li>Change behavior: If both sides of a comparison are parameters with unknown data type, then an exception is thrown now.
224
    The same happens for UNION SELECT if both columns are parameters.
225 226 227 228
</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.
229
    The function is still called twice (first to get the column list at parse time, and then at execute time). 
230
    The behavior has been changed: Now first call contains the values if set, but the connection URL is different
231
    (jdbc:columnlist:connection instead of jdbc:default:connection).
232
</li></ul>
233

234
<h3>Version 1.0 / 2006-11-20 (Build 31)</h3><ul>
235
<li>SCRIPT: New option BLOCKSIZE to split BLOB and CLOB data into separate blocks, to avoid OutOfMemory problems.
236
</li><li>When using the READ_COMMITTED isolation level, a transaction now waits until there are no write locks
237
    when trying to read data. However, it still does not add a read lock.
238 239
</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. 
240
    The API has been changed, there is now only one static method, getInstance().
241
</li><li>ALTER TABLE ADD did throw a strange message if the table contained views. Now the message is better,
242
    but it is still not possible to do that if views on this table exist.
243 244 245
</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,
246
    the constraint was dropped.
247
</li><li>The input streams returned from this database did not always read all data when calling
248
    read. This was not wrong, but unexpected. Now changed that read behaves like readFully.
249
</li><li>The default cache size is now 65536 pages instead of 32768. Like this the memory used for caching
250
    is about 10 MB. Before, it used to be about 6 MB.
251
</li><li>Inserting rows into linked tables did not work for HSQLDB when the value was NULL. 
252
    Now NULL values are inserted if the value for a column was not set in the insert statement.
253 254 255 256
</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.
257 258
    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.
259
</li><li>When using the server version, when not closing result sets or using nested DatabaseMetaData result sets,
260
    the connection could break. This has been fixed.
261 262 263 264 265 266 267 268
</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>
269

270
<h3>Version 1.0 / 2006-11-03 (Build 30)</h3><ul>
271 272 273 274 275 276
<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'.
277
</li><li>
278
    Wide b-tree indexes (with large VARCHAR columns for example) could get corrupted. Fixed.
279
</li><li>
280 281 282
    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
283
</li><li>
284 285
    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.
286
</li><li>
287 288 289
    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.
290
</li><li>
291
    Linked Tables: Only the first column was linked when linking to PostgreSQL, or a subquery. Fixed.
292
</li><li>
293
    Sequences: When the database is not closed normally, the value was not set correctly. Fixed.
294
</li><li>
295
    The optimization for IN(SELECT...) was too aggressive; changes in the inner query where not accounted for.
296
</li><li>
297
    Index names of referential constraints are now prefixed with the constraint name.
298
</li><li>
299
    Blob.getBytes skipped the wrong number of bytes. Fixed.
300
</li><li>
301
    Group by a function didn't work if a column list was specified in the select list. Fixed.
302
</li><li>
303
    Improved search functionality in the HTML documentation.
304
</li><li>
305
    Triggers can now be defined on a list of actions (for example: INSERT, UPDATE) instead of just one action per trigger.
306
</li><li>
307 308
    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.
309
</li><li>
310 311
    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.
312
</li><li>
313
    Java functions with Blob or Clob parameters are now supported.
314
</li><li>
315 316
    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.
317
</li><li>
318
    New SQL statement COMMENT ON ... IS ...
319
</li><li>
320 321
    Added a 'remarks' column to most system tables.
    New system table INFORMATION_SCHEMA.TRIGGERS
322
</li><li>
323
    PostgreSQL compatibility: Support for the date format 2006-09-22T13:18:17.061
324
</li><li>
325
    MySQL compatibility: ResultSet.getString("PEOPLE.NAME") is now supported.
326
</li><li>
327
    JDBC 4.0 driver auto discovery: When using JDK 1.6, Class.forName("org.h2.Driver") is no longer required.
328
</li></ul>
329

330
<h3>Version 1.0 / 2006-10-10 (Build 28)</h3><ul>
331 332
<li>
    Redundant () in a IN subquery is now supported: where id in ((select id from test))
333
</li><li>
334 335
    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.
336
</li><li>
337
    Can now compile everything with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
338
</li><li>
339
    A small FTP server is now included. Disabled by default. Intended as a simple mechanism to transfer data in ad-hoc networks.
340
</li><li>
341
    GROUP BY an formula or function didn't work if the same expression was used in the select list. Fixed.
342
</li><li>
343
    Reconnect didn't work after renaming a user if rights were granted for this user. Fixed.
344
</li><li>
345 346
    Opening and closing connections in many threads sometimes failed because opening a session
    was not correctly synchronized. Fixed.
347
</li><li>
348
    Function aliases may optionally include parameter classes. Example:
349
    CREATE ALIAS PARSE_INT2 FOR "java.lang.Integer.parseInt(java.lang.String, int)"
350
</li><li>
351
    Support for UUID
352
</li><li>
353
    Support for DOMAIN (user defined data types).
354
</li><li>
355
    Could not re-connect to a database when ALLOW_LITERALS or COMPRESS_LOB was set. Fixed.
356
</li></ul>
357

358
<h3>Version 1.0 / 2006-09-24 (Build 27)</h3><ul>
359 360
<li>
    New LOCK_MODE 3 (READ_COMMITTED). Table level locking, but only when writing (no read locks).
361
</li><li>
362
    Connection.setTransactionIsolation and getTransactionIsolation now set / get the LOCK_MODE of the database.
363
</li><li>
364
    New system function LOCK_MODE()
365
</li><li>
366 367
    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)
368
</li><li>
369 370
    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.
371
</li><li>
372
    Reading from compressed LOBs didn't work in some cases. Fixed.
373
</li><li>
374 375
    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.
376
</li><li>
377 378
    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.
379
</li><li>
380
    For compatibility with Derby, the precision in a data type definition can now include K, M or G as in BLOB(10M).
381
</li><li>
382
    CREATE TABLE ... AS SELECT ... is now supported.
383
</li><li>
384
    DROP TABLE: Can now drop more than one column in one step: DROP TABLE A, B
385
</li><li>
386
    CREATE SCHEMA: The authorization part is now optional.
387
</li><li>
388 389 390
    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.
391
</li><li>
392 393 394
    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.
395
</li><li>
396 397 398
    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.
399
</li><li>
400
    [NOT] EXISTS(SELECT ... EXCEPT SELECT ...) did not work in all cases. Fixed.
401
</li><li>
402 403 404
    DatabaseMetaData.getProcedures and getProcedureColumns are implemented now.
    INFORMATION_SCHEMA.FUNCTION_ALIASES was changed, and there is a new table
    INFORMATION_SCHEMA.FUNCTION_COLUMNS.
405
</li><li>
406 407
    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.
408
</li><li>
409 410 411
    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.
412
</li><li>
413
    Improved performance for MetaData calls. The table name is now indexed.
414
</li><li>
415
    BatchUpdateException was not helpful, now includes the cause
416
</li><li>
417 418 419
    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.
420
</li><li>
421 422
    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.
423
</li><li>
424
    Backup and Runscript tools now support options (H2 only)
425
</li></ul>
426

427
<h3>Version 1.0 / 2006-09-10 (Build 26)</h3><ul>
428 429
<li>
    Updated the performance test so that Firebird can be tested as well.
430
</li><li>
431 432
    Now an exception is thrown when the an overflow occurs for mathematical operations (sum, multiply and so on)
    for the data type selected.
433
</li><li>
434 435
    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).
436
</li><li>
437
    The script can now be compressed. Syntax: SCRIPT TO 'file' COMPRESSION {DEFLATE|LZF|ZIP|GZIP}.
438
</li><li>
439 440
    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).
441
</li><li>
442 443 444
    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
445
</li><li>
446
    Deleting many rows from a table with a self-referencing constraint with 'on delete cascade' did not work.
447
    Now referential constraints are checked after the action is performed.
448
</li><li>
449
    The cross references in the SQL grammar docs where broken in the last release.
450
</li><li>
451 452
    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.
453
</li><li>
454 455
    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.
456
</li><li>
457 458
    Until now, unknown connection properties where ignored (for OpenOffice compatibility).
    This is not a good solution because typos are not detected.
459
    This behavior is still the default but it can be disabled by adding
460 461
    ;IGNORE_UNKNOWN_SETTINGS=FALSE to the database URL.
    However this is not the final solution.
462
</li><li>
463 464 465
    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.
466
</li><li>
467 468 469
    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.
470
</li><li>
471 472
    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.
473
</li><li>
474
    SET IGNORECASE is now supported for compatibility with HSQLDB, and because using collations (SET COLLATION) is very slow on JDKs (JDK 1.5)
475
</li><li>
476
    ORDER BY an expression didn't work when using GROUP BY at the same time.
477
</li></ul>
478

479
<h3>Version 1.0 / 2006-08-31 (Build 25)</h3><ul>
480 481
<li>
    In some situations, wide b-tree indexes (with large VARCHAR columns for example) could get corrupted. Fixed.
482
</li><li>
483
    ORDER BY was broken in the last release when using table aliases. Fixed.
484
</li></ul>
485 486 487 488 489
    
<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.
490
</li><li>
491
    LOB files where not deleted when the table was truncated or dropped. This is now done.
492
</li><li>
493 494 495 496
    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
497
</li><li>
498
    New setting MAX_LENGTH_INPLACE_LOB
499
</li><li>
500 501
    When reading from a table with many small CLOB columns, in some situations
    an ArrayIndexOutOfBoundsException was thrown. Fixed.
502
</li><li>
503 504 505
    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.
506
</li><li>
507
    Subqueries: Constant subqueries are now only evaluated once (like this was before).
508
</li><li>
509
    Linked tables: Improved compatibility with other databases and improved error messages.
510
</li><li>
511 512
    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).
513
</li><li>
514
    Outer join: There where some incompatibilities with PostgreSQL and MySQL with more complex outer joins. Fixed.
515
</li></ul>
516

517
<h3>Version 0.9 / 2006-08-23 (Build 23)</h3><ul>
518 519
<li>
    Bugfix for LIKE: If collation was set (SET COLLATION ...), it was ignored when using LIKE. Fixed.
520
</li><li>
521 522 523 524
    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.
525
</li><li>
526 527 528 529
    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).
530
</li><li>
531 532
    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.
533
</li><li>
534 535
    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.
536
</li><li>
537
    Local temporary tables where not included in the meta data. Fixed.
538
</li><li>
539 540 541 542 543
    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
544
</li><li>
545
    Improvements in the autocomplete feature. Thanks a lot to James Devenish for his very valuable feedback and testing!
546
</li><li>
547
    Bugfix for an outer join problem (too many rows where returned for a combined inner join / outer join).
548
</li><li>
549
    Date and time constants outside the valid range (February 31 and so on) are no longer accepted.
550
</li></ul>
551

552
<h3>Version 0.9 / 2006-08-14 (Build 21)</h3><ul>
553 554
<li>
    SET LOG 0 didn't work (except if the log level was set to some other value before). Fixed.
555
</li><li>
556 557
    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.
558
</li><li>
559 560 561 562 563 564
    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.
565
</li><li>
566 567
    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
568
</li><li>
569
    Space is better re-used after deleting many records.
570
</li><li>
571
    Umlauts and Chinese characters are now supported in
572
    identifier names (table name, column names and so on).
573
</li><li>
574
    Fixed a problem when comparing BIGINT values with constants.
575
</li><li>
576
    NULL handling was wrong for: true IN (true, null). Fixed.
577
</li><li>
578
    It was not possible to cancel a select statement with a (temporary) view. Fixed.
579
</li></ul>
580

581
<h3>Version 0.9 / 2006-07-29 (Build 18)</h3><ul>
582 583
<li>
    ParameterMetaData is now implemented (mainly to support getParameterCount).
584
</li><li>
585
    Improved performance for Statement.getGeneratedKeys().
586
</li><li>
587 588
    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.
589
</li><li>
590
    Bugfix: IN(NULL) didn't return NULL in every case. Fixed.
591
</li><li>
592
    Bugfix: DATEDIFF didn't work correctly for hour, minute and second if one of the dates was before 1970. Fixed.
593
</li><li>
594 595
    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.
596
</li><li>
597 598
    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.
599
</li><li>
600
    SELECT EXCEPT (or MINUS) did not work for some cases. Fixed.
601
</li><li>
602
    DATEDIFF now returns a BIGINT and not an INT
603
</li><li>
604 605
    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).
606
</li><li>
607 608 609 610
    '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.
611
</li><li>
612
    H2 Console: First version of an autocomplete feature.
613
</li><li>
614
    DATEADD didn't work for milliseconds. Fixed.
615
</li><li>
616
    New parameter schemaName in Trigger.init.
617
</li><li>
618
    New method DatabaseEventListener.init to pass the database URL.
619
</li><li>
620 621 622
    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.
623
</li><li>
624
    Could not connect to a database that was closing at the same time.
625
</li><li>
626
    C-style block comments /* */ are not parsed correctly when they contain * or /
627
</li></ul>
628

629
<h3>Version 0.9 / 2006-07-14 (Build 16)</h3><ul>
630 631
<li>
    The regression tests are no longer included in the jar file. This reduces the size by about 200 KB.
632
</li><li>
633
    Fixed some bugs in the CSV tool. This tool should now work for most cases, but is still not fully tested.
634
</li><li>
635 636
    The cache size is now measured in blocks and no longer in rows. Manually setting
    the cache size is no longer necessary in most cases.
637
</li><li>
638
    Objects of unknown type are no longer serialized to a byte array (and deserialized when calling getObject on a byte array data type)
639
    by default. This behavior can be changed with Constants.SERIALIZE_JAVA_OBJECTS = true
640
</li><li>
641
    New column IS_GENERATED in the metadata tables SEQUENCES and INDEXES
642
</li><li>
643
    Optimization: deterministic subqueries are evaluated only once.
644
</li><li>
645
    An exception was thrown if a scalar subquery returned no rows. Now the NULL value is used in this case.
646
</li><li>
647
    IF EXISTS / IF NOT EXISTS implemented for the remaining CREATE / DROP statements.
648
</li><li>
649
    ResultSetMetaData.isNullable is now implemented.
650
</li><li>
651
    LIKE ... ESCAPE: The escape character may now also be an expression.
652
</li><li>
653
    Compatibility: TRIM(whitespace FROM string)
654
</li><li>
655
    Compatibility: SUBSTRING(string FROM start FOR length)
656
</li><li>
657
    CREATE VIEW now supports a column list: CREATE VIEW TEST_V(A, B) AS ...
658
</li><li>
659 660
    Compatibility: 'T', 'Y', 'YES', 'F', 'N', 'NO' (case insensitive) can now also be converted to boolean.
    This is allowed now: WHERE BOOLEAN_FIELD='T'=(ID>1)
661
</li><li>
662
    Optimization: data conversion of constants was not optimized. This is done now.
663
</li><li>
664 665
    Compatibility: Implemented a shortcut version to declare single column referential integrity:
    CREATE TABLE TEST(ID INT PRIMARY KEY, PARENT INT REFERENCES TEST)
666
</li><li>
667
    Issue #126: It is possible to create multiple primary keys for the same table.
668
</li><li>
669
    Issue #125: Foreign key constraints of local temporary tables are not dropped when the table is dropped.
670
</li><li>
671
    Issue #124: Adding a column didn't work when the table contains a referential integrity check.
672
</li><li>
673 674
    Issue #123: The connection to the server is lost if an abnormal exception occurs.
    Example SQL statement: select 1=(1,2)
675
</li><li>
676 677 678
    The H2 Console didn't parse statements containing '-' or '/' correctly. Fixed.
    Now uses the same facility to split a script into SQL statements for
    the RunScript tool, for the RUNSCRIPT command and for the H2 Console.
679
</li><li>
680
    DatabaseMetaData.getTypeInfo: BIGINT was returning AUTO_INCREMENT=TRUE, which is wrong. Fixed.
681
</li></ul>
682

683
<h3>Version 0.9 / 2006-07-01 (Build 14)</h3><ul>
684
<li>
685
    After dropping constraints and altering a table sometimes the database could not be opened. Fixed.
686
</li><li>
687
    Outer joins did not always use an index even if this was possible. Fixed.
688
</li><li>
689
    Issue #122: Using OFFSET in big result sets (disk buffered result sets) did not work. Fixed.
690
</li><li>
691
    Support DatabaseMetaData.getSuperTables (currently returns an empty result set in every case).
692
</li><li>
693 694
    Database names are no longer case sensitive for the Windows operating system,
    because there the files names are not case sensitive.
695
</li><li>
696
    If an index is created for a constraint, this index now belong to the constraint and is removed when removing the constraint.
697
</li><li>
698
    Issue #121: Using a quoted table or alias name in front of a column name (SELECT "TEST".ID FROM TEST) didn't work.
699
</li><li>
700
    Issue #120: Some ALTER TABLE statements didn't work when the table was in another than the main schema. Fixed.
701
</li><li>
702 703 704
    Issue #119: If a table with autoincrement column is created in another schema,
        it was not possible to connect to the database again.
        Now opening a database first sorts the script by object type.
705
</li><li>
706
    Issue #118: ALTER TABLE RENAME COLUMN doesn't work correctly. Workaround: don't use it.
707
</li><li>
708 709
    Cache: implemented a String cache and improved the Value cache. Now uses a weak reference
    to avoid OutOfMemory due to caching values.
710
</li><li>
711 712
    Server: changed the public API a bit to allow an application to deal easier with start problems.
    Now instead of Server.startTcpServer(args) use Server.createTcpServer(args).start();
713
</li><li>
714
    Issue #117: Server.start...Server sometimes returned before the server was started. Solved.
715
</li><li>
716
    Issue #116: Server: reduces memory usage. Reduced number of cached objects per connection.
717
</li><li>
718
    Improved trace messages, and trace now starts earlier (when opening the database).
719
</li><li>
720
    Simplified translation of the Web Console (a tool to convert the translation files to UTF-8).
721
</li><li>
722
    Newsfeed sample application (used to create the newsfeed and newsletter).
723
</li><li>
724
    New functions: MEMORY_FREE() and MEMORY_USED().
725
</li></ul>
726 727 728 729 730 731 732

<h3>Version 0.9 / 2006-06-16</h3><ul>
<li>
    Implemented distributing lob files into directories, and only keep up to 255 files in one directory.
    However this is disabled by default; it will be enabled the next time the file format changes
    (maybe not before 1.1). It can be enabled by the application by setting
    Constants.LOB_FILES_IN_DIRECTORIES = true;
733
</li><li>
734
    If a connection is closed while there is still an operation running, this operation is stopped (like when calling Statement.cancel).
735
</li><li>
736
    Issue #115: If three or more threads / connections are used, sometimes lock timeout exceptions can occur when they should not. Solved.
737
</li><li>
738
    Calling Server.start...Server now doesn't return until the server socket is ready to accept connections.
739
</li><li>
740 741
    Issue #112: Two threads could not open the same database at the same time. Solved.
    This was only a problem when the database was opened in a non-standard was, without using DriverManager.getConnection.
742
</li><li>
743
    Implemented DROP TRIGGER.
744
</li><li>
745
    Issue #113: Drop is now restricted: can only drop sequences / functions / tables if nothing depends on them.
746
</li><li>
747
    Issue #114: Support large index data size. Before, there was a limit of around 1000 bytes per row.
748
</li><li>
749
    Blob.getLength() and Clob.getLength() are now fast operations and don't read the whole object any longer.
750
</li><li>
751 752
    Issue #111: The catalog name in the DatabaseMetaData calls and Connection.getCatalog was lowercase;
    some applications may not work because they expect it to be uppercase.
753
</li><li>
754 755 756
    Issue #110: PreparedStatement.setCharacterStream(int parameterIndex, Reader reader, int length) and
    ResultSet.updateCharacterStream(...) didn't work correctly for 'length' larger than 0 and using Unicode characters
    higher than 127 (or 0). The number of UTF-8 bytes where counted instead of the number of characters.
757
</li><li>
758
    The catalog name is now uppercase, to conform the JDBC standard for DatabaseMetaData.storesUpperCaseIdentifiers().
759
</li><li>
760
    Creating or opening a small encrypted database is now a lot faster.
761
</li><li>
762
    New functions FORMATDATETIME and PARSEDATETIME.
763
</li><li>
764
    New XML encoding functions (XMLATTR, XMLNODE, XMLCOMMENT, XMLCDATA, XMLSTARTDOC, XMLTEXT).
765
</li><li>
766
    Performance: improved opening of a large databases (about 3 times faster now for 500 MB databases).
767
</li><li>
768
    Documented ALTER TABLE DROP COLUMN. The functionality was there already, but the documentation not.
769
</li></ul>
770

771
<h3>Version 0.9 / 2006-06-02 (Build 10)</h3><ul>
772 773
<li>
    Removed the GCJ h2-server.exe from download. It was not stable on Windows.
774
</li><li>
775
    Issue #109: ALTER TABLE ADD COLUMN can make the database unusable if the original table contained a IDENTITY column.
776
</li><li>
777 778
    New option to disable automatic closing of a database when the virtual machine exits.
    Database URL: jdbc:h2:test;db_close_on_exit=false
779
</li><li>
780
    New event: DatabaseEventListener.closingDatabase() is called before closing the database.
781
</li><li>
782 783
    Connection.getCatalog() now returns the database name (CALL DATABASE()).
    This name is also used in all system tables and DatabaseMetaData calls where applicable.
784
</li><li>
785 786
    The function DATABASE() now return the short name of the database (without path),
    and 'Unnamed' for anonymous in-memory databases.
787
</li><li>
788 789
    Issue #108: There is a concurrency problem when multi threads access the same database at the same time,
    and one is closing the connection and the other is executing CHECKPOINT at the exact the same time. Fixed.
790
</li><li>
791 792
    Statements containing LIKE are now re-compiled when executed. Depending on the data,
    an index on the column is used or not.
793
</li><li>
794 795
    Issue# 107: When executing scripts that contained inserts with many columns, an OutOfMemory error could occur.
    The problem was usage of shared Strings (String.substring). Now each String value is copied if required, releasing memory.
796
</li><li>
797
    Issue #106: SET commands where not persisted if they where the first DDL commands for a connection. Fixed.
798
</li><li>
799 800
    Issue #105: RUNSCRIPT (the command) didn't commit after each command if autocommit was on, therefore
    large scripts run out of memory. Now it automatically commits.
801
</li><li>
802
    Automatic starting of a web browser for Mac OS X should work now.
803
</li><li>
804 805
    Starting the server is not a bit more intuitive. Just setting the -baseDir option for example will still start all servers.
    By default, -tcp, -web, -browser and -odbc are started.
806
</li><li>
807
    Issue #104: A HAVING condition on a column that was not in the GROUP BY list didn't work correctly in all cases. Fixed.
808
</li><li>
809
    ORDER BY now uses an index if possible. Queries with LIMIT with ORDER BY are faster when the index can be used.
810
</li><li>
811
    New option '-ifExists' for the TCP and ODBC server to disallow creating new databases remotely.
812
</li><li>
813
    Shutdown of a TCP Server: Can now specify a password (tcpPassword). Passwords used at startup and
814 815
    shutdown must match to shutdown. Uses a management database now for each server / port.
    New option tcpShutdownForce (default is false) to kill the server without waiting for other connections to close.
816
</li><li>
817
    Issue #103: Shutdown of a TCP Server from command line didn't always work.
818
</li></ul>
819 820 821 822

<h3>Version 0.9 / 2006-05-14</h3><ul>
<li>
    New functions: CSVREAD and CSVWRITE to access CSV (comma separated values) files.
823
</li><li>
824 825
    Locking: the synchronization was too restrictive, locking out the connection holding a lock
    when another connection tried to lock the same object. Fixed.
826
</li><li>
827 828 829
    Outer Join: currently, the table order of outer joins is kept, the tables are evaluated left to right
    (with the exception of right outer join tables). This is a temporary solution only to solve problems
    with join conditions.
830
</li><li>
831
    Bugfix for SCRIPT: the rights where created before the objects. Fixed.
832
</li><li>
833
    Implemented function LOCK_TIMEOUT().
834
</li><li>
835
    Compatibility with DBPool: Support 'holdability' in the Connection methods (however the value it is currently ignored).
836
</li><li>
837
    Connection.setTypeMap does not throw an exception any more if the type map is empty (null or 0 size).
838
</li><li>
839
    Issue #102: INSTR('123456','34') should return 2.
840
</li><li>
841
    Issue #101: A big result set with order by on a column or value that is not in the result list didn't work. Fixed.
842
</li><li>
843
    Referential integrity: cascade didn't work for more than one level when it was self-referencing. Fixed.
844
</li><li>
845 846
    New parameter charsetName in RunScript.execute.
    New option CHARSET in the RUNSCRIPT command.
847
</li><li>
848 849 850
    A backslash in the database URL (database name) didn't work, fixed. But a backslash in the settings part of the URL
    is used as an escape for ';', that means database URLs of the form jdbc:h2:test;SETTING=a\;b\;c
    are possible (but hopefully this is never required).
851
</li><li>
852 853
    Added an interface SimpleRowSource so that an application / tool can create a dynamic result set
    that produces rows on demand (for streaming result sets).
854
</li><li>
855
    Foreign key constraints with different data types on the references / referencing column did not work. Fixed.
856
</li><li>
857 858
    Fixed a problems that lead to a database (index file) corruption when killing the process.
    Added a test case.
859
</li><li>
860 861
    If a user has SELECT privileges for a view, he can now retrieve the data even if he does not have
    privileges for the underlying table(s).
862
</li><li>
863 864
    New system table INFORMATION_SCHEMA.CONSTRAINT that returns information about constraints
    (mainly for check and unique constraints; for referential constraints see CROSS_REFERENCES).
865
</li><li>
866 867 868
    Alter table alter column: the data type of a column could not be changed, and columns could not be dropped
    if there was a constraint on the table. Now the data type can always be changed.
    Alter table drop column: now the column can be dropped if it is not part of a constraint.
869
</li><li>
870
    Views can now be 'invalid', for example if the table does not exist.
871
    New syntax CREATE FORCE VIEW.
872 873
    New column STATUS (invalid / valid) in INFORMATION_SCHEMA.VIEWS table.
    New SQL statement ALTER VIEW viewName RECOMPILE.
874
</li><li>
875
    Issue #100: Altering a table with a self-referencing constraint didn't work. Fixed.
876
</li><li>
877
    Support for IDENTITY(start, increment) as in CREATE TABLE TEST(ID BIGINT NOT NULL IDENTITY(10, 5));
878
</li><li>
879 880
    Recovery did not always work correctly when the log file was switched.
    Added tests for this use case.
881
</li><li>
882
    Bugfix for CREATE ROLE IF NOT EXISTS X.
883
</li><li>
884 885
    Removed support for LZMA. It was slow, and code coverage could not run.
    If somebody needs this algorithm, it is better to add an open compression API.
886
</li><li>
887
    Starting a server took 1 second before, now it takes only about 100 ms.
888
</li><li>
889 890
    Server mode: If a parameter was not set in a prepared statement when using the server mode,
    the connection to the server broke. Fixed.
891
</li><li>
892 893
    Referential integrity: If there was a unique index with fewer columns than the foreign key on the child table,
    it did not work. Fixed.
894
</li><li>
895
    Javadoc / Doclet: ResultSet.getBytes was documented to return byte instead of byte[]. Fixed.
896
</li><li>
897 898
    New setting RECOVER in the database URL (jdbc:h2:test;RECOVER=1) to open corrupted databases
    (wrong checksum, corrupted index file or summary).
899
</li><li>
900
    Bugfix: In server mode, big scrollable result sets didn't work. Fixed.
901
</li><li>
902
    MERGE: instead of delete-insert, now use update-[insert]. This solves problems with foreign key constraints.
903
</li><li>
904
    Bugfix for LIKE: A null pointer exception was thrown for WHERE NAME LIKE CAST(? AS VARCHAR).
905
</li><li>
906
    Join optimization: expressions are now evaluated as early as possible, avoiding unnecessary lookups.
907
</li><li>
908
    ResultSetMetaData.isAutoIncrement is implemented.
909
</li><li>
910 911
    SCRIPT: STRINGENCODE instead of STRINGDECODE was used. The implementation
    of STRINGDECODE was not correct for Unicode characters > 127. Fixed.
912
</li><li>
913
    Documentation: [[NOT] NULL] instead of [NOT [NULL]]
914
</li><li>
915 916 917
    Because of the REAL support, databases are not compatible with the old version.
    Backup (with the old version), replace 'STRINGENCODE' in the script with
    'STRINGDECODE', and restore is required to upgrade to the new version.
918
</li><li>
919
    Support for REAL data type (Java 'float'; so far the DOUBLE type was used internally).
920
</li></ul>
921 922 923 924 925

<h3>Version 0.9 / 2006-04-20</h3><ul>
<li>
    Performance improvement for AND, OR, IFNULL, CASEWHEN, CASE, COALESCE and ARRAY_GET:
    only the necessary parameters / operators are evaluated.
926
</li><li>
927
    Bugfix for CASEWHEN: data type of return value was not evaluated, and this was a problem
928
    when using prepared statements like this: WHERE CASEWHEN(ID &lt; 10, A, B)=?
929
</li><li>
930
    New function DATABASE_PATH to retrieve the path and file name of a database.
931
</li><li>
932 933
    Made the code more modular. New ant target: jarClient to compile the JDBC driver and the classes used
    to remotely connect to a H2 server.
934
</li><li>
935
    JdbcDataSourceFactory constructor is now public.
936
</li><li>
937 938
    SET THROTTLE allows to throttle down the resource usage of a connection.
    After each 50ms, the session sleeps for the specified amount of time.
939
</li><li>
940
    ALTER TABLE ALTER COLUMN for a table that was referenced by another table didn't work. Fixed.
941
</li><li>
942
    If index log is disabled (the default), index changes are flushed automatically each second
943 944 945
    if the index was not changed. Like this index rebuilding is only required
    (after a unexpected program termination, like power off) for indexes that
    where recently changed.
946
</li><li>
947 948
    Bugfix: ids of large objects (LOBs) are now correctly reserved when opening the database.
    This improves the performance when using many LOBs.
949
</li><li>
950
    Replaced log_index=1 with log=2. There are 3 log options now: 0 (disabled), 1 (default), and 2 (log index changes as well).
951
</li><li>
952
    Got rid of the summary (.sum.db) file. The data is now stored in the log file instead.
953
</li><li>
954 955
    If the application stopped without closing all connections (for example calling System.exit),
    some committed transactions may have not been written to disk. Fixed.
956
</li><li>
957 958
    Bugfix if index changes where logged (default is off): not all changes where logged, in some situations recovery would not work fast
    (but no data loss as the indexes can be rebuilt; just slower database opening). Fixed.
959
</li><li>
960
    DatabaseMetaData.getTypeInfo: compatibility with HSQLDB improved.
961
</li><li>
962
    Parser: quoted keywords where not allowed as table or column names. Fixed.
963
</li><li>
964
    An exception was thrown in FileStoreInputStream when reading 0 bytes. But this is allowed according to the specs.
965
</li><li>
966
    Performance of LIMIT was slow when the result set was very big and the number of rows was small. Fixed
967
</li><li>
968
    The RunScript tool (org.h2.tools.RunScript) now uses the same algorithm to parse scripts.
969
</li><li>
970 971 972
    Bugfix: it was allowed to use jdbc:h2:test;database_event_listener=Test,
    but only for the first connection. Now, the class name must be quoted in all cases:
    jdbc:h2:test;database_event_listener='Test'
973
</li><li>
974
    Implemented SET LOG 0 to disable logging for improved performance (about twice as fast).
975
</li><li>
976
    Implemented TRUNCATE TABLE.
977
</li><li>
978
    SCRIPT: New option 'DROP' to drop tables and views before creating them.
979
</li><li>
980
    Chinese translation updates.
981
</li><li>
982
    It was not possible to create views on system tables. Fixed.
983
</li><li>
984 985 986
    If a database can't be opened because there is a bug in the startup information
    (which is basically the SQL script of CREATE statements), then the database can now be opened
    by specifying a database event listener.
987
</li><li>
988 989 990 991
    Parser / MySQL compatibility: in the last release, for MySQL compatibility, this syntax was supported:
    CREATE TABLE TEST(ID INT, INDEX(ID), KEY(ID));
    But that means INDEX and KEY can't be used as column names.
    Changed the parser so that this syntax is only supported in MySQL mode.
992
</li><li>
993 994 995
    Optimizer: now chose a plan with index even if there are no or only a few rows in a table.
    To avoid doing a table scan when no rows are in the table at prepare time, and many rows at execution time.
    Using a row count offset of 1000.
996
</li><li>
997
    MERGE: new SQL command to insert or update a row. Sometimes this is called UPSERT for update or insert.
998
    The grammar is a lot simpler than what Oracle supports.
999
</li><li>
1000
    SCRIPT: now the CREATE INDEX statements are after the INSERT statements, to improve performance.
1001
</li><li>
1002
    Console: Bugfix for newline in a view definition.
1003
</li><li>
1004
    Bugfix for CONCAT with more than 2 parameters.
1005
</li></ul>
1006 1007 1008 1009

<h3>Version 0.9 / 2006-04-09</h3><ul>
<li>
    Bugfix for VARCHAR_IGNORECASE.
1010
</li><li>
1011 1012
    Open database: Improved the performance for opening a database if it was not closed correctly before
    (due to abnormal program termination or power failure).
1013
</li><li>
1014
    Compact database: Added a sample application and documented how to do this.
1015
</li><li>
1016
    Bugfix: the SCRIPT command created insert statements for views. Removed.
1017
</li><li>
1018 1019
    Implemented a way to shutdown a TCP server.
    From command line, run: java org.h2.tools.Server -tcpShutdown tcp://localhost:9092
1020
</li><li>
1021 1022
    LOG_INDEX: new option in the database URL to log changes to the index file. This is allows fast recovery
    from system crash for big databases. Sample URL: jdbc:h2:test;LOG_INDEX=1
1023
</li><li>
1024
    Bugfix: using a low MAX_MEMORY_ROWS didn't work for GROUP BY queries with more groups. Fixed.
1025
</li><li>
1026
    Improved the trigger API (provide a Connection to the Java function) and added a sample application.
1027
</li><li>
1028 1029 1030 1031 1032
    Support setting parameters in the statement:
    INSERT INTO TEST VALUES(?, ?) {1: 5000, 2: 'This is a test'}.
    However, this feature is not documented yet as it's not clear if this is the final syntax.
    Currently, used in the trace feature to create sql script files instead of Java class files
    (in many cases, the classes just get too big and can't be compiled).
1033
</li><li>
1034
    Support multiline statement in RUNSCRIPT.
1035
</li><li>
1036 1037
    Improved parser performance. Re-parsing of statements is avoided now if the database schema didn't change.
    This should improve the performance for Hibernate, when many CREATE / DROP statements are executed.
1038
</li><li>
1039
    Performance improvement for LIMIT when using simple queries without ORDER BY.
1040
</li><li>
1041
    Improve compression ratio and performance for LZF.
1042
</li><li>
1043
    Improve performance for Connection.getReadOnly() (Hibernate calls it a lot).
1044
</li><li>
1045 1046
    Server mode: Increased the socket buffer size to 64 KB. This should help performance
    for medium size and bigger result sets. May thanks to James Devenish again!
1047
</li><li>
1048 1049
    Support for IPv6 addresses in JDBC URLs. RFC 2732 format is '[a:b:c:d:e:f:g:h]' or '[a:b:c:d:e:f:g:h]:port'.
    May thanks to James Devenish
1050
</li><li>
1051 1052
    Bugfix for Linked Tables: The table definition was not stored correctly (with ''driver'' instead of 'driver' and so on). Fixed.
    This was found by 'junheng'
1053
</li><li>
1054
    Chinese localization of the H2 Console.
1055
</li><li>
1056
    Bugfix: The connection was automatically closed sometimes when using a function with a connection parameter.
1057
</li><li>
1058
    Bugfix in the Console: Auto-refresh of the object list for DROP / ALTER / CREATE statements, updatable result sets.
1059
</li><li>
1060 1061
    Improved support for MySQL syntax: ` is the same as ", except that the identifiers are converter to upper case.
    Added support for special cases of MySQL CREATE TABLE syntax.
1062
</li><li>
1063 1064 1065 1066 1067
    Java Functions: functions can now return ResultSet.
    The SQL statement CALL GET_RESULT_SET(123)
    then returns the result set created by the function.
    Implemented a simple result set / result set meta data class that can be used
    to create result sets in an application from scratch.
1068
</li><li>
1069 1070
    Web Server: the command line options where ignored. Fixed.
    Renamed the options 'httpAllowOthers', 'httpPort', 'httpSSL' to 'web...'.
1071
</li><li>
1072 1073 1074 1075 1076 1077 1078 1079
    Oracle compatibility: CASE...END [CASE] (the last word is new).
    Oracle compatibility: Date addition/subtraction: SYSDATE+1, (SYSDATE-1)-SYSDATE and so on.
    Limited support for old Oracle outer join syntax (single column outer join, (+) must be on the far right).
    This works:
    CREATE TABLE Customers(CustomerID int); CREATE TABLE Orders(CustomerID int);
    INSERT INTO Customers VALUES(1), (2), (3);  INSERT INTO Orders VALUES(1), (3);
    SELECT * FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
    SELECT * FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID(+);
1080
</li></ul>
1081 1082 1083

<h3>Version 0.9 / 2006-04-23</h3><ul>
<li>
1084
    Improved Hibernate Dialect for SQuirreL DB Copy.
1085
</li><li>
1086 1087
    Support for UPDATE TEST SET (column [,...])=(select | expr [,...]').
    For Compiere compatibility.
1088
</li><li>
1089
    Bugfixes for SCRIPT: write sequences before tables; write CLOB and BLOB data.
1090
</li><li>
1091
    Added PolePosition benchmark results.
1092
</li><li>
1093 1094
    Bugfix in the server implementation: free up the memory. The new implementation does not rely on finalizers
    any more. Removed finalizers, this improves the performance.
1095
</li><li>
1096
    Implemented CROSS JOIN and NATURAL JOIN.
1097
</li><li>
1098
    New keywords for join syntax: CROSS, NATURAL, FULL. However full outer join is not supported yet.
1099
</li><li>
1100
    Improved documentation of the tools (Server port and so on).
1101
</li><li>
1102
    The schema name of constraints is now automatically set to the table.
1103
</li><li>
1104
    Compatibility for Derby style column level constraints.
1105
</li><li>
1106 1107
    New command ANALYZE to update the selectivity statistics.
    New command ALTER TABLE ALTER COLUMN SELECTIVITY to manually set the selectivity of a column.
1108
</li><li>
1109
    Allow a java.sql.Connection parameter in the Java function (as in HSQLDB).
1110
</li><li>
1111
    Optimizer improvements.
1112
</li><li>
1113 1114
    Implemented SET DB_CLOSE_DELAY numberOfSeconds to be able to delay, or disable
    (-1) closing the database. Default is still 0 (close database when closing the last connection).
1115
</li><li>
1116 1117 1118 1119 1120
    After finding that HSQLDB was faster in the PolePosition test, and unsuccessful tries to
    improve the performance of H2 even more, the reason is finally found: H2 always closed
    the database when closing the last connection, but HSQLDB leaves the database open.
    This also explains OutOfMemory problems when testing multiple databases with PolePosition.
    But leaving the database open is a useful feature for some applications.
1121
</li><li>
1122 1123
    New system table CROSS_REFERENCES.
    Implemented DatabaseMetaData.getImportedKeys, getExportedKeys, getCrossReferences.
1124
</li><li>
1125
    Performance improvements in the binary tree and other places.
1126
</li><li>
1127 1128 1129
    A new aggregate function SELECTIVITY is implemented. It calculates the selectivity of a column
    by counting the distinct rows, but only up to 10000 values are kept in memory.
    SELECT SELECTIVITY(ID), SELECTIVITY(NAME) FROM TEST LIMIT 1 SAMPLE_SIZE 10000 scans only 10000 rows.
1130
</li><li>
1131 1132
    A new option SAMPLE_SIZE is added to the LIMIT clause to specify the number of rows to scan for a aggregated
    query.
1133
</li><li>
1134 1135 1136
    Bugfixes for temporary tables. Implemented ON COMMIT DROP / DELETE ROWS, but
    not documented because it is only here for compatibility, and I would advise against using it
    because it is not available in most databases.
1137
</li><li>
1138
    Improved parser performance and memory usage.
1139
</li></ul>
1140 1141 1142 1143

<h3>Version 0.9 / 2005-12-13</h3><ul>
<li>
    First public release.
1144
</li></ul>
1145

1146
<br /><a name="roadmap"></a>
1147 1148 1149 1150 1151
<h2>Roadmap</h2>

<h3>Highest Priority</h3>
<ul>
<li>Improve test code coverage
1152 1153 1154 1155
</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>
1156 1157 1158 1159

<h3>In Version 1.1</h3>
<ul>
<li>Change Constants.DEFAULT_MAX_MEMORY_UNDO to 10000 (and change the docs). Test.
1160 1161 1162 1163 1164 1165
</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>
1166 1167 1168

<h3>Priority 1</h3>
<ul>
1169
<li>More tests with MULTI_THREADED=1
1170 1171 1172 1173 1174 1175 1176
</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>MVCC (Multi Version Concurrency Control)
</li><li>Server side cursors
</li><li>Row level locking
1177
</li><li>Read-only databases inside a jar (splitting large files to speed up random access)
1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193
</li><li>System table: open sessions and locks of a database
</li><li>Function in management db: 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>RECOVER=1 automatically if a problem opening
</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>Support SET REFERENTIAL_INTEGRITY {TRUE|FALSE}
</li><li>Support CHAR data type (internally use VARCHAR, but report CHAR for JPox)
1194 1195
</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
1196
</li><li>Support Oracle RPAD and LPAD(string, n[, pad]) (truncate the end if longer)
1197
</li></ul>
1198 1199 1200 1201

<h3>Priority 2</h3>
<ul>
<li>Support OSGi: http://oscar-osgi.sourceforge.net, http://incubator.apache.org/felix/index.html
1202
</li><li>System table / function: cache usage
1203
</li><li>Connection pool manager
1204
</li><li>Set the database in an 'exclusive' mode (restrict to one user at a time)
1205 1206 1207 1208 1209 1210 1211 1212
</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>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)
1213
</li><li>Test with Sun ASPE1_4; JEE Sun AS PE1.4
1214 1215 1216
</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)
1217
</li><li>Find a tool to view a text file >100 MB, with find, page up and down (like less)
1218 1219 1220
</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)
1221
</li><li>CHECK: find out what makes CHECK=TRUE slow, move to CHECK2
1222 1223 1224 1225 1226
</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
1227 1228
</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)
1229 1230 1231 1232 1233 1234 1235
</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
1236
</li><li>Implement missing JDBC API (CallableStatement,...)
1237
</li><li>Compression of the cache
1238
</li><li>Run H2 Console inside servlet (pass-through servlet of fix the JSP / app)
1239 1240 1241 1242
</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)
1243 1244
</li><li>JSON parser and functions
</li><li>Option for Java functions: constant/isDeterministic to allow early evaluation when all parameters are constant
1245
</li><li>Automatic collection of statistics (auto ANALYZE)
1246 1247 1248 1249
</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
1250
</li><li>Support SET TABLE DUAL READONLY
1251 1252
</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?)
1253
</li><li>GCJ: what is the state now?
1254 1255 1256 1257
</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)
1258
</li><li>Optimization: Log compression
1259 1260 1261
</li><li>Allow editing NULL values in the Console
</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)
1262
    SELECT * FROM TEST T, P2C(T.A, T.R)
1263
    </li><li>Custom class loader to reload functions on demand
1264 1265 1266 1267
</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
1268
</li><li>Profiler option or profiling tool to find long running and often repeated queries (using DatabaseEventListener API)
1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284
</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>System Tray: http://jroller.com/page/stritti?entry=system_tray_implementations_for_java
</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)
1285
</li><li>Optimize getGeneratedKey: send last identity after each execute (server).
1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300
</li><li>Clustering: recovery needs to becomes fully automatic.
</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>Support home directory as ~ in database URL (jdbc:h2:file:~/.dir/db)
</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
1301
</li><li>The Script tool should work with other databases as well
1302 1303
</li><li>Deferred integrity checking (DEFERRABLE INITIALLY DEFERRED)
</li><li>Automatically convert to the next 'higher' data type whenever there is an overflow.
1304
</li><li>Throw an exception when the application calls getInt on a Long (optional)
1305 1306 1307 1308 1309 1310
</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)
1311

1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328
</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
1329 1330
    See also http://sr5tech.com/write_back_cache_experiments.htm
    and http://www.jasonbrome.com/blog/archives/2004/04/03/writecache_enabled.html
1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388
</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>Remove finally() (almost) everywhere
</li><li>Java static code analysis: http://pmd.sourceforge.net/
</li><li>Java static code analysis: http://www.eclipse.org/tptp/
</li><li>Java static code analysis: http://checkstyle.sourceforge.net/
</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>Option to globally disable / enable referential integrity checks
</li><li>Support ISO 8601 timestamp / date / time with timezone
</li><li>Support TRANSFORM / PIVOT as in MS Access
</li><li>ALTER SEQUENCE ... RENAME TO ... (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)
1389
</li><li>Index usage for IN(...), IN ARRAY(?), and IN ARRAY RANGES(?, ?); support ARRAY in JDBC API (variable size)
1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413
</li><li>Change LOB mechanism (less files, keep index of lob files, point to files and row, delete unused files earlier)
</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
</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>User defined aggregate functions
</li><li>System property for base directory (h2.baseDir) in embedded mode
</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)
1414
</li><li>Support ANALYZE {TABLE|INDEX} tableName COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
1415
</li><li>Support Sequoia (Continuent.org)
1416 1417
</li><li>Dynamic length numbers / special methods for DataPage.writeByte / writeShort / Ronni Nielsen
</li><li>Pluggable tracing system, ThreadPool,  (AvalonDB / deebee / Paul Hammant)
1418 1419 1420 1421 1422 1423
</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
1424 1425
</li><li>Support OUTER UNION
</li><li>Support Parameterized Views (similar to CSVREAD, but using just SQL for the definition)
1426 1427 1428 1429 1430 1431 1432 1433
</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)
1434 1435 1436 1437 1438 1439 1440
</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
1441
</li></ul>
1442 1443 1444 1445

<h3>Not Planned</h3>
<ul>
<li>HSQLDB (did) support this: select id i from test where i>0 (other databases don't)
1446 1447
</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>
1448

1449
<br /><a name="supporters"></a>
1450 1451 1452 1453 1454 1455
<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
1456
</li><li>Pete Haidinyak, USA
1457 1458
</li><li>Jun Iyama, Japan
</li><li>Antonio Casqueiro, Portugal
1459
</li></ul>
1460

1461
</div></td></tr></table></body></html>