6

utPLSQL 3.0 – How to have your cake and eat it

 3 years ago
source link: https://mikesmithers.wordpress.com/2018/04/25/utplsql-3-0-how-to-have-your-cake-and-eat-it/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

utPLSQL 3.0 – How to have your cake and eat it

Posted on April 25, 2018

“You can’t have your cake and eat it !” This seems to be a regular refrain from the EU in the ongoing Brexit negotiations.
They also seem to be a bit intolerant of “cherry picking”.
I’ve never really understood the saying, “You can’t have your cake and eat it”.
What’s the point in having the cake unless you are going to eat it ?
Fortunately, I’m not alone in my perplexity – just ask any Brexiteer member of the British Cabinet.
For those who want to make sense of it ( the saying, not Brexit), there is a handy Wikepedia page that explains all.

When it comes to Unit Testing frameworks for PL/SQL, compromise between cake ownership and consumption is usually required.
Both utPLSQL 2.0 and ruby-plsql-spec have their good points, as well as some shortcomings.
Of course, if you want a more declarative approach to writing Unit Tests, you can always use TOAD or SQLDeveloper’s built-in tools.

Recently, a new player has arrived on the PL/SQL testing scene.
Despite it’s name, utPLSQL 3.0 appears to be less an evolution of utPLSQL 2.0 as a new framework all of it’s own.
What I’m going to do here, is put utPLSQL 3.0 through it’s paces and see how it measures up to the other solutions I’ve looked at previously.
Be warned, there may be crumbs…

Installation and Setup

If you’re comfortable on the command line, you can follow the instructions in the utPLSQL 3.0 documentation.
On the other hand, if you’re feeling old-fashioned, you can just head over to the Project’s GitHub page and download the latest version.
At the time of writing this is 3.0.4.

The downloaded file is utPLSQL.zip.

Now to unzip it. In my case, on Ubuntu, things look like this…

unzip utPLSQL.zip
Archive:  utPLSQL.zip
980af88b62c3c75b11a8f81d6ad96d1c835021b8
creating: utPLSQL/
inflating: utPLSQL/CONTRIBUTING.md 
inflating: utPLSQL/LICENSE        
extracting: utPLSQL/VERSION        
creating: utPLSQL/docs/
...
***snip***
...
creating: utPLSQL/test/ut_suite_manager/
inflating: utPLSQL/test/ut_suite_manager/test_suite_manager.pkb 
inflating: utPLSQL/test/ut_suite_manager/test_suite_manager.pks 
creating: utPLSQL/test/ut_utils/
inflating: utPLSQL/test/ut_utils/test_ut_utils.pkb 
inflating: utPLSQL/test/ut_utils/test_ut_utils.pks 

The archive will have unzipped into a directory called utPLSQL.

We now have some decisions to make in terms of how we want to install the framework.
To save a bit of time, I’m going to go with the default.
Essentially this is :

DBMS_PROFILER is used by the framework to provide testing coverage statistics, more of which later.

Note that the documentation includes setup steps that provide you with a bit more control. However, if you’re happy to go with the default then you simply need to run the appropriate script as a user connected as SYSDBA…

cd utPLSQL/source
sqlplus berrym@bakeoff_tent as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 6 17:26:37 2018
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> @install_headless.sql
no rows selected
Creating utPLSQL user UT3
--------------------------------------------------------------
Installing utPLSQL v3 framework into UT3 schema
--------------------------------------------------------------
Switching current schema to UT3
--------------------------------------------------------------
Installing component UT_DBMS_OUTPUT_CACHE
Installing component UT_EXPECTATION_PROCESSOR
--------------------------------------------------------------
...
***snip***
...
Installing PLSQL profiler objects into UT3 schema
PLSQL_PROFILER_RUNS table created
PLSQL_PROFILER_UNITS table created
PLSQL_PROFILER_DATA table created
Sequence PLSQL_PROFILER_RUNNUMBER created
Installing component UT_FILE_MAPPER
--------------------------------------------------------------
...
***snip***
...
Synonym created.
Synonym created.
Synonym created.

We should now have a schema called UT3 which owns lots of database objects…

