history.html 89.0 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
<h3>Version 1.0 / 2007-TODO</h3><ul>
40 41 42
<li>For most IOExceptions now the file name is included in the error message.
</li><li>A java.util.Date object is now converted to a TIMESTAMP in the JDBC API. Previously it was converted to a DATE.
</li><li>After calling SHUTDOWN and closing the connection and a superfluous error message appeared in the trace file. Fixed.
43
</li><li>In many situations, views did not use an index if they could have. Fixed. Also the explain plan for views works now.
44 45 46
</li><li>The table id (important for LOB files) is now included in INFORMATION_SCHEMA.TABLES.
</li><li>When using DISTINCT, ORDER BY a function works now as long as it is in the column list.
</li><li>Support for the data type CHAR. The difference to VARCHAR is: trailing spaces are ignored. This 
47
    data type is supported for compatibility with other databases and older applications.
48 49 50 51 52 53
</li><li>The aggregate function COUNT(...) now returns a long instead of an int.
</li><li>The 'ordering' of data types was not always correct, for example an operation involving REAL and DOUBLE produced a result of type REAL. Fixed.
</li><li>CSV tool: If the same instance was used for reading and writing, the tool wrote the column names twice. Fixed.
</li><li>Compatibility: Support for the data type notation CHARACTER VARYING
</li><li>Can now ORDER BY -1 (meaning order by first column, descending), and ORDER BY ? (parameterized column number).
</li><li>Databases with invalid linked tables (for example, because the target database is not accessible) can now be opened. 
54
    Old table links don't work however.
55 56
</li><li>There was a small memory leak in the trace module. One object per opened connection was kept in a hash map.
</li><li>Linked tables can now emit UPDATE statements if 'EMIT UPDATES' is specified in the CREATE LINKED
57
    TABLE statement.  So far, updating a row always deleted the old row and then inserted the new row.
58
</li><li>In the last release, the H2 Console opened two connection when logging into a database, 
59
    and only closed one connection when logging out. Fixed.
60 61 62 63
</li><li>New functions LEAST and GREATEST to get the smallest or largest value from a list.
</li>
</ul>

64
<h3>Version 1.0 / 2007-04-29 (Build 46)</h3><ul>
65 66 67
<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.
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
</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.
103 104 105 106 107 108 109 110
</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
111 112
    name exactly in the select list and the order list exactly in the same way. 
    This is not required any longer.
113 114
</li></ul>

115
<h3>Version 1.0 / 2007-03-04 (Build 44)</h3><ul>
116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
<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).
132
</li><li>ORDER BY picked the wrong column if the same column name (but with a different table name) 
133
    was used twice in the select list.
134
</li><li>When a subquery was used in the select list of a query, and GROUP BY was used at the same time,
135 136
    a NullPointerException could occur. Fixed.
</li><li>ORDER BY did not work when DISTINCT was used at the same time in some situations. Fixed.
137
</li><li>When using IN(...) on a case insensitive column (VARCHAR_IGNORECASE), 
138
    an incorrect optimization was made and the result was wrong sometimes.
139
</li></ul>
140

141
<h3>Version 1.0 / 2007-01-30 (Build 41)</h3><ul>
142 143
<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.
144
</li><li>When using the server mode, temporary files for large LOB values are now deleted when the result set is closed.
145
    This also means that LOBs become unavailable after closing the result, however this is according to the specs.
146 147 148 149
</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, 
150
    and to Indonesian thanks to Joko Yuliantoro
151 152
</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.
153
    Now the result is not re-used in this case. Generally, large subqueries should be avoided for performance reasons.
154 155 156 157 158
</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], 
159
    and partially supported features are [Partially supported].
160 161
</li><li>The forum subscriptions (the emails sent from the forum) now works.
</li></ul>
162

163
<h3>Version 1.0 / 2007-01-17 (Build 40)</h3><ul>
164
<li>Setting the collation (SET COLLATOR) was very slow on some systems (up to 24 seconds). 
165
    Thanks a lot to Martina Nissler for finding this problem!
166 167
</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. 
168
    There are still some limitations, and the ant script to build the jar does not work yet.
169
</li><li>Fixed a problem where data in the log file was not written to the data file (recovery failure) after a crash, 
170
    if an index was deleted previously.
171 172 173
</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.
174
    However dates before year 1 are not formatted correctly (this is a Java problem).
175
</li><li>When stopping the TCP server from an application and immediately afterwards staring it again
176
    using a different TCP password, an exception was thrown sometimes.
177
</li><li>Now PreparedStatement.setBigDecimal(..) can only be called with an object
178 179
    of type java.math.BigDecimal. Derived classes are not allowed any more. Many thanks to
    Maciej Wegorkiewicz for finding this problem.
180
</li><li>It was possible to manipulate values in the byte array after calling PreparedStatement.setBytes, and this
181
    could lead to problems if the same byte array was used again. Now the byte array is copied if required.
182 183
</li><li>Date, time and timestamp objects were cloned in cases where it was not required. Fixed.
</li></ul>
184

185
<h3>Version 1.0 / 2007-01-02 (Build 36)</h3><ul>
186
<li>It was possible to drop the sequence of a temporary tables with DROP ALL OBJECTS, resulting in a null pointer exception afterwards.
187 188 189
</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. 
190
    This can be changed using the system property h2.clientTraceDirectory.
191 192 193
</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,
194
    the object was lost if it was large. Fixed.
195 196
</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.
197
    However, there is currently no load balancing made to avoid problems with transactions / locking.
198
</li><li>Many settings are now initialized from system properties and can be changed on the command line without having
199
    recompile the database. See Advances / Settings Read from System Properties.
