Test Driven Development and PL/SQL – The Odyssey Begins

In the aftermath of the Brexit vote, I’m probably not alone in being a little confused.
Political discourse in the UK has focused on exactly who it was who voted to Leave.
The Youth Spokesperson you get on a lot of political programs right now, will talk accusingly of older voters “ruining” their future by opting to Leave.
Other shocked Remainers will put it down to people without a University Degree.
I’m not sure where that leaves me as someone who is ever so slightly over the age of 30, does not have a degree…and voted to Remain. I’m pretty sure I’m not Scottish…unless there’s some dark family secret my parents haven’t let me in on.
I suppose I must be a member of the “Metropolitan Elite” the Leave side was always muttering darkly about.
After all, I do pay a great deal of money to be driven from my country residence to London to work every day…although I do have to share the train with the odd one or two fellow elitists who’ve made the same extravagant choice.
This does of course assume that Milton Keynes qualifies as being “in the country” and that my living there is a matter of choice rather than a question of being able to afford living any closer to London.

With all the excrement…er…excitement of the Referendum Campaign and it’s aftermath, I somehow never got around to writing my application to track the progress of the Euros (or the Copa America for that matter).
Whenever a major football tournament comes around, I always resolve to do this, if only to evoke memories of my youth when a large part of my bedroom wall was taken up with a World Cup Wallchart where you could fill in the results as they happened. That’s without mentioning the months leading up to the tournament and trying to complete the Panini collection – the only time you’d ever hear a conversation such as “OK, I’ll let you have Zico in exchange for Mick Mills”.

In order to prevent this happening again, I’ve resolved to write an application capable of holding details of any major international football tournament.
In the course of writing this application, I’d like to take the opportunity to have a look at an aspect of PL/SQL development that maybe isn’t as commonly used as it should be – Unit Testing.

Over the next few weeks, I plan to take a look at some of the Testing Frameworks available for PL/SQL and see how they compare.
The objective here is not so much to find which framework is the best/most suitable, but to perform an objective comparison between them using the same set of tests which implement fairly commonly encountered functionality.

If you’re looking for recommendations for a framework, then this article by Jacek Gebal is probably a good place to start.

In this post, I’ll be outlining the functionality that I’ll be testing in the form of User Stories, together with the application data model (or at least, the bit of it I need to execute the tests).
I’ll also have a look at the common pattern that tests written in these frameworks tend to follow.
Just to highlight why using a Test Framework might be useful, I’ll also script a couple of simple tests in SQL to see just how much code you have to write to implement tests without using a framework.

Unit Testing Approach

Taking the purist approach to Test-Driven Development, we’d need to :

  1. Write the test first and ensure that it fails
  2. Write the minimum amount of code required for the test to pass
  3. Run the test to make sure it passes

Additionally, we’d need to make sure that the tests were independent of each other – that the execution of one test is not dependent on the successful execution of a prior test.

Following this approach to the letter would cause one or two issues.
Firstly, if the procedure your testing does not exist, your test will not run and fail. It will error.
As the point of this step is, essentially, to ensure that your test code is sound (i.e. it won’t pass irrespective of the code it runs against), this is not what we’re after.
The second issue is specific to PL/SQL.
When defining PL/SQL procedures that interact with database tables, it’s usually a good idea to use anchored declarations where appropriate.
Even if we write a stub procedure, if the tables it will interact with do not exist, we’d have to use native types for our parameters and update the signature of the procedure once the tables had been created.
There is always the danger that this additional step would be missed.

So, in terms of PL/SQL then, I’d suggest that the pre-requisites for writing our test are :

  • The data model components (tables, views, RI constraints) that the procedure will interact with
  • A stub procedure with correctly typed parameters

Many testing frameworks seem to adopt four basic steps for each test, some of which are optional. They are :

  1. Setup – put the application into a known state from which the test can run
  2. Execute – run the code that you want to test
  3. Verify – check that what you expected to happen did happen
  4. Teardown – return the system to the state it was in before this test was run