select object_type, count(*)
from dba_objects
where owner = 'UT3'
group by object_type
order by object_type
/
OBJECT_TYPE   COUNT(*) 
-----------   ---------
INDEX         13       
LOB            1        
PACKAGE       16       
PACKAGE BODY  16       
SEQUENCE       3        
SYNONYM       13       
TABLE          9        
TYPE          71       
TYPE BODY     53       
VIEW           2        
10 rows selected.

One subtle difference that you may notice between utPLSQL 3.0 and it’s predecessor is the fact that the default application owner schema has a fairly “modest” set of privileges :

select privilege
from dba_sys_privs
where grantee = 'UT3'
/
PRIVILEGE        
---------
CREATE SESSION   
CREATE TYPE      
CREATE VIEW      
CREATE SYNONYM   
CREATE SEQUENCE  
CREATE PROCEDURE 
CREATE TABLE     
ALTER SESSION    
8 rows selected.

However, the default password for this account is known…

SQL> connect ut3/XNtxj8eEgA6X6b6f@centos_xe
Connected.
SQL> show user
USER is "UT3"
SQL>

Whilst it’s true that, as a testing framework, utPLSQL should be deployed only in non-production environments you may nevertheless find it prudent to lock the account immediately after installation…

alter user ut3 account lock
/

…and possibly even change the password for good measure.

Annotations and Matchers

There are two main component types in a utPLSQL 3.0 unit test – Annotations and Matchers.

Annotations allow the framework to identify packaged procedures as tests and (if required), group them into suites. This obviates the need for separate storage of configuration information.
Matchers are used to validate the results from a test execution.

This explanation would probably benefit from an example…

create or replace package ut3_demo
as
-- %suite(Demonstrate Framework)
-- %test(Will always pass)
procedure perfect_cake;
-- %test( Will always fail)
procedure dontlike_cake;
end ut3_demo; 
/

The package begins with the suite annotation to identify it as a package that contains unit tests.

-- %suite(Demonstrate Framework)

The text in brackets displays when the test suite is executed.
The positioning of this annotation is important. It needs to be the first thing in the package after the CREATE OR REPLACE statement.
Also, as it’s a package level annotation, it needs to have one or more blank lines between it and any procedure level annotations.

Each of the procedures in the package is identified as an individual test

-- %test(Will always pass)
-- %test( Will always fail)

Once again the text will display when the test is executed.

In the package body, we can see the matchers come into play :

create or replace package body ut3_demo
as
procedure perfect_cake is
begin
ut.expect( 1).to_( equal(1) );
end;
procedure dontlike_cake is
begin
ut.expect(1, 'Oops').to_( equal(0) );
end;
end ut3_demo;
/

First impressions are that the code seems to have more in common with ruby-plsql-spec than it does with utPLSQL 2.0.
This impression is re-enforced when we execute the tests…

I was going to re-introduce the Footie app at this point as I’ve used it to demonstrate all of the other PL/SQL testing frameworks I’ve looked at so far.
However, in these unprecedented times, I feel that an unprecedented (and very British) example is called for.
Therefore, I humbly present…

The Great Brexit Bake-Off Application

The application owner is one hollywoodp ( the observant among you will have already noticed that Mary Berry is the DBA)…

The application consists of some tables :

alter session set current_schema = hollywoodp
/
create table brexit_bake_off
(
id number primary key,
contestant varchar2(100),
show_stopper varchar2(100),
notes varchar2(4000)
)
/
--
-- Create an error table for bulk loads - ERR$_BREXIT_BAKE_OFF
--
exec dbms_errlog.create_error_log('brexit_bake_off');
--
-- External table for ETL process to upload records to the application
--
create table contestants_xt
(
id number,
contestant varchar2(100),
show_stopper varchar2(100),
notes varchar2(4000)
)
organization external
(
type oracle_loader
default directory my_files
access parameters
(
records delimited by newline
badfile 'contestants.bad'
logfile 'contestants.log'
skip 1
fields terminated by '|'
(
id integer external,
contestant char(100),
show_stopper char(100),
notes char(4000)
)
)   
location ( 'contestants.csv')
)
reject limit unlimited
/

…and a package :

