Chamomile Wiki

SQL tools for documentation, error handling, logging, and testing.

Brought to you by: kelightsey

unit_test

Unit Test

In computer programming, unit testing is a method by which individual units of source code, sets of one or more computer program modules together with associated control data, usage procedures, and operating procedures, are tested to determine if they are fit for use. Intuitively, one can view a unit as the smallest testable part of an application. In procedural programming a unit could be an entire module but is more commonly an individual function or procedure. In object-oriented programming a unit is often an entire interface, such as a class, but could be an individual method. Unit tests are created by programmers or occasionally by white box testers during the development process.
Ideally, each test case is independent from the others: substitutes like method stubs, mock objects, fakes and test harnesses can be used to assist testing a module in isolation. Unit tests are typically written and run by software developers to ensure that code meets its design and behaves as intended. Its implementation can vary from being very manual (pencil and paper) to being formalized as part of build automation.

That's what Wikipedia says to describe a Unit Test in its introductory paragraph. I would urge you to read the entire content of the article. What I would like to do here is define what I mean by the term unit test when it is used in the context of my methods and Chamomile. I don't redefine the term casually, but feel that the types of tests are too numerous for the small team to cover and discuss, so I include them all under the label of "unit test". I could have referred to this instead as a "purple and white striped dog with yellow and lavender polka dotted ears" test, perhaps using the acronym "pawsdwyalpde" to describe it, but just rolling up all common testing functionality into the definition of "unit test" seemed to be a more approachable method to my madness.

A unit test is a method which programmatically tests the execution of another program, testing either individual cases for the target method or a batch of cases as a "test suite". Unit tests are designed to test both primary and boundary conditions of the target method as well as both the business functionality of an object and the system functionality (non-business exposed) of an object. Unit tests can be both white box and black box methods and can be run during code promotion or in regression.

An object is considered to have been unit tested when a suite of unit tests are run successfully against it for which, in the suite of tests, there are individual unit tests for both the business and system functionality of the object as well as for all boundary conditions for the object. Objects tested in this manner are considered to be unbreakable.

Why unit tests are so important
Unit tests are incredibly important to the design and implementation of a system because they allow you to build an extensible, loosely coupled, and robust system rapidly! This may seem counter-intuitive at first, but once you understand what a unit test is and how it works, it will become apparent. In brief, a unit test allows you to rapidly deploy an object that only meets the minimum current requirements, and without any regard for future growth. This is a core principle of the agile methodology called you ain't gonna need it (YAGNI). You know you can do this because, when and/or if additional functionality is required in the future you can easily extend or refactor the object with the confidence that the unit test ensures that the existing functionality is not impaired! <-- Again; very important concept!

An example of what this means would be in the implementation of a [<customer>].[contact] table and associated getter and setter methods. Ideally you would normalize the data, possibly creating normalized tables for email, phone, address, name, etc., along with getter and setter methods for all of these. You could implement this as a simple table, abstracting it through a facade of course in the way of a view, and create getter and setter methods just for this simple table. Unit tests for the getter and setter methods and the view would be created at the same time. In the future, if the table needs to be normalized you can do so and modify the methods and view as required to work with the new structure, secure in the knowledge that you have not lost or changed the prior functionality as long as the unit tests still pass.</customer>

Consider the unit tests below (as pseudo code):

    declare fun_ut.get_silly_number_zero_silly_factor
    as
        begin
            if (fun.get_silly_number @in=1, @silly_factor=0) != null
                throw "<cow bells go here> Hey! 
                    You are supposed to return null for a divide by zero error!";
        end;
    go

    declare fun_ut.get_silly_number_in_too_big
    as
        begin
            if (fun.get_silly_number @in=300, @silly_factor=12) != null
                throw "<cow bells go here> Hey! 
                    You can't handle a value bigger than 255!";
        end;
    go

By writing tests such as these, we have begun to define the actual method required (fun.get_silly_number) before it is even written! It is in this respect that the unit test is a contract between the designer/engineer and the developer. As a software engineer, I can fully specify the requirements of an object with a unit test or suite of tests and give these to you as a developer without having to give you any other information and without having constrained your ability to meet those requirements in any method that meets the demands of your shop (such as naming standards for example);

Consider the (pseudo code) method below.

    declare fun.get_silly_number @in [tinyint], @silly_factor [int] 
    as 
        begin
            return @in * rand() / @silly_factor;
        end;
    go

This method obviously fails to meet the unit test fun_ut.get_silly_number_zero_silly_factor as it will throw a divide by zero error rather than return null when @silly_factor = 0. It also fails to meet unit test fun_ut.get_silly_number_in_too_big as it will return an arithmetic overflow rather than return null if the value for @in is greater than 255.

It should be obvious at this point that the method fun.get_silly_number can be refactored to meet the requirements of the unit tests listed as well as others that might be placed on the object.

Unit Tests typically do not take parameters
Unit tests should not be dependent on input parameters! A unit test with input parameters would require yet another unit test (YAUT??? :) to test the unit test! Yes, there are arguments for including parameters; that's why I included a link where you can read the counter argument. I'm sticking with the no parameters side though. It's just too easy to write another no-argument test than to validate one with arguments. Besides, the entire point of a unit test for me is to be able to run them programmatically. If I have to write yet another unit test (YAUT) to run the unit test with all the possible parameters, again, I could have just written the unit tests without parameters and reduced the opportunities for error.

There is also a school of thought that there should be only one test per method. I understand that, but too often I find I need eight or ten tests that are very simple, and it just doesn't make sense to clutter up the schema with lots of things that do almost identical things. I group them together when it makes sense; when they are similar, and I separate them out when there are major differences.

