Converting SQL script Tests to JUnit

Notes on how to easily convert SQL scripts to running under JUnit using .org.apache.derbyTesting.functionTests.util.ScriptTestCase.
ScriptTestCase runs a SQL script against a database and compares the output to a single master file (canon), thus its mode of execution is very similar to running the SQL script under the old harness..

Is the test suitable?

If the test has any of these attributes then most likely it is not suitable for conversion as a SQL script and should instead be converted to a test case using JDBC and BaseJDBCTestCase.

  • Has multiple master files - ScriptTestCase is intended to be simple to maintain in the future so does not support multiple master files.
  • Relies (really relies) on the sed function of the old harness. ScriptTestCase does not support sed'ing as I believe sed'ing has the potential to hide bugs, seen possibly in the bugs in converting time values since all time values were sed'ed out. Note that some of the current sed'ing of output is not required and this should not stop a test being converted and run with ScriptTestCase
  • Test displays current time or date values

Examples

See LangScripts and NistScripts in the lang and nist functional test packages.

Converting

Language Tests

Run the script using LangScripts, e..g to run the script insert.sql execute

java org.apache.derbyTesting.functionTests.tests.lang.LangScripts insert

Other Tests

If the package does not contain a JUnit test that extends ScriptTestCase then create one following the examples. Run the script as a JUnit test and proceed to checking the output.

Checking the Output

Test Passes!

If you are lucky then the test passed which means the output under ScriptTestCase matched the output under the old harness. The test is ready to be added to JUnit, ensure that it is run by the _Suite suite in the same package, remove the test from the old harness suite files and commit or submit the patch.

Test Fails

The test will fail at the first line in the output that does not match the canon. The output from the test is written as a .out
file with the name of script under the fail folder. It may be a couple of levels deep, depending on the extension of ScriptTestCase. For example running the insert test with LangScripts resulted in the output file in fail/Embedded/LangScripts/insert/insert.out. Compare this file with the master file from java/testing/org/apache/derbyTesting/functionTests/master using diff or similar utility. Possible reasons for the failure are:

  • Blank lines in output file, not in master file. Not a problem, the old harness ignored blank lines due to having to perform sed.
  • WARNING 02000: No row was found for ... lines in output. Not a problem, the old harness sed these lines out for no good reason (most likely by the person who added this warning to avoid having to modify many master files).
  • xxxxxxFILTERED-TIMESTAMPxxxxx replaced with constant date/time output. Not a problem, unless the time display is based upon the current time or date. The old harness sed'ed every time value, not just those with the current time.

If the diff only contains the above items then you can copy the output into the master file folder and re-run the test to see it pass. Commit or submit the patch!

If the output contains any of these diffs then some change is needed or the test may be unsuitable to run using ScriptTestCase:

  • xxxxGENERATED-IDxxxx replaced with generated constraint names - Consider explicitly naming constraints in the SQL script files and adjusting the output to reflect those fixed names.
  • xxxxxxFILTERED-TIMESTAMPxxxxx replaced current time or date values - Re-write the tests cases using JDBC, see lang.TimeHandlingTest.
  • Unable to load resource - need to investigate what is causing this.
  • No labels