Database script execution with test

Introduction

Testing is a very helpful tool for checking code behavior and help to write bug free code. In this article I try to explain how test will be benefited when we maintain any application database in production and manipulation data with the help of script execution. Actually I want share my knowledge with my real life experience.

Background

Testing is the process by which we can make sure our code is working as our expectation. Most of the time we write test against application code (ui, business, data access layer) and we are not very familiar that in respect of database script execution level. But in database script execution purpose we can use it and get definite.<u1:p>

Why script execution is needed

We familiar the scenario when we create/deploy database and its objects, alter database schema, insert lookup data. All these scenarios we execute scripts in our database. Think a scenario, when client report a bug from its application. You need to fix it. After investigation, you found that your code has bug and you deploy that code. But also you need to update data in production database. Think another scenario where client request to you to update some production data and you have no any user interface to update that data. So you have no option to execute script which can alter production data. If I want to create a reason list where script will need to alter data, the reasons are like as follows

• Find bug which already alter some data. Now need to fix that data.
• Requirement needs to fulfill which does not support by application directly.
• Data migration.
• By passing application level security and alter data directly.
• Need to manipulate data but application has no any user interface.
• By pass business rule for a certain time and alter data directly.
• May be more...

Requirement

Need to change a Standard (an entity) status which number is #1000 in production database. Assume that there are many tables data need to alter and no application ui exists to solve the problem. So we need to alter data directly in our production database.

What to do then

Steps are given below:

• Update Standard Table.
• Insert new record to Standard Version table,
• update Last Endorsed version,
• Insert New Record To StatusChangeTable,
• Insert new record to NoteTable,
• Insert New Record to NoteVersionTable.

Solution

We need to prepare script file and write SQL for solve the problem. SQL like as follows

DECLARE @SId INT;

