test_driven_development_example

Test Driven Development Example

---

Requirements
1. A table containing all state abbreviations for the USA.
2. the ability to add state codes for other countries.
3. Retrieve state code by either primary key or by [country], [state] combination.
4. Invalid search parameters returns a null.
5. Executable as "select [utility].[get_state_abbreviation] (1, null, null)".
6. Executable as "select [utility].[get_state_abbreviation] (null, N'USA', N'TX')".
6. Executable as "select [utility].[get_state_abbreviation] (46, N'USA', N'TX')".

---

Unit Tests
[utility_ut.get_state_abbreviation]
[utility_ut.insert_state]

The first objects to be written are the unit tests. Note that each attempts to fully specify the actions of the target object. While it is often recommended that you first write a test that fails, the behavior of these objects are to return null for a failure so it is difficult to do so. Writing a suite of tests allows us to test around this type of behavior. Test 1 for utility_ut.get_state_abbreviation may pass if the procedure has no functionality or the table is not loaded, but in that case Test 2 will fail.

Note how utility_ut.insert_state uses a transaction to allow it to insert a new state, tests, and then roll that insert back. This does work, but introduces the cosmetic issue of the identity column being incremented but not rolled back. If you reset it in the transaction the reset will be rolled back! The only way I know around this is to capture the value before using ident_current, then in a separate transaction after the test transaction finishes you test to see if the value is the still the same relative to the max id in the column. If it is, you can then reset it in that transaction. If it has changed between the time your test transaction committed and your reset transaction starts you can't reset it. The cleanest way to eliminate this problem is just to use [guid] [uniqueidentifier] default(newsequentialid()) for your primary key rather than an identity! The issue is the same, but no one can see it so it doesn't bother them!

In production I will typically use a randomly generated value for object names rather than a fixed value such as "newstate" and "ns", and I will still check to ensure that randomly generated value does not exist prior to attempting to insert it for the test! When writing tests such as these you want to remember that your test may be run months or years from now and conditions may have changed dramatically. You should write your test using caution when deciding on types and names of objects.

Notice that the unit tests are located in a dedicated _ut schema, utility_ut which allows them to be easily accessed but prevents them from cluttering up the production schema, utility.

---

Table
[utility_secure.state]
With the tests in place I can see that I need to create and populate a table with columns of id, country, state, and abbreviation. Because the tests imply uniqueness and are for country and state values I can infer that I need to create unique and check constraints to validate values. I enforce all lower case so that the retrieving method only has to cast one side of the statement and not both.

Think of a database table (a relation) as a vault for storing entries (tuples).

A bank vault (or strongroom) is a secure space where money, valuables, records, and documents can be stored. It is intended to protect their contents from theft, unauthorized use, fire, natural disasters, and other threats, just like a safe. But unlike safes, vaults are an integral part of the building within which they are built, using armored walls and a tightly fashioned door closed with a complex lock.

Notice that the table is located in a secure schema, utility_secure, while the access methods and tests are located in a public schema, utility. This simple mechanism allows you to easily restrict direct access to the table (data vault) to only those methods that are approved to have access.

---

Methods
[utility.get_state_abbreviation]
[utility.insert_state]
The methods can now be written to meet the tests using the table which has been built. All of the code in this example can be implemented on SQL Server and the tests will pass.

You may not like the way I've implemented the table or the methods. Good! Rewrite them! As long as your rewrite passes the unit tests above your rewrite is valid.

A core tenet of the Agile methodologies is that of you ain't gonna need it! or, do the simplest thing that could possibly work. Because an object can be refactored to extend its functionality or improve its performance at any time in the future, it is NOT necessary to speculate now and add PERCEIVED functionality! Keep it simple and meet the requirements, then move on.

I usually write methods with the following objectives in mind in (generally) this order:
1. The method must meet the requirements of the unit test(s).
2. The method should follow best practices both for the industry and for the environment.
3. The method should be self documenting.
4. The method should be loosely coupled.

Many of the astute among you will note the complete lack of concern for performance and immediately pronounce me as; "Charlatan!" "False Prophet!" "Evil Whore of Satan!" You judge me too quickly! (ok, maybe you're right in part but let's not go there! :). There are several reasons I do not include performance in my default objectives:

1. Performance should be one of the features evaluated in the unit tests. By stating that the method must meet the requirements of the unit test I HAVE met the performance requirements of the object.
2. It is easier to refactor a method for performance which is well written than it is to try and decouple a method and make it meet the self documenting principle when it has been written poorly.

I often find performance to be cited when developers build code that is very poorly written and includes tight coupling to other objects. Many times the developer cannot cite the performance requirements which he is trying to meet. Most often they simply write bad code and use performance as an excuse.

Write loosely coupled and self documenting code and you will find it easy to refactor it for performance gains should they be required.

You may not like the way I wrote the methods utility.get_state_abbreviation and utility.insert_state. In fact, I'm not overly happy with the way I wrote them. But they're cleanly formatted, reasonably self documenting, and they meet the unit tests. They are more than adequate for production in most cases. Feel free to rewrite them! I'd love to post some more examples that address the requirements by meeting the unit tests, but meet them using different techniques. Experiment with adding functionality to them. Create new unit tests (existing unit tests, once the code tested has been promoted to production, are never modified!) and extend the functionality.

---

Views/Synonyms
[utility.state]
You can use either views or synonyms in the public schema to allow access to the table in the secure schema. In this respect both views and synonyms are getter methods, [facade] patterns. Note that the view aliases all columns. While this is not necessary for the moment, as the alias names are the same as the column names, it reinforces the point that the business user need have no knowledge of the table structure itself. This mechanism allows you to rename or redefine the columns if need, or even to refactor the table into multiple tables.

The view can always be used as a structural pattern to present to the business (or to other objects) the expected interface. I will reinforce this by say that, other than the getter/setter methods, no object should directly access the table itself. This fully supports the objective of abstracting the implementation of the object from the representation of the object and dramatically enhances the robustness, usability, and life of your code.

Synonyms do not provide the same level of abstraction or the ability to create an adapter, bridge, or other pattern. However, they can be pointed away from the main table and to a view or other facade in the future. In this manner a synonym is a lightweight coupling that allows flexibility to implemented in the future. Using a synonym to achieve this doesn't make sense at first, since it does expose the structure of the table. But the synonym can be pointed to a new object in the future, perhaps a view that mimics the table. Using a synonym simply adds a "quick disconnect" feature for future use.

[test_driven_development]

---

copyright Katherine Elizabeth Lightsey 1959-2013 (aka; my life)

"Few things are harder to put up with than the annoyance of a good example." - Mark Twain