PL/SQL Unit Testing Can Be Fun (with a little help from Ruby)

Presentation at Miracle Open World 2012

Transcript

1. PL/SQL Unit Testing Can Be Fun! with a little help from Ruby
2. Raimonds Simanovskis github.com/rsim @rsim .com
3. What is good code? Correct Fast enough Maintainable Test functionality! Test performance! Test after changes!
4. How is it done? try something... dbms_output.put_line(...) it is obvious that there are no bugs testers should test, that’s their job
5. What are typical problems? Trying and not testing Tests are not repeatable Manual verification of results Testing is done too late in development
6. Types of tests unit tests integration tests performance & load tests exploratory & usability tests programmer’s responsibility
7. Test Driven Development
8. Good unit tests Automatic, run fast Wide code coverage including edge cases Repeatable Independent from execution order Using real and understandable test data
9. How to do it when programming in PL/SQL?
10. created by Steven Feuerstein, 1999 based on “xUnit”-style frameworks not maintained anymore :(
11. Example substring from start until end position CREATE OR REPLACE FUNCTION betwnstr ( string_in IN VARCHAR2, start_in IN INTEGER, end_in IN INTEGER ) RETURN VARCHAR2 IS l_start PLS_INTEGER := start_in; BEGIN IF l_start = 0 THEN l_start := 1; END IF; RETURN (SUBSTR (string_in, l_start, end_in - l_start + 1)); END;
12. Tests CREATE OR REPLACE PACKAGE ut_betwnstr AS PROCEDURE ut_setup; PROCEDURE ut_teardown; PROCEDURE ut_normal_usage; PROCEDURE ut_first_index_null; END ut_betwnstr; CREATE OR REPLACE PACKAGE BODY ut_betwnstr AS PROCEDURE ut_setup AS BEGIN NULL; END ut_setup; PROCEDURE ut_teardown AS BEGIN NULL; END ut_teardown; PROCEDURE ut_normal_usage AS BEGIN utassert.eq('Normal usage', betwnstr('abcdefg', 2, 5), 'bcde'); END ut_normal_usage; PROCEDURE ut_first_index_null AS BEGIN utassert.isnull('First index is null', betwnstr('abcdefg', NULL, 5)); END ut_first_index_null; END ut_betwnstr;
13. Results exec utplsql.test('betwnstr', recompile_in => FALSE); . > SSSS U U CCC CCC EEEEEEE SSSS SSSS > S S U U C C C C E S S S S > S U U C C C C E S S > S U U C C E S S > SSSS U U C C EEEE SSSS SSSS > S U U C C E S S > S U U C C C C E S S > S S U U C C C C E S S S S > SSSS UUU CCC CCC EEEEEEE SSSS SSSS . SUCCESS: "betwnstr" . > Individual Test Case Results: > SUCCESS - betwnstr.UT_FIRST_INDEX_NULL: ISNULL "First index is null" Expected "" and got "" > SUCCESS - betwnstr.UT_NORMAL_USAGE: EQ "Normal usage" Expected "cde" and got "cde" > > > Errors recorded in utPLSQL Error Log: > > NONE FOUND
14. Visual testing tools Quest Code Tester SQL Developer >= 2.1
15. Why used just by few? Too large / too verbose test code? Hard to read, too much noise? Hard to test complex cases? No best practices how to write tests? Nobody is using, why should I use?
16. ruby-plsql-spec ideal language for writing tests powerful testing tools with “readable” syntax library for calling PL/SQL procedures from Ruby RSpec ruby-plsql
17. Demo
18. ruby-plsql gem plsql.connect! "hr","hr","xe" plsql.test_uppercase('xxx') # => "XXX" plsql.test_uppercase(:p_string => 'xxx') # => "XXX" plsql.test_copy("abc", nil, nil) # => { :p_to => "abc", # :p_to_double => "abcabc" } plsql.test_copy(:p_from => "abc", :p_to => nil, :p_to_double => nil) # => { :p_to => "abc", # :p_to_double => "abcabc" } plsql.hr.test_uppercase('xxx') # => "XXX" plsql.test_package.test_uppercase('xxx') # => 'XXX' plsql.hr.test_package.test_uppercase('xxx') # => 'XXX' plsql.logoff
19. Benefits Compact, readable syntax Powerful features also for complex tests Best practices from Ruby community Based on needs from real projects Open-source – “free as in beer” :)
20. More information http://github.com/rsim/ruby-plsql-spec http://blog.rayapps.com/2010/01/06/screencasts-of- oracle-plsql-unit-testing-with-ruby/ http://blog.rayapps.com/2009/11/27/oracle-plsql- unit-testing-with-ruby/