Confidently unit testing code that relies on a database is troublesome. There are many different approaches and many opinions on the matter. I've previously written about using transactions to safely rollback the database after every test. This worked well for me at the time, but there are pitfalls in using that approach. Recently, I've done some more research on the subject and even attempted, without success, to integrate DBUnit into my database reliant unit tests. After some trial and error, I landed on the following approach that is a culmination of a few ideas I'd previously heard of, but never tried. The goal of this approach is to:

1. Run tests against a known data set.
2. Reset the entire database prior to each test.

The example code that follows requires a test database, test database user and a test datasource targeting the test database.In my previous post, I described my local setup for the fictitious site www.langerhans.local. Let's assume that the database for this site is named langerhans and it is a MySQL db. For testing purposes, I would create a test specific version called langerhans_test which can be either a point-in-time backup, or an initial creation script with static data tables. To reset my database to this state repeatedly, I use an sql script. The following script is a simple example of a database creation script with a few tables and default data. DROP schema langerhans_test;

CREATE schema langerhans_test;

USE langerhans_test;

DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
UserId INT UNSIGNED NOT NULL AUTO_INCREMENT,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Email varchar(100) NOT NULL,
Username varchar(16) NOT NULL,
Password char(32) NOT NULL,
PRIMARY KEY (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS tbl_role;
CREATE TABLE tbl_role (
RoleId TINYINT UNSIGNED NOT NULL,
Name varchar(50) NOT NULL,
Description varchar(100) NOT NULL,
PRIMARY KEY (RoleId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_role (RoleId,Name,Description) VALUES (1,'Master','Full priviledge account.');
INSERT INTO tbl_role (RoleId,Name,Description) VALUES (2,'Admin','Admin account with grant priviledge.');
INSERT INTO tbl_role (RoleId,Name,Description) VALUES (3,'User','Normal user account.');
INSERT INTO tbl_role (RoleId,Name,Description) VALUES (4,'Guest','Anonymous account.');

DROP TABLE IF EXISTS tbl_user_role;
CREATE TABLE tbl_user_role (
UserId INT UNSIGNED NOT NULL,
RoleId TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (UserId,RoleId),
KEY ix_UserId (UserId),
KEY ix_RoleId (RoleId),
CONSTRAINT user_role_has_user FOREIGN KEY (UserId) REFERENCES tbl_user (UserId),
CONSTRAINT user_role_has_role FOREIGN KEY (RoleId) REFERENCES tbl_role (RoleId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS tbl_permission;
CREATE TABLE tbl_permission (
PermissionId TINYINT UNSIGNED NOT NULL,
Name varchar(50) NOT NULL,
Description varchar(100) NOT NULL,
PRIMARY KEY (PermissionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_permission (PermissionId,Name,Description) VALUES (1,'CREATE','create permission');
INSERT INTO tbl_permission (PermissionId,Name,Description) VALUES (2,'VIEW','view permission');
INSERT INTO tbl_permission (PermissionId,Name,Description) VALUES (3,'EDIT','edit permission');
INSERT INTO tbl_permission (PermissionId,Name,Description) VALUES (4,'DELETE','delete permission');
INSERT INTO tbl_permission (PermissionId,Name,Description) VALUES (5,'GRANT','grant permission');


DROP TABLE IF EXISTS tbl_role_permission;
CREATE TABLE tbl_role_permission (
RoleId TINYINT UNSIGNED NOT NULL,
PermissionId TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (RoleId,PermissionId),
KEY ix_RoleId (RoleId),
KEY ix_PermissionId (PermissionId),
CONSTRAINT role_permission_has_role FOREIGN KEY (RoleId) REFERENCES tbl_role (RoleId),
CONSTRAINT role_permission_has_permission FOREIGN KEY (PermissionId) REFERENCES tbl_permission (PermissionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (1,1);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (1,2);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (1,3);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (1,4);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (1,5);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (2,2);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (2,3);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (2,4);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (2,5);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (3,2);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (3,3);
INSERT INTO tbl_role_permission (RoleId,PermissionId) VALUES (4,2); To execute this script, I pass the path to the sql file and datasource to a utility component. <cfcomponent displayname="model.util.DBScript" output="false">

<cffunction name="init" access="public" returntype="any" output="false">
<cfargument name="datasource" type="string" required="true">
<cfargument name="source" type="string" required="true">
<cfset var script = "">
<cffile action="read" file="#expandPath(arguments.source)#" charset="utf-8" variable="script">
<cfset variables.datasource = arguments.datasource>
<cfset variables.source = source>
<cfset variables.script = script>
<cfreturn this>
</cffunction>

<cffunction name="execute" access="public" output="false" returntype="boolean">
<cfset var reset = "">
<cfset var success = false>
<cftry>
<cfquery name="reset" datasource="#variables.datasource#">
#preserveSingleQuotes(variables.script)#
</cfquery>
<cfset success = true>
<cfcatch type="database">
<cfthrow message="script [#variables.source#] could not be executed.">
</cfcatch>
</cftry>
<cfreturn success>
</cffunction>

</cfcomponent> I would use the following Coldspring configuration for testing a database gateway. <bean id="Gateway" class="path.to.Gateway">
<constructor-arg name="datasource">
<value>${datasource}</value>
</constructor-arg>
</bean>
<bean id="DBScript" class="model.util.DBScript">
<constructor-arg name="datasource"><value>${datasource}</value></constructor-arg>
<constructor-arg name="source"><value>${resetScript}</value></constructor-arg>
</bean> And finally, here is my Gateway test case. <cfcomponent displayname="GatewayTest" extends="tests.BaseTestCase">

<cffunction name="setUp" access="public" returntype="void">
<cfscript>
var beanDefFileLocation = expandPath("/" & Replace(Getmetadata(this).displayname,'.','/','all') & ".xml");
var params = structnew();
params.datasource = 'langerhans_test';
params.resetScript= '/sql/reset.sql';
setBeanFactory(beanDefFileLocation,params);
getBeanfactory().getBean('DBScript').execute();
</cfscript>
</cffunction>

<cffunction name="getRoleListReturnsQueryWithFourRecords" access="public" returntype="void">
var roleList = getBeanFactory().getBean("Gateway").getRoleList();
assertTrue(isQuery(roleList),"roleList is not a query");
assertTrue(roleList.recordcount eq 4);
</cffunction>

</cfcomponent> Executing the DBScript within the setup() method ensures that the database is reset to a known state prior to each test. This works well for a smallish database, but it might be unwieldy for larger projects or databases.