Oracle Unit Testing with utPLSQL

Brendan Furey, October 2015http://aprogrammerwrites.eu/

Oracle Unit Testing with utPLSQL 2

Agenda

Manual vs Automated Unit Testing, and Agile (6 slides) utPLSQL vs Gherkin (2 slides) Working with utPLSQL (5 slides) utPLSQL Examples (2 slides)

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 3

Manual vs Automated Unit Testing

Unit testing on database is mostly manual up to now utPLSQL inspired by xUnit testing Automated unit testing on the database has additional challenges compared

with Java unit testing for example Database developers express concerns about cost of automation Creator of utPLSQL admits he does not use it himself Checklist Driven Development: TDD on the cheap

"Doing full-blown test driven development (TDD) simply is not going to happen widely in the Oracle database programming world."

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 4

Cost of Automated Testing

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 5

Agile Changes the Equation

Agile and TDD with ideas of smaller, more frequent changes Manual testing now too expensive Automation therefore required Overall cost may be smaller with automation

Continuous Integration / Delivery changes everything!

utPLSQL was ahead of its time initially (c.15 years ago)

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 6

Example: Feature Cloning with Manual Testing

New feature similar to an existing one (second version of web service maybe) Manual regression testing too costly, so refactoring rarely happens Copy and modify, end up with code duplication, larger code base Code becomes larger and less modular as time passes FDD - Fear Driven Development

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 7

Example: Feature Cloning with Automated Testing

Refactor to reduce new code base, and re-use base and unit test code Automated regression testing gives confidence to refactor safely Code is continuously refined and improved Less technical debt

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 8

Testing Cycle: Pattern vs Antipattern

Diagrams from software testing ice-cream cone

Pyramid Ideal Pattern

• Consider testing approaches with these in mind

Icecream Cone Antipattern

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 9

utPLSQL vs Gherkin 1: Gherkin

Some support for Cucumber and other Gherkin methods, but some opposition too:

Please don't use Cucumber TDD: Where Did It All Go Wrong?

Driving motivation may be, as Ian Cooper suggests, due to poor understanding of TDD

Notably, focusing on implementation details, not behaviours Defining all tests up front with QA, less iterative Going out to Java problematic May not be very agile? Acceptance testing is generally less rigorous than good unit testing

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 10

utPLSQL vs Gherkin 2: utPLSQL

Processing in base code is of same nature as that of unit testing code, hence well-suited to database language PL/SQL

Developer can start with a single test Make it work Add additional tests and make them work iteratively Tests are exclusively behavioural, eliminating need for repeated unit testing up

the pyramid More of a TDD approach

QA will focus on integration tests and avoid unit testing Currently QA tend to do unit testing after developers have done theirs Symptom of icecream cone antipattern

QA will validate the development unit tests

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 11

Working with utPLSQL 1: Home Page

utPLSQL Home Page Steve Feuerstein's Recommendations for unit testing PL/

SQL programs

"utPLSQL is free and gives you total control over your test code. But that's mostly because you have to write all the test code"

"If you are allergic to UI-based testing and/or worry about being constrained by an IDE's feature set (in other words, you are a command line fanatic), utPLSQL offers a powerful alternative."

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 12

Working with utPLSQL 2: Structure

CREATE OR REPLACE PACKAGE ut_package IS PROCEDURE ut_setup; PROCEDURE ut_teardown; PROCEDURE ut_proc; END ut_package;

Think in design patterns One web service procedure may be based on the code for an earlier one Use similar coding strategies Generic array types can help here Modularise just as with base code Behaviours only tested, no implementation tests Large set of ‘assert’ procedures: may be simpler to just cast to strings and assert string

equality

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 13

Working with utPLSQL 3: Improving the Output

Output format not great, but use a wrapper assert procedure Can group tests by category such as normal/edge/exception

etc. Tabular output of expected vs actuals Count tests and failures by group

Also summarise by suite Unit tests succeed or fail, but:

Can add useful information, such as code timings (I have small footprint code timer)

May help to diagnose a sudden performance problem

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 14

Working with utPLSQL 4: Coding Strategies 1 – data structures etc.

Drive by data structures rather than case by case in program logic

Use arrays for test data setup, for calling parameters and for expected results

Use arrays to store meaningful test descriptions Concatenate fields in a record to reduce number of assertions Use utility procedures to transform a range to a range category

for assertion, eg NOW = 'in last ten minutes', PAST = 'earlier than 10 minutes ago'

Use generic arrays rather than creating new record or object types for each test case

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 15

Working with utPLSQL 5: Coding Strategies 2 - DML

Use special DML APIs for the test data Parametrise important fields Use dummy values for other mandatory fields for

simplicity to avoid passing parameters; maybe index-suffix them

Ignore non-mandatory fields that don't figure in the testing

May be best to keep simple, and avoid over-sharing

Brendan Furey, 2015

Oracle Unit Testing with utPLSQL 16

utPLSQL Example 1: Simple Table API

API to merge reference data into a table Runs via suite (although only 1 test set in suite)

Brendan Furey, 2015

C:\Users\Brend_000\Dropbox\Blog\utPLSQL\R

C:\Users\Brend_000\Dropbox\Blog\utPLSQL\u

Oracle Unit Testing with utPLSQL 17

utPLSQL Example 2: Web Service Procedure with Batch Denormalisation

Batch job runs to pre-populate a table for performance reasons Test records for each type of driving entity Test each outer-join by including record with missing join Check output table record values Check output table record counts for the input test set

Web service procedure reads the table and outputs a reference cursor Input is an array of records

Array of input arrays for all combinations of inputs Test program calls procedure for each input, then reads the output cursor into

an array Again test counts and record values against expected value arrays

Brendan Furey, 2015