200
</li><li>H2 is now available in Maven. The groupId is com.h2database, the artifactId h2 and the version 1.0.20061217.
201
    To create the maven artifacts yourself, use 'ant mavenUploadLocal' and 'ant mavenBuildCentral'.
202
</li></ul>
203

204
<h3>Version 1.0 / 2006-12-17 (Build 34)</h3><ul>
205
<li>Can be compiled with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
206 207
</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
208
    (INFORMATION_SCHEMA.SEQUENCES).
209
</li><li>String.toUpperCase and toLowerCase can not be used to process SQL, as they depend on the current locale.
210
    Now using toUpperCase(Locale.ENGLISH) or Character.toUpperCase(..)
211 212
</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.
213
    It was already possible to do that using embedded RUNSCRIPT statements, but not remotely.
214 215 216
</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
217
    earlier.
218
</li><li>Opening a large database is now much faster when even when using the default log mode (LOG=1),
219
    if the database was closed previously.
220
</li><li>Very large BLOB and CLOB data can now be used with the server and the cluster mode.
221 222
    The objects will temporarily be buffered on the client side if they are larger than some
    size (currently 64 KB).
223
</li><li>PreparedStatement.setObject(x, y, Types.OTHER) does now serialize the object in every case
224
    (even for Integer).
225
</li><li>EXISTS subqueries with parameters were not re-evaluated when the prepared statement was
226
    reused. This could lead to incorrect results.
227 228
</li><li>Support for indexed parameters in PreparedStatements: update test set name=?2 where id=?1
</li></ul>
229

230
<h3>Version 1.0 / 2006-12-03 (Build 32)</h3><ul>
231
<li>The SQL statement COMMENT did not work as expected. Many bugs have been fixed in this area.
232
    If you already have comments in the database, it is recommended to backup and restore the database,
233
    using the Backup and RunScript tools or the SQL commands SCRIPT and RUNSCRIPT.
234
</li><li>Mixing certain data types in an operation, for example VARCHAR and TIMESTAMP, now converts both expressions to TIMESTAMP.
235
    This was a problem when comparing a string against a date.
236 237
</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),
238
    even calling close() threw an exception. This is not done any more as it is not a problem.
239 240
</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 
241
    pattern of repeated encrypted bytes where generated for empty blocks in the file. This has been is fixed. 
242
    The security of the data itself was not compromised, but this was not the intended behavior.
243 244 245
    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.
246
</li><li>New connection time setting CACHE_TYPE=TQ to use the 2Q page replacement algorithm.
247
    The overall performance seems to be a bit better when using 2Q. Also, 2Q should be more resistant to table scan.
248
</li><li>Change behavior: If both sides of a comparison are parameters with unknown data type, then an exception is thrown now.
249
    The same happens for UNION SELECT if both columns are parameters.
250 251 252 253
</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.
254
    The function is still called twice (first to get the column list at parse time, and then at execute time). 
255
    The behavior has been changed: Now first call contains the values if set, but the connection URL is different
256
    (jdbc:columnlist:connection instead of jdbc:default:connection).
257
</li></ul>
258

259
<h3>Version 1.0 / 2006-11-20 (Build 31)</h3><ul>
260
<li>SCRIPT: New option BLOCKSIZE to split BLOB and CLOB data into separate blocks, to avoid OutOfMemory problems.
261
</li><li>When using the READ_COMMITTED isolation level, a transaction now waits until there are no write locks
262
    when trying to read data. However, it still does not add a read lock.
263 264
</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. 
265
    The API has been changed, there is now only one static method, getInstance().
266
</li><li>ALTER TABLE ADD did throw a strange message if the table contained views. Now the message is better,
267
    but it is still not possible to do that if views on this table exist.
268 269 270
</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,
271
    the constraint was dropped.
272
</li><li>The input streams returned from this database did not always read all data when calling
273
    read. This was not wrong, but unexpected. Now changed that read behaves like readFully.
274
</li><li>The default cache size is now 65536 pages instead of 32768. Like this the memory used for caching
275
    is about 10 MB. Before, it used to be about 6 MB.
276
</li><li>Inserting rows into linked tables did not work for HSQLDB when the value was NULL. 
277
    Now NULL values are inserted if the value for a column was not set in the insert statement.
278 279 280 281
</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.
282 283
    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.
284
</li><li>When using the server version, when not closing result sets or using nested DatabaseMetaData result sets,
285
    the connection could break. This has been fixed.
286 287 288 289 290 291 292 293
</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>
294

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

355
<h3>Version 1.0 / 2006-10-10 (Build 28)</h3><ul>
356 357
<li>
    Redundant () in a IN subquery is now supported: where id in ((select id from test))
358
</li><li>
359 360
    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.
361
</li><li>
362
    Can now compile everything with JDK 1.6. However, only very few of the JDBC 4.0 features are implemented so far.
363
</li><li>
364
    A small FTP server is now included. Disabled by default. Intended as a simple mechanism to transfer data in ad-hoc networks.
365
</li><li>
366
    GROUP BY an formula or function didn't work if the same expression was used in the select list. Fixed.
367
</li><li>
368
    Reconnect didn't work after renaming a user if rights were granted for this user. Fixed.
369
</li><li>
370 371
    Opening and closing connections in many threads sometimes failed because opening a session
    was not correctly synchronized. Fixed.
372
</li><li>
373
    Function aliases may optionally include parameter classes. Example:
374
    CREATE ALIAS PARSE_INT2 FOR "java.lang.Integer.parseInt(java.lang.String, int)"
375
</li><li>
376
    Support for UUID
377
</li><li>
378
    Support for DOMAIN (user defined data types).
379
</li><li>
380
    Could not re-connect to a database when ALLOW_LITERALS or COMPRESS_LOB was set. Fixed.
