Initial Data

Nov 16, 2009 at 7:36 PM

As i am checking the T.S.T. project i wonder about the Initial Data. 

I have two databases. The "REDB" and the "MISReporting". The second database has a lot of SP's that evaluate the first database and store the results How do i validate the results if i don't know the expected results. Do i need to clean up the main database each time when running the SQLTST_xxx SP's and initialize with specified records in order to know what i am expecting. Or do i have to create a new emtpy database "REDBTest" which will contain the initial data. In that case i have the problem with "MISReporting" queries which refers to "REDB" and not to the "REDBTest". What is your opinion?


Nov 17, 2009 at 3:28 AM

You’d want to have a build environment where you have control of some test version of your DBs as opposed to an environment that uses production DBs. If you need to switch between test DBs and production DBs you can write a script that does restore DB from some backups. Unless you have really big DBs, switching from one set of backups to another will be relatively fast.

In many cases it is useful if as part of your build process you include the process that initializes your DBs. In that case the test automation can be integrated as one of the final steps of the build process and you can work on top of a clean and predictable set of DBs. In some cases you may also want to create a back-up of the clean DB (this typically takes a few seconds) and restore it at different moments during the test process.

Having this type of environment where you have a clean DB, you will have to set the initial data as part of your test. Typically a test will not do a lot of things. It will be focused on a limited aspect and it will have the following structure:

  1. The test fixture. The part where you set up the data context for your test. Can be INSERTS / UPDATES or even better calls to sprocs.
  2. Exercise the unit under test. Here you invoke the sprocs / functions, views, etc that are the subject of your test.
  3. Read and verify the expected result.

My point here is that 1. (the test fixture) is part of the test. Normally you should not rely on the right context being already in place. Rather you should start from a clean or at least predictable state and set-up the right context in the test.

There are plenty of reason why in a test environment you really want to avoid having to work with a production DB: Complexity that is out of your control, performance, the inability of scoping tests to only well defined scenarios, and so on.

Nov 17, 2009 at 8:10 AM

First of all thanks for your fast response.

I will create a secondary empty database "REDBTest" which will be used to hold the initial data from the test fixture. "MISReporting" Stored Procedures retrieves data from the "REDB". Instead of writing the statements to call them directly as below:

select, b.yyy 
from MISReporting.dbo.Table1 a 
inner join REDB.dbo.Table2 b 
on a.Id = b.Id

I will utilize the "Create Synonym" command as an abstruction layer. So using the following command on "MisReporting".

Create Synonym REDB_Table2 for REDBTest.dbo.Table2

And the TSQL statement inside the Stored Procedure on "MISReporting" database will be:

select, b.yyy 
from MISReporting.dbo.Table1 a 
inner join REDB_Table2 b 
on a.Id = b.Id

If i run the SQLTest Tests it will reference the "MISReporting" with "REDBTest" data.

To restore for production purpose i will use the command:

USE [MISReporting]

CREATE SYNONYM REDB_Table2 for REDB.dbo.Table2