Testing the Deathstar security with utPLSQL contexts
Tags: 100codeexamples, utplsql, plsql, sql
Published: 2019-06-11 05:41:48

---
title: Testing the Deathstar security with utPLSQL contexts
published: true
tags: #100CodeExamples,utPLSQL,PL/SQL,SQL
canonical_url: https://cleandatabase.wordpress.com/2019/06/06/testing-the-deathstar-security-with-utplsql-contexts/
---

One of the really great features of utPLSQL are contexts. They allow to further organize tests inside a test suite (which can also be organized [hierarchically by suitepaths](https://dev.to/pesse/100codeexamples--utplsqls-suite-hierarchy-in-action-lk3)).

Contexts can help with several things:

- They can reduce the setup/teardown time (things can be done once per context instead of before every test)
- They can help to reveal the intention by grouping several tests
- They can be used to avoid duplication

For this example we build upon the active deathstar protocol which in our case controls how the security system reacts to an unknown, hooded person (you all know that hooded strangers on a deathstar are always undercover jedi).

```sql
create or replace package deathstar_security as
   
  /* This is just for making the setting a
     little bit more realistic. Implementation-wise
     we dont make a distinction whether a person
     is hooded (which is a sure sign for an
     undercover jedi) or not
   */
  type t_person is record (
    is_hooded boolean
    );
 
  /* Returns a welcome message based on the
     active Deathstar protocols DEFENSE_MODE
   */
  function welcome(i_person t_person)
    return varchar2;
 
  /* Checks whether access is allowed based on
     the active Deathstar protocols ALERT_MODE
   */
  function access_allowed( i_person t_person )
    return boolean;
end;
/
 
create or replace package body deathstar_security as
  /* Helper-function to get the active
     Deathstar protocol row
   */
  function active_protocol
    return deathstar_protocols%rowtype
  as
    l_result deathstar_protocols%rowtype;
  begin
    select p.* into l_result
      from deathstar_protocols p
        inner join deathstar_protocol_active pa
          on p.id = pa.id;
    return l_result;
  end;
 
  function welcome(i_person t_person)
    return varchar2
  as
    l_protocol deathstar_protocols%rowtype
      := active_protocol();
  begin
    case l_protocol.defense_mode
      when 'BE_KIND' then
        return 'Be welcome!';
      when 'BE_SUSPICIOUS' then
        return 'Are you a jedi?';
      when 'SHOOT_FIRST_ASK_LATER' then
        return 'Die rebel scum!';
      else
        raise_application_error(-20000, 'Ooops, no welcome');
    end case;
  end;
 
  function access_allowed( i_person t_person )
    return boolean
  as
    l_protocol deathstar_protocols%rowtype
      := active_protocol();
  begin
    case l_protocol.alert_level
      when 'LOW' then
        return true;
      when 'MEDIUM' then
        return false;
      when 'VERY HIGH' then
        raise_application_error(-20100,
          'Unauthorized attempt to access a public area');
      else
        raise_application_error(-20000, 'Oooops, no access');
    end case;
  end;
end;
/
```

We have the two methods `welcome` and `access_allowed` and depending on the active protocol the security system reacts differently.

To reduce setup-time (let’s assume setting the active protocol is a pretty costly action) and give the whole test-suite some structure we use the `%context` annotation and divide our test-suite into three different parts:

```sql
create or replace package ut_deathstar_security as
    -- %suite(Deathstar Security)
    -- %suitepath(deathstar.defense)
 
    /* This first beforeall is only issued
       once for the whole suite - which can be
       a massive speed boost
     */
    -- %beforeall
    procedure setup_test_protocols;
 
    /* Every context can have an identifier */
    -- %context(low)
        /* With the displayname-annotation we can also
           give a label */
        -- %displayname(Protocol: Low)
 
        /* This is only issued once in this context */
        -- %beforeall
        procedure setup_protocol_low;
 
        -- %test(Hooded Person gets a kind welcome message)
        procedure low_welcome_message;
 
        -- %test(Entry to public area is allowed)
        procedure low_entry_allowed;
    /* Every context needs to be closed */
    -- %endcontext
 
    -- %context(medium)
        -- %displayname(Protocol: Medium)
 
        -- %beforeall
        procedure setup_protocol_medium;
 
        -- %test(Hooded Person gets a suspicious welcome message)
        procedure medium_welcome_message;
 
        -- %test(Entry to public area is denied)
        procedure medium_entry_allowed;
    -- %endcontext
 
    -- %context(high)
        -- %displayname(Protocol: High)
 
        -- %beforeall
        procedure setup_protocol_high;
 
        -- %test(Hooded Person is yelled at)
        procedure high_welcome_message;
 
        -- %test(Try to access public area throws exception)
        -- %throws(-20100)
        procedure high_entry_allowed;
    -- %endcontext
end;
/
```

The implementation of the tests is very straight forward:

```sql
create or replace package body ut_deathstar_security as
 
  /* Helper-function to get a hooded test-person
     Not really needed but everything gets
     better with hoods!
   */
  function get_hooded_person
    return deathstar_security.t_person
  as
    l_result deathstar_security.t_person;
  begin
    l_result.is_hooded := true;
    return l_result;
  end;
 
  /* Helper-Function to make the tests
     more expressive
   */
  procedure expect_welcome_message(
    i_expected_msg varchar2)
  as
  begin
    ut.expect(
      deathstar_security.welcome(
          get_hooded_person()
        )
      ).to_equal(i_expected_msg);
  end;
 
  /* Helper-Function to make the tests
     more expressive
   */
  procedure expect_access(
    i_expected_access boolean)
  as
  begin
    ut.expect(
      deathstar_security.access_allowed(
          get_hooded_person()
        )
      ).to_equal(i_expected_access);
  end;
 
  procedure setup_test_protocols
  as
  begin
    /* For we want to completely control our tests,
       we also set up specific test-protocols because we
       cannot be sure they stay the same over time.
       This is highly dependent on the use case, of course
     */
    insert into deathstar_protocols
      values (-1, 'Test Low', 'LOW', 'BE_KIND', 80);
    insert into deathstar_protocols
      values (-2, 'Test Medium', 'MEDIUM', 'BE_SUSPICIOUS', 90);
    insert into deathstar_protocols
      values (-3, 'Test High', 'VERY HIGH', 'SHOOT_FIRST_ASK_LATER', 120);
 
    /* In case no active protocol entry exists */
    insert into deathstar_protocol_active ( id )
      select -1 from dual
        where not exists (select 1 from deathstar_protocol_active);
  end;
 
  procedure setup_protocol_low
  as
  begin
    update deathstar_protocol_active
      set id = -1;
  end;
 
  procedure low_welcome_message
  as
  begin
    expect_welcome_message('Be welcome!');
  end;
 
  procedure low_entry_allowed
  as
  begin
    expect_access(true);
  end;
 
  procedure setup_protocol_medium
  as
  begin
    update deathstar_protocol_active
      set id = -2;
  end;
 
  procedure medium_welcome_message
  as
  begin
    expect_welcome_message('Are you a jedi?');
  end;
 
  procedure medium_entry_allowed
  as
  begin
    expect_access(false);
  end;
 
  procedure setup_protocol_high
  as
  begin
    update deathstar_protocol_active
      set id = -3;
  end;
 
  procedure high_welcome_message
  as
  begin
    expect_welcome_message('Die rebel scum!');
  end;
 
  procedure high_entry_allowed
  as
  begin
    expect_access(false);
  end;
end;
/
```

The result of tests looks very strucutred and understandable:

```
call ut.run('ut_deathstar_security');
 
deathstar
  defense
    Deathstar Security
      Protocol: Medium
        Hooded Person gets a suspicious welcome message [,003 sec]
        Entry to public area is denied [,002 sec]
      Protocol: Low
        Hooded Person gets a kind welcome message [,001 sec]
        Entry to public area is allowed [,001 sec]
      Protocol: High
        Hooded Person is yelled at [,001 sec]
        Try to access public area throws exception [,002 sec]
 
Finished in ,020629 seconds
6 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
```

You can also test just one specific context by using the suitepath-notation:

```
call ut.run(':deathstar.defense.ut_deathstar_security.high');
 
deathstar
  defense
    Deathstar Security
      Protocol: High
        Hooded Person is yelled at [,002 sec]
        Try to access public area throws exception [,001 sec]
 
Finished in ,009016 seconds
2 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
```

As always, you can find the whole example on my [github repository](https://github.com/pesse/sith-demo-db/blob/master/100CodeExamples/utplsql/15_utplsql_contexts_with_deathstar_protocol.sql).

_I would love to hear how you like this kind of examples, don’t hesitate to reach out to me!_