{"id":6841,"date":"2016-04-23T14:48:09","date_gmt":"2016-04-23T12:48:09","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=6841"},"modified":"2023-11-07T22:39:34","modified_gmt":"2023-11-07T21:39:34","slug":"monitoring-plsql-code-evolution-with-plsql-cop-for-sonarqube","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2016\/04\/23\/monitoring-plsql-code-evolution-with-plsql-cop-for-sonarqube\/","title":{"rendered":"Monitoring PL\/SQL Code Evolution"},"content":{"rendered":"\n

Last week I presented the PL\/SQL Cop tool suite to a customer in Germany. While preparing the demo\u00a0I had taken my first deeper look at the PL\/SQL Cop SonarQube plugin<\/a>, written by Peter Rohner, a fellow Trivadian. I\u00a0was impressed\u00a0by how well the additional PL\/SQL Cop metrics integrate into SonarQube and how easy it is to monitor the code evolution.<\/p>\n\n\n\n

Before I show the code evolution I will go through the metric definitions based on a fairly simple example. If you are not interested in the math, then feel free to skip reading the metric sections.<\/p>\n\n\n\n

Password_Check, Version 0.1<\/h2>\n\n\n\n

As a starting point, I use the following simplified password verification\u00a0procedure, which ensures that every password contains a digit. I know this procedure is not a candidate for “good PL\/SQL code”, but 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.<\/p>\n\n\n\n

Password_Check, v0.1<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> PROCEDURE PASSWORD_CHECK (in_password <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>IS<\/span> <\/span>-- NOSONAR<\/span><\/span>\n   co_digitarray <\/span>CONSTANT<\/span> STRING(<\/span>10<\/span>)     := <\/span>'0123456789'<\/span>;<\/span><\/span>\n   co_one        <\/span>CONSTANT<\/span> <\/span>SIMPLE_INTEGER<\/span> := <\/span>1<\/span>;<\/span><\/span>\n   co_errno      <\/span>CONSTANT<\/span> <\/span>SIMPLE_INTEGER<\/span> := -<\/span>20501<\/span>;<\/span><\/span>\n   co_errmsg     <\/span>CONSTANT<\/span> STRING(<\/span>100<\/span>)    := <\/span>'Password must contain a digit.'<\/span>;<\/span><\/span>\n   <\/span>l_isdigit<\/span>     <\/span>BOOLEAN<\/span>;<\/span><\/span>\n   <\/span>l_len_pw<\/span>      <\/span>PLS_INTEGER<\/span>;<\/span><\/span>\n   <\/span>l_len_array<\/span>   <\/span>PLS_INTEGER<\/span>;<\/span><\/span>\nBEGIN<\/span><\/span>\n   <\/span>-- initialize variables<\/span><\/span>\n   <\/span>l_isdigit<\/span> := <\/span>FALSE<\/span>;<\/span><\/span>\n   <\/span>l_len_pw<\/span> := <\/span>LENGTH<\/span>(in_password);<\/span><\/span>\n   <\/span>l_len_array<\/span> := <\/span>LENGTH<\/span>(co_digitarray);<\/span><\/span>\n   <<check_digit>><\/span><\/span>\n   <\/span>FOR<\/span> i <\/span>IN<\/span> co_one .. <\/span>l_len_array<\/span><\/span>\n   <\/span>LOOP<\/span><\/span>\n      <<check_pw_char>><\/span><\/span>\n      <\/span>FOR<\/span> j <\/span>IN<\/span> co_one .. <\/span>l_len_pw<\/span><\/span>\n      <\/span>LOOP<\/span><\/span>\n         <\/span>IF<\/span> <\/span>SUBSTR<\/span>(in_password, j, co_one) = <\/span>SUBSTR<\/span>(co_digitarray, i, co_one) <\/span>THEN<\/span><\/span>\n            <\/span>l_isdigit<\/span> := <\/span>TRUE<\/span>;<\/span><\/span>\n            <\/span>GOTO<\/span> check_other_things;<\/span><\/span>\n         <\/span>END IF<\/span>;<\/span><\/span>\n      <\/span>END LOOP<\/span> check_pw_char;<\/span><\/span>\n   <\/span>END LOOP<\/span> check_digit;<\/span><\/span>\n   <<check_other_things>><\/span><\/span>\n   <\/span>NULL<\/span>;<\/span><\/span>\n   <\/span><\/span>\n   <\/span>IF<\/span> <\/span>NOT<\/span> <\/span>l_isdigit<\/span> <\/span>THEN<\/span><\/span>\n      <\/span>raise_application_error<\/span>(co_errno, co_errmsg);<\/span><\/span>\n   <\/span>END IF<\/span>;<\/span><\/span>\nEND<\/span> password_check;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
\"password_check_v0.1\"<\/a><\/figure>\n\n\n\n
Simple Metrics<\/h4>\n\n\n\n
Here are the definitions of the simple metrics shown above.<\/p>\n\n\n\n
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<\/a> and the SQL Developer extension<\/a> of SQL Cop.<\/p>\n\n\n\n