Home / Documentation / FAQ / Support / Download Mckoi SQL Database - Version History

Mckoi SQL Database History

Version 0.94 beta - September 17, 2002
Version 0.93 beta - April 11, 2002
Version 0.92 beta - October 28, 2001
Version 0.91 beta - September 6, 2001
Version 0.90 beta - May 23, 2001
Version 0.89 beta - February 25, 2001
Version 0.88b beta - December 16, 2000
Version 0.88 beta - December 10, 2000
Version 0.87 beta - November 12, 2000
Version 0.86 beta - October 15, 2000
Version 0.85 beta - September 23, 2000

Version 0.94 beta - September 17, 2002

• (in 0.94h) BIT and NUMERIC types are now comparable.

• (in 0.94h) Added several null checks to the setxxx methods of the JDBC driver.

• (in 0.94h) Fixed NullPointerException in ResultSet.getBigDecimal

• (in 0.94h) Removed a confusing message output to System.out in the JDBC client.

• (in 0.94h) There is now only one SQL parser instance per JVM managed by the evaluator to reduce memory requirements.

• (in 0.94h) Non indexed range search error fixed (bug report by Elmar Haneke).

• (in 0.94h) Aggregate functions are no longer cached in a FunctionTable.

• (in 0.94h) Fixed error that causes an exception when the query planner plans a union on an aliased table.

• (in 0.94h) Index problem error (non-corrupting) fixed. Bug report by Tervo Salminen.

• (in 0.94h) Fixed bug generated when using GROUP BY in a VIEW (bug report by Scott).

• (in 0.94h) Added Jim McBeath's best search constructor patch.

• (in 0.94h) Repair tool improvements for fixing corrupt databases.

• (in 0.94h) Sequence id for system tables are now reset when a database is repaired or upgraded.

• (in 0.94h) NIO disabled on 95/98 machines.

• (in 0.94g) Fixed double open repair tool problem in V2MasterTableDataSource. Thanks to Elmar Haneke for the report.

• (in 0.94g) Workaround for NIO file synch issues under Windows 98.

• (in 0.94g) Repair scan change in com.mckoi.store.AbstractStore because the recursive nature of the scan was causing stack overflow problems.

• (in 0.94g) Fixed incorrect handling of NULL in joins. Thanks to David Tinker for the report.

• (in 0.94g) BIT and NUMERIC are now comparable types. Thanks to Aaron Westendorf for the report.

• (in 0.94f) Added 'create_or_boot' flag to JDBC URL which is the same as the 'boot_or_create' flag.

• (in 0.94f) The PreparedStatement.setObject method now correctly casts primitive Java language objects as per the JDBC specification.

• (in 0.94e) Fixed outer join regression bug. Thanks to Bernard for providing the test case.

