HSQLDB example source code file (changelist_1_8_0.txt)

This example HSQLDB source code file (changelist_1_8_0.txt) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - HSQLDB tags/keywords

added, alter, create, fixed, fixed, select, set, set, sql, table, table, the, the, when

The HSQLDB changelist_1_8_0.txt source code

Chronological Change List

This file is the accumulated list of changes in the course of development of
hsqldb 1.8.x, including point releases, in reverse chronological order.

Fred Toussi (fredt@users.sourceforge.net)
Maintainer, HSQLDB Project

2010.06.10  1.8.1.3

Fixed occasional deadlock issue with SET WRITE DELAY
Fixed synchronization issue with mass creation of hundreds of mem; database 
Added sync() calls to closing all files
Added all improvements to GUI utilities from version 2.0

2010.02.27  1.8.1.2

Fixed regression in the optimisation added in 1.8.1.1

2009.09.02  1.8.1.1

Added support for SET FILES INCREMENT BACKUP { TRUE | FALSE } - the same as
  documented version 2.0 functionality
Fixed regression of get_column_names connection properties - now defaults to true
Fixed lock file issue - reverted to default lock file creation
Allowed mixed-case resource paths for res: databases
Fixed issue with text table source
Fixed support for text table source files in res: databases
Fixed issue with LIKE statements not working in CHECK constraints
Changed rounding of DECIMAL values in division to ROUND_DOWN for uniformity
with other integer types. Now 7/4 and 7/5 both return 1, while the first one was
previously rounded up to 2.
Added optimisation for query conditions in the form of
T1.A = T2.A AND T1.B = T2.B where there is an index on (A,B, ...).


2008.06.02  1.8.0.10

Removed use of the lock file by default.
Connection property hsqldb.lock_file=true should now be specified for its use.
Fixed deadlock in shutdown of Server
Fixed bug that in some circumstances could result in corrupted .data file and
cause Null Pointer or other exceptions.
New database property hsqldb.lock_file, true by default. When false, no lock
file is created when the database is opened.
Compile with JDK 6 is possible
New implementation of lock file
Fixed issue with org.hsqldb.Servlet
Fixed issue with scripting of views
Fixed issue with HsqlTimer
Minor improvement to WebServer
Minor corrections to utilities
Correction to build.xml to allow hsqldbmin target

2007.10.22  1.8.0.9

Internal changes to classloader for FileAccess classes for next OOo release.
new optional Java system property hsqldb.method_class_names allows preventing
access to classe other than org.hsqldb.Library and java.lang.Math or specifying
a semicolumn-separated list of allowed classes. For example,
java -Dhsqldb.method_class_names="org.me.MyClass;org.you.YourClass"
allows access to methods in the two specified classes.

2007.09.06  1.8.0.8.1

Fixed regression that impacted performance of system table selects and JDBC
DatabaseMetaData calls that use system tables.

2007.08.28  1.8.0.8

Fixed issue with some text table sources.
Fixed issue caused after adding columns to tables used in certain view definitions
Added sych call before closing .script files.
Minor bug fixes

New implementation of HsqlTimer to allow proper database shutdown while used
within an application server or similar.

Improvements in TEXT TABLE source file handling. If source file is unavailable at
startup, the source specification is retained but its usage is turned off.
An SQL warning is added to the first session in the event.
Added SET TABLE <tablename> SOURCE {ON | OFF} enables/disables the usage of the
source file.

Some relaxation of use of LIMIT in subqueries

Improvements to SqlTool and DatabaseManager tools by Blaine Simpson.

2006.09.27  1.8.0.7

Fixed regression in 1.8.0.6 that prevented reuse of empty spaces in the .data file.
Fixed regression that prevented setting the textdb.allow_full_path property

2006.09.22  1.8.0.6

Fixed issue where missing dbname.0 in server properties files was not ignored
Fixed issue where a crash during logging of an update query may result in
incomplete logging and recovery
Fixed issue where a PK defined after another constraint would result in wrong
constraint name
Fixed issue with incorrect evaluation of null values in CASE WHEN in certain cases
Fixed issue where ResultSet column class names were misreported for functions
returning primitive types

2006.07.27 1.8.0.5

(3 fixed issues affecting durability)
Fixed serious reported issue in some JVM's zip libraries by adding a workaround.
Fixed serious issue when cache_scale and cache_size_scale are small but very
large rows are inserted in cached tables.
Fixed bug where an automatic rollback of an UPDATE statement was not logged.

