What best practices should be applied to data generation for QA testing

Question

This question is kind of general and not very specific. We have a Java project that uses Oracle database. We are currently using SoapUI tool for the QA tests. Each test needs some data to exist on the database before it is run. Our current way of running the tests is as follows:

1. Before each test we run a .sql file (unique to the test) to load some data into the db
2. We run the SoapUI test
3. We use a general .sql file to erase the test data we inserted for the test
4. Go back to 1 and run the next test.

The advantage of this method for us is that each test runs on a "clean sheet" with it's own data and is unrelated to the other tests.

The disadvantage is that each time during development when something changes in the db, for example a column was added to a table, we need to change all of the sql scripts that inserts to this table instead of changing in one place, this makes it very hard to maintain the tests.

I wanted to know what are some of the industry "standards" ways of doing this kind of stuff, or to hear more approaches to solving this problem.

Any advice would be great.

Answer 1

Instead of using SQL scripts, I would recommend using something like dbUnit for importing your test data. dbUnit will generate database dumps in XML format (this will also allow you database-independent test data).

The advantage is that you can write (build) scripts that will

1. import the dbUnit datasets,
2. run the database update SQL scripts on them (if you don't have these, you're out of luck anyway), and then
3. export the dbUnit datasets again.

In this way, you get a completely automated update of your test data. Then you can still clean up after each of your tests, and just import the test data, retaining the "clean sheet" feature.

Each test case would then work something like this:

1. Setup: Import dataset via dbUnit
2. Execution: Execute your test based on the imported test data
3. Teardown: Delete all database content

There is a slight disadvantage, of course, and that is that preparing the test data will be a bit slower this way. - However, in my experience, this is more than outweighed by the automated updates as long as you generally keep your individual test datasets small and specific.

If importing datasets for each test case takes too long, you could also create groups of tests (and test data) and import datasets for each such group instead of for each single test case.

Answer 2

Personally I would write a test data generator that generates unique test data and pushes into the database via a direct api call as part of the setup for each test. That way you can run multiple tests in parallel and can scale up your automated test execution.

From my experience, if you are using SoapUI, you probably should be calling the applications API's directly from your test code.

Answer 3

You are on the right path with the "clean sheet" approach.

It would be best if every test runs on empty database and it fills in data in the database as a part of its own fixture. But if you find yourself using a lot of the same kind of the data for a bunch of tests, then a XML import file would be best.

Your testsuite should clear the database before each test, load the xml file with the data, run the test and optionally clear the database if it's successful.

You could make different test data files to be imported for each module of your application. So if you test the auth module, you would have test_auth_data.xml which would be imported only for tests from the auth module.

For data that would be needed only for a specific test use manual importing, but if you could use some library tied with your ORM, so you use your data models.

The problem you mention about maintaining the same database schema between the development/production databases and the test database could be solved via database migrations. They could be applied to both of the databases and the migration version should be the same before the tests. You could read the Ruby on Rails guide to migrations.

Answer 4

In this situation, we are using the approach exposed by Bruce McLeod.

We use a data generator to produce 4 datasets from the same configuration defined by the test case:

• the input data for the test (run manually or with qtp) to fill gui forms for example,
• the data inserted before test with soapui in the application to create the customers on which the test applies,
• the control data that will help to diagnose the result of the based on inserted data (the expected status of the customer on which the test is run)
• and finally the clean up data to be used to remove inserted data.

In our case we can batch preload data, batch run the tests, batch verify and batch cleanup.

Since I'm the owner of GEDIS Studio (http://www.gedis-studio.com) we used that generator. But the approach can be implemented with any good shaped generator.

But back to the core issue:how to maintain such a solution in a changing world ?

Well, we rely only on application API, we never load or remove data directly in the database - too risky as we don't have time to maintain knowledge on the db and their changes.

My two cents advice is to rely on app’s API