Roadmap

New Features

• New database properties: garbageCollectStatements, garbageCollectPercent, defaultIsolationLevel, timeFormat.
• SHUTDOWN command now accepts the qualifier, THIS_DATABASE. When present, this causes only the database on the current connection to close. Without this qualifier, all databases are closed.
• All four transaction isolation levels are now supported with full read and write capabilities in all levels.
• New __IDB_INTERNAL parameters:
  • data_type - returns the java.sql.Types type for the column.
  • readers_waiting - the number of connections waiting to get a read lock on the table.
  • read_locks - the number of read locks granted so far on a table.
  • write_locks - the number of write locks granted so far on a table.
  • readers - the number of connections with a read lock on the table.
  • writers - the number of connections with a write lock on the table.
• DBBrowser can now run ScriptTool scripts.
• ScriptTool will ignore lines which begin with a "/" character. In particular, this allows one line comments of the form "/* a comment */" to be ignored.

Bug fixes

• DatabaseMetaData reported that there was no DOUBLE type. Fixed.
• The default DATETIME format was incorrect. Fixed.
• Multiple writers would sometimes deadlock when auto-commit was switched off. Fixed.
• SQLBuilder failed to provide a full path for its resource bundle. Fixed.
• Outer joins on empty tables could lead to ClassCasteExceptions. Fixed.
• Timestamps reported their type as java.sql.Types.DATE instead of TIMESTAMP. Fixed.
• Database meta-data could not be read for databases with hyphenated names. Fixed.
• ALTER TABLE failed to modify the size of the read ahead buffer when a new column was added. This could lead to errors when non-cached items were read from disk. Fixed.
• __IDB_INTERNAL last_value nearly always returned at least two rows. Fixed.
• Row limited ORDER BY failed on in memory results sets. Fixed.
• '' LIKE '' returned false, whereas ANSI requires this to be true. Fixed.
• PreparedStatemnts with NULL parameters near then end of their parameters lists would sometimes generate an ArrayIndexOutOfBoundsException when the parameter was set to be non-NULL. Fixed.

New Features

• New database property dateTimeFormat and new SQL statement:

  SET DATETIME FORMAT <$ time>

• Date and time formats and currency symbols can now be defined on a per column basis during table creation.
• DBBrowser and SQLBuilder have had all hard coded strings removed and placed in ResourceBundles for easy future internationalization.

Bug fixes

• Boolean parameters in PreparedStatements caused a ClassCastException during expression evaluation. Fixed.
• ON DELETE... and ON UPDATE... could not be specified as table constraints. Fixed.
• Multiple WHERE conditions could sometimes execute the wrong inequality comparisons. Fixed.
• ALTER TABLE would fail and corrupt system tables if a column was added as NOT NULL but with no DEFAULT value. Fixed.
• Multiple writing threads waiting for an existing writer, could wake up when the write lock was freed, grab multiple read locks and then would deadlock each other until the transaction timeout out. Read locks for writers are now not grabbed until the write lock is also guaranteed.
• Timestamps used floating point arithmetic to set second and millisecond values. This was subject to floating point rounding errors. Fixed.
• Scrollable results sets required a moveToInsertRow() call before every insertRow() call. However, multiple calls to insertRow() should be allowed after only a single call to moveToInsertRow(). Fixed.
• Updating of non-key fields in a parent table being referenced by a child would be denied if the non-key field was not cached. Fixed.

• The big addition in this release is Foreign Key (FK) support. Many of the items listed below relate either directly or indirectly to FK support.
• The DatabaseMetaData methods: getCrossReference, getExportedKeys and getImportedKeys are now supported.
• A new built in function, translatefkflags, has been added. It just translates InstantDB's internal FK flag settings to DatabaseMetaData values. Its mainly used by the JDBC driver in the above calls.
• A new TRACE bit has been defined: TR_FK = 8192. Setting this bit in the trace level will cause FK changes to be logged.
• The SQL syntax has changed to handle the FK support. You can always get the syntax supported by your InstantDB release by typing in the command:

      java org.enhydra.instantdb.db.SqlSyntax