Fixed bug with nested JDBC escapes.
Fixed bug with logging of schema definition.
Fixed bug where DROP TABLE was allowed even when a view used the table.
Fixed bug where DISTINCT together with some GROUP BY lists were not allowed.
Fixed bug where Server.shutdown() threw spurious NPE.

Improved .data file persistence classes with better errror logging.

Improved client/server retrieval speed of large result sets.
Column declarations of TIME(0) are now accepted.
Named parameter in the form of :name are allowed in prepared statements.

2006.04.16 1.8.0.4

General improvements to all utilities, including SQLTool, DatabaseManager, etc.
General improvements to build.xml.
Improvement to fail-safe persistence by saving the .properties file first as a
new file, before renaming.
Fixed bugs with text tables that use quoted source files and have a heading row.
Fixed bug which prevented dropping a column before an indexed column.
Fixed bug where DISTINCT was not allowed with SQL functions (BT 1400344)
Fixed bug where PreparedStatement calls were allowed with some unbound variable
(Bug Tracker 1399324) but commented out to allow compatibility with earlier
versions (see org.hsqldb.jdbc.jdbcPreparedStatement).
VARCHAR_IGNORECASE size specification is now enforeced.
Fixed bugs where ORDER BY did not work in some cases (BT 1383218, BT 1344895).
Fixed bug where IFNULL was used with a function (BT 1315834).
Fixed bug where creation of a new table was not allowed if the old table with
the same name had been renamed.
Fixed bug where SYSTEM_INDEXINFO could not be read.
Fixed bug to allow running with JDK 1.1 when compiled for this target.
Fixed bug with COALESCE, et containing an aggregate function (BT 1242448).

Fixed bug with ResultSet.getTimestamp(int, Clandar).
Fixed bug with access non-admin access to tables was allowed when using
PreparedStatement objects.
Added org.hsqldb.Library function, getDatabaseFullProductVersion to return the
full version string, including the 4th digit (e.g 1.8.0.4).

2006.01.08

Fixed bug in WebServer
Added support for remote startup of databases in Server and WebServer

2005.12.22

Fixed bug where ON DELETE SET DEFAULT was accepted without a matching DEFAULT value
Fixed bug where column renaming was allowed in tables accessed in views
Fixed bug where TRUE or FALSE is used as search condition as WHERE .. AND FALSE
Fixed bug where dropping TEMPORARY tables was not logged
Fixed bug where negative values for IDENTITY columns were treated as positive


2005.11.02 1.8.0.3

Bug fixes

Fixed bug with tables formed with SELECT .. INTO that did not allow creation of indexes
Fixed bug with scripting of some VIEW definitions with quoted column names
Fixed bug in generation of some system tables with multi schema databases
Fixed bug with access to network result set data when UNION with different column
types is used
Fixed bug with scripting of some TIMESTAMP values

2005.08.18 1.8.0.2

Bug fixes and enhancements:

Fixed bugs with GLOBAL TEMPORARY tables with PRESERVE ROWS or
DELETE ROWS options.
Fixed bug with DESC ignored in ORDER BY when a correlation name was used to
specify the column.
Fixed ResultSet.getTimestamp() and PreparedStatement.setTimestam() bug with null
timestamp parameters.
Enhancement to allow string contatenation in LIKE predicates.
Fixed two JDK 1.1.x incompatibilities.
Fixed issue with some SET TABLE <name> SOURCE  command.
Fixed issue with DDL not being logged immediatedly with WRITE_DELAY set to 0.
Fixed issue with the combination of CASE WHEN (or COALESCE, etc.) with
aggregate functions when the table contains no rows and the aggregate function
returns null.
Fixed issue with ALTER SCHEMA <name> RENAME TO 

New features

Support for RIGHT OUTER JOIN and CROSS JOIN in select statements

ALTER TABLE <name> ALTER COLUMN  SET [NOT] NULL

Added DAY_OF_WEEK as an alternative for EXTRACT(XXX FROM <date time>)


2005.07.11 1.8.0.1

Bug fixes and enhancements:

Correlated subqueries with UNION (and other set operators) are allowed.
LEFT OUTER JOIN join statement can contain NOT expressions.
Set functions, SOME() and EVERY() implementation corrected.
Quoted empty strings in TEXT table sources are no-longer interpreted as null.
CHECKPOINT DEFRAG issued while there are uncommitted transactions works correctly.
build.xml updated to fix issue with JDK 1.3 build

