Qute – new Unit Testing Engine for PL/SQL (successor for utPLSQL)

Steven Feuerstein and his team bring the world a new gift: Qute – the Quick Unit Test Engine.
Steven says: “Qute is the Quick Unit Test Engine. It is a tool for
defining unit tests, generating test code, and running those tests, all
within a graphical interface.”  The ultimate objective of Qute is quite
simple to state: allow you to define and run your tests without having
to write any code: purely declarative specification of tests.” And:
“Qute will help you unit test your programs more quickly, easily and
effectively. Using Qute, you can define your tests through a graphical
interface and then generate and run those tests. Results are visible
through the graphical interface.”

The first pre-release version of Qute, the Quick Unit Test Engine, is now
available for downloading. Simply visit http://www.unit-test.com/download.php.
Note the Qute is free, but not open source. I do not know what the
intentions for the future are, but right now you can download, use it
and be happy but under a license that states: Subject to the terms
and conditions of this Agreement, PL/Solutions, Inc., grants you a
non-exclusive, revocable, limited license, to (a) download and install
the Software; and (b) use the Software you download and install.

Personally I am happy with more attention for Unit Testing
PL/SQL programs and especially with an easier to use framework to do
so. While I was perfectly happy to use utPLSQL, it was not integrated
into any IDE nor did it provide a good user interface. It can be
plugged into automated build systems, using Ant or TransferWare, which
is a good thing. The GUI that Steven Feuerstein provided for utPLSQL,
called OUnit, for some reason never really worked very well for me. So
I am anxious to find out what Qute can do for us. Using JUnit is made
that much more attractive to developers when working with IDEs or tools
that allow easy creation of test-cases and GUI driven execution of
tests and reporting of feedback. If that is wat Qute does, it will make
it much easier to persuade developers to actually start Unit Testing
PL/SQL code.

About utPLSQL: “Qute is also compatible with utPLSQL,
the open source framework for unit testing PL/SQL – Original developed
by Steven Feuerstein, it is now maintained and enhanced by a team of
developers.  You do not, however, need to install utPLSQL to use Qut.
In fact, Qute improves upon utPLSQL in a variety of ways.” It seems that Steven Feuerstein is letting go of utPLSQL in favor or Qute; from the Qute Help document:”utPLSQL
offers a standardized process for writing and running test packages.
Several people have also written GUI interfaces to the underlying
utPLSQL APIs. Qute is intended to go well beyond utPLSQL in test code
definition and generation. By using Qute, you can avoid writing much of
the code you would have to write manually with utPLSQL. In addition,
Qute offers an expanded set of assertions (programs to determine if the
test outcomes were as expected) and better results storage and
reporting. You can view and run your utPLSQL test packages from within
Qute, but Qute does not currently generate utPLSQL-compatible packages.
Qute also does not support utPLSQL test suites.”

Getting Started with Qute – Installation

The
Download is about 3.5 Mb. Installation is started by running the
QuteInstall.exe installer. After accepting the License Agreement, we
are informed of the requirements:

• we need an Oracle 9iR2 or higher database,
• a user schema with basic privileges (SCOTT will do),
• Windows 98, Windows 2000 or Windows XP to run the front end
• Oracle
  client software must be installed – that is a pity; running through a
  JDBC driver that does not need any further client installs is that much
  nicer, such as Raptor does; however, most Oracle developers will
  probably have an Oracle client so it is not a real pain.

The
Client Side installation takes all of 15 seconds. Next a window is
displayed that allows us to install the server side for Qute:

In
this case I have chosen to create a new schema that will be used as a
shared Qute instance – available to all developers in my database. The
next step: provide details for a database user with DBA privileges.
Next is specifying details for the schema to be created for installing Qute:

And
the installation is kicked off. Qute keeps us nicely informed of what
it is doing. This server side installation took about three minutes on
my laptop.

When done, the Finish window is displayed. Now it is time to get really started.

Qute
comes with its own help system (about 250 Kb worth of help). At the
very least, the structure of the help system looks promising:

Getting Started with Qute – Running Qute