• The pseudonym, VARCHAR2, is now supported. It is identical to VARCHAR.
• The options on the EXPORT command can now be in any order. Previously the order was fixed.
• InstantDB supports two modes of string literal processing. In STRICT mode, a quoted string is always a literal. In the default mode quoted strings can be interpreted as columns. A built in function, TEXT ("string literal"), can now be used in the default mode to force a quoted string to be interpreted as a string literal.
• A new expression element is now supported:

    __IDB_INTERNAL <param> FROM expr1 IN TABLE expr2

  This is intended to allow SQL access to InstantDB internal variables. The <param> must be one of a fixed set of string literals which select the internal variable required. Case is not significant.
• BIT and BOOLEAN types added.
• PreparedStatement.getMetaData implemented.
• Can now get the last value inserted using SQL:

    __IDB_INTERNAL last_value FROM TEXT("int1") IN TABLE aggrInFunc 

• Shared read locks added. As well as improving performance this will form the basis in the future for additional transaction modes.
• Support for the XA compliant JDBC drivers in Enhydra Enterprise (2-phase commits). InstantDB has introduced the new Interface org.enhydra.instantdb.jdbc.ConnectionExtensions. This interface gathers together all of the InstantDB specific extensions into one interface, including those necessary to perform 2-phase commits. InstantDB's native JDBC driver and the RmiJdbc driver, have both been modified to implement this interface.
• PreparedStatement execution sometimes generated the spurious error "Column NULL not found". Fixed.
• Defaults could not be defined for DATE types. Fixed.
• In 3.20 beta 2, FKs could not reference the table in which they themselves were defined. Fixed.
• In 3.20 beta 2, FKs could prevent updates to non-exported columns in the table they referenced. Fixed.
• ALTER TABLE DROP COLUMN... didn't alter the read ahead buffer record length. Uncached values would be wrong thereafter. Fixed.
• Aggregates were not allowed inside user defined functions. Fixed.
• ResultSetMetaData.getTableName returned the wrong value. Fixed.
• ScriptTool now accepts "--" as a comment introducer and defaults to the "e " command. This should improve compatibility with some other scripting tools.
• The IMPORT command behaved badly if there was an empty line at the end of an import file. Fixed.
• LEFT OUTER JOINs could sometimes return the wrong values. Fixed.
• If DBBrowser is started with an initial url on the command line then it now auto connects to that database.
• idbf.jar has been removed. All functions are now included in idb.jar.

• Internal release only. Identical to 3.21 apart from inclusion of the "update FK" bug.

• OUTER JOIN processing rewritten. This should cure a whole lot of bugs that prevented outer joins from correctly processing the WHERE clause.
• Objects saved using setObject could not be compared to other objects in a query. Fixed.
• idbResultsSet.dump() added - provides an easy way to dump a results set to stdout.

• Now accepts SELECT tableName.* from table...
• LIKE tests now allow arbitrary expressions rather than just column comparisons.
• Alternative locations for new tables can now be specified. Indexes are created in same place as alternate location tables.
• SET EXPORT no allows a DELIMITER (delim) clause.
• Statement.setQueryTimeout() has now been fully implemented.
• preparedStatement.setXXXXstream() functions now implemented.
• New property "timerCheck" added. Defines interval at which timeouts are checked.
• New property "prepareIgnoresEscapes" added. Allows PreparedStatement.setString() to accept strings containing "\" (backslash) to be parsed without interpreting escape characters.
• Non-updatable ResultSets now do a commit in auto-commit mode as soon as the results have been fetched. i.e. Table locks are freed even if the whole results set has not been read.
• Minor parsing enhancement to allow quoted: "table"."column" as valid syntax.
• Minor enhancement to JDBC™ Appl to allow <ENTER> after URL or SQL entry.
• LIKE clauses did not handle \\ correctly. Fixed.
• GROUP BY did not execute correctly on second run of a prepared statement. Fixed.
• SMALLCHAR did not process NULLs correctly. Fixed.
• Statement.setMaxRows(int) would truncate results sets prematurely even if an ORDER BY was included. This made it impossible to fetch the "top" or "bottom" set of values from a table. Fixed.
• The read ahead cache was not updated when rows got deleted from large tables. Fixed.
• UNIQUE PRIMARY KEYs would sometimes allow duplicates. Fixed.
• Adding a binary column using ALTER TABLE caused an exception. Fixed.
• Bug when updating binary encrypted fields. Fixed.
• Index builds which utilise a large number of similar keys were very slow. Fixed.
• Table names appearing in the SELECT part of GROUP BY expressions sometimes caused parsing errors. Fixed.