2005.07.01 1.8.0.0

First release.

2005.06.16 1.8.0.RC12

Introduced new connection property shutdown, which, if true on the first
connection, will cause the engine to perform a SHUTDOWN on the database when
the last connection is closed.

Enhancements to TEXT table implementation to maintain the commit status of rows
during recovery from an abrupt shutdown. Uncommitted rows will not appear in
TEXT tables.

2005.05.17 1.8.0.RC10

SCHEMAS

Support for SQL schemas. Each database can contain multiple schemas. The
following commands have been introduced:

CREATE SCHMEA <schemaname> AUTHORIZATION DBA
DROP SCHEMA <schemaname> {CASCADE | RESTRICT}
ALTER SCHEMA <schemaname> RENAME TO 
SET SCHEMA <schemaname>

Initially, the default user schema will be created with the name PUBLIC. This
schema can be renamed or dropped. When the last user schema has been dropped,
an empty default schema with the name PUBLIC is created.

System tables all belong to INFORMATION_SCHEMA. To access system tables, either
SET SCHEMA INFORMATION_SCHEMA should be used once or they should be referred to
by fully specified names, e.g.
INFORMATION_SCHEMA.SYSTEM_TABLES

Similarly all database objects apart from columns can be referenced with fully
qualified schema names.

The CREATE SCHEMA command can be followed by other CREATE and GRANT commands
without an intervening semicolon. All such commands are executed in the context
of the newly created schema. A semicolon terminates an extended CREATE SCHEMA
command.

ROLES

Support for SQL standard roles.

CREATE ROLE <role name>
GRANT ... TO <role name>
REVOKE ... FROM <role name>
GRANT <role name> TO 
DROP ROLE <role name>

The GRANT and REVOKE commands are similar to those used for granting
permissions on different objects to USER objects. A role can then be granted to
or revoked from different users, simplifying permission management.

GLOBAL TEMPORARY TABLES

The implementation of temporary tables has changed to conform to SQL standards.

The definition of a GLOBAL TEMPORARY table persists with the database. When a
session (JDBC Connection) is started, an empty instance of the table is created.
Temporary tables can be created with (the default) ON COMMIT DELETE ROWS or
ON COMMIT PRESERVE ROWS added to the end of table definition.
With ON COMMIT PRESERVE ROWS, the table contents are not cleared when the
session commits. In both cases, the contents are cleared when the session is
closed.


SCHEMA MANIPULATION COMMANDS

Several schema manipulation commands have been enhanced.

Tables, views and sequences can be dropped with the CASCADE option. This
silently drops all tables and views that reference the given database object.

DROP TABLE <table name> [IF EXISTS] [CASCADE];
DROP VIEW <view name> [IF EXISTS] [CASCADE];
DROP SEQUENCE <sequence name> [IF EXISTS] [CASCADE];

ALTER TABLE <table name> DROP COLUMN now silently drops any primary key or
unique constraint declared on the column (excluding multi-column constraints).

ALTER TABLE <table name> ADD [COLUMN] now accepts primary key and identity
attributes.

COLUMN MANIPULATION

Support for converting type, nullability and identity attributes of a column

ALTER TABLE <table name> ALTER COLUMN  

<column definition> has the same syntax as normal column definition. The new
column definition replaces the old one, so it is possible to add/remove a
DEFAULT expression, a NOT NULL constraint, or an IDENTITY definition.
No change to the primary key is allowed with this command.

- The column must be already be a PK column to accept an IDENTITY definition.
- If the column is already an IDENTITY column and there is no IDENTITY
definition, the existing IDENTITY attribute is removed.
- The default expression will be that of the new definition, meaning an
existing default can be dropped by ommission, or a new default added.
- The NOT NULL attribute will be that of the new definition, similar to above.
- Depending on the conversion type, the table may have to be empty for the
command to work. It always works when the conversion is possible in general and
the individual existing values can all be converted.

A different syntax can be used to change the next value for an IDENTITY column:

ALTER TABLE <table name> ALTER [COLUMN]  RESTART WITH 


ALL and ANY expressions

Full support for ALL(SELECT ....) and ANY(SELECT ....) with comparison o
perators: =, >, <, <>, >=, <=
Example:

