Query based test generation for database applications l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 45

Query-based Test Generation for Database Applications PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on
  • Presentation posted in: General

Query-based Test Generation for Database Applications. David Chays, Adelphi University John Shahid , Polytechnic University Phyllis G. Frankl , Polytechnic University. Outline. Issues in Testing DB Applications AGENDA Overview Generating Test Generation Queries

Download Presentation

Query-based Test Generation for Database Applications

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Query-based Test Generation for Database Applications

David Chays, Adelphi University

John 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


ssnname

001-00-0356Johnson

012-34-5678Smith

036-54-5555Jones

051-88-9911Blake

Table E

Relational databases

  • Data is viewed as a collection of relations

    • relation schema

    • relation (relation state)

  • Tables, tuples, attributes, constraints

  • for example,

  • create tableE(ssnchar(11)primary key,

  • namechar(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?


function

imperative nature

function

Semi-declarative nature

Traditional vs. DB programs

input DB state

input

output

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

    Id:

    --choice_name: Student

    112

    252

    334

    121

    013

    311

    ----

    --choice_name: Faculty

    888

    887

    CrsCode:

    --choice_name: Undergrad

    CS101

    EL101

    CS110

    ----

    --choice_name: Grad

    EL501

    CS608

    CS912

    Name:

    Deng

    David

    Phyllis

    Gleb

    Eric

    Wang


    Sample output of State Generator

    Table Transcript

    Table Person

    Table Class


    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;

    • int enroll, maxenroll ;

    • 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 ;

    • 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_recs


    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 Class

    WHERE 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 Class

    WHERE 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 Class

    WHERE 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)

    AND

    EXISTS (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)

    AND

    EXISTS (SELECT id FROM Person WHERE id = temp1)

    AND

    EXISTS (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)

    AND

    EXISTS (SELECT id FROM Person WHERE id = temp1)

    AND

    EXISTS (SELECT crsCode FROM Class WHERE crsCode = temp2)


    What is the result?

    Table Transcript

    Table Person

    Table Class

    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?

    [email protected]


  • Login