381
</li></ul>
382

383
<h3>Version 1.0 / 2006-09-24 (Build 27)</h3><ul>
384 385
<li>
    New LOCK_MODE 3 (READ_COMMITTED). Table level locking, but only when writing (no read locks).
386
</li><li>
387
    Connection.setTransactionIsolation and getTransactionIsolation now set / get the LOCK_MODE of the database.
388
</li><li>
389
    New system function LOCK_MODE()
390
</li><li>
391 392
    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)
393
</li><li>
394 395
    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.
396
</li><li>
397
    Reading from compressed LOBs didn't work in some cases. Fixed.
398
</li><li>
399 400
    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.
401
</li><li>
402 403
    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.
404
</li><li>
405
    For compatibility with Derby, the precision in a data type definition can now include K, M or G as in BLOB(10M).
406
</li><li>
407
    CREATE TABLE ... AS SELECT ... is now supported.
408
</li><li>
409
    DROP TABLE: Can now drop more than one column in one step: DROP TABLE A, B
410
</li><li>
411
    CREATE SCHEMA: The authorization part is now optional.
412
</li><li>
413 414 415
    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.
416
</li><li>
417 418 419
    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.
420
</li><li>
421 422 423
    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.
424
</li><li>
425
    [NOT] EXISTS(SELECT ... EXCEPT SELECT ...) did not work in all cases. Fixed.
426
</li><li>
427 428 429
    DatabaseMetaData.getProcedures and getProcedureColumns are implemented now.
    INFORMATION_SCHEMA.FUNCTION_ALIASES was changed, and there is a new table
    INFORMATION_SCHEMA.FUNCTION_COLUMNS.
430
</li><li>
431 432
    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.
433
</li><li>
434 435 436
    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.
437
</li><li>
438
    Improved performance for MetaData calls. The table name is now indexed.
439
</li><li>
440
    BatchUpdateException was not helpful, now includes the cause
441
</li><li>
442 443 444
    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.
445
</li><li>
446 447
    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.
448
</li><li>
449
    Backup and Runscript tools now support options (H2 only)
450
</li></ul>
451

452
<h3>Version 1.0 / 2006-09-10 (Build 26)</h3><ul>
453 454
<li>
    Updated the performance test so that Firebird can be tested as well.
455
</li><li>
456 457
    Now an exception is thrown when the an overflow occurs for mathematical operations (sum, multiply and so on)
    for the data type selected.
458
</li><li>
459 460
    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).
461
</li><li>
462
    The script can now be compressed. Syntax: SCRIPT TO 'file' COMPRESSION {DEFLATE|LZF|ZIP|GZIP}.
463
</li><li>
464 465
    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).
466
</li><li>
467 468 469
    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
470
</li><li>
471
    Deleting many rows from a table with a self-referencing constraint with 'on delete cascade' did not work.
472
    Now referential constraints are checked after the action is performed.
473
</li><li>
474
    The cross references in the SQL grammar docs where broken in the last release.
475
</li><li>
476 477
    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.
478
</li><li>
479 480
    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.
481
</li><li>
482 483
    Until now, unknown connection properties where ignored (for OpenOffice compatibility).
    This is not a good solution because typos are not detected.
484
    This behavior is still the default but it can be disabled by adding
485 486
    ;IGNORE_UNKNOWN_SETTINGS=FALSE to the database URL.
    However this is not the final solution.
487
</li><li>
488 489 490
    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.
491
</li><li>
492 493 494
    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.
495
</li><li>
496 497
    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.
498
</li><li>
499
    SET IGNORECASE is now supported for compatibility with HSQLDB, and because using collations (SET COLLATION) is very slow on JDKs (JDK 1.5)
500
</li><li>
501
    ORDER BY an expression didn't work when using GROUP BY at the same time.
502
</li></ul>
503

504
<h3>Version 1.0 / 2006-08-31 (Build 25)</h3><ul>
505 506
<li>
    In some situations, wide b-tree indexes (with large VARCHAR columns for example) could get corrupted. Fixed.
507
</li><li>
508
    ORDER BY was broken in the last release when using table aliases. Fixed.
509
</li></ul>
510 511 512 513 514
    
<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.
515
</li><li>
516
    LOB files where not deleted when the table was truncated or dropped. This is now done.
517
</li><li>
518 519 520 521
    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
522
</li><li>
523
    New setting MAX_LENGTH_INPLACE_LOB
524
</li><li>
525 526
    When reading from a table with many small CLOB columns, in some situations
    an ArrayIndexOutOfBoundsException was thrown. Fixed.
527
</li><li>
528 529 530
    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.
531
</li><li>
532
    Subqueries: Constant subqueries are now only evaluated once (like this was before).
533
</li><li>
534
    Linked tables: Improved compatibility with other databases and improved error messages.
535
</li><li>
536 537
    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).
538
</li><li>
539
    Outer join: There where some incompatibilities with PostgreSQL and MySQL with more complex outer joins. Fixed.
540
</li></ul>
541

542
<h3>Version 0.9 / 2006-08-23 (Build 23)</h3><ul>
543 544
<li>
    Bugfix for LIKE: If collation was set (SET COLLATION ...), it was ignored when using LIKE. Fixed.
545
</li><li>
546 547 548 549
    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.
550
</li><li>
551 552 553 554
    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).
555
</li><li>
556 557
    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.
558
</li><li>
559 560
    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.
561
</li><li>
562
    Local temporary tables where not included in the meta data. Fixed.
563
</li><li>
564 565 566 567 568
    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
569
</li><li>
570
    Improvements in the autocomplete feature. Thanks a lot to James Devenish for his very valuable feedback and testing!
571
</li><li>
572
    Bugfix for an outer join problem (too many rows where returned for a combined inner join / outer join).
