I have a situation I'm hoping you can provide some insight into. I am thinking about test automation (JAVA) for a system that goes through various stages of processing and has a large Oracle database (1000+ tables) at its core.
E.g. process 1 - Run Process1 -> Find file with customer details -> load details to staging tables in database. process 2 - Vet customer details -> Run Process2 -> Customer details found in staging tables will be moved to proper tables.
Now from an automation point of view I need to re-initialize the database state each time before I run a test, but the problem is that the database instance will be shared by some others. Without getting into the why we don't have a separate database instance, does anyone know how to contain the state? So for example imagine a database with a table Customer, that contains a column FirstName.
Before test run: FirstName contains the data 'Peter', 'Paul',
After test run: FirstName contains the data 'Peter', 'Paul', 'Jack', 'Amy'
Before next test run: What is an effective way to bring the database state back to how it was before the last test run i.e. the data contained should be 'Peter', 'Paul' again.
I have seen DBUnit but my understanding is that whilst you can initialize the state with your own data before a test run, it will also clear all data before a test run. The problem is that I don't want to clear all data before a test run, but just that data that was introduced as part of the last test run.
I hope that was clear :) Thanks!
Refer to the DatabaseOperation class [0] for dbUnit operation choices before and after each test. Additional notes are on the Components page [1]. Particularly for your case, consider UPDATE, DELETE, and REFRESH.
A good approach for database integration tests and acceptance/end-to-end tests is to isolate the scenarios as much as possible, including the data sets. E.g. each test should setup and cleanup its own data. Consider only sharing the data setup files (inserted/updated before a test and cleanup after each test), not sharing via leaving data in the database.
Lastly, strongly consider making a db instance dedicated for the automated tests so there is no other considerations and interference for the data. Commonly, use a db such as Apache Derby (using its in-memory mode) for this if it is not possible to allocate another Oracle instance.
[0] http://dbunit.sourceforge.net/apidocs/org/dbunit/operation/DatabaseOperation.html
[1] http://dbunit.sourceforge.net/components.html#DatabaseOperation