create or replace package bake_off as
procedure add_contestant(
i_id number,
i_contestant varchar2,
i_show_stopper varchar2,
i_notes varchar2 default null);
function get_contestant_id( i_contestant varchar2)
return number;
function get_show_stopper( i_id number)
return varchar2;
procedure list_contestants( io_contestant_list in out sys_refcursor);
procedure upload_contestants;
end bake_off;
/
create or replace package body bake_off as
procedure add_contestant(
i_id number,
i_contestant varchar2,
i_show_stopper varchar2,
i_notes varchar2 default null)
is
begin
insert into brexit_bake_off( id, contestant, show_stopper, notes)
values( i_id, i_contestant, i_show_stopper, i_notes);
end add_contestant;   
function get_contestant_id( i_contestant varchar2)
return number
is
l_rtn number;
begin
select id
into l_rtn
from brexit_bake_off
where upper(contestant) = upper(i_contestant);
return l_rtn;
exception when no_data_found then
raise_application_error(-20900, 'This contestant is not in The Tent at the moment.');
end get_contestant_id;
function get_show_stopper( i_id number)
return varchar2
is
l_rtn varchar2(100);
begin
select show_stopper
into l_rtn
from brexit_bake_off
where id = i_id;
return l_rtn;
exception when no_data_found then
raise_application_error(-20901, 'Soggy Bottom Error !');
end get_show_stopper;   
procedure list_contestants( io_contestant_list in out sys_refcursor)
is
begin
open io_contestant_list for
select id, contestant, show_stopper, notes
from brexit_bake_off
order by id;
end list_contestants;
procedure upload_contestants
is
begin
insert into brexit_bake_off( id, contestant, show_stopper, notes)
select id, contestant, show_stopper, notes
from contestants_xt
log errors reject limit unlimited;
end upload_contestants;   
end bake_off;
/

Now, whilst Mr Hollywood is a renowned TV Chef, his PL/SQL coding skills do leave a little to be desired.
Also, the application in it’s current state is just about the minimum he could come up with to demonstrate the framework, which is, after all, why we’re here.
Therefore, I’d ask you to overlook the lack of anchored declarations etc. because, before we put the oven on, we need to make a fairly important design decision.

Where should I put my tests ?

According to the documentation, the default for utPLSQL is to have the tests located in the same schema as the code they are to run against. However, you may well have good reasons for wanting to keep the tests in a separate schema.

For one thing, you may want to ensure that the process to promote your codebase through to Test and Production environments remains consistent and that you don’t have to worry about taking specific steps to ensure that your test code ends up somewhere it shouldn’t.
Additionally, you may find it useful to create “helper” packages for your unit tests. These packages won’t themselves contain tests but will need to be treated as part of your test codebase rather than the application codebase.

If you decide to go down this route with utPLSQL, then you will have to ensure that the schema that owns your tests has the CREATE ANY PROCEDURE privilege if you want to avail yourself of the code coverage reporting provided by the framework.

This privilege does not need to be granted if the application owning schema also holds the tests.

I really would prefer to have my tests in an entirely separate schema. So, I’ve created this schema as follows :

set verify off
accept passwd prompt 'Enter password for UTP_BAKEOFF : ' hide
create user utp_bakeoff identified by &passwd
default tablespace users
temporary tablespace temp
/
grant create session, create view, create sequence, create table, 
create any procedure to utp_bakeoff
/
alter user utp_bakeoff quota unlimited on users
/
--
-- Application specific grants required to generate test file for data load to
-- external table
grant read, write on directory my_files to utp_bakeoff
/
grant execute on utl_file to utp_bakeoff
/

The test schema also requires privileges on all of the Application’s database objects :

set serveroutput on size unlimited
declare
l_priv varchar2(30);
begin
for r_object in
(
select object_name, object_type
from dba_objects
where owner = 'HOLLYWOODP'
and object_type in ('PACKAGE', 'PROCEDURE', 'SEQUENCE', 'TABLE', 'VIEW')
)
loop
l_priv :=
case r_object.object_type
when 'PACKAGE' then 'EXECUTE'
when 'PROCEDURE' then 'EXECUTE'
when 'TABLE' then 'ALL'
else 'SELECT'
end;
dbms_output.put_line('Granting '||l_priv||' on '||r_object.object_name);
execute immediate 'grant '||l_priv||' on hollywoodp.'||r_object.object_name||' to UTP_BAKEOFF';
end loop;
end;
/

