How to automate a test that queries from a large database? (import or create)

Question

Say I have the following scenario (and obviously I oversimplify it): A 'Manager' in a 'Department' 'Approves' 'Expenses' of 'Employees'.

My test here is to test a Query that returns the followings: All expenses above $X for all managers from department 'HR' between D1 and D2 etc...

I realized there are 2 approches to do this:

1. Start from scratch - create a User/Group User, add permissions, create level of security, add expenses and test your query.

2. Import a database that already has some data and test that the return result is just like what you expect to get?

Problem with option 1 is that it takes so long to 'create the environment' in order to test it.

problem with option 2 is that it takes so long to 'import' a 1GB (or more) database and then simply run your query and see if the expected result are correct.

I was wondering if there's a 3rd approach? or any improvement to the existing alternative above.

Thanks!!!

Answer 1

I recommend you take a look at DbUnit - it's a library designed to facilitate database testing. The approach I usually take is to write some code in a main method which will generate the test data I need in .csv files (one per table) and then use H2 to create in-memory tables populated with this data.

This typically looks something like:

public static void main(final String args) {
  final DatabaseConnection conn = new DatabaseConnection(getConnectionToDbWithRealData());
  final QueryDataSet dataset = new QueryDataSet(conn);
  dataset.addTable("foo", "select * from foo where bar = 'xyz'");
  new CsvDataSetWriter(getOutputFile()).write(dataset);
}

and

CREATE TABLE foo (
  ...
) AS SELECT * FROM CSVREAD('classpath:/foo.csv', null, 'null=null');

It should take an hour or so to get your dataset created, but then you can re-generated it on demand in a few seconds.

Answer 2

A third option could be to test it on a smaller database, obviously, unless you're doing some performance testing. If you go this way, you might as well try and use a small in-memory db to reduce the performance cost to test your queries.

For big queries that are noit destructive (really only read info in the database), then you don't necessaraily have to recreate the db every time - your test might "assume" that a database exists, at a well-known location, that is only infrequently created.

Also, as always, don't hesitate to test as much of your system as possible against a "mock" db, especially if you really want to test :

• how the query parameters are set
• what the query looks like in the end
• or how the query result is handled,
• etc... rather than how the query is actually run by your db engine.