SELECT ... WHERE <value expression> >= ALL(SELECT ...)

LIMIT and OFFSET

New alternative syntax for LIMIT at the end of the query:

LIMIT L [OFFSET O]

It is now possible to use LIMIT combined with ORDER BY in subqueries and SELECT
statements in brackets that are terms of UNION or other set operations.

An ORDER BY or LIMIT clause applies to the complete result of the UNION and
other set operations or alternatively to one of its components depending on how
parentheses are used. In the first example the scope is the second SELECT,
while in the second query, the scope is the result of the UNION.

SELECT ... FROM ... UNION
(SELECT ... FROM ... ORDER BY .. LIMIT)

SELECT ... FROM ... UNION
SELECT ... FROM ... ORDER BY .. LIMIT

Support for ORDER BY, LIMIT and OFFSET in CREATE VIEW statements


COLLATIONS

Added support for collations. Each database can have its own collation. The SQL
command below sets the collation from the set of collations in the source
for org.hsqldb.Collation:

SET DATABASE COLLATION <double quoted collation name>

Once this command has been issued, the database can be opened in any JVM and
will retain its collation.

The property sql.compare_in_locale=true is no longer supported. If the line
exists in a .properties file, it will switch the database to the collation for
the current default.

ENHANCEMENTS

Support for the res: connection protocol (database files in a jar) has been
extended to allow CACHED tables.

Support for correct casting of TIME into TIMESTAMP, using CURRENT_DATE

Symmetrical handling of setTimestamp() and getTimestamp() with Calendar
parameters.

BUG FIX

Fixed bug where two indexes where switched, causing wrong results in some
queries in the following circumstances:

CREATE TABLE is executed.
ALTER TABLE ADD FORIEGN KEY is used to create an FK on a different table that
was already present when the first command was issued.
CREATE INDEX is used to add an index.
Data is added to the table.
Database is shutdown.
Database is restarted.
At this point the indexes are switched and queries that use either of the
indexes will not return the correct set of rows.

BUG FIXES

Fixed reported bug with NOT LIKE and null values

Fixed bug with OR conditions in OUTER JOIN

Fixed bug with duplicated closing of prepared statements

Fixed bug with scalar subqueries. If the result of a scalar subquery is empty
(no rows returned), the value is NULL.

Fixed various parsing anomalies where SQL commands were accepted when quoted,
double-quoted or prefixed with an identifier, or identifiers were accepted in
single quotes. Example of a command that is no-longer tolerated:

Malformed query: MY. "SELECT" ID FROM 'CUSTOMER' IF.WHERE ID=0;
Actual query: SELECT ID FROM CUSTOMER WHERE ID=0;

Fixed bug that prevented adding a primary key with no name to a table.

Fixed bug that resulted in an error when the type of a column was changed to
the same type as before. E.g.
ALTER TABLE <table name> ALTER COLUMN  INTEGER

BUG FIX

Fixed bug in ROLLBACK. If several updates were made to a row of a table that
has no primary key, then ROLLBACK was issued, there would remain multiple
versions of the row. E.g.

CREATE TABLE T (I INT);
INSERT INTO T VALUES (0);
SET AUTOCOMMIT FALSE;
UPDATE T SET I=1 WHERE I = 0;
UPDATE T SET I=2 WHERE I = 1;
UPDATE T SET I=3 WHERE I = 2;
UPDATE T SET I=4 WHERE I = 3;
ROLLBACK

After the rollback the table would contain more than one row.


2005.03.02 1.8.0.RC9

Fixed unreported bug in recovery when first column contains null.
Fixed unreported issue with possible PK and FK name duplication
Fixed reported bug in recovery when there is no primary key.
Fixed reported issue with deleting self referencing row (with foreign key).
Fixed reported issue with illegal user names
Fixed jdk 1.3 compilation issues
Applied submitted patch to Server

Applied submitted patch for setting the default table type when CREATE TABLE is
used. The connection property, hsqldb.default_table_type=cached will set the
default to CACHED tables, or the SET PROPERTY command can be used. Values,
"cached" and "memory" are allowed.

