I had the privilege of discussing obstacles to testing our PL/SQL code with Samuel Nitsche (aka, Der Pesse). About Sam:
Samuel Nitsche is a curiosity-driven software developer who programs, learns and collaborates in the software trade since the early 2000s. He works as a Senior Software Developer and trainer at Smart Enterprise Solutions GmbH, a tiny company in South Germany. His main interest is modern database development, automated testing and code quality, topics he writes regularly about on different platforms (e.g. his blog https://developer-sam.de, Simple-Talk, and several Oracle-related print magazines). Samuel is an Oracle ACE, as well as one of the main contributors and maintainers of utPLSQL (http://utplsql.org). He loves to share his experience in an entertaining way – gladly in Sith robes – at meetups and conferences.
Please watch and/or listen to the recording for all details of our conversation.
Some Sam thoughts you might find interesting:
- If you leave testing for after you finish coding, then you have left the most boring part for the end – of course you’re not going to do it, or much of it!
- Don’t think of testing as something separate from coding. It’s all part of the same process of building applications.
- Treating testing as different from coding is like trying to get kids to eat their vegetables. If you keep them separate from everything else, and left to the end, then the veggies are cold and, of course, kids will hate veggies.
- Mix the veggies with the rest of the meal, and then everything is eaten with gusto!
- Sam introduced us to the Lump of Code Fallacy, a fascinating way of looking at and thinking about test-driven development from GeePaw. Have you ever heard of GAK (geek at keyboard)? I had not. Check it out!
And from me:
- Trying a few things is different from testing. But at a minimum, save every “thing” (block of code, test case, whatever) so that you can try it again in the future.
- Automated testing, regression testing, TDD: they can all sound and be really intimidating.
- But once you start doing some of that sort of testing, a very powerful, positive feedback loop gets started.
- We get such a good feeling out of seeing test results, and running them more than once, that we are encouraged to do more.
- The easiest programs to test are deterministic functions. It’s actually pretty easy to build your own simple test harness for these. I offer an example below.
Sam and I didn’t nearly get to all the thoughts bouncing around in our heads on this topic, so we will do a follow up in the near future!
Simple test harness
The APEXd2d team needed to clean up the text entered by a user for their twitter handle. By which I mean:
- @sfonplsql -> sfonplsql
- https://twitter.com/sfonplsql -> sfonplsql
- etc.
Here’s the function:
create or replace function clean_twitter_handle (
p_handle in varchar2
) return varchar2 is
begin
return
case
when instr(p_handle, '/') > 0 then
substr(p_handle, instr(p_handle, '/', -1) + 1)
when instr(p_handle, '@') > 0 then
substr(p_handle, instr(p_handle, '@', -1) + 1)
else p_handle
end;
end;
And here’s an anonymous block that tests for various scenarios and shows the results.
declare
k_program constant varchar2(100) := 'clean_twitter_handle';
type results_t is
table of varchar2(1000) index by pls_integer;
l_results results_t;
l_success boolean := true;
l_failure_count integer := 0;
procedure test_it (
p_test_case in varchar2,
p_handle in varchar2,
p_expected in varchar2,
p_show_success in boolean default true
) is
l_value varchar2(1000);
begin
l_value := clean_twitter_handle(p_handle => p_handle);
if l_value = p_expected or (
l_value is null and p_expected is null
) then
if p_show_success then
l_results(l_results.count + 1) := p_test_case || ' succeeded';
end if;
else
l_results(l_results.count + 1) := p_test_case || ' failed';
l_success := false;
l_failure_count := l_failure_count + 1;
end if;
end;
procedure summarize_test is
l_title varchar2(1000) := lpad('*', 5, '*')
|| ' Results from testing '
|| k_program
|| ' '
|| lpad('*', 5, '*');
begin
DBMS_OUTPUT.put_line(l_title);
DBMS_OUTPUT.put_line('*');
if l_success then
DBMS_OUTPUT.put_line('* SUCCESS - All test cases passed.');
else
DBMS_OUTPUT.put_line('* FAILURE - '
|| l_failure_count
|| ' test cases failed.');
end if;
DBMS_OUTPUT.put_line('*');
for indx in 1..l_results.count loop
DBMS_OUTPUT.put_line('* ' || l_results(indx));
end loop;
DBMS_OUTPUT.put_line('*');
DBMS_OUTPUT.put_line(lpad('*', length(l_title), '*'));
end;
begin
test_it('Just as we want it', 'sfonplsql', 'sfonplsql');
test_it('Leading @', '@sfonplsql', 'sfonplsql');
test_it('Leading /', '/sfonplsql', 'sfonplsql');
test_it('Full url', 'https://twitter.com/sfonplsql', 'sfonplsql');
summarize_test;
end;
/
I run it and see this output:
***** Results from testing clean_twitter_handle ***** * * SUCCESS - All test cases passed. * * Just as we want it succeeded * Leading @ succeeded * Leading / succeeded * Full url succeeded * *****************************************************
A little harness generator
I spent some time last week abstracting the specifics you see above to a utility that will generate a test harness for your function – you then finish it up with some formatting and elaboration of test cases. It’s not nearly as good or useful as utPLSQL, but it might get you excited to do more testing. You can download and try it out right here.
Here’s an example of the code it generates, using this block:
begin
test_utils.initialize_test_cases_for ('CLEAN_TWITTER_HANDLE', null);
test_utils.add_test_case (
p_test_case_name =>'Just as we want it',
p_expected =>'sfonplsql'
);
test_utils.add_test_case (
p_test_case_name =>'Leading @ with @sfonplsql',
p_expected =>'sfonplsql'
);
test_utils.add_test_case (
p_test_case_name =>'Leading / with /sfonplsql',
p_expected =>'sfonplsql'
);
test_utils.add_test_case (
p_test_case_name =>'Full URL with https://twitter.com/sfonplsql',
p_expected =>'sfonplsql'
);
test_utils.generate_tester_for_deterministic_function(
p_function_name => 'CLEAN_TWITTER_HANDLE',
p_package_name => null,
p_overload => null,
p_owner => user
);
end;
/
The output:
declare
k_program constant varchar2(100) := 'SFEUERSTEIN.CLEAN_TWITTER_HANDLE';
type results_t is
table of varchar2(1000) index by pls_integer;
l_results results_t;
l_success boolean := true;
l_failure_count integer := 0;
procedure test_it (
p_test_case in varchar2,
P_HANDLE in varchar2,
p_expected in varchar2,
p_show_success in boolean default true
) is
l_value varchar2(1000);
begin
l_value := SFEUERSTEIN.CLEAN_TWITTER_HANDLE(P_HANDLE => P_HANDLE);
if l_value = p_expected or (
l_value is null and p_expected is null
) then
if p_show_success then
l_results(l_results.count + 1) := p_test_case || ' succeeded';
end if;
else
l_results(l_results.count + 1) := p_test_case || ' failed';
l_success := false;
l_failure_count := l_failure_count + 1;
end if;
end;
procedure summarize_test is
l_title varchar2(1000) := lpad('*', 5, '*')
|| ' Results from testing '
|| k_program
|| ' '
|| lpad('*', 5, '*');
begin
DBMS_OUTPUT.put_line(l_title);
DBMS_OUTPUT.put_line('*');
if l_success then
DBMS_OUTPUT.put_line('* SUCCESS - All test cases passed.');
else
DBMS_OUTPUT.put_line('* FAILURE - '
|| l_failure_count
|| ' test cases failed.');
end if;
DBMS_OUTPUT.put_line('*');
for indx in 1..l_results.count loop
DBMS_OUTPUT.put_line('* ' || l_results(indx));
end loop;
DBMS_OUTPUT.put_line('*');
DBMS_OUTPUT.put_line(lpad('*', length(l_title), '*'));
end;
begin
test_it(
p_test_case => 'Just as we want it',
p_expected => 'sfonplsql',
p_show_success => TRUE,
P_HANDLE => 'Value_for_P_HANDLE'
);
test_it(
p_test_case => 'Leading @ with @sfonplsql',
p_expected => 'sfonplsql',
p_show_success => TRUE,
P_HANDLE => 'Value_for_P_HANDLE'
);
test_it(
p_test_case => 'Leading / with /sfonplsql',
p_expected => 'sfonplsql',
p_show_success => TRUE,
P_HANDLE => 'Value_for_P_HANDLE'
);
test_it(
p_test_case => 'Full URL with https://twitter.com/sfonplsql',
p_expected => 'sfonplsql',
p_show_success => TRUE,
P_HANDLE => 'Value_for_P_HANDLE'
);
summarize_test;
end;
Perhaps you will find this useful. Let me know!
