A Comprehensive Framework for Testing Database-Centric Software Applications Gregory M. Kapfhammer Department of Computer Science University of Pittsburgh PhD Dissertation Defense April 19, 2007 PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 1 

Dissertation Committee Director: Dr. Mary Lou Soffa (University of Virginia) Dr. Panos Chrysanthis (University of Pittsburgh) Dr. Bruce Childers (University of Pittsburgh) Dr. Jeffrey Voas (SAIC) With support and encouragement from countless individuals! PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 2 

Motivation The Risks Digest, Volume 22, Issue 64, 2003 Jeppesen reports airspace boundary problems About 350 airspace boundaries contained in Jeppesen NavData are incorrect, the FAA has warned. The error occurred at Jeppe- sen after a software upgrade when information was pulled from a database containing 20,000 airspace boundaries worldwide for the March NavData update, which takes effect March 20. Important Point: Practically all use of databases occurs from within applica- tion programs [Silberschatz et al., 2006, pg. 311]. PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 3 

Research Contributions Comprehensive framework that tests a program’s interaction with the complex state and structure of a database Database interaction fault model Database-aware representations Test adequacy Test coverage monitoring Regression testing Worst-case analysis of the algorithms and empirical evaluation with six case study applications PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 4 

Traditional Software Testing System Operating File System Virtual P Machine Graphical Interface Database Byte Code Execution Environment Input a 5 print ... exit Final Result: 45 Output Defects (e.g., bugs, faults, errors) can exist in program P and all aspects of P’s environment PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 5 

Testing Environment Interactions File System Virtual Machine P Database System Operating Execution Environment Input a 5 print ... exit Final Result: 45 Output Defects can also exist in P’s interaction with its environment PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 6 

Focus on Database Interactions 1 D e D P m update select insert delete Program P can view and/or modify the state of the database PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 7 

Types of Applications Interaction Approach Program Location Database−Centric Applications Embedded Inside DBMS Interface Outside DBMS Testing framework relevant to all types of applications Current tool support focuses on Interface-Outside applications Example: Java application that submits SQL Strings to HSQLDB relational database using JDBC drivers PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 8 

Research Contributions Database Interaction Fault Model Test Adequacy Criteria Test Coverage Monitoring Regression Testing Reduction Prioritization PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 9 

Database Interaction Faults: (1-v) P m actual before after expected insert update P uses update or insert to incorrectly modify items within database Commission fault that violates database validity Database-aware adequacy criteria can support fault isolation PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 10 

Database Interaction Faults: (1-c) P m actual before after expected delete P uses delete to remove incorrect items from database Commission fault that violates database completeness Database-aware adequacy criteria can support fault isolation PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 11 

Data Flow-Based Test Adequacy select * from R delete R where A > 100 from i m 3 6 P use(R) define(R) The intraprocedural database interaction association n3, n6, R exists within method mi PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 12 

Research Contributions Database Interaction Fault Model Test Adequacy Criteria Test Coverage Monitoring Regression Testing Reduction Prioritization PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 13 

Test Adequacy Component P, C Representation Constructor ICFG Database Interaction Analyzer Database-Aware Representation Constructor Database Entities Database Database Interaction ICFG (DI-ICFG) Data Flow Analyzer Database Interaction Associations Process: Create a database-aware representation and perform data flow analysis Purpose: Identify the database interaction associations (i.e., the test requirements) PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 14 

Database-Aware Representation exit G G G G r r2 r 2 r1 1 entry entry exit lockAccount update_lock = m_connect.createStatement() if( result_lock == 1) completed = true exit D qu_lck = "UPDATE UserInfo ..." + temp1 + ";" use(temp4) result_lock = update_lock.executeUpdate(qu_lck) define(temp2) A Ir define(temp3) Database interaction graphs (DIGs) are placed before interaction point Multiple DIGs can be integrated into a single CFG Analyze interaction in a control-flow sensitive fashion PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 15 

Data Flow Time Overhead P P D P R P Rc P A P Av Database Granularity 5 10 15 20 25 Time sec P P D P R P Rc P A P Av 20.50 20.74 20.77 20.78 20.94 21.06 2.7% increase in time overhead from P to P + Av (TM) PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 16 

Research Contributions Database Interaction Fault Model Test Adequacy Criteria Test Coverage Monitoring Regression Testing Reduction Prioritization PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 17 

