Last October I gave a presentation on database unit testing with utPLSQL, Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively.
In the current set of articles, I develop the ideas further, starting from the idea that all database APIs can be considered in terms of the axes:
- direction (i.e. getter or setter, noting that setters can also ‘get’)
- mode (i.e. real time or batch)
For each cell in the implied matrix, I construct an example API (or view) with specified requirements against Oracle’s HR demo schema, and use this example to construct a testing program with appropriate scenarios as a design pattern. Concepts and common patterns and anti-patterns in automated API testing are discussed throughout, and these are largely independent of testing framework used. However, the examples use my own lightweight independent framework that is designed to help avoid many API testing anti-patterns. The code is available on GitHub here, BrenPatF/trapit_oracle_tester, and includes both framework and design pattern examples.
Behind the four examples, there is an underlying design pattern that involves wrapping the API call in a ‘pure’ procedure, called once per scenario, with the output ‘actuals’ array including everything affected by the API, whether as output parameters, or on database tables, etc. The inputs are also extended from the API parameters to include any other effective inputs. Assertion takes place after all scenarios and is against the extended outputs, with extended inputs also listed. This concept of the ‘pure’ function, central to Functional Programming, has important advantages in automated testing. I explained the concepts involved in a presentation at the Oracle User Group Ireland Conference in March 2018:
The Database API Viewed As A Mathematical Function: Insights into Testing
In this 2-part article, I present a design pattern for testing views. I start by discussing when and how to test views. Unlike in the design paatern of the first article in the series, Design Patterns for Database API Testing 1: Web Service Saving – Design, test data has to be created during testing of views, and a very general approach to creating and selecting the test data is proposed. The use case for the design pattern is described, and scenarios and sub-scenarios are defined conceptually. Finally, the output from the testing is presented, with notes.
The second post provides some code extracts, with notes: Design Patterns for Database API Testing 2: Views 2 – Code.
When to Test Views
Views can be simple or complex, or, as I categorise them in Brendan’s 2-Page Oracle Programming Standards, thin or thick, where thick views include table joins and thinviews don’t. Thin views do not normally require testing while it may or may not be appropriate to test thick views.
As explained in the second part of the first article mentioned above, method-based testing is a bad idea, and occurs when the test suite is based on testing all the methods in a package, rather than units of (external) behaviour (often corresponding to procedures prefixed AIP in a common naming convention). Similarly, we can consider views in the same way as methods and ask whether they represent testable units of behaviour or are merely internal code structures, which should not normally have individual automated tests for the reasons given there.
Good examples of views that should be tested would be those that form the basis of complex data extraction to file, by ETL tools such as Informatica, or those that form the basis of reporting tools such as Business Objects. In fact, it is very good practice to place SQL for these tools into views precisely so that they can be tested.
How to Test Views Using a PL/SQL Testing Framework
In order to leverage a PL/SQL API testing framework to also test views, the API test package procedures call a library procedure passing the name of the relevant view: The library procedure returns the result of querying the view as an array of delimited strings, and the API test procedures then compare the results against their own expected results.
Each API test procedure will have its own setup local procedure to create test data, and we need to discuss the issue of distinguishing test data from pre-existing data.
Test Data
In the earlier article on database save procedures, we did not create any test data within the testing code itself, but the base procedure did create data, and those were queried back for assertion. In order to select only the data created by the procedure call a prefix was used in one of the string fields which was assumed not to exist already. This is a workable approach in some cases, but may not be possible in general. Let us consider the different types of database data that may affect our testing:
- Data created by the base code being tested
- Data created by test code to be read by the base code
- Data not created by test code to be read by base code
In order to verify that the program calls are giving results as expected, the test code needs to know all the data that influence the results, not necessarily just directly created data. Our view testing use case described below has an example where the results depend on an aggregate of all the records on the database. This is a problem when we have a shared database, where we cannot freeze the data at the time of test development. In order to handle this problem, we propose to borrow a technique used in Oracle’s ebusinees applications.
Partitioning Views with System Contexts
In Oracle ebusiness’s multi-org implementations, transactions are partitioned by a numeric identifier for the organization owning the transaction. This org_id value is stored in a column in the base table on transaction creation. Within the application code the base table is not queried directly, but through a view that restricts records returned to those for the organization corresponding to the current role of the application user, which is stored in the userenv system context (this is true up to release 11.5, but the mechanism changed in release 12.1).
See SYS_CONTEXT for information on the system context database feature, and Oracle E-Business Suite Multiple Organizations Implementation Guide (12.1) for release 12.1 multi-org implementation in Oracle ebusiness.
Partitioning Views for Testing
We propose to use views in a similar way to the multi-org views, to restrict records to those created in the testing session, by means of a ttid column on the base table that will hold the session id. The new optional column is added to those tables where this approach is required, and view are created on the tables. Our testing utility package Utils_TT sets a context variable to the value ‘TT’ to signify testing mode, and the session id is set to a package variable in the general utilities package Utils.
Any base code that inserts data into the tables has to check for test mode, and if set, put the session id into the ttid field, and if not, leave it blank. The views use the following clause:
WHERE (ttid = SYS_Context ('userenv', 'sessionid') OR
Substr (Nvl (SYS_Context ('userenv', 'client_info'), 'XX'), 1, 2) != 'TT')
Both test code and base code now query the views instead of the base tables. As the base code to write to the tables has to account for the new column, it is necessary for the column to be added in all instances including production. If this seems a little drastic, consider the importance that you attach to testing, and bear in mind that the earlier, less general, approaches may suffice in many cases. In these design pattern demos I use the general solution.
Schema Structure
In the earlier articles, the base code and test packages were created in the HR schema, with utility packages kept in the custom brendan schema. However, it is more common to use separate schemas for code and data, so we will now place all packages and supporting objects in the brendan schema, and create the testing views there.
Design Pattern Use Case for Testing Views
Modern Oracle SQL is very powerful and can apply complex logic within a single statement, reducing the need for more complex procedural code. In order to show how to test SQL, we will devise a test view, HR_Test_V, having a range of features that we might want to test in general:
- Inner joins suppress driving records where there is no joining record
- Outer joins return driving records where there is no joining record
- Analytic functions that partition by some key, and return aggregates on the returned record set
- Functions based on aggregates over records that include those not in the returned record set
- Constraints based on aggregates over records that include those not in the returned record set
- Constraints on column values
The view functionality can be described in words as:
- Selected values
- Employee name, department name, and salary
- Manager’s name
- Ratio of employee’s salary to the department average (returned employees only)
- Ratio of employee’s salary to the average salary of all employees
- Constraints
- Exclude employees in job ‘AD_ASST’
- Exclude employees without a department
- Do not return any records if the total salary of all employees is below 1600
- Outer join
- Include employees both with and without a manager
The view SQL is:
CREATE OR REPLACE VIEW hr_test_view_v AS
WITH all_emps AS (
SELECT Avg (salary) avg_sal, SUM (salary) sal_tot_g
FROM employees e
)
SELECT e.last_name, d.department_name, m.last_name manager, e.salary,
Round (e.salary / Avg (e.salary) OVER (PARTITION BY e.department_id), 2) sal_rat,
Round (e.salary / a.avg_sal, 2) sal_rat_g
FROM all_emps a
CROSS JOIN employees e
JOIN departments d
ON d.department_id = e.department_id
LEFT JOIN employees m
ON m.employee_id = e.manager_id
WHERE e.job_id != 'AD_ASST'
AND a.sal_tot_g >= 1600
Scenarios and Sub-scenarios
Scenario definition
Following our earlier article, we may define a scenario as being the set of all relevant records, both on the database and passed as parameters, to a single program call. API or view testing involves creating one or more scenarios, calling the program (or executing the process) for each scenario, and verifying that the output records are as expected.
Good testing is achieved when the scenarios are chosen to validate as wide a range of behaviours as possible. It is not always, or usually, necessary to create a new scenario for each aspect of behaviour to be tested.
Sub-scenario definition
Often, several features can be tested in the same program call by setting up different records in the scenario that will independently test the different features. For example, in our use case above we can create employees with and without a department, and with and without a manager in the same scenario to test the different types of join.
It may be helpful to think of these separate records, or fields within a record, as corresponding to sub-scenarios, and try to construct scenarios as efficiently as possible without making more calls than necessary.
View Test Output
Data setup section
SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep, Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job ========================================================================================================================================================================================= # Employee id Department id Manager Job id Salary - ----------- ------------- ---------- ---------- ---------- 1 1493 10 IT_PROG 1000 2 1494 10 1493 IT_PROG 2000 3 1495 1493 IT_PROG 3000 4 1496 10 1493 AD_ASST 4000 SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep, Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20) =============================================================================================================================================================== # Employee id Department id Manager Job id Salary - ----------- ------------- ---------- ---------- ---------- 1 1497 10 IT_PROG 1000 2 1498 10 1497 IT_PROG 2000 3 1499 1497 IT_PROG 3000 4 1500 10 1497 AD_ASST 4000 5 1501 20 1497 IT_PROG 5000 SCENARIO 3: DS-2, passing 'WHERE dep=10', Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20) ================================================================================================================================== # Employee id Department id Manager Job id Salary - ----------- ------------- ---------- ---------- ---------- 1 1502 10 IT_PROG 1000 2 1503 10 1502 IT_PROG 2000 3 1504 1502 IT_PROG 3000 4 1505 10 1502 AD_ASST 4000 5 1506 20 1502 IT_PROG 5000 SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600), Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600 ============================================================================================================================================================================= # Employee id Department id Manager Job id Salary - ----------- ------------- ---------- ---------- ---------- 1 1507 10 IT_PROG 100 2 1508 10 1507 IT_PROG 200 3 1509 1507 IT_PROG 300 4 1510 10 1507 AD_ASST 400 5 1511 20 1507 IT_PROG 500
Notes on data setup section
- There are three data sets, and four scenarios, each of which references a data set
- The call to set up the data for a scenario writes out all the data created
- A header provides a description of the features (or sub-scenarios) in the data set
- In the output above scenarios 2 and 3 use the same data set, DS-2
Results section
SQL>BEGIN
Utils.Clear_Log;
Utils_TT.Run_Suite (Utils_TT.c_tt_suite_bren);
EXCEPTION
WHEN OTHERS THEN
Utils.Write_Other_Error;
END;
/
SQL> @L_Log_Default
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRAPIT TEST: TT_View_Drivers.tt_HR_Test_View_V
==============================================
Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job
==========================================================================================
# Employee id Department id Manager Job id Salary
- ----------- ------------- ---------- ---------- ----------
1 1518 10 IT_PROG 1000
2 1519 10 1518 IT_PROG 2000
3 1520 1518 IT_PROG 3000
4 1521 10 1518 AD_ASST 4000
Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================
# Employee id Department id Manager Job id Salary
- ----------- ------------- ---------- ---------- ----------
1 1522 10 IT_PROG 1000
2 1523 10 1522 IT_PROG 2000
3 1524 1522 IT_PROG 3000
4 1525 10 1522 AD_ASST 4000
5 1526 20 1522 IT_PROG 5000
Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)
========================================================================================
# Employee id Department id Manager Job id Salary
- ----------- ------------- ---------- ---------- ----------
1 1527 10 IT_PROG 1000
2 1528 10 1527 IT_PROG 2000
3 1529 1527 IT_PROG 3000
4 1530 10 1527 AD_ASST 4000
5 1531 20 1527 IT_PROG 5000
Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600
================================================================================================================
# Employee id Department id Manager Job id Salary
- ----------- ------------- ---------- ---------- ----------
1 1532 10 IT_PROG 100
2 1533 10 1532 IT_PROG 200
3 1534 1532 IT_PROG 300
4 1535 10 1532 AD_ASST 400
5 1536 20 1532 IT_PROG 500
SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep {
===============================================================================================
INPUTS
======
GROUP Employee {
================
Employee Id Last Name Email Hire Date Job Salary Manager Id department Id
----------- --------- ----- --------- ------- ------ ---------- -------------
1518 LN_1 EM_1 09-JUL-16 IT_PROG 1000 10
1519 LN_2 EM_2 09-JUL-16 IT_PROG 2000 1518 10
1520 LN_3 EM_3 09-JUL-16 IT_PROG 3000 1518
1521 LN_4 EM_4 09-JUL-16 AD_ASST 4000 1518 10
}
=
GROUP Where {
=============
Where
-----
}
=
OUTPUTS
=======
GROUP Select results: Actual = 2, Expected = 2 {
================================================
F? Name Department Manager Salary Salary Ratio (dep) Salary Ratio (overall)
-- ---- -------------- ------- ------ ------------------ ----------------------
LN_1 Administration 1000 .67 .4
LN_2 Administration LN_1 2000 1.33 .8
} 0 failed, of 2: SUCCESS
=========================
} 0 failed, of 2: SUCCESS
=========================
SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep {
=======================================================================
INPUTS
======
GROUP Employee {
================
Employee Id Last Name Email Hire Date Job Salary Manager Id department Id
----------- --------- ----- --------- ------- ------ ---------- -------------
1522 LN_1 EM_1 09-JUL-16 IT_PROG 1000 10
1523 LN_2 EM_2 09-JUL-16 IT_PROG 2000 1522 10
1524 LN_3 EM_3 09-JUL-16 IT_PROG 3000 1522
1525 LN_4 EM_4 09-JUL-16 AD_ASST 4000 1522 10
1526 LN_5 EM_5 09-JUL-16 IT_PROG 5000 1522 20
}
=
GROUP Where {
=============
Where
-----
}
=
OUTPUTS
=======
GROUP Select results: Actual = 3, Expected = 3 {
================================================
F? Name Department Manager Salary Salary Ratio (dep) Salary Ratio (overall)
-- ---- -------------- ------- ------ ------------------ ----------------------
LN_1 Administration 1000 .67 .33
LN_2 Administration LN_1 2000 1.33 .67
LN_5 Marketing LN_1 5000 1 1.67
} 0 failed, of 3: SUCCESS
=========================
} 0 failed, of 3: SUCCESS
=========================
SCENARIO 3: DS-2, passing 'WHERE dep=10' {
==========================================
INPUTS
======
GROUP Employee {
================
Employee Id Last Name Email Hire Date Job Salary Manager Id department Id
----------- --------- ----- --------- ------- ------ ---------- -------------
1527 LN_1 EM_1 09-JUL-16 IT_PROG 1000 10
1528 LN_2 EM_2 09-JUL-16 IT_PROG 2000 1527 10
1529 LN_3 EM_3 09-JUL-16 IT_PROG 3000 1527
1530 LN_4 EM_4 09-JUL-16 AD_ASST 4000 1527 10
1531 LN_5 EM_5 09-JUL-16 IT_PROG 5000 1527 20
}
=
GROUP Where {
=============
Where
--------------------------------
department_name='Administration'
}
=
OUTPUTS
=======
GROUP Select results: Actual = 2, Expected = 2 {
================================================
F? Name Department Manager Salary Salary Ratio (dep) Salary Ratio (overall)
-- ---- -------------- ------- ------ ------------------ ----------------------
LN_1 Administration 1000 .67 .33
LN_2 Administration LN_1 2000 1.33 .67
} 0 failed, of 2: SUCCESS
=========================
} 0 failed, of 2: SUCCESS
=========================
SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600) {
=============================================================
INPUTS
======
GROUP Employee {
================
Employee Id Last Name Email Hire Date Job Salary Manager Id department Id
----------- --------- ----- --------- ------- ------ ---------- -------------
1532 LN_1 EM_1 09-JUL-16 IT_PROG 100 10
1533 LN_2 EM_2 09-JUL-16 IT_PROG 200 1532 10
1534 LN_3 EM_3 09-JUL-16 IT_PROG 300 1532
1535 LN_4 EM_4 09-JUL-16 AD_ASST 400 1532 10
1536 LN_5 EM_5 09-JUL-16 IT_PROG 500 1532 20
}
=
GROUP Where {
=============
Where
-----
}
=
OUTPUTS
=======
GROUP Select results: Actual = 0, Expected = 0: SUCCESS
=======================================================
} 0 failed, of 1: SUCCESS
=========================
TIMING: Actual = 48, Expected <= 1: FAILURE
===========================================
SUMMARY for TT_View_Drivers.tt_HR_Test_View_V
=============================================
Scenario # Failed # Tests Status
--------------------------------------------------------------------------------- -------- ------- -------
DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep 0 2 SUCCESS
DS-2, testing same as 1 but with extra emp in another dep 0 3 SUCCESS
DS-2, passing 'WHERE dep=10' 0 2 SUCCESS
DS-3, Salaries total 1500 (< threshold of 1600) 0 1 SUCCESS
Timing 1 1 FAILURE
--------------------------------------------------------------------------------- -------- ------- -------
Total 1 9 FAILURE
--------------------------------------------------------------------------------- -------- ------- -------
Timer Set: TT_View_Drivers.tt_HR_Test_View_V, Constructed at 09 Jul 2016 13:32:42, written at 13:32:42
======================================================================================================
[Timer timed: Elapsed (per call): 0.01 (0.000013), CPU (per call): 0.02 (0.000020), calls: 1000, '***' denotes corrected line below]
Timer Elapsed CPU Calls Ela/Call CPU/Call
------- ---------- ---------- ------------ ------------- -------------
Setup 0.11 0.00 4 0.02675 0.00000
Caller 0.19 0.06 4 0.04750 0.01500
(Other) 0.03 0.03 1 0.02700 0.03000
------- ---------- ---------- ------------ ------------- -------------
Total 0.32 0.09 9 0.03600 0.01000
------- ---------- ---------- ------------ ------------- -------------
Notes on results section
- In a view test there is only one group, namely the selected data set
The second part of the article is here: Design Patterns for Database Unit Testing 2: Views 2 - Code