query-based test generation for database applications david chays, adelphi university john shahid,...
TRANSCRIPT
Query-based Test Generation for Database Applications
David Chays, Adelphi UniversityJohn Shahid, Polytechnic University
Phyllis G. Frankl, Polytechnic University
DBTest '08, Vancouver, BC
Outline
• Issues in Testing DB Applications• AGENDA Overview• Generating Test Generation Queries• Conclusions and Future Work
Relational databases
• Data is viewed as a collection of relations– relation schema– relation (relation state)
Table E ssn name
001-00-0356 Johnson012-34-5678 Smith036-54-5555 Jones051-88-9911 Blake
• Tables, tuples, attributes, constraints for example, create table E (ssn char(11) primary key,
name char(25) not null)
DB Application• SQL: declarative language for defining and manipulating
databases. Includes statements to create and modify schema and to select, insert, delete, update table elements that satisfy certain conditions
• Application typically written in high level language host program with SQL statements embedded or dynamically generated
• SQL statements passed to DBMS for execution and results returned to host program
• Communication via host variables
Aspects of Correctness
• Does the DBMS perform all operations correctly?• Is concurrent access handled correctly?• Is the system fault-tolerant?• Is the system secure?• ...
Does the application program behave as intended?
Traditional vs. DB programs
• function• imperative nature• function• Semi-declarative nature
input
output
input DB state
output DB state
DB Application Testing Goal
• Select “interesting” DB states along with user inputs that exercise “interesting” behavior
• Cover wide variety of situations that could arise in practice
• Do so in a way that facilitates checking of output to user and resulting DB state
Current Practice
• Testing is largely manual• Limited tool support
– Database state generation– DBUnit – extension of JUnit with support for importing
XML descriptions of database state and for checking results of individual tests supplied by tester
• AGENDA goal: More thorough automation of entire test process for DB applications
AGENDA Parser
State Generator Input Generator
State Validator Output Validator
AGENDA DB
AGENDA System Overview• Inputs
• database schema• application source code• Sample values, divided into data groups• test heuristics• info about expected behavior of test cases• Constraints on expected database state and outputs
• Outputs• Initial database state• Test cases• Hooks for validation of resulting DB state and output• Error reports when tests violate constraints
AGENDA System Overview• Inputs
• database schema• application source code• Sample values, divided into data groups• test heuristics• info about expected behavior of test cases• Constraints on expected database state and outputs
• Outputs• Initial database state• Test cases• Hooks for validation of resulting DB state and output• Error reports when tests violate constraints
Student Registration Application Tables:
Person (id, name, passwd, type) Class (crsCode, credit, enrollment,
maxEnrollment, profId) Transcript (sid, code, grade)
who’s registered for what and their grades
Constraints in schema: primary keys (possibly composite) foreign key
Transcript.sid references Person.id Transcript.code references Class.crsCode
Sample data values and groups
Name:
DengDavidPhyllisGlebEricWang
Id:
--choice_name: Student112252334121013311------choice_name: Faculty888887
CrsCode:
--choice_name: UndergradCS101EL101CS110------choice_name: GradEL501CS608CS912
Sample output of State Generator
Sid Code Grade
311 CS912
252 CS101 C
311 EL101 A
311 CS608
112 CS912 B
Id Name Passwd Type
311 Deng deng123 1
112 Eric eric123 1
887 Phyllis pf123 2
252 Wang w123 1
888 Gleb g12 2
Crscode Credit Enrollment MaxEnroll ProfId
CS912 4 2 60 887
CS608 3 1 50 887
EL101 3 1 120 888
CS905 5 0 30 888
CS101 3 1 60 888
Table Person
Table Class
Table Transcript
Input Generator• Each combination of data groups can serve as a
test template for test cases.• For each input parameter (input host variable) find info
about data groups for the associated attribute along with candidate values in the Agenda DB.
• Selection of arbitrary elements of data groups does not work– SELECT sid FROM transcript WHERE code = :hv1 and grade = :hv2– Choosing hv1 = CS912, hv2 = ‘A’ leads to no rows that
satisfy the where clause– OK for testing robustness, but one also wants test cases
that test more typical behavior
• Original approach: Combination of sample values from relevant data groups + automatic generation and execution of SELECT statements to extract related attribute values
• New approach: more general, flexible, hopefully more effective
Sample Code (Class Registration)BEGIN DECLARE SECTION;1) int enroll, maxenroll ;2) char sid[20], code [10];END DECLARE SECTION;
void register(char[] sid, char[] code) {
3) EXEC SQL SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll FROM Class WHERE crsCode = :code
4) if (enroll < maxEnroll) {
5) EXEC SQL UPDATE Class SET enrollment = :enroll + 1 WHERE crsCode = :code ;
6) EXEC SQL INSERT INTO TRANSCRIPT (sid, code) VALUES (:sid, :code);
} // end if
7) COMMIT; } // end register transaction
Approach
• Test template– id: student– course code: undergrad
• General approach: generate a query to select values of these parameters that correspond to tuples in the populated DB representing these data groups
• Use auxiliary data:parameter_value_recs table
Table parameter_value_recsparameter_name group_name value
code Undergrad CS101
code Undergrad EL101
code Undergrad CS110
code Grad EL501
code Grad CS608
code Grad CS912
sid Student 112
sid Student 252
sid Student 334
sid Student 121
sid Student 013
sid Student 311
sid Faculty 888
sid Faculty 887
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT temp1
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT temp1 FROM (
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT temp1 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:code’
AND group_name = ‘Undergrad’)
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT temp1 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:code’
AND group_name = ‘Undergrad’))WHERE EXISTS (
SELECT * FROM
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT temp1 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:code’
AND group_name = ‘Undergrad’))WHERE EXISTS (
SELECT * FROM Class
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT temp1 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:code’
AND group_name = ‘Undergrad’))WHERE EXISTS (
SELECT * FROM ClassWHERE crsCode = temp1
)
SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll
FROM Class WHERE crsCode = :code
SELECT temp1 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:code’
AND group_name = ‘Undergrad’))WHERE EXISTS (
SELECT * FROM ClassWHERE crsCode = temp1
)
UPDATE Class SET enrollment = :enroll + 1 WHERE crsCode = :code
SELECT temp1 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:code’
AND group_name = ‘Undergrad’))WHERE EXISTS (
SELECT * FROM ClassWHERE crsCode = temp1
)
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2 FROM (
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:sid’ AND
group_name = ‘Student’)
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:sid’ AND group_name = ‘Student’)CROSS_JOIN(SELECT value AS temp2 FROM parameter_value_recs WHERE parameter_name = ‘:code’ AND group_name = ‘Undergrad’)
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:sid’ AND
group_name = ‘Student’)CROSS_JOIN(SELECT value AS temp2 FROM parameter_value_recs WHERE parameter_name = ‘:code’ AND
group_name = ‘Undergrad’))WHERE NOT EXISTS (SELECT sid, code FROM Transcript
WHERE sid = temp1 and code = temp2)
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:sid’ AND group_name = ‘Student’)CROSS_JOIN(SELECT value AS temp2 FROM parameter_value_recs WHERE parameter_name = ‘:code’ AND group_name = ‘Undergrad’)
)WHERE NOT EXISTS (SELECT sid, code FROM Transcript
WHERE sid = temp1 and code = temp2)ANDEXISTS (SELECT id FROM Person WHERE id = temp1)
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:sid’ AND group_name = ‘Student’)CROSS_JOIN(SELECT value AS temp2 FROM parameter_value_recs WHERE parameter_name = ‘:code’ AND group_name = ‘Undergrad’)
)WHERE NOT EXISTS (SELECT sid, code FROM Transcript
WHERE sid = temp1 and code = temp2)ANDEXISTS (SELECT id FROM Person WHERE id = temp1)ANDEXISTS (SELECT crsCode FROM Class WHERE crsCode = temp2)
INSERT INTO Transcript (sid, code) VALUES (:sid, :code)
SELECT temp1, temp2 FROM ((SELECT value AS temp1 FROM parameter_value_recs WHERE parameter_name = ‘:sid’ AND group_name = ‘Student’)CROSS_JOIN(SELECT value AS temp2 FROM parameter_value_recs WHERE parameter_name = ‘:code’ AND group_name = ‘Undergrad’)
)W HERE NOT EXISTS (SELECT sid, code FROM Transcript
WHERE sid = temp1 and code = temp2)ANDEXISTS (SELECT id FROM Person WHERE id = temp1)ANDEXISTS (SELECT crsCode FROM Class WHERE crsCode = temp2)
What is the result?
Sid Code Grade
311 CS912
252 CS101 C
311 EL101 A
311 CS608
112 CS912 B
Id Name Passwd Type
311 Deng deng123 1
112 Eric eric123 1
887 Phyllis pf123 2
252 Wang w123 1
888 Gleb g12 2
Crscode Credit Enrollment MaxEnroll ProfId
CS912 4 2 60 887
CS608 3 1 50 887
EL101 3 1 120 888
CS905 5 0 30 888
CS101 3 1 60 888
Table Person
Table Class
Table Transcript
Test Cases
(112, CS101)(112, EL101)(252, EL101)(311, CS101)
Status of Test Generation Query Generator
• Implemented– Automatically generates test generation queries
given info about application unit’s SQL statements and parameters
• Parsing application for useful test information– Working on automating this for Java programs
Initial evaluation
• 5 transactions of TPC-C Benchmark• Produced Type A test cases that caused all 5
transactions to commit• Current work: How effective are these tests?
– Seeded faults in the transactions– If transaction commits, check specification violation– If transaction does not commit, this means the test case
was not Type A or there was a bug
• Initial results on error-seeded versions– Some transactions identified as buggy but most committed
with no specification violation
Conclusions
• Interplay between input parameters and DB state, and SQL statements under test
• Integration, flexibility– Test template– Type A inputs
• Non-empty result sets • Honoring constraints in updates
Ongoing and Future Work• Improve State Generator• Experiment
– Evaluate effectiveness and performance
• Complex manipulation of host variables by host program– Symbolic execution
• Type A and type B test cases • Other kinds of test templates• Further automate and integrate Java
static analysis tools
Related Work
• Willmor and Embury, “An intensional approach to the specification of test cases for database applications”, ICSE 2006
• Binnig et al, “Reverse query processing”, ICDE 2007
• Binnig et al, “Qagen: generating query-aware test databases”, ACM SIGMOD 2007.
• Emmi et al, “Dynamic test input generation for database applications”, ISSTA 2007.
Previous work on AGENDA• “A Framework for Testing Database Applications”,
ISSTA 2000• Earlier version of AGENDA System was demonstrated
at ASE03/ICSE03• “An AGENDA for testing relational database
applications”, Journal of Software Testing, Verification and Reliability, Mar 2004.
• “Testing Database Transaction Concurrency”, International Conference on Automated Software Engineering 2003
• “Testing Web Database Applications”, TAV-WEB workshop, ACM SIGSOFT Notes, Sept 2004
• “Testing Database Transaction with AGENDA”, International Conference on Software Engineering 2005
Questions?