This is the general pattern that I’ll be following for my tests.

The Stories to Test

My story finding is complete and the backlog has been populated.
The stories selected for Sprint 1 are on the board. OK, they’ve been chosen so that they cover some of the more common scenarios that we might need to test.
The stories are :

  1. Add a competition – tests insert into a table with a Primary Key
  2. Add a Tournament – insert into a table with a Foreign Key constraint
  3. Remove Tournament – delete from a table
  4. Remove Competitions – delete from a Parent table
  5. Edit a Tournament – update of a record in a table
  6. View Tournaments by Competition – select multiple records using an in/out ref cursor
  7. Bulk Upload Competitions – insert records in bulk using LOG ERRORS
  8. Add a Team to a Tournament – insert a record using a synthetic foreign key

Each of these stories have multiple Acceptance Criteria.
It’s worth noting that, as some of the functionality (i.e. the data integrity) is implemented in the data model ( Primary Keys, Foreign Keys etc), the Acceptance Criteria for these stories needs to cover this as well as the functionality implemented in the PL/SQL code itself.

The Data Model

I’ve taken the approach that, for a story to be Sprint Ready, the Data Model to support it must already be in place.
Currently, the data model looks like this :

sprint1_data_model

The DDL to create the application owner is :

create user footie identified by password
/

alter user footie default tablespace USERS
/

grant create session, create table, create procedure, create sequence to footie
/

alter user footie quota unlimited on users
/

grant read, write on directory my_files to footie
/

…where password is the password you want to give this user.
Note that a directory called MY_FILES already exists in my database.

The DDL to create the Data Model includes the COMPETITIONS table…

create table competitions
(
    comp_code varchar2(5) constraint comp_pk primary key,
    comp_name varchar2(50) not null,
    description varchar2(4000)
)
/

comment on table competitions is
    'International Football competitionss for which tournament data can be added'
/

comment on column competitions.comp_code is
    'Internal code to uniquely identify this competitions'
/

comment on column competitions.comp_name is
    'The name of the competitions'
/

comment on column competitions.description is
    'A description of the competitions'
/

…an external table to facilitate the bulk upload of COMPETITIONS…

create table competitions_xt
(
    comp_code varchar2(5),
    comp_name varchar2(50),
    description varchar2(4000)
)
    organization external
    (
        type oracle_loader
        default directory my_files
        access parameters
        (
            records delimited by newline
            badfile 'competitions.bad'
            logfile 'competitions.log'
            skip 1
            fields terminated by ','
            (
                comp_code char(5),
                comp_name char(50),
                description char(4000)
            )
        )
            location('competitions.csv')
    )
reject limit unlimited
/

….the TOURNAMENTS table…

create table tournaments
(
    id number constraint tourn_pk primary key,
    comp_code varchar2(5),
    year_completed number(4) not null,
    host_nation varchar2(100),
    year_started number(4),
    number_of_teams number(3) not null,

    constraint tourn_uk unique( comp_code, year_completed, host_nation)
)
/

comment on table tournaments is
    'A (finals) tournament of an International Football competition. Table alias is tourn'
/

comment on column tournaments.id is
    'Synthetic PK for the table as the Natural Key includes host_nation, which may be null. Values taken from sequence tourn_id_seq'
/
comment on column tournaments.comp_code is
    'The Competition that this tournament was part of (e.g. World Cup). Part of the Unique Key. FK to COMPETITIONS(comp_code)'
/

comment on column tournaments.year_completed is
    'The year in which the last match of this tournament took place. Manatory. Part of Unique Key'
/

comment on column tournaments.host_nation is
    'The nation where the tournament was played (if a finals tournament). Part of Unique Key but is optional'
/
comment on column tournaments.year_started is
    'The year in which the first match was played ( if applicable). Cannot be later than the value in YEAR_COMPLETED'
