[testbox2.1] Understanding how to test a method that simply does a database query

Communities ColdBox HMVC
1

Here is a sample of a simple event which accepts up to 8 optional arguments and returns some data. What should I be testing for? And does anyone have an example of a test suite for something like this?

Model:
`

SELECT DISTINCT clientname FROM dbo.projectMetaSearch_view WHERE 1=1 AND wbs1 LIKE AND wbs2 LIKE AND wbs3 LIKE AND projectname LIKE AND clientname LIKE AND pmemployee LIKE AND tlemployee LIKE AND qaemployee LIKE ORDER BY clientname `

Handler:
`
component extends=“handlers.baseHandler” {

property name=“projectMetaService” inject=“projectMetaService”;
property name=“queryToArray” inject=“queryToArray”;

// Client Names
function getClientNames(event,rc,prc){

var results = projectMetaService.getClientNames(rc);
prc.results.data.clientnames = QueryToArray.convert(results);

}
}
`

Test:
`
component extends=“coldbox.system.testing.BaseTestCase” {

// executes before all suites+specs in the run() method
function beforeAll(){
// do your own stuff here
// super.setup();
super.beforeAll();
}

// executes after all suites+specs in the run() method
function afterAll(){
super.afterAll();
// do your own stuff here
}

function run(){
describe( title=“Project Meta Methods”, body=function(){

beforeEach(function(){
reset();
structClear(FORM);
});

it( title=“should get City of Arizona”, labels=“super”, body=function(){
FORM.wbs1 = ‘5COR0101’;
FORM.wbs2 = ‘00001’;
FORM.wbs3 = ‘5AA’;
FORM.projectname = ‘Arizona Street’;
FORM.clientname = ‘City of Arizona’;
FORM.projectmanager = ‘Pablo’;
FORM.teamlead = ‘Maria’;
FORM.qamanager = ‘Juan’;
var event = execute( event=“project:meta.getClientNames”);
expect( getRequestContext().getRenderData().DATA.data.PROJECTS[1].clientname ).toBe(“City of Arizona”);
});

it( title=“should get a list of client names”, labels=“super”, body=function(){
var event = execute( event=“project:meta.getClientNames”);
expect( ArrayLen(getRequestContext().getRenderData().DATA.data.PROJECTS) ).toBeGTE(1);
});
}
}
}
`

2

We have ran into the similar issues trying test a function that simply does a db query. We are trying to get away from sample data and simply testing to confirm the SQL is correct. So I wrote a custom assertSQL call that comes the sql metadata.

Custom Assert

`



actual = ReReplace(ARGUMENTS.arg1.getMetadata().getExtendedMetaData().sql, "[[:space:]]","","ALL"); expected = ReReplace(ARGUMENTS.arg2, "[[:space:]]","","ALL");

assertEquals(actual,expected,ARGUMENTS.message);

`

So we can test this way by calling our function and test the resulting sql that is returned:

Test:

`
/* ONLY SYNC FLAG */
var actual = pricingObj.getInvoice(syncFlag = 1);
var expected = “Select Top 1 from pricing Where syncFlag = ?”;
assertSQL(actual, expected, “SQL Does Not Match”);

/* ONLY ICLASS */
var actual = pricingObj.getCustomers(class = ‘Test’);
var expected = “Select TOP 1 From CustomerMaster where iClass = ?”;
assertSQL(actual, expected, “SQL Does Not Match”);
`

This way I can test that CF will generate the correct SQL, we are not testing SQL but CF…This makes testing these types of functions easy, but this is only an idea and would be interested if anyone else has other opinions about this situation, other than generating sample data. (BTW: We are looking into Liquidbase as a possible future solution to build out sample data, but can’t get over the cost currently)

Thanks

3

Ahh, you are saying it doesn’t really matter what data is returned from the query because that could change. As long as the expected sql statement is generated then the function is doing what its suppose to be doing. I like it.

I could then just do something like:

statement = ReReplace(ARGUMENTS.arg1.getMetadata().getExtendedMetaData().sql, "[[:space:]]","","ALL"); expect(statement).contains("ANDwbs1LIKE?","SQL Does Not Match"); expect(statement).contains("ANDwbs2LIKE?","SQL Does Not Match"); expect(statement).contains("ANDwbs3LIKE?","SQL Does Not Match"); etc...

I’m still interested in knowing other solutions from the community.

4

There were some really good discussions on the old MXUnit list about this sort of stuff.

I’ve done a few things - not sure if any of them are ‘correct’.

Populate sample data in the setup/before - then query for that and check results. Then delete that sample data in tear down.

Just run any query and check to see if isQuery() and maybe recordcount > 0.

I think the answer is ‘it depends’ :slight_smile: I do like Garrett’s solution as well.

Jim

5

Setup and teardown are also expensive as they run for each test, or in bdd, beforeEach and afterEach.

We would recommend doing so in beforeTests() and afterTests() which affect the entire testing bundle.

Luis Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com
P/F: 1-888-557-8057
Direct: (909) 248-3408

ColdBox Platform: http://www.coldbox.org

ContentBox Platform: http://www.gocontentbox.org
Linked In: http://www.linkedin.com/pub/3/731/483

Social: twitter.com/ortussolutions | twitter.com/coldbox | twitter.com/lmajano | twitter.com/gocontentbox

6

Check GetXindi CMS test cases, GitHub - simonbingham/xindi: Xindi is a lightweight CFML content management system ideally suited to smaller websites.

It has similar test cases