Run this and we get …

Granting ALL on BREXIT_BAKE_OFF
Granting EXECUTE on BAKE_OFF
Granting ALL on ERR$_BREXIT_BAKE_OFF
Granting ALL on CONTESTANTS_XT

Finally, we’re ready to start testing our application…

Testing Single Row Operations

First, we’re going to write some tests for the BAKE_OFF.ADD_CONTESTANT procedure. So, in the utp_bakeoff schema, we create a package

create or replace package add_contestant_ut
as
-- %suite(add_contestant)     
-- %suitepath(brexit_bake_off.bake_off)
-- helper function to generate a single contestant record
function setup_contestant return hollywoodp.brexit_bake_off%rowtype;
-- %test(Add a new contestant)
procedure add_contestant;
-- %test( Add existing contestant)
procedure add_duplicate_contestant;
end add_contestant_ut;
/

Before we take a look at the package body, it’s worth pausing to take note of the %suitepath annotation.
This Annotation allows separate test packages to be grouped together. In this instance, I’ve defined the path as Application Name/Package Name.
Note that if you want to use this annotation then it must be on the line directly after the %suite annotation in the package header. Otherwise utPLSQL won’t pick it up.

Now for the test package body…

create or replace package body add_contestant_ut
as
function setup_contestant return hollywoodp.brexit_bake_off%rowtype
is
rec_contestant hollywoodp.brexit_bake_off%rowtype;
begin
select nvl(max(id), 0) + 1 as id,
'David Davis' as contestant,
'Black Forest Gateaux' as show_stopper,
'Full of cherries to pick' as notes
into rec_contestant
from hollywoodp.brexit_bake_off;
return rec_contestant;
end setup_contestant;   
function contestant_exists( i_id in number)
return boolean
is
dummy pls_integer;
begin
select 1
into dummy
from hollywoodp.brexit_bake_off
where id = i_id;
return true;
exception when no_data_found then return false;
end contestant_exists;
-- %test(Add a new contestant)
procedure add_contestant is
rec_contestant hollywoodp.brexit_bake_off%rowtype;
begin
-- Test setup phase
rec_contestant := setup_contestant;
-- Test execution
hollywoodp.bake_off.add_contestant(
i_id => rec_contestant.id,
i_contestant => rec_contestant.contestant,
i_show_stopper => rec_contestant.show_stopper,
i_notes => rec_contestant.notes);
-- Verify result
ut.expect( contestant_exists(rec_contestant.id)).to_( be_true() );
end add_contestant;
-- %test( Add existing contestant)
procedure add_duplicate_contestant is
rec_contestant hollywoodp.brexit_bake_off%rowtype;
begin
-- Test setup phase
rec_contestant := setup_contestant;
insert into hollywoodp.brexit_bake_off( id, contestant, show_stopper, notes)
values( rec_contestant.id, rec_contestant.contestant, rec_contestant.show_stopper, rec_contestant.notes);
-- Test execution - use a nested block as we're expecting an error...
begin
hollywoodp.bake_off.add_contestant(
i_id => rec_contestant.id,
i_contestant => rec_contestant.contestant,
i_show_stopper => rec_contestant.show_stopper,
i_notes => rec_contestant.notes);
-- Validation
ut.fail('Expected unique key violation error but none raised');
exception when others then
ut.expect( sqlcode).to_( equal( -1));
end;
end add_duplicate_contestant;
end add_contestant_ut;
/

The structure of the tests is quite familiar in that there are four distinct phases, the first three of which are explicit :

  • Setup – prepare the system for the test
  • Execute – run the code to be tested
  • Verify – check the result
  • Teardown – reset the system to the state it was in prior to the test being run

Note that, in this instance, we are using the default behaviour of the framework for the teardown. This involves a savepoint being automatically created prior to each test being run and a rollback to that savepoint once the test completes. Later on, we’ll have a look at circumstances where we need to handle the Teardown phase ourselves.