• (in 0.94e) Fixed IN regression bug, and bug with aggregated functions and NULL. Thanks to Doug Currie (http://hibernate.sourceforge.net/).

• (in 0.94e) Performance enhancement for natural joins.

• (in 0.94d) Upgraded grammar to JavaCC 2.1 and enabled parsing of unicode characters.

• (in 0.94d) Large result set memory use optimizations made to FunctionTable and jdbcserver.AbstractJDBCDatabaseInterface.

• (in 0.94d) Fixed DataCellCache bug that might cause more cache misses than necessary under heavy record recycling conditions.

• Added String types that may be given locale collation ordering. For example, japanese_string TEXT COLLATE 'jaJP'.

• Binary data can now be collated. An index will not be created on binary data.

• Referential actions are now processed when data in a table is deleted/updated. For example, customer_name REFERENCES Customer(name) ON UPDATE CASCADE will cause an update made to Customer to be reflected in any 'customer_name' keys in the table.

• The configuration file for making a local database connection can now be inside a .jar file. For exmaple; jdbc:mckoi:local://myjarfile.jar!/mckoi/mydbsettings.conf.

• Rewrote com.mckoi.tools.DataFileConvertTool to be used to convert between different versions of the database file format. The database will now fail to work with incompatible file formats.

• Overhaul of the engine type system to add strong type features. NULLs are now passed with their original type information.

• Added a basic CALL syntax to the grammar to allow for backups of the database to be made while live. For example; CALL SYSTEM_MAKE_BACKUP('/my_mckoi/backups/1').

• COMPACT TABLE will now ALWAYS compact a table to the smallest size.

• Added transient (non serialized) caching for Function objects in FunctionDef to improve function performance.

• Completed CREATE USER, DROP USER and GRANT, REVOKE syntax.

• 0.94 features a redesigned file heap manager that uses the new memory mapping functionality from the NIO API in Java 1.4. The new file structure should improve database read/write operations and allow for future extensibility of the database file format.

• Added VIEW functionality to the engine that can be used via CREATE VIEW and DROP VIEW queries.

• Added a shutdown hook on 1.3 Java VMs that attempts to wait for any write operations to finish before allowing the VM to exit.

• Fixed referential integrity bug when deleting a referenced record with a key that is null. Thanks to Patrick Godeau.

• Fixed a bug with using upper-case boolean literals. Thanks to Bernard for reporting the bug.

• An error is now thrown when an attempt is made to insert values into a column of a table that doesn't exist. The previous behaviour was that the problem was silently ignored.

• Fixed a bug with ORDER BY - if the order direction was not specified, it defaulted to the direction of the previous column. It now defaults to ASC if no direction specified.

• SUBSTRING function changed to be SQL compliant.

• Changed handling of NULL with boolean operators. An expression WHERE column_with_null_values = NULL now evaluates to NULL which results in no values being found. An expression that evaluates to TRUE for NULL values is WHERE column_with_null_values IS NULL.

• Fixed a bug with NOT IN and ANY <> that occurred when the sub-query contained an empty result. Thanks to Kevin Schmidt for finding the bug and providing the test case.

• Fixed bug with DatabaseMetaData.getPrimaryKeys when engine set to case-insensitive identifier mode. Thanks to Partrick Godeau.

• Inner join bug fix for some complex uses of inner joins. Thanks to Bernard and Jim McBeath for bug reports.

• Fixed a bug with DISTINCT and ORDER BY in a SELECT that caused incorrect ordering due to a problem with the query planner. Thanks to M. A. Sridhar for finding the error and producing the test case.

• Fixed a caching problem when updating information that passed over the max cache size threshold. Thanks to Frank Hemer for finding the problem and spending the time to produce a test case that reproduced the problem.

Version 0.93 beta - April 11, 2002

• Changed DatabaseMetaData.getPrimaryKeys so that a schema parameter of null includes all schema in the primary keys search. Also changed getImportedKeys, getExportedKeys and getCrossReference methods for the same reason.

• String literals parsed through the SQL grammar now allow for multiple lines.

• Included Jim McBeath's JavaDoc HTML files and build instructions with the source.

• MSQLException now overwrites the printStackTrace methods to better inform of the error that occurred.

• Added a new API for controlling Mckoi databases running within the JVM.

  The new API is found in the com.mckoi.database.control package and allows for programmatic configuration, JDBC access and low-level access to one or more Mckoi database engines running in the current JVM. It also provides for binding of TCP/IP Mckoi JDBC servers to local databases.

• Extensive engine plumbing changes.

  The engine now supports multiple databases running within the same JVM. Debug logging and system properties and services are no longer optained via static references.

• Implemented an internal java.sql.Connection that is accessed from a DatabaseConnection object. This allows the possibility of SQL queries through a JDBC interface against the database from inside the engine.

• Improved the data cache hashing size algorithm to scale better for larger sized caches.

• Changed behaviour in 0.93pr1 to allow ORDER BY on columns not selected.

• Fixed bug in a correlated sub-query with a constant on the LHS of the sub-query operator. eg. 4 in ( select .... Thanks to Doug Currie (http://hibernate.sourceforge.net/).

• Added BLOB type which is an alias of LONGVARBINARY.

• 'user' and 'password' properties can now be specified in the remote JDBC URL string. For example, jdbc:mckoi://localhost/?user=my_username&password=my_password.

• Comparing against SQL CHAR types now pads or truncates as necessary.

• Bug fixed with planner code on a join behind an OR. Thanks to Bill Lear.

• If the java.util.regex package is detected (available in 1.4) the engine will now use it instead of the GNU regex library.

• Expressions may now include the NOT operator. For example, NOT (A > 3 OR B = 2) which is equivalent to (A <= 3 AND B <> 2).

• Added support for correlated sub-queries.

• A new query planning architecture has been implemented for this release.

• The getX(String, ... methods in ResultSet now ignore the case of the identifier if the database is setup for case insensitive identifiers.

• Unique identifiers now start at 1 instead of 0 for freshly created tables.

• Inline aggregate functions are now allowed in the HAVING clause. Previously the only way to use an aggregate function was to reference it indirectly via a named aggregate function column.

• Fixed a synchronization problem in Database.java. Thanks to Brent Taylor for the very detailed bug report.

• Fixed a bug with the client-server protocol that under some situations would cause the query to not include all the arguments in a PreparedStatement. Thanks to Volker Turau.

• Changed MPreparedStatement and CastHelper classes to correctly cast long values to a BigDecimal. Previously the engine was silently casting long values to double before being cast to a BigDecimal. Thanks to Doug Currie (http://hibernate.sourceforge.net/) for finding the error.

• MPreparedStatement.setCharacterStream bug fixed. Thanks to Victor Chew.

• ALTER TABLE table ADD CONSTRAINT FOREIGN KEY with columns specified in the REFERENCES part bug fixed.

• Fixed bug in MPreparedStatement.execute() that returned true instead of false for update results. Thanks to Gyuri Lajos and John Kos at EasySoft.

• The MResultSetMetaData.getPrecision method now returns the size of string *CHAR SQL types. This change was made for JBuilder compatibility.

• MDatabaseMetaData now supports the 'type' parameter in the getTables method.

• Fixed issue in com/mckoi/jfccontrols/QueryAgent.java that could cause an indefinite block under certain thread situations. Thanks to Mike Calder and Frank Hemer.

• ResultSet.wasNull bug fixed for embedded engines. Thanks to Steve Feinstein.

• PreparedStatement.setObject now handles byte[] arrays correctly. BINARY fields can now be set to null. Thanks to Patrick Godeau.

• Fixed a NullPointerException when dropping a column of a table with no primary key. Thanks to Jim McBeath.

• Fixed LIKE bug caused by error in implementation of BlockIntegerList. Thanks to Elmar Haneke.

Version 0.92 beta - October 28, 2001

• 'This is not a grouping function' error message changed to be more descriptive.

• New Function: DATEFORMAT(date, format_string)

  Formats a date using the Java date formatting API available in the java.text package. For example, SELECT DATEFORMAT(DATE '2001-12-25', 'EEEE') = 'Tuesday' (the day of the week of Christmas in 2001). See the class API documentation of java.text.SimpleDateFormat for details of the pattern format 'format_string'.

• Changes made to the incremental RID list generation code because non-deleted record scanning was synchronized incorrectly.

• Grammar now accepts exponent part for numeric literals, as in the example 0.173E-2.

• Fixed bug that occurs when SQL functions are in DEFAULT and CHECK clauses in CREATE TABLE. Thanks to Alan Littleford.

• Fixed constraint violation bug for updated records. Thanks to Patrick Godeau.

• Corrected JDBC API getColumnName to return the column name instead of an incorrect more verbose form.

  This may cause compatibility problems. A method was added to MckoiConnection to toggle verbose column names.

• LIKE and REGEX now handle NULL input correctly.

• Medium-high impact architectural change on core index storage system.

  This change was to address scalability issues from the previous implementation. Now index information is paged to disk and an index update does not require a full write of the entire index to disk. This reduces operating system memory requirements significantly for large tables and should also reduce file system resources. Additionally it scales table update performance for large tables. This change paves the way for more customizable index schemes in the future such as multi-column and functional.

• Medium impact change of referential integrity conflict detection code.

  The engine now correctly supports deferred and immediate constraint checks.

• Support for foreign key constraint checks.

• Altering or dropping tables/constraints will now cause referential integrity errors if a violation is detected.

• Check expressions are now serializable.

• Medium impact revision of constraint handling.

  Integrity constraint resolution is now handled during primitive table operations to allow for efficient deferred integrity constraint checking. This change caused alterations to DatabaseConnection and Transaction. Also TableDataConglomerate now manages the internal persistent constraint tables.

• Fixed bug that prevented defining a self referencing table in a CREATE TABLE query.

• Grammar now accepts quoted deliminated identifiers of the form "Flight"."departs".

• ResultSet.getString method now returns an SQL-92 formatted date/time/timestamp object.

• Operator changes so (NULL AND TRUE) IS NULL, (NULL AND FALSE) = FALSE, and (NULL OR TRUE) = TRUE, (NULL OR FALSE) IS NULL.

• Building a RID list (secondary index on a column) is now an incremental operation.

  This means the table is not locked while the index is being built and read/write operations can continue to occur while the index is being built in the background.

• The SQL parser now 'compiles' to an intermediary form that is passed to the interpreter.

  This change allows parsed statements to be cached on the server and will help in implementing future query planning/optimization systems.

• Values are now cast correctly in the INSERT and UPDATE statements.

  For example, defining a FLOAT column will only allow values of float precision to be stored.

• The CHAR SQL type is now supported correctly.

  CHAR is used to store strings of fixed length. Any attempt to store a string smaller than the size of a CHAR field is padded with whitespace to the size specified for the field.

• Fixed a bug introduced in 0.91 on non-complex sub-queries. Thanks to Elmar Haneke for finding it.

• Fixed a bug with accessing aliased columns by name in a ResultSet. Thanks to Tim Pizey for help finding it.

• JDBC driver now understands JDBC escape codes for date, time and timestamp SQL types.

  For example, SELECT {d '2001-5-2'}

• Added SQL-92 date/time/timestamp literals to grammar.

  For example, SELECT DATE '2001-5-2', TIMESTAMP '2001-1-12 8:45:00'

• Changes to the SQL DATEOB function to understand SQL style date format (yyyy-mm-dd).

• setMaxRows(int max) method implemented in Statement to limit the number of rows in a ResultSet.

Version 0.91 beta - September 6, 2001

• Remote query time out changed to throw more intuitive error message.

• Standard SQL-92 update and delete rules added to foreign key reference grammar.

• Database.getColumns now returns the correct scale constraint of numbers in the DECIMAL_DIGITS column.

• The Statement Multiple Result interface now will not confuse a single celled query for an update count in the getUpdateCount method. This was causing problems with some database browser applications.

• Grammar now understands DEFERRABLE INITIALLY DEFERRED for defining constraint deferrability.

• SQL function CONCAT now returns NULL for CONCAT('a', NULL).

• MDatabaseMetaData.getColumns fixed to handle the schema argument.

• Specifying a non-existant schema name on the JDBC URL will now throw an error.

• CREATE TABLE can no longer create a table in a schema that hasn't been created.

• Concurrent transactions can no longer create duplicate tables.

• MResultSetMetaData.getColumnLabel(column) now returns only the column name and not the schema/table part of the column reference. The behaviour of MResultSetMetaData.getColumnName(column) was not changed. getColumnName continues to return the fully qualified column reference.

• Changes to the system tables in the SYS_INFO to support SQL type grants, host verification and advanced user options. Future system tables also added. Hopefully this will be the final change needed to the system schema for a while.

• index_str column added to SYS_INFO.sUSRTableColumns system table which allows access to information about the column index.

• Added BETWEEN predicate and improved the indexing API to better handle range searches.

• Added boot_or_create variable to JDBC URL encoding which if set will boot the database if it exists or create and boot if the database doesn't exist.

  For example; jdbc:mckoi:local://mydb.conf?boot_or_create=true

• Altered some error messages to be more verbose.

• JDBCQueryTool class adapted to be easier to componentize.

• prepared_statement.setObject(n, new byte[] { ... }) will correctly cast to a Blob SQL type.

• Aggregate and function based tables are now cached the same way as regular table data sources.

• Added ANY and ALL sub-query capabilities as well as single cell comparison sub-query evaluation.

  For example, WHERE salery > ( SELECT avg(salery) FROM emp ). Note that IN is equivalent to = ANY and NOT IN is equivalent to <> ALL.

• Added sub-query capabilities in the FROM clause of the SELECT statement.

  For example, SELECT * FROM ( SELECT ... ) ....

• Due to modifications to SELECT, the name of columns in a ResultSet may change slightly from earlier releases. If a query uses a reference that is possibly ambiguous, the fully resolved name that was determined is used in the ResultSet.

  For example, the query SELECT name FROM APP.Customer will create a ResultSet with a single column named APP.Customer.name.

• The getObject method and other getxxx methods in ResultSet were modified so that they correctly convert the returned object to the appropriate Java type as required by the JDBC specification.

• The setObject mechanism from PreparedStatement was altered so that it converts Java object types to the correct internal database object.

  For example, using setObject(3, new Integer(400)) when the database expects an int (for example, WHERE ? = 400) will now work as expected. The only way to work around this issue in previous releases was to make sure all numeric values were wrapped around a BigDecimal.

• The SHUTDOWN command now results in a single celled ResultSet with a value of '0' (previously returned '1').

• Fixed memory leak which occured during the 'ConnectTest' application from the test suite.

  The problem was that a reference from Database to every DatabaseConnection was being kept even after the connection was closed thus causing a memory leak. The leak was made visible by 'ConnectTest' which opens, processes a transaction, then closes a connections 120 times.

• Fixed bug with query evaluator which failed to join tables with 0 entries thus causing an inefficient natural join to occur in certain situations.

• Due to a 'bug' or bad design of the Java DateFormat API implementation, instances of classes derived from SimpleDataFormat are not thread safe. To work around the problem all calls to static DateFormat objects were synchronized.

• Cleaned up the engine shutdown behaviour. Client/Server shutdown now works correctly under Linux.

Version 0.90 beta - May 23, 2001

• Closing an embedded database now correctly cleans static instances to allow for restarting of the database. StartStopTest added to test suite.

• Fixed problem with JDBC getXXXColumn on quoted variable names.

• MConnection changed so setTransactionIsolation method correct.

• First version of the conglomerate (database) repair tool released. The repair tool is com.mckoi.tools.DBConglomerateRepairTool.

  If a database becomes corrupt then back up the data and try using this Java application to fix the data.

• You can now add an optional initial schema name to the Mckoi JDBC URL.

• Fixed a bug with the conglomerate state file that caused information in the DefaultDatabase.sf file to slowly add up without being deleted when the database structure was changed.

• New Function: COUNT(DISTINCT [expression_list])

• The internal expression string was changed to represent the non-escaped version of the string literal so the expression string can be correctly re-parsed later if desired. This change will in some cases affect the label and name of columns returned by ResultSetMetaData.

• Grammar changed so '' (double apostrophe) is treated as a single apostrophe in string literals. For example, SELECT 'Toby''s Fix'.

• Fixed a caching bug in FunctionTable that caused errors in function tables in excess of 1024 columns or 2,000,000 rows.

• The CREATE, ALTER, and DROP SQL commands now return a result set containing '0' if the operation was successful. Previously they return values > 0 depending on the function.

• Fixed bug that caused a query to time-out earlier than it should have when a connection was saturated with new statement requests.

• New Function: IF([condition expression], [true expression], [false expression])

• An internally generated SYS_INFO.sUSRTableColumns table added which allows production of queries on the entire database schema.

• Several new system tables were added to the SYS_INFO schema including a number of important referential integrity and constraint tables.

  The referential integrity and constriant tables keep data on which columns are unique and primary key, any check constraints, and foreign key references.

• Schema information and other system tables have moved to the (now read-only) SYS_INFO schema.

• The engine now supports the ability to manage multiple schema.

  This change will allow for better separation of groups of tables and allow the ability to define referential constraints within a schema. The change required revision of the Transaction and DatabaseConnection API as well as some other classes.

• SET SCHEMA [schema name] command to switch between different schema.

• Improved performance of table creation and reduced memory utilization of small tables. Since tables are to become a useful tool to the engine itself this was necessary.

• Behaviour of 'auto-commit' mode changed slightly so if an error occurs during query evaluation then an auto roll-back occurs instead of an auto commit.

• CREATE TABLE syntax improved to handle primary keys, multiple foreign keys and unique constraints.

• JDBC getColumnxxx methods will now search on [column_name], [table_name.column_name] as well as the fully declared name [schema_name.table_name.column_name].

  For example, SELECT * FROM Table then result.getString("my_col") is equivalent to result.getString("Table.my_col") and result.getString("MySchema.Table.my_col").

• The mckoidb.jar manifest file now includes a Class-Path directive that will include any regular expression libraries found at the launch directory/URL.

• Fixed bug when using aggregate functions on an empty table (eg. SELECT COUNT(*) FROM Table WHERE false) that previously returned an empty result. Now it correctly evaluates the result of the aggregate function for an empty set.

• Added additional variables to SHOW STATUS to list some important Java runtime information the engine is being run on.

• Various minor rearrangement and alteration of the source tree to ease a port of the engine to the Java 1.1 environment.

Version 0.89 beta - February 25, 2001

• Fixed bug with com.mckoi.runtime.McKoiDBMain command line and the '-create' switch.

• Trigger support added back in to engine.

  Triggers are only fired when a transaction is committed. A connection will not see a trigger event unless it either doesn't have a transaction open or when the open transaction is closed (via commit or rollback).

• As part of object/relational enhancements, added the ability to construct new objects remotely in the database via new.

  For example, SELECT new java.awt.Point(x, y) FROM Coordinates or INSERT INTO Address (url) VALUES (new java.net.URL('http://www.mckoi.com/database')).

• HEXTOBINARY and BINARYTOHEX functions added.

  These are binary functions and convert between hex strings and binary (byte) data types.

• Added JAVA_OBJECT SQL type for storing serialized Java objects as an experimental feature and a start for adding object capabilities to the engine.

  An example; CREATE TABLE ObjTable ( col1 JAVA_OBJECT(java.awt.Point) ). Now Java objects can be stored in the table via INSERT INTO ObjTable ( col1 ) VALUES ( ? ) and by calling setObject(1, new Point(10, 10)) on the prepared statement.

• Out of bounds SQL error returned from the get... methods in ResultSet if the column index is in error.

• CREATE [UNIQUE] INDEX ... and DROP INDEX ... statements added to the grammar. Currently they are no-operation.

• INT SQL type is now an alias for INTEGER.

• Table names can now be quoted. eg. SELECT * FROM "DATE" WHERE "DATE.time" IS NOT NULL.

• Fixed string escape code error with INSERT INTO TEST(TEST1,TEST2) VALUES('Test\\','Test2').

• Mckoi JDBC URL now accepts the form 'jdbc:mckoi: ....' without the ':' prefix.

• com.mckoi.JDBCDriver now extends com.mckoi.database.jdbc.MDriver so that a developer may directly create an instance of the Mckoi JDBC driver class bypassing DriverManager. It appears that the JDBC specification implies that you need to do this.

• com.mckoi.database.jdbc.MDatabaseMetaData and MResultSetMetaData are now public to allow third party tools to introspect instances using the reflection API.

• JDBC API Additions.

  Completed all 'not implemented' methods in ResultSetMetaData and DatabaseMetaData. Some of the implementations are thin shells that should be improved upon in the future.

• Workaround for an issue with the UNIQUEKEY function that caused an auto- commit connection to keep the transaction alive. We need a better fix for this function. Possibly we should make it so it's not transaction dependent.

Version 0.88b beta - December 16, 2000

• Caching improvements on the JDBC client to shorten long term memory usage and to more quickly free server side resources.

• Code profiled and performance improved for reading tables with a large numbers of columns.

• FIX: Bug with transaction journal entry corruption under specific conditions.

• liveCopyTo(File path) added to TableDataConglomerate that can be used to make a copy of the data while the database is live.

  Written so we can make a service that backs up the database without shutting the system down. While the 'liveCopyTo' method is executing, all commit attempts will block. Limited database reads are still permitted, but in practice the client will see choppy performance while a backup is happening.

• Added a database file lock that prevents a database from being opened for write by multiple JVMs.

• Fixed bug with conglomerate clean up that would cause an error if the result set was open on a dropped table after the table was deleted from the file system.

Version 0.88 beta - December 10, 2000

• New SHOW - SHOW CONNECTION_INFO. Shows information about the connection the statement was evaluated in.

• New command COMPACT TABLE. Compacts the size of a database table in the file system. This command is transaction sensitive and must be committed before taking effect. See the documentation for more information.

• Added a 'switch to connection [connection name];' commands to JDBCScriptTool that will tell the tool to execute the next commands on a different connection.

  This change is for writing scripts to test concurrent transaction sequences.

• Added an optional INDEX_BLIST and INDEX_NONE flag when defining a column. This informs the engine whether the column is to be indexed or not.

• DataCellCache is now a prime number in size to improve hashing algorithm.

  Tests and a look at the heap dump shows this is an effective way of hashing the cache and reduces memory useage slightly.

• Fix for ResultSetMetaData.getColumnType(column) which previously returned a generically assigned type.

• SQL-92 TRIM function added.

  For example, SELECT TRIM(TRAILING 'a' FROM 'aaabgggaaa') will result in 'aaabggg'.

• Local users can now make multiple connections to a database running locally.

• Minor bug fixed with busy connection handshaking causing non-fatal exception.

• Added a SET command to the grammar for setting runtime variables on the current connection.

• Multi-threadedness inside a transaction now inherits the locking semantics that pre version 0.88 used for the entire database.

  That is, a write operation must aquire an exclusive lock over a table. A read operation aquires a shared lock. Altering, creating and dropping tables requires an exclusive lock over the entire transaction. NOTE: Concurrent transactions only lock over a COMMIT or ROLLBACK or the start of a transaction.

• Transaction isolation level SERIALIZABLE is now supported.

  COMMIT and ROLLBACK SQL commands are now handled by the interpreter. SET AUTOCOMMIT ON | OFF will switch auto commit mode. Transaction support required a major overhaul of the core file IO and primitive file operations.

• Various usability improvements to the com.mckoi.tools.JDBCQueryTool tool.

• REGEX operator now accepts a complete expression on the RHS. SELECT * FROM Files WHERE content REGEX ? is now valid.

Version 0.87 beta - November 12, 2000

• JDBC API additions.

  In DatabaseMetaData, getDatabaseProductName(), getDatabaseProductVersion(), getUserName(), isReadOnly() methods implemented.

• The SHOW statement now has an optional WHERE clause.

• Added SHOW CONNECTIONS. Shows a snapshot of current connections to the database.

• Fixed a bug in the lock debugging code that caused an error to be thrown under busy write situations. 'table_lock_check' configuration property now works as advertised.

• All string transfer is now as a char[] array due to limitations with UTF-8. It's too difficult to determine how large a UTF-8 format string will be and the UTF-8 protocol has a size limitation of 64k.

  If the JDBC client/server communication protocol uses too much bandwidth then we need to look into using a general compression algorithm for reducing this traffic.

• Completed DESCRIBE [table name] command.

  This command provides information about the columns of the table.

• SQL-92 style ALTER syntax has been added. See documentation for details.

• Added a regular expression literal used only for the REGEX operator. The new grammar will be familiar to Perl users.

  Some examples; SELECT 'abba' REGEX /ABb./i is a case insensitive search and returns true in this case. SELECT content FROM Files WHERE content REGEX /.*harry.*/i searches for the string 'harry' somewhere in the 'content' column of the Files table. SELECT content FROM Files WHERE content REGEX /.*harry.*/i = false finds all Files that do not have the string 'harry' in the content column.

• SQL grammar now accepts NULL literal.

  SELECT * FROM Table WHERE Column <> NULL is equivalent to SELECT * FROM Table WHERE Column IS NOT NULL. Another example, SELECT 9 / 0 = NULL returns true.

• Recovery mechanism after an unexpected shutdown has been changed so it now only reindexes a table at the next boot if there are any modifications that haven't been checked in.

  The old behaviour was that all tables were reindexed if the database was not properly shut down. Now, only tables that have unchecked modifications are rebuilt. This change improves boot up times for when shutdown wasn't called. The old behaviour is still available if the 'always_reindex_dirty_tables' configuration property is enabled (disabled by default).

• Added static quote(String) method to com.mckoi.database.jdbc.MckoiConnection class. This will convert a Java string to a quoted SQL string to be used as an expression in an SQL statement.

• Changed so any properties passed through the java.sql.DriverManager.getConnection(url, properties) method are treated as overridden properties in the configuration file for a local database.

• The :jdbc:mckoi:local:// URL encoding has been extended to allow any configuration property to be overridden inside the URL.

  For example, :jdbc:mckoi:local://conf.db?database_path=/home/db/datadir&lookup_comparison_list=disabled will boot a local database with the database path at '/home/db/datadir' and the 'lookup_comparison_list' property disabled. Note that a database can still be created by including 'create=true' in the JDBC URL variable encoding.

• Added a -C[key]=[value] switch to the Mckoi server application that allows any property in the configuration file to be overridden on the command line. See java -jar mckoidb.jar -h for command line help.

• The internal length SQL function now handles binary columns.

• 'lookup_comparison_list' configuration property bug and Blob bug fixed. Neither worked as advertised.

• Changes to table add/remove row behaviour and column index management.

  Now, 30 seconds after the last table modification, table relational and modification data are flushed to the file system. Column indices are no longer read into memory as part of the boot up procedure. Instead they are loaded as needed on the first request to the table. This means the engine boots faster and requires less memory at the start. First query times are lengthened however.

• Added memory optimization 'soft_index_storage' configuration property.

  This property will reduce the memory requirements of the engine by keeping all column indices behind a soft reference. This means the JVM Garbage Collector can reclaim memory used by indices. If an index is needed after it has been reclaimed, then it is reloaded from the file system. Enabling this property will lower performance and raise the amount of memory available on the heap to any other applications running within the same JVM. Ideal for a very low memory footprint local database.

• Added -dbpath, -logpath and -jdbcport switches to the Mckoi server command line program (com.mckoi.runtime.McKoiDBMain).

  These options will override variables set in the configuration file and set the database log and data file path. The 'jdbcport' switch allows for the TCP port to be set.

Version 0.86 beta - October 15, 2000

• Added trigger demonstration to the 'demo' directory.

• Fixed bug with INSERT INTO ATable ( my_column ) SELECT my_column FROM ATable that previously caused an infinite loop.

• Removed 'tools.jar' from the distribution because I felt it was un-necessary. All the development tools are in 'mckoidb.jar'.

• The GPL license is not compatible with the Apache Jakarta Regexp library so I have removed it from the distribution.

  The GNU LGPL regular expression library is shipped instead, including the source code. There is now an interface for bridging to any regular expression library. See the 'com.mckoi.database.RegexLibrary' interface. Note that a regular expression library is only needed if the REGEX operator is used in a search expression. For example, SELECT number FROM Part WHERE number REGEX '[a-z]+[0-9]*';. The database will happily operate without a regular expression library (provided it doesn't encounter the REGEX operator).

• New connections on the server will no longer make a call to 'InetAddress.getHostName()'. This method performs a reverse DNS lookup on the connecting IP address that can cause long delays if DNS has not been set up on the server.

• FIX: Dropped tables are now deleted from the filesystem.

• Triggers added. Triggers are an experimental feature. Please report any bugs you find with them.

  A trigger is added with a CREATE TRIGGER trigger_name [ UPDATE | INSERT | DELETE ] ON table_name SQL command. There is a demonstration of using triggers in the 'demo' directory.

• DataTable's now stay live after a Database.shutdown. Any server side references to an instance of a Table is no longer invalidated over a shutdown/init.

  The primary importance of this fix is that we can shutdown the database, perform some maintenance on the data (backing up, etc), re-initialize the engine and any Table references created are NOT invalidated. After the database is initialized after the shutdown, the system will perform as normal and will even be able to continue downloading result sets in progress from before the shutdown.

• Strings >64k in length are no longer transferred as UTF-8. UTF-8 has a 64k size limitation so we have to transfer large strings as a char array.

  Note that UTF-8 is a compression format for unicode strings. Serializing strings as a char array will (for the English ASCII character set) double bandwidth usage. Perhaps we need to implement a variant of UTF for large strings.

• ResultSet.wasNull() implemented properly. The primitive type getxxx methods in ResultSet now work as specified in JDBC for null values.

• BLOBs can now be stored and retrieved.

  A Blob has a theoretical size limitation of 2 gigabytes but in practice this limitation is much lower. A Blob must fit entirely in memory both on the client and on the server. So for all practical purposes, Blob's should be no larger than a few megabytes. Additionally, it is not possible to search against a binary column. This is on purpose - how would you cast binary data to a type that could be queried against?

Version 0.85 beta - September 23, 2000

• Fixed bug with SELECT Customer.*, Flight.number where the column selected after a glob would not appear in the result.

• Added com.mckoi.tools.JDBCScriptTool, a command line driven SQL scripting tool for debugging, diagnostics and regression testing.

• Added LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN and [INNER] JOIN joining operators in the FROM clause.

  The current implementation performs all outer joins first, then evaluates the where clause and performs any inner joins. Performance looks good from some tests on common uses. Perhaps we need to look into revising and updating the search expression evaluation system so outer joins can be optimized better with the rest of the clauses.

• Changed RawTableInformation and added a RootTable interface to allow for more flexibility when defining the lowest layer tables in a hierarchy.

• AVG and SUM aggregate functions now handle NULL input safely. If the group contains a NULL value, then the result is NULL. Changes were made to the other internal functions so they safely handle NULL input.

• SQLException can now be thrown in the middle of fetching a ResultSet block if the data is in error.

• Fixed bug that allowed ambiguous table declarations in the FROM clause of the SELECT statement.

• The configuration property 'function_factories' can now register any number of user definable function factories. This allows for custom written function libraries to be plugged into the SQL engine.

  See com.mckoi.database.FunctionFactory for the API to write custom SQL functions. For reference, all internal functions are defined in the com.mckoi.database.InternalFunctionFactory class.

• FIX: Escape codes \n, \t, \', \\ now work in string literals.

• FIX: NOT LIKE now fully functional.

• Added support for read only databases. Log files can now be disabled, and a new 'read_only' property was added to the database configuration file.

  Now database data files can be stored on read-only media such as a CD.

• Fixed a potential problem with User and Database.shutdown()/.init(). Also fixed a related but more obscure problem with JDBCDatabaseInterface.

  User held a reference to the priv tables, but all Table objects are currently invalidated when Database.shutdown() is issued. A User object can be live over a .shutdown(), therefore the User would end up undefined after a shutdown. A similar problem was found with JDBCDatabaseInterface if a table lock was held over a shut down/init. Now, if you execute a query and are reading a result set over when the database shuts down, the result set will throw an SQLException after the database initializes. This still isn't perfect behaviour, but it's better than before where the connection would lock up. Added a TODO entry for keeping DataTable objects live to solve this issue entirely.

• Added TONUMBER internal SQL function that casts an expression to a number.

  This comes in particularly useful for changing a date to the number of milliseconds since Jan 1st, 1970.

• Fixed IS NOT NULL error in expression grammar.

• Added 'socket_polling_frequency' property to configuration file.

  This changes the time in milliseconds between each poll of the 'available' method of input connections. This can be tweaked for different VM implementations.

• JDBC Driver now correctly returns a native Java 'null' for null results.

  Previously the driver returned an instance of global.NullObject.