Database-Aware Coverage Monitoring Program Instrumentation Test Suite Adequacy Criteria Instrumented Program Test Suite Execution Instrumented Test Suite Coverage Results Adequacy Calculation Test Requirements Adequacy Measurements Purpose: Record how the program interacts with the database during test suite execution PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 18 

Database-Aware Instrumentation Test Coverage Monitoring Instrumentation Interaction Location Interaction Type Program Test Suite Defining Using Defining-Using Efficiently monitor coverage without changing the behavior of the program under test Record coverage information in a database interaction calling context tree (DI-CCT) PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 19 

Configuring the Coverage Monitor Configuration of the Test Coverage Monitor Instrumentation Tree Format Tree Type Tree Storage Static Dynamic Source Code Bytecode Binary XML Traditional Database-Aware CCT DCT Interaction Level DI-DCT DI-CCT Database Relation Attribute Record Attribute Value Standard Compressed Flexible and efficient approach that fully supports both traditional and database-centric applications PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 20 

Static Instrumentation: Time FF PI RM ST TM GB All Application 2 4 6 8 10 Static Instrumentation Time sec FF PI RM ST TM GB All 4.391 4.404 4.396 4.394 5.169 5.583 8.687 Attach probes to all of the applications in less than nine seconds Static approach is less flexible than dynamic instrumentation PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 21 

Static Instrumentation: Space ZIP GZIP PACK Compression Technique GB 20000 40000 60000 80000 Application Size bytes ZIP GZIP PACK 25084 19419 9034 75782 68456 68475 A As Increase in bytecode size may be large (space vs. time trade-off) PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 22 

Static vs. Dynamic Instrumentation Norm Sta CCT Sta DCT Dyn CCT Dyn DCT Instrumentation Technique Tree Type GB 2 4 6 8 10 12 14 TCM Time sec Norm Sta CCT Sta DCT Dyn CCT Dyn DCT 6.939 7.626 8.026 11.084 11.435 Static is faster than dynamic / CCT is faster than DCT The coverage monitor is both efficient and effective PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 23 

Size of the Instrumented Applications Compr Tech Before Instr (bytes) After Instr (bytes) None 29275 887609 Zip 15623 41351 Gzip 10624 35594 Pack 5699 34497 Average static size across all case study applications Compress the bytecodes with general purpose techniques Specialized compressor nicely reduces space overhead PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 24 

Database Interaction Levels CCT Interaction Level TCM Time (sec) Percent Increase (%) Program 7.44 12.39 Database 7.51 13.44 Relation 7.56 14.20 Attribute 8.91 34.59 Record 8.90 34.44 Attribute Value 10.14 53.17 Static instrumentation supports efficient monitoring 53% increase in testing time at finest level of interaction PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 25 

Research Contributions Database Interaction Fault Model Test Adequacy Criteria Test Coverage Monitoring Regression Testing Reduction Prioritization PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 26 

Database-Aware Regression Testing Begin Coverage Report End Program or Database Changes Program Test Suite Execution Reduction or Prioritization Original Test Suite Modified Test Suite Testing Results Version specific vs. general approach Use paths in the coverage tree as a test requirement Prioritization re-orders the test suite so that it is more effective Reduction identifies a smaller suite that still covers all of the requirements PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 27 

Configuring the Regression Tester Type Data Flow Coverage Tree Path Configuration of the Regression Tester Technique Test Cost Requirements Reduction Prioritization Type Greedy Reverse Random Overlap-Aware Not Overlap-Aware Cost Coverage Ratio Unit Actual Traditional Database-Aware Unique Dominant Type of Tree Super Path Containing Path DCT CCT Regression testing techniques can be used in the version specific model PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 28 

Research Contributions Database Interaction Fault Model Test Adequacy Criteria Test Coverage Monitoring Regression Testing Reduction Prioritization PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 29 

Finding the Overlap in Coverage T2 T3 T6 T9 R R1 R2 T4 T8 R3 T12 R4 T1 R4 R4 R4 T5 R4 R4 10 R4 T11 R5 T5 R5 R6 R6 10 R7 R7 T7 R7 T10 Rj → Ti means that requirement Rj is covered by test Ti T = T2, T3, T6, T9 cover all of the test requirements PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 30 

