roadmap.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Copyright 2004-2010 H2 Group. Multiple-Licensed under the H2 License, Version 1.0,
and under the Eclipse Public License, Version 1.0
(http://h2database.com/html/license.html).
Initial Developer: H2 Group
-->
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /><title>
Roadmap
</title><link rel="stylesheet" type="text/css" href="stylesheet.css" />
<!-- [search] { -->
<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>Roadmap</h1>
<p>
New (feature) requests will usually be added at the very end of the list. The priority is increased for important and popular requests.
Of course, patches are always welcome, but are not always applied as is.
See also <a href="build.html#providing_patches">Providing Patches</a>.
</p>

<h2>Version 1.3.x: Planned Changes</h2>
<ul><li>Enable h2.lobInDatabase (store CLOB and BLOB in the database file).
</li><li>Set ANALYZE_AUTO to 2000 (automatic ANALYZE).
</li><li>Enable FUNCTIONS_IN_SCHEMA (allow to store functions in a schema).
</li><li>Enable SELECT_FOR_UPDATE_MVCC (MVCC and SELECT FOR UPDATE).
</li><li>Enable LARGE_TRANSACTIONS (support for very large transactions).
    Change documentation for MAX_MEMORY_UNDO in help.csv, because
    now changes to tables without a primary key can be buffered to disk.
    Later, change MAX_MEMORY_UNDO to reflect number of bytes instead of record
    (also remove Constants.UNDO_BLOCK_SIZE).
    Later, change undo log file format (fillAligned no longer required; var int).
</li><li>Enable NESTED_JOINS (nested joins and right outer joins).
</li><li>Enable OPTIMIZE_OR (convert OR conditions to IN(..) if possible).
</li><li>Enable DROP_RESTRICT (default action for DROP is RESTRICT).
    Change documentation.
</li><li>Set MAX_MEMORY_ROWS_DISTINCT to a lower value.
</li><li>Enable OPTIMIZE_INSERT_FROM_SELECT (speed up CREATE TABLE ... AS SELECT).
</li><li>Possibly enable QUERY_CACHE_SIZE.
</li><li>Disable DB_CLOSE_ON_EXIT (the shutdown hook) by default.
</li><li>For RUNSCRIPT and SCRIPT (commands and tools), use UTF-8 by default
    (ScriptCommand.charset).

</li><li>Use Lucene 3 by default.
</li></ul>

<h2>Priority 1</h2>
<ul><li>Bugfixes
</li><li>Test if out of disk space can corrupt a database.
</li><li>More tests with MULTI_THREADED=1
</li><li>Server side cursors
</li></ul>

<h2>Priority 2</h2>
<ul><li>Improve test code coverage.
</li><li>Maybe disable shutdown hook (DB_CLOSE_ON_EXIT=FALSE) by default.
</li><li>Access rights: remember the owner of an object. Create, alter and drop privileges. COMMENT: allow owner of object to change it. Issue 208. Access rights for schemas.
</li><li>Test multi-threaded in-memory db access.
</li><li>Support hints for the optimizer (which index to use, enforce the join order).
</li><li>Migrate database tool (also from other database engines). For Oracle, maybe use
    DBMS_METADATA.GET_DDL / GET_DEPENDENT_DDL.
</li><li>Full outer joins.
</li><li>Clustering: support mixed clustering mode (one embedded, others in server mode).
</li><li>Clustering: reads should be randomly distributed (optional) or to a designated database on RAM (parameter: READ_FROM=3).
</li><li>PostgreSQL catalog: use BEFORE SELECT triggers instead of views over metadata tables.
</li><li>Compatibility: automatically load functions from a script depending on the mode - see FunctionsMySQL.java, issue 211.
</li><li>Test very large databases and LOBs (up to 256 GB).
</li><li>Sequence: add features [NO] MINVALUE, MAXVALUE, CYCLE.
</li><li>Deferred integrity checking (DEFERRABLE INITIALLY DEFERRED).
</li><li>Groovy Stored Procedures: http://groovy.codehaus.org/Groovy+SQL
</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>Rebuild index functionality to shrink index size and improve performance.
</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>Test performance again with SQL Server, Oracle, DB2.
</li><li>Test with Spatial DB in a box / JTS: http://www.opengeospatial.org/standards/sfs - OpenGIS Implementation Specification.
</li><li>Write more tests and documentation for MVCC (Multi Version Concurrency Control).
</li><li>Find a tool to view large text file (larger than 100 MB), with find, page up and down (like less), truncate before / after.
</li><li>Implement, test, document XAConnection and so on.
</li><li>Issue 159: System property for the H2 Console and TCP configuration (which .h2.server.properties and .h2.keystore to use).
</li><li>Pluggable data type (for streaming, hashing, compression, validation, conversion, encryption).
</li><li>CHECK: find out what makes CHECK=TRUE slow, move to CHECK2.
</li><li>Drop with invalidate views (so that source code is not lost). Check what other databases do exactly.
</li><li>Index usage for (ID, NAME)=(1, 'Hi'); document.
</li><li>Make DDL (Data Definition) operations transactional.
</li><li>RANK() and DENSE_RANK(), Partition using OVER().
</li><li>Set a connection read only (Connection.setReadOnly) or using a connection parameter.
</li><li>Optimizer: use an index for IS NULL and IS NOT NULL (including linked tables).
    ID IS NOT NULL could be converted to ID &gt;= Integer.MIN_VALUE.
</li><li>Access rights: finer grained access control (grant access for specific functions).
</li><li>Version check: docs / web console (using Javascript), and maybe in the library (using TCP/IP).
</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>Performance: update in-place.
</li><li>Eclipse plugin.
</li><li>Asynchronous queries to support publish/subscribe: SELECT ... FOR READ WAIT [maxMillisToWait].
    See also MS SQL Server "Query Notification".
</li><li>Fulltext search Lucene: analyzer configuration.
</li><li>Fulltext search (native): reader / tokenizer / filter.
</li><li>Linked schema using CSV files: one schema for a directory of files; support indexes for CSV files.
</li><li>iReport to support H2.
</li><li>Implement missing JDBC API (CallableStatement,...).
</li><li>Compression of the cache.
</li><li>Include SMPT (mail) client (alert on cluster failure, low disk space,...).
</li><li>JSON parser and functions.
</li><li>Server: client ping from time to time (to avoid timeout - is timeout a problem?).
</li><li>Copy database: tool with config GUI and batch mode, extensible (example: compare).
</li><li>Document, implement tool for long running transactions using user-defined compensation statements.
</li><li>Support SET TABLE DUAL READONLY.
</li><li>GCJ: what is the state now?
</li><li>Events for: database Startup, Connections, Login attempts, Disconnections, Prepare (after parsing), Web Server. See http://docs.openlinksw.com/virtuoso/fn_dbev_startup.html
</li><li>Optimization: log compression.
</li><li>ROW_NUMBER() OVER([ORDER BY columnName]).
</li><li>Support standard INFORMATION_SCHEMA tables, as defined in http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt - specially 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>Compatibility: in MySQL, HSQLDB, /0.0 is NULL; in PostgreSQL, Derby: division by zero.
</li><li>Functional tables should accept parameters from other tables (see FunctionMultiReturn) SELECT * FROM TEST T, P2C(T.A, T.R).
</li><li>Custom class loader to reload functions on demand.
</li><li>Clustering: when a database is back alive, automatically synchronize with the master.
</li><li>Test http://mysql-je.sourceforge.net/
</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>Use Janino to convert Java to C++.
</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>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 Server 2005, Oracle: support COUNT(*) OVER(). See http://www.orafusion.com/art_anlytc.htm
</li><li>SQL 2003: http://www.wiscorp.com/sql_2003_standard.zip
</li><li>Version column (number/sequence and timestamp based).
</li><li>Optimize getGeneratedKey: send last identity after each execute (server).
</li><li>Test and document UPDATE TEST SET (ID, NAME) = (SELECT ID*10, NAME || '!' FROM TEST T WHERE T.ID=TEST.ID).
</li><li>Max memory rows / max undo log size: use block count / row size not row count.
</li><li>Support 123L syntax as in Java; example: SELECT (2000000000*2).
</li><li>Implement point-in-time recovery.
</li><li>Option for SCRIPT to only process one or a set of schemas or tables, and append to a file.
</li><li>LIKE: improved version for larger texts (currently using naive search).
</li><li>Automatically convert to the next 'higher' data type whenever there is an overflow.
</li><li>Throw an exception when the application calls getInt on a Long (optional).
</li><li>Default date format for input and output (local date constants).
</li><li>Support custom Collators.
</li><li>Document ROWNUM usage for reports: SELECT ROWNUM, * FROM (subquery).
</li><li>File system that writes to two file systems (replication, replicating file system).
</li><li>Standalone tool to get relevant system properties and add it to the trace output.
</li><li>Support 'call proc(1=value)' (PostgreSQL, Oracle).
</li><li>Console: improve editing data (Tab, Shift-Tab, Enter, Up, Down, Shift+Del?).
</li><li>Console: autocomplete Ctrl+Space inserts template.
</li><li>Option to encrypt .trace.db file.
</li><li>Write Behind Cache on SATA leads to data corruption See also http://sr5tech.com/write_back_cache_experiments.htm and http://www.jasonbrome.com/blog/archives/2004/04/03/writecache_enabled.html
</li><li>Functions with unknown return or parameter data types: serialize / deserialize.
</li><li>Auto-Update feature for database, .jar file.
</li><li>ResultSet SimpleResultSet.readFromURL(String url): id varchar, state varchar, released timestamp.
</li><li>Partial indexing (see PostgreSQL).
</li><li>Add GUI to build a custom version (embedded, fulltext,...) using build flags.
</li><li>http://rubyforge.org/projects/hypersonic/
</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 could 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>Check if 'FSUTIL behavior set disablelastaccess 1' improves the performance (fsutil behavior query disablelastaccess).
</li><li>Java static code analysis: http://pmd.sourceforge.net/
</li><li>Java static code analysis: http://www.eclipse.org/tptp/
</li><li>Compatibility for CREATE SCHEMA AUTHORIZATION.
</li><li>Implement Clob / Blob truncate and the remaining functionality.
</li><li>Tree join functionality.
</li><li>Add multiple columns at the same time with ALTER TABLE .. ADD .. ADD ...
</li><li>Use JDK 1.4 file locking for the database file and for the lock file (but not yet by default); writing a system property to detect concurrent access from the same VM (different classloaders).
</li><li>Pure SQL triggers (example: update parent table if the child table is changed).
</li><li>Add H2 to Gem (Ruby install system).
</li><li>Order conditions inside AND / OR to optimize the performance.
</li><li>Support Oracle functions: TRUNC, NVL2, TO_CHAR, TO_DATE, TO_NUMBER.
</li><li>Support linked JCR tables.
</li><li>Read InputStream when executing, as late as possible (maybe only embedded mode). Problem with re-execute.
</li><li>Native fulltext search: min word length; store word positions.
</li><li>Add an option to the SCRIPT command to generate only portable / standard SQL.
</li><li>Updatable Views (simple cases first).
</li><li>Improve create index performance.
</li><li>Compact databases without having to close the database (vacuum).
</li><li>Implement more JDBC 4.0 features.
</li><li>Support TRANSFORM / PIVOT as in MS Access.
</li><li>SELECT * FROM (VALUES (...), (...), ....) AS alias(f1, ...).
</li><li>Support updatable views with join on primary keys (to extend a table).
</li><li>Public interface for functions (not public static).
</li><li>Support reading the transaction log.
</li><li>Support GRANT SELECT, UPDATE ON *.
</li><li>Eliminate undo log records if stored on disk (just one pointer per block, not per record).
</li><li>Feature matrix as in <a href="http://www.inetsoftware.de/products/jdbc/mssql/features/default.asp">i-net software</a>.
</li><li>Updatable result set on table without primary key or unique index.
</li><li>Compatibility with Derby and PostgreSQL: VALUES(1), (2); SELECT * FROM (VALUES (1), (2)) AS myTable(c1). Issue 221.
</li><li>Use LinkedList instead of ArrayList where applicable.
</li><li>Allow execution time prepare for SELECT * FROM CSVREAD(?, 'columnNameString')
</li><li>Support data type INTERVAL
</li><li>Support % operator (modulo).
</li><li>Support JMX: create an MBean for each database and server (support JConsole).
    See http://thedevcloud.blogspot.com/2008/10/displaying-hsql-database-manager-in.html
    http://java.sun.com/j2se/1.5.0/docs/api/java/lang/management/ManagementFactory.html#getPlatformMBeanServer()
    http://java.sun.com/j2se/1.5.0/docs/guide/management/agent.html
</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) over TCP/IP.
</li><li>Support curtimestamp (like curtime, curdate).
</li><li>Support ANALYZE {TABLE|INDEX} tableName COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options.
</li><li>Release locks (shared or exclusive) on demand
</li><li>Support OUTER UNION
</li><li>Support parameterized views (similar to CSVREAD, but using just SQL for the definition)
</li><li>A way (JDBC driver) to map an URL (jdbc:h2map:c1) to a connection object
</li><li>Support linked tables to the current database
</li><li>Support dynamic linked schema (automatically adding/updating/removing tables)
</li><li>Clustering: adding a node should be very fast and without interrupting clients (very short lock)
</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
</li><li>DatabaseEventListener: callback for all operations (including expected time, RUNSCRIPT) and cancel functionality
</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>In the MySQL and PostgreSQL mode, use lower case identifiers by default (DatabaseMetaData.storesLowerCaseIdentifiers = true)
</li><li>Support multiple directories (on different hard drives) for the same database
</li><li>Server protocol: use challenge response authentication, but client sends hash(user+password) encrypted with response
</li><li>Support EXEC[UTE] (doesn't return a result set, compatible to MS SQL Server)
</li><li>Support native XML data type
</li><li>Support triggers with a string property or option: SpringTrigger, OSGITrigger
</li><li>Ability to resize the cache array when resizing the cache
</li><li>Time based cache writing (one second after writing the log)
</li><li>Check state of H2 driver for DDLUtils: https://issues.apache.org/jira/browse/DDLUTILS-185
</li><li>Index usage for REGEXP LIKE.
</li><li>Compatibility: add a role DBA (like ADMIN).
</li><li>Better support multiple processors for in-memory databases.
</li><li>Support N'text'
</li><li>Support compatibility for jdbc:hsqldb:res:
</li><li>Provide an Java SQL builder with standard and H2 syntax
</li><li>Trace: write OS, file system, JVM,... when opening the database
</li><li>Support indexes for views (probably requires materialized views)
</li><li>Document SET SEARCH_PATH, BEGIN, EXECUTE, parameters
</li><li>Browser: use Desktop.isDesktopSupported and browse when using JDK 1.6
</li><li>Server: use one listener (detect if the request comes from an PG or TCP client)
</li><li>Optimize SELECT MIN(ID), MAX(ID), COUNT(*) FROM TEST WHERE ID BETWEEN 100 AND 200
</li><li>Sequence: PostgreSQL compatibility (rename, create) http://www.postgresql.org/docs/8.2/static/sql-altersequence.html
</li><li>DISTINCT: support large result sets by sorting on all columns (additionally) and then removing duplicates.
</li><li>Support a special trigger on all tables to allow building a transaction log reader.
</li><li>File system with a background writer thread; test if this is faster
</li><li>Better document the source code
</li><li>Support select * from dual a left join dual b on b.x=(select max(x) from dual)
</li><li>Optimization: don't lock when the database is read-only
</li><li>Issue 146: Support merge join.
</li><li>Integrate spatial functions from http://geosysin.iict.ch/irstv-trac/wiki/H2spatial/Download
</li><li>Cluster: hot deploy (adding a node at runtime)
</li><li>Support DatabaseMetaData.insertsAreDetected: updatable result sets should detect inserts.
</li><li>Support COSH, SINH, and TANH functions
</li><li>Oracle: support DECODE method (convert to CASE WHEN).
</li><li>Native search: support "phrase search", wildcard search (* and ?), case-insensitive search, boolean operators, and grouping
</li><li>Improve documentation of access rights
</li><li>Support ENUM data type (see MySQL, PostgreSQL, MS SQL Server, maybe others)
</li><li>Remember the user defined data type (domain) of a column
</li><li>MVCC: support multi-threaded kernel with multi-version concurrency.
</li><li>Support Jackcess (MS Access databases)
</li><li>Built-in methods to write large objects (BLOB and CLOB): FILE_WRITE('test.txt', 'Hello World')
</li><li>Improve time to open large databases (see mail 'init time for distributed setup')
</li><li>Move Maven 2 repository from hsql.sf.net to h2database.sf.net
</li><li>Java 1.5 tool: JdbcUtils.closeSilently(s1, s2,...)
</li><li>Javadoc: document design patterns used
</li><li>Write an article about SQLInjection (h2/src/docsrc/html/images/SQLInjection.txt)
</li><li>Convert SQL-injection-2.txt to html document, include SQLInjection.java sample
</li><li>Support OUT parameters in user-defined procedures.
</li><li>Web site design: http://www.igniterealtime.org/projects/openfire/index.jsp
</li><li>HSQLDB compatibility: Openfire server uses: CREATE SCHEMA PUBLIC AUTHORIZATION DBA;
    CREATE USER SA PASSWORD ""; GRANT DBA TO SA; SET SCHEMA PUBLIC
</li><li>Translation: use ${.} in help.csv
</li><li>Translated .pdf
</li><li>MySQL compatibility: update test1 t1, test2 t2 set t1.id = t2.id where t1.id = t2.id;
</li><li>Recovery tool: bad blocks should be converted to INSERT INTO SYSTEM_ERRORS(...), and things should go into the .trace.db file
</li><li>RECOVER=2 to backup the database, run recovery, open the database
</li><li>Recovery should work with encrypted databases
</li><li>Corruption: new error code, add help
</li><li>Space reuse: after init, scan all storages and free those that don't belong to a live database object
</li><li>Use FilterIn / FilterOut putStream?
</li><li>Access rights: add missing features (users should be 'owner' of objects; missing rights for sequences; dropping objects)
</li><li>Support NOCACHE table option (Oracle).
</li><li>Support table partitioning.
</li><li>Index usage for UPDATE ... WHERE .. IN (SELECT...)
</li><li>Add regular javadocs (using the default doclet, but another css) to the homepage.
</li><li>The database should be kept open for a longer time when using the server mode.
</li><li>Javadocs: for each tool, add a copy &amp; paste sample in the class level.
</li><li>Javadocs: add @author tags.
</li><li>Fluent API for tools: Server.createTcpServer().setPort(9081).setPassword(password).start();
</li><li>MySQL compatibility: real SQL statement for DESCRIBE TEST
</li><li>Use a default delay of 1 second before closing a database.
</li><li>Write (log) to system table before adding to internal data structures.
</li><li>Support opening a database that is in the classpath, maybe using a new file system.
</li><li>Doclet (javadocs): constructors are not listed.
</li><li>Support direct lookup for MIN and MAX when using WHERE (see todo.txt / Direct Lookup).
</li><li>Support other array types (String[], double[]) in PreparedStatement.setObject(int, Object);
</li><li>MVCC should not be memory bound (uncommitted data is kept in memory in the delta index; maybe using a regular b-tree index solves the problem).
</li><li>Oracle compatibility: support NLS_DATE_FORMAT.
</li><li>Cluster: add feature to make sure cluster nodes can not get out of sync (for example by stopping one process).
</li><li>H2 Console: support CLOB/BLOB download using a link.
</li><li>Support flashback queries as in Oracle.
</li><li>Import / Export of fixed with text files.
</li><li>Support getGeneratedKeys to return multiple rows when used with batch updates.
    This is supported by MySQL, but not Derby. Both PostgreSQL and HSQLDB don't support getGeneratedKeys.
    Also support it when using INSERT ... SELECT.
</li><li>HSQLDB compatibility: automatic data type for SUM if value is the value is too big (by default use the same type as the data).
</li><li>Improve the optimizer to select the right index for special cases: where id between 2 and 4 and booleanColumn
</li><li>Linked tables: make hidden columns available (Oracle: rowid and ora_rowscn columns).
</li><li>H2 Console: in-place autocomplete.
</li><li>Support large databases: split database files to multiple directories / disks (similar to tablespaces).
</li><li>Support to assign a primary key index a user defined name.
</li><li>H2 Console: support configuration option for fixed width (monospace) font.
</li><li>Native fulltext search: support analyzers (specially for Chinese, Japanese).
</li><li>Automatically compact databases from time to time (as a background process).
</li><li>Test Eclipse DTP.
</li><li>H2 Console: autocomplete: keep the previous setting
</li><li>MySQL, MS SQL Server compatibility: support case sensitive (mixed case) identifiers without quotes.
</li><li>executeBatch: option to stop at the first failed statement.
</li><li>Implement OLAP features as described here: http://www.devx.com/getHelpOn/10MinuteSolution/16573/0/page/5
</li><li>Support Oracle ROWID (unique identifier for each row).
</li><li>Server mode: improve performance for batch updates.
</li><li>Applets: support read-only databases in a zip file (accessed as a resource).
</li><li>Long running queries / errors / trace system table.
</li><li>H2 Console should support JaQu directly.
</li><li>Document FTL_SEARCH, FTL_SEARCH_DATA.
</li><li>Sequences: CURRVAL should be session specific. Compatibility with PostgreSQL.
</li><li>Auto-server: add option to define the IP address range or list.
</li><li>Index creation using deterministic functions.
</li><li>Support DELETE with TOP or LIMIT. See also: http://dev.mysql.com/doc/refman/5.1/de/delete.html
</li><li>ANALYZE: for unique indexes that allow null, count the number of null.
</li><li>AUTO_SERVER: support changing IP addresses (disable a network while the database is open).
</li><li>Avoid using java.util.Calendar internally because it's slow, complicated, and buggy.
</li><li>Support TRUNCATE .. CASCADE like PostgreSQL.
</li><li>Fulltext search: lazy result generation using SimpleRowSource.
</li><li>Support transformation to join for user defined functions, as for IN(SELECT...).
</li><li>Fulltext search: support alternative syntax: WHERE FTL_CONTAINS(name, 'hello').
</li><li>MySQL compatibility: support REPLACE, see http://dev.mysql.com/doc/refman/6.0/en/replace.html and issue 73.
</li><li>MySQL compatibility: support INSERT INTO table SET column1 = value1, column2 = value2
</li><li>Docs: add a one line description for each functions and SQL statements at the top (in the link section).
</li><li>Javadoc search: weight for titles should be higher ('random' should list Functions as the best match).
</li><li>Replace information_schema tables with regular tables that are automatically re-built when needed. Use indexes.
</li><li>Delete temporary files or objects using finalize.
</li><li>Oracle compatibility: support calling 0-parameters functions without parenthesis. Make constants obsolete.
</li><li>MySQL, HSQLDB compatibility: support where 'a'=1 (not supported by Derby, PostgreSQL)
</li><li>Allow calling function with no parameters without parenthesis. See http://code.google.com/p/h2database/issues/detail?id=50
</li><li>CSV: currently # is a line comment and can start at any field. Make it optional.
</li><li>Support a data type "timestamp with timezone" using java.util.Calendar.
</li><li>Finer granularity for SLF4J trace - See http://code.google.com/p/h2database/issues/detail?id=62
</li><li>Add database creation date and time to the database.
</li><li>Support ASSERTION.
</li><li>MySQL compatibility: support comparing 1='a'
</li><li>Support PostgreSQL lock modes: http://www.postgresql.org/docs/8.3/static/explicit-locking.html
</li><li>PostgreSQL compatibility: test DbVisualizer and Squirrel SQL using a new PostgreSQL JDBC driver.
</li><li>RunScript should be able to read from system in (or quite mode for Shell).
</li><li>Natural join: support select x from dual natural join dual.
</li><li>Support using system properties in database URLs (may be a security problem).
</li><li>Natural join: somehow support this: select a.x, b.x, x from dual a natural join dual b
</li><li>Use the Java service provider mechanism to register file systems and function libraries.
</li><li>MySQL compatibility: for auto_increment columns, convert 0 to next value (as when inserting NULL).
</li><li>Optimization for multi-column IN: use an index if possible. Example: (A, B) IN((1, 2), (2, 3)).
</li><li>Optimization for EXISTS: convert to inner join or IN(..) if possible.
</li><li>Functions: support hashcode(value); cryptographic and fast
</li><li>Serialized file lock: support long running queries.
</li><li>Network: use 127.0.0.1 if other addresses don't work.
</li><li>Pluggable network protocol (currently  Socket/ServerSocket over TCP/IP) - see also TransportServer with master slave replication.
</li><li>Select for update in mvcc mode: only lock the selected records.
</li><li>Support reading JCR data: one table per node type; query table; cache option
</li><li>OSGi: create a sample application, test, document.
</li><li>help.csv: use complete examples for functions; run as test case.
</li><li>Functions to calculate the memory and disk space usage of a table, a row, or a value.
</li><li>Re-implement PooledConnection; use a lightweight connection object.
</li><li>Doclet: convert tests in javadocs to a java class.
</li><li>Doclet: format fields like methods, but support sorting by name and value.
</li><li>Doclet: shrink the html files.
</li><li>MySQL compatibility: support SET NAMES 'latin1' - See also http://code.google.com/p/h2database/issues/detail?id=56
</li><li>MySQL compatibility: DELETE .. FROM .. USING - See http://dev.mysql.com/doc/refman/5.0/en/delete.html
</li><li>Allow to scan index backwards starting with a value (to better support ORDER BY DESC).
</li><li>Java Service Wrapper: try http://yajsw.sourceforge.net/
</li><li>Batch parameter for INSERT, UPDATE, and DELETE, and commit after each batch. See also MySQL DELETE.
</li><li>MySQL compatibility: support ALTER TABLE .. MODIFY COLUMN.
</li><li>Use a lazy and auto-close input stream (open resource when reading, close on eof).
</li><li>PostgreSQL compatibility: generate_series.
</li><li>Connection pool: 'reset session' command (delete temp tables, rollback, auto-commit true).
</li><li>Improve SQL documentation, see http://www.w3schools.com/sql/
</li><li>MySQL compatibility: DatabaseMetaData.stores*() methods should return the same values. Test with SquirrelSQL.
</li><li>MS SQL Server compatibility: support DATEPART syntax.
</li><li>Sybase/DB2/Oracle compatibility: support out parameters in stored procedures - See http://code.google.com/p/h2database/issues/detail?id=83
</li><li>Support INTERVAL data type (see Oracle and others).
</li><li>Combine Server and Console tool (only keep Server).
</li><li>Store the Lucene index in the database itself.
</li><li>Support standard MERGE statement: http://en.wikipedia.org/wiki/Merge_%28SQL%29
</li><li>Oracle compatibility: support DECODE(x, ...).
</li><li>MVCC: compare concurrent update behavior with PostgreSQL and Oracle.
</li><li>HSQLDB compatibility: CREATE FUNCTION (maybe using a Function interface).
</li><li>HSQLDB compatibility: support CALL "java.lang.Math.sqrt"(2.0)
</li><li>Support comma as the decimal separator in the CSV tool.
</li><li>Compatibility: Support jdbc:default:connection using ThreadLocal (part of SQLJ)
</li><li>Compatibility: Java functions with SQLJ Part1 http://www.acm.org/sigmod/record/issues/9912/standards.pdf.gz
</li><li>Compatibility: Java functions with SQL/PSM (Persistent Stored Modules) - need to find the documentation.
</li><li>CACHE_SIZE: automatically use a fraction of Runtime.maxMemory - maybe automatically the second level cache.
</li><li>Support date/time/timestamp as documented in http://en.wikipedia.org/wiki/ISO_8601
</li><li>PostgreSQL compatibility: when in PG mode, treat BYTEA data like PG.
</li><li>Support =ANY(array) as in PostgreSQL. See also http://www.postgresql.org/docs/8.0/interactive/arrays.html
</li><li>IBM DB2 compatibility: support PREVIOUS VALUE FOR sequence.
</li><li>MySQL compatibility: alter table add index i(c), add constraint c foreign key(c) references t(c);
</li><li>Compatibility: use different LIKE ESCAPE characters depending on the mode (disable for Derby, HSQLDB, DB2, Oracle, MSSQLServer).
</li><li>Oracle compatibility: support CREATE SYNONYM table FOR schema.table.
</li><li>Optimize A=? OR B=? to UNION if the cost is lower.
</li><li>FTP: document the server, including -ftpTask option to execute / kill remote processes
</li><li>FTP: problems with multithreading?
</li><li>FTP: implement SFTP / FTPS
</li><li>FTP: access to a database (.csv for a table, a directory for a schema, a file for a lob, a script.sql file).
</li><li>More secure default configuration if remote access is enabled.
</li><li>Improve database file locking (maybe use native file locking). The current approach seems to be problematic
    if the file system is on a remote share (see Google Group 'Lock file modification time is in the future').
</li><li>Document internal features such as BELONGS_TO_TABLE, NULL_TO_DEFAULT, SEQUENCE.
</li><li>Issue 107: Prefer using the ORDER BY index if LIMIT is used.
</li><li>An index on (id, name) should be used for a query: select * from t where s=? order by i
</li><li>Support reading sequences using DatabaseMetaData.getTables(null, null, null, new String[]{"SEQUENCE"}).
    See PostgreSQL.
</li><li>Add option to enable TCP_NODELAY using Socket.setTcpNoDelay(true).
</li><li>Maybe disallow = within database names (jdbc:h2:mem:MODE=DB2 means database name MODE=DB2).
</li><li>Fast alter table add column.
</li><li>Improve concurrency for in-memory database operations.
</li><li>Issue 122: Support for connection aliases for remote tcp connections.
</li><li>Fast scrambling (strong encryption doesn't help if the password is included in the application).
</li><li>H2 Console: support -webPassword to require a password to access preferences or shutdown.
</li><li>Issue 126: The index name should be "IDX_" plus the constraint name unless there is a conflict, in which case append a number.
</li><li>Issue 127: Support activation/deactivation of triggers
</li><li>Issue 130: Custom log event listeners
</li><li>Issue 131: IBM DB2 compatibility: sysibm.sysdummy1
</li><li>Issue 132: Use Java enum trigger type.
</li><li>Issue 134: IBM DB2 compatibility: session global variables.
</li><li>Cluster: support load balance with values for each server / auto detect.
</li><li>FTL_SET_OPTION(keyString, valueString) with key stopWords at first.
</li><li>Pluggable access control mechanism.
</li><li>Fulltext search (Lucene): support streaming CLOB data.
</li><li>Document/example how to create and read an encrypted script file.
</li><li>Check state of https://issues.apache.org/jira/browse/OPENJPA-1367 (H2 does support cross joins).
</li><li>Fulltext search (Lucene): only prefix column names with _ if they already start with _. Instead of DATA / QUERY / modified use _DATA, _QUERY, _MODIFIED if possible.
</li><li>Support a way to create or read compressed encrypted script files using an API.
</li><li>Scripting language support (Javascript).
</li><li>The network client should better detect if the server is not an H2 server and fail early.
</li><li>H2 Console: support CLOB/BLOB upload.
</li><li>Move away from system properties where possible.
</li><li>Database file lock: detect hibernate / standby / very slow threads (compare system time).
</li><li>Automatic detection of redundant indexes.
</li><li>Maybe reject join without "on" (except natural join).
</li><li>Implement GiST (Generalized Search Tree for Secondary Storage).
</li><li>Function to read a number of bytes/characters from an BLOB or CLOB.
</li><li>Issue 156: Support SELECT ? UNION SELECT ?.
</li><li>Automatic mixed mode: support a port range list (to avoid firewall problems).
</li><li>Support the pseudo column rowid, oid, _rowid_.
</li><li>H2 Console / large result sets: stream early instead of keeping a whole result in-memory
</li><li>Support TRUNCATE for linked tables.
</li><li>UNION: evaluate INTERSECT before UNION (like most other database except Oracle).
</li><li>Delay creating the information schema, and share metadata columns.
</li><li>TCP Server: use a nonce (number used once) to protect unencrypted channels against replay attacks.
</li><li>Simplify running scripts and recovery: CREATE FORCE USER (overwrites an existing user).
</li><li>Support CREATE DATABASE LINK (a custom JDBC driver is already supported).
</li><li>Support large GROUP BY operations. Issue 216.
</li><li>Issue 163: Allow to create foreign keys on metadata types.
</li><li>Logback: write a native DBAppender.
</li><li>Cache size: don't use more cache than what is available.
</li><li>Tree index: Instead of an AVL tree, use a general balanced trees or a scapegoat tree.
</li><li>User defined functions: allow to store the bytecode (of just the class, or the jar file of the extension) in the database.
</li><li>Compatibility: ResultSet.getObject() on a CLOB (TEXT) should return String for PostgreSQL and MySQL.
</li><li>Optimizer: WHERE X=? AND Y IN(?), it always uses the index on Y. Should be cost based.
</li><li>Make the cache scan resistant (currently a small cache is faster than a large cache for large table scans).
</li><li>Issue 178: Optimizer: index usage when both ascending and descending indexes are available.
</li><li>Issue 179: Related subqueries in HAVING clause
</li><li>IBM DB2 compatibility: NOT NULL WITH DEFAULT. Similar to MySQL Mode.convertInsertNullToZero.
</li><li>Creating primary key: always create a constraint.
</li><li>Maybe use a different page layout: keep the data at the head of the page, and ignore the tail
    (don't store / read it). This may increase write / read performance depending on the file system.
</li><li>Indexes of temporary tables are currently kept in-memory. Is this how it should be?
</li><li>The Shell tool should support the same built-in commands as the H2 Console.
</li><li>Maybe use PhantomReference instead of finalize.
</li><li>Database file name suffix: a way to use no or a different suffix (for example using a slash).
</li><li>Database file name suffix: should only have one dot by default. Example: .h2db
</li><li>Issue 196: Function based indexes
</li><li>Fix the disk space leak (killing the process at the exact right moment will increase
    the disk space usage; this space is not re-used). See TestDiskSpaceLeak.java
</li><li>ROWNUM: Oracle compatibility when used within a subquery. Issue 198.
</li><li>Allow to access the database over HTTP (possibly using port 80) and a servlet in a REST way.
</li><li>ODBC: encrypted databases are not supported because the ;CIPHER= can not be set.
</li><li>Support CLOB and BLOB update, specially conn.createBlob().setBinaryStream(1);
</li><li>Optimizer: index usage when both ascending and descending indexes are available. Issue 178.
</li><li>Triggers: support user defined execution order. Oracle:
    CREATE OR REPLACE TRIGGER TEST_2 BEFORE INSERT
    ON TEST FOR EACH ROW FOLLOWS TEST_1.
    SQL specifies that multiple triggers should be fired in time-of-creation order.
    PostgreSQL uses name order, which was judged to be more convenient.
    Derby: triggers are fired in the order in which they were created.
</li><li>PostgreSQL compatibility: combine "users" and "roles". See:
    http://www.postgresql.org/docs/8.1/interactive/user-manag.html
</li><li>Improve documentation of system properties: only list the property names, default values, and description.
</li><li>Support running totals / cumulative sum using SUM(..) OVER(..).
</li><li>Improve object memory size calculation. Use constants for known VMs, or use reflection to call java.lang.instrument.Instrumentation.getObjectSize(Object objectToSize)
</li><li>Triggers: NOT NULL checks should be done after running triggers (Oracle behavior, maybe others).
</li><li>Common Table Expression (CTE) / recursive queries: support INSERT INTO ... SELECT ... Issue 219.
</li><li>Common Table Expression (CTE) / recursive queries: support non-recursive queries. Issue 217.
</li><li>Common Table Expression (CTE) / recursive queries: avoid endless loop. Issue 218.
</li><li>Common Table Expression (CTE) / recursive queries: support multiple named queries. Issue 220.
</li><li>Common Table Expression (CTE) / recursive queries: identifier scope may be incorrect. Issue 222.
</li><li>Common Table Expression (CTE) / recursive queries: support parameters.
</li><li>Log long running transactions (similar to long running statements).
</li><li>Support schema specific domains.
</li><li>Parameter data type is data type of other operand. Issue 205.
</li><li>Some combinations of nested join with right outer join are not supported.
</li><li>DatabaseEventListener.openConnection(id) and closeConnection(id).
</li><li>Compatibility for data type CHAR (Derby, HSQLDB). Issue 212.
</li><li>Compatibility with MySQL TIMESTAMPDIFF. Issue 209.
</li><li>Optimizer: use a histogram of the data, specially for non-normal distributions.
</li><li>Trigger: allow declaring as source code (like functions).
</li><li>User defined aggregate: allow declaring as source code (like functions).
</li><li>The error "table not found" is sometimes caused by using the wrong database.
    Add "(this database is empty)" to the exception message if applicable.
</li><li>PostgreSQL compatibility: support escape with double \\.
</li><li>Document the TCP server "management_db". Maybe include the IP address of the client.
</li><li>Use javax.tools.JavaCompilerTool instead of com.sun.tools.javac.Main
</li><li>If a database object was not found in the current schema, but one with the same name existed in another schema, included that in the error message.
</li><li>Optimization to use an index for OR when using multiple keys: where (key1 = ? and key2 = ?) OR (key1 = ? and key2 = ?)
</li><li>JaQu should support a DataSource and a way to create a Db object using a Connection (for multi-threaded usage with a connection pool).
</li><li>Move table to a different schema (rename table to a different schema), possibly using ALTER TABLE ... SET SCHEMA ...;
</li><li>nioMapped file system: automatically fall back to regular (non mapped) IO if there is a problem (out of memory exception for example).
</li><li>Column as parameter of function table. Issue 228.
</li><li>Connection pool: detect ;AUTOCOMMIT=FALSE in the database URL, and if set,
    disable autocommit for all connections.
</li><li>Compatibility with MS Access: support "&amp;" to concatenate text.
</li><li>The BACKUP statement should not synchronize on the database, and therefore should not block other users.
</li><li>Listener or authentication module for new connections, or a way to restrict the number of different connections to a tcp server,
    or to prevent to login with the same username and password from different IPs.
    Possibly using the DatabaseEventListener API, or a new API.
</li><li>Support ALTER TABLE ADD COLUMN IF NOT EXISTS.
</li><li>Document the database file format.
</li><li>Support reading LOBs.
</li><li>Require appending DANGEROUS=TRUE when using certain dangerous settings such as
    LOG=0, LOG=1, LOCK_MODE=0, disabling FILE_LOCK,
</li></ul>

<h2>Not Planned</h2>
<ul>
<li>HSQLDB (did) support this: select id i from test where i&lt;0 (other databases don't). Supporting it may break compatibility.
</li><li>String.intern (so that Strings can be compared with ==) will not be used because some VMs have problems when used extensively.
</li><li>In prepared statements, identifier names (table names and so on) can not be parameterized. Adding such a feature would complicate the source code without providing reasonable speedup, and would slow down regular prepared statements.
</li></ul>

<!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html>