/

comment on column tournaments.number_of_teams is
    'The number of teams taking part in the tournament'
/

…the TOURNAMENT_TEAMS table…

create table tournament_teams
(
    tourn_id number,
    team_name varchar2(100),
    group_stage varchar2(2),

    constraint team_pk primary key (tourn_id, team_name)
)
/

comment on table tournament_teams is
    'Teams participating in the Tournament. Alias is TEAM'
/

comment on column tournament_teams.tourn_id is
    'The ID of the tournament the team is participating in. Foreign Key to TOURNAMENTS(id).'
/

comment on column tournament_teams.team_name is
    'The name of the participating team'
/

comment on column tournament_teams.group_stage is
    'If the tournament has an initial group stage, the group identifier that the team is drawn in'
/

…this being 11g, a sequence to provide the values for the TOURNAMENT.ID synthetic key (in 12c you can define this as part of the table ddl)…

create sequence tourn_id_seq
/

… a Foreign Key constraint from TOURNAMENTS to COMPETITIONS….

alter table tournaments
    add constraint tourn_comp_fk foreign key
        (comp_code) references competitions(comp_code)
/

…and a Foreign Key from TOURNAMENT_TEAMS to TOURNAMENTS…

alter table tournament_teams
    add constraint team_tourn_fk foreign key
        (tourn_id) references tournaments(id)
/

I’ve kept the Foreign Keys in separate files to make the initial deployment of the application simpler. By doing this, I can create the tables in any order without worrying about RI constraints. I can then add these as a separate step after all of the tables have been created.
The tables’ non-RI constraints (Primary, Unique Keys, Not Null constraints etc.) are created along with the table.

One other point to note is that I know there are one or two issues with the first-cut of the DDL above. This is so that I can see how well the tests I write highlight these issues.
As we know, before we begin writing a test, we’ll need to have a stub procedure for it to compile against.

The first of these is :

create or replace package footie.manage_competitions
as
    procedure add_competition
    (
        i_code footie.competitions.comp_code%type,
        i_name footie.competitions.comp_name%type,
        i_desc footie.competitions.description%type
    );
end manage_competitions;
/

create or replace package body footie.manage_competitions
as
    procedure add_competition
    (
        i_code footie.competitions.comp_code%type,
        i_name footie.competitions.comp_name%type,
        i_desc footie.competitions.description%type
    )
    is
    begin
        null;
    end add_competition;
end manage_competitions;
/

Scripting the Tests

I want to write a test script for my first story – Add a Competition.
There are two Acceptance Criteria that I need to test :

  • A new competition is added
  • A competition cannot be added more than once

That’s pretty simple, so the test should be fairly straight-forward. Using a SQL script, the first test would probably look something like this :

set serveroutput on size unlimited
declare
    l_result varchar2(4);
    l_err_msg varchar2(4000) := null;

    l_rec_count pls_integer;
    l_code footie.competitions.comp_code%type;
    l_name footie.competitions.comp_name%type := 'UT1 Comp';
    l_desc footie.competitions.description%type := 'Test';

    l_counter pls_integer := 1;

begin
    -- Setup - make sure that the competition we're using for the test does not already exist
    select 'UT'||numeral
    into l_code
    from
    (
        select max( to_number( substr( comp_code, regexp_instr( comp_code, '[[:digit:]]')))) + 1 as numeral
        from footie.competitions
        where comp_code like 'UT%'
        and regexp_instr( substr( comp_code, -1, 1), '[[:digit:]]') = 1
        union
        select 1 from dual
    )
    where numeral is not null;
    -- execute - nested block so that we can handle/report any exceptions
    begin
        footie.manage_competitions.add_competition(l_code, l_name, l_desc);
        l_result := 'PASS';
    exception
        when others then
            l_err_msg := dbms_utility.format_error_backtrace;
            l_result := 'FAIL';
    end; -- execute block
    -- validate
    if l_result = 'PASS' then
        select count(*) into l_rec_count
        from footie.competitions
        where comp_code = l_code
        and comp_name = l_name
        and description = l_desc;

        if l_rec_count != 1 then
            l_result := 'FAIL';
            l_err_msg := 'Record not added';
        end if;
    end if;
    -- teardown
    rollback;
    -- Display Result
    dbms_output.put_line('Add Competition : '||l_result);
    if l_result = 'FAIL' then
        dbms_output.put_line(l_err_msg);
    end if;
