Printer Friendly Format

This is the same document as this one, but comes as one large HTML file, which is suitable for printing and reading where you may not have access to a computer.

You can use the File::Print command from the browser menu to send the document to the printer.

Print User Guide.

Downloadable Format

This is a tar.gz file which can be downloaded to your personal computer, so you dont have to connect to the network every time you want to read the SQLUnit documentation. You will have to make a subdirectory and gunzip and untar inside that subdirectory.

Here is the sequence of instructions (for Unix/Linux users) to build the documentation locally.

$ mkdir sqlunit-docs
$ mv /download/dir/htdoc.tar.gz sqlunit-docs
$ cd sqlunit-docs
$ tar xvzf htdoc.tar.gz

You can access this from the browser using a file URL from your browser, like so:

file:///path/to/sqlunit-docs/index.html

Make sure to check back from time to time, though, since there may have been bug fixes and feature enhancements in future releases that you could probably take advantage of.

Download User Guide.

Downloading the source

The latest source code can be downloaded from sourceforge via anonymous cvs. The CVSROOT environment will need to be set as follows (for Linux using bash):

$ export CVSROOT=:pserver:anonymous@cvs.sqlunit.sourceforge.net:/cvsroot/sqlunit
$ cvs -z3 checkout sqlunit

Alternatively, you can download the latest released tarball (which also contains the source, but may not be the latest) from the download section. If you are going to be doing any development work for SQLUnit, however, I would recommend getting the source from CVS.

Note that tarballs prior to version 1.4 cotained only the SQLUnit JAR file due to a packaging bug, and are hence binary only. Additionally, you would have to go to CVS to pick up the DTD file. Thanks to Arjen Stolk for pointing this out.

Required JAR files

JAR files for open source databases, such as MySQL and PostgreSQL, which used to be supplied with the distribution, have been removed. This is because SQLUnit has shifted to using the MockRunner project to build its own mock database to run its tests instead of running them against popular databases. Users of databases already have the corresponding JDBC drivers installed, so providing them did not make much sense anyway.

Running the install script

If you want to install SQLUnit from source, you can run the following commands.

$ ant

Buildfile: build.xml

init:

help:
     [echo] SQLUnit version 4.9
     [echo] Targets:
     [echo]   help - display this message
     [echo]   clean - removes all files from the build directory
     [echo]   distclean - removes all files from the dist directory
     [echo]   compile - compile all the java files
     [echo]   compile-test - compiles java files needed for testing
     [echo]   test - runs the mock database tests
     [echo]   junit-test -Dtest.class=classname - runs JUnit test
     [echo]   reformat - formats sources in contrib with Jalopy
     [echo]   checkstyle - check code style using CheckStyle
     [echo]   install - creates the jar files in the dist directory
     [echo]   sqlunit-flat -Dtestfile=file.xml - runs SQLUnit
     [echo]   sqlunit-nested -Dtestdir=dir - runs SQLUnit w/FileSet
     [echo]   javadoc - creates the project javadocs
     [echo]   doclet - generates Docbook XML for sqlunit tags
     [echo]   htdoc - creates tarball for website
     [echo]   package - creates tarball for download
     [echo]   gui - start up the SQLUnit GUI Tool
     [echo]   tui - runs the SQLUnit TUI Tool for testing

BUILD SUCCESSFUL


      

So to create a SQLUnit JAR file from scratch, run the following ant directive to create the sqlunit.jar file in your dist directory.

$ ant install

Buildfile: build.xml

setup:

clean:
   [delete] Deleting directory /home/spal/sqlunit/build
    [mkdir] Created dir: /home/spal/sqlunit/build

compile:
    [javac] Compiling 18 source files to /home/spal/sqlunit/build
     [copy] Copying 1 file to /home/spal/sqlunit/build/net/sourceforge/sqlunit

distclean:
   [delete] Deleting directory /home/spal/sqlunit/dist
    [mkdir] Created dir: /home/spal/sqlunit/dist

install:
      [jar] Building jar: /home/spal/sqlunit/dist/sqlunit-1.0.jar
     [echo] File sqlunit-1.0.jar in dist

BUILD SUCCESSFUL

Total time: 3 seconds

      

SQLUnit Command Line

SQLUnit is really a single JUnit test which reads the SQLUnit test XML file to determine what it should do. Starting with version 1.8, the SQLUnit Ant task comes with new configuration options.

Since the sqlunit task is an optional task, the task must be first declared to Ant using a taskdef, like this.

<!-- This is specified once per buildfile -->
<target name="def">
  <taskdef name="sqlunit" 
       classname="net.sourceforge.sqlunit.ant.SqlunitTask">
    <classpath>
      <pathelement location="/your/location/for/sqlunit.jar" />
    </classpath>
  </taskdef>
</target>

In order to run a single test file called test/mysql/test.xml from within Ant using the sqlunit task, the XML snippet in the build file would look like this.

<!-- This is repeated for each test or 
     group of tests in case of nested filesets -->
<target name="run-postgres-test" depends="def">
  <sqlunit testfile="test/postgres/test.xml" 
      haltOnFailure="false" debug="false" />
</target>

Notice the new attributes haltOnFailure and debug. Both of them are optional attributes and SQLUnit will revert back to its default behavior if they are not specified. We will discuss them in more detail below.

In cases where you would like to run a group of SQLUnit test files, you can use nested filesets instead of the testfile attribute. So if your build base directory was the same as your stored procedure repository root, say /opt/database, under which you have organized your stored procedures into different subdirectories based on function, something like this:

/opt/database
 |-- stored_procedures
 |    |
 |    |-- accounting
 |    |-- human_resources
 |    |-- infotech
 |    |-- ...
      

Further, let us assume that you store your SQLUnit test XML files for each functional silo along with the stored procedures, so your accounting SQLUnit XML test file is /opt/database/accounting/accounting_tests.xml. To specify that you wish to run all the .xml under the stored procedures directory, you would specify the sqlunit target like this:

<target name="run-all-test" depends="compile,def">
  <sqlunit haltOnFailure="false" debug="false">
    <fileset dir="stored_procedures">
      <include name="**/*.xml" />
    </fileset>
  </sqlunit>
</target>

You can specify multiple nested fileset elements if needed. Just remember that the testfile attribute specification and the nested fileset specifications are mutually exclusive. SQLUnit will give you an error if you attempt to specify both.

The haltOnFailure and the debug attributes can be used to control the behavior of SQLUnit. This is described below.

If the logfile attribute is not set, SQLUnit will log its output to STDOUT and its exceptions, if any, to STDERR. This behavior has been introduced since version 2.2. Scripts running SQLUnit versions prior to this and depending on the older behavior (all logging to STDERR) will need to change the scripts to add logging to STDOUT as well).

SQLUnit will report all its status messages, including error messages to STDOUT. If there were one or more failures at the end of the build, then it will send a BuildException to Ant which will report a BUILD FAILED message to the user.

Starting with version 2.2, SQLUnit can be used with Ant's own BuildLoggers. The code has been tested with the org.apache.tools.ant.DefaultLogger and org.apache.tools.ant.XmlLogger loggers. To use a specific buildlogger, you can either use Ant's record tag or supply the log file name and logger class name in the ant command line, like so:

ant -logfile ${logfile_name} -logger ${logger_class_name} ${target}

Do not specify a logfile attribute in the sqlunit task when specifying the logfile and logger on the command line. This can lead to unexpected behavior.

Alternatively, you can use either the depends attribute of target or the antcall task to run more than one suite of tests with a single call. However, using the nested fileset elements in the sqlunit task seems to be the more obvious way to do it.

The options described for running SQLUnit from the command line using java or using the junit task under Ant in the documentation for versions prior to version 1.8 are deprecated and no longer supported. These approaches were cumbersome and not widely used.

Interpreting the output

A clean SQLUnit run will look something like the example below. This is the output from the test suite. Notice that the ant target sqlunit uses the standard JUnit output formatter but only writes its log to STDOUT, so its output is all grouped neatly in one place.

$  ant sqlunit -Dtestfile=test/postgresql/test.xml

Buildfile: build.xml

init:

test-ant-task:
  [sqlunit] Getting connection...
  [sqlunit] Setting up test...
  [sqlunit] Running test[1]: Adding department HR
  [sqlunit] Running test[2]: Adding department InfoTech using non-Callable form
  [sqlunit] Running test[3]: Adding Employee John Doe to InfoTech
  [sqlunit] Running test[4]: Adding John Doe again
  [sqlunit] Running test[5]: Adding Jane Doe to HR
  [sqlunit] Running test[6]: Adding Dick Tracy to InfoTech
  [sqlunit] Running test[7]: Updating Hourly Rate for John
  [sqlunit] Running test[8]: Looking up John Doe by name
  [sqlunit] Running test[9]: Looking up all employees in InfoTech
  [sqlunit] Running test[10]: Adding timecard for John
  [sqlunit] Running test[11]: Adding another timecard for John
  [sqlunit] Running test[12]: Adding timecard for Dick
  [sqlunit] Running test[13]: Getting monthly report for InfoTech
  [sqlunit] Tearing down test...
  [sqlunit] .
  [sqlunit] Time: 3.795
  [sqlunit]
  [sqlunit] OK (1 tests)
  [sqlunit]

BUILD SUCCESSFUL

Total time: 4 seconds

By default, SQLUnit will not consider a test failure as sufficient reason to stop the test suite from completing, since a test suite can contain unrelated tests that may not depend on each other. If you need SQLUnit to stop processing the suite after it encounters a test failure, set the haltOnFailure attribute to true. Here is an example of SQLUnit generating an error, with inline pointers to comments.

Buildfile: build.xml

init:

test-ant-task:
  [sqlunit] Getting connection...
  [sqlunit] Setting up test...
  [sqlunit] Running test[1]: Adding department HR
  [sqlunit] Running test[2]: Adding department InfoTech using non-Callable form
  [sqlunit] Running test[3]: Adding Employee John Doe to InfoTech
  [sqlunit] Running test[4]: Adding John Doe again
  [sqlunit] Running test[5]: Adding Jane Doe to HR
  [sqlunit] Running test[6]: Adding Dick Tracy to InfoTech
  [sqlunit] Running test[7]: Updating Hourly Rate for John
  [sqlunit] Running test[8]: Looking up John Doe by name
  [sqlunit] Running test[9]: Looking up all employees in InfoTech
  [sqlunit] Running test[10]: Adding timecard for John
  [sqlunit] Running test[11]: Adding another timecard for John
  [sqlunit] Running test[12]: Adding timecard for Dick
  [sqlunit] Running test[13]: Getting monthly report for InfoTech
  [sqlunit] No match on variable at [rset,row,col]=([1,2,4] (1)
  [sqlunit] *** expected: (2)
  [sqlunit] <result>
  [sqlunit]   <resultset id="1">
  [sqlunit]     <row id="1">
  [sqlunit]       <col id="1" type="VARCHAR">Information Technology</col>
  [sqlunit]       <col id="2" type="VARCHAR">Dick Tracy</col>
  [sqlunit]       <col id="3" type="INTEGER">13</col>
  [sqlunit]       <col id="4" type="NUMERIC">50.00</col>
  [sqlunit]       <col id="5" type="NUMERIC">650.00</col>
  [sqlunit]     </row>
  [sqlunit]     <row id="2">
  [sqlunit]       <col id="1" type="VARCHAR">Information Technology</col>
  [sqlunit]       <col id="2" type="VARCHAR">John Doe</col>
  [sqlunit]       <col id="3" type="INTEGER">16</col>
  [sqlunit]       <col id="4" type="NUMERIC">56.00</col (3)
  [sqlunit]       <col id="5" type="NUMERIC">880.00</col>
  [sqlunit]     </row>
  [sqlunit]   </resultset>
  [sqlunit] </result>
  [sqlunit] *** but got: (4)
  [sqlunit] <result>
  [sqlunit]   <resultset id="1">
  [sqlunit]     <row id="1">
  [sqlunit]       <col id="1" type="VARCHAR">Information Technology</col>
  [sqlunit]       <col id="2" type="VARCHAR">Dick Tracy</col>
  [sqlunit]       <col id="3" type="INTEGER">13</col>
  [sqlunit]       <col id="4" type="NUMERIC">50.00</col>
  [sqlunit]       <col id="5" type="NUMERIC">650.00</col>
  [sqlunit]     </row>
  [sqlunit]     <row id="2">
  [sqlunit]       <col id="1" type="VARCHAR">Information Technology</col>
  [sqlunit]       <col id="2" type="VARCHAR">John Doe</col>
  [sqlunit]       <col id="3" type="INTEGER">16</col>
  [sqlunit]       <col id="4" type="NUMERIC">55.00</col> (5)
  [sqlunit]       <col id="5" type="NUMERIC">880.00</col>
  [sqlunit]     </row>
  [sqlunit]   </resultset>
  [sqlunit] </result>
  [sqlunit] Tearing down test...
  [sqlunit] .
  [sqlunit] Time: 1.204
  [sqlunit]
  [sqlunit] OK (1 tests)
  [sqlunit]

BUILD SUCCESSFUL

Total time: 2 seconds

SQLUnit GUI Tool

This tool was originally contributed by Mohan Iyer. Arun Viswanath has done some major rework to add many more features to this tool.

The GUI Tool is a Swing based application for generating test cases by running the stored procedure against the database. The tool will append to the capture file defined in the properties file for the application. This is a very basic tool, it does not have any elegant error handling, but it works. The original version of the tool was contributed by Mohan Iyer, who used it to generate SQLUnit test cases against an Oracle database. The tool has been changed since to be more database agnostic.

To start the tool from the command line using, you can use the ant gui command defined in the supplied build.xml file. Alternatively you can set up a batch file or shell script to include the SQLUnit and JDOM JAR files in your classpath and invoke the following command:

$ ant gui [-Dgui.rcfile=etc/guirc.properties]

Here are some screenshots of the SQLUnit GUI Tool.

A sample guiconfig.properties is provided in the etc directory in the distribution. Please modify it to suit your own installation.

Arun Viswanath has made many improvements to the GUI Tool, since Mohan Iyer's original contribution, and my subsequent effort to make the tool database agnostic. If you have suggestions for improvement for the GUI Tool, please let him know by posting to the forum.

Mohan Iyer envisaged that this basic Swing application could be leveraged to provide various editor plug-ins for SQLUnit. If anyone is interested in doing this, please feel free to go ahead and send me the plug-in code and I will be happy to put it in CVS and give you credit. Since I dont use IDEs myself, I will just take your word for it that the plug-in works.

SQLUnit TUI Tool

This tool was originally contributed by Sahib S Wadhwa for use with Oracle databases and subsequently modified by Sujit Pal to make it more database agnostic.

The TUI tool is a console based tool for manufacturing test cases by running the stored procedure against the database. The tool will generate the XML for the test case, which can then be cut and pasted into the test specifications. The tool eliminates the drudgery of having to write the XML test cases by hand, speeding up the test building process enormously. It also has a new interactive interface similar to text based database client tools. The inputs can be either partially or fully driven by the contents of a Java properties file, thereby making it partially or completely non-interactive if desired.

Here is a sample run of the TUITool. Make sure that the JDBC Driver for the database you are using, the JDOM JAR file and the SQLUnit JAR file is in your CLASSPATH. Alternatively, set the CLASSPATH on the java command line with -cp.

$ ant tui [-Dtui.mode=offline] [-Dtui.rcfile=etc/tuirc.properties]

SQLUnit TUI Tool
Copyright(c) 2003 The SQLUnit Team
TUITool:_captureFile> /home/sujit/sqlunit/tui.out
TUITool:_connection!driver> com.mysql.jdbc.Driver
TUITool:_connection!url> jdbc:mysql://localhost:3306/sqlunitdb
TUITool:_connection!user> defaultuser
TUITool:_connection!password> defaultuser
TUITool:__test!name> Checking returned value from customer
TUITool:__test!call!stmt> select custId from customer where custId=?
TUITool:___test!call!param[1]!type> INTEGER
TUITool:___test!call!param[1]!inout> in
TUITool:___test!call!param[1]!is-null> false
TUITool:___test!call!param[1]!value> 1
<test name="Checking returned value from customer">
  <sql>
    <stmt>select custId from customer where custId=?</stmt>
    <param id="1" type="INTEGER" inout="in" is-null="false">1</param>
  </sql>
  <result>
    <resultset id="1">
      <row id="1">
        <col id="1" type="INTEGER">1</col>
      </row>
    </resultset>
  </result>
</test>

Output captured to /home/sujit/sqlunit/tui.out
TUITool:__test!name> q
$ 

The -Dmode=offline indicates that all replies to the prompts are stored in the supplied properties file indicated by -Drcfile. Offline mode can be used to run only a single test and is totally non-interactive. When the interactive mode is desired, some things such as the connection information, can be stored in the properties file to minimize typing effort.

The properties file is a standard Java properties file. A sample properties file is included in the distribution in etc/tuirc.properties. This will need to be modified and copied to whatever you want to call your rcfile for the SQLUnitTUI Tool. The keys mimic the prompts that the tool provides.

SQLUnit XSL Transform Tool

If your SQL or stored procedures that you want to test are already available in an XML format, if you were using a ORM framework such as iBATIS SQLMaps, for instance, then you can use this tool to transform the information into a form suitable for SQLUnit to consume. The Transform Tool is a simple XSLT parser. You will need to supply the appropriate XSL file containing the required transformation.

The W3Schools page on XSLT is quite comprehensive and is useful as a reference or tutorial if you need pointers on how to get started using XSLT.

A sample run of using the TransformTool from within ant is shown below:

$ ant transform -Dtransform.input=inputfile.xml \
    -Dtransform.transform=xsltfile.xsl \
    -Dtransform.output=sqlunitfile.xml

Buildfile: build.xml
 
init:
  
precompile:
   
compile:
    [javac] Compiling 10 source files to /home/sujit/src/sqlunit/build
    [javac] Compiling 1 source file to /home/sujit/src/sqlunit/build
            
transform:
    [java] SQLUnit Transform Tool
    [java] Copyright(c) 2005, The SQLUnit Team

BUILD SUCCESSFUL
Total time: 5 seconds

The various files that drove the development of this tool can be found here:

• Input XML file

• XSL Transform file

SQLUnit TestDocsGenerator Tool

This tool was contributed by James Henderson.

It generates a listing of all available tests suites by traversing the supplied directory recursively, and a listing of all tests within each test suite. It is useful to determine if a test has been created to address specific criteria when testing your SQL code (or if a new test must be developed).

The tool assumes that your tests are organized hierarchically with each directory representing a test suite, and SQLUnit test file within each directory representing a test case. Each SQLUnit test file can have multiple tests.

This tool, when supplied with a directory, will recurse and process each SQLUnit XML file to extract the test name/description for each test within the test case. This process is performed for all SQLUnit XML files that are located by the recursion.

To run this tool, you can use the following ant command:

  $ ant test-doc [-Doutput.dir=/path/to/output/directory] [-Dtest.dir=/path/to/test/directory]
  
  
Buildfile: build.xml
 
init:
 
precompile:
 
compile:
 
test-doc:
   [delete] Deleting directory /home/sujit/src/sqlunit/output
    [mkdir] Created dir: /home/sujit/src/sqlunit/output
     [java] Processing /home/sujit/src/sqlunit/test/sybase/MyTestProcTest.xml:MyTestProcTest
     [java] Processing /home/sujit/src/sqlunit/test/mssqlserver/TestSQLUnit_SQLServer2KTypes.xml:TestSQLUnit_SQLServer2KTypes
     ...
     [java] Processing /home/sujit/src/sqlunit/test/mock/variabletests.xml:variabletests
     [java] Number of test cases processed:39
     [echo] XHTML TOC file(s) in output/Test*.html
  

The -Doutput.dir argument is optional, if not supplied, it will default to using the directory output/ under the current directory. The tool will generate three XHTML files that form the directory of all tests within the processed test suite.

The -Dtest.dir argument is also optional, if not supplied, it will process all XML files (with the .xml suffix) under the test/ subdirectory.

• TestDirectory.html - a list of all processed test cases in alphabetical order and a sequential list of all tests within a test case. Each test case also has a link to its SQLUnit XML source file.

• TestCaseIndex.html - an alphabetical index of all processed test cases. This file provides a link to the corresponding test case with its test listing in TestDirectory.html

• TestCaseFrameset.html - creates a frameset document to house the previous two documents.

Most people will load TestCaseFrameset.html within a browser to get a complete view of all processed test cases and the tests within each test case.

This tool depends on certain naming conventions in the SQLUnit test suite as outlined below.

TestCaseName.xml
    TestCaseName[_1]: Description
    TestCaseName[_2]: Description
    ...
    TestCaseName[_3]: Description
      

If numbered test cases exist, the test listed in the output will be numbered. The test case name will be extracted from the first numbered test. The description will always follow the colon (:).

As an example:

MyTestCase.xml
    MyTestCase_1: A simple test
    MyTestCase_2: Another simple test
      

will have a test case name of MyTestCase (which matches the file name). Each test will be numbered and each description will be listing withi each test. The TestDirectory.html file when viewed through a browser will look something like this:

Tests within Test Case MyTestCase
1. A simple test
2. Another simple test
      

If the naming convention is not used, the entire name attribute of the first test is used as the name of the test case. Numbering of tests is not required, but useful. A test description which follows the colon is required.

Also, you cannot use the resource form of the DTD SYSTEM declaration in your SQLUnit test files, you must use either the relative or absolute form instead.

Some of these conventions may be relaxed or removed in the future with changes to the sqlunit.dtd to accomodate the requirements of this tool. Please contact James Henderson on the SQLUnit "Open Discussion" forum to discuss and suggest ideas/features or to report bugs about this tool.

SQLUnit Canoo2HTML Tool

This tool was contributed by James Henderson.

This tool converts the XML file generated by running SQLUnit with the Canoo reporter, into an XHTML document that can be displayed on a browser. This tool can be combined with the TestDocsGenerator to produce automated SQLUnit test reports, possibly with a continuous integration tool such as CruiseControl.

To run this tool, you can run this ant target:

  $ ant -Dlog.format=canoo \
  -Doutput.file=output/mytests.xml -Dtestfile=test/mock/coretests.xml \
  sqlunit-flat canoo2html
  
Buildfile: build.xml
 
init:
 
precompile:
 
compile:
    [javac] Compiling 10 source files to /home/sujit/src/sqlunit/build
 
compile-test:
    [javac] Compiling 31 source files to /home/sujit/src/sqlunit/build
 
def:
 
test:
 
init:
 
precompile:
 
compile:
    [javac] Compiling 10 source files to /home/sujit/src/sqlunit/build
 
canoo2html:
     [xslt] Processing /home/sujit/src/sqlunit/output/mytests.xml to /home/sujit/src/sqlunit/output/mytests.xml.html
     [xslt] Loading stylesheet /home/sujit/src/sqlunit/etc/canoo2html_result_transform.xsl
 
BUILD SUCCESSFUL
Total time: 10 seconds
  

The first target will run SQLUnit against the test/mock/coretests.xml file, and generate a Canoo XML test log in the output directory. The target need not necessarily be sqlunit-flat, it could also be sqlunit-nested or test from the distribution, or your own custom target to run the tests.

The second target applies an XSL transformation to the Canoo XML file to produce an XHTML file in the output directory (the same directory as the Canoo XML file). The XHTML file name is the same as the Canoo XML file name suffixed with .html. Thus, in our case described above, the XHTML file is output/mytests.xml.html.

The tests are color coded in the XHTML file, successful runs are shown in green and failures in red.

List of currently supported tags

The XML Tags that make up a SQLUnit test specification are described below.

sqlunit ::= ((connection)+, (setup)*, (test | batchtest | diff | echo | func)+, (teardown)*)
      

setup ::= ((set)*, (((sql)*, (foreach)*) | (include)*))
      

 <setup>
   <sql><stmt>delete from foo where 1=1</stmt></sql>
 </setup> 
      

skip ::= (BODY)
      

struct ::= (field)*
      

 <outparam id="1" type="STRUCT">
   <struct>
     <field name="description">Red rubber ball</field>
     <field name="size">30</field>
   </struct>
 </outparam> 
      

subparam ::= (BODY)
      

 <param id="1" name="v1" type="INTEGER">1</param>
 
      

batchresult ::= ((updatecount)*)
      

 <batchresult>
   <updatecount>1</updatecount>
   <updatecount>1</updatecount>
 </batchresult>
 
      

call ::= (stmt, (param)*)
      

 <call>
   <stmt>{? = call AddDept(?)}</stmt>
   <param id="1" type="INTEGER" inout="out">${deptId}</param>
   <param id="2" type="VARCHAR">Information Technology</param>
 </call> 
      

set ::= ((sql|call),result)?|(constructoryArgs*,methodArgs*)?
      

 <set name="${var}" value="14" /> 
      

 <set name="${myquery}">
   <sql><stmt>select col1 from mytable where col2=45</stmt></sql>
   <result>
     <resultset id="1">
       <row id="1">
         <col id="1" name="c1" type="INTEGER">${col1}</col>
       </row>
     </resultset>
   </result>
 </set> 
      

 <set name="${var}" class="java.lang.System" static="true"
     method="getProperty">
   <methodArgs>
     <arg name="key" type="java.lang.String"
     value="property" />
   </methodArgs>
 </set> 
      

classifiers ::= ((severity)?, (category)?)
      

 <test name="Find Agent by Id test"
     failure-message="Find Agent by Id test failed">
   <classifiers>
     <severity>INFO</severity>
     <category>findById</category>
   </classifiers>
   <sql connection-id="mysql">
     ...
   </sql>
 </test> 
      

jndi ::= ((arg)*)
      

 <connection>
   <datasource>jdbc/myDSN</datasource>
   <jndi>
     <arg name="java.naming.factory.initial"
       value="org.jnp.interfaces.NamingContextFactory" />
     <arg name="java.naming.provider.url" 
       value="jnlp://localhost:1099" />
     <arg name="java.naming.factory.url.pkgs" 
       value="org.jboss.naming" />
   </jndi>
 </connection>
      

methodinvoker ::= ((constructorArgs)*, (methodArgs)*)
      

 <methodinvoker class="net.sourceforge.sqlunit.utils.TestArgument"
     method="toString" >
   <constructorArgs>
     <arg name="c7" type="java.lang.Double.TYPE" value="0.2" />
   </constructorArgs>
 </methodinvoker> 
      

teardown ::= (((sql)*, (foreach)*) | (include)*)
      

 <teardown>
   <sql><stmt>drop table testdata</stmt></sql>
   <sql><stmt>drop table testdata2</stmt></sql>
 </teardown> 
      

foreach ::= ((sql)*)
      

 <prepare>
   <sql><stmt>delete from foreachtest where 1=1</stmt></sql>
   <foreach param="id" start="0" stop="10" step="1">
     <sql>       <stmt>insert into foreachtest (id,name) values (${id},'name${id}')</stmt>
     </sql>
   </foreach>
 </prepare>
 
      

connection ::= (((driver, url, user, password, (jarfile-url)?) | (datasource, jndi))?)
      

 <connection>
   <driver>org.postgresql.Driver</driver>
   <url>jdbc:postgresql://localhost:5432/demodb</url>
   <user>defaultuser</user>
   <password>d3fau1tus3r</password>
 </connection>
      

 <connection>
   <datasource>jdbc/myDSN</datasource>
   <jndi>
     <arg name="java.naming.factory.initial"
       value="org.jnp.interfaces.NamingContextFactory" />
     <arg name="java.naming.provider.url" 
       value="jnlp://localhost:1099" />
     <arg name="java.naming.factory.url.pkgs" 
       value="org.jboss.naming" />
   </jndi>
 </connection>
      

func ::= (EMPTY)
      

<func name="databasename" lookup="databasedef" />
      

<func name="mycol" lookup="customdef" param="234" />
      

echo ::= EMPTY
      

include ::= (EMPTY)
      

 <teardown>
   <include file="test/sybase/teardown.sql" />
 </teardown> 
      

result ::= ((((outparam)*, (updatecount)?, (resultset)*)+ | ((outparam)*, exception))?)
      

 <result>
   <resultset id="1">
     <row id="1">
       <col id="1" name="c1" type="INTEGER">1</col>
       <col id="2" name="c2" type="VARCHAR">Blah</col>
       <col id="3" name="c3" type="INTEGER">3</col>
     </row>
   </resultset>
   <resultset id="2" />
   <resultset id="3" />
   <resultset id="4">
   <row id="1">
       <col id="1" name="ac1" type="INTEGER">1</col>
       <col id="2" name="ac2" type="INTEGER">1</col>
       <col id="3" name="ac3" type="VARCHAR">Unused Blahs</col>
       <col id="4" name="ac4" type="INTEGER">1</col>
       <col id="5" name="ac5" type="VARCHAR">Unused</col>
   </row>
   <row id="2">
       <col id="1" name="ac1" type="INTEGER">1</col>
       <col id="2" name="ac2" type="INTEGER">1000</col>
       <col id="3" name="ac3" type="VARCHAR">Deprecated Blahs</col>
       <col id="4" name="ac4" type="INTEGER">1</col>
       <col id="5" name="ac5" type="VARCHAR">Deprecated</col>
   </row>
   </resultset>
 </result> 
      

 <result>
   <exception>
     <code>1234</code>
     <message>Test Exception</message>
   </exception>
 </result> 
      

methodArgs ::= ((arg)*)
      

 <methodArgs>
   <arg name="m3" type="java.lang.Integer" value="-2147483648" />
   <arg name="m7" type="java.lang.Double" value="2.2" />
   <arg name="m8" type="java.lang.String" value="Some text" />
 </methodArgs> 
      

row ::= (col)+
      

 <row id="1">
   <col id="1" name="col1" type="INTEGER">1</col>
 </row> 
      

param ::= (BODY)
      

 <param id="1" name="v1" type="INTEGER">1</param>
 
      

funcdef ::= 
      

 <funcdef name="databasedef" query="select DATABASE()"
     description="Returns the current database name" /> 
      

 <funcdef name="customdef"
   query="select myCol from myTable where myCol1 = ${0}"
   description="Defines a custom function" /> 
      

batchsql ::= ((stmt)+)
      

 <batchsql>
   <stmt>delete from customer where custId=1</stmt>
   <stmt>insert into customer values(1,'Some One','secret',
     'en_US',null,0,now())</stmt>
   <stmt>insert into customer values(1,'Someone Else','secret',
     'en_US',null,0,now())</stmt>
   <stmt>delete from customer where 1=1</stmt>
 </batchsql> 
      

prepare ::= ((set)*, (sql)*, (foreach)*)
      

 <prepare>
   <sql><stmt>delete from foreachtest where 1=1</stmt></sql>
   <foreach param="id" start="0" stop="10" step="1">
     <sql><stmt>insert into foreachtest (id,name) values 
       (${id},'name${id}')</stmt></sql>
   </foreach>
 </prepare> 
      

typedef ::= (EMPTY)
      

 <typemap>
     <typedef typename="MY_SQL_OBJ"
       classname="org.my.stuff.MyClass" />
     <typedef typename="THEIR_SQL_OBJ"
       classname="org.their.things.TheirClass" />
 </typemap>
 
      

severity ::= (BODY)
      

paramset ::= ((param)+)
      

 <paramset id="1">
   <param id="1" type="INTEGER">1</param>
   <param id="2" type="VARCHAR">Some One</param>
 </paramset> 
      

subdef ::= ((param)*)
      

 <subdef name="UpsertStreetDef"
   query="{? = pk_edit_street.upsert_street(?,?)}"
   description="Definition of Upsert Street">
     <param id="1" type="INTEGER" name="rc" inout="out">
 {t}    ${streetId}
     </param>
     <param id="2" type="VARCHAR" name="street">?</param>
     <param id="3" type="VARCHAR" name="city">SFO</param>
 </subdef> 
      

col ::= (BODY)
      

<col id="1" name="col1" type="INTEGER">1</col>
      

batchtest ::= ((skip)?, (classifiers)?, (batchcall | batchsql), batchresult)
      

 <batchtest name="Testing basic batchtest functionality">
   <batchsql>
     <stmt>delete from customer where custId=1</stmt>
     <stmt>insert into customer values(1,'Some One','secret',
       'en_US',null,0,now())</stmt>
     <stmt>insert into customer values(1,'Someone Else','secret',
       'en_US',null,0,now())</stmt>
     <stmt>delete from customer where 1=1</stmt>
   </batchsql>
   <batchresult>
     <updatecount>1</updatecount>
     <updatecount>1</updatecount>
     <updatecount>1</updatecount>
     <updatecount>2</updatecount>
   </batchresult>
 </batchtest> 
      

constructorArgs ::= ((arg)*)
      

 <constructorArgs>
   <arg name="c1" type="java.lang.Byte" value="-128" />
   <arg name="c2" type="java.lang.Short" value="-32768" />
   <arg name="c3" type="java.lang.Integer" value="-2147483648" />
   <arg name="c5" type="java.lang.Character" value="a" />
   <arg name="c6" type="java.lang.Float" value="1.1" />
   <arg name="c7" type="java.lang.Double" value="2.2" />
   <arg name="c8" type="java.lang.String" value="Some text" />
 </constructorArgs> 
      

batchcall ::= ((stmt), (paramset)+)
      

 <batchcall>
   <stmt>insert into customer values(?,?,?,?,?,?,now())</stmt>
     <paramset id="1">
       <param id="1" type="INTEGER">1</param>
       <param id="2" type="VARCHAR">Some One</param>
       <param id="3" type="VARCHAR">secret</param>
       <param id="4" type="VARCHAR">en_US</param>
       <param id="5" type="VARCHAR">SQLUnit</param>
       <param id="6" type="INTEGER">0</param>
     </paramset>
     <paramset id="2">
       <param id="1" type="INTEGER">2</param>
       <param id="2" type="VARCHAR">Someone Else</param>
       <param id="3" type="VARCHAR">secret</param>
       <param id="4" type="VARCHAR">en_US</param>
       <param id="5" type="VARCHAR">SQLUnit</param>
       <param id="6" type="INTEGER">0</param>
     </paramset>
 </batchcall> 
      

outparam ::= (BODY)|(resultset)|(struct)
      

 <outparam id="1" type="INTEGER">24</outparam> 
      

 <outparam id="1" type="oracle.CURSOR">
   <resultset id="1">
     <row id="1">
       <col id="1" type="INTEGER">7</col>
       <col id="2" type="VARCHAR">James Bond</col>
       <col id="3" type="VARCHAR">Martini</col>
     </row>
   </resultset>
 </outparam> 
      

typemap ::= (typedef)*
      

 <connection>
   <typemap>
     <typedef typename="MY_SQL_OBJ"
       classname="org.my.stuff.MyClass" />
   </typemap>
 </connection>
 
      

test ::= ((skip)?, (classifiers)?, (match)*, (prepare)?, ((sql | call | methodinvoker | dynamicsql), result)?)
      

 <test name="Adding department HR">
   <sql>
     <stmt>select AddDept(?)</stmt>
     <param id="1" type="VARCHAR">Human Resources</param>
   </sql>
   <result>
     <resultset id="1">
       <row id="1">
         <col id="1" name="adddept" type="INTEGER">
           ${deptId_HR}
         </col>
       </row>
     </resultset>
   </result>
 </test> 
      

resultset ::= (row)*
      

 <resultset id="1">
   <row id="1">
     <col id="1" name="col1" type="INTEGER">1</col>
   </row>
 </resultset> 
      

match ::= ((arg)*)
      

 <match col-id="1-2"
   matcher="net.sourceforge.sqlunitmatchers.PercentageRangeMatcher">
   <arg name="pc-tolerance" value="20" />
 </match> 
      

field ::= (BODY)|(struct)
      

 <struct>
   <field name="capacity">30</field>
   <field name="handler">     <struct>
       <field name="name">AEF Gripper</field>
       <field name="workLoad">12.5</field>
     </struct>
   </field>
   <field name="storage">NULL</field>
 </struct>
 
      

sub ::= 
      

 <sub lookup="UpsertStreet">
   <subparam name="street" value="Market Street" />
 </sub> 
      

exception ::= (code)?, (message)?
      

 <exception>
   <code>0</code>
   <message>ERROR: Cannot insert a duplicate key into unique index ux2_employee</message>
 </exception> 
      

diff ::= ((skip)?, (classifiers)?, (match)*, (prepare)?, (sql|call), (sql|call))
      

 <diff name="Diffing different resultset/multiple matchers"
     failure-message="Diff test #3 failed">
   <match col-id="1" 
     matcher="net.sourceforge.sqlunitmatchers.AllOrNothingMatcher">
     <arg name="match" value="true" />
   </match>
   <match col-id="2"
     matcher="net.sourceforge.sqlunitmatchers.RangeMatcher">
     <arg name="tolerance" value="50" />
   </match>
   <match col-id="3" 
     matcher="net.sourceforge.sqlunitmatchers.PercentageRangeMatcher">
     <arg name="pc-tolerance" value="10" />
   </match>
   <sql connection-id="1">
     <stmt>select widget_name, price_per_unit, number_sold from widgets where widget_id=?</stmt>
     <param id="1" type="INTEGER">1</param>
   </sql>
   <sql connection-id="2">
     <stmt>select widget_name, price_per_unit, number_sold from widgets where widget_id=?</stmt>
     <param id="1" type="INTEGER">2</param>
   </sql>
 </diff> 
      

category ::= (BODY)
      

arg ::= (EMPTY)
      

 <arg name="java.naming.factory.initial"
   value="org.jnp.interfaces.NamingFactory" />
 
      

 <methodArgs>
   <arg name="col1" type="java.lang.String" value="ABC" />
 </methodArgs>
 
      

sql ::= (stmt, (param)*)
      

 <sql>
   <stmt>select custId from customer where custId=?</stmt>
   <param id="1" type="INTEGER">1</param>
 </sql> 
      

dynamicsql ::= ((constructorArgs)*, (methodArgs)*)
      

 <dynamicsql class="net.sourceforge.sqlunit.utils.TestArgument"
   static="true" method="getGeneratedOracleSqlString">
 </dynamicsql> 
      

Extending SQLUnit: Writing your own tag handler

Overview

This is getting asked a lot nowadays, so I thought I will write this up for the benefit of those who are interested in doing this. To include a new tag, you need to do three things:

1. Write a Handler for your tag that implements the IHandler interface.

2. Add the mapping of the tag name to the actual handler class in the etc/handlers.properties file

3. Update the list of "swappable" tags in handlers.properties, if applicable.

4. Modify the sqlunit.dtd file so that your new tag becomes part of the sqlunit dialect.

If you have written a TagHandler that does something unique, and would like to contribute it to the SQLUnit project, it would be gratefully accepted and credit given.

---

Finding your way around

It may help a little if you are familiar with the way the code is laid out in the distribution, so its easy to find things. The following table provides the name, location and a brief description of each of the packages in use.

If you are already familiar with the pre-3.8 code, then you will find many changes in code for releases 4.0 and up. Hopefully, you will find that the changes are for the better. The code is (at least in my opinion) easier to read and understand, and better organized. Most of all, it should be easier to add new features as they are requested.

Table 67. SQLUnit Code Layout

Package Name	Location	Description
net.sourceforge.sqlunit	src/net/sourceforge/sqlunit	The main package which contains the classes which did not fit neatly into the sub-packages detailed below. This used to be the only package for SQLUnit.
net.sourceforge.sqlunit.ant	src/net/sourceforge/sqlunit/ant	Contains the Ant SQLUnit task
net.sourceforge.sqlunit.beans	src/net/sourceforge/sqlunit/beans	Contains beans to support the handlers. Older versions of SQLUnit handlers would return an Object from the process() method. They still return Objects, but now some of the handlers return well-defined Objects which can be cast to one of the beans in this package.
net.sourceforge.sqlunit.handlers	src/net/sourceforge/sqlunit/handlers	Contains all the handler implementations. All the implementations implement the IHandler interface.
net.sourceforge.sqlunit.types	src/net/sourceforge/sqlunit/types	Contains implementations of various types. All the implementations implement the IType interface.
net.sourceforge.sqlunit.matchers	src/net/sourceforge/sqlunit/matchers	Contains some user-defined matcher classes for use with the diff tag. All matchers implement the IMatcher interface.
net.sourceforge.sqlunit.reporters	src/net/sourceforge/sqlunit/reporters	Contains reporter classes that can be used with SQLUnit. All reporters implement the IReporter interface.
net.sourceforge.sqlunit.utils	src/net/sourceforge/sqlunit/utils	Contains utility classes whose methods get called by SQLUnit.
net.sourceforge.sqlunit.tools	src/net/sourceforge/sqlunit/tools	Contains tools to generate test cases from existing SQL statements using console input and GUI based input.
net.sourceforge.sqlunit.test	test/java	Contains some Java code used for LOB testing.
net.sourceforge.sqlunit.test.mock	test/java/mock	Contains code for the mock database framework for testing SQLUnit code independent of a database.
Test files for various databases	test/*	Contains subdirectories named after various databases and contains stored procedures and test XML files for these databases.
SQLUnit registry files	etc/*.properties	Most of SQLUnit is interface driven, and uses Factory classes to instantiate a named implementation of an interface. The registry files contain the mappings to the various implementations.

public java.lang.Object process(org.jdom.Element el) throws java.lang.Exception;
          

IHandler handler = HandlerFactory.getInstance(element.getName());
          

mytag = net.sourceforge.sqlunit.MyTagHandler
          

sqlunit.swappable.tags = test, batchtest, diff
test.swappable.tags = sql, call, methodinvoker, dynamicsql
batchtest.swappable.tags = batchsql, batchcall
          

<!ELEMENT mytag EMPTY>
  <!ATTLIST mytag
    attr1 CDATA #REQUIRED
    attr2 CDATA #REQUIRED>

          

Basic JDBC Connection

SQLUnit can build up a database Connection object using JDBC if the driver, URL, user and password are supplied. Here is an example of setting up a Connection object for the SQLUnit Mock Database.

<connection connection-id="1">
  <driver>net.sourceforge.sqlunit.test.mock.SQLUnitMockDriver</driver>
  <url>jdbc:mock:net.sourceforge.sqlunit.test.mock.SQLUnitMockDatabase</url>
  <user>defaultuser</user>
  <password>defaultuser</password>
</connection>
      

JDBC Connection with properties

Certain old database drivers will complain about not being able to connect to the database if the user and password are not embedded in the JDBC URL itself. Certain others allow parameters other than user and password to be passed on the URL as a query string, so the user can set additional properties. In either of the two cases, you should simply embed all the properties in the URL element and leave the user and password elements empty. Here is an example:

<connection connection-id="3">
  <driver>net.sourceforge.sqlunit.test.mock.SQLUnitMockDriver</driver>
  <url>jdbc:mock:net.sourceforge.sqlunit.test.mock.SQLUnitMockDatabase;user=none;password=none</url>
  <user />
  <password />
</connection>
      

Basic JNDI Connection

SQLUnit can also lookup a DataSource object from a JNDI server, and get the database Connection object from that. You will need to specify the data source name to lookup, and the JNDI arguments. Refer to the documentation for your JNDI server to determine what the JNDI arguments should be. Here is an example of setting up a Mock JNDI server that SQLUnit uses for its tests.

<connection connection-id="2">
  <datasource>jdbc/mockDSN</datasource>
  <jndi>
    <arg name="java.naming.factory.initial"
        value="net.sourceforge.sqlunit.test.mock.MockInitialContextFactory" />
  </jndi>
</connection>
      

JDBC Connection using local JAR file

SQLUnit can also instantiate a driver class from a JAR or class file that is not in SQLUnit's classpath. This feature was originally built to allow SQLUnit to regression test a newer version of a JDBC driver against the older version. Since the class names were identical, SQLUnit had to load them in different contexts. SQLUnit uses a non-delegating URLClassLoader to load the driver class from the JAR or class file specified. JDBC Driver JAR files are normally self-contained, so you will typically have to specify only one single JAR file, but you can also specify a comma-separated list of file URLs containing paths to JAR files and directories containing class files. An example of the connection setup used for mock testing this functionality follows:

<connection connection-id="4">
  <driver>net.sourceforge.sqlunit.test.mock.SQLUnitMockDriver</driver>
  <url>jdbc:mock:net.sourceforge.sqlunit.test.mock.SQLUnitMockDatabase</url>
  <user />
  <password />
  <jarfile-url>file:build/,file:lib/mockrunner.jar,file:lib/log4j-1.2.13.jar,file:lib/commons-lang-2.1.jar</jarfile-url>
</connection>
      

Connection Properties from external file

Connection properties can also be specified in an external file. This is useful when the same tests should run in different environments against different databases. The properties are specified as the value of the extern attribute of the connection element. The property can be specified either as a relative or absolute file path, or as a resource in the application classpath. Property files can either contain properties to build a Connection using JDBC or look up a DataSource using JNDI. Here are some examples:

To set up an external JDBC connection, the properties file must contain the following:

#
# etc/external-jdbc-connection.properties
#
sqlunit.driver = net.sourceforge.sqlunit.test.mock.SQLUnitMockDriver
sqlunit.url = jdbc:mock:net.sourceforge.sqlunit.test.mock.SQLUnitMockDatabase
sqlunit.user = 
sqlunit.password = 
      

To set up an external JNDI connection, the properties file must contain the following name-value pairs:

#
# etc/external-jndi-connection.properties
#
sqlunit.datasource = jdbc/mockDSN
sqlunit.jndi.java.naming.factory.initial = net.sourceforge.sqlunit.test.mock.MockInitialContextFactory
      

The connection element to point to a properties file as a file name would look like this:

<connection connection-id="5" extern="etc/external-jdbc-connection.properties" />
      

To specify the properties file as a resource, it must be in the classpath and the connection element will look like this:

<connection connection-id="7" extern="external-jdbc-connection" />
      

Passing in a Connection object to SQLUnit

You can also embed SQLUnit inside your application, in which case you do not need to specify the connection element in the XML file at all. The only downside to this approach is that you are restricted to only a single database connection. To do this, build the Connection object in your application, and set the Connection object for the SQLUnit object. A code snippet to do this would look something like this:

    // Instantiate SQLUnit
    SQLUnit sqlunit = new SQLUnit("sqlunit");
    // set the properties
    sqlunit.setTestFile("/my/test/file.xml");
    sqlunit.setHaltOnFailure(false);
    sqlunit.setDebug(false);
    sqlunit.setReporter(new TextReporter("/my/report/file.xml"));
    // set the Connection
    sqlunit.setConnection(conn);
    // run the tests
    try {
        sqlunit.runTest();
    } catch (SQLUnitException e) {
        // report the exception
    }
      

Note that you can also choose to supply the Connection information inside the XML files themselves using any of the methods described above, and not specify the setConnection() method at all. SQLUnit will then read the Connection properties from the XML files and run the tests using these Connections.

Controlling Transactions

SQLUnit can put each Connection object in one of three transaction modes. The mode is controlled by the transaction-support attribute. The table below summarizes the key characteristics of each mode.

When transaction-support=on (the default), SQLUnit turns off JDBC AutoCommit mode and ensures that each SQL or CALL is committed on success and rolled back on failure. The unit of work it assumes is the block contained in either an sql.stmt or call.stmt tag. If a different unit of work is required for your tests, you should consider setting transaction-support=off.

When transaction-support=off, SQLUnit turns of JDBC AutoCommit mode. It will not do either COMMIT or ROLLBACK. The COMMIT or ROLLBACK has to be handled by the client by adding the appropriate statements in the stored procedures.

When transaction-support=implicit, SQLUnit will turn on JDBC AutoCommit mode, but not issue any COMMITs or ROLLBACKs. The job of controlling transactions is left to the database. Some databases, such as Sybase and MS SQL Server, have modes that intelligently handle transactional units of work on behalf of the client. In situations where the database does not offer these features, the JDBC transaction behavior will be used, where every SQL statement or stored procedure call will be treated as a single transaction.

Prior to version 4.6, the transaction support feature did not work correctly. AutoCommit was turned on in both transaction-support on and off modes. The only difference between the two modes was that SQLUnit did a COMMIT or ROLLBACK after executing a SQL or CALL statement in the "on" mode, and not in the "off" mode. The problem was not noticeable unless you explicitly wanted to change the default unit of work, and found that setting transaction-support=off did not produce the results you expected. The fix in version 4.6 was to set the default AutoCommit mode to false, so transaction-support=off would not do any COMMIT or ROLLBACK on its own. However, this created a problem for Sybase tests that use the CHAINED feature (and is likely to cause similar problems for MS SQL Server tests that use TRANSACTION MODE IMPLICIT, although none have been reported at the time of this writing) with transaction-support=off. Using CHAINED or IMPLICIT implies that the caller lets the database decide when to apply a COMMIT or ROLLBACK, if the database supports it, or apply COMMIT or ROLLBACK after every call (the JDBC default). This mode requires that AutoCommit must be turned on. For this scenario, a new transaction-support mode "implicit" has been introduced in version 4.7. If you experience problems with CHAINED mode or IMPLICIT mode in Sybase or MS SQL Server, then you should try to use the transaction-mode="implicit".

Setting explicitly from scalar

A variable "var" in a SQLUnit test specification is denoted by ${var}. The value of a single variable can be set explicitly using the scalar form of the set tag:

<set name="${var}" value="41">
      

Setting explicitly from query

You can also set the value for a group of variables using the non-scalar form of the set tag, shown below, which will set the value of ${myquery.col1} to the value returned from the SELECT statement.

<set name="${myquery}">
  <sql><stmt>select col1 from mytable where col2=45</stmt></sql>
  <result>
    <resultset id="1">
      <row id="1">
        <col id="1" name="c1" type="INTEGER">${col1}</col>
      </row>
    </resultset>
  </result>
</set>
      

Setting explicitly from Java method call

Since version 4.3, variables can also be set explicitly from the results of a Java method call, which must return a String. The set tag needs to specify the class name and the method name to invoke, and the class must be in the CLASSPATH. An example of such a call is shown below:

<set name="${var}" static="true" class="com.mycompany.sqlunit.Utilities"
    method="currentTimestamp">
  <methodArgs>
    <arg name="prefix" type="java.lang.String" value="MyPrefix" />
    <arg name="format" type="java.lang.String" value="yyyyMMddhhmmss" />
  </methodArgs>
</set>
      

In the above example, SQLUnit will call the method currentTimeStamp in the class com.mycompany.sqlunit.Utilities and populate the returned string into the ${var} variable in its symbol table. The ${var} can then be used by other tests in the suite.

public static String currentTimeStamp(String,String);
      

Setting variables from ant

Since version 4.6, assuming that SQLUnit is called as an Ant task, SQLUnit can refer to the variables in Ant's context by prefixing the variable name with "ant.". For example, if you wanted to access the Ant variable ${ant.project.name} within SQLUnit, you can refer to it as ${ant.ant.project.name}.

Setting variables implicitly

Variables can also be set implicitly when it is defined as a variable in a result tag and when it has not been previously declared (ie it does not have a value). The example below sets the value of the variable ${var} from the value returned from the stored procedure AddDept.

<test name="Adding department HR">
  <sql>
    <stmt>select AddDept(?)</stmt>
    <param id="1" type="VARCHAR">Human Resources</param>
  </sql>
  <result>
    <resultset id="1">
      <row id="1">
        <col id="1" name="adddept" type="INTEGER">${var}</col>
      </row>
    </resultset>
  </result>
</test>
      

In both the implicit and explicit cases, the variable ${var} functions like an lvalue in an assignment expression. Once the variable is set, the variable functions like an rvalue in an assignment expression. So if we had specified the scalar set tag above to appear in the setup tag for the test specification or the prepare tag for the test, then the test would compare the value that AddDept returned with the value for the variable ${var}.

The scope of the variable is an individual test specification file. So a variable that has been declared and assigned a value is available for the life of the test suite defined by a single XML file.

The only way to reset the value of a variable that has already been declared and assigned a value is to invoke the set tag for that variable again within the test specification.

Including XML Files

When talking of included files, people commonly mean included XML in the context of XInclude or external entity references. The SQLUnit include tag represents an unfortunate choice of keyword and does not imply inclusion of external XML files. SQLUnit does not natively support inclusion of external XML files at all. If this functionality is desired, then you can use external entity references or embed XInclude tags and pre-process your test suite with a tool such as XIncluder.

Using External entity references is simple and requires no pre-processing of the test suite. Suppose you wanted to include an XML snippet that predeclares certain common operations that you wish to have executed during the setup phase of each test. The example below shows the declarations you would need to make.

<!DOCTYPE sqlunit SYSTEM "sqlunit.dtd" [
  <!ENTITY common-setup SYSTEM "file:tests/common-setup.xml">
]>
    <connection... />

    <setup>
      &common-setup;
    </setup>

    <test... />

      

The ENTITY declaration within the DOCTYPE associates the name of the entity with the file name. The entity can then be referenced in the body of the test suite as shown in the setup tag above.

Information on using embedded XInclude tags and using a pre-processor can be found at the XIncluder project. I considered calling XIncluder from within SQLUnit, but that would impose a penalty for everyone, including people who have no interest in including XML files. If you need this functionality, it is easy enough to pre-process the file using a script, and then delete the generated test suite XML file after the test is complete.

SQLUnit Include Files

Include files in SQLUnit refer to plain text files which contain a list of SQL or stored procedure calls that need to be run before or after a test suite is run.

The include file is parsed according to a set of fairly simple and hopefully intuitive rules. An include file consists of a collection of statements separated by delimiters.

The set of allowed delimiters are a free-standing slash ("/") character, a free-standing semicolon (";") character or a free-standing string "go" on its own line. In all cases other than when the SQL starts with a CREATE or REPLACE, a terminating semicolon (";") character is also supported.

The various kinds of statements supported in an include file are comments, CREATE and REPLACE statements, EXECUTE PROCEDURE calls, other SQL statements and shell calls. We will describe each of them in detail below.

Both single line and multi line SQL comments are supported. Single line SQL comments are those that begin with "--" on a new line. Multi-line comments begin with a "/*" and end with a "*/" followed by a newline. Comments are only for the benefit of the human author or user of the include file and is of no consequence to SQLUnit. Comments are discarded by the parser.

CREATE and REPLACE statements are typically used to define stored procedures, functions or triggers and can contain embedded semicolon characters at the end of the line before the definition is complete. These statements cannot be delimited with a terminating semicolon character because of ambiguity. Although CREATE and REPLACE can be used generically for DDL operations, making this rule only for stored procedures or functions would have led to a complexity in the parser grammar. It is also easier to remember about this restriction when applied to a generic set of statements rather than a very specific subset. CREATE and REPLACE statements can be and often will be multi-line calls.

EXECUTE PROCEDURE statements are those start with "EXEC ", "EXECUTE PROCEDURE ", or "{CALL ". They differ from regular SQL statements in that they need to be handled using a CallableStatement. The statement will be re-written to the form expected by the JDBC driver, ie {CALL procedurename()}, if it is not already specified in that form. The syntax rules for EXECUTE PROCEDURE statements are identical to that of regular SQL statements (described below), but SQLUnit will handle them as a CallableStatement rather than as a PreparedStatement. Any of the delimiters listed above will work with an EXECUTE PROCEDURE statement. Like CREATE and REPLACE statements, EXECUTE PROCEDURE statements can be multi-line.

Other SQL statements are those which do not fall into the two categories described above. Examples are SELECT, INSERT, UPDATE, DELETE, INDEX, DROP, etc. They are handled by SQLUnit using PreparedStatement. Any of the listed delimiters will work for these statements. Like the CREATE/REPLACE and EXECUTE PROCEDURE statements, these can also be multi-line. Some examples are listed below:

-- Simple select
select * from foo where bar = 1
/
-- A multi-line example
UPDATE foo 
  SET bar = 1
  WHERE bar = 2
/
      

In all the types of SQL calls described above, case does not matter, unless it is enforced by the database engine, for example, case sensitive table and column names in Sybase.

Shell calls are calls to the operating system. They are preceded with a bang ("!") character on a new line. Since Java will spawn a separate process for the shell command, it has no knowledge of the user's environment. It is generally recommended to call a shell script or batch file with the full path name and source the environment within the script. In case you wish to use the shell metacharacters such as the pipe ("|") or redirection metacharacters, it must be called within the context of the shell, with the actual command in parenthesis. As before, the full path to the command processor will need to be supplied. Multi-line commands can be specified using backslash ("\") character. Any of the delimiters listed above can be used. Here are some examples of a shell call.

-- Example of a shell script call
!/path/to/shell/script.sh
/
-- Example of a call using the shell
!/bin/sh -c (rm -rf /tmp/*.test; ls -l | wc -l >/tmp/xyz)
/
-- Example of a call using the Windows NT command processor
!COMMAND.COM /C (DEL C:\TMP\*.TST)
/
-- Example of a call using Windows XP command processor
!CMD.EXE /C (DEL C:\TMP\*.TST)
/
      

In all cases, the parser makes no attempt to validate or parse the SQL or shell script. If SQLUnit encounters an error while running the statement, it will return the stack trace to the caller to aid in debugging.

The BNF grammar (generated by jjdoc) for the Include File Parser is shown below. The comments come from inline comments in the parser grammar file, and are terse versions of the description above.

DOCUMENT START
NON-TERMINALS
// An include file is a collection of statements separated by one of the
// available delimiter characters.
	StatementCollection	:=	( CallableSQLStatement | ShellCallStatement | OtherSQLStatement | MultilineSQLStatement | CommentStatement )* ( <EOF> )?
// Comment statements can either be single line or multiline.
	CommentStatement	:=	( SingleLineCommentStatement | MultiLineCommentStatement )
// Single line comments begin with a "--" string on a newline and are 
// terminated by newline.
	SingleLineCommentStatement	:=	( <START_SINGLELINE_COMMENT> ( <SINGLELINE_COMMENT_CHAR> )* <END_SINGLELINE_COMMENT> )
// Multi line comments begin with "/*" on a newline and can contain
// multiple lines. They are terminated with a "*/" string at the end
// of the line.
	MultiLineCommentStatement	:=	( <START_MULTILINE_COMMENT> ( <MULTILINE_COMMENT_CHAR> )* <END_MULTILINE_COMMENT> )
// Multi line SQL statements are those that have a semi-colon (";") character
// terminating lines within the multi-line SQL. The trailing semi-colon 
// delimiter is not allowed for this type of statement.
	MultilineSQLStatement	:=	( <START_MULTILINE> ( <MULTILINE_CHAR> )* <MULTILINE_DELIMITER> )
// A Callable SQL statement is one which starts with "EXEC(UTE PROCEDURE) "
// or "{CALL ". The execute procedure call will be rewritten to a CALL form,
// since that is the form all JDBC drivers understand. The handler will 
// use a java.sql.CallableStatement to handle this call.
	CallableSQLStatement	:=	( ( <START_EXEC_PROC_CALL> | <START_EXEC_PROC_OTHER> ) ( <EXEC_PROC_CHAR> | <EXEC_PROC_OPEN_PAREN> | <EXEC_PROC_CLOSE_PAREN> | <EXEC_PROC_CONT_CHAR> )* <EXEC_PROC_DELIMITER> )
// A Shell call statement represents a call to an operating system command
// or external utility. This may be useful for cleaning up temporary files
// or making SQL calls through the database client of your choice. A statement
// can span multiple lines using the backslash (\) continuation character. 
// The parser treats the shell call as a system process and will start up a 
// Java Runtime object for it. Note that the runtime will have no knowledge
// of the environment settings, so you need to provide full path names. Special
// features of the shell such as pipes and redirectors will also not work 
// unless you wrap the command in the shell processor, like so:
//   !/bin/sh -c (command) for Unix/Linux
//   !command.com /c (command) for Windows XP
//   !cmd.exe /c (command) for Windows NT
// The parenthesis are required. The recommended procedure would be to 
// simply invoke a shell script which does what you want as well as source
// environment variables that you may need.
	ShellCallStatement	:=	( <START_SHELL_CALL> ( <SHELL_CALL_CHAR> | <SHELL_CONT_CHAR> | <OPEN_PAREN> | <CLOSE_PAREN> )* <SHELL_CALL_DELIMITER> )
// The Other SQL statement represents an SQL statement that is not a CREATE
// or REPLACE call (MultilineSQLStatement) or an EXEC(UTE PROCEDURE) call
// (CallableSQLStatement). SQL statements such as SELECT, INSERT, UPDATE,
// DELETE, RENAME, DROP, etc, fall into this category. The SQL statement
// can contain newlines which will be transformed by the parser into white
// space. The parser will use a java.sql.PreparedStatement to process this
// statement.
	OtherSQLStatement	:=	( ( <START_OTHER_SQL_CHAR> | <OTHER_SQL_CHAR> )* <OTHER_SQL_DELIMITER> )

DOCUMENT END

      

List of currently supported datatypes

The Database datatypes supported by SQLUnit are described below. Type classes are the names of the class used by SQLUnit to provide support for that datatype. The Type name is the name of the datatype that is used in the SQLUnit test specification. The Server is the database server for which the datatype is defined. A Server name of Any indicates global support across all databases. The Allow Input, Allows Output and Is Sortable columns indicate whether you can use these datatypes as inputs (in your param element) or as outputs (in your result specifications), and whether you can sort by columns with these datatypes (using the sort-by attribute of the resultset element).

Starting with version 4.3, the base type mappings have been slightly modified to conform with the usage recommendations from Sun Microsystems. Details of the changes are given below. Table 2. Changes to base type mappings Data Type Old Mapping New Mapping FLOAT FloatType (java.lang.Float) DoubleType (java.lang.Double) NUMERIC DoubleType (java.lang.Double) BigDecimalType (java.math.BigDecimal) VARBINARY BinaryType (java.io.InputStream) ByteArrayType (byte[]) As a result, the overrides for adaptive_server_anywhere.NUMERIC, adaptive_server_enterprise.NUMERIC, sybase_sql_server.NUMERIC, oracle.NUMERIC, postgresql.NUMERIC and mysql.FLOAT have been removed, since these mappings now conform to the corrected base type mappings.

Extending SQLUnit: Adding support for your own datatypes

Differences in implementation among different JDBC drivers and databases will require you to add support for your database if your datatype is not listed above, or if it uses a different native type from that listed. SQLUnit allows you to define these outside the package in a properties file usertypes.properties which must be in your CLASSPATH. Some common scenarios are covered below:

# usertypes.properties
adaptive_server_enterprise.DATETIME.class = net.sourceforge.sqlunit.types.TimestampType
adaptive_server_enterprise.DATETIME.type = 93
        

# usertypes.properties
postgresql.NUMERIC.class = net.sourceforge.sqlunit.types.BigDecimalType
postgresql.NUMERIC.type = 2
        

// DateTimeType.java
package com.myapplication.mypackage;

import net.sourceforge.sqlunit.types.TimestampType;

/**
 * Models a Sybase DATETIME type.
 */
public class DateTimeType extends TimestampType {
    // just use this new pattern to parse and format
    protected static String PATTERN = "MMM dd, yyyy HH:mm:ss Z";
}
        

# user.properties
adaptive_server_enterprise.DATETIME.class = com.myapplication.mypackage.DateTimeType
adaptive_server_enterprise.DATETIME.type = 93
        

List of currently supported Assertions

Prior to version 4.0, SQLUnit only allowed for matching two results for equality, or for matching specific columns using a matching criterion defined by one or more MatchPattern objects. SQLUnit now allows the caller to specify an assertion that should be satisfied for a given test as an attribute of the test, diff and batchtest tags. The change is backward compatible, so if the assert attribute is not specified, it defaults to "equal" in case of diff and test, and "batch-equal" in case of batchtest. Both these default assert attributes are actually macro-assertions, and are composed of a comma-separated series of assertions. These assertions, along with some new ones, can be specified either individually or in a sequence that makes sense in the context of the test case.

The assertions that are currently supported by SQLUnit are shown below:

Extending SQLUnit: Adding your own Assertion

Assertions can be of two kinds, one specific to the batchtest tag (involving BatchDatabaseResult objects) and the other specific to diff and test tags (involving DatabaseResult objects). The following things need to be done to add a new assertion.

1. Determine if the tag is for the batchtest tag or the diff/test tags.

2. Add an entry for the mapping from the assert string to the actual method name to invoke.

3. Implement the method.

This can be most easily explained by means of an example. Suppose we wanted to implement an assertion "in-same-ballpark" for DatabaseResult objects. We wish to map this assertion to the method assertInSameBallpark(). The mapping to set up is shown below.

    private static final String[] DATABASE_RESULT_ASSERT_MAPPING = {
        {"equal", "assertEqual"},
        // more mappings here
        {"in-same-ballpark", "assertInSameBallpark"}
    };
      

We then implement the logic to determine if two DatabaseResult objects are in the same ballpark. This involves adding a method and implementing it, like so:

    private static void assertInSameBallpark(String failureMessage,
            DatabaseResult expR, DatabaseResult gotR, List matchPatterns)
            throws SQLUnitException {
        // some logic here. If the test fails it should throw an
        // SQLUnitException with an ASSERT_FAILED message.
    }
      

Look at the code for Assertions.java for more information.

Handling Object output from ORDBMS

Databases such as PostgreSQL allow storing non-primitive database types, both built-in such as the geometric data types, or user-defined. These datatypes can be returned from stored procedure calls. As long as a toString() method is defined for these objects, all that needs to be done is to specify the String representation of this object in your expected result. For example, a PostgreSQL POINT data type is backed by the org.postgresql.geometric.PGPoint object, which defines a toString() method. So a POINT(x,y) can be represented by the String (x,y). So to test that a POINT(10.0, 20.0) is returned as the seventh column of the first row of the first resultset from a procedure call, the XML would look something like this:

<result>
  <resultset id="1">
    <row id="1">
      <col id="1" type="INTEGER">123</col>
      ...
      <col id="7" type="OTHER">(10.0,20.0)</col>
    </row>
  </resultset>
</result>
        
        

The toString() method must be defined in order for the comparison to work, since SQLUnit compares two objects for equality based on their String representation returned from the toString() method.

Note that SQLUnit does not yet (and probably never will) support passing built-in or user-defined objects as arguments to stored procedures to be tested with SQLUnit. The reason is that it is difficult to convert from the String representation to the equivalent object representation in a generic manner. The approach taken in the PostgreSQL test (AddEmployee.sql) is to pass the parameters for object creation (the x and y coordinates for the Employee.location::POINT) as separate arguments to the stored procedure. The stored procedure is responsible for constructing a POINT object and storing it into the database. This approach allows scaling to more complicated types (such as CIRCLE((x,y),r)) in a trivial manner.

Handling BLOB and CLOB outputs

Modern databases are frequently capable of storing BLOBs (Binary large objects) and CLOBs (Character large objects) as columns in their tables. Since it is hard and messy to include the expected CLOB output in the XML test file, and pretty much impossible in case of a BLOB, SQLUnit requires that you either specify the name of a file containing the data or specify the MD5 Checksum for the data as a String in your expected results element.

SQLUnit converts large objects such as CLOB, BLOB, LONGVARBINARY and LONGVARCHAR to an MD5 digest when extracting from the database via a stored procedure or SQL call. This is done in order to make subsequent comparisons faster. Other types are extracted and stored as Strings, which may be converted to an MD5 digest form at comparison time if the test matches it against a file name (prefixed with file:) or an MD5 digest (prefixed with md5:).

If you are specifying the name of the file to compare against, prepend either the full path name of the file or the relative path offset from the directory where the tests will be run, with the string file:. If specifying the MD5 Checksum for the resulting LOB data, prepend the checksum string with md5:.

SQLUnit writes CLOB and BLOB objects retrieved from the database into temporary files on disk. In case of a test failure, the mapping between the LOB column [resultset,row,col] in the failed test and the temporary file created is shown at the bottom of the test report. It is up to the user to verify what the difference is using operating system tools such as diff (for CLOBs) and cmp (for BLOBs).

Java Object Support

Java aware databases support storing Java objects in the database natively. Most relational databases are not Java aware. SQLUnit provides a way to handle serialized Java Objects as first class Java Objects by storing them as BLOB columns. This is a little different from storing native database objects stored in Object Relational Databases as discussed in the section Handling Object Output from ORDBMS. Java Objects are populated, then serialized and the bytecode generated are then stored in the database as BLOBs. When they are returned in the resultset of a stored procedure or SQL call, they can be automatically stringified using the Object's toString() method. The only requirements for the Object being stored are that it must implement java.io.Serializable and override the default Object.toString() method.

Here is a contrived example. Suppose you wanted to store a permission object in a user table, to indicate which user had access to what system resources. You could create a Dictionary Object which contained a Map of resource names to permission. So you would create a Dictionary class which implements the java.io.Serializable interface and whose toString() method you would override to create a compact but readable String representation. The source code for the Dictionary object is available at test/Dictionary.java. You would then have your application populate these objects for different users and store them in the database. The source code for the JUnit class which simulates this behavior is available at test/LOBLoader.java. The JUnit class also creates several other files in order to run the MySQL SQLUnit tests. See the README file for details on how to run the test.

What is User-Defined Matching

Starting with version 2.7, SQLUnit provides the ability to match the results generated from two different SQL queries or Stored Procedure calls. It also provides the ability for the user to define matching strategies on a per-column basis, to override the default matching strategy. By default, SQLUnit will match two columns from two different results on the basis of exact equality, including type. Matchers match only the value, based on the matching criteria. The user can specify different behavior for one or more columns in the result, such as match when the two columns are different by a certain tolerable amount.

SQLUnit ships with some simple Matcher classes that are available in net.sourceforge.sqlunit.matchers. These can be used as-is, or can be used as the basis for more sophisticated Matcher classes with site-specific functionality.

Wildcarding rules for Match Attributes

List of currently supported Matchers

Extending SQLUnit: Writing your own Matcher

If you have written a Matcher that does something unique, and would like to contribute it to the SQLUnit project, it would be gratefully accepted and credit given.

Writing your own Matcher is easy. The Matcher simply specifies the match operation by implementing the isEquals(String,String,Map) method in the net.sourceforge.sqlunit.IMatcher interface. Specifying where the matcher should be invoked can be done from the SQLUnit specification file itself.

We will illustrate the process by taking the PercentageRangeMatcher class supplied with the SQLUnit distribution and annotating it with comments that may be helpful in writing your own matcher.

/*
 * $Id: sqlunit-book.xml,v 1.100 2006/04/30 22:25:54 spal Exp $ (1) 
 * $Source: /cvsroot/sqlunit/sqlunit/docs/sqlunit-book.xml,v $
 * SQLUnit - a test harness for unit testing database stored procedures.(2)
 * Copyright (C) 2003  The SQLUnit Team
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of 
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package net.sourceforge.sqlunit.matchers; (3)

import java.util.Map;  (4)
import net.sourceforge.sqlunit.IErrorCodes;
import net.sourceforge.sqlunit.IMatcher;
import net.sourceforge.sqlunit.SQLUnitException;

/** (5)
 * The PercentageRangeMatcher is an implementation of the IMatcher interface
 * used to define rulesets for matching columns in SQLUnit. This matcher
 * will accept a percentage tolerance value and check to see that the target 
 * is within (+/-) tolerance percent of the source.
 * Arguments:
 * pc-tolerance : a percentage tolerance value.
 * @author Sujit Pal (spal@users.sourceforge.net)
 * @version $Revision: 1.100 $
 */
public class PercentageRangeMatcher implements IMatcher { (6)

    /**
     * Default constructor as per contract with IMatcher.
     */
    public PercentageRangeMatcher() {;}

    /**
     * Returns true if the value of the target is withing (+/-) a specified
     * tolerance value of the source. Note that in this case, the source,
     * target and tolerance must all be numeric values.
     * @param source the String representing the source to be matched.
     * @param target the String representing the target to be matched.
     * @param args a Map of name value pairs of arguments passed in.
     */
    public boolean isEqual(String source, String target, Map args)
            throws SQLUnitException { (7)

        String aTolerance = (String) args.get("pc-tolerance"); (8)
        if (aTolerance == null) {
            throw new SQLUnitException(IErrorCodes.MATCHER_EXCEPTION,
                new String[] {this.getClass().getName(),
                "Value for key 'pc-tolerance' is NULL"});
        }
        // is tolerance a float?
        float iTolerance = 0;
        try {
            iTolerance = Float.parseFloat(aTolerance);
        } catch (NumberFormatException e) {
            throw new SQLUnitException(IErrorCodes.MATCHER_EXCEPTION,
                new String[] {this.getClass().getName(),
                "Value of key 'pc-tolerance' is not a FLOAT"});
        }
        // cannot have the tolerance exceed 100
        if (iTolerance > 100.0) {
            throw new SQLUnitException(IErrorCodes.MATCHER_EXCEPTION,
                new String[] {this.getClass().getName(),
                "Value of key 'pc-tolerance' must be between 0 and 100"});
        }
        // is the source a float? (9)
        float iSource = 0;
        try {
            iSource = Float.parseFloat(source);
        } catch (NumberFormatException e) {
            throw new SQLUnitException(IErrorCodes.MATCHER_EXCEPTION,
                new String[] {this.getClass().getName(),
                "Value of 'source' is not a FLOAT"});
        }
        // is the target an integer?
        float iTarget = 0;
        try {
            iTarget = Float.parseFloat(target);
        } catch (NumberFormatException e) {
            throw new SQLUnitException(IErrorCodes.MATCHER_EXCEPTION,
                new String[] {this.getClass().getName(),
                "Value of 'target' is not a FLOAT"});
        }
        // return the match (10)
        return ((iTarget >= (iSource - (iTolerance * 100))) &&
            (iTarget <= (iSource + (iTolerance * 100))));
    }
}
      

Setting up your Matcher for use with SQLUnit

This can best be explained by use of an example. Suppose you to specify a particular Matcher, com.mycompany.sqlunit.matchers.MyMatcher to work on the first resultset, all rows, and columns 1, columns 3 to 5 and column 7, you would embed the Match element within your Diff Element as shown below. In addition, if your matcher needed some extra information, you can supply them as a set of key-value pairs using nested Arg elements within the Match element. In our example below, the Matcher looks for the variable key1 with a value value1 in order to do the matching.

<diff name="MyDiff">
  <match resultset-id="1" row-id="*" col-id="1,3-5,7"
    matcher="com.mycompany.sqlunit.matchers.MyMatcher">
    <arg name="key1" value="value1" />
  </match>
</diff>
        
        

The above example illustrates some rudimentary wildcarding features that SQLUnit supports in order to specify more than one column for a particular matching strategy. Any columns that are not covered by the wildcarded filters default to the default matching strategy, which is to check for exact equality. The rules for wildcarding are the same for the resultset-id, row-id and col-id attributes and are detailed below. SQLUnit decides which columns qualify by checking the patterns for resultset-id, row-id and col-id additively.

What is User-Defined Reporting?

Starting with version 3.6, SQLUnit has the ability to use reporters that work within the context of other testing frameworks. By default, SQLUnit uses the TextReporter, which can be used to print a text report of the results of the test, either to a file specified in the sqlunit task's logfile attribute, or to STDERR if the logfile attribute is not specified. This functionality was contributed by Rob Nielsen and Paul King. They have also contributed a reporter that works within the context of the Canoo Web Test framework.

If you have written a Reporter that does something unique, and would like to contribute it to the SQLUnit project, it would be gratefully accepted and credit given.

List of currently supported Reporters

Extending SQLUnit: Writing your own Reporter

To add a new User-defined reporter, simply implement the IReporter interface and add an entry into etc/reporters.properties, and recompile the distribution. The interface specifies a template with methods to be implemented that are then called by SQLUnit in a predetermined manner in the reporting lifecycle. The Javadocs provide guidance as to what point in the lifecycle that method would be called by SQLUnit, so the implementation would have to code the required behavior. Take a look at the CanooWebTestReporter and TextReporter for more details. There is also a hasContainer() method which indicates if the reporter is running within the context of another test framework or not. If it is running in the context of another test framework, then SQLUnit will not attempt to set up the Ant BuildListener objects for it.

Setting up your Reporter for use with SQLUnit

SQLUnit currently comes with the default TextReporter and the CanooWebTestReporter. To use either of these, specify the logformat attribute of the sqlunit task to be the alias for the reporter of your choice. The aliases can be found in the reporters.properties file in the etc subdirectory of the distribution. Here is an example of using the CanooWebTestReporter.

<sqlunit testfile="mytest.xml" haltOnFailure="false" debug="false"
    logfile="./output.log" logformat="canoo" />
        
        

What are User-Defined Test Groups

Starting with version 4.9, SQLUnit provides to ability to classify the tests according to some user-defined criteria. One example for classification may be by test importance (severity) or by the part of the system (category) it applies to or both. This gives the ability to execute for example only tests that applies to ModuleA and with the highest priority.

Declaring the group of a test

All the grouping information of a given test is declared in <classifiers> tag. It is called in this way, because it classfies the test. One concrete example is:

  <test name="testName">
    <classifiers>
      <severity>WARN</severity>
      <category>TestForModuleA</category>
    </classifiers>
    <sql>
      <stmt>aSimpleResult>/stmt>
    </sql>
    <result>
      <resultset id="1"gt;
        <row id="1">
          <col id="1" name="col1" type="INTEGER">1</col>
        </row>
      </resultset>
    </result>
  </test>
      

This means that this test belongs to TestForModuleA category and its severity is WARN, so that if it fails it will be just a warning and not a error or a fatal situation.

The user then should provide some global criteria and each test's classification will be compared. If the test matches them it will be executed; if not will be skipped and reported as skipped. Here is a detailed explantion how the decision whether a test matches ot not is done. When <classifiers> tag is reached, its corresponding ClassifiersHandler is called, whose in turn calls the handlers of all its children. It is responsibility of the children's handler to extract the criterion they are interested in from the environment and then to compare whether their content meets that criterion. If it is not met they return false (as a Boolean object) to ClassifiersHandler. On the other hand, the latter stops its execution on the first false value it receives and the test is not executed.

For a concrete example how the user defines these global criteria see the next two sections.

SQLUnit comes with two classifying handlers (but you can provide your own too), which are SeverityHandler and CategoryHandler and the next two sections are dedicated to them.

SeverityHandler

SeverityHandler corresponds to <severity> tag, nested in <classifiers> tag. As shown in the example of the previous section the body of <severity> tag contains the severity of the test. Now the user gives the global severity, which we will call the threshold severity in one of the following way: as an ant property called sqlunit.threshold.severity, as the value of the key sqlunit.threshold.severity from SQLUnit's SymbolTable or as a System property called sqlunit.threshold.severity. SeverityHandler will take the threshold severity, the current test's severity and will execute the test iff the current test's severity is equal ot greater than the threshold severity.

The following values are valid severity value: DEBUG, INFO, WARN, ERROR, FATAL and are compared in the following way: DEBUG < INFO < WARN < ERROR < FATAL. For example if the user invokes the test from the previous section with

ant -Dsqlunit.severity.threshold=INFO
      

the test will be executed because INFO <= WARN. But if it is invoked with

ant -Dsqlunit.severity.threshold=ERROR
      

the test will not be executed as ERROR > WARN.

Since <severity> is an optional tag, the following edge cases are taken into account: if the threshold severity is not set all tests match regardless of their severities; if a test does not have assigned severity it matches, regardless of the threshold severity.

CategoryHandler

CategoryHandler corresponds to <category> tag, nested in <classifiers> tag. As shown in the example of the previous section the body of <category> tag contains the category of the test. Now the user gives the global category in one of the following ways: as an ant property called sqlunit.category, as the value of the key sqlunit.category from SQLUnit's SymbolTable or as a System property called sqlunit.category. CategoryHandler will take the global category, the current test's category and will execute the test iff they match in the terms of regular expression matching. For example if the user invokes the test from the previous section with

ant -Dsqlunit.category=TestForModuleA
      

the test will be executed. If it is invoked with

ant -Dsqlunit.category=TestForModuleB
      

the test will not be executed.

Since <category> is an optional tag, the following edge cases are take into account: if the global category is not set all tests match regardless of their category; if a test does not have assigned category it matches regardless of the global category.

The global category can be given as regular expression pattern also:

ant -Dsqlunit.category=TestForModule(.*)

Extending SQLUnit: Write your own classification

If you want to classify your tests not only by severity and category, but by other criteria, you have just to add your own handler as it described in the section Writing your handler. Note that in the case of classifying handlers, their process method should always return to ClassifiersHandler a Boolean object. Except the fact that it must receive a Boolean from its "children" the ClassifiersHandler knows nothing about the handlers it calls, thus making it more robust. If you return to it an object of another type, you will receive ClassCastException.

Skipping Tests with <skip>

Sometimes you may want to skip deliberately some test (regardless whether they match some criteria like the ones above or not). SQLUnit provides skip tag which is used in this way:

  <test name="Testing with skip=true and reason">
    <skip value="true">Reason for skipping</skip>
    <call>
      <stmt>aSimpleResult</stmt>
    </call>
    <result>
      <resultset id="1">
        <row id="1">
          <col id="1" name="col1" type="INTEGER">1</col>
        </row>
      </resultset>
    </result>
  </test>
      

The content of skip tag value attribute specifies whether the test will be skipped or not. If it is true the test is skipped and the reason for its skipping is given in the body of <skip> tag.

Overview

The SQLUnit Mock Testing Framework is based on the JDBC mock testing framework provided by the MockRunner Project, and allows a developer to test the SQLUnit code without using a database. Its primary value would be to model databases to which members of the SQLUnit development team do not have access.

Instead of a real database, the testing framework relies on introspecting a class specified in the JDBC URL. This class must implement the IMockDatabase interface and contains one or more methods with the following signature:

public MockResultSet methodName(Integer resultSetId);
      

The actual implementation of IMockDatabase is specified in the connection parameters in the XML test file. In the example below, the "jdbc:mock" string is used by the DriverManager to select the SQLUnitMockDriver to handle the Connection, and the rest of the URL is used to identify the class name which will be used to supply the MockResultSets needed for the tests.

<connection>
  <driver>net.sourceforge.sqlunit.test.mock.SQLUnitMockDriver</driver>
  <url>jdbc:mock:net.sourceforge.sqlunit.test.mock.SQLUnitMockDatabase</url>
  <user />
  <password />
</connection>

      

The AbstracMockDatabase abstract class provides an introspecting implementation of the IMockDatabase#getResultSet() method. It is an abstract class, so it is expected that someone implementing a Mock Database using this mechanism would extend this class. This is the case with the SQLUnitMockDatabase class, which contains various methods that model mock stored procedure calls.

Writing your own mock procedures

The SQLUnitMockDatabase provides various examples of mock procedures written using the SQLUnit Mock framework, but there are a number of conventions which you should be aware of when reading through these examples.

1. Each mock procedure will need to return various resultsets based on the resultset id supplied. Calls made to the same procedure with different values of the resultset id will result in different MockResultSet objects being returned.

2. ResultSet 0 is always the count of the number of resultsets that can be returned from this procedure call, embedded in a MockResultSet object.

3. ResultSet 1 through n represents the first through the n-th ResultSet objects that may be returned from the procedure.

4. ResultSet -1 through -n represents the first through n-th Outparam objects that may be returned from the procedure. They need to be always specified as Strings, they will be automatically converted based on the Outparam definition supplied in the param element. Oracle CURSOR outparams are returned as MockResultSets.

5. Throwing of SQLExceptions can be simulated by wrapping a SQLException within a MockResultSet at a particular ResultSet position.

6. MockResultSetUtils contains most of the functionality of wrapping and unwrapping non-ResultSet objects into and out of MockResultSets. Consider adding functionality to this class if you do not see something you want.

Here is an example of a mock procedure that returns a single return code (OUTPARAM 1) and a single resultset consisting of 1 row with 3 columns. As described above, resultset index of -1 corresponds to the return code at outparam position 1, resultset index of 0 corresponds to the number of resultsets returned from the procedure, and resultset index of 1 corresponds to the first resultset returned from the procedure.

/**
 * Returns a result code and a single resultset.
 * @param index the result set id.
 * @return a MockResultSet at the specified index.
 */
public MockResultSet resultAndOneResultSet(Integer index) {
    if (index == null) { return null; }
    int rsid = index.intValue();
    switch (rsid) {
        case -1:
            return MockResultSetUtils.buildScalarOutParam("143");
        case 0:
            return MockResultSetUtils.buildZerothResultSet(1);
        case 1:
            MockResultSet mrs = new MockResultSet("resultAnd1ResultSet:-1");
            mrs.setResultSetMetaData(MockResultSetUtils.buildMetaData(
                new ColumnMetaData[] {
                new ColumnMetaData("agentId", Types.INTEGER),
                new ColumnMetaData("name", Types.VARCHAR),
                new ColumnMetaData("drink", Types.VARCHAR)}));
            mrs.addRow(new Object[] {
                new Integer(7), new String("James Bond"),
                new String("Martini")});
            return mrs;
        default:
            return null;
    }
}
      

More information on how the various peices fit together from a programmatic perspective can be found in the package level documentation in the Javadocs for this package.

Writing tests against the mock procedures

The process of writing tests against the mock procedure is identical to writing it for a real database. You can find some examples under test/mock/test.xml in the distribution. Here is one of them, which corresponds to the code shown above for the mock procedure.

<test name="Testing result code and one resultset">
  <call>
    <stmt>resultAndOneResultSet</stmt>
    <param id="1" type="INTEGER" inout="out">${rc}</param>
  </call>
  <result>
    <outparam id="1" type="INTEGER">143</outparam>
    <resultset id="1">
      <row id="1">
        <col id="1" type="INTEGER">7</col>
        <col id="2" type="VARCHAR">James Bond</col>
        <col id="3" type="VARCHAR">Martini</col>
      </row>
    </resultset>
  </result>
</test>