Narrate Star Wars Movies with utPLSQL annotations
Tags: 100codeexamples, utplsql
Published: 2019-07-31 06:42:27

---
title: Narrate Star Wars Movies with utPLSQL annotations
published: true
tags: #100CodeExamples,utPLSQL
canonical_url: https://cleandatabase.wordpress.com/2019/06/25/narrate-star-wars-movies-with-utplsql-annotations/
---

I recently played around a bit with the various utPLSQL annotations and found them pretty powerful.   
I’m pretty sure I will write some more about how you can use annotations like `--%beforeall` or `--%context` to make your tests more expressive, but today I just want to showcase some techniques and tell the story of the original Star Wars movies.

For the interesting part is completely in the package specification I will skip the body in the highlighted code here. Each procedure implementation only contains a call to `dbms_output.put_line` with some text.

You can get the whole example as always from my [github](https://github.com/pesse/sith-demo-db/blob/master/100CodeExamples/utplsql/17_utplsql_narrate_star_wars_movies_with_annotations.sql).

I create two test-packages, one parent package:

```sql
create or replace package sw_story_base as
  -- %suite(Star Wars: Movies)
 
  -- %beforeall
  procedure in_a_galaxy_far_away;
 
  -- %afterall
  procedure the_good_wins;
 
  procedure epic_intro;
 
  -- %beforeeach
  procedure deleted_scenes;
 
  -- %aftereach(deleted_scenes)
 
end;
/
```

And one child package:

```sql
create or replace package sw_story_episode4_to_6 as
  -- %suite(Episode IV-VI)
  -- %suitepath(sw_story_base)
 
  -- %beforeall
  procedure clone_troops_are_bad;
 
  -- %beforeall
  procedure rebels_are_good;
 
  -- %test(Scene: Construction plans of the deathstar are stolen (Rogue One))
  -- %beforetest(sw_story_base.epic_intro)
  -- %aftertest(rebels_flee)
  procedure rogue_one;
 
  -- %context(episode4)
  -- %displayname(Episode IV - A New Hope)
 
    -- %beforeall(sw_story_base.epic_intro)
 
    -- %beforeeach(c3po_is_afraid)
 
    -- %test(Scene: Rescue of princess Leia)
    -- %aftertest(death_of_obiwan)
    procedure rescue_of_leia;
 
    procedure death_of_obiwan;
 
    -- %test(Scene: Destruction of the deathstar)
    procedure destroy_deathstar;
 
    -- %aftereach
    procedure c3po_is_relieved;
 
  -- %endcontext
 
  -- %context(episode5)
  -- %displayname(Episode V - The Empire Strikes Back)
 
    -- %beforeall(sw_story_base.epic_intro,rebels_flee)
 
    -- %test(Scene: Battle for Hoth)
    -- %aftertest(rebels_flee)
    procedure battle_for_hoth;
 
  -- %endcontext
 
  -- %context(episode6)
  -- %displayname(Episode VI - Return of the Jedi)
 
    -- %beforeall(sw_story_base.epic_intro)
 
    -- %beforeeach(c3po_is_afraid)
    -- %aftereach(c3po_is_relieved)
 
    -- %test(Scene: Battle for Endor)
    procedure battle_for_endor;
 
  -- %endcontext
 
  procedure c3po_is_afraid;
 
  procedure rebels_flee;
 
end;
/
```

Let’s look at the complete output of `call ut.run(':sw_story_base')` (with `set serveroutput on`) before I list some takeaways from this example

```
Star Wars: Movies
  In a galaxy far, far away
  Episode IV-VI
    Context: Clone troops are bad
    Context: Rebels are good
    Episode VI - Return of the Jedi
      An epic intro is shown
      Scene: Battle for Endor [,001 sec]
      C3PO is afraid
      Cute teddy-bears totally own the imperial troops
      C3PO is relieved
    Episode V - The Empire Strikes Back
      An epic intro is shown
      The rebels have to flee
      Scene: Battle for Hoth [,001 sec]
      Imperium wins the battle for Hoth
      The rebels have to flee
    Episode IV - A New Hope
      An epic intro is shown
      Scene: Rescue of princess Leia [,002 sec]
      C3PO is afraid
      Luke, Han and Chewie find Leia and escape
      Obi-Wan Kenobi dies
      C3PO is relieved
      Scene: Destruction of the deathstar [,001 sec]
      C3PO is afraid
      The deathstar is actually destroyed.
      C3PO is relieved
    Scene: Construction plans of the deathstar are stolen (Rogue One) [,001 sec]
    An epic intro is shown
    Rebels are able to steal the construction plans of the deathstar
    The rebels have to flee
  Finally, the good always wins.
```

If we draw some boxes around suites, contexts and tests and highlight the different outputs based on the annotation that made them appear, we get this:

[![](https://cleandatabase.files.wordpress.com/2019/06/utplsql_annotations_starwars_movies-e1561497249965.png?w=742&h=539)](https://cleandatabase.files.wordpress.com/2019/06/utplsql_annotations_starwars_movies-e1561497249965.png)

## Takeaway 1: “each”-annotations are not called in child-suites

Although we defined `--%beforeeach` and `--%aftereach` in the `sw_story_base` suite, they are never called.

That is because there are no tests in the `sw_story_base` suite, but only in the `sw_story_episode4_to_6`, but `beforeeach` and `aftereach` are limited to tests of the same **suite.**

However, if they are defined on suite-level, they are also called for tests inside of contexts.

## Takeaway 2: “before” and “after” annotations can be parameterized with method-references

All `before` and `after` annotations allow to be parameterized with references to procedures:

- `beforeall`
- `beforeeach`
- `beforetest`
- `aftertest`
- `aftereach`
- `afterall`

The referenced procedures don’t need to be in the same package but must be accessible.

Multiple references can be separated by comma and their order of execution is fixed.

## Takeaway 3: You can reference the same method from different annotations

If you look at the `rebels_flee` procedure, it is referenced from several different annotations: `beforeall` and `aftertest`.

There is no limitation how often or from which annotations you can reference a method.

As you can see with `deleted_scenes`, you can even annotate a method with `beforeeach` and additionally reference it as `aftereach` (or any other `before` or `after` annotation).

## Takeaway 4: You cannot rely on the order of contexts and tests, but on the order of setup and teardown

It’s obvious that our example messed up the order in which the different Episodes and Scenes are being called (there is even a new random test-execution functionality available since 3.1.7).

But the order in which the `before` and `after` methods are called is always fixed and you can totally rely on that.

So while you should aim to make tests and contexts independent from each other, you can guarantee their setup and teardown.

## A request

I hope this little funny example showed you something new. If so, I’d love to hear about it.

But there’s another thing: I really wanted to create a little animation of that example-image where each step is highlighted together with the annotation that was used to call it. I sunk some hours trying to achieve it with PowerPoint but finally gave up frustrated.

If you know about any good and cheap tools that would help to explain stuff like this (for example by animated highlight/spotlight), please give me a hint.