[coldbox-3.5.1] Mock Database Content

Communities ColdBox HMVC
1

Hi

Is there any way to mock database tables and the content of those
tables? I would like to test code, where I insert, edit and delete
table contents by SQL statements and I would like to do that without
inserting test records and having to delete them at the end of the
tests.

Thanks for your help!

2

mockBox is what you need.

3

Thanks Andrew, I've seen that I can mock a database with the
DatasourceBean, but that's the whole database, how can I create tables
in there? And how can I create recordsets in those tables?

4

You don’t you use mock box to return dummy information, essentially mocking the DB call. But this is provided that you are using the likes of DAO and gateways or ORM to do that.

5

But I want to test, if everything was inserted, updated and deleted
the right way. I want to test the functions that are doing the
inserting, the updating and the deleting and not mock them, meaning I
want to test the gateway itself.

I am using DAO and gateways.

6

Have you looked at wrapping your tests in a transaction? You can do whatever data manipulation you want for real and verify it, then rollback at the end.

Thanks!

~Brad

7

We generally do what Brad suggested. However, with ORM entities, I
generally create a convenience method or two that gets run after each
test to clean up any test data that may have been created.

Jason Durham

8

I’ve setup various methods to assert SQL behavior. With ORM, I ended up setting up a second ‘Unit Test’ database that gets reset between tests. This allows me to confirm a fetch operation returns the expected ‘42’ records and so on.

We’ve abandoned ORM development for front-end facing projects in favor of highly-turned SQL statements. Most of these test cases simply asset the SQL generated is as expected (since the SQL is dynamically generated by a DAO based on the arguments). Switching to just validating the SQL statement accelerated our CI builds by 40m!

Here is an example test case:

public void function setUp()
{
// Mocks
MockBox = new coldbox.system.testing.MockBox();
SUT = new platform.shared.models.DAOs.indexedDAO();
MockBox.prepareMock(SUT);

Q = MockBox.createStub();
qExecuted = MockBox.createStub();
qPrefix = structNew();

SlowCache = MockBox.createStub();
SlowCache
.$(‘get’)
.$(‘set’);

QuickCache = MockBox.createStub();
QuickCache
.$(‘get’)
.$(‘set’);

SUT
.$property(‘ApplicationContext’,‘variables’,‘production’)
.$property(‘QuickCache’,‘variables’,QuickCache)
.$property(‘SlowCache’,‘variables’,SlowCache);
}

public void function EPISODES_can_use_product_urlname_filter()
{
var expectedSQL = “select this from that were something = :else and so_on and so_on”;

// Results are ignored.
qResult = MockBox.querySim(‘this#chr(10)#1’);
qExecuted
.$(‘getPrefix’,{columnlist=‘asset_id’})
.$(‘getResult’,qResult);

SUT.$(‘getQueryObject’,Q);
Q.$(‘setSQL’).$(‘addParam’).$(‘execute’,qExecuted).$(‘setMaxrows’);

local.r = SUT.get(target=‘episode’,lid=4,allowPaging=false,filters=[{k=‘product_urlname’,v=‘photoshop-elements’}]);

assertEquals(expectedSQL,q.$callLog().setSQL[1][1]);
}

9

Thanks a lot for all the answers, I will try your suggestions!