W
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;
The secret
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;
/
Final advice
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.
Good luck.
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
Comments
Post a Comment