Chapter 2. Hsqldb Test Utility

The org.hsqldb.test package contains a number of tests for various functions of the database engine. Among these, the TestUtil class performs the tests that are based on scripts. To run the tests, you should compile the hsqldbtest.jar target with Ant and JUnit.

The TestUtil class should be run in the /testrun/hsqldb directory of the distributed files. It then runs the set of TestSelf*.txt files in the directory. To start the application in Windows, change to the directory and type:

 java org.hsqldb.test.TestUtil

All files in the working directory with names matching TestSelf*.txt are processed in alphabetical order.

You can add your own scripts to test different series of SQL queries. The format of the TestSelf*.txt file is simple text, with some indentation and prefixes in the form of Java-style comments. The prefixes indicate what the expected result should be.

The class org.hsqldb.test.TestScriptRunner is a more general program which you can use to test any script files which you specify (with scripts of the same exact format as described below). For example,

java org.hsqldb.test.TestScriptRunner --urlid=mem script1.tsql script2.sql

You must have the HSQLDB classes, including the util and test classes, in your CLASSPATH. The urlid must be set up in an RC file as explained in the RC File Authentication Setup section. Use the rcfile= argument to specify an RC file other than the default of testscriptrunner.rc in the current directory. To see all invocation possibilities, just run TestScriptRunner with no arguments at all. TestScriptRunner can run tests sequentially (the default) or in simultaneous asynchronous threads.

Comment lines must start with -- and are ignored
Lines starting with spaces are the continuation of the previous line (for long SQL statements)
SQL statements with no prefix are simply executed.
The remaining items in this list exemplify use of the available command line-prefixes.
The /*s*/ option stands for silent. It is used for executing queries regardless of results. Used for preparation of tests, not for actual tests.
```
/*s*/ Any SQL statement - errors are ignored
```
The /*c<rows>*/ option is for SELECT queries and asserts the number of rows in the result matches the given count.
```
/*c<rows>*/ SQL statement returning count of <rows>
```
The /*u*/ option is for queries that return an update count, such as DELETE and UPDATE. It asserts the update count matches.
```
/*u<count>*/ SQL statement returning an update count equal to <count>
```
The /*e*/ option asserts that the given query results is an error. It is mainly used for testing the error detection capabilities of the engine. The SQL State of the expected error can be defined, for example /*e42578*/, to verify the returned error. This option can be used with syntactically valid queries to assert a certain state in the database. For example a CREATE TABLE can be used to assert the table of the same name already exists.
```
/*e*/ SQL statement that should produce an error when executing
```
The /*r....*/ option asserts the SELECT query returns a single row containing the given set of field values.
```
/*r<string1>,<string2>*/ SQL statement returning a single row ResultSet equal to the specified value
```
The extended /*r...*/ option asserts the SELECT query returns the given rows containing the given set of field values.
```
/*r
    <string1>,<string2>
    <string1>,<string2>
    <string1>,<string2>
*/ SQL statement returning a multiple row ResultSet equal to the specified values
```
(note that the result set lines are indented).
The /*d*/ directive just displays the supplied text.
```
/*d*/ Some message
```
The /*w MILLIS*/ directive causes the test to Wait for a specified number of milliseconds.
```
/*w 1000*/ Optional message
```
The /*w ENFORCE_SEQUENCE WAITER_NAME*/ directive causes the test to Wait for the specified Waiter. A waiter is just name that is used to associate a /*w*/ directive to its corresponding /*p*/ directive. The ENFORCE_SEQUENCE argument must be set to true or false to specify whether to fail unless the /*p*/ command runs after the /*w*/ command is waiting.
```
/*w true script4*/ Optional message
```
The /*p ENFORCE_SEQUENCE WAITER_NAME*/ directive is the peer directive to /*w*/, which causes a waiting thread to Proceed.
```
/*p true script4*/ Optional message
```
All the options are lowercase letters. During development, an uppercase can be used for a given test to exclude a test from the test run. The utility will just report the test blocks that have been excluded without running them. Once the code has been developed, the option can be turned into lowercase to perform the actual test.

See the TestSelf*.txt files in the /testrun/hsqldb/ directory for actual examples.

The String ${timestamp} may be used in script messages (like in /*d*/, /*w*/, /*p*/). It expands to the current time, down to the second. For example,

/*d*/ It is now ${timestamp}

	Chapter 2. Hsqldb Test Utility
Chapter 1. SqlTool		Chapter 3. Database Manager