[Db-derby Wiki] Update of "ConvertSqlScriptTestsToJunit" by DanDebrunner

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by DanDebrunner:
http://wiki.apache.org/db-derby/ConvertSqlScriptTestsToJunit

New page:
= 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.