• Bug in index recovery could make recovery very slow. Fixed.
• Illegal dates were accepted. Fixed
• A bug in the Microsoft virtual machine causes certain floating point comparisons to produce erroneous results. A workaround has been put in place.
• Dropping the final column in a table would result in illegal table headers. Fixed.
• TEXT columns in IN phrases were not properly evaluated. Fixed.
• Temporary tables used in empty GROUP BY sets were not created. Fixed.
• Optimised WHERE clause could omit first matching row. Fixed.

• Added single character data type SMALLCHAR.
• Major improvements in query performance where tables are not indexed and a WHERE clause testing for equality is included.
• Major improvements in query performance of GROUP BY queries.
• GROUP BY clause has been extended to allow simple function expressions as well as column names.
• Added an INTO clause in SELECT statement.
  e.g. SELECT a,b INTO new_table FROM old_table WHERE C > 1.
• New property, searchDeletes (default 0). Default value should speed up some UPDATE statements. Setting searchDeletes=1 results in old behaviour which gives slower updates, but more compact tables.
• Readonly databases now use more compact indexes (half the size in memory).
• Index tables are now automatically saved after being recovered.
• If a table is recovered then all indexes on that table are also recovered, even if they are marked as up to date.
• GROUP BY ... HAVING caused an exception if COUNT(*) used in having clause. Fixed.
• Failing to specify all four table paths could cause an exception. Fixed.
• PreparedStatement selects using parameterized IN clauses failed. Fixed.
• Parameterless user defined functions caused an exception. Fixed.
• PreparedStatements did only allowed setString on IN clauses. Now all setXXX methods should work on IN clauses.

• Added the SQL extension to allow: UPDATE tableName SET col = (SELECT...). Currently doesn't allow references to the updated table though.
• All calls to System.gc() made by InstantDB have now been removed. It is now up to the application to decide if and when to make gc calls.
• Small negative currency values were printed out wrong. Fixed.
• Added a SET DEFAULT_USERNAME uname [DEFAULT_PASSWORD pword] statement. This sets the default username and password for imports from other JDBC™ data sources.
• Relative URLs for specifying database properties files should now work.
• The outer join syntax did not allow for pseudo tablenames. Fixed.
• IMPORTs from files now search the database properties directory and allow files to use fully qualified path names.
• Currency columns now accept values without the "." in them. "$300" is interpreted as three hundred dollars. "300" is interpreted as three hundred cents (as before).
• The interaction between outer joins and WHERE clauses was incorrect. Row selection was done by filtering on the WHERE and then adding and extending unmatched rows without further regard to the WHERE clause. Extended rows must now match the WHERE condition.
• Added SETINCREMENT_BASE {<base>|MAX} ON{<table>.<column> | ALL} ao that auto increment columns can use different base values.
• Now throws an exception when invalid escape characters are detected in strings (e.g. "abc\kde").
• ResultSetMetaData.isAutoIncrement and isNullable have been implemented correctly.
• UNIQUE table constraints now constrain columns collectively rather than individually.
• db.TableEncrypt has been extended to allow for the encryption of binary columns.

• Added support for JDBC™ 2.0 features including: scrollable results sets and batch processing.
• Numerous additions to sample.java to make use of JDBC™ 2.0 features and to support database export and import.
• Functions: UPPER, LOWER, ABS, LENGTH, TO_DATE and TO_NUMBER added.
• Added support for user defined functions.
• Support for OUTER joins added.
• Date accepts "current time" (full timestamp) as well as "now" (format dependent).
• New property: nowMeansTime. If set to 1 (one) then "now" always places a full timestamp in a date column, regardless of its date format.
• Added missing getTimestamp and getTime methods.
• Non-standard method:
  
  Object idbConnection.getLastValueInserted(String tableName, String columnName)
  has been added.
