Welcome to the last part of the unit testing serial, the gamification part. If you are still reading this then congratulations, you have the most boring parts behind you.
How to keep writing tests fun?
Let's think about it. As a leader/manager you (should) want your code covered by meaningful tests which won't costs you a lot of money. You don't want useless tests. As a developer you don't want to create too many tests but you want to cover at least the most risky/fragile parts of your code.
Gamification is the answer.
I created some packages for a tower defence game. Then I want to make sure I wont break my code in the future or at least I would be notified when I do before it reaches users. My procedures are usually short but there are some exceptions. Also they usually do more than one thing so they require more than one test. I wrote some tests and realised it is like being half blind. That led me to the code coverage feature (DBMS_PLSQL_CODE_COVERAGE) introduced in Oracle 12.2.
Theory is simple. You start code coverage, run your tests, stop coverage and check not covered rows and write tests for those. As usual it is more complicated in real life. First, I found the results of code coverage inconsistent, unreliable. Maybe I am doing something wrong but not all rows in code coverage report which were accessed by test are marked as covered. So I turned into DBMS_PROFILER with pretty much same results. I somehow don't trust it either. I studied the reports for a while. I was thinking about trying also DBMS_HPROF but I don't want to deal with files and I didn't grasp the file less method yet. Maybe DBMS_TRACE would be useful? So I merged Code Coverage and Profiler reports. It is not perfect but it is close enough. If you come up with a better method please let me know.
DECLARE coverage_id PLS_INTEGER; BEGIN coverage_id := DBMS_PLSQL_CODE_COVERAGE.START_COVERAGE('UNIT_TESTS'); DBMS_OUTPUT.PUT_LINE('COVERAGE_ID: ' || coverage_id); -- DBMS_PROFILER.START_PROFILER(run_comment => 'UNIT_TESTS'); -- ut.run(); -- DBMS_PROFILER.STOP_PROFILER; DBMS_PLSQL_CODE_COVERAGE.STOP_COVERAGE; -- COMMIT; END; /
It is a good practice to set session parameters and force recompile schema before start. Also avoid NULL statements in your code.
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1; -- to not to shift lines ALTER SESSION SET PLSQL_CODE_TYPE = 'INTERPRETED'; ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
Code coverage vs Profiler issues
You may wonder which one is better? Which one should I use?
You can check the code coverage output:
SELECT s.type, s.name, s.line, b.run_id, b.block, --LISTAGG(b.col, ', ') WITHIN GROUP (ORDER BY b.col) OVER (PARTITION BY b.run_id, b.object_id, b.line) AS cols, b.col, b.covered, s.text FROM user_source s LEFT JOIN dbmspcc_units u ON u.name = s.name AND u.type = s.type LEFT JOIN dbmspcc_blocks b ON b.run_id = u.run_id AND b.object_id = u.object_id AND b.line = s.line WHERE s.name = 'PLAYER' -- your package AND s.type = 'PACKAGE BODY' AND u.run_id = (SELECT MAX(r.run_id) FROM dbmspcc_runs r) ORDER BY s.line, b.col;
You can check profiler output:
SELECT u.runid, u.unit_type, u.unit_name, d.line#, d.total_occur, d.total_time, d.min_time, d.max_time, s.text AS source_line FROM plsql_profiler_units u JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number JOIN all_source s ON s.owner = u.unit_owner AND s.name = u.unit_name AND s.type = u.unit_type AND s.line = d.line# WHERE u.runid = (SELECT MAX(u.runid) FROM plsql_profiler_runs u) AND u.unit_owner = USER AND s.name = 'PLAYER' -- your package AND s.type = 'PACKAGE BODY' ORDER BY u.unit_number, d.line#;
Now lets check the code coverage vs profiler coverage:
WITH p AS ( SELECT u.unit_name AS name, d.line# AS line FROM plsql_profiler_units u JOIN plsql_profiler_data d ON d.runid = u.runid AND d.unit_number = u.unit_number --AND d.total_occur > 0 --AND d.total_time > 0 WHERE u.runid = (SELECT MAX(u.runid) FROM plsql_profiler_runs u) AND u.unit_type = 'PACKAGE BODY' AND u.unit_owner = USER AND u.unit_name NOT LIKE '%\_UT' ESCAPE '\' GROUP BY u.unit_name, d.line# --ORDER BY u.unit_name, d.line# ), c AS ( SELECT u.name, b.line FROM dbmspcc_units u JOIN dbmspcc_blocks b ON b.run_id = u.run_id AND b.object_id = u.object_id WHERE u.run_id = (SELECT MAX(r.run_id) FROM dbmspcc_runs r) AND u.type = 'PACKAGE BODY' AND b.covered = 1 GROUP BY u.name, b.line --ORDER BY u.name, b.line ), r AS ( SELECT s.name, s.line, p.line AS profiler_, c.line AS coverage_, s.text FROM user_source s LEFT JOIN p ON p.name = s.name AND p.line = s.line LEFT JOIN c ON c.name = s.name AND c.line = s.line WHERE s.type = 'PACKAGE BODY' -- AND NOT REGEXP_LIKE(s.text, '^\s*([-][-].*)?$') -- ignore empty line AND NOT REGEXP_LIKE(s.text, '^\s*(END[\s;])') -- ignore END; AND NOT REGEXP_LIKE(s.text, '^\s*(BEGIN)') -- ignore BEGIN ) SELECT r.name, COUNT(r.name) AS total_lines, COUNT(r.profiler_) AS prof_lines, ROUND(COUNT(r.profiler_) / COUNT(r.name) * 100, 2) AS prof_perc, COUNT(r.coverage_) AS cov_lines, ROUND(COUNT(r.coverage_) / COUNT(r.name) * 100, 2) AS cov_perc FROM r WHERE r.name IN ('PLAYER') -- your package(s) GROUP BY r.name ORDER BY 1;
Following query will give you a simple ascii chart with the percentage of code covered by tests. One line per package. This should get you motivated to get the percentage up. This should give you the juice to continue in your journey (assuming your packages have "_UT" postfix). And if not, you have to come up with a game which works for you.
SELECT u.type AS object_type, u.name AS object_name, MAX(t.tests) AS unit_tests, ROUND((SUM(b.covered) / COUNT(*) * 100), 2) AS covered_perc, RPAD(LPAD('', (SUM(b.covered) / COUNT(*) * 100), '.'), 100, '.') AS covered_graph FROM dbmspcc_runs r JOIN dbmspcc_units u ON r.run_id = u.run_id JOIN dbmspcc_blocks b ON r.run_id = b.run_id AND u.object_id = b.object_id LEFT JOIN ( SELECT REPLACE(c.object_name, '_UT', '') AS object_name, COUNT(a.subobject_name) AS tests FROM ut3.ut_annotation_cache_info c JOIN ut3.ut_annotation_cache a ON c.object_owner = USER AND a.cache_id = c.cache_id WHERE a.annotation_name = 'test' GROUP BY REPLACE(c.object_name, '_UT', '') ) t ON t.object_name = u.name WHERE r.run_id = (SELECT MAX(run_id) FROM dbmspcc_runs) AND b.not_feasible = 0 AND u.name IN ( SELECT DISTINCT REPLACE(object_name, '_UT', '') AS target_package FROM all_procedures WHERE object_name LIKE '%\_UT' ESCAPE '\' ) GROUP BY u.type, u.name ORDER BY 1, 2;
Create this SELECT statement as ut_code_coverage_report view. Then you can check the charts everytime you run tests.
DECLARE in_name CONSTANT VARCHAR2(30) := 'UNIT_TESTS'; coverage_id PLS_INTEGER; profiler_id PLS_INTEGER; BEGIN coverage_id := DBMS_PLSQL_CODE_COVERAGE.START_COVERAGE(in_name); DBMS_PROFILER.START_PROFILER(in_name, run_number => profiler_id); -- run all available unit tests ut.run(); -- stop DBMS_PROFILER.STOP_PROFILER; DBMS_PLSQL_CODE_COVERAGE.STOP_COVERAGE; -- COMMIT; -- show coverage DBMS_OUTPUT.PUT_LINE('CODE COVERAGE: ' || coverage_id); FOR c IN ( SELECT c.name, c.covered_graph FROM ut_code_coverage_report c ORDER BY 1 ) LOOP DBMS_OUTPUT.PUT_LINE(LPAD(c.name, 10) || ' ' || c.covered_graph); END LOOP; DBMS_OUTPUT.PUT_LINE(''); END; /
Keep in mind that there is no point in doing tests just to increase the numbers. You should always put the quality of your tests first.
Final level, the boss
Now you are at the last level and facing the boss. Literally. It is horrifying.
I have had and I still have ongoing troubles convincing pretty much all of my bosses to invest some time into unit tests. Unsuccessfully. Your boss might be more open minded, but you should prepare good arguments why your work will now take (optimistically) twice as long.
Update (Dec 2021):
I have been neglecting Office Hours lately, so I missed this video from Jacek Gebal and Samuel Nitsche about Testing with PL/SQL