Steven Feuerstein makes it very clear in the email announcing this pre-release version of Qute: I
am very happy and proud to inform you that the first pre-release
version of Qute, the Quick Unit Test Engine, is now available for
downloading. Qute is far from complete and far from fully implementing
my vision of 100% declarative specification of unit test definitions
(that is, no coding needed to test your own programs). Still, I am
confident that you will find it to be by far the best path to designing
and running tests of your PL/SQL code.

Let’s see where Qute currently stands and where it seems to be headed.

The opening screen of Qute looks like this:

I had to get used to what is displayed here, but it is
really quite simple. To add a new Package to be tested, you click on
Define a New test for your program or you to go the Test Harnesses menu
option and create a new Test Harness. This window pops up. It invites
us to select the schema that owns the Package we want to create a Test
Harness for after which we can select the package itself. We can set
some generation options for the Test Harness (itself a package).

In
this case, I have selected a package called APP_ARITHMETHIC. I have
used this package in demonstrations with utPLSQL in the past. In those
examples – see for example the paper The
Best of Both Worlds Crossbreeding Java and PL/SQL
for more background – I had to hand-code (most of) the test package
UT_APP_ARITHMETIC. This time, I will use Qute to do the hard work for
me.

The next step in the definition of the new Test Harness:

Qute
has analyzed the package that I want tested. It has found several
public functions that are candidates for Unit Testing. It now allows us
to very easily define those test cases by providing a name, the input
values and the expect output value. Since I know that the add function
was designed to deal with NULLs in an intuitive way – simply do not add
any NULL input, treat it as if it were 0 – I have defined test cases
with various combinations of actual values with NULLs. Two NULLs as
input for example should yield 0 (zero) as outcome for this function.

Qute
also provides functionality that generates Test Cases: a list of useful
input values – for which of course we have to provide the expected
results ourselves:

I
have removed a number of these generated test cases, as they somewhat
cloud the picture. But this feature can be very useful indeed! It uses
combinations of typically tricky values: NULL, -1, 0, 1 in this case. I
have not yet tried out this feature for VARCHAR2 and DATE input; I hope
this same sort of smart generated values is available.

Upon clicking Next, we get to this screen:

Now
we can go forward and generate the code for the Test Harness package.
We can also immediately run the tests for the APP_ARITHMETIC package:

The
code for the Test Harness is generated to the local file system. It is
also loaded into the database, in the qute schema as I was connected as
Qute in this case.

The first tab-sheet displays the overall properties for this TestHarness – it also allows us to re-run the test:

The
Test Code Tabsheet shows us the generated TestHarness itself (and
allows us to edit it as well). For those of you who have worked with
utPLSQL, this code will look both familiar and impressive for being
generated (Qute does a lot of work for us). The code is shown at the
end of this article.

Qute provides us with a lot of hooks
and options for customizing the Test Cases. We can specify code to be
executed upon SetUp and TearDown of the Unit Test and at many different
moments in the life cycle for a TestCase, for example during
initialization, cleanup or even execution of the individual TestCase.

We
also have a substantial set of assertion-types for the TestCase. By
default we will compare the expected value to the outcome of the
function call – on equality of the two -, but there are many other
assertions we can make, like checking for the occurrence of a certain
exception or whether the function result is greater than or smaller
than the expected value.

The
code generated for the TestHarness – and I have stripped out all
TestCases except the Two_nulls TestCase for brevity’s sake – is the
following:

CREATE OR REPLACE PACKAGE BODY QUTE_APP_ARITHMETIC
/*
| Unit Test Package for APP_ARITHMETIC
|
| Generated by Qute -- the Quick Unit Test Engine (www.unit-test.com)
| Generated on 2006-02-02 12:26:22
| This package must be run from within the Qute graphical interface.
*/
IS
FUNCTION qut#error_info RETURN VARCHAR2 IS
   l_return VARCHAR2(32767);
   l_stack VARCHAR2(32767);
BEGIN
   l_return := DBMS_UTILITY.FORMAT_ERROR_STACK;
   BEGIN
      EXECUTE IMMEDIATE
        'BEGIN :val := ''PL/SQL Error Backtrace: ''
             || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE; END;'
      USING OUT l_stack;
   EXCEPTION
      WHEN OTHERS THEN l_stack := DBMS_UTILITY.FORMAT_CALL_STACK;
   END;
   RETURN l_return || CHR(10) || l_stack;