The first test – add_contestant – uses a helper function and a boolean matcher :

ut.expect( contestant_exists(rec_contestant.id)).to_( be_true() );

The second test is checking both that we get an error when we try to add a duplicate record and that the error returned is the one we expect, namely :

ORA-00001: unique constraint (constraint_name) violated

As we’re expecting the call to the application code to error, we’re using a nested block :

begin
hollywoodp.bake_off.add_contestant(
i_id => rec_contestant.id,
i_contestant => rec_contestant.contestant,
i_show_stopper => rec_contestant.show_stopper,
i_notes => rec_contestant.notes);
-- Validation
ut.fail('Expected unique key violation error but none raised');
exception when others then
ut.expect( sqlcode).to_( equal( -1));
end;

If we now run the test, we can see that our code works as expected.

Incidentally, we can also see how utPLSQL recognises the hierarchy we’ve defined in the suitepath.

Whilst this approach works just fine for single-row operations, what happens when the framework is confronted with the need for …

Testing Ref Cursor values

This is always something of an ordeal in PL/SQL test frameworks – at least all of the ones I’ve looked at up until now. Fortunately utPLSQL’s equality matcher makes testing Ref Cursors as simple as you feel it really should be…

create or replace package list_contestants_ut as
-- %suite(list_contestants)     
-- %suitepath(brexit_bake_off.bake_off)
-- %test( List all the contestants)
procedure list_contestants;
end list_contestants_ut;
/
create or replace package body list_contestants_ut as
procedure list_contestants
is
l_rc_expected sys_refcursor;
l_rc_actual sys_refcursor;
begin
-- setup
insert into hollywoodp.brexit_bake_off
with recs as
(
select nvl(max(id), 0) + 1 as id,
'David Davis' as contestant,
'Black Forest Gateau' as show_stopper,
'Lots of cherries' as notes
from hollywoodp.brexit_bake_off
union all
select nvl(max(id), 0) + 2,
'Michel Barnier',
'Chocolate Eclair',
'No cherries to pick'
from hollywoodp.brexit_bake_off
union all
select nvl(max(id), 0) + 3,
'Jacob Rees-Mogg',
'Victoria Sponge',
'Traditional and no need for cherries'
from hollywoodp.brexit_bake_off
union all
select nvl(max(id), 0) + 4,
'Tony Blair',
'Jaffa Cake',
'Definitely not a biscuit and a new referendum is required to settle this'
from hollywoodp.brexit_bake_off
)
select * from recs;
-- Get expected results
open l_rc_expected for
select id, contestant, show_stopper, notes
from hollywoodp.brexit_bake_off
order by 1;
-- execute
hollywoodp.bake_off.list_contestants(l_rc_actual);
-- Verify
ut.expect( l_rc_actual).to_equal( l_rc_expected);
close l_rc_actual;
close l_rc_expected;
end list_contestants;
end list_contestants_ut;
/

Run this and we get :

Incidentally, you may notice that the call to ut.run in this instance is a little different to what I was using previously.
There are a number of ways to execute one or more utPLSQL tests through the ut.run procedure and we’ll be taking a look at some of these in a little while.

Testing across Transaction Boundaries

In this case, we’re testing the bulk upload of records from a file into the application tables via an external table.
The load itself makes use of the LOG ERRORS clause which initiates an Autonomous Transaction in the background.
This means we’re going to need to handle the teardown phase of the tests ourselves as utPLSQL’s default rollback-to-savepoint operation will not do the job.

First of all, here’s a quick reminder of the BAKE_OFF.UPLOAD_CONTESTANTS procedure that we want to test :

...
procedure upload_contestants
is
begin
insert into brexit_bake_off( id, contestant, show_stopper, notes)
select id, contestant, show_stopper, notes
from contestants_xt
log errors reject limit unlimited;
end upload_contestants;   
...

As part of the setup and teardown for the test, we’ll need to do a number of file operations – i.e.

  • backup the existing data file
  • create a test file for the external table
  • remove the test file
  • move the original file (if any) back into place

As we may have other loads we want to test this way in the future, then it would seem sensible to separate the code for these file operations into a helper package :

