Setting Up the Database with Test Data: A Small Taste Of Ant
Testing apps with database access can be tricky. One of the problems is that you need data in the database to test the app. It's a classic chicken-or-egg problem: What do you do first? Another problem with databases is realizing that the DDL for the schema is source code and should be managed in the Concurrent Versioning System (CVS) or equivalent. In this example, to solve the problem of needing data to test our model objects, we created an Ant script that can populate the database (see the listing below). Although we haven't covered Ant yet, we included the listing here because it shows the structure of the database for the sample app. The fundamental problem with testing code that interacts with a database is that you cannot test database access layer code unless you have data. The data must be repeatable so that the test cases have a point of reference. The populateTables target solves the problem by populating the database with sample data that we can test for later in the unit tests. You should also have a development and/or test database so that you can set the database back to a testable state. The code in the following listing helps you put the database into a testable state. You could connect the populateTables target to the Ant test buildfile as a dependency. We will cover this topic further in the when we discuss database access layer unit testing.
<?xml version="1.0"?>
<project basedir="." default="build">
<property value="sun.jdbc.odbc.JdbcOdbcDriver" />
<property value="jdbc:odbc:petstore" />
<property value="" />
<property value="" />
<target >
<sql driver="${driver}" url="${url}" userid="${user}" password="${password}" >
CREATE TABLE CATEGORY (
ID int PRIMARY KEY,
DESCRIPTION varchar (100) NOT NULL ,
NAME varchar (25) NOT NULL )
CREATE TABLE SUBCATEGORY (
ID int PRIMARY KEY,
FK_CATEGORY_ID int REFERENCES CATEGORY(ID),
DESCRIPTION varchar (50) NOT NULL ,
NAME varchar (25) NOT NULL )
CREATE TABLE PRODUCT (
DESCRIPTION varchar (50) NOT NULL ,
NAME varchar (20) NOT NULL ,
ID int IDENTITY (1, 1) PRIMARY KEY,
FK_SUBCATEGORY_ID int REFERENCES SUBCATEGORY(ID),
QTY int DEFAULT (5),
PRICE DECIMAL(10,2) not null,
)
</sql>
</target>
<target >
<sql driver="${driver}" url="${url}" userid="${user}" password="${password}" >
DROP TABLE PRODUCT DROP TABLE SUBCATEGORY
DROP TABLE CATEGORY
</sql>
</target>
<target >
<sql driver="${driver}" url="${url}" userid="${user}" password="${password}" >
insert into category (description, name, id) values ('test data', 'test',777)
insert into subcategory (name, description, fk_category_ID, id) values ('dogs', 'dogs description', 777, 111)
insert into subcategory (name, description, fk_category_ID, id) values ('cats', 'cats description', 777, 222)
insert into subcategory (name, description, fk_category_ID, id) values ('fish', 'fish description', 777, 333)
insert into subcategory (name, description, fk_category_ID, id) values ('birds', 'birds description', 777, 444)
insert into subcategory (name, description, fk_category_ID, id) values ('reptiles', 'reptiles description', 777, 555)
insert into Product (description, name, price, fk_subcategory_id)
values('Poodle description','Poodle',1,111)
insert into Product (description, name, price, fk_subcategory_id)
values('Scottie description','Scottie',1,111)
insert into Product (description, name, price, fk_subcategory_id)
values('Schnauzer description','Schnauzer',1,111)
insert into Product (description, name, price, fk_subcategory_id)
values('Calico description','Calico',1,222)
insert into Product (description, name, price, fk_subcategory_id)
values('Jaguar description','Jaguar',1,222)
insert into Product (description, name, price, fk_subcategory_id)
values('Siamese description','Siamese',1,222)
insert into Product (description, name, price, fk_subcategory_id)
values('Goldfish description','Goldfish',1,333)
insert into Product (description, name, price, fk_subcategory_id)
values('Shark description','Shark',1,333)
insert into Product (description, name, price, fk_subcategory_id)
values('Piranha description','Piranha',1,333)
insert into Product (description, name, price, fk_subcategory_id)
values('Parakeet description','Parakeet',1,444)
insert into Product (description, name, price, fk_subcategory_id)
values('Canary description','Canary',1,444)
insert into Product (description, name, price, fk_subcategory_id)
values('Wren description','Wren',1,444)
insert into Product (description, name, price, fk_subcategory_id)
values('Iguana description','Iguana',1,555)
insert into Product (description, name, price, fk_subcategory_id)
values('Boa description','Boa',1,555)
insert into Product (description, name, price, fk_subcategory_id)
values('Python description','Python',1,555)
insert into Product (description, name, price, fk_subcategory_id)
values('African Tree Frog description','African Tree Frog',1,555)
</sql>
</target>
<target depends="createTables,populateTables" />
</project>
This code listing uses the Ant SQL task to create the tables, populate the tables, and (later) drop the tables. In order to use the SQL task, we need to pass it the JDBC URL, user name, and password. Because we will do this three times, we define some properties to hold these values:
<property value="sun.jdbc.odbc.JdbcOdbcDriver" /> <property value="jdbc:odbc:petstore" /> <property value="" /> <property value="" />
Each time we call the SQL task, we pass it these values. Here is the SQL task used in BuildDB:
<target >
<sql driver="${driver}" url="${url}" userid="${user}" password="${password}" >
The first thing we do with the SQL task is create the database tables. There are only three tables, so creating them is easy:
<sql driver="${driver}" url="${url}" userid="${user}" password="${password}" >
CREATE TABLE CATEGORY (
ID int PRIMARY KEY,
DESCRIPTION varchar (100) NOT NULL ,
NAME varchar (25) NOT NULL )
CREATE TABLE SUBCATEGORY (
ID int PRIMARY KEY,
FK_CATEGORY_ID int REFERENCES CATEGORY(ID),
DESCRIPTION varchar (50) NOT NULL ,
NAME varchar (25) NOT NULL )
CREATE TABLE PRODUCT (
ID int IDENTITY (1, 1) PRIMARY KEY
DESCRIPTION varchar (50) NOT NULL ,
NAME varchar (20) NOT NULL ,
FK_SUBCATEGORY_ID int REFERENCES SUBCATEGORY(ID),
QTY int DEFAULT (5),
PRICE DECIMAL(10,2) not null,
)
</sql>
 |
