wtPLSQL is a white-box testing framework for Oracle database objects.
Following are some links regarding the history of utPLSQL.
Steven Feuerstein Designed and Developed utPLSQL (V1)
Steven Feuerstein’s Recommendations for Unit Testing PL/SQL Programs
Because of his reputation with Oracle’s PL/SQL, Steven Feuerstein’s utPLSQL has been widely adopted. However, maintenance of the utPLSQL source code became a problem with the latest utPLSQL V2 releases. Inspection of the utPLSQL V2 source code revealed some very complex code pathways. Much of this resulted from the layering of the V1 API on top of the V2 implementation. There is no documentation on how the V1 layering was intended to work. There is no documentation on the overall design of the V2 implementation. There is no documentation on how to use the V2 API. (Kudos to @PaulWalkerUK for an amazing job of maintaining the V2 code set.) As a result, most all unit tests written with utPLSQL V2 use the V1 APIs.
The utPLSQL V3 project was started with a “clean sheet” approach. The project took a distinctly object oriented direction. This is apropos, given that Steven Feuerstein subtitles utPLSQL as “JUnit for PLSQL”. The V3 project has also adopted other aspects of JUnit testing like annotations. It is a clever and useful approach and will be familiar to Java developers. @jgebal was part of the utPLSQL V3 development from the beginning and continues to provide excellent contributions and information for that project.
Before the “clean sheet” approach was adopted, the V3 team reviewed what has been published as the utPLSQL_Lite project. The utPSQL_Lite project was an effort to create a simplified utPLSQL core with the use of options/add-ons to achieve additional functionality.
The wtPLSQL project is a continuation of the utPLSQL_Lite project.
This project focuses on providing a simple, yet robust, framework for dynamic, white box testing of Oracle Database Objects.
The wtPLSQL project is an attempt to allow PL/SQL developers to be PL/SQL developers. The test runners are entirely user-written in PL/SQL. The framework supplies resources for collecting and reporting information from those test runners. Through its simplified architecture and open source approach, extensions of the functionality are relatively easy.
Robustness is the ability of a computer system to cope with errors during execution
The wtPLSQL framework includes provisions for the following errors during execution:
Testing that takes place when the program itself is run.
The wtPLSQL framework supports testing of source code during its execution. That is, the source code is executed during testing. It is not a static code analyzer or a guide for review meetings.
Tests internal structures or workings of a program
The essence of white box testing is the careful testing of the application at the source code level to prevent any hidden errors later on. A key measure of completeness for this kind of testing is the code coverage of the test. A complete white box test will achieve 100% code coverage. This does not guarantee all aspects of the code have been tested, but it does ensure that all code pathways have been tested.
An important part of establishing code coverage is identifying what code is being tested. The wtPLSQL framework uses a DBOUT (DataBase Object Under Test) to identify the code being tested. Upon identifying the DBOUT, the framework can gather and report information regarding code coverage.
Many kinds of database objects need to be tested, not just packages. Triggers containing PL/SQL need to be tested. With the addition of inline functions in SQL, views can contain PL/SQL as well. Oracle Type Bodies also include PL/SQL procedures and functions. All of these database objects can be tested with wtPSQL.
In the wtPLSQL framework, the DBOUT can be any of the following PL/SQL objects:
With utPLSQL V1/V2, packages can include an embedded self-test. The required calls can be exposed within the package that is being tested. This is particularly useful for testing package internals like private variables and procedures. These embedded selftests also remove the need to expose private variables and procedures to public calls so they can be tested.
wtPLSQL continues this capability. However, with wtPLSQL, the addition of an embedded selftest requires only 1 additional procedure call in the package specification (WTPLSQL_RUN).
As mentioned above, white box testing can occur at various levels of development, including:
The wtPLSQL project focuses on white box testing instead of unit testing in order to avoid some controversial aspects of unit testing, namely Test Isolation and Test Transience.
A unit test should usually not go outside of its own class boundary
In OO (object oriented) programming, object data is transient. This is due to the nature of object instantiation. Persistence of object data beyond the instance of an object is banished to non-OO components. Since the unit test movement gained its largest following in OO, the idea of testing persisted object data is, unfortunately, a distraction. This has evolved into the idea that testing a database interface should always involve the use of a fake or mock to isolate the unit under test from the influence of these non-OO components.
Transactional data (ACID compliance) introduces a complexity to the persistence of object data. Attempting to fake this complexity is very difficult. Particularly difficult is the determination of how much functionality to include in the fake, especially when the storage of the data is the main purpose for the system. Focusing on white box testing, instead of unit testing, allows the wtPLSQL framework to test integrated functionality from other system components.
A unit test should set up a known good state before the tests and return to the original state after the tests
There are many arguments to be made regarding the idea of a known good state in a database. The only sure way to achieve a known good state is to leave the the database unchanged after a unit test. Ideally, changes made by a test process would be transient, that is the process would setup (insert) and tear down (delete) data in the database. However, many Oracle database implementations include additional functionality that can make this difficult.
In the wtPLSQL framework, integration testing of multiple database objects (no mocks or fakes) is allowed (i.e. not bound by the transience aspect). Artifacts from multiple test runs can remain in the database after the testing is complete. Additionally, artifacts that remain after testing can help identify other problems in the database.
A test fixture … is the set of preconditions or state needed to run a test
A test suite is a set of tests that all share the same fixture.
Test fixtures and test suites are a part of the xUnit testing framework. At the core, wtPLSQL does not include test fixtures or test suites. If needed, these can be easily defined and implemented in a test runner package.
With TDD (Test Driven Development), you write a test before you write just enough production code to fulfill that test
The wtPLSQL framework is not intended for Test Driven Development. The wtPLSQL framework embraces 100% code coverage and does not require test isolation or test transience.