In many environments, having a code review policy and procedure is a worthwhile and sometimes necessary practice. Maintaining a regression testing process is almost universally important.
Just keeping track of who reviewed what can be tedious and even burdensome if you don’t have a tool that manages that tedium effectively. Managing a suite of tests for simple procedures can also be burdensome if you don’t have a working process.
While TFS provides some solutions for both problems, not everyone has the luxury of that kind of environment. In my case, the client was under pressure to implement a solution quickly and without a budget. The two problems turn out to be related because review of the code should include review of associated regression test cases as well.
Microsoft SQL Server has, for many years, supported Extended Properties on database objects. The most commonly used extended property is MS_Description. SSMS uses this property to store “Description” text you supply in the ‘Properties’ window of table or diagram wizard maintained objects. Many third party tools such as Apex SQL and Red Gate also use this property to store object descriptions.
It’s an often overlooked fact that an almost limitless number of extended properties can be placed on a database object making them ideal for keeping track of many ancillary bits of data such as the code-review status of an object.
The stored procedure below implements a ‘poor man’s review tracking system. It makes use of extended properties to allow developers to keep track of what is ready for review, what has been approved and what needs changes before being approved. The property dbo.CodeReviewStatus defines and is named CodeReviewStatus. The procedure definition also demonstrates a simple mechanism for insuring that working regression tests exist.
Developers use the stored procedure to flag any database object as ready for review.
Ex: EXEC CodeReviewStatus ‘Review’ ,‘dbo.CodeReviewStatus’ — Mark my review procedure for review
Other developers or responsible persons use the same stored procedure to list items that are ready for review.
Ex: EXEC CodeReviewStatus ‘LIST’
When a review is complete, the stored procedure is used again to mark the object as approved or rejected.
Ex: EXEC CodeReviewStatus ‘Approve’ ,‘dbo.CodeReviewStatus’
Multiple people can review the same code with this procedure.
The definition of dbo.CodeReviewStatus (listed below) also contains an example of a very simple mechanism for managing regression test cases. At the end of the procedure, a comment block containing commands that exercise the procedure, is included. When performing maintenance on the procedure, a developer simply comments out the block comment start characters (change /* to –/*). Now, every time they alter the procedure, they also execute the list of test cases. When changes to both the procedure and test cases are complete they simply change –/* back to /* and execute the ALTER one more time to save all the test cases along with the procedure.
Using this one, rather lightweight procedure, and a ‘trailing comment’ convention both code review and regression test problems can be managed in a relatively simple way.
Testing and using the stored procedure
The test script at the end of this procedure assumes certain database objects exist. Obviously, once you define the CodeReviewStatus procedure itself it will exist but the test cases also refer to a schema named Hierarchy and a table trigger named iuLog. You can point the test cases to any objects that do exist in your environment or create objects with the expected names as you prefer. The definition of iuLog is not important. In fact, as long as an object by that name exists (it doesn’t even need to be a trigger), the test should work as documented.