• Recovery message no longer implies database corruption. Default recovery policy is now to prompt the user.
• Serializable objects can now be saved in Binary columns.
• TEXT columns now do lexicographical comparisons rather than binary comparisons.
• Binary deletes and updates closed the transaction too early with unpredictable results. Fixed.
• Index tables attempted to write back in readonly mode. Fixed.
• ALTER TABLE n ADD UNIQUE cols failed to add the uniqueness flag correctly. Fixed.
• ResultSetMetaData.getDisplaySize returned too small a value for integers. Fixed.
• Index based queries could sometimes include/exclude erroneous bordering values. Fixed.
• Exceptions when reusing a deleted row would leave the row marked as valid. Fixed.
• Subselects which included a GROUP BY clause would be incorrectly parsed. Fixed.
• Long columns could not be dropped via ALTER TABLE. Fixed.
• Exceptions when an index was being rebuilt would make the database unopenable. Invalid indexes are now dropped during recovery with a message to stdout.

• BlobColumns created with 2.41 couldn't be reopened. Fixed.
• Blobcolumns couldn't be used with readonly databases. Fixed.
• Limited DECIMAL and NUMERIC data type support.

• Default recovery behaviour is now to not perform an automatic recovery. Database property recoveryPolicy added to change default behaviour.
• Recognises noBanner environment variable in order to suppress the opening copyright message.
• Now allows subselects in the FROM clause of SELECT statements.
• MAX (string column) was failing. Fixed.
• Extensive reorganization of web site and documentation.

• If a column uses a DATE format that does not include hours, then the column will now store only the date, not the date and time as it did previously. This enables the NOW keyword to be used to create date only entries and to query such columns using date only values or the NOW keyword.
• Facilities to specify usernames and passwords while importing from external data sources have been added.
• Expressions involving currency columns can now handle more natural decimal usage.
• Queries involving EXISTS did not always provide correct results. Fixed.
• Many subselects and correlated subselects did not produce the correct results. Fixed.
• NULL values were not properly handled in summary queries. Fixed.

• Substantial performance improvements to GROUP BY.
• TEXT and LONGCHAR types now assume that only text is included, i.e. they do not attempt to interpret numbers or filenames.
• The fastUpdate property now applies to BLOB files as well as main table files.
• ResultsSets being closed by the finalizer thread was still causing lockups. Made even more resistant to this problem from this release.

• ResultSets which were closed by the finalizer thread would sometimes lock up. This release attempts to make InstantDB less sensitive to this problem.
• Connections in the READ_UNCOMMITTED transaction mode did not lock access to the disk read ahead buffer during object instantiation. The result was an unpredictable series of IO problems. Fixed.
• Index files are now closed while not in use. This can substantially reduce the number of file handles required to access a database. This should help users experiencing difficulties where they must operate in an environment where the number of file handles is limited.

• Temporary tables are now allocated in a thread safe manner.
• idbConnection.setTableEncryption and BlobColumn.blobToString had been accidentally obfuscated. Fixed.
• Various case errors in the documentation fixed.
• ORDER BY tree rebalancing was actually slowing down many queries. Improved.
• New property, ignoreKeywords, has been added. This allows elected InstantDB reserved words to be "un-reserved" so that they can be used as table or column names.

• LIKE expressions can now be used with any data type, not just strings.
• Since the introduction of temporary tables, InstantDB has been creating all tables with 100% caching. This could lead to memory problems. Fixed.
• A new database property, altStringHashing has been introduced. This allows the change in String hashing from JDK 1.1 TO 1.2 to be overcome. See FAQ for details.
• DatabaseMetaData.getTables() now creates temporary tables in a thread safe manner.
• Subselects in UPDATE calls were not parsed correctly. Fixed.

