My Approach to Test Driven Development Part 3 - Testing Against a Database

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);

<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>

<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>

<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>