I do typically include an output parameter to capture the results of the test. I have included a brief example below of how you can use xml to capture the results of a unit test. You can see how the results of the tests as well as test counts are passed out to the calling application. In this way I can use a [testing].[run] method to run multiple tests, aggregating the results into a tree with all the results counted programmatically. With that in place I can easily do regression testing by just calling execute [testing].[run] @tests="all", as an example. Since I use [<schema>_ut>] nomenclature for all tests it is a trivial exercise to find all tests (select [name] from [sys].[objects] where object_schema_name([object_id]) like N'%_ut') and then cursor through them in [testing].[run].</schema>

I actually use typed xml when I do this to ensure that each test returns the output that I expect. You can view the Chamomile source code for examples. You can also easily save this output to a log (see [chamomile].[logging].[entry]) for future reference, regression analysis, etc.

There are additional examples at [utility_ut.get_state_abbreviation] and [utility_ut.insert_state]. [utility_ut.insert_state] is interesting because it performs a non-destructive test on a destructive(insert) process by using a transaction then rolling back the result. There is also [test_driven_development_example] here on this wiki.

    declare @output [xml];
    execute [<schema>_ut].[<method>] @output=@output output;
    select @output;
    go

    declare [<schema>_ut].[<method>] @output [xml] output
    as
    begin
        declare @test [xml], @count [int];
        set @output=N'<test_stack 
            test_count="-1" 
            pass_count="-1" 
            timestamp="'+convert([sysname],current_timestamp,121)+N'" />';

        -- test 1 <checks something>
        begin
            set @test=N'<test name="test 1 <checks something>" result="fail" />';
            <setup test>
            execute [<schema>].[<method>];
            if (<test> = <pass>)
                set @test.modify(N'replace value of (/test/result)[1]' 
                with ("pass"));
            set @test_stack.modify(N'insert sql:variable("@test") 
                as last into (/test_stack)[1]');
        end

        -- test 2 <checks something else>
        begin
            set @test=N'<test name="test 2 <checks something else>" result="fail" />';
            <setup test>
            execute [<schema>].[<method>];
            if (<test> = <pass>)
                set @test.modify(N'replace value of (/test/result)[1]' 
                with ("pass"));
            set @test_stack.modify(N'insert sql:variable("@test") 
                as last into (/test_stack)[1]');
        end

        <do as many tests as required>

        -- format test stack
        begin
            set @count = @test_stack.value(N'count (/test_stack/test)', 'int');
            set @test_stack.modify(
                N'replace value of (/test_stack/@test_count)[1] 
                with sql:variable("@count")');

            set @count = @test_stack.value(
                N'count (/test_stack/test[@result="pass")', 'int');
            set @test_stack.modify(
                N'replace value of (/test_stack/@pass_count)[1] 
                with sql:variable("@count")');
        end
    end
    go

"All through my life, I have been tested. My will has been tested, my courage has been tested, my strength has been tested. Now my patience and endurance are being tested." - Muhammad Ali, The Soul of a Butterfly: Reflections on Life's Journey

Unit Tests are never modified
The rule of thumb is that a unit test is never modified once the target object has been promoted into production. You can create a new test to add to the original test(s), but the original test(s) must be considered to be an immutable object. There are two viable methods to maintaining these tests.

My recommendation is that unit tests be treated the same as production code. They should be run during peer review, analyzed during code review, validated and run during quality assurance checks, promoted to test along with the target object and used to validate that the target object is correctly deployed before beginning testing, and finally promoted to production with the object and used to validate deployment once again.

Before any future changes are made to production (any changes at all, of any kind, whatsoever, period) the tests should be run in regression first to ensure that there has been no change and that the object still passes. This re-establishes the baseline. After the changes are made the tests are re-run to ensure that the changes did not impact any objects under test. If any tests fail, the change should be rolled back until an analysis of the cause of failure can be performed.

When used in this way the unit tests are being used as a contract between the business and operations. This is an incredibly important concept. Go to the business sometime and tell them that you can absolutely guarantee them that, once functionality is in place, it will never be broken again. You'll get their attention, although first they'll just think you're joking because they are so used to production code being broken time and time again!

The second method for maintaining the tests is simply to do less of what I outlined above. You should at the very least check them into your source repository so that future development in that area can use them. But you are limiting their utility dramatically by not just putting them into production.

Seriously. What are you afraid of? Is the big, bad, unit test boogie monster gonna jump out at you??? You're going to put them into a dedicated schema anyway ([<schema>_ut]) and you're not going to run them except during changes, which is when you really, really, really, need them. The only plausible reason not to implement them as I've recommended is that you, or someone in charge, simply does not understand them. You can read and build tests to convince yourself. Then you've just got to convince the neanderthals in management. But that's why they pay you the big bucks, right? </schema>

"The problem is that the people with the most ridiculous ideas are always the people who are most certain of them." - Bill Maher

*Handling Procedures with Nested Transaction in SQL Server
SQL Server does not handle transactions nested in procedures very well. Here I've included some examples to show you how to deal with this issue. This is extremely important in unit testing as you want to be able to call a 'destructive' method with a unit test, but rollback any changes made. If the called procedure has a transaction you have to deal with that.

"It is foolish to wish for beauty. Sensible people never either desire it for themselves or care about it in others. If the mind be but well cultivated, and the heart well disposed, no one ever cares for the exterior. - Anne Brontë, Agnes Grey

Chamomile Wiki

SQL tools for documentation, error handling, logging, and testing.

unit_test

Related