Unit test is the process by which we can make sure our code is working as our expectation. Actually most of the time we write unit test against frond-end code(ui level, business, dataaccess) and we are not very familiour that in respect of database level.

But some different purpose we can use it and get befinited. I an trying to explain it from my working experiance.
 
When any one work for maintaining/supporting any application then he/she  often need to execute db script for insert/update/delete record directly into the database. It is actually very risky job. Within very few seconds you can damange consistency of your database. But there are some reasons you need to do this job. Some are
 
1. Find bug which already alter some data. Now need to fix that.
2. Requirement need to fulfill which does not support by application.
3. Data migration.
4. By passing application level security and alter data directly.
5. Change data that data manipulation Interface not provided by the application.
6. many more...
 
DB script which i want to execute can decrease risk factors and save our life if we can use unit test properly. Ok understand but how?
 
Senario: 
Need to change a Standard status which number is #1000.
 
What to do? 
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 and may be many more.
 
So Script 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();
--and many more...

We often execute that script in our stagging server. Our QA team test it and if they found ok then we directly execute that in production db server. 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.

we easily found some risk factors are there. Risk factors are
 
1. If developer enter wrong value(TableName, ColumnName, ParameterValue)
2. If developer miss to include some more tables.
3. If developer's join operation produce cross join and affect more rows then expected.
4. If tester not cover all senario.
5. If tester skip some table/column check.
6. If staging server and production server not syncronous.
7. many more...
 
So easily we can decrease risk to our script using unit test. Then how looks like my script is. The sample 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;
 
--UNIT 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
     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
 
After analysing above new script we can easily understand the purpose of unit test block  in script. It will make sure that our script must change expected value and if any disaster(reason may cross/wrong join, wrong value enter, duplicate data in database) happend then @Affected variable contains wrong value and script unit test failed and rollback all changes and notify developer that something wrong and developer start investigating and fix the problem and our production/stagging server data will be consistent.
 
Same technic can use when execute any stored procedure.
 
Some times we see that from application, QA team can not tested every aspect due to limitation of application features. They need to see database table for make sure that any new features/fixes are working 100% fine. In that senario QA team also use that type of Unit test. QA's/Developers can store some predefined Unit test scripts which can make sure any operation is perfectly all right after new implementation/refactor/deployment done.
 
Some Tips for writing Unit Test for Script Execution:
 
1. Do not copy filter criteria from main script. Besides that you type this criteria again and again. Because very few possibilites to make same mistakes twice.
 
2. When wrting Unit test think success criteria independently. Means what type of table modfication  you excepted from the requirement not from the query you write.
 
3.  If unit test is failed then you replace commit transaction into rollback transaction. Then try to analyse why unit test failed with the help of print statement which show the actual and expected value to execution window.
 
4.  After finishing Script, when you read execution code then your mind set should be what data you are trying to  update, and when read unit test code then what changed data you expected.
 
5. If any one knows requirement and table structure better then for make sure you can share what table  data you modifed based on unit test though if anything missed then he/she can easily tell you.
 
6. If Unit Test code is going to bigger and bigger then cutdown you unit test code based on priority. You will  set the priority based on complexity(no of joins, subqueries, complex data type(xml etc).
 
7. Some times we need to run 1 or line of very simple action query to production server. In that case you  can ignore that. But my suggestion is that time also you write that unit test for habituate that.   
 
8. Some times you see that you can not easily find the unit test criteria for which you can test. In that situation you need to think out of box to find out criteria.   
   
9. 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 unit test code with test criteria.
  
   BEGIN TRY
   BEGIN TRAN
  --Added script logic   
   --UNIT TEST BLOCK
 --Added test logic
  COMMIT TRAN
  ROLLBACK TRAN
  PRINT '!!!SUCCESS!!'
  PRINT '???FAILED.PLS FIX AND TRY AGAIN???'
   END TRY
   BEGIN CATCH
    ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
   END CATCH
 
10. Never write unit test in a hurry. Think and think then set your success criteria. 
 
11. Some times you may think script unit test take more time then actual script. Be patient,   do not give up write unit test in this senario also.
 
12. When estimating your time for any script execution, you must added your unit test time there.  If your authority not happy to your time     then also i suggest think about unit test. Otherwise  your job will be in a very high risk.

Comments

rokon's picture

When i came to know that Habib vi is using this type of Unit testing on sql script, I am writing unit testing on my daily digests seeing its benefit. Actually there is two benefits that i get:
1. writing script in this fashion we can have a look on resultant data before executing  the actual script.  
2. Based on the test case we can check wheather we are writing right script or not.

behestee's picture

hmm, looks great...

Add new comment