#100CodeExamples – utPLSQL’s Suite Hierarchy in action
Tags: 100codeexamples, plsql, utplsql, testing
Published: 2018-12-17 09:11:46

---
title: #100CodeExamples – utPLSQL’s Suite Hierarchy in action
published: true
tags: #100CodeExamples,PL/SQL,utPLSQL,Testing
canonical_url: https://cleandatabase.wordpress.com/2018/12/16/100codeexamples-utplsqls-suite-hierarchy-in-action/
---

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.

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

```sql
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

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

```sql
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](https://github.com/pesse/sith-demo-db/blob/master/100CodeExamples/4_utplsql_test_hierarchy.sql).

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