573
</li><li>
574
    Date and time constants outside the valid range (February 31 and so on) are no longer accepted.
575
</li></ul>
576

577
<h3>Version 0.9 / 2006-08-14 (Build 21)</h3><ul>
578 579
<li>
    SET LOG 0 didn't work (except if the log level was set to some other value before). Fixed.
580
</li><li>
581 582
    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.
583
</li><li>
584 585 586 587 588 589
    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.
590
</li><li>
591 592
    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
593
</li><li>
594
    Space is better re-used after deleting many records.
595
</li><li>
596
    Umlauts and Chinese characters are now supported in
597
    identifier names (table name, column names and so on).
598
</li><li>
599
    Fixed a problem when comparing BIGINT values with constants.
600
</li><li>
601
    NULL handling was wrong for: true IN (true, null). Fixed.
602
</li><li>
603
    It was not possible to cancel a select statement with a (temporary) view. Fixed.
604
</li></ul>
605

606
<h3>Version 0.9 / 2006-07-29 (Build 18)</h3><ul>
607 608
<li>
    ParameterMetaData is now implemented (mainly to support getParameterCount).
609
</li><li>
610
    Improved performance for Statement.getGeneratedKeys().
611
</li><li>
612 613
    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.
614
</li><li>
615
    Bugfix: IN(NULL) didn't return NULL in every case. Fixed.
616
</li><li>
617
    Bugfix: DATEDIFF didn't work correctly for hour, minute and second if one of the dates was before 1970. Fixed.
618
</li><li>
619 620
    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.
621
</li><li>
622 623
    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.
624
</li><li>
625
    SELECT EXCEPT (or MINUS) did not work for some cases. Fixed.
626
</li><li>
627
    DATEDIFF now returns a BIGINT and not an INT
628
</li><li>
629 630
    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).
631
</li><li>
632 633 634 635
    '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.
636
</li><li>
637
    H2 Console: First version of an autocomplete feature.
638
</li><li>
639
    DATEADD didn't work for milliseconds. Fixed.
640
</li><li>
641
    New parameter schemaName in Trigger.init.
642
</li><li>
643
    New method DatabaseEventListener.init to pass the database URL.
644
</li><li>
645 646 647
    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.
648
</li><li>
649
    Could not connect to a database that was closing at the same time.
650
</li><li>
651
    C-style block comments /* */ are not parsed correctly when they contain * or /
652
</li></ul>
653

654
<h3>Version 0.9 / 2006-07-14 (Build 16)</h3><ul>
655 656
<li>
    The regression tests are no longer included in the jar file. This reduces the size by about 200 KB.
657
</li><li>
658
    Fixed some bugs in the CSV tool. This tool should now work for most cases, but is still not fully tested.
659
</li><li>
660 661
    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.
662
</li><li>
663
    Objects of unknown type are no longer serialized to a byte array (and deserialized when calling getObject on a byte array data type)
664
    by default. This behavior can be changed with Constants.SERIALIZE_JAVA_OBJECTS = true
665
</li><li>
666
    New column IS_GENERATED in the metadata tables SEQUENCES and INDEXES
667
</li><li>
668
    Optimization: deterministic subqueries are evaluated only once.
669
</li><li>
670
    An exception was thrown if a scalar subquery returned no rows. Now the NULL value is used in this case.
671
</li><li>
672
    IF EXISTS / IF NOT EXISTS implemented for the remaining CREATE / DROP statements.
673
</li><li>
674
    ResultSetMetaData.isNullable is now implemented.
675
</li><li>
676
    LIKE ... ESCAPE: The escape character may now also be an expression.
677
</li><li>
678
    Compatibility: TRIM(whitespace FROM string)
679
</li><li>
680
    Compatibility: SUBSTRING(string FROM start FOR length)
681
</li><li>
682
    CREATE VIEW now supports a column list: CREATE VIEW TEST_V(A, B) AS ...
683
</li><li>
684 685
    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)
686
</li><li>
687
    Optimization: data conversion of constants was not optimized. This is done now.
688
</li><li>
689 690
    Compatibility: Implemented a shortcut version to declare single column referential integrity:
    CREATE TABLE TEST(ID INT PRIMARY KEY, PARENT INT REFERENCES TEST)
691
</li><li>
692
    Issue #126: It is possible to create multiple primary keys for the same table.
693
</li><li>
694
    Issue #125: Foreign key constraints of local temporary tables are not dropped when the table is dropped.
695
</li><li>
696
    Issue #124: Adding a column didn't work when the table contains a referential integrity check.
697
</li><li>
698 699
    Issue #123: The connection to the server is lost if an abnormal exception occurs.
    Example SQL statement: select 1=(1,2)
700
</li><li>
701 702 703
    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.
704
</li><li>
705
    DatabaseMetaData.getTypeInfo: BIGINT was returning AUTO_INCREMENT=TRUE, which is wrong. Fixed.
706
</li></ul>
707

708
<h3>Version 0.9 / 2006-07-01 (Build 14)</h3><ul>
709
<li>
710
    After dropping constraints and altering a table sometimes the database could not be opened. Fixed.
711
</li><li>
712
    Outer joins did not always use an index even if this was possible. Fixed.
713
</li><li>
714
    Issue #122: Using OFFSET in big result sets (disk buffered result sets) did not work. Fixed.
715
</li><li>
716
    Support DatabaseMetaData.getSuperTables (currently returns an empty result set in every case).
717
</li><li>
718 719
    Database names are no longer case sensitive for the Windows operating system,
    because there the files names are not case sensitive.
720
</li><li>
721
    If an index is created for a constraint, this index now belong to the constraint and is removed when removing the constraint.
