A Framework for Testing Database Application

1. A Framework for Testing Database Application Author: David Chays, Saikat Dan, Filippos I. Vokolos, Elaine J. Weyuker Presenter: Liping Liu

2. Outline • Motivation • Background • Issues in testing DB application • Testing tool • Conclusion

3. Motivation • Important role of DB system • DB application program in semi-declarative language; many testing techniques for programs in imperative language • Different input and output space of DB application

4. Background • Focus on Relational DB and SQL • Data is viewed as a collection of relations • relation schema • relation (relation state): element of Cartesian product • Tables, tuples, attributes, constrains • Constrain: • Domain Constrain • Uniqueness Constrain • not-NULL Constrain • Referential Integrity (foreign key) • Semantic Integrity (general)

5. Correctness (def) • Does the DBMS perform all operations correctly? • Protect security and privacy? • Is the system fault-tolerant? • ... • Does the application program behave as intended?

6. Issues in DB application input DB state input • traditional • imperative nature • DB • declarative nature output DB state output

7. Example: • Customer-feature table • Customer ID, feature name • Customer ID invalid-> return 0; • Feature invalid-> return 0; • ID and feature valid, feature available->return 1; • Feature not available->return 2; • New feature incompatible with old feature->return 3;

8. example • Naïve approach • I = {customer-IDs} X {feature-names} • 0 = {0,1,2,3} • More suitable approach: • I = {customer-IDs} X {feature-names} X {database-states} • 0 = {0,1,2,3} X {database-states}

9. DB state • Ways to deal with DB state: • ignore • as environment • as part of input and output space • Problems: • Controllability: at desired state before execution->populate DB with appropriate data • Observability: observe the state after execution->check the state of DB after execution

10. DB state • How to obtain DB state • Live data • not reflect sufficiently wide variety of situations • difficult to find the situations of interest • violate privacy or security constraints • Synthetic data • Generating domain elements and gluing them together • generate interesting data that obey integrity constraints • Use schema and user supplied info

11. DB schema Suggestions from tester App source State Generator DB state Log file Input Generator State Validator Output Verifier User input Results App exec Output

12. Tool • State Generator • Input: DB schema, user info (suggested value) • Populate DB state • Input Generator • Input: info from State Generator, info of query • Generate input data for test cases • Output Verifier • Input: output of test case • Check with expected value • State Validator • Check state after execution

13. State Generator • Inputs DB schema (in SQL) • Parses schema to derive info about • Attributes • Tables • constraints : uniqueness, not-NULL, referential integrity • inputs additional info from user • suggested attribute values, divided into groups, similar to Category-Partition Testing

14. 0 1 2 3 0 1 2 3 0 1 2 3 sno | F| F| F| F| F| F| F| sno | F| F| F| F| F| F| F| sno | S | char | pr | un | ~nn cp globalTablePointer sname | S | char | ~pr | ~un | ~nn sname | F| F| F| F| F| F| F| cp status | F| F| F| F| F| F| F| status | F| F| F| F| F| F| F| status | S | dec | ~pr | ~un | ~nn cp City | F| F| F| F| F| F| F| city | S | char | ~pr | ~un | ~nn City | F| F| F| F| F| F| F| cp S | 4 | 0 1 2 3 0 1 2 3 4 pno | F| F| F| F| F| F| F| pno | P | char | pr | un | ~nn cp pname | P | char | ~pr | ~un | ~nn pname | F| F| F| F| F| F| F| cp P | 5 | color | P | char | ~pr | ~un | ~nn color | F| F| F| F| F| F| F| cp weight | P | dec | ~pr | ~un | ~nn weight| F| F| F| F| F| F| F| cp city | P | char | ~pr | ~un | ~nn cp SP | 3 | 0 1 2 sno |SP | char | pr | un | ~nn | foreign cp pno |SP | char | pr | un | ~nn | foreign cp Null qty |SP | dec | ~pr | ~un | ~nn cp Data structure S: supplier; P: part

15. cp low medium high 10 300 5000 20 400 6000 30 • Category Partition Each category (column) can have a list of choices pointed to by cp.

16. Populated DB table • Tester specifies table sizes • Tool generates tuples for insertion • select data group or NULL, guided by annotations • select value from data group, obeying constraints • keep track of values used • Outputs sequence of SQL insert statements

17. Table s sno pno qty S1 P1 5000 S1 P2 300 S1 P3 10 S2 P1 6000 S2 P2 400 S2 P3 5000 S3 P1 20 S3 P2 300 S3 P3 30 S4 P1 6000 sno sname status city S1 NULL 0 Brooklyn S2 Smith 1 Florham-Park S3 Jones NULL London S4 Blake NULL Middletown pno pname color weight city P1 NULL blue 100 Brooklyn P2 Seats green 300 Florham-Park P3 airbags yellow 500 Middletown Example Table sp Table p

18. Conclusion • Issues in testing DB application • Framework: DB state • Testing tool: focus on the state generator

19. Future work • Future work: An GENDA for testing relational database applications • 5 components: AGENDA Parser • Data structure ->AGENDA DB; avoids memory issue; more accessible • Interested? http://cis.poly.edu/~ytdeng/paper/jstvr_2004.pdf

20. Thanks!