• SQLBuilder query window made larger by default. Commented alternative import lines in code.
• The BTree sort used during ORDER BY now attempts regular tree rebalancing. Should help when the data is partially ordered.
• idbTrigger.getConnection(), idbTrigger.preUpdate() and idbConnection.getProperties() added.
• New SQL command: SHUTDOWN [NOWAIT] added.
• InstantSQL and ObjectToRdbms removed.
• JDBC™ main now takes an optional URL as a parameter at startup.
• JDBC™ Appl now loads the Rmi and bridge drivers as well as idbDriver.
• Implements own String hashing to get around the 1.1/1.2 String hash problem (see FAQ for details).
• SET EXPORT "null" now shuts down the previous export file allowing it to be manipulated by other processes.
• INSERT and UPDATE didn't accept table.col style column references. Fixed.
• CSVDELIMITED exports space padded the final field. Fixed.
• EXPORT command now has an optional QUOTE <string expr> clause. The QUOTE clause allows each field output to be quoted using the given character. e.g. SET EXPORT "mylog.txt" CSVDELIMITED QUOTE "\"", would put double quotes around each field as was output.
• New database property: likeIgnoreCase. Setting this to 1 (one) causes all LIKE string comparisons to be case insensitive.

• UPDATEs on AUTO INCREMENT columns after an ALTER TABLE caused values to accidentally increment. Fixed.
• Bug in DBSimpleInterface.java. Fixed.
• Doing a buffered import from an empty source would cause an IO exception. Fixed.
• LONGVARBINARY data type caused an exception. Fixed.
• ResultSet.getBinaryStream() caused an exception when an empty results set was returned. Fixed.
• Some database controls supplied with certain IDEs would call ResultSet.close() on an already closed ResultSet. InstantDB threw an exception on this, which is correct. Nevertheless, it still prevented InstantDB being used with those controls. InstantDB now silently ignores multiple close calls on the same ResultSet.

• SMALLINT and TINYINT pseudonyms added for INTEGER and BYTE respectively.
• Accepts an optional length parameter on binary fields. This is for compatibility with other SQL implementations only and is currently ignored.
• Uses a Date column's own date format when parsing dates. Only if this fails will alternative date formats be tried.
• Now accepts date/time escape sequences in UPDATE statements.
• Negative values could not be read into currency columns. Fixed.
• A single character question mark when used as a string literal (i.e. "?") was being misinterpreted as a parameter in prepared statements. Fixed.

• Added support for multiple simultaneous database access for the first time.
• Optimization of integer equality testing.
• GROUP BY ... HAVING added.
• Support for database encryption added.
• Results sets now held in memory by default. resultOnDisk property added to override this default behaviour.
• Many new DatabaseMetaData calls added. This should ease compatibility with many common IDE database controls.
• CREATE INDEX on an unknown table caused a NullPointerException. Fixed.
• Updated RmiJdbc installation instructions.
• Can create temporary tables using CREATE TEMPORARY TABLE.
• Added new statement: SET table AUTO INCREMENT {ON|OFF}.
• Removed the sqlsyntax.txt file.

• Added support for imports from other JDBC™ data sources.
• Class files now run under the released Java™ 2 (formerly 1.2) JVM.
• DatabaseMetaData.getTables() now distinguishes between system and user tables.
• Buffered IMPORTs speeded up to make use of the write buffer for row lookahead during index construction.
• Table locking was not always properly ordered in auto-commit statements. This could lead to deadlocks. Fixed.

• ALTER TABLE support added.
• The Sample scripting program now has some limited looping support.
• Defaults for types other than Char types now work correctly.
• Testing for NULL on a UNIQUE column would fail. Fixed.
• Circular references amongst joins would lead to infinite recursion. Fixed.
• Expressions which evaluated to a series of constants would fail. Fixed.
• Looking up a double column using a float value was subject to float roundoffs. Fixed.
• If there was only a single deleted row, and the row counts were out of sync,it was possible for a valid row to be marked deleted. Fixed.
• Calling ResultSet.close() on an already closed results set caused a NullPointerException. Fixed.
• NOT LIKE clauses gave incorrect results. Fixed.

• Various INDEX bugs sorted. Users who frequently UPDATE or DELETE rows in a table are advised to DROP and then recreate any indexes on such tables.
• Some performance improvements in ORDER BY.
• Would sometimes fail to find the indexes table when absolute paths were used. Fixed.
• Expressions - certain String null values would cause an exception. Fixed.
• Function evaluation where row selection included multiple conditions from the same table could generate an exception. Fixed.

