Meszaros G. xUnit Test Patterns Refactoring Test Code

Подождите немного. Документ загружается.

This page intentionally left blank

649

CHAPTER 25

Database Patterns

Patterns in This Chapter

Database Sandbox. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650

Stored Procedure Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654

Table Truncation Teardown . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661

Transaction Rollback Teardown . . . . . . . . . . . . . . . . . . . . . . . . . . . 668

Database

Patterns

650

Chapter 25 Database Patterns

Database Sandbox

How do we develop and test software that depends on a database?

We provide a separate test database for each developer or tester.

Many applications use a database to store the persistent state of the application.

At least some of the tests for such an application will require accessing the data-

base. Unfortunately, a database is a primary cause of Erratic Tests (page 228) due

to the fact that data may persist between tests. A major goal in keeping tests from

interacting is ensuring that the test ﬁ xtures used by each test do not overlap. This

is especially difﬁ cult when the development environment contains only a single

test database and all tests run by all developers run against the same database.

A Database Sandbox is one way to keep the tests from interacting by acciden-

tally accessing the same records in the database.

How It Works

We provide each user with a separate, self-consistent sandbox in which to work.

This sandbox includes the user’s own copy of any code plus—most importantly—the

user’s own copy of the database. Such an arrangement allows each user to modify

the database in any way he or she sees ﬁ t and to exercise the application with tests

without worrying about any interactions between the user’s own tests and the tests

conducted by other users.

When to Use It

We should use a Database Sandbox whenever we are building or modifying an

application that depends on a database for a signiﬁ cant portion of its functionality.

SUT

Setup

Exercise

Verify

Teardown

Database

Fixture

Developer 1

Database

SUT

Setup

Exercise

Verify

Teardown

Fixture

Developer 2

SUT

Setup

Exercise

Verify

Teardown

Database

Fixture

Developer 1

Database

SUT

Setup

Exercise

Verify

Teardown

Fixture

Developer 2

Database

Sandbox

651

This need is especially evident if we have chosen to use a Shared Fixture (page 317).

Using a Database Sandbox will help us avoid Test Run Wars (see Erratic Test)

between different users of the database. Depending on how we have chosen to

implement the Database Sandbox, it may or may not allow different users to

modify the structure of the database. A Database Sandbox will not prevent Un-

repeatable Tests (see Erratic Test) or Interacting Tests (see Erratic Test), however,

because it merely separates different users (and their test runs) from one another;

tests within a single test run may continue to share a test ﬁ xture.

Implementation Notes

The application needs to be made conﬁ gurable so that the database to be used

in testing can be changed without modifying the code. Typically, this goal is

accomplished by reading the database conﬁ guration information from a proper-

ties ﬁ le that is customized in each user’s environment.

A Database Sandbox can be implemented in many different ways. Fundamentally,

the choice comes down to whether we give each user a separate database instance or

just simulate one. In general, giving each user a real separate database instance is the

preferred choice. This scheme may not always be feasible, however—especially if the

database vendor’s licensing structure makes it cost prohibitive.

Variation: Dedicated Database Sandbox

We give each developer, tester, or test user a separate database instance. This is

typically accomplished by installing a lightweight database technology in each

user’s test environment. Examples of lightweight database technologies include

MySql and Personal Oracle. The database instance can be installed on the user’s

own machine, on a shared test server, or on a dedicated “virtual server” running

on shared server hardware.

A Dedicated Database Sandbox is the preferred solution because it provides

the greatest ﬂ exibility. It allows a developer to modify the database schema,

load his or her own test data, and so on.

Variation: DB Schema per Test Runner

With DB Schema per Test Runner, we give each developer, tester, or test user

what appears to be a separate database instance by using built-in database sup-

port for multiple schemas.

One considerable advantage that the DB Schema per Test Runner pattern

enjoys relative to the Dedicated Database Sandbox pattern is that we can share

an Immutable Shared Fixture (see Shared Fixture) deﬁ ned in a common schema

and put each user’s mutable ﬁ xture in his or her own private schema. Note that

Database Sandbox

Database

Sandbox

652

Chapter 25 Database Patterns

this scheme does not allow the user to modify the structure of the database

(at least not to the same degree as is possible with a Dedicated Database Sandbox).

It also forces all users, including both developers and testers, to use the same

database structure. This can create logistical issues when database structure

upgrades need to be rolled out.

Variation: Database Partitioning Scheme

We give each developer, tester, or test user a separate set of data within a single

Database Sandbox. Each user can modify that data as he or she sees ﬁ t but is not

allowed to modify the data assigned to other users.

This approach requires less database administration overhead but more data

administration overhead than with the other ways to implement a Database

Sandbox. Because it does not allow developers to modify the database schema,

a Database Partitioning Scheme is not appropriate for evolutionary database

development. It is, however, appropriate for preventing Interacting Tests when

applied to different tests run from the same Test Runner. That is, we give each

test a unique key such as a CustomerNumber that it uses for all data. As a conse-

