utPLSQL gives you the possibility to organize test-suites in a hierarchical way. We can use this for collecting setup-/cleanup-methods into separate parent-packages.

We can not only outsource our setup-methods into a single place (following DRY principle), but we can also make clear that several test-suites are somehow connected.

create table planets (
  id integer primary key,
  name varchar(256)
);

create or replace package ut_planet_setup as
  -- %suite(planetSetup)
  -- %suitepath(galaxy)

  -- %beforeall
  procedure setup_test_planet;
end;
/

create or replace package body ut_planet_setup as
  procedure setup_test_planet
  as
    begin
      insert into planets ( id, name )
        values ( -1, 'Korriban');
    end;
end;
/

create or replace package ut_planets as
  -- %suite(Planets)
  /* Hierarchic Suitepath must contain any parent suitepaths
     and the name of the parent test-package */
  -- %suitepath(galaxy.ut_planet_setup)

  -- %test(Check if test-plantes exist: 1 planet)
  procedure test_planets_exist;
end;
/

create or replace package body ut_planets as
  procedure test_planets_exist
  as
    l_count int;
    begin
      select count(*) into l_count from planets where id < 0;
      ut.expect(l_count).to_equal(1);
    end;
end;
/

create or replace package ut_garrisons as
  -- %suite(Garrisons)
  -- %suitepath(galaxy.ut_planet_setup)

  -- %beforeall
  procedure setup_another_test_planet;

  -- %test(Check if test-plantes exist: 2 planets)
  procedure test_planets_exist;

  /* We could add some more tests for Planet-Garrisons here */
end;
/

create or replace package body ut_garrisons as
  procedure setup_another_test_planet
  as
    begin
      insert into planets ( id, name )
        values (-2, 'Dromund Kaas');
    end;

  procedure test_planets_exist
  as
    l_count int;
    begin
      select count(*) into l_count from planets where id < 0;
      ut.expect(l_count).to_equal(2);
    end;
end;
/

When we run the suite by its suitepath, the parent suite-name is shown with its displayname but has to be called by its package-name. A beginning ":" means we search for a suitepath:

call ut.run(':galaxy.ut_planet_setup');

 

galaxy
   planetSetup
     Planets
       Check if test-plantes exist: 1 planet [,011 sec]
     Garrisons
       Check if test-plantes exist: 2 planets (,003 sec)

 Finished in ,020764 seconds
 2 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

If we call it by package-name only, utPLSQL doesnt run the child suites

call ut.run('ut_planet_setup');

 

galaxy
   planetSetup

 Finished in ,03545 seconds
 0 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

But we can call it with either of the specific test suites names for a partial test:

call ut.run('ut_planets');
call ut.run('ut_garrisons');

 

galaxy
   planetSetup
     Planets
      Check if test-plantes exist: 1 planet (,001 sec)

 Finished in ,02547 seconds
 1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

galaxy
   planetSetup
     Garrisons
      Check if test-plantes exist: 2 planet (,001 sec)

 Finished in ,01228 seconds
 1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

You can find a full example at github.

Why I learned this

I try to clean up my test codebase and

  1. Avoid duplication where possible
  2. Give some context about dependencies between topics/modules
  3. Reduce the time my tests need to run

2 Comments

Pradeep · February 27, 2020 at 2:07 am

Similar to child procedure which runs beforeall, can we use afterall procedure? I tried using afterall procedure in child package but that is not waiting for my testcases.

    Pesse · July 20, 2020 at 3:59 pm

    Hey Pradeep – sorry, totally missed your comment. Can you give me a bit more information what you’re trying to achieve? `–%afterall` is definitely an existing annotation that works.

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.