create or replace package test_file_utils as
function file_exists( i_dir all_directories.directory_name%type, i_fname varchar2)
return boolean;
procedure backup_file( i_dir all_directories.directory_name%type, i_fname varchar2, o_backup_fname out varchar2);
procedure revert_file( i_dir all_directories.directory_name%type, i_fname varchar2, i_backup_fname varchar2);
end test_file_utils;
/
create or replace package body test_file_utils as
function file_exists( i_dir all_directories.directory_name%type, i_fname varchar2)
return boolean
is
fh utl_file.file_type;
e_no_file exception;
-- ORA-29283 is returned if file does not exist or is not accessible.
-- If the latter then the whole thing will fall over when we try to overwrite it.
-- For now then, we can assume that this error means "file does not exist"
pragma exception_init(e_no_file, -29283);
begin
fh := utl_file.fopen( i_dir, i_fname, 'r');
utl_file.fclose(fh);
return true;
exception when e_no_file then
return false;
end file_exists;
procedure backup_file(
i_dir all_directories.directory_name%type,
i_fname varchar2,
o_backup_fname out varchar2)
is
backup_fname varchar2(100);
begin
backup_fname := i_fname||systimestamp||'.bak';
utl_file.frename( i_dir, i_fname, i_dir, backup_fname);
o_backup_fname := backup_fname;
end backup_file;
procedure revert_file(
i_dir all_directories.directory_name%type,
i_fname varchar2,
i_backup_fname varchar2)
is
begin
-- delete i_fname - the file created for the test
utl_file.fremove(i_dir, i_fname);
-- if a backup filename exists then put it back
if i_backup_fname is not null then
utl_file.frename( i_dir, i_backup_fname, i_dir, i_fname);
end if;
end revert_file;
end test_file_utils;
/

Remember, as we’ve decided to hold all of our test code in a separate schema, we don’t have to worry about distinguishing this package from the application codebase itself.

Now for the test. In the package header, we’re using the rollback annotation to let utPLSQL know that we’ll look after the teardown phase manually for any test in this package :

create or replace package upload_contestants_ut as
-- %suite(upload_contestants)    
-- %rollback(manual)
-- %suitepath(brexit_bake_off.bake_off)
-- %test( bulk_upload_contestants)
procedure upload_contestants;
end upload_contestants_ut;
/

Now for the test code itself. There’s quite a bit going on here.
In the setup phase we :

  • backup the target application table and it’s associated error table
  • generate the file to be uploaded
  • populate ref cursors with the expected results

In the verification phase, we use the to_equal matcher to compare the expected results refcursors with the actual results ( also ref cursors).

Finally, we re-set the application to it’s state prior to the test being executed by :

  • removing test records from the application and error tables
  • dropping the backup tables
  • tidying up the data files

All of which looks something like this :