722
</li><li>
723
    Issue #121: Using a quoted table or alias name in front of a column name (SELECT "TEST".ID FROM TEST) didn't work.
724
</li><li>
725
    Issue #120: Some ALTER TABLE statements didn't work when the table was in another than the main schema. Fixed.
726
</li><li>
727 728 729
    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.
730
</li><li>
731
    Issue #118: ALTER TABLE RENAME COLUMN doesn't work correctly. Workaround: don't use it.
732
</li><li>
733 734
    Cache: implemented a String cache and improved the Value cache. Now uses a weak reference
    to avoid OutOfMemory due to caching values.
735
</li><li>
736 737
    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();
738
</li><li>
739
    Issue #117: Server.start...Server sometimes returned before the server was started. Solved.
740
</li><li>
741
    Issue #116: Server: reduces memory usage. Reduced number of cached objects per connection.
742
</li><li>
743
    Improved trace messages, and trace now starts earlier (when opening the database).
744
</li><li>
745
    Simplified translation of the Web Console (a tool to convert the translation files to UTF-8).
746
</li><li>
747
    Newsfeed sample application (used to create the newsfeed and newsletter).
748
</li><li>
749
    New functions: MEMORY_FREE() and MEMORY_USED().
750
</li></ul>
751 752 753 754 755 756 757

<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;
758
</li><li>
759
    If a connection is closed while there is still an operation running, this operation is stopped (like when calling Statement.cancel).
760
</li><li>
761
    Issue #115: If three or more threads / connections are used, sometimes lock timeout exceptions can occur when they should not. Solved.
762
</li><li>
763
    Calling Server.start...Server now doesn't return until the server socket is ready to accept connections.
764
</li><li>
765 766
    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.
767
</li><li>
768
    Implemented DROP TRIGGER.
769
</li><li>
770
    Issue #113: Drop is now restricted: can only drop sequences / functions / tables if nothing depends on them.
771
</li><li>
772
    Issue #114: Support large index data size. Before, there was a limit of around 1000 bytes per row.
773
</li><li>
774
    Blob.getLength() and Clob.getLength() are now fast operations and don't read the whole object any longer.
775
</li><li>
776 777
    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.
778
</li><li>
779 780 781
    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.
782
</li><li>
783
    The catalog name is now uppercase, to conform the JDBC standard for DatabaseMetaData.storesUpperCaseIdentifiers().
784
</li><li>
785
    Creating or opening a small encrypted database is now a lot faster.
786
</li><li>
787
    New functions FORMATDATETIME and PARSEDATETIME.
788
</li><li>
789
    New XML encoding functions (XMLATTR, XMLNODE, XMLCOMMENT, XMLCDATA, XMLSTARTDOC, XMLTEXT).
790
</li><li>
791
    Performance: improved opening of a large databases (about 3 times faster now for 500 MB databases).
792
</li><li>
793
    Documented ALTER TABLE DROP COLUMN. The functionality was there already, but the documentation not.
794
</li></ul>
795

796
<h3>Version 0.9 / 2006-06-02 (Build 10)</h3><ul>
797 798
<li>
    Removed the GCJ h2-server.exe from download. It was not stable on Windows.
799
</li><li>
800
    Issue #109: ALTER TABLE ADD COLUMN can make the database unusable if the original table contained a IDENTITY column.
801
</li><li>
802 803
    New option to disable automatic closing of a database when the virtual machine exits.
    Database URL: jdbc:h2:test;db_close_on_exit=false
804
</li><li>
805
    New event: DatabaseEventListener.closingDatabase() is called before closing the database.
806
</li><li>
807 808
    Connection.getCatalog() now returns the database name (CALL DATABASE()).
    This name is also used in all system tables and DatabaseMetaData calls where applicable.
809
</li><li>
810 811
    The function DATABASE() now return the short name of the database (without path),
    and 'Unnamed' for anonymous in-memory databases.
812
</li><li>
813 814
    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.
815
</li><li>
816 817
    Statements containing LIKE are now re-compiled when executed. Depending on the data,
    an index on the column is used or not.
818
</li><li>
819 820
    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.
821
</li><li>
822
    Issue #106: SET commands where not persisted if they where the first DDL commands for a connection. Fixed.
823
</li><li>
824 825
    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.
826
</li><li>
827
    Automatic starting of a web browser for Mac OS X should work now.
828
</li><li>
829 830
    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.
831
</li><li>
832
    Issue #104: A HAVING condition on a column that was not in the GROUP BY list didn't work correctly in all cases. Fixed.
833
</li><li>
834
    ORDER BY now uses an index if possible. Queries with LIMIT with ORDER BY are faster when the index can be used.
835
</li><li>
836
    New option '-ifExists' for the TCP and ODBC server to disallow creating new databases remotely.
837
</li><li>
838
    Shutdown of a TCP Server: Can now specify a password (tcpPassword). Passwords used at startup and
839 840
    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.
841
</li><li>
842
    Issue #103: Shutdown of a TCP Server from command line didn't always work.
843
</li></ul>
844 845 846 847

<h3>Version 0.9 / 2006-05-14</h3><ul>
<li>
    New functions: CSVREAD and CSVWRITE to access CSV (comma separated values) files.
848
</li><li>
849 850
    Locking: the synchronization was too restrictive, locking out the connection holding a lock
    when another connection tried to lock the same object. Fixed.
851
</li><li>
852 853 854
    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.
855
</li><li>
856
    Bugfix for SCRIPT: the rights where created before the objects. Fixed.
857
</li><li>
858
    Implemented function LOCK_TIMEOUT().
859
</li><li>
860
    Compatibility with DBPool: Support 'holdability' in the Connection methods (however the value it is currently ignored).
