Testing Database Applications from

Testing Database Applications

Carsten Binnig

University of Heidelberg

Germany

carsten.binnig@informatik.uni-

heidelberg.de

Donald Kossmann

ETH Zurich

Switzerland

kossmann@inf.ethz.ch

Eric Lo

ETH Zurich

Switzerland

ecllo@inf.ethz.ch

ABSTRACT

Testing database application is challenging because most methods

and tools developed for application testing do not consider the data-

base state during the test. In this paper we demonstrate three dif-

ferent tools for testing database applications: HTDGen, HTTrace

and HTPar. HTDGen generates meaningful test databases for data-

base applications. HTTrace executes database applications testing

ef ciently and HTPar extends HTTrace to run tests in parallel.

1. INTRODUCTION

Database applications are becoming increasingly complex. They

are composed of many components and stacked in several layers.

Furthermore, most database applications are subject to constant

change; for instance, business processes are re-engineered, autho-

rization rules are changed, components are replaced by other more

powerful components, or optimizations are added in order to achieve

better performance for a growing number of users and data. The

more complex an application becomes, the more frequently the ap-

plication and its con guration must be changed.

The SIKOSA project is a joint research project of several univer-

sities in Western Europe. One goal of the SIKOSA project is to

develop new techniques and tools in order to automate the test-

ing and quality assurance of database applications. The goal is to

reduce the burden of programmers and engineers (i.e., people) to

guarantee the quality of a database application and to provide a

(computer-based) infrastructure that automatically checks diverse

quality metrics. This project has several apparent results: (a) the

quality of the database applications can be dramatically increased

obvious errors which might slip a human s attention can be de-

tected; (b) the cost of testing can be reduced; (c) the time to market

of a computer system can be reduced; (d) new computer technol-

ogy (e.g., fast hardware) can be leveraged in order to achieve even

higher quality and reduced costs in the long run.

As part of this project, a suite of tools for testing database applica-

tions has been developed: HTDGen, HTTrace, and HTPar. First,

HTDGen is a tool for generating a test database such that we could

use that to test a database application thoroughly. Second, HTTrace

Permission to make digital or hard copies of all or part of this work for

personal or classroom use is granted without fee provided that copies are

not made or distributed for pro t or commercial advantage and that copies

bear this notice and the full citation on the rst page. To copy otherwise, to

republish, to post on servers or to redistribute to lists, requires prior speci c

permission and/or a fee.

SIGMOD 2006, June 27 29, 2006, Chicago, Illinois, USA.

is a tool to run regression tests on database applications. It learns

during each test and devises a new test run execution schedule after

each test. The next test will then be executed according to the new

devised schedule so as to minimize the testing time. Third, HTPar

is an extension of HTTrace. It is used to execute test runs in parallel

on a single or several machines. Its goal is to exploit the available

resources (e.g., machine) as well as possible and/or achieve linear

speed up in testing.

2. HTDGEN: A TEST DATABASE GENER-

ATOR

In order to execute tests on a database application the database it-

self has to be initialized with interesting data (called test database).

Most methods for generating test databases only consider the data-

base schema [9, 4] or generate random data for a given statistical

distribution [3, 6]. However, these generated databases are inade-

quate to cover many critical execution paths of the application. It

is because these generated test databases never take the embedded

SQL queries of the database application into account. As a con-

sequence, there is a gap between the generated test databases and

the queries of the application during test: this leads to the fact that

many queries of the application get no (meaningful) results from

the generated databases and thus many execution paths of the appli-

cation cannot be tested. In order to test the critical execution paths

of a database application in a meaningful way, we designed HTD-

Gen. HTDGen is a test database generator that considers both the

database schema and the queries of the database application during

data generation. Currently, HTDGen supports database applica-

tions with embedded SQL statements and it generates relational test

databases for testing these applications. To show the idea, consider

the following pseudocode fragment from a database application:

foreach price in SELECT price FROM Product do

if(price>=0 && price<=10)

//do something

else if(price>10)

//do something else

end if

end foreach

To test all execution paths of this fragment, HTDGen collects the

embedded SQL query Q (SELECT price FROM Product),

the schema S

of the target database (e.g., S

includes the schema

of table Product and integrity constraints) and the results R of

the SQL query (e.g., a table with three rows: 5, 5 and 15 for

the attribute price of the table Product; either given by the

testers or by running a code analyzer). Then HTDGen sends Q, S

739