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.<\/p>\n\n\n\n
I’m proud to announce that utPLSQL for SQL Developer<\/a> introduced in version 1.0.0 such a component. Download the latest version from Github<\/a>. At the end of this blog post, you find an audio-less video, showing the realtime reporter (utPLSQL test runner) in Action<\/a>.<\/p>\n\n\n\n In this blog post, I show how the utPLSQL test runner works and explain some design decisions.<\/p>\n\n\n\n We had quite extensive threads in our private utPLSQL Slack design channel about various topics. And it became evident that as soon as we talked about the UI, the number of opinions was 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.<\/p>\n\n\n\n There are currently three options to run utPLSQL tests without installing additional extensions (besides utPSQL for SQL Developer).<\/p>\n\n\n\n Open a worksheet and type all the necessary commands yourself<\/p>\n\n\n\n Select one or more nodes (user, schemas, package specifications, package bodies or package procedures), right-click and select Right-click and select utPLSQL supports an unbounded number of reporters, that can be attached to a test run. For example:<\/p>\n\n\n\n We call the utPLSQL test runner window in SQL Developer also “realtime reporter”, since it shows the results of a test run in near-realtime.<\/p>\n\n\n\n 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 the consumer session.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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 compactly as possible, hence small font and no icon) :<\/p>\n\n\n\n In all other docking areas, an icon appears left to the text and the text font is bigger. It looks like this:<\/p>\n\n\n\n Within the SQL Developer framework (which is based on JDeveloper) certain standards in place are responsible for the final representation.<\/p>\n\n\n\n The components on the toolbar from left to right have the following meaning:<\/p>\n\n\n\n Important: the scope of the rerun buttons in the toolbar is the complete run. Always.<\/p>\n\n\n\n The next screenshot shows the final status of a test run.<\/p>\n\n\n\n At the top, we see a textual status and at the bottom, the progress bar indicates success via a green and failure or error via a red bar. The textual status either ends on In the middle are some counters. All counters have an associated icon, except the first one, You can enable\/disable these additional counters via context menu or in the utPLSQL preferences.<\/p>\n\n\n\n A test has one of the following final states:<\/p>\n\n\n\n The sum of these counters matches the total number of tests.<\/p>\n\n\n\n Additionally, every test may have:<\/p>\n\n\n\n By default, this tabular representation of all tests has the following three columns:<\/p>\n\n\n\n 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 You can enable\/disable these additional indicators via the context menu or in the utPLSQL preferences.<\/p>\n\n\n\n You can select one or more tests and rerun them. Either in realtime reporter or in a new worksheet.<\/p>\n\n\n\n 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, and find failed or slow tests. However, sorting is not easy in a tree. Even a combined tree\/table structure does not help 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n But what happens with results on the suite level? – We ignore them. With two exceptions. Warnings and informational messages on the suite level are included in the last test of the suite. Here’s an example of warnings:<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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:<\/p>\n\n\n\n You can enable\/disable this synchronization via the context menu or in the utPLSQL preferences.<\/p>\n\n\n\n There are 5 tabs with detailed information for a test in the test overview.<\/p>\n\n\n\n In the best case, successful and disabled tests provide further details in this tab only.<\/p>\n\n\n\n The description is empty in this example. However, the utPLSQL team recommends to use of descriptions. I remember when Jacek Gebal gave me once the following feedback regarding my tests for the ut_realtime_reporter<\/a>:<\/p>\n\n\n <\/i>Jacek Gebal<\/a><\/p><\/div>\n\n\n\n\n Good advice. Sounds easier than it is, especially when you want to keep the descriptions short and concise. However, I’m working on it.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n I really like that utPLSQL provides a complete list of all failed assertions and does not stop after the first one like JUnit.<\/p>\n\n\n\n Errors that occur during the execution of a test are reported here. However, if an error occurs on the test suite level, for example in a procedure annotated with Click on a hyperlink to open the associated source code line in the PL\/SQL editor.<\/p>\n\n\n\n When you read the warnings casually then 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 the test level had been reported.<\/p>\n\n\n\n Click on a hyperlink to open the associated source code line in the PL\/SQL editor.<\/p>\n\n\n\n I recommend using 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 ( Important: Warning messages have been introduced in the BTW, I see no reason to work with an old version of utPLSQL, besides the fear of new bugs. utPLSQL is basically stateless, which 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<\/a>. Thanks in advance. ;-)<\/p>\n\n\n\n The utPLSQL framework captures all If these messages contain source code references, then these references are converted to hyperlinks as shown in the tabs In this 2.5 minute audio-less video (in original speed) I run a test suite using utPLSQL for SQL Developer v1.0.0<\/a>. And I fix 1 error, 1 failure,\u00a0 1 warning,\u00a0 2 informational messages and re-enable a disabled test.<\/p>\n\n\n\nContent<\/h2>\n\n\n\n
Running utPLSQL Tests<\/h2>\n\n\n\n
1. Manual<\/h3>\n\n\n\n
![\"Manually](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_manual.png\")
<\/a><\/figure>\n\n\n\n2. From Connections Window<\/h3>\n\n\n\n
Run utPLSQL test<\/code> from the context menu.<\/p>\n\n\n\n![\"Run](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_from_connections.png\")
<\/a><\/figure>\n\n\n\n3. From Editor (PL\/SQL or Worksheet)<\/h3>\n\n\n\n
Run utPLSQL test<\/code> 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<\/code>.<\/p>\n\n\n\n![\"Run](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_editor.png\")
<\/a><\/figure>\n\n\n\nRealtime Reporter (utPLSQL Test Runner)<\/h2>\n\n\n\n
\n
ut_documentation_reporter<\/code> for human-readable output on a console<\/li>\n\n\n\nut_junit_reporter<\/code> for CI environments like Jenkins or Bamboo<\/li>\n\n\n\nut_coverage_html_reporter<\/code> for code coverage report in HTML format<\/li>\n\n\n\nut_realtime_reporter<\/code> for IDEs such as SQL Developer or TOAD<\/li>\n<\/ul>\n\n\n\nDefault Layout<\/h3>\n\n\n\n
![\"Default](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utlsql_default.png\")
<\/a><\/figure>\n\n\n\nComponents<\/h3>\n\n\n\n
Window Title<\/h4>\n\n\n\n
![\"Title](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_title_left.png\")
<\/a><\/figure>\n\n\n\n![\"Title](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_title_other.png\")
<\/a><\/figure>\n\n\n\nToolbar<\/h4>\n\n\n\n
![\"Toolbar\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_toolbar.png\")
<\/a><\/figure>\n\n\n\n\n
![\"refresh\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/refresh.png\")
<\/a> Reset ordering and refresh: Restores default sort order and deselects all tests.<\/li>\n\n\n\n![\"run\"\/](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/run.png\")
<\/a> Rerun all tests: Reruns all tests shown in the current realtime reporter, the selected tests are not changing the behaviour, because it would be cumbersome to rerun all tests.<\/li>\n\n\n\n![\"run](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/run_worksheet.png\")
<\/a> Rerun all tests in new worksheet: The same logic as the previous button, but runs the tests in a new worksheet.<\/li>\n\n\n\n![\"combobox\"\/](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/combobox.png\")
<\/a> Run history: The identifier of a run is the start time and the connection name in parenthesis. The last ten runs are kept by default. Select another run anytime. The UI should never be blocked.<\/li>\n\n\n\n![\"clear\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/clear.png\")
<\/a> Clear run history: Clears all history entries, except the currently selected run.<\/li>\n<\/ul>\n\n\n\nRun Status<\/h4>\n\n\n\n
![\"Run](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_run_status.png\")
<\/a><\/figure>\n\n\n\n...<\/code> or .<\/code> as an additional indicator for the completeness of a test run.<\/p>\n\n\n\nTests<\/code>, to reserve space for larger numbers (e.g. 4242\/4242<\/code>). All counters have the same width to represent them as columns, e.g when including additional counters like here:<\/p>\n\n\n\n![\"Run](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_run_status_2.png\")
<\/a><\/figure>\n\n\n\n![\"Run](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_run_status_3.png\")
<\/a><\/figure>\n\n\n\nTest States<\/h5>\n\n\n\n
\n
![\"success\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/success.png\")
<\/a> success: the expected value matches the actual value<\/li>\n\n\n\n![\"failure\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/failure.png\")
<\/a> failure: the expected value does not match the actual value<\/li>\n\n\n\n![\"error\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/error.png\")
<\/a> error: there was an error during the test execution<\/li>\n\n\n\n![\"disabled\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/disabled.png\")
<\/a> disabled: the test is not enabled and therefore not executed<\/li>\n<\/ul>\n\n\n\nWarnings and Informational Messages<\/h5>\n\n\n\n
\n
![\"warning\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/warning.png\")
<\/a> warnings:\n\n
![\"info\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/info.png\")
<\/a> info:\n\n
DBMS_OUTPUT<\/code> messages by the program under test or by the test code.<\/li>\n\n\n\ninfo<\/code> and not DBMS_OUTPUT<\/code> or server output<\/code> or simply output<\/code>? Well, there are some practical reasons. info<\/code> is short and has a nice, known icon. Besides that, we name here the content\/severity<\/strong> (informational message, that does not affect the outcome of a test) and not the transportation mechanism<\/strong> (DBMS_OUTPUT<\/code>). As a result, this is concise with failure<\/code>, error<\/code>\u00a0 and\u00a0 warning<\/code>.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\nTest Overview<\/h4>\n\n\n\n
![\"Test](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_test_overview.png\")
<\/a><\/figure>\n\n\n\n\n
![\"test](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/status.png\")
<\/a> Test status:\n\n
\n
<\/a> success<\/li>\n\n\n\n<\/a> failure<\/li>\n\n\n\n<\/a> error<\/li>\n\n\n\n<\/a> disabled<\/li>\n<\/ul>\n<\/li>\n\n\n\n\n
plscope.test.test_lineage_util.test_<\/code>.<\/li>\n\n\n\n\n
<\/a> in the toolbar. Ascending sort order for test status means: <\/a>, , <\/a>, <\/a>.<\/a><\/p>\n\n\n\nWarnings and Informational Messages<\/h5>\n\n\n\n
![\"Run](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_test_overview_2.png\")
<\/a><\/figure>\n\n\n\nRerun Selected Tests<\/h5>\n\n\n\n
![\"Rerun](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_test_overview_3.png\")
<\/a><\/figure>\n\n\n\n
<\/a><\/p>\n\n\n\nTable or Tree?<\/h5>\n\n\n\n
Test Results on Suite Level<\/h5>\n\n\n\n
![\"Warnings](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_test_overview_4.png\")
<\/a><\/figure>\n\n\n\nOpen PL\/SQL Editor<\/h5>\n\n\n\n
Synchronize Detail Tab Based on Test Status<\/h5>\n\n\n\n
\n
Failures<\/code> if number of failures > 0<\/li>\n\n\n\nErrors<\/code> if errors is not empty<\/li>\n\n\n\nWarnings<\/code> if warnings is not empty<\/li>\n\n\n\nInfo<\/code> if info is not empty<\/li>\n\n\n\nTest<\/code> in all other cases<\/li>\n<\/ul>\n\n\n\nTest Details<\/h4>\n\n\n\n
1. Test<\/h5>\n\n\n\n
![\"\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_detail_test.png\")
<\/a><\/figure>\n\n\n\nRather than describing what the test is checking:
--%test(Check XML report structure)<\/code>, describe the tested code functionality:\u00a0--%test(Builds appropriate XML report structure)<\/code>. That way, when executing the tests, we see a list of descriptions for functionalities (requirements) that are working.<\/blockquote>2. Failures<\/h5>\n\n\n\n
![\"\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_detail_failures.png\")
<\/a><\/figure>\n\n\n\n3. Errors<\/h5>\n\n\n\n
![\"Errors\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_detail_errors.png\")
<\/a><\/figure>\n\n\n\n%afterall<\/code>, then these errors are considered warnings by the utPLSQL framework and are reported in the warnings tab.<\/p>\n\n\n\n4. Warnings<\/h5>\n\n\n\n
![\"Warnings\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_detail_warnings.png\")
<\/a><\/figure>\n\n\n\nat package \"PLSCOPE.TEST_LINAGE_UTIL\", 20<\/code>) it’s clear. But without the package<\/code> token, utPLSQL for SQL Developer assumes that the package body is meant.<\/p>\n\n\n\nut_realtime_reporter<\/code> of utPLSQL v3.1.7. Therefore warning messages are empty if you use v3.1.4, v3.1.5 or v3.1.6.<\/p>\n\n\n\n5. Info<\/h5>\n\n\n\n
![\"Info\"](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2019\/07\/running_utplsql_detail_info.png\")
<\/a><\/figure>\n\n\n\nDBMS_OUTPUT<\/code> messages.<\/p>\n\n\n\nFailures<\/code>, Errors<\/code> and Warnings<\/code>.<\/p>\n\n\n\n<\/a>Realtime Reporter in Action<\/h2>\n\n\n\n