861
</li><li>
862
    Connection.setTypeMap does not throw an exception any more if the type map is empty (null or 0 size).
863
</li><li>
864
    Issue #102: INSTR('123456','34') should return 2.
865
</li><li>
866
    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.
867
</li><li>
868
    Referential integrity: cascade didn't work for more than one level when it was self-referencing. Fixed.
869
</li><li>
870 871
    New parameter charsetName in RunScript.execute.
    New option CHARSET in the RUNSCRIPT command.
872
</li><li>
873 874 875
    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).
876
</li><li>
877 878
    Added an interface SimpleRowSource so that an application / tool can create a dynamic result set
    that produces rows on demand (for streaming result sets).
879
</li><li>
880
    Foreign key constraints with different data types on the references / referencing column did not work. Fixed.
881
</li><li>
882 883
    Fixed a problems that lead to a database (index file) corruption when killing the process.
    Added a test case.
884
</li><li>
885 886
    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).
887
</li><li>
888 889
    New system table INFORMATION_SCHEMA.CONSTRAINT that returns information about constraints
    (mainly for check and unique constraints; for referential constraints see CROSS_REFERENCES).
890
</li><li>
891 892 893
    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.
894
</li><li>
895
    Views can now be 'invalid', for example if the table does not exist.
896
    New syntax CREATE FORCE VIEW.
897 898
    New column STATUS (invalid / valid) in INFORMATION_SCHEMA.VIEWS table.
    New SQL statement ALTER VIEW viewName RECOMPILE.
899
</li><li>
900
    Issue #100: Altering a table with a self-referencing constraint didn't work. Fixed.
901
</li><li>
902
    Support for IDENTITY(start, increment) as in CREATE TABLE TEST(ID BIGINT NOT NULL IDENTITY(10, 5));
903
</li><li>
904 905
    Recovery did not always work correctly when the log file was switched.
    Added tests for this use case.
906
</li><li>
907
    Bugfix for CREATE ROLE IF NOT EXISTS X.
908
</li><li>
909 910
    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.
911
</li><li>
912
    Starting a server took 1 second before, now it takes only about 100 ms.
913
</li><li>
914 915
    Server mode: If a parameter was not set in a prepared statement when using the server mode,
    the connection to the server broke. Fixed.
916
</li><li>
917 918
    Referential integrity: If there was a unique index with fewer columns than the foreign key on the child table,
    it did not work. Fixed.
919
</li><li>
920
    Javadoc / Doclet: ResultSet.getBytes was documented to return byte instead of byte[]. Fixed.
921
</li><li>
922 923
    New setting RECOVER in the database URL (jdbc:h2:test;RECOVER=1) to open corrupted databases
    (wrong checksum, corrupted index file or summary).
924
</li><li>
925
    Bugfix: In server mode, big scrollable result sets didn't work. Fixed.
926
</li><li>
927
    MERGE: instead of delete-insert, now use update-[insert]. This solves problems with foreign key constraints.
928
</li><li>
929
    Bugfix for LIKE: A null pointer exception was thrown for WHERE NAME LIKE CAST(? AS VARCHAR).
930
</li><li>
931
    Join optimization: expressions are now evaluated as early as possible, avoiding unnecessary lookups.
932
</li><li>
933
    ResultSetMetaData.isAutoIncrement is implemented.
934
</li><li>
935 936
    SCRIPT: STRINGENCODE instead of STRINGDECODE was used. The implementation
    of STRINGDECODE was not correct for Unicode characters > 127. Fixed.
937
</li><li>
938
    Documentation: [[NOT] NULL] instead of [NOT [NULL]]
939
</li><li>
940 941 942
    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.
943
</li><li>
944
    Support for REAL data type (Java 'float'; so far the DOUBLE type was used internally).
945
</li></ul>
946 947 948 949 950

<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.
951
</li><li>
952
    Bugfix for CASEWHEN: data type of return value was not evaluated, and this was a problem
953
    when using prepared statements like this: WHERE CASEWHEN(ID &lt; 10, A, B)=?
954
</li><li>
955
    New function DATABASE_PATH to retrieve the path and file name of a database.
956
</li><li>
957 958
    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.
959
</li><li>
960
    JdbcDataSourceFactory constructor is now public.
961
</li><li>
962 963
    SET THROTTLE allows to throttle down the resource usage of a connection.
    After each 50ms, the session sleeps for the specified amount of time.
964
</li><li>
965
    ALTER TABLE ALTER COLUMN for a table that was referenced by another table didn't work. Fixed.
966
</li><li>
967
    If index log is disabled (the default), index changes are flushed automatically each second
968 969 970
    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.
971
</li><li>
972 973
    Bugfix: ids of large objects (LOBs) are now correctly reserved when opening the database.
    This improves the performance when using many LOBs.
974
</li><li>
975
    Replaced log_index=1 with log=2. There are 3 log options now: 0 (disabled), 1 (default), and 2 (log index changes as well).
976
</li><li>
977
    Got rid of the summary (.sum.db) file. The data is now stored in the log file instead.
978
</li><li>
979 980
    If the application stopped without closing all connections (for example calling System.exit),
    some committed transactions may have not been written to disk. Fixed.
981
</li><li>
982 983
    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.
984
</li><li>
985
    DatabaseMetaData.getTypeInfo: compatibility with HSQLDB improved.
986
</li><li>
987
    Parser: quoted keywords where not allowed as table or column names. Fixed.
988
</li><li>
989
    An exception was thrown in FileStoreInputStream when reading 0 bytes. But this is allowed according to the specs.
990
</li><li>
991
    Performance of LIMIT was slow when the result set was very big and the number of rows was small. Fixed
992
</li><li>
993
    The RunScript tool (org.h2.tools.RunScript) now uses the same algorithm to parse scripts.