create or replace package body upload_contestants_ut as
--
-- Private helper procedures
--
procedure backup_tables is
pragma autonomous_transaction;
begin
execute immediate 'create table brexit_bake_off_bu as select rowid as bu_rowid, tab.* from hollywoodp.brexit_bake_off tab';
execute immediate 'create table err$_brexit_bake_off_bu as select rowid as bu_rowid, tab.* from hollywoodp.err$_brexit_bake_off tab';
end backup_tables;
procedure create_contestant_file( i_dir all_directories.directory_name%type, i_fname varchar2)
is
fh utl_file.file_type;
begin
fh := utl_file.fopen(i_dir, i_fname, 'w');
utl_file.put_line(fh, 'id|contestant|show_stopper|notes');
for r_contestant in
(
select nvl(max(id), 0) + 1||chr(124)||'David Davis'
||chr(124)||'Black Forest Gateau'||chr(124)||null||chr(124) as rec
from hollywoodp.brexit_bake_off
union all
select nvl(max(id), 0) + 2||chr(124)||'Michel Barnier'
||chr(124)||'Chocolate Eclair'||chr(124)||'Leave my cherries alone !'||chr(124)
from hollywoodp.brexit_bake_off
union all
-- Duplicate records (by ID)
select nvl(max(id), 0) + 1||chr(124)||'Jacob Rees-Mogg'
||chr(124)||'Victoria Sponge'||chr(124)||null||chr(124)
from hollywoodp.brexit_bake_off
union all
select nvl(max(id), 0) + 2||chr(124)||'Tony Blair'
||chr(124)||'Jaffa Cakes'||chr(124)||'Tough on brexit, tough on the causes of Brexit'||chr(124)
from hollywoodp.brexit_bake_off
)
loop
utl_file.put_line(fh, r_contestant.rec);
end loop;
utl_file.fflush(fh);
utl_file.fclose(fh);
end create_contestant_file;
procedure cleardown_test_records is
pragma autonomous_transaction;
begin
execute immediate
'delete from hollywoodp.brexit_bake_off
where rowid not in (select bu_rowid from brexit_bake_off_bu)';
execute immediate
'delete from hollywoodp.err$_brexit_bake_off
where rowid not in (select bu_rowid from err$_brexit_bake_off_bu)';
commit;   
end cleardown_test_records;
procedure drop_backup_tables is
pragma autonomous_transaction;
begin
execute immediate 'drop table brexit_bake_off_bu';
execute immediate 'drop table err$_brexit_bake_off_bu';
end drop_backup_tables;
-- The test itself
procedure upload_contestants
is
target_dir constant all_directories.directory_name%type := 'MY_FILES';
fname constant varchar2(100) := 'contestants.csv';
backup_fname varchar2(100);
expected_load sys_refcursor;
expected_err sys_refcursor;
actual_load sys_refcursor;
actual_err sys_refcursor;
begin
--
-- Setup Phase
--
backup_tables;
-- Backup the external table file
if test_file_utils.file_exists( target_dir, fname) then
test_file_utils.backup_file( target_dir, fname, backup_fname);
end if;
-- Create a load file
create_contestant_file( target_dir, fname);
-- Populate the expected results
open expected_load for
select *
from hollywoodp.contestants_xt
where upper(contestant) in ('DAVID DAVIS', 'MICHEL BARNIER')
order by id;
open expected_err for   
select 1 as ora_err_number$,
id, contestant, show_stopper, notes
from hollywoodp.contestants_xt
where upper( contestant) in ('JACOB REES-MOGG', 'TONY BLAIR')
order by id;
--   
-- Execute
--
hollywoodp.bake_off.upload_contestants;
--
-- Verify
--
open actual_load for
select *
from hollywoodp.brexit_bake_off
order by id;
open actual_err for
select ora_err_number$,
id, contestant, show_stopper, notes
from hollywoodp.err$_brexit_bake_off
order by id;
ut.expect( expected_load).to_equal( actual_load);
ut.expect( expected_err).to_equal( actual_err);
--
-- Teardown
--
cleardown_test_records;
drop_backup_tables;
test_file_utils.revert_file( target_dir, fname, backup_fname);
end upload_contestants;
end upload_contestants_ut;
/

Running tests and reporting results

The framework does offer an API for use to execute tests programatically. However, whilst you’re writing the tests themselves, you’ll probably want something a bit more interactive.

You can simply run all of the tests in the current schema as follows :

set serveroutput on size unlimited
exec ut.run

However, there are times when you’ll probably need to be a bit more selective.
Therefore, it’s good to know that utPLSQL will let you execute tests interactively in a number of different ways :

set serveroutput on size unlimited
alter session set current_schema = utp_bakeoff
/
-- single test passing in (package name.procedure name)
exec ut.run('add_contestant_ut.add_contestant')
-- all tests in a package (package name)
exec ut.run('add_contestant_ut')
-- all suites in a suitepath (owning schema:suitepath)
exec ut.run('utp_bakeoff:brexit_bake_off.bake_off')

If we run this for the application tests we’ve written, the output looks like this :

By default ut_run uses the ut_document_reporter to format the output from the tests.
However, there are other possible formats, which you can invoke with a second argument to UT_RUN.

For example…

exec ut_run('add_contestant_ut', ut_xunit_reporter());

…outputs…

