[[TOC(Documentation,Installation,Configuration,Usage,Usage/*,JavaDoc,UnitTest,SyntaxHighlighting,Localization, depth=3)]]

== Embedding unit-tests with JavaDoc ==
HyperSQL allows you to define Unit-Tests with your JavaDoc-style documentation.
The ideas behind this are:

 * defining them here makes it more likely they are kept up-to-date
 * providing an easy syntax makes it more likely developers will add/maintain them
 * exporting them to XML gives you the possibility to automatically create/run
   those tests using a programming language of your choice

Currently, I'm not aware of any openly available program processing those XML files,
but with some luck they may be provided in the future. I hereby strongly encourage
the developers amongst you to create them, and feed them back to this project, so
they may be included with future versions of HyperSQL or at least mentioned in its
documentation together with a link to your project, if you prefer that. The
following definitions should give you all required information - plus if you have
any questions left, I'll do my best to answer them.

=== Defining test-cases ===
This can be done in your JavaDoc style documentation, using the `@testcase` tag. It's content can be multi-line, but all
line-breaks are optional. First a basic example given the

{{{
#!sql
FUNCTION foo (bar IN NUMBER);
}}}

So here is some dummy JavaDoc for this function, just with the basic information to be explained here:

{{{
/**
 * @function foo
 * @param in number bar The foo bar description
 * @return varchar2
 * @testcase
 *     name    { foo_bar_five };
 *     comment { Converting 5 to a word should give us "five" };
 *     message { foo(5) did not return "five"! };
 *     param   { bar 5 };
 *     return  { = five };
 */
}}}

This should look quite intuitive, so now we can go for the formal description.
First, what is written there in a line we now call an "element", which has the
form "<name> { <value> };". <name> is always a WORD, <value> can be anything
depending on the <name>. Second: all elements are generally optional - which are
required depends on the "object" the @testcase is attached to (a procedure e.g.
has no return value) - but a testcase with no value to check makes no sense at
all, and will be silently ignored. Third, @testcase will only be evaluated for
functions and procedures (stand-alone or in packages).

So here comes the more formal syntax:

{{{
element := [ element ] name | comment | message | param | check | return | runsql

name    := "name" { word };
comment := "comment" { text };
message := "message" { text };
param   := "param" { param_name value };
check   := "check" | "check_param" | "checkparam" { param_name operator value };
return  := "return" | "check_return" | "checkreturn" { operator value };
runsql  := "presql" | "postsql" | "checksql" { text };

word    := [A-z_]+
text    := .+
param_name := word
value   := text
operator:= "<" | ">" | "=" | "!="
}}}

Note that the definition for "word" and "text" is given as regular expression:
The "+" at the end means it needs at least one character of the definition before,
[A-z_] means you can use only upper-case letters and the underscore. The "."
stands for any character - so even line breaks are permitted here. Terms in
double quotes are meant literally. Just compare it with the example above ;)

`presql` and `postsql` are corresponding to "setup" resp. "teardown" ''for this testcase only'' (other than JUnit 4 "before" and "after" which are used for ''each'' test case of the entire unit test) -- i.e. they are run immediately before resp. after the test itself. `checksql` is intended to allow you verify "void" procedures as well: this SQL statement should evaluate to "boolean" (e.g. `SELECT COUNT()` or `SELECT 1`).

=== The generated XML file ===
To activate the XML output now described, you must define a directory where the generated XML files are to be stored, plus tell ''HyperSQL'' explicitely to produce those files. See [wiki:Configuration] for details.

Output will be generated on a per-input-file basis (one output file per input file,
but only if the input file contains any testcases). So the name of the generated
XML file corresponds to the name of the input file (including some encoded
directory information, if the input file was located in a subdirectory).

Content starts with the XML declaration, followed by one <UNITTEST> element
serving as our "root". This "root" contains one or more TESTSUITEs, representing
either a database package or a collection of all stand-alone functions and
procedures of the input file. Each TESTSUITE holds one or more OBJECTs, where
each OBJECT represents a single procedure or function and contains its
SIGNATURE and TESTCASEs.

Again, all this is best explained given a basic example, using our above foo() function:

{{{
#!xml
<?xml version="1.0" encoding="utf-8"?>
<UNITTEST>
    <TESTSUITE TYPE="package" NAME="my_package">
        <OBJECT TYPE="function" NAME="foo">
            <SIGNATURE>
                <NAME>foo</NAME>
                <PARAM TYPE="in" DATATYPE="number" NAME="bar" INDEX="0" OPTIONAL="FALSE" />
                <RET>varchar2</RET>
            </SIGNATURE>
            <TESTCASE NAME="foo_bar_five">
                <COMMENT><![CDATA[Converting 5 to a word should give us "five"]]></COMMENT>
                <MESSAGE><![CDATA[foo(5) did not return "five"!]]></MESSAGE>
                <PARAM NAME="bar"><![CDATA[5]]></PARAM>
                <RET OP="="><![CDATA[five]]></RET>
            </TESTCASE>
        </OBJECT>
    </TESTSUITE>
</UNITTEST>
}}}

The TESTCASE for a procedure would not have the RET child, but instead probably
some OUT Variable to check:

{{{
#!xml
                <CHECK NAME="my_out" OP="!="><![CDATA[bullshit]]></CHECK>
}}}

So there you've got all what's currently implemented. Admitted, this will not
allow for complex cases - but those may be supported by a later version (see below).

As for the `presql`, `postsql`, and `checksql` elements which are not covered by our example, we'd have something like

{{{
#!xml
                <PRESQL><![CDATA[INSERT INTO testtable VALUES (1,2)]]></PRESQL>
                <POSTSQL><![CDATA[DELETE FROM testtable]]></POSTSQL>
                <CHECKSQL>
                  <STATEMENT><![CDATA[SELECT COUNT(*) FROM testtable]]></STATEMENT>
                </CHECKSQL>
}}}

=== Generating the real code ===
'''''WARNING: The following is not well tested, so don't use it straight on your (production) database without having everything checked and tested out yourself![BR]You have been warned! '''''

More or less as a "proof of concept", you will find a script named `ut_plsql.py` in the `doc/` directory of this distribution. It will accept the name of a single XML file as input, generate PL/SQL unit-test code, and write it to STDOUT. Thus given a directory containing (only) all your generated XML files and this script, plus the script made executable (Unix/Linux: `chmod u+x ut_plsql.py`), on a Unix/Linux machine you could simply run

{{{
#!sh
for file in *.xml; do ./ut_plsql.py $file > ${file%*.xml}.sql; done
}}}

and afterwards find a corresponding *.sql file for each of your XML files. One more line afterwards:

{{{
#!sh
for file in *.sql; do sqlplus scott/tiger@orcl <$file; done
}}}

And you have the unit-test packages installed into your database. '''''Of course you should check the SQL files first!!! ''''' Never do such things blindly. Most important is to '''''make sure there are no existing packages in your database with the same name as one of those you are going to install...'''''

As said before, this is mostly a "proof of concept". It should however work for simple cases (feedback welcome); but you are strongly encouraged to write your own code generators (you may use this code to your convenience according to the terms of its license, the GPL). Use whatever language fits ''YOU'' best, and the generated code can reflect that as well. As author of ''HyperSQL'', I appreciate your reporting back, and will happily mention (and link to or even include) your code generator with ''HyperSQL''!

Example output for our above `foo()` function you can find on the [wiki:UnitTest/PLSQL_example] page. Note that this sample script does not (yet?) support the `presql`, `postsql`, and `checksql` elements.


=== Generated documentation ===
The generated documentation will show a presentation of defined test-cases together with the functions/procedures they belong to in "human readable" format.

=== Future Plans ===
Don't take them as already scheduled - but there are some ideas of what might
possibly added in the future.

You may already have noted the different notation for the `<CHECKSQL>` element, having the SQL statement as a child. This is intended for future enhancements: I think of an optional comparision to be given, such as `checksql { { [ operator value ] } sql-statement };` -- so statements do not need to explicitely be "booleanized", which would make certain definitions easier.

As said above, this is just some brainstorming - non of this is scheduled or
even implemented yet. But we have the possibilities to extend the features
already offered...