994
</li><li>
995 996 997
    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'
998
</li><li>
999
    Implemented SET LOG 0 to disable logging for improved performance (about twice as fast).
1000
</li><li>
1001
    Implemented TRUNCATE TABLE.
1002
</li><li>
1003
    SCRIPT: New option 'DROP' to drop tables and views before creating them.
1004
</li><li>
1005
    Chinese translation updates.
1006
</li><li>
1007
    It was not possible to create views on system tables. Fixed.
1008
</li><li>
1009 1010 1011
    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.
1012
</li><li>
1013 1014 1015 1016
    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.
1017
</li><li>
1018 1019 1020
    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.
1021
</li><li>
1022
    MERGE: new SQL command to insert or update a row. Sometimes this is called UPSERT for update or insert.
1023
    The grammar is a lot simpler than what Oracle supports.
1024
</li><li>
1025
    SCRIPT: now the CREATE INDEX statements are after the INSERT statements, to improve performance.
1026
</li><li>
1027
    Console: Bugfix for newline in a view definition.
1028
</li><li>
1029
    Bugfix for CONCAT with more than 2 parameters.
1030
</li></ul>
1031 1032 1033 1034

<h3>Version 0.9 / 2006-04-09</h3><ul>
<li>
    Bugfix for VARCHAR_IGNORECASE.
1035
</li><li>
1036 1037
    Open database: Improved the performance for opening a database if it was not closed correctly before
    (due to abnormal program termination or power failure).
1038
</li><li>
1039
    Compact database: Added a sample application and documented how to do this.
1040
</li><li>
1041
    Bugfix: the SCRIPT command created insert statements for views. Removed.
1042
</li><li>
1043 1044
    Implemented a way to shutdown a TCP server.
    From command line, run: java org.h2.tools.Server -tcpShutdown tcp://localhost:9092
1045
</li><li>
1046 1047
    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
1048
</li><li>
1049
    Bugfix: using a low MAX_MEMORY_ROWS didn't work for GROUP BY queries with more groups. Fixed.
1050
</li><li>
1051
    Improved the trigger API (provide a Connection to the Java function) and added a sample application.
1052
</li><li>
1053 1054 1055 1056 1057
    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).
1058
</li><li>
1059
    Support multiline statement in RUNSCRIPT.
1060
</li><li>
1061 1062
    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.
1063
</li><li>
1064
    Performance improvement for LIMIT when using simple queries without ORDER BY.
1065
</li><li>
1066
    Improve compression ratio and performance for LZF.
1067
</li><li>
1068
    Improve performance for Connection.getReadOnly() (Hibernate calls it a lot).
1069
</li><li>
1070 1071
    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!
1072
</li><li>
1073 1074
    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
1075
</li><li>
1076 1077
    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'
1078
</li><li>
1079
    Chinese localization of the H2 Console.
1080
</li><li>
1081
    Bugfix: The connection was automatically closed sometimes when using a function with a connection parameter.
1082
</li><li>
1083
    Bugfix in the Console: Auto-refresh of the object list for DROP / ALTER / CREATE statements, updatable result sets.
1084
</li><li>
1085 1086
    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.
1087
</li><li>
1088 1089 1090 1091 1092
    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.
1093
</li><li>
1094 1095
    Web Server: the command line options where ignored. Fixed.
    Renamed the options 'httpAllowOthers', 'httpPort', 'httpSSL' to 'web...'.
1096
</li><li>
1097 1098 1099 1100 1101 1102 1103 1104
    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(+);
1105
</li></ul>
1106 1107 1108

<h3>Version 0.9 / 2006-04-23</h3><ul>
<li>
1109
    Improved Hibernate Dialect for SQuirreL DB Copy.
1110
</li><li>
1111 1112
    Support for UPDATE TEST SET (column [,...])=(select | expr [,...]').
    For Compiere compatibility.
1113
</li><li>
1114
    Bugfixes for SCRIPT: write sequences before tables; write CLOB and BLOB data.
1115
</li><li>
1116
    Added PolePosition benchmark results.
1117
</li><li>
1118 1119
    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.
1120
</li><li>
1121
    Implemented CROSS JOIN and NATURAL JOIN.
1122
</li><li>
1123
    New keywords for join syntax: CROSS, NATURAL, FULL. However full outer join is not supported yet.
1124
</li><li>
1125
    Improved documentation of the tools (Server port and so on).
1126
</li><li>
1127
    The schema name of constraints is now automatically set to the table.
1128
</li><li>
1129
    Compatibility for Derby style column level constraints.
1130
</li><li>
1131 1132
    New command ANALYZE to update the selectivity statistics.
    New command ALTER TABLE ALTER COLUMN SELECTIVITY to manually set the selectivity of a column.
1133
</li><li>
1134
    Allow a java.sql.Connection parameter in the Java function (as in HSQLDB).
1135
</li><li>
1136
    Optimizer improvements.
1137
</li><li>
1138 1139
    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).
1140
</li><li>
1141 1142 1143 1144 1145
    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.
1146
</li><li>
1147 1148
    New system table CROSS_REFERENCES.
    Implemented DatabaseMetaData.getImportedKeys, getExportedKeys, getCrossReferences.
1149
</li><li>
1150
    Performance improvements in the binary tree and other places.
1151
</li><li>
1152 1153 1154
    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.
1155
</li><li>
1156 1157
    A new option SAMPLE_SIZE is added to the LIMIT clause to specify the number of rows to scan for a aggregated
    query.
1158
</li><li>
1159 1160 1161
    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.
1162
</li><li>
1163
    Improved parser performance and memory usage.
1164
</li></ul>
1165 1166 1167 1168

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