• PreparedStatement.clearParameters() caused an exception on the next execute if the prepared statement did not have any parameters. Fixed.
• If a results set had column names of the form: "table.column", then doing a getXXX with just the column name would fail to match. Fixed.
• All path names now converted to the correct path separator character, regardless of the values supplied in the URL or in the properties file.
• SELECT COUNT(*) FROM table WHERE expression would return an empty result set if no rows matched. Now returns a single row result set with the value 0. Similar problems with SUM have also been fixed.
• A SELECT with an ORDER BY clause, when used on a readonly database would lead to a nullPointerException. Fixed.
• Changes to SQLBuilder to avoid some bugs and add layout managers to the main panels. Courtesy of Mark Ford.

• An attempt to optimize row lookahead in 1.82 actually had the opposite affect. Modified in this release.
• PreparedStatement.setXXX(), where XXX was any numeric type would fail on execution. Fixed.
• InstantDB was hanging on to Transaction objects and Thread names long after the objects had ceased to be useful. This lead to a slow, but steady growth in the memory footprint for users who frequently reconnected or frequently created new threads. Fixed.

• Prepared statements did not respond correctly to strict literal usage. Fixed.
• Auto-increment columns could be accidentally incremented during a recovery. Fixed.
• ORDER BY on results sets which included binary data could generate an internal error. Fixed.
• With the strict literals off option, any string off the form "aaaa.bbbb" would be interpreted as "table.column". This is no longer the case.

• Patch to Table.class to fix a disk read ahead overflow error introduced in 1.82.

• The JDBC™ driver did not allow calls of the form getXXX ("table.col"). This is required by certain development environments. This is now supported.
• Some support for getPrimaryKeys() added.
• The support added in version 1.7 for quoted column names and quoted integer literals, used by some development environments was causing problems in others. Users may now choose the strictness implied by quotes using either the strictLiterals property, or the SET LITERALS [STRICT_ON|STRICT_OFF] command. See the InstantDB documentation for details.
• A new property has been added to assist millenium aware applications. The property, milleniumBoundary, allows two digit dates less than the milleniumBoundary value to be interpreted as 21st century dates.
• A new property has been added for rapidly updating databases. Setting fastUpdate=1 in the database properties file removes most of the database sync() calls. This allows for faster operation when database recoverability can be achieved using data from outside InstantDB.
• MAJOR BUG. Tables which performed:
  • either a lot of adds and deletes,
  • or a lot of updates,
  • and where an indexed column was not fully cached,
  Could pick up the wrong value for the index. This was caused by the hash routine picking up values from the disk read ahead buffer which had not yet been updated to reflect the newly added row. Fixed. Users worried that they may be affected by this bug should DROP the affected index and issue a new CREATE INDEX command.
• Support has been added for the getTimestamp() and setTimestamp() calls.

• Instead of sync()ing after every database write, sync()s were restricted to transaction completion. Helps improve performance.

• Removed all calls to deprecated APIs. Database engine now 1.1 compliant.
• Move to 1.1 enabled use of java.io.FileDescriptor.sync() method. Should greatly increase the reliability of the database in the event of unexpected program termination.
• Expressions of the form SELECT value FROM table WHERE value LIKE "" (empty LIKE clause) would fail. Fixed.
• Expressions inside numeric reporting functions, e.g. SUM (a+b), were not evaluated correctly. Only the first term in the expression was included. Fixed.
• Insert statements that included a subselect which included a date would fail. Fixed.

• Major re-write of prepared statement handling. Prepared statements now execute up to 30% faster.
• Support for PreparedStatement.setBytes() and setObject() added.
• Examples modified to use Class.forname("idbDriver").newInstance(). Class.forName() by itself was insufficient for some JVMs.
• JBuilder can generate clauses of the form WHERE "numcolumn" = "123". InstantDB previously interpreted all quoted strings as string literals. Quoted column names are now recognized and quoted integers are interpreted as numbers where necessary.
• Now generates an exception for unimplemented JDBC™ methods.
• Expressions of the form ((col1+col2)+col3) were incorrectly evaluated. Fixed.
• Can now recover from inconsistent deleted row counts.