Skip to content
This repository has been archived by the owner on Aug 1, 2019. It is now read-only.

UnitTest

Jump to bottom
Izzy edited this page Jan 29, 2018 · 1 revision

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 among 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

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 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 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 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:

                <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

                <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!
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

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:

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 codegenerator with HyperSQL!

Example output for our above foo() function you can find with the PLSQL_example below. 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 …

PLSQL Example

Example output of the demo PL/SQL UnitTest generator:

-- ------------------------------------------------------------------
-- Testsuite my_package
-- ------------------------------------------------------------------

CREATE OR REPLACE PACKAGE test_my_package AS
  PROCEDURE test_run_all;
  PROCEDURE foo_bar_five;
END test_my_package;
/

CREATE OR REPLACE PACKAGE BODY test_my_package AS

  --
  -- Helpers to report our UnitTest results
  --
  success NUMBER := 0;
  failed  NUMBER := 0;
  errors  NUMBER := 0;
  deferr  NUMBER := 0;
  PROCEDURE report_unit_start(name IN VARCHAR2) IS
    BEGIN
        dbms_output.put_line('UNITTEST START FOR '||name);
    END report_unit_start;
  PROCEDURE report_unit_end(name IN VARCHAR2) IS
    BEGIN
        dbms_output.put_line('RESULTS FOR UNITTEST '||name||': ');
        dbms_output.put_line('Definition errors: '||deferr||', Errors: '||errors||', Failed: '||failed||', OK: '||success);
    END report_unit_end;
  PROCEDURE report_definition_error (obj IN VARCHAR2, msg IN VARCHAR2 DEFAULT '') IS
    BEGIN
        dbms_output.put_line('"'||obj||'" could not been tested due to invalid test specification.');
        IF msg != '' THEN
            dbms_output.put_line('Details: '||msg);
        END IF;
        deferr := deferr +1;
    END report_definition_error;
  PROCEDURE report_failure (obj IN VARCHAR2, msg IN VARCHAR2 DEFAULT '') IS
    BEGIN
        IF msg = '' THEN
            dbms_output.put_line('UnitTest for '||obj||' failed.');
        ELSE
            dbms_output.put_line(obj||': '||msg);
        END IF;
        failed := failed +1;
    END report_failure;
  PROCEDURE report_error (obj IN VARCHAR2,sqlerr IN VARCHAR2) IS
    BEGIN
        dbms_output.put_line('An error occured while testing "'||obj||'":');
        dbms_output.put_line(sqlerr);
        errors := errors +1;
    END report_error;
  PROCEDURE report_success(obj IN VARCHAR2) IS
    BEGIN
        success := success +1;
    END report_success;

  --
  -- The UnitTests themselves
  --
  PROCEDURE foo_bar_five IS
    retval VARCHAR2(4000);
    bar NUMBER := 5;
    BEGIN
      retval := my_package.foo(bar);
      IF retval = 'five' THEN
        NULL;
      ELSE
        report_failure('foo_bar_five','foo(5) did not return "five"!');
        RETURN;
      END IF;
      report_success('foo_bar_five');
    END foo_bar_five;
  PROCEDURE test_run_all IS
    BEGIN
      report_unit_start('my_package');
      foo_bar_five();
      report_unit_end('my_package');
    END test_run_all;
END test_my_package;
/
Clone this wiki locally