One problem with this example is that it uses the IDENTITY keyword to define the primary key ID of the product. The IDENTITY keyword is removed in the second iteration of our app. If you are using a database that does not support the IDENTITY keyword (such as Access or Oracle), then you will have to change this line of code to something equivalent for your RDBMS system.
|
After we create the tables, we need to populate them with some sample data. The tables model a hierarchal data structure, so we have to insert category rows, then subcategory rows, and then products. First we insert the parent category.
insert into category (description, name, id) values ('test data', 'test',777)
Then we insert the subcategory and associate it with the parent category:
insert into subcategory (name, description, fk_category_ID, id) values ('dogs', 'dogs description', 777, 111)
. . .
Finally, we add products to the subcategory:
insert into Product (description, name, price, fk_subcategory_id)
values('Poodle description','Poodle',1,111)
insert into Product (description, name, price, fk_subcategory_id)
values('Scottie description','Scottie',1,111)
insert into Product (description, name, price, fk_subcategory_id)
values('Schnauzer description','Schnauzer',1,111)
. . .
Sometimes we need to make additions to the tables, and we want to incorporate the additions into the build. Thus, it is very convenient to delete the tables and the test data. We add the target dropTables, defined as follows:
<target >
<sql driver="${driver}" url="${url}" userid="${user}" password="${password}" >
DROP TABLE PRODUCT DROP TABLE SUBCATEGORY
DROP TABLE CATEGORY
</sql>
</target>
Notice from the DDL in the createTables target that there are primary key constraints; thus the tables must be dropped in the order specified here.
| Previous Next |