<testsuites tests="2" skipped="0" error="0" failure="0" name="" time=".002972" >
<testsuite tests="2" id="1" package="brexit_bake_off"  skipped="0" error="0" failure="0" name="brexit_bake_off" time=".002894" >
<testsuite tests="2" id="2" package="brexit_bake_off.bake_off"  skipped="0" error="0" failure="0" name="bake_off" time=".002859" >
<testsuite tests="2" id="3" package="brexit_bake_off.bake_off.add_contestant_ut"  skipped="0" error="0" failure="0" name="add_contestant" time=".00279" >
<testcase classname="brexit_bake_off.bake_off"  assertions="1" skipped="0" error="0" failure="0" name="Add a new contestant" time=".001087" >
</testcase>
<testcase classname="brexit_bake_off.bake_off.add_contestant_ut"  assertions="1" skipped="0" error="0" failure="0" name="Add existing contestant" time=".001175" >
</testcase>
</testsuite>
</testsuite>
</testsuite>
</testsuites>

By contrast, if you want something slightly more colourful…

set serveroutput on size unlimited
exec ut.run('add_contestant_ut', a_color_console => true)

…or even…

set serveroutput on size unlimited
exec ut.run(a_color_console => true)

Note that, unlike the previous executions, the a_color_console parameter is being passed by reference rather than position.

Provided your command line supports ANSICONSOLE, you are rewarded with…

Test Coverage reporting

As mentioned a couple of times already, utPLSQL does also provide coverage reporting functionality.
In this case, we’re going to look at the HTML report.

set serveroutput on size unlimited
alter session set current_schema = utp_bakeoff;
set feedback off
spool add_contestant_coverage.html
exec ut.run('add_contestant_ut', ut_coverage_html_reporter(), a_coverage_schemes => ut_varchar2_list('hollywoodp'))
spool off

Opening the file in a web browser we can see some summary information :

Clicking on the magnifying glass allows us to drill-down into individual program units :

Of course, you’ll probably want to get an overall picture of coverage in terms of all tests for the application code. In this case you can simply run :

set serveroutput on size unlimited
alter session set current_schema = utp_bakeoff;
set feedback off
spool brexit_bake_off_coverage.html
exec ut.run(ut_coverage_html_reporter(), a_coverage_schemes => ut_varchar2_list('hollywoodp'))
spool off

When we look at this file in the browser, we can see that at least we’ve made a start :

Keeping track of your Annotations

Whilst annotations provide a method of identifying and organising tests in a way that avoids the need for storing large amounts of metadata, it can be easy to “lose” tests as a result.
For example, if you have a fat-finger moment and mis-type a suitepath value, that test will not execute when you expect it to ( i.e. when you run that suitepath).

Fortunately, utPLSQL does keep track of the annotations under the covers, using the UT_ANNOTATION_CACHE_INFO and UT_ANNOTATION_CACHE tables. Despite their names, these are permanent tables :

annotation_cache_tables.png?w=584

So, if I want to make sure that I haven’t leaned on the keyboard at an inopportune moment, I can run a query like :

select aci.object_name, ac.annotation_text
from ut3.ut_annotation_cache_info aci
inner join ut3.ut_annotation_cache ac
on ac.cache_id = aci.cache_id
and ac.annotation_name = 'suitepath'
and aci.object_owner = 'UTP_BAKEOFF'
order by 1
/

…which in my case returns…

OBJECT_NAME                    ANNOTATION_TEXT                        
------------------------------ ----------------------------------------
ADD_CONTESTANT_UT              brexit_bake_off.bake_off               
LIST_CONTESTANTS_UT            brexit_bake_off.bake_off               
UPLOAD_CONTESTANTS_UT          brexit_bake_off.bake_off               

Final Thoughts

I’ve tried to give some flavour of what the framework is capable of, but I’ve really just scratched the surface.
For more information, I’d suggest you take a look at the framework’s excellent documentation.

Also, Jacek Gebal, one of the authors of the framework has shared a presentation which you may find useful.

The utPLSQL 3.0 framework is a very different beast from it’s predecessor. The ground up re-write of the framework has brought it bang up to date in terms of both functionality and ease of use.
If you’re looking for a PL/SQL testing framework that’s contained entirely within the database then look no further…unless your allergic to cherries.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK