Testing Framework for Query

1
(No Transcript)
2
Effectively Validate Query/Report Strategy and
Tool
Session id 36993

• Steven Luo
• Sr. System Analyst
• Barnes Noble

3
Agenda

• PART I Introduction
• Why, when, and how to validate
• PART II Strategy
• Basic strategy
• Advanced strategy
• PART III Tool
• Test script
• Engine - sqlUnit
• PART IV Conclusion

4
Why to Validate?

• Assure Data Accuracy
• Managers need accurate data to make strategic
  decisions
• A companys sales team needs accurate data to
  launch market campaign
• Assure ETL (Extraction, Transformation, and
  Loading) Process Correctness
• ETL tools
• SQL scripts

5
When to Validate?

• Any of the following types of testing needs
  validation
• Unit testing
• Integration testing
• System testing
• Acceptance testing
• Maintenance regression testing

6
How to Validate?
7
Agenda

• PART I Introduction
• Why, when, and how to validate?
• ?PART II Strategy
• Basic strategy
• Advanced strategy
• PART III Tool
• Test script
• Engine - sqlUnit
• PART IV Conclusion

8
Validating Report/Query

• A Query or Report is actually a result set, so
  validating needs to answer two questions
• Are you getting the result set right?
• right data in each cell
• Are you getting the right result set?
• exact number of records

9
A Typical Process of Generating Reports
ETL steps
Stored procedure
report
staging tables
materialized view
flat files
T0
T1
Tn
...
10
Basic Strategy

• Assert that each (cell) in the result set matches
  the data in the source table
• Number
• String
• Date
• Result Set, etc.
• Sampling should be used if a result set is big
• Assert the right number of records in the result
  set
• Check duplicated

11
Advanced Strategy

• Try to uncover invalid data item
• Boundary Validation
• Count, sum, max, min, x not in table,
  between..and on whole resultset or certain
  partitions.
• Special Value (constraints) Validation
• isXXX() and notXXX()
• e.g. IsNull, notNull, notNegative, notZero
• Business Rule Validation
• dept1.salegt dept2.sale

12
Agenda

• PART I Introduction
• Why, when, and how to validate ?
• PART II Strategy
• Basic Strategy
• Advanced Strategy
• ?PART III Tool
• Test Script
• Engine - sqlUnit
• PART IV Conclusion

13
Why Use a Validation Tool?

• Automate the validating process
• Reduces the cost, time and effort
• Reuse the procedures
• Write once, run many times on QA box and/or
  production box.
• Re-factor SQL
• Share by group (save to PVCS)

14
Tool

• Open Source
• jUnit, etc.
• Steven Feuersteins utPL/SQL
• My tool -- sqlUnit

15
sqlUnit Overview

• The framework consists of two major parts
• test scripts
• test engine
• implemented in java stored procedure with PL/SQL
  interface
• Implement 2 types of strategy
• basic strategy
• advanced strategy
• Record the validating results
• Monitor long-running validation process

16
sqlUnit Overview (2)

• From the Engine Perspective
• run all your test cases defined in your PL/SQL
  package
• From the User Perspective
• write all test scripts
• start the Engine

17
Architecture
Engine
Assert
Test scripts

util
Database
18
Test Script (1)

• Write Test script in PL/SQL
• Define test package
• Call APIs
• Define test procedures
• Test procedure MUST begin with test
• Setup()
• Teardown() clean up

19
Test Script (2)

• Use your business knowledge to get expected data
  and actual data
• Get raw or original data from source table
• such as POS, Daily Sales, etc.
• Get data from a report
• call API fetchCursorData(), or
• using cursor directly, e.g.

cc my_test_pkg.get_ref_cursor('SCOTT') loop
fetch cc into value1,value2,..., valuek
exit when ccnotfound if(...) then
sqlunit.assert(desc, value1, 100) end if
end loop
20
APIs for Basic Strategy
procedure runTestCases(testPackageName
varchar2) procedure assert(description varchar2,
num1 number, num2 number) procedure
assert(description varchar2, str1 varchar2, str2
varchar2) procedure assert(description varchar2,
a1 STRING_ARRAY, a2 STRING_ARRAY) procedure
assert(description STRING_ARRAY, a1 STRING_ARRAY,
a2 STRING_ARRAY) procedure assertQuery(description
varchar2, query1 varchar2, query2 varchar2)
procedure fetchRefCursorInto(pname in varchar2,
parameters in
STRING_ARRAY,
fetchfields in out STRING_ARRAY,
uniqField in varchar2,
uniqValue in varchar2 )
function getCountForQuery(sqlstr varchar2)
return number function getCountForProcedure(sqlstr
varchar2) return number
21
Test Script Template
22
Apply Basic Strategy

• A tool should pick up
• m column(s) in a row of a result set
• m between 1 and number of columns
• m column(s) in n rows of a result set
• m between 1 and number of columns
• n between 2 and number of rows
• Cells to be validated
• cells between 1 and mn

23
(No Transcript)
24
Apply Advanced Strategy

• Advanced Strategy
• Boundary Object
• Special value (Constraint)Object
• Business Rule Object

25
Test Script for Advance Strategy
procedure test_adv_1 is obj
sqlunitBoundary sqlunitBoundary(NULL,NULL,NULL,
NULL) begin obj.setTestingQuery('my
_test_pkg.get_ref_cursor(''SCOTT'')')
obj.setCountCriteria(2)
obj.setQueryCriteria('c1 is not null')
obj.setQueryType(1) //1 store procedure. 0
sql query obj.checkBoundary
exception when others then
dbms_output.put_line('exception! ')
end test_adv_1
26
Example for Validating Two Queries
27
How Engine Works

• Users start the Engine by calling
• exec sqlunit.runtestcases('VALIDATEPACKAG
  E')
• Engine calls back test scripts by calling the
  following
• set_up test_1 tear_down
• set_up test_2 tear_down
• ...
• set_up test_n tear_down
• Test scripts call Framework APIs .
• sqlunit.assert(...)
• sqlunit.assert(...)
• sqlunit.assert(...)

28
Sequence Diagram
testPackage
sqlUnit/engine
DB
Runtestcases()
setup
test_1
assert
recordResult
tear_down
setup
test_2
recordResult
assert
tear_down
View results
29
Record the Test Result

• Use package name as testing result table name.

30
(No Transcript)
31
Monitoring Validation Process

• At Engine level, by instrumentation
  DBMS_APPLICATION_INFO into Engine, you can
  monitor the progress
• At test script, you can instrument
  DBMS_APPLICATION_INFO to test scripts too.
• Get the progress information from
  vsession_longop in other session

32
Test Scripts Guideline

• Be simple
• Dont use the same sql that generates the report
• Use Business/domain knowledge

33
Conclusion

• Data accuracy is very important
• Use automated validating tool whenever possible

34
Limitation of validation

• Program testing can be used to show the
  presence of bugs, but never to show their
  absence
• --E. W.
  Dijkstra

35
References

• Asimkumar Munshi, Testing a Data Warehouse
  Application white paper http//www.wipro.com/insig
  hts/testingaDWApplication.htm
• B. Hailpern and P. Santhanam Software
  debugging, testing, and verification IBM System
  Journal Vol. 41, No. 1, 2002
• Thomas Kyte, Expert One on One Oracle, Wrox,
  2001

36
A
37
(No Transcript)