END qut#error_info;
 
   PROCEDURE QUTE_setup IS
   BEGIN
      -- Qute START CUSTOM SETUP for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
      -- Place all your custom initialization code between these START and END comments.

      -- Qute END CUSTOM SETUP  for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
      NULL; -- Included to ensure that this procedure will compile if empty.
   END QUTE_setup;

   PROCEDURE QUTE_teardown IS
   BEGIN
   -- Qute START CUSTOM TEARDOWN for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
   -- Place all your custom cleanup code between these START and END comments.

   -- Qute END CUSTOM TEARDOWN for HARNESS {1D39C6D3-1576-4F34-93A0-AC2A235482CA}
      NULL; -- Included to ensure that this procedure will compile if empty.
   END QUTE_teardown;

   PROCEDURE QUTE_ADD
   -- Unit test procedure for ADD
   IS
       
      PROCEDURE Two_nulls
      IS
      -- Return value of function
      "Value From Function" NUMBER;
      "Expected Value From Function" NUMBER;
         -- Qute START CUSTOM DECLARATIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
         -- Place all your custom declaration code between these START and END comments.
         
         -- Qute END CUSTOM DECLARATIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}

         PROCEDURE setup IS
         BEGIN
            -- Qute START CUSTOM SETUP for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom setup code between these START and END comments.

            -- Qute END CUSTOM SETUP for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
             
            NULL; -- Included to ensure that this procedure will compile if empty.
         END setup;

         PROCEDURE set_in_values IS
         BEGIN 
            NULL; -- Included to ensure that this procedure will compile if empty.
         END set_in_values;

         PROCEDURE set_expected_values IS
         BEGIN 
            NULL; -- Included to ensure that this procedure will compile if empty.
         END set_expected_values;

         PROCEDURE call_the_program IS
         BEGIN
            -- Qute START CUSTOM PRE-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom pre-execution code between these START and END comments.

            -- Qute END CUSTOM PRE-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            "Value From Function" :=
            OSP.APP_ARITHMETIC.ADD(
                  A => NULL
                  ,B => NULL
            );
            -- Qute START CUSTOM POST-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom post-execution code between these START and END comments.
            
            -- Qute END CUSTOM POST-EXECUTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            EXCEPTION
            WHEN OTHERS THEN 
               qu_assert.report_result('{F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}', 'RUNTIME-ERROR', qut#error_info);
         END call_the_program;

         PROCEDURE check_out_args_and_return IS
         BEGIN
            qu_assert.compare_two_values(
                outcome_guid_in => '{12BF1366-9085-4EEC-A9BA-E052C65E0C66}'
               ,operator_in => 'EQ'
               ,check_this_in => "Value From Function"
               ,against_this_in => 0
               ,raise_exc_in => FALSE
               ,null_ok_in => TRUE
               ,truncate_in => FALSE
               );
 
             
            -- Qute START CUSTOM ASSERTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom assertion code between these START and END comments.
            
            -- Qute END CUSTOM ASSERTION for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            NULL; -- Included to ensure that this procedure will compile if empty.
         END check_out_args_and_return;

         PROCEDURE teardown IS
         BEGIN
          
            -- Qute START CUSTOM TEARDOWN for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            -- Place all your custom cleanup code between these START and END comments.
            
            -- Qute END CUSTOM TEARDOWN for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
            NULL; -- Included to ensure that this procedure will compile if empty.
         END teardown;

      BEGIN
         setup;
          
         set_in_values;
         set_expected_values;
         call_the_program;
         check_out_args_and_return;
          
         teardown;

         -- Qute START CUSTOM EXCEPTIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
         -- Place all your custom EXCEPTION code between these START and END comments.
         
         -- Qute END CUSTOM EXCEPTIONS for TESTCASE {F361D9F2-C1AA-4DA7-B44B-88C173E4A87A}
      END Two_nulls;
       
   BEGIN
      Two_nulls;
   END QUTE_ADD;