end;
/

If we run this, we’d expect it to fail, as things stand :

PL/SQL procedure successfully completed.

Add Competition : FAIL
Record not added

Our second test will probably look like this :

set serveroutput on size unlimited
declare
    l_result varchar2(4);
    l_err_msg varchar2(4000) := null;
    l_err_code number;

    l_rec_count pls_integer;
    l_code footie.competitions.comp_code%type := 'UT2';
    l_name footie.competitions.comp_name%type := 'UT2 Comp';
    l_desc footie.competitions.description%type := null;

    l_counter pls_integer := 1;

begin
    -- Setup - make sure that the competition we're using for the test exists
    merge into footie.competitions
    using dual
    on (comp_code = l_code)
    when not matched then
    insert( comp_code, comp_name, description)
    values(l_code, l_name, l_desc);

    -- execute - nested block so that we can handle/report any exceptions
    begin
        footie.manage_competitions.add_competition(l_code, l_name, l_desc);
        l_result := 'FAIL';
    exception
        when others then
            l_err_msg := dbms_utility.format_error_backtrace;
            l_result := 'PASS';
    end; -- execute block
    -- validate
    if l_result = 'PASS' then
        select count(*) into l_rec_count
        from footie.competitions
        where comp_code = l_code
        and comp_name = l_name
        and nvl(description, 'X') = nvl(l_desc, 'X');

        if l_rec_count > 1 then
            l_result := 'FAIL';
            l_err_msg := 'Duplicate record has been added';
        end if;
    end if;
    -- teardown
    rollback;
    -- Display Result
    dbms_output.put_line('Add Competition : '||l_result);
    if l_result = 'FAIL' then
        dbms_output.put_line(l_err_msg);
    end if;
end;
/

That looks rather similar to our first test. Furthermore, the two test scripts combined add up to quite a lot of code.
At least when we run it, it fails as expected…

PL/SQL procedure successfully completed.

Add Competition : FAIL

The next step is to write the code to pass the tests…

create or replace package body manage_competitions
as
	procedure add_competition
	(
		i_code competitions.comp_code%type,
		i_name competitions.comp_name%type,
		i_desc competitions.description%type default null
	)
	is
	begin
        insert into competitions( comp_code, comp_name, description)
        values( i_code, i_name, i_desc);
	end add_competition;
end manage_competitions;
/

Now when we run our tests we get …

PL/SQL procedure successfully completed.

Add Competition : PASS

PL/SQL procedure successfully completed.

Add Competition : PASS

Now, I could carry on writing SQL scripts for Acceptance Criteria for all of the other stories in the sprint, but I think you get the idea.
Writing tests this way requires a large amount of code, much of which is being replicated in separate tests.

Of course, you could go down the route of moving some of your repeating test routines into PL/SQL packages and deploying your test code along with your application code to non-production environments.
Before going to all of that extra effort though, it’s probably worth checking to see if there’s a framework out there that can help reduce the testing burden.
Fortunately, I’ve got a couple of years until the next major international tournament so, I’ll be taking some time to do just that.
So, tune in next week ( or sometime soon after) when I’ll be taking a look at the first of these frameworks…SQLDeveloper Unit Testing

One thought on “Test Driven Development and PL/SQL – The Odyssey Begins

  1. Pingback: utPLSQL – We’re building the…Unit Tests! | The Anti-Kyte

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s