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


  • 59 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 l.jpg

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 l.jpg

Outline

  • Issues in Testing DB Applications

  • AGENDA Overview

  • Generating Test Generation Queries

  • Conclusions and Future Work


Relational databases l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

function

imperative nature

function

Semi-declarative nature

Traditional vs. DB programs

input DB state

input

output

output DB state


Db application testing goal l.jpg

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 l.jpg

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


Slide9 l.jpg

AGENDA Parser

State Generator

Input Generator

State Validator

Output Validator

AGENDA DB


Agenda system overview l.jpg

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 overview11 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    Sample output of State Generator

    Table Transcript

    Table Person

    Table Class


    Input generator l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    Table parameter_value_recs


    Select enrollment maxenrollment into enroll maxenroll from class where crscode code l.jpg

    SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll FROM Class WHERE crsCode = :code

    SELECT


    Select enrollment maxenrollment into enroll maxenroll from class where crscode code21 l.jpg

    SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll FROM Class WHERE crsCode = :code

    SELECT temp1


    Select enrollment maxenrollment into enroll maxenroll from class where crscode code22 l.jpg

    SELECT enrollment, maxEnrollment INTO :enroll, :maxEnroll FROM Class WHERE crsCode = :code

    SELECT temp1 FROM (


    Select enrollment maxenrollment into enroll maxenroll from class where crscode code23 l.jpg

    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 code24 l.jpg

    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 code25 l.jpg

    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 code26 l.jpg

    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 code27 l.jpg

    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 l.jpg

    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 l.jpg

    INSERT INTO Transcript (sid, code) VALUES (:sid, :code)

    SELECT


    Insert into transcript sid code values sid code30 l.jpg

    INSERT INTO Transcript (sid, code) VALUES (:sid, :code)

    SELECT temp1, temp2


    Insert into transcript sid code values sid code31 l.jpg

    INSERT INTO Transcript (sid, code) VALUES (:sid, :code)

    SELECT temp1, temp2 FROM (


    Insert into transcript sid code values sid code32 l.jpg

    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 code33 l.jpg

    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 code34 l.jpg

    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 code35 l.jpg

    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 code36 l.jpg

    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 code37 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    Questions?

    [email protected]


  • Login