Reducing the Size of the Test Suite App Rel Attr Rec Attr Value All RM (13) (7, .462) (7, .462) (10, .300) (9, .308) (8.25, .365) FF (16) (7, .563) (7, .563) (11, .313) (11, .313) (9, .438) PI (15) (6, .600) (6, .600) (8, .700) (7, .533) (6.75, .550) ST (25) (5, .800) (5, .760) (11, .560) (10, .600) (7.75, .690) TM (27) (14, .481) (14, .481) (15, .449) (14, .481) (14.25, .472) GB (51) (33, .352) (33, .352) (33, .352) (32, .373) (32.75, .358) All (24.5) (12, .510) (12.17, .503) (14.667, .401) (13.83, .435) Reduction factor for test suite size varies from .352 to .8 PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 31 

Reducing the Testing Time Rc Av Database Interaction GB 0.2 0.4 0.6 0.8 1 Reduction Factor Time Rc Av 0.78 0.78 0.94 0.94 0.06 0.06 0.81 0.81 0.79 0.79 0.36 0.39 GRO GRC GRV GRR RVR RAR GRO reduces test execution time even though it removes few tests PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 32 

Preserving Requirement Coverage Rc Av Database Interaction GB 0.2 0.4 0.6 0.8 1 Preservation Factor Coverage Rc Av 1. 1. 0.3 0.09 0.98 0.99 0.96 0.98 0.91 0.94 0.71 0.72 GRO GRC GRV GRR RVR RAR GRO guarantees coverage preservation - others do not PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 33 

Research Contributions Database Interaction Fault Model Test Adequacy Criteria Test Coverage Monitoring Regression Testing Reduction Prioritization PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 34 

Cumulative Coverage of a Test Suite Testing Time . . . Covered Test Reqs cements T1 Done Tn−1 Done Tn Done Cover Π(T1) Cover n−1 i=1 Π(Ti) Cover Π(T) Area t(n) 0 κ(T, t) κ(T, t) (t) Calculate coverage effectiveness of a prioritization : Actual Ideal PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 35 

Improving Coverage Effectiveness Rc Av Database Interaction GB 0.2 0.4 0.6 0.8 1 Coverage Effectiveness Rc Av 0.94 0.94 0.88 0.87 0.87 0.9 0.93 0.93 0.84 0.86 0.22 0.22 0.55 0.56 GPO GPC GPV GPR RVP ORP RAP GRO is the best choice - original ordering is poor PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 36 

Conclusions Practically all use of databases occurs from within application programs - must test these interactions! Incorporate the state and structure of the database during all testing phases Fault model, database-aware representations, test adequacy, test coverage monitoring, regression testing Experimental results suggest that the techniques are efficient and effective for the chosen case study applications A new perspective on software testing: it is important to test a program’s interaction with the execution environment PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 37 

Future Work Avoiding database server restarts during test suite execution Time-aware regression testing Input simplification and fault localization during debugging Reverse engineering and mutation testing New environmental factors: eXtensible markup language (XML) databases Distributed hash tables Tuple spaces Further empirical studies with additional database-centric applications and traditional programs PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 38 

Further Resources Gregory M. Kapfhammer and Mary Lou Soffa. A Family of Test Adequacy Criteria for Database-Driven Applications. In Proceedings of the ACM SIGSOFT Symposium on the Foundations of Software Engi- neering, 2003. (Distinguished Paper Award) Gregory M. Kapfhammer. The Computer Science Handbook, chapter 105: Software Testing. CRC Press, Boca Raton, FL, Second Edition, June 2004. Gregory M. Kapfhammer, Mary Lou Soffa, and Daniel Mossé. Testing in Resource-Constrained Exe- cution Environments. In Proceedings of the 20th ACM/IEEE International Conference on Automated Software Engineering, Long Beach, California, November, 2005 Kristen R. Walcott, Mary Lou Soffa, Gregory M. Kapfhammer, and Robert S. Roos. Time-Aware Test Suite Prioritization. In Proceedings of the ACM SIGSOFT/SIGPLAN International Symposium on Software Testing and Analysis, Portland, Maine, June, 2006. Gregory M. Kapfhammer and Mary Lou Soffa. A Test Adequacy Framework with Database Interac- tion Awareness. ACM Transactions on Software Engineering and Methodology. Invited Paper Under Review. PhD Dissertation Defense, University of Pittsburgh, April 19, 2007 – p. 39