Last week I’ve presented the PL/SQL Cop tool suite to a customer in Germany. While preparing the demo I had taken my first deeper look at the PL/SQL Cop SonarQube plugin, written by Peter Rohner, a fellow Trivadian. I was impressed how well the additional PL/SQL Cop metrics integrate into SonarQube and how easy it is to monitor the code evolution.
Before I show the code evolution I will go through the metric definitions based on fairly simple example. If you are not interested in the math, then feel free to skip reading the metric sections.
As a starting point I use the following simplified password verification procedure, which ensures that every password contains a digit. I know this procedure is not a candidate for “good PL/SQL code”, but nonetheless it is based on a real live example. The goal of this piece of code is to explain some metrics, before starting to improve the code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
CREATE OR REPLACE PROCEDURE PASSWORD_CHECK (in_password IN VARCHAR2) IS -- NOSONAR co_digitarray CONSTANT STRING(10) := '0123456789'; co_one CONSTANT SIMPLE_INTEGER := 1; co_errno CONSTANT SIMPLE_INTEGER := -20501; co_errmsg CONSTANT STRING(100) := 'Password must contain a digit.'; l_isdigit BOOLEAN; l_len_pw PLS_INTEGER; l_len_array PLS_INTEGER; BEGIN -- initialize variables l_isdigit := FALSE; l_len_pw := LENGTH(in_password); l_len_array := LENGTH(co_digitarray); <<check_digit>> FOR i IN co_one .. l_len_array LOOP <<check_pw_char>> FOR j IN co_one .. l_len_pw LOOP IF SUBSTR(in_password, j, co_one) = SUBSTR(co_digitarray, i, co_one) THEN l_isdigit := TRUE; GOTO check_other_things; END IF; END LOOP check_pw_char; END LOOP check_digit; <<check_other_things>> NULL; IF NOT l_isdigit THEN raise_application_error(co_errno, co_errmsg); END IF; END password_check; / |
After running this code through PL/SQL Cop I get the following metrics. I show here just the SonarQube output, but the results are the same for the command line utility and the SQL Developer extension of SQL Cop.
Here are the definitions of the simple metrics shown above.
Simple metrics such as Lines of Code are an easy way to categorise the programs in a project. But the program with the most lines of code does not necessarily have to be the most complex one. Other metrics are better suited to identify the complex parts of a project. But it is important to have a good idea how such metrics are calculated, because no single metric is perfect. I typically identify programs to have a closer look at by a combination of metrics such as lines of code, statements, cyclomatic complexity and the number of severe issues.
See SonarQube documentation for the further metric definitions. Please note, that PL/SQL Cop does not calculate all metrics and some metrics are calculated a bit differently, e.g. Comment Lines.
SonarQube rates a project using the SQALE Rating which is is based on the Technical Dept Ratio and calculated as follows:
$$\text'tdr' = 100⋅{\text'Technical Debt'}/{\text'Development Cost'}$$where
The ranges for the SQALE Rating values A (very good) to E (very bad) are based on the SQALE Method Definition Document. SonarQube uses the default rating scheme “0.1,0.2,0.5,1” which may be amended on the Technical Debt page. The rating scheme defines the rating thresholds for A, B, C and D. Higher values lead to an E rating. Here is another way to represent the default rating scheme:
Based on the default SQALE Rating scheme, a project rated as “E” should be rewritten from scratch, since it would take more time to fix all issues.
Thomas J. McCabe introduced 1976 the metric Cyclomatic Complexity which counts the number of paths in the source code. SonarQube uses this metric to represent the complexity of a program. PL/SQL Cop calculates the cyclomatic complexity as follows:
$$M=E-N+2P$$where
The higher the cyclomatic complexity, the more difficult it is to maintain the code.
Please note that PL/SQL Cop V1.0.16 adds an additional edge for ELSE branches in IF/CASE statements, for PL/SQL blocks and for GOTO statements. I consider this a bug. However, Toad Code Analysis (Xpert) calculates the Cyclomatic Complexity the very same way.
PL/SQL Cop calculates the Cyclomatic Complexity per program unit and provides the aggregated Max. Cyclomatic Complexity on file level.
Maurice H. Halstead introduced 1977 the metric Halstead Volume which defines the complexity based on the vocabulary and the total number of words/elements used within a program. In his work Halstead showed also how to express the complexity of academic abstracts using his metrics. PL/SQL Cop calculates the Halstead volume as follows:
$$V=N⋅log_2n$$where
using
The higher the Halstead volume, the more difficult it is to maintain the code.
PL/SQL Cop calculates the Halstead Volume per program unit and provides the aggregated Max. Halstead Volume on file level.
Paul Oman and Jack Hagemeister introduced 1991 the metric Maintainability Index which weighs comments and combines it with Halstead Volume and Cyclomatic Complexity. PL/SQL Cop calculates the maintainability index as follows:
$$\text'MI'=\text'MI'woc+\text'MI'cw$$where
The lower the maintainability index, the more difficult it is to maintain the code.
PL/SQL Cop calculates the Maintainability Index per program unit and provides the aggregated Min. Maintainability Index on file level.
To get rid of the GOTO I’ve rewritten the procedure to use regular expressions to look for digits within the password. The code looks now as follows:
1 2 3 4 5 6 7 |
CREATE OR REPLACE PROCEDURE PASSWORD_CHECK (in_password IN VARCHAR2) IS BEGIN IF NOT REGEXP_LIKE(in_password, '\d') THEN raise_application_error(-20501, 'Password must contain a digit.'); END IF; END; / |
After loading the new version into SonarQube, the dashboard looks as follows:
Almost all metrics look better now. But instead of 1 major issue I have now 5 minor ones. This leads to a higher Technical Dept Ratio and a bad trend in this area. So let’s see what these minor issues are.
I consider all guideline violations as not worth to fix and marked them as “won’t fix”. After reloading the unchanged password_check.sql the SonarQube dashboard looks as follows:
The differences/improvement to the previous version is shown in parenthesis.
The version 0.2 code looks really good. No technical debt, no issues. A complexity of 2 and just 7 lines of code. But is it possible to improve this code further? Technically yes, especially since we know how the Maintainability Index is calculated. We could simply reduce the Lines of Code as follows:
1 2 |
CREATE OR REPLACE PROCEDURE PASSWORD_CHECK(in_password IN VARCHAR2)IS BEGIN IF NOT REGEXP_LIKE(in_password,'\d')THEN raise_application_error(-20501,'Password must contain a digit.');END IF;END; / |
And after loading the new version into SonarQube the dashboard looks as follows:
Reducing the number of lines from 7 to 2 leads to better Maintainability Index, but the number of statements, the Cyclomatic Complexity and Halstead Volume are still the same. The change from version 0.2 to 0.3 reduces the readability of the code and has a negative value. That clearly shows, that the Maintainability Index has its flaws (see also https://avandeursen.com/2014/08/29/think-twice-before-using-the-maintainability-index/). There are various ways to discourage such kind of changes in a project. Using a formatter/beautifier with agreed settings is my favourite.
SonarQube shows the metrics of the latest two versions in the dashboard. Use the Time Machine page to show metrics of more than two versions of project.
Or use the Compare page to compare metrics between versions or projects.
Every metric has its flaws, for example
But these metrics are still useful to identify complex programs, to measure code evolution (improvements, degradations) and to help you writing better PL/SQL, if you do not trust in metrics blindly.