1171
<br /><a name="roadmap"></a>
1172 1173 1174 1175 1176
<h2>Roadmap</h2>

<h3>Highest Priority</h3>
<ul>
<li>Improve test code coverage
1177 1178
</li><li>More fuzz tests
</li><li>Test very large databases and LOBs (up to 256 GB)
1179
</li><li>Test multi-threaded in-memory db access
1180
</li></ul>
1181 1182 1183 1184

<h3>In Version 1.1</h3>
<ul>
<li>Change Constants.DEFAULT_MAX_MEMORY_UNDO to 10000 (and change the docs). Test.
1185 1186 1187 1188 1189 1190
</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>
1191 1192 1193

<h3>Priority 1</h3>
<ul>
1194 1195
<li>RECOVER=1 should automatically recover, =2 should run the recovery tool if required
</li><li>More tests with MULTI_THREADED=1
1196 1197 1198 1199 1200 1201 1202
</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
1203
</li><li>Read-only databases inside a jar (splitting large files to speed up random access)
1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217
</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>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}
1218 1219
</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
1220
</li><li>Support Oracle RPAD and LPAD(string, n[, pad]) (truncate the end if longer)
1221
</li><li>Allow editing NULL values in the Console
1222
</li></ul>
1223 1224 1225 1226

<h3>Priority 2</h3>
<ul>
<li>Support OSGi: http://oscar-osgi.sourceforge.net, http://incubator.apache.org/felix/index.html
1227
</li><li>System table / function: cache usage
1228
</li><li>Connection pool manager
1229
</li><li>Set the database in an 'exclusive' mode (restrict to one user at a time)
1230 1231 1232 1233 1234 1235 1236 1237
</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)
1238
</li><li>Test with Sun ASPE1_4; JEE Sun AS PE1.4
1239 1240 1241
</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)
1242
</li><li>Find a tool to view a text file >100 MB, with find, page up and down (like less)
1243 1244 1245
</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)
1246
</li><li>CHECK: find out what makes CHECK=TRUE slow, move to CHECK2
1247 1248 1249 1250 1251
</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
1252 1253
</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)
1254 1255 1256 1257 1258 1259 1260
</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
1261
</li><li>Implement missing JDBC API (CallableStatement,...)
1262
</li><li>Compression of the cache
1263
</li><li>Run H2 Console inside servlet (pass-through servlet of fix the JSP / app)
1264 1265 1266 1267
</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)
1268 1269
</li><li>JSON parser and functions
</li><li>Option for Java functions: constant/isDeterministic to allow early evaluation when all parameters are constant
1270
</li><li>Automatic collection of statistics (auto ANALYZE)
1271 1272 1273 1274
</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
1275
</li><li>Support SET TABLE DUAL READONLY
1276 1277
</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?)
1278
</li><li>GCJ: what is the state now?
1279 1280 1281 1282
</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)
1283
</li><li>Optimization: Log compression
1284 1285
</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)
1286
    SELECT * FROM TEST T, P2C(T.A, T.R)
1287
    </li><li>Custom class loader to reload functions on demand
1288 1289 1290 1291
</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
1292
</li><li>Profiler option or profiling tool to find long running and often repeated queries (using DatabaseEventListener API)
1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308
</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)
1309
</li><li>Optimize getGeneratedKey: send last identity after each execute (server).
1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324
</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
1325
</li><li>The Script tool should work with other databases as well
1326 1327
</li><li>Deferred integrity checking (DEFERRABLE INITIALLY DEFERRED)
</li><li>Automatically convert to the next 'higher' data type whenever there is an overflow.
1328
</li><li>Throw an exception when the application calls getInt on a Long (optional)
1329 1330 1331 1332 1333 1334
</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)
1335

1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352
</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
1353 1354
    See also http://sr5tech.com/write_back_cache_experiments.htm
    and http://www.jasonbrome.com/blog/archives/2004/04/03/writecache_enabled.html
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 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412
</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)
1413
</li><li>Index usage for IN(...), IN ARRAY(?), and IN ARRAY RANGES(?, ?); support ARRAY in JDBC API (variable size)
1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437
</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)
1438
</li><li>Support ANALYZE {TABLE|INDEX} tableName COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
1439
</li><li>Support Sequoia (Continuent.org)
1440 1441
</li><li>Dynamic length numbers / special methods for DataPage.writeByte / writeShort / Ronni Nielsen
</li><li>Pluggable tracing system, ThreadPool,  (AvalonDB / deebee / Paul Hammant)
1442 1443 1444 1445 1446 1447
</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
1448 1449
</li><li>Support OUTER UNION
</li><li>Support Parameterized Views (similar to CSVREAD, but using just SQL for the definition)
1450 1451 1452 1453 1454 1455 1456 1457
</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)
1458 1459 1460 1461 1462 1463 1464
</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
1465 1466 1467 1468 1469 1470 1471
</li><li>Benchmark: add a graph to show how databases scale (performance/database size)
</li><li>Implement a SQLData interface to map your data over to a custom object
</li><li>Extend H2 Console to run tools (show command line as well)
</li><li>Make DDL (Data Definition) operations transactional
</li><li>Sequence: add features [NO] MINVALUE, MAXVALUE, CACHE, CYCLE
</li><li>Allow execution time prepare for SELECT * FROM CSVREAD(?, 'columnNameString')
</li><li>Support multiple directories (on different hard drives) for the same database
1472
</li></ul>
1473 1474 1475 1476

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

1480
<br /><a name="supporters"></a>
1481 1482 1483 1484 1485 1486
<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
1487
</li><li>Pete Haidinyak, USA
1488 1489
</li><li>Jun Iyama, Japan
</li><li>Antonio Casqueiro, Portugal
1490
</li></ul>
1491

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