The database property sql.enforce_strict_size=true has now a wider effect
Previously CHAR /VARCHAR lengths could be checked and padding performed only
when inserting / updating rows. Added support for CHAR(n), VARCHAR(n),
NUMERIC(p,s) and DECIMAL(p,s) including SQL standard cast and convert semantics.
Explicit CAST(c AS VARCHAR(2)) will always truncate the string. Explicit
CAST(n AS NUMERIC(p,s)) will always perform the conversion or throw if n is out
of bounds. All other implicit and explicit conversions to CHAR(n) and VARCHAR(n)
are subject to SQL standard rules.

Bob has improved support for text tables. Newline support in quoted fields is
now complete. It is now possible to save and restore the first line header of a
CSV file when ignore_first=true is specified. When a text table is created with
a new source (CSV) file, and ignore_first=true has been specified the following
command can be used to set a user defined string as the first line:
SET TABLE <tablename> SOURCE HEADER .

When embedded in OOo, several defaults and properties are set automatically:

CREATE TABLE ... defaults to CREATE CACHED TABLE ...
hsqldb.cache_scale=13
hsqldb.cache_size_scale=8
hsqldb.log_size=10
SET WRITE DELAY 2
sql.enforce_strict_size=true
hsqldb.first_identity=1


2005.02.11 1.8.0.RC8

Fixed minor bugs

Added public shutdown() method to Server.

A new application log has been introduced as an optional feature. The
property/value pair "hsqldb.applog=1" can be used in the first connection string
to log some important messages. The default is "hsqldb.applog=0", meaning no
logging. A file with the ending ".app.log" is generated alongside the rest of
the database files for this purpose.

In the current version only the classes used for file persistence (different in
OpenOffice.org), plus any error encountered while processing the .log file after
an abnormal end is logged.

Bob Preston has updated the text table support to accept new line characters
inside quoted strings. It is still not possible to create / modify a row
containing a new line inside the engine. This will be a simple upgrade and will
be included in the next RC.

Note that the JDBC driver and the engine for 1.8.0 cannot be mixed with those of
earlier versions in client/server setup. Check your classpaths and use the same
version of the engine for both client and server.


2005.01.28 1.8.0.RC7

Fixed minor bugs

2005.01.24 1.8.0.RC5

Issues with SET CHECKPOINT DEFRAG fixed

New property for larger data file limits is introduced. Once set, the limit will
go up to 8GB. The property can be set with the following SQL command only when
the database has no tables (new database).

SET PROPERTY "hsqldb.cache_file_scale" 8

To apply the change to an existing database, SHUTDOWN SCRIPT should be performed
first, then the property=value line below should be added to the .properties
file before reopening the database:

hsqldb.cache_file_scale=8

It is now possible to add or drop a primary key.

An existing primary key that is to be removed should not be referenced in a
FOREIGN KEY constraint. If a table has an IDENTITY column, removing a primary
key will remove the identity attribute of the column but leave the actual data.

When adding a primary key, a NOT NULL constraint is automatically added to the
column definitions. The table data for the columns of a newly declared primary
key should not contain null values.

ALTER TABLE <name> ADD CONSTRAINT  PRIMARY KEY(collist);
ALTER TABLE <name> DROP CONSTRAINT ;
ALTER TABLE <name> DROP PRIMARY KEY; // alternative syntax

2005.01.20 1.8.0.RC4

More minor bug fixes
ResultSetMetaData reports identical precision/scale in embedded and
client/server modes

2005.01.16 1.8.0.RC3

Regression bug fixes.
New property allows a CHECKPOINT DEFRAG to be performed automatically whenever
CHECKPOINT is performed internally or via a user command.

SET CHECKPOINT DEFRAG n

The parameter n is the megabytes of abandoned space in the .data file. When
a CHECKPOINT is performed either as a result of the .log file reaching the
limit set by "SET LOGSIZE m", or by the user issuing a CHECKPOINT command, the
amount of space abandoned during the session is checked and if it is larger
than n, a CHECKPOINT DEFRAG is performed instead of a checkpoint.

2005.01.14 1.8.0.RC2

Regression bug fixes.


2005.01.10 1.8.0.RC1

Allows embedding into OpenOffice.org
Rewrite of log and cache handling classes, including:
New deleted block manager with more effective deleted block reuse.
Faster log processing after an abnormal termination.
Better checks when maximum data file size is reached.
Better recovery when maximum data file size is reached.

Changes to row access to use iterators. More code clarity.

Various refactorings and new package for improved modularity.

Enhancements to DatabaseManagerSwing

In all other respects, 1.8.0.RC1 is identical to 1.7.3.1