quence, other tests within the same test run use different data. This pattern can

be combined with many of the other variations of Database Sandbox to prevent

Interacting Tests when using a Shared Fixture. Note that this pattern does not

prevent Unrepeatable Tests unless we also use Distinct Generated Values (see

Generated Value on page 723).

Motivating Example

The following test uses Literal Values for the arguments to a constructor of a

Product that is persisted into a database instance shared among several developers.

The name of the Product must be unique:

public void testProductPrice_HCV() {

// Setup

Product product =

new Product( 88, // ID

"Widget", // Name

new BigDecimal("19.99")); // Price

// Exercise SUT

// ...

}

Unfortunately, we may end up with a Test Run War when we run this test against a

shared database instance regardless of how effectively we tear down the Product after

each test. This is because we are trying to create the same Product that the same test

run from another Test Runner might be in the process of using at the same time.

Database

Sandbox

653

Refactoring Notes

There are no code changes required of our test when we create a Dedicated

Database Sandbox for each developer and tester. Therefore, tests should not

have to do anything special to run completely independently of tests being run

from other Test Runners (page 377). There is a small change required of the

SUT, however, to allow the SUT to connect to different database instances based

on conﬁ guration data. How we make this change varies with the technology we

use and is beyond the scope of this book.

We can convert the test to use a Database Partitioning Scheme by replacing

the Literal Values with calls to the appropriate getUnique method passing an ID

speciﬁ c to the Test Runner as a seed.

Example: Database Partitioning Scheme

Here is the same test using a Database Partitioning Scheme to ensure that each

test uses a different set of products. For the getUniqueString method, we pass a

string based on the MAC address of our computer.

public void testProductPrice_DPS() {

// Setup

Product product =

new Product( getUniqueInt(), // ID

getUniqueString(getMacAddress()), // Name

new BigDecimal("19.99")); // Price

// Exercise SUT

// ...

}

static int counter = 0;

int getUniqueInt() {

counter++;

return counter;

}

BigDecimal getUniqueBigDecimal() {

return new BigDecimal(getUniqueInt());

}

String getUniqueString(String baseName) {

return baseName.concat(String.valueOf( getUniqueInt()));

}

This test can now be run from several different computers against the same

shared database instance without fear of a Test Run War.

Database Sandbox

Database

Sandbox

654

Chapter 25 Database Patterns

Stored Procedure Test

How can we verify logic independently when we have stored procedures?

We write Fully Automated Tests for each stored procedure.

Many applications that use a database to store the persistent state of the appli-

cation also use stored procedures and triggers to improve performance and do

common processing on updates.

A Stored Procedure Test is a way to apply automated testing practices to this

code that lives inside the database.

How It Works

We write unit tests for the stored procedures independent of the client application

software. These tests may be layer-crossing tests or round-trip tests, depending

on the nature of the store procedure(s) being tested.

When to Use It

We should write Stored Procedure Tests whenever we have nontrivial logic in

stored procedures. This pattern will help us verify that the stored procedures—our

Application Environment

Database

Stored

Procedure

Proxy

Stored

Procedure

Testcase Class

testMethod_1

testMethod_2

Testcase Class

testMethod_1

testMethod_2

Application Environment

Database

Stored

Procedure

Proxy

Stored

Procedure

Testcase Class

testMethod_1

testMethod_2

Testcase Class

testMethod_1

testMethod_2

Stored

Procedure

Test

655

SUT for the purposes of these tests—are working properly independently of the

client application. This consideration is particularly important when more than

one application will use the stored procedures or when the stored procedures are

being developed by a different development team. Stored Procedure Tests are

particularly important when we cannot ensure the procedures are tested ade-

quately simply by exercising the application software (a form of Indirect Testing;

see Obscure Test on page 186). Using Stored Procedure Tests also helps us to

enumerate all the conditions under which the stored procedure could be called

and what should happen in each circumstance. The very act of thinking about

these circumstances is likely to improve the design—a common result of doing

test-ﬁ rst development.

Implementation Notes

There are two fundamentally different ways to implement Stored Procedure Tests:

(1) We can write the tests in the same programming language as the stored proce-

dure and run them in the database or (2) we can write the tests in our application

programming language and access the stored procedure via a Remote Proxy [GOF].

We might even write tests both ways. For example, the stored-procedure developers

might write unit tests in the database programming language, whereas the applica-

tion developers might prepare some acceptance tests in the application programming

language to run as part of the application build.

Either way, we need to decide how the test will set up the ﬁ xture (the “before”

state of the database) and verify the expected outcome (the “after” state of the

database as well as any expected actions such as cascading deletes). The test may

interact directly with the database to insert/verify the data (a form of Back Door

Manipulation; see page 327) or it could use another stored procedure (a form of

round-trip test).

Variation: In-Database Stored Procedure Test

One advantage of the xUnit approach to automated testing is that the tests are

written in the same language as the code we are testing. This makes it easier for the

developers to learn how to automate the tests without learning a new program-

ming language, debugger, and so on. Extending this idea to its logical conclusion,

it makes sense to test stored procedures using tests that are written in the stored-

procedure programming language. Naturally, we will need to run these tests inside

the database. Unfortunately, that requirement may make it hard to run them as

part of the Integration Build [SCM].

This variation on the Stored Procedure Test pattern is appropriate when we

have more experience writing code in the stored-procedure language and/or

Stored Procedure Test

Stored

Procedure

Test

656

Chapter 25 Database Patterns

environment than in the application environment and it is not essential that

all tests be run from a single place. For example, a database or data services

team that is writing stored procedures for use by other teams would ﬁ nd this

approach attractive. Another circumstance in which it would be appropriate

to use In-Database Stored Procedure Tests arises when the procedures are

stored in a different source code repository than the application logic. Using

In-Database Stored Procedure Test allows us to store the tests in the same

repository as the SUT (in this case, the stored procedures).

In-Database Stored Procedure Tests may allow somewhat more thorough

unit testing (and test-driven development) of the stored procedures because we

may have better access to implementation details of the stored procedure from

our tests. Of course, this violation of encapsulation could result in Overspeciﬁ ed

Software (see Fragile Test on page 239). If the client code uses a data access layer,

we must still write unit tests for that software in the application programming

language to ensure that we handle errors correctly (e.g., failure to connect).

Some databases support several programming languages. In such a case,

we can choose to use the more test-friendly programming language for our

tests but write the stored procedures in the more traditional stored-procedure

programming language. For example, Oracle databases support both PLSQL

and Java, so we could use JUnit tests to verify our PLSQL stored procedures.

Likewise, Microsoft’s SQL Server supports C#, so we could use NUnit tests

written in C# to verify the stored procedures written in Transact-SQL.

Variation: Remoted Stored Procedure Test

The purpose of Remoted Stored Procedure Tests is to allow us to write the tests in

the same language as the unit tests for the client application logic. We must access

the stored procedure via a Remote Proxy [GOF] that hides the mechanics of inter-

acting with that procedure. This proxy can be structured as either a Service Facade

[CJ2EEP] or a Command [GOF] (such as Java’s JdbcOdbcCallableStatement).

Remoted Stored Procedure Tests are, in effect, component tests in that they

treat the stored procedure as a “black box” component. Because Remoted Stored

Procedure Tests do not run inside the database, we are more likely to write them

as round-trip tests (calling other stored procedures to set up the ﬁ xture, verify

the outcome, and perform other necessary tasks) unless we have an easy way

to insert or verify data. Some members of the xUnit family have extensions that

are speciﬁ cally intended to facilitate this behavior (e.g., DbUnit for Java and

NDbUnit for .NET languages).

This solution is more appropriate if we want to keep all our tests in a single

programming language. The Remoted Stored Procedure Test pattern makes it

easier to run all the tests every time we check in changes to the application code.

Stored

Procedure

Test

657

Testing Stored Procedures with JUnit

On an early XP project, our application was mandated to use stored

procedures being developed by another group. It seemed that every time

we integrated our Java with those developers’ PLSQL code, we found

serious bugs in the fundamental behavior of their stored procedures.

We were writing automated tests using JUnit for our code. Although we

were sure that writing unit tests for the stored procedures would clarify

the interface contract and improve the quality of the other group’s code,

we couldn’t force the other team to write unit tests. Nor had utPLSQL

even been invented at that point.

We decided to try writing unit tests for the stored procedures in the xUnit

family member we were comfortable with: JUnit. Because we had to write

JDBC code to access the stored procedures anyway, we deﬁ ned JUnit tests

for each stored procedure via the JDBC

PreparedStatement classes that we

had built. The tests exercised the basic behavior of the stored behaviors

and a few of the more obvious failure cases. Whenever we received a new

version of the stored procedures, we would run the JUnit tests before we

even tried to exercise the procedures from our application code. Needless

to say, many of the tests failed.

We sat down with the developers who were building the stored proce-

dures and showed them our tests—including how they were failing left,

right, and center. Needless to say, the developers were a bit embarrassed

but they agreed that our tests were correct. They went off to ﬁ x the stored

procedures and gave us a new version to test. The revision fared somewhat

better but still produced some failures. Then a very important thing hap-

pened: The members of the other group asked for a copy of the tests we

had written and instructions on how to run them for themselves. Before

long, these developers were writing their own PLSQL unit tests in JUnit!

This capability is particularly useful if the stored procedures are being writ-

ten and/or modiﬁ ed by the same team that is developing the client code. We

can also use Remoted Stored Procedure Tests when another team is provid-

ing the stored procedures and we are not conﬁ dent in those developers’ ability

to write defect-free code (probably because they are not writing In-Database

Stored Procedure Tests for their code). In this situation, we can use the Remot-

ed Stored Procedure Tests as a form of acceptance test for their code. See the

sidebar “Testing Stored Procedures with JUnit” for an illustration of how this

setup worked on one project.

Stored Procedure Test

Stored

Procedure

Test