SET @SId = (SELECT MId FROM Standards WHERE MNumber = '100';

--alter status in Standard Table
UPDATE Standards SET Stutatus = 99, Version = Version + 1, DTS = DTS + 1 WHERE MId = @Mid;

--add new version record for specific standard
INSERT StandardVersions SELECT * FROM Standards WHERE Id = @MId

--alter last 50 status version record from version table
UPDATE StandardVersions SET Published = 0 WHERE Id = (SELECT Max(Id) FROM Standards WHERE SId = @SId
AND Status = 50)
 
--add new note
INSERT Notes(Title, VersionId) VALUES('Status changed by user', SCOPE_IDENTITY());

--Version that note
INSERT NotesVersion SELECT * FROM Notes WHERE Id = SCOPE_IDENTITY();  

In real life, we often execute that script in our staging server first. Our QA team tests it and if they found working then next we directly execute that in production database server. But you should always mind it that any data alter from production database directly; its test scope will be very high and difficult to QA team to cover whole scope. It is a just single standard status change script. Sometimes we got 10-110 Standard status change, renumbering, delete request and many more. Think that you write that type of script and imagine the script size and complexity and if any do any mistake then what will be its outcome.

Risk factors

We found that risk factors are present when directly execute database script in production. They are as follows

• If type wrong value in script (TableName, ColumnName, ParameterValue).
• If miss to include required tables.
• If join operation produce cross join and affect more rows than expected.
• If tester not cover all scenarios.
• If tester skip some table/column check.
• If staging server and production server not synchronous.
• may be more...

Decrease risk factors

So we can decrease risk to our script adding test block. Then how will be my script? The sample script is as follows

 --Start Script 
TRY
BEGIN TRANSACTION 

DECLARE @SId INT, @NewVersionId INT, @NewNoteId INT, @Affected INT;

SET @Affected = 0;  
SET @SId = (SELECT MId FROM Standards WHERE MNumber = '100';

UPDATE Standards SET Stutatus = 99, Version = Version + 1, DTS = DTS + 1 WHERE MId =@Mid;
SET @Affected = @Affected + @@RowCount; 

INSERT StandardVersions SELECT * FROM Standards WHERE Id = @MId; 

SET @Affected = @Affected + @@RowCount; 
SET @NewVersionId = SCOPE_IDENTITY();

UPDATE StandardVersions SET Published = 0 WHERE Id = (SELECT Max(Id) FROM Standards WHERE  SId = @SId AND Status = 50); 
SET @Affected = @Affected + @@RowCount; 

INSERT Notes(Title, VersionId) VALUES('Status changed by user', NewVersionId);

SET @Affected = @Affected + @@RowCount;
SET @NewNoteId = SCOPE_IDENTITY(); 

INSERT NotesVersion SELECT * FROM Notes WHERE Id = @NewNoteId;
SET @Affected = @Affected + @@RowCount; 



--TEST BLOCK
DECLARE @StatusMain INT, @VersionMain INT, @StatusVersion INT, @VersionVersion INT;

SELECT @StatusMain = S.Status, @VersionMain = S.Version FROM Standards S WHERE S.MNumber = '100';
SELECT @StatusVersion = S.Status, @VersionVersion = S.Version FROM Standards S WHERE S.MNumber = '100';
 
IF @StatusMain = 99 AND @VersionMain = 3 AND @Statusmain = @StatusVersion AND @VersionMain = VersionVersion AND @Affected = 5  
BEGIN  
--when all my expected result will be matched then commit that transaction 
     COMMIT TRANSACTION;
    PRINT '!!!SUCCESS!!!' 
ELSE 
BEGIN 
      ROLLBACK TRNSACTION;
      PRINT @StatusMain;
      PRINT @VersionMain;
      PRINT @StatusMain ;
      PRINT @StatusVersion; 
      PRINT @Affected
      PRINT '???FAILED. PLS CORRECT SCRIPT AND TRY AGAIN????'
  END
  END
  BEGIN CATCH
  ROLLBACK TRANSACTION;
  PRINT ERROR_MESSAGE();
  END CATCH 
--End Script  

Analyzing test script block

After analyzing above script we can easily understand the purpose of test block. It will make sure that our script must meet our expectation against data manipulation, if any disaster (reason may cross/wrong join, wrong value enter, duplicate data in database) happened then @Affected variable contains wrong value and script test failed and rollback all changes and notify developer that something wrong and developer start investigating then identifying and fix the problem and our production/staging database server and data will be accurate. Testing block and expected criteria may be differing based on requirement. Same technique can use when execute any stored procedure for alter data in database (by passing application).

Sometimes we see that from application, QA team cannot test every aspect due to limitation of some constraints. They need to see database table for make sure that any new features/fixes are working 100% fine. In that scenario QA team also use that type of test. QA's/Developers can store some predefined test scripts which can make sure any operation is perfectly all right after new implementation/re-factor/deployment done.

Tips for writing test block

• Do not copy filter criteria from main script. Besides that you type these criteria again and again. Because very few possibilities to make same mistakes twice.
• When writing test think success criteria independently. Means what type of table modification you excepted from the requirement, not from the query you write.
• If test is failed then you replace commit transaction into rollback transaction. Then try to analyse why test failed with the help of print statement which show the actual and expected value to execution window.
• After finishing script, when you read execution code then your mind set should be what data you are trying to insert/update/delete, and when read test code then what changed data you expected.
• If anyone knows requirement and table structure better than for make sure you can share what table data you modified based on test though if anything missed then he/she can easily tell you.
• If Test code is going to bigger and bigger then cut down you  test code based on priority. You will set the priority based on complexity (no. of joins, sub-queries, complex data type (XML etc.).
• Sometimes we need to run 1 or 2 lines of very simple data manipulation query to production server. In that case you can ignore that. But my suggestion is, that time also, you write that  test for habituate that.
• Sometimes you see that you cannot easily find the test criteria for which you can test. In that situation you need to think out of box to find out criteria.
• Never write test in a hurry. Take time, think and think then set your success criteria.
• Sometimes you may think script test take more time than actual script. Keep patient, do not give up write test in this scenario also.
• When estimating your time for any script execution, you must add your test time there. If your authority not happy to your time then also i suggest think about test. Otherwise your job will be in risk.

Script Template

You can create a script file like Template.sql and it may contain following script code and when required just added your script code and test code with test criteria.

BEGIN TRY

BEGIN TRANSACTION

--Added script logic...
--TEST BLOCK
--Added test logic...
COMMIT TRANSACTION;
PRINT '!!!SUCCESS!!'

END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION;
   PRINT ERROR_MESSAGE();
   PRINT '???FAILED.PLS FIX AND TRY AGAIN???'
END CATCH   

Points of Interest

Apply testing technique in database script execution level will be helpful and decrease risk factors when data need to alter in production database. In real life, when execute any database script in production database then need to take more and more extra caution. The objective of the extra caution is, no data should be accidentally altered and no inconstant data will be produced. Testing technique can be used as extra caution for protect production data for accidental altered.