SqlCredit - Part 3: Schema/Proc Updates and Automated Unit Testing

March 23, 2007

This is part 3 of a series. If you have not read part 2, you can find it here.

In this month’s installment, we will discuss updates based on feedback from part 2 as well as introduce automated unit testing.

Running Changes

As stated in part 1, “This series will be about the process that results in a well-designed, well-coded, and well-tested database.” Part of that process is updating the design and code based on feedback from other members of the team. In this case, the feedback generally comes from the forum.

Stored Procedure Naming Convention

I got strong feedback in the stored procedures thread that <object><action> naming is better than <action><object>, so I changed the naming convention.

Account and Cardholder Tables

Based on forum feedback on CardType and AccountType, I will add type (Gold, Platinum, etc.) to Account instead of Card. I originally planned to call this CardType, but that no longer seems appropriate. I will have to call it something other than “type” because that is not descriptive enough. If you have naming suggestions, pass them on. I know this is a fairly minor decision, but it is something typically discussed with other developers in a group.

The Gold/Platinum change caused me to challenge the placement of the “Partner” (Alaska Airlines, Lexus, etc.). That also looks like it needs to be part of the account and not the individual card.

If a person has more than one type or partner (a Gold card from Lexus and a Platinum card from Alaska Airlines), then those cards will be part of separate Accounts. That person’s personal data will be duplicated.

These changes will have to wait until the initial version of the tables is complete and tested.

Diagram

I had requests for an E/R diagram. This would definitely be helpful. In a normal project, I make a point of creating diagrams and passing them out to the developers. My goal is to see people using the E/R diagrams as a reference throughout the project. Here is a logical diagram of the currently-implemented schema:

DateTime and UTC

We had an interesting discussion about datetime columns and UTC. The datetime columns will remain UTC as they were originally.

Also, I found that using smalldatetime for DateCreated and DateLastUpdated made unit testing difficult. The precision is just not good enough for unit testing or, most likely, for production. I changed the columns to a full datetime.

NULLable Columns

I had set a number of columns to be nullable (Account.Address2, Cardholder.FirstName, etc.). I really don’t want to have to write “IF <col> IS NULL OR <col> = ''”code for these simple string values, so I changed those to be NOT NULL. I will leave it to the calling code to check for empty strings in places where a value is required.

Phone Columns

Somehow, I had named the three phone numbers HomePhone, MobilePhone, and WorkNumber. I fixed it by changing WorkNumber to WorkPhone.

Database Options

There was not as much forum discussion of database options as I had hoped, but I have done more reading based on prompting from Scott Hanselman and other regional directors. (See the References and Further Reading section at the end.) I made two updates to CreateDatabase.sql based on this feedback. Specifically, I added SET statements for ANSI_PADDING and NUMERIC_ROUNDABORT.

I am not at all satisfied with this one. I will continue to do my own research, and I have a few people helping me. I am hoping that I will get more feedback from readers in the forum.

CRUD Procedures for Account and Cardholder

The Account and Cardholder tables now have a complete set of CRUD stored procedures. The procedures include the updates to the tables. These are available in the attached code. Other Read stored procedures may be added later, but the base procedure is there now.

Automated Unit Testing Using TSqlTest

I wrote and published TSqlTest out of my own need to have an automated set of unit tests for stored procedures. It is written specifically for Microsoft SQL Server. There are options, most notably TSQLUnit and the recently-published Visual Studio Team Edition for Database Professionals.

What is TSqlTest and How is it Different?

TSqlTest is a simple, lightweight database test framework. It utilizes .sql and .bat scripts only. No perl, no VB, no ruby; just T-SQL code wrapped in a bit of batch file. It is designed to be used for automated testing and incorporated into a continuous integration environment.

It supports testing of stored procedures, functions, triggers, and database “state”. It can test pretty much anything written in T-SQL. I suspect it will be used for parts I haven’t even thought of yet.

Here is a diagram of the TSqlTest tables:

and an example of a test command and the output from the test run in the attached code:

>.\Tests .\RGINST1 full
===============================================
  Results from tests on database SqlCredit:
    Total  Tests: 23
    Failed Tests: 0
    Open Defects: 0
    Failed Files: 0
===============================================

Features

• Automated reporting (human-readable and XML/Nant output)
• Support for known defects
• Report results even if a script fails
• Scripts include before/after counters for tables (verify that the tests did not leave rows behind)

Testing Database “State”

TSqlTest allows you to write simple tests of database “state” to verify a certain level of “goodness” in your database before firing off the rest of the tests. Examples include testing for tables, indexes, stored procedures, functions, defaults, and data.

By convention, I always create a test scripts called _InitialDatabaseStateTests.sql for each database. The attached script tests for

• the existence of the TSqlTest framework
• the existence of the Account table
• the existence of the CreateAccount stored procedure
• the default for the Account.DateCreated column (making sure it is set to GETUTCDATE())

This script is great for adding simple tests as a database evolves over time. If you add a column to an existing table, add a test here that validates the existence of the column. If you change a default, add a test that validates the new default.

I have created a forum thread to discuss TSqlTest and database unit testing in general.

For Next Time

More testing, more running changes based on feedback, and ... finally ... security.

Download the code for this article.

References and Further Reading

• SET Options That Affect Results at MSDN
• Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 at TechNet
• Stored procedure recompiles and SET options by SQL Server MVP Tibor Karaszi
• SqlCredit - Part 1: Introduction, Business Problem, and Initial Design at Database Journal
• SqlCredit - Part 2: Creating the Database, Tables, CRUD Procedures at Database Journal

» See All Articles by Columnist Rob Garrison

SqlCredit - Developing a Complete SQL Server OLTP Database Project