In November 2017 Jacek Gebal asked me if I could help to integrate utPLSQL into SQL Developer. In January 2018 we released the first MVP. Tests were executed in a new SQL Developer worksheet showing the result in the script output pane.This was easy to implement and it simplified the use of utPLSQL. But there are some downsides to that:
While we introduced a couple of new features such as test templates, test generation or code coverage; we never addressed these primary flaws. Surely you have heard the motto “keep the bar green to keep the code clean”. It refers to the JUnit test runner IDE component that displays a progress bar which remains green as long as all tests are passed.
I’m proud to announce that utPLSQL for SQL Developer introduced in version 1.0.0 such a component. Download the latest version from Github. At the end of this blog post you find an audioless video, showing the realtime reporter (utPLSQL test runner) in Action.
In this blog post I show how the utPLSQL test runner works and explain some design decisions.
We had quite extensive threads in our private utPLSQL Slack design channel about various topics. And it became evident that as soon as we talk about the UI, the number of opinions is quite consistent with the number of people involved. So this blog post should help me (and hopefully others as well) to simplify some future discussions around this topic.
There are currently three options to run utPLSQL tests without installing additional extensions (beside utPSQL for SQL Developer).
Open a worksheet and type all the necessary commands yourself
Select one or more nodes (user, schemas, package specifications, package bodies or package procedures), right-click and select Run utPLSQL test
from the context menu.
Right-click and select Run utPLSQL test
from the context menu in the editor containing a test package or test package body (or both). The cursor position determines the test or suite to be executed. In the following example it’s the package procedure test_linage_util.test_target_cols_from_insert
.
utPLSQL supports an unbounded number of reporters, that can be attached to a test run. For example:
ut_documentation_reporter
for human readable output on a consoleut_junit_reporter
for CI environments like Jenkins or Bamboout_coverage_html_reporter
for code coverage report in HTML formatut_realtime_reporter
for IDEs such as SQL Developer or TOADWe call the utPLSQL test runner window in SQL Developer also “realtime reporter”, since it shows the results of a test run in near-realtime.
The realtime reporter uses for every run two fresh connections to the database. One to run the tests (producer) and the other to read the results continuously (consumer). For a good user experience, it is important that you save the password of the connections used for utPLSQL within SQL Developer. Otherwise you will be prompted for the password. In fact twice. For the producer and for the consumer session.
By default, you find the dockable window next to the Connections window. At this position we want a narrow window, so that there is enough space on the right side for editors. You can move this window to any position you like and change its size. SQL Developer saves the settings on exit. The following screenshot shows the realtime reporter with default settings.
We use Java Swing components. It’s possible to use others, but SQL Developer uses mostly Swing components as well. Hence, we decided to go this way as long as there is no pressing reason to change that.
The visualization of Swing components depends heavily on the chosen Look & Feel (e.g. on Windows the progress bar is segmented). The default Look & Feel for SQL Developer is on all platforms “Oracle”. On Windows you may change it to “Windows”, on macOS to “Mac OS X” and on Ubuntu to “Metal”. In this blog post I use the Oracle Look & Feel. It works best on all platforms.
The representation of the window title depends on the window position. Within a tabbed pane on the leftmost docking area it looks like this (in this area we would like to present titles as compact as possible, hence small font and no icon) :
In all other docking areas an icon appears left to the text and the text font is bigger. It looks like this:
Within the SQL Developer framework (which is based on JDeveloper) there are certain standards in place that are responsible for the final representation.
The components on the toolbar from left to right have the following meaning:
Important: the scope of the rerun buttons in the toolbar is the complete run. Always.
The next screenshot shows the final status of a test run.
At the top we see a textual status and at the bottom the progress bar indicating success via a green and failure or error via a red bar. The textual status either ends on ...
or .
as an additional indicator for the completeness of a test run.
In the middle are some counters. All counters have an associated icon, except the first one, Tests
, to reserve space for larger numbers (e.g. 4242/4242
). All counters have the same width to represent them as columns, e.g when including additional counters like here:
You can enable/disable these additional counters via context menu or in the utPLSQL preferences.
A test has one of the following final states:
The sum of these counters matches the total number of tests.
Additionally every test may have:
DBMS_OUTPUT
messages by the program under test or by the test code.info
and not DBMS_OUTPUT
or server output
or simply output
? Well, there are some practical reasons. info
is short and has a nice, known icon. Beside that, we name here the content/severity (informational message, that does not affect the outcome of a test) and not the transportation mechanism (DBMS_OUTPUT
). As a result, this is concise with failure
, error
and warning
.By default, this tabular representation of all tests has the following three columns:
plscope.test.test_lineage_util.test_
.You may sort the overview table by clicking on a column. The first click sorts them ascending, the second click descending. To restore the original order, press in the toolbar. Ascending sort order for test status means:
,
,
,
.
You can enable/disable these additional indicators via context menu or in the utPLSQL preferences.
You can select one or more tests and rerun them. Either in realtime reporter or in a new worksheet.
utPLSQL test suites are hierarchies. A tree view would be a natural choice, right? Well, yes for the hierarchical representation this is correct. We already use that in the oddgen integration. Sorting is a simple way to group tests, find failed or slow tests. However, sorting is not easy in a tree. Even a combined tree/table structure is not helpful to sort the complete result set. And there are other issues. Filtering tests and presenting a well-arranged result, for instance. Because of these limitations, we decided to go with a simple table.
That said, I think, that we should provide rather sooner than later an alternative hierarchical view to give the test descriptions (representing features/requirements) a better context. The suite descriptions are currently lost and this is really sad for projects that have good suite and test descriptions.
But what happens with results on suite level? – We ignore them. With two exceptions. Warnings and informational messages on suite level are included in the last test of the suite. Here’s an example for warnings:
Double-click on a test in the test overview table to open the PL/SQL package specification at the line of the associated test procedure.
By default, the most relevant detail tab of a test is opened automatically. As a result you do not need to browse through all detail tabs. Behind the scenes, we use this rule set:
Failures
if number of failures > 0Errors
if errors is not emptyWarnings
if warnings is not emptyInfo
if info is not emptyTest
in all other casesYou can enable/disable this synchronization via context menu or in the utPLSQL preferences.
There are 5 tabs with detailed information for a test in the test overview.
In the best case, successful and disabled tests provide further details in this tab only.
The description is empty in this example. However, the utPLSQL team recommends to use descriptions. I remember when Jacek Gebal gave me once the following feedback regarding my tests for the ut_realtime_reporter:
Rather than describing what the test is checking:--%test(Check XML report structure)
, describe the tested code functionality:--%test(Builds appropriate XML report structure)
. That way, when executing the tests, we see a list of descriptions for functionalities (requirements) that are working.
Good advice. Sounds easier than it is, especially when you want to keep the descriptions short and concise. However, I’m working on it.
A test may have an unbounded number of asserts. Each failed assert is listed in the failures table. And for each failed assert, you find a detailed failure message. You can either double click on the row in the failed assert table or click on the hyperlink to open the PL/SQL editor at the line of the failed assert.
I really like that utPLSQL provides a complete list of all failed assert and does not stop after the first one like JUnit.
Errors that occur during the execution of a test are reported here. However, if an error occurs on test suite level, for example in a procedure annotated with %afterall
, then these errors are considered warnings by the utPLSQL framework and are reported in the warnings tab.
Click on a hyperlink to open the associated source code line in the PL/SQL editor.
When you read the warnings casually than you might get the impression that the rollback warning is reported twice. However, the first warning was for the rollback after test
. The second warning was for the rollback after test suite
. The last test of a suite also contains the warnings at the suite level. For example, the warning about the incomplete --%tags
would have been lost, if only warnings at test level had been reported.
Click on a hyperlink to open the associated source code line in the PL/SQL editor.
I recommend to use utPLSQL v3.1.8 or later (which will be released soon). Starting with this version, utPLSQL answers the following question regarding the link to the source code: Do I have to open the package specification or the package body? In this case (at package "PLSCOPE.TEST_LINAGE_UTIL", 20
) it’s clear. But without the package
token, utPLSQL for SQL Developer assumes that the package body is meant.
Important: Warning messages have been introduced in the ut_realtime_reporter
of utPLSQL v3.1.7. Therefore warning messages are empty, if you use v3.1.4, v3.1.5 or v3.1.6.
BTW, I see no reason to work with an old version of utPLSQL, beside the fear of new bugs. utPLSQL is basically stateless, this means there is no data to be migrated. Hence, a complete reinstall is always feasible and the annotation cache will be recalculated automatically. If you really stumble over a critical bug, then fix it by installing the previous version. It is simple enough. And don’t forget to let us know. Thanks in advance. ;-)
The utPLSQL framework captures all DBMS_OUTPUT
messages.
If these messages contain source code references, then these references are converted to hyperlinks as shown in the tabs Failures
, Errors
and Warnings
.
In this 2.5 minute audioless video (in original speed) I run a test suite using utPLSQL for SQL Developer v1.0.0. And I fix 1 error, 1 failure, 1 warning, 2 informational messages and re-enable a disabled test.