Database test models

It will be nice to have automated continues tests for a database. Unfortunately testing a database is not as simple as testing an applications. Let’s consider there different models for testing a database.

Directly calling database procedures/functions and verifying responses.

This model is suitable for unit tests as it allows direct access to database objects. On the other hand testing complex use cases might require a series of calls that makes it more difficult.

Calling business application that is connected to database and verifying responses.

This model is suitable for testing business functionality through the business applications. But it is not suitable for unit tests or testing asynchronies processes as the responses might not always be available from the application or might not include all expected details about how the database performed a request.

Calling business application that is connected to database and verifying database changes/events.

It is the most complex model. It uses business application to request calls to the database and it assumes that the database has an agent that detects specific database events. The events are stored in event log and based on that a database monitor can process the events and forward it to the test engine for matching. The model is suitable for both low-level unit tests of elementary database objects as well as for testing complex business functions including asynchronies processes. On the other hand the model requires additional resources to run the agent, log and database monitor. And it requires more configuration to define and detect database events and additional configuration to match calls with events that might arrive asynchronously. One technique that helps to simplified the matching process might be adding distinguish identifier to each test if the application allows it and then storing the identifier with each change within the database. A practical example of an identifier could an USER id. To run a test in this model it will required a configuration of series of tests, and each test must include four configuration elements:

  • Definition of a call to application.
  • List of events that the Database Event Agent should watch for and then store to Event Log (both positive and negative).
  • List of events that Database Monitor should forward to the Test Engine.
  • Matching rules for the Test Engine that will define success and fail results.

The model might be suitable for continues integration of a complex database systems.

SCM for Oracle benefits by examples

The software configuration management process worked in a company and proved many benefits in the 2011.

Over 120 patches were build for 5 deliveries. The automatic  processes for building patches and upgrades saved on average between 1-2h for each build. It makes about 180h saved of the config manager time. And besides that, the build process can be driven by an average IT employee, who don’t need to be a configuration management expert. The same is true for administrator’s skills.

Additionally the automatic processes allow to eliminate human errors.  Assuming 5% human error rate out of 120 patches it makes 6 issues that hypothetically will required additional time of the config manager, maybe developers, administrator etc.  It sums up to about 20h and eliminates costly  delays when business would wait for fixing problems.

Simultaneous  work on multiple versions (represented by parallel branches in version repository) allows the manager of the IT team to assign more efficiently tasks to developers avoiding idle times. The developers can share their time to work on a few different changes in different branches – maybe closing support with regards to the current unit tested version and kicking-off the next version. It also gives the team manager more flexibility. It means maybe a few (let’s say 2) days saved for each developer. Assuming a team of 5 developers it makes 80h.

From the business perspective, the automated process of building patches on demand allowed changes to be delivered more frequently and more closely to the business schedule. On the other hand unit tests can be executed simultaneously with regards to different versions. And the IT can address new issues and deliver fixes on timely basis.  It means that more versions can be tested and delivery in one year.  It helped to deliver about 20% more changes and improvements to production for the company in question.  In fact it is a scalable solution that depends on the available resources and the range of up to 50% sounds reasonable.

And least but not last. The process helped to organized work and relations between many employees reducing the stress situations.  It added more understanding and supported positive culture of work.