Search results matching tag 'unit testing'

Database unit testing is now available for SSDT

jamiet — Fri, 14 Dec 2012 12:47:47 GMT

Good news was announced yesterday for those that are using SSDT and want to write unit tests, unit testing functionality is now available. The announcement was made on the SSDT team blog in post Available Today: SSDT—December 2012. Here are a few thoughts about this news.

Firstly, there seems to be a general impression that database unit testing was not previously available for SSDT – that’s not entirely true. Database unit testing was most recently delivered in Visual Studio 2010 and any database unit tests written therein work perfectly well against SQL Server databases created using SSDT (why wouldn’t they – its just a database after all). In other words, if you’re running SSDT inside Visual Studio 2010 then you could carry on freely writing database unit tests; some of the tight integration between the two (e.g. right-click on an object in SQL Server Object Explorer and choose to create a unit test) was not there – but I’ve never found that to be a problem. I am currently working on a project that uses SSDT for database development and have been happily running VS2010 database unit tests for a few months now.

All that being said, delivery of database unit testing for SSDT is now with us and that is good news, not least because we now have the ability to create unit tests in VS2012. We also get tight integration with SSDT itself, the like of which I mentioned above. Having now had a look at the new features I was delighted to find that one of my big complaints about database unit testing has been solved. As I reported here on Connect a refactor operation would cause unit test code to get completely mangled. See here the before and after from such an operation:

SELECT    *
FROM    bi.ProcessMessageLog pml
INNER JOIN bi.[LogMessageType] lmt
    ON    pml.[LogMessageTypeId] = lmt.[LogMessageTypeId]
WHERE    pml.[LogMessage] = 'Ski[LogMessageTypeName]of message: IApplicationCanceled'
AND        lmt.[LogMessageType] = 'Warning';

which is obviously not ideal. Thankfully that seems to have been solved with this latest release.

One disappointment about this new release is that the process for running tests as part of a CI build has not changed from the horrendously complicated process required previously. Check out my blog post Setting up database unit testing as part of a Continuous Integration build process [VS2010 DB Tools - Datadude] for instructions on how to do it. In that blog post I describe it as “fiddly” – I was being kind when I said that!

@Jamiet

Thoughts on Test Driven Database Development

jamiet — Thu, 26 Jan 2012 08:30:00 GMT

Test-Driven Development (TDD) is a software development practise that has been around for a few years. Wikipedia describes it as:

Test-driven development (TDD) is a software development process that relies on the repetition of a very short development cycle: first the developer writes a failing automated test case that defines a desired improvement or new function, then produces code to pass that test and finally refactors the new code to acceptable standards. Kent Beck, who is credited with having developed or 'rediscovered' the technique, stated in 2003 that TDD encourages simple designs and inspires confidence.

http://en.wikipedia.org/wiki/Test-driven_development

Since 2003 TDD practises have seen refinements such as Behavior-Driven Development and Uncle Bob's Three Rules of TDD, all the while TDD has pretty much become an accepted way of developing quality software. Accepted that is everywhere outside of the database development arena and that is the arena in which I spend my working life. TDD simply has not, in my opinion, caught on with database developers like it has our appdev brethren and I was reminded of this yesterday when Atul Thakor asked on Twitter:

anyone done TDD for database development and would they recommend it?

https://twitter.com/#!/atulthakor/status/161886007929733120

To which my answer was an emphatic:

(1) yes & (2) absolutely, yes
https://twitter.com/#!/jamiet/status/161894215217987585

I'll use this blog post to expand on that outside of 140 characters.

In October 2010 I undertook a mini-project for the client I was working for at the time (a bank) where a colleague and I were tasked with building the database portion of a system that would support reconciliation of our ETL processes. It was a nice piece of work in that it was small, well-scoped, time-bound, greenfield, did not have any external dependancies and had a technically savvy product owner. We sat down at the start and decided that this was an ideal opportunity to trial TDD as a method of developing a database; I would write the failing tests and my colleague would make the tests pass. We came up with some guiding principles and, although we didn't know it at the time, they were pretty close to Uncle Bob's three rules.

I used Visual Studio 2010's database unit testing framework¹ to write my tests and have them run as part of our Continuous Integration (CI) build (see Setting up database unit testing as part of a Continuous Integration build process). I would write the tests, check-in, the CI build would fail and my colleague would "get latest" in order to see what code he had to write to stop the build from failing. To cut a long story short the use of TDD was considered to be a great success; we shipped a working system on time/on budget and moreover, even though I didn't write a scrap of code that went into production I have never had more confidence that a system I was involved in building worked as intended. That's quite a statement. My confidence stemmed from the fact that as the test author I was ultimately responsible for ensuring that the system did what it was supposed to; I could qualify my confidence by pointing at our CI build and highlighting the number of tests that were passing and how that number had steadily increased as the project progressed.

By the time the project had finished the database consisted of (if memory serves me correctly) 6 tables and about 10 stored procedures or functions (so yes, very small). To test that we had roughly 70 tests that were getting run up to 20 times a day. The project had taken about two months from start-up to final delivery - you can make your own opinions as to whether you consider that prompt or tardy but our product owner was happy and that's pretty much all that counted as far as I was concerned.

Since that project I have moved onto other clients and at each one I have always extolled the use of database unit testing; we haven't always practised TDD but at each one we have been writing database unit tests and in the future I suspect that a client's willingness (or lack thereof) to use database unit testing will be a major factor in influencing whether we end up working together or not.

Are you a database developer doing database unit testing or perhaps even TDD? Let me know in the comments, I'd love to hear about others' experiences.

@jamiet

¹Yes, that linked-to article from 7 years ago is the best one I could find to describe what Visual Studio's Database Unit testing Framework actually is - sort it out Microsoft!

UPDATE: I have just remembered that Jamie Laflen has written an excellent whitepaper entitled Apply Test-Driven Development to your Database Projects that goes into much more detail about how to achieve database TDD using Visual Studio than I have here. Well worth a read.

Benefit from Unit Testing T-SQL: Ensure quick turnaround

Alexander Kuznetsov — Thu, 04 Nov 2010 01:50:00 GMT

The biggest advantage of unit testing is the ability to make changes quickly, and with confidence that we have not broken anything with our change. Whether we need to speed up a query real quick, or to fix a bug, automated testing saves us a lot of time, especially if test failure is exposed in user friendly way.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Benefit from Unit Testing T-SQL: Speed up Your Test Harness

To ensure quick turnaround, we typically use the following approaches:

Utilize version control. Use branches.

When all the code, including unit tests, is in version control, the failure of our workstation does not delay us too much: we can quickly download source code on any other workstation and continue working. Similarly, when we work on a feature and use a branch, we can quickly switch context to the trunk, make a fix against it, and switch back to the branch.

Run all tests off local instance. By default, build test database from scratch.

If we have to share test databases, too much time can be wasted on coordinating, especially if we need to change database schema or test data. So, all the scripts needed to build the test database and populate it with test data must be downloaded from version control. Optionally, we should be able to bypass creating and populating test database - that saves time on subsequent runs.

Practice, practice, practice.

To act quickly, we need to be familiar with unit testing. Learning something during an emergency is not very efficient. Unit testing should be practiced frequently, so that our skills do not get rusty.

Also we need to be familiar with our tools. If we use open source ones, we need to be familiar with the source code. Anyway, if our tools have gotchas, we need to learn them proactively, not when we are in a hurry and those gotchas get in our way. Needless to say, in most cases simple and rock solid tools are preferable to complex ones with gotchas.

Benefit from Unit Testing T-SQL: Speed up Maintenance of Unit Tests

Alexander Kuznetsov — Wed, 27 Oct 2010 21:00:00 GMT

Once we have a considerable amount of unit tests, their maintenance begins to take noticeable time. If we need to change a module covered by unit tests, it may take less time to make the change itself than to change the corresponding tests accordingly.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Benefit from Unit Testing T-SQL: Speed up Your Test Harness

We may not notice this effect when we are evaluating a new technology against a small set of tests. However, in real life, and with a project of any reasonable size, we will surely notice how unit tests slow us down. Typically we shall start paying attention to tests maintenance as soon as we need to change a module covered by them, most likely long before we have just one thousand of unit tests.

Let me quote from the blog post The Maintainability of Unit Tests
written by my coworker Jay Fields, who, together with Shane Harvey, Martin Fowler, and Kent Beck, wrote Refactoring: Ruby Edition

Jay says the following: "In my experience, making the interface or interaction change often takes 15-20% of the time, while changing the associated tests take the other 80-85%. When the effort is split that drastically, people begin to ask questions. Should I write Unit Tests? The answer at speakerconf was: Probably, but I'm interested in hearing other options."

As we discussed before in "How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests", we have chosen to separate expected results from the tests themsleves, and to generate those expected results rather than hardcode them. One of the reasons for this choice was to speed up tests maintenance. Let us see how having expected results decoupled from the tests themselves makes maintenance so very much faster.

Changing tests to accommodate changes in the module being tested

Suppose that we have a stored procedure which returns the following columns: Subject, SentAt, exactly as we discussed in "How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way".
Suppose that we have modified this procedure: we added one more column, Priority, to its result set. With expected results decoupled from the test itself, the test stays the same, and we only need to change expected results. That is very easy: we can just regenerate the expected results, just using the same tool that generated them, and overwrite the files.

Note: Both version control systems I am using right now, Subversion and Git, do not require me to explicitly check out, or unlock, the files being modified, the files with expected results - I can just overwrite them without any effort. Of course, if you are using an old fashioned version control system that requires you to explicitly unlock or check out files before you can modify them, you are in for much more work.

Let us visually review all the changes and confirm that the only change in the expected results is that additional column, like in the following screenshot from TortoiseSVN diff tool.

Once we have confirmed that our procedure still works as before, but returns an additional column, we can just commit the changes into version control and we are all set.

Naturally, as we visually verify the changes in the expected results, we can make mistakes, like in any other task. However, because the process of changing expected results is automated, and because the changes are exposed in a very user friendly way, we are not likely to make those mistakes. Overall, this is a safer and faster way of maintaining unit tests than just fixing them manually.

The next post in this series is Benefit from Unit Testing T-SQL: Speed up Maintenance of Unit Tests Part Two

Benefit from Unit Testing T-SQL: Speed up Your Test Harness

Alexander Kuznetsov — Fri, 15 Oct 2010 19:04:00 GMT

Ideally unit test harness should run very fast. Because all unit testing involving a database is quite slowish, we need to apply some effort to ensure that our tests finish in acceptable time.

This slowness might not be a concern when we try out a cool new technology against a tiny set of objects and tests, but it surely does become an issue when we unit test a real life system of any reasonable size. In this post we shall learn how to increase the speed of our test harness.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Do not explicitly test modifications

Explicitly testing modifications is definitely doable, but it is quite slow.
Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, and verify that our selects return expected results.

Use one and the same test data for all tests

Adding test data specifically for a group of tests and tearing it down afterwards uses up too much precious time. Instead, we should run all tests off of the same test data. As an additional beefit, that allows us to run tests in parallel against the same test database. Of course, when we change test data to accomodate a unit test, this can break other tests. We shall address that in a later post.

Use C#, not T-SQL, to match actual results against expected.

We can love T-SQL as much as anyone else, but it is simply not the best tool for the job. First of all, it cannot capture more than one result set. Besides, matching actual results against expected in C# is in my experience much faster. One and the same C# module can work against all result sets. On the other hand, should we be matching results in T-SQL, a lot of time would be spent parsing and compiling SQL.

Concentrate on testing only the most important and the most complex modules.

We have discussed that before, in How to Benefit from Unit Testing T-SQL: choosing what to test , but it is definitely worth repeating here.

Next steps.

When we use one and the same test data for all tests, we may need to change it from time to time. These changes may break some tests. Fixing them should be fast and easy. We shall address it in the next post.

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Alexander Kuznetsov — Thu, 14 Oct 2010 17:39:00 GMT

Unit tests provide a very accurate snapshot of what we were thinking about (and what we were missing) when we were writing them. In fact, they are working examples of how we think our code should be used. More to the point, unit tests must be up-to-date, otherwise they would not pass.
Fro example, if the signature of a method changes, or we add more use cases, our tests reflect that. As such, unit tests are good a source of current information about our code.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Reusing unit tests as documentation

Let us have another look at the test from one of previous posts, How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests:

-- no messages from jb12345, should return nothing EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown sent no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateTo = '20101004'; -- must return all messages from jbrown sent no earlier than 20101001 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateFrom = '20101001'; -- must return all messages from jbrown sent no earlier than 20101001 -- and no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jbrown', @DateFrom = '20101001', @DateTo = '20101004';

This script demonstrates how we think the code should be used at the time when we are develoiping or changing it. If we misunderstand some requirements, that will be reflected in our tests. If we have forgot about some use cases during development, surely they will be missing from oiur tests as well. Should we need to change the signature of our funstion of stored procedure, we must change our tests accordingly.

Also providing working examples is a very clear and unambiguous way of documenting code.

Documenting assumptions as unit tests

As we develop code, we make assumptions. As Jeff Atwood puts it in The Pragmatic Programmer Quick Reference Guide, "Test assumptions as well as code". We can explicitly write unit tests which verify that the assumptions used in our development are correct.
I have written a few examples:

Optimizing a query, then documenting assumptions in a unit test.

Yet another example of defensive query optimization

Defensive database programming: SET vs. SELECT.

The next post in this series is: Benefit from Unit Testing T-SQL: Speed up Your Test Harness

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Alexander Kuznetsov — Fri, 08 Oct 2010 18:45:00 GMT

Well-written unit tests should succeed only when the module being tested meets the requirements completely.If the test fails, it can do much better than just indicate failure. If the test has managed to provide a clear and comprehensive report of what is wrong, that can save us a lot of time troubleshooting.

We shall see a few examples soon.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

Verify everything

Surely we can come up with many different scenarios, but in my practice we almost always want to do one and the same thing: capture all the result sets and verify everything. "Everything" means this:Column names and types should be matched precisely in every result set - any change in them may break some code. Order of columns must stay the same.
Similarly, all the values in all result sets should match as well.

Of course, we need to be very specific when we say "all the values in all result sets should match". For example, if we are not required to order the results, they can be in any order, and our matching must be able to match unordered result sets.
Another example: if we aggregate floating point numbers, we can get slightly different results, and our matching must tolerate minor differences.

Having said all these reservations, my main point remains the same: if the requirements are specific, our testing must be just as specific. For example, checking row count and nothing else makes sense only if the only requirement is to return a specific number of rows. Similarly, checking that the result set is not empty and nothing else makes sense only if the only requirement is to return a not empty result set.

Saving the full description of a result set in a file

Suppose that our test returns the following result set:

Subject                           SentAt
--------------------------------- -----------------------
Free Donuts in Kitchen, Hurry up! 2010-10-07 07:12:00.000
East side printer jammed          2010-10-07 09:21:00.000
TPS Reports due in 10 minutes     2010-10-08 11:50:00.000

My testing tool saves column names, types, and all the data returned in this xml file:

<?xml version="1.0" encoding="utf-8"?>
<results>
<object name1="result set" name2="">
    <row type="schema">
      <column name="subject" value="varchar(50)" />
      <column name="sentat" value="datetime" />
    </row>
    <row type="datarow">
      <column name="subject" value="Free donuts in kitchen area, hurry up!" />
      <column name="sentat" value="10/7/2010 07:12" />
    </row>
    <row type="datarow">
      <column name="subject" value="East side printer jammed" />
      <column name="sentat" value="10/7/2010 09:21" />
    </row>
      <column name="subject" value="TPS Reports due in 10 minutes" />
      <column name="sentat" value="10/8/2010 11:50" />
</object>
</results>

Exposing Failure in a User-Friendly Way

If at some later time our test fails, we want to see the whole picture of what exactly failed, and what returned as expected. There are quite a few utilities which present differences between text files in an easy-to-interpret way.Let us use one of them.

If the test fails, let us output the actual results into an XML file, and use TortioseMerge, which gives a great visual presentation of the differences. For example, the following screenshot is very easy to interpret: we have correct data in the wrong order:

Note that if we were checking only the row count, we would not detect the discrepancies in this case. If we were checking only some value in the first row only, it is just as easy to come up with a scenario when we would fail to detect the discrepancies.
We really want to verify everything.

If we used NUnit assertions to verify all the values individually, we would definitely detect a failure, but we would get less useful information, only one first discrepancy. Similarly, if we were checking all the values individually, output failures, but continue to check, we would be overwhelmed with too many details in the output looking like this, with every value not matching.

Row 0, Column 0,
Expected: Free donuts in kitchen area, hurry up!
Actual: TPS reports due in 10 minutes

Row 1, Column 0,
Expected: East side printer jammed
Actual: Free donuts in kitchen area, hurry up!

Row 2, Column 0,
Expected: TPS reports due in 10 minutes
Actual: East side printer jammed

Based on such too detailed information, it is very difficult to see the forest behind the trees, to detect the pattern in the failures, to quickly understand what went wrong.

As we have seen, if our unit test fails, it is very important to present all the information about the discrepancies in an easy to understand way.

The next post in this series is: Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

Alexander Kuznetsov — Wed, 06 Oct 2010 13:36:00 GMT

In this post we shall see how to incorporate a typical manual test in an automated test harness with minimal effort.

Because our goal is to benefit from unit testing, and not to develop as many features as we can think of, we shall concentrate on the most common use cases. In fact, just one use case is so common that is happens more often all all others combined together, at least in my practice. Naturally this whole "the most common" thing is very subjective, and your perspective may be very different from mine. Anyway, the following example demonstrates what I think is the most common scenario in my practice. Supporting this very common scenario allows me to gain about 80% of possible benefits by implementing about 5% or less of possible features (naturally, my estimates are very subjective).

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

Developing a stored procedure

So here is my example of the most common scenario in my day-to-day development activity.
Suppose that we need to implement this requirement: "For a given sender name, and a range of DATETIME values, select all the messages sent by the sender between these two times, ordered by the time they were sent. "Between" must mean the way SQL interprets it, with both ends included. One or both ends of the interval can be omitted, in which cases we must return the following:

If DateFrom is omitted, return all the messages from the sender sent no later than DateTo,
If DateTo is omitted, return all the messages from the sender sent no earlier than DateFrom,

If both DateFrom and DateTo are omitted, return all the messages from this sender"

We have already discussed that we prefer to test complex modules, so let us assume that this is a challenging task, and that developing this module fascinates us. In other words, this module is complex enough and eventually we need unit tests for it.

Here is the procedure that implements these requirements:

CREATE PROCEDURE dbo.SelectMessagesBySenderNameAndTimeRange @SenderName VARCHAR(30), @DateFrom DATETIME = NULL, @DateTo DATETIME = NULL AS BEGIN SELECT Subject, Body, SentAt FROM dbo.Messages WHERE SenderName = @SenderName AND SentAt>=COALESCE(@DateFrom, SentAt) AND SentAt<=COALESCE(@DateTo, SentAt) ; END

Note: Maybe this implementation is not performant enough, and at some later time we shall need to speed it up. Right now we are making sure that this module work correctly. Maybe, however, this procedure will perform well enough for the time we need it, so let us not over-engineer it just yet, let us not optimize it before we know we need optimization. For now, let us concentrate on the correctness of this procedure.

Developing manual tests

Even if there is considerable pressure to get things done fast, we absolutely want to test the module as it is being developed, manually or automatically. For thorough testing, we need enough test data to cover all the cases. It does not really matter too much whether we generate test data with the help of some tool, or just type some insert commands manually - there are many possible ways to accomplish this task. Yet it does matter very much that the test data represents all the cases.

Suppose that we have already come up with the procedure and all the needed test data, and that we want to test the procedure. I find it easier and faster to first test my procedure manually, in SSMS, as shown in the following script:

-- no messages from jb12345, should return nothing EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown sent no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateTo = '20101004'; -- must return all messages from jbrown sent no earlier than 20101001 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateFrom = '20101001'; -- must return all messages from jbrown sent no earlier than 20101001 -- and no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jbrown', @DateFrom = '20101001', @DateTo = '20101004';

Suppose that these test cases cover all the possible situations we know of (maybe I am missing something, but I don't know it at this time), and that when we run this manual test script in SSMS against our test data, we are sure all the results are correct. Now that we have a working procedure, we need to come up with unit tests equivalent to the script above. Of course, we also need to document the module, and to add all the results of our work to version control.

Needless to say, we want this process of coming up with unit tests to be very efficient, because we are going to repeat it many times a day.

From manual tests to automated without writing code

So I have come up with the following simple process: to indicate that my manual test should be incorporated into my test harness, I just save it into a folder named Tests. I have a little utility which executes the manual test and saves all the output from it in a separate XML file, in the same tests folder, with the same name as the sql test script.

When I have my expected results persisted in a file, I can run another utility which executes my test script and matches its results against the XML file with my expected results. Note, however, that I don't have to write a single line of code neither for expected results, nor to match the actual results against expected.

In the past I was writing trivial glue C# code to invoke my T-SQL modules and to match the actual results against expected ones, as we described in Close those Loopholes - Testing Stored Procedures

This worked very well for me, but surely not having to write any code at all, having a tool do everything for us, is more efficient than having to write trivial glue code. So here we are, eliminating fluff, getting rid of unnecessary complications.

So let me summarize the process described above: I will spend as much time as needed,
making absolutely sure that I have enough test data and enough test cases. I will not cut any corners and will not compromise the quality of my code until I know that my code works.

However, when I know that my code works, I will save my manual test in Tests folder, to indicate that this is a test. I will generate my expected results, and save them as a file in the same Tests folder. and I do not need to write a single line of code to invoke my tests. A utility will run this test for me.

Generating expected results is faster and less prone to errors, than doing it manually

The process of reading a test script from a file, executing it against some test database, and matching the results against the expected ones is highly repetitive. As such, it can be fully automated. This – the fact that expected results are generated and not manually developed - may be considered by some professionals as a very serious compromise.

Some, probably many, will not approve the fact that I generate my expected results, some will say that it violates some kind of basic principle of unit testing or TDD. Maybe so. Yet this approach saves me a huge amount of time, and allows me to concentrate on what really matters, what really makes the difference: building a comprehensive set of test cases.

In this case, should we choose to stick to pure theoretical principles, we have nothing to gain and a lot of time and effort to lose. More to the point, coding the expected results manually is a very mundane and time-consuming process. As such, it is very prone to errors. In addition to the time spent manually defining the expected results, a lot of time is usually spent fixing bugs in our test harness. When we generate expected results, we eliminate the source of such bugs and save a lot of time.

Next steps

Note that our test script can and should be also used as documentation, which saves even more time. We shall discuss it a later post.

Another important point here is this: when the test script is stored separately from its expected results, this can simplify maintenance. We shall talk about it more in some later post.

One last note: if at some later time our unit test fails, we need to expose the discrepancies between actual and expected results in a very clear way. When the expected results are stored in a separate file, this is very easy to accomplish. This is described in my next post, How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Enforcing naming conventions using database unit testing

jamiet — Tue, 05 Oct 2010 22:42:17 GMT

My naming convention obsession

Anyone that has ever worked with me will tell you that I am a stickler for naming conventions. I have a somewhat obsessive reputation for it; I can’t help it – I seem to have a deep seated uncontrollable desire to ensure that every object in my database(s) is/are named consistently (is there anyone else out there equally as obsessive?).

I have tried various techniques down the years to try and enforce naming conventions but none of them really worked. I’ve got scripts that alter object names (such a script is in my script library in fact) but these are only any use if they actually get run, they don’t actually enforce the conventions – that’s a manual step. I’ve thought about using Policy-Based Management (PBM) to enforce naming conventions but given I’m a developer and not a DBA that’s not something that is readily available to me and besides, using PBM to enforce naming conventions is reactive rather than proactive if you are developing the code on a machine where the policies are not enforced.

Another option I looked into using was Team Foundation Server (TFS) check-in policies; these are policies that can be applied to artefacts when they get checked-in to TFS’s source control system. This option really appealed to me because the naming conventions could be enforced during check-in (i.e. very very early) and didn’t require DBA intervention. In practice though enforcing naming conventions using TFS check-in policies has a few sizable issues:

Its not easy. It would require you to parse the file that was getting checked-in, decide what sort of object is defined in the file, and then check the name of the object based on things like object name, schema, etc...
TFS check-in policies are not installed on the TFS server, they are installed on the development workstations. This means there is a dependency and, even though the source code for the check-in policies can be distributed with your application source code, I didn’t really like this.
You’re relying on each developer to enforce the check-in policy and with the greatest will in the world….that aint gonna happen. Its too easy to turn them off.
There is the obvious dependency on using TFS, not something every development shop uses even in the Microsoft space.

Database unit testing to the rescue

No, a better solution was needed and I came up with one in the shape of automated database unit testing. I have spoken recently about how I have become a big fan of database unit testing (see my post Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution) and being able to enforce naming conventions is one very good reason for that. Enforcing naming conventions using automated unit tests has a number of advantages:

They can be written against the metadata of the objects themselves (i.e. by querying SQL Server’s system views) so there’s no parsing that needs to be done.
They can be employed as part of a Continuous Integration (CI) process and run as a build verification test (BVT). Someone checks-in an object that violates the naming convention? Bang: broken build!
Developers can’t circumvent the tests.
Nothing needs to be installed on the development workstations. The tests live wholly as part of your source code.
Not dependent on use of a particular source control system

Hence I have written some unit tests that enforce the following naming conventions:

Check constraints must be of the form CK_<schemaName><tableName>_XXX
Column names must begin with a capital letter
Column names cannot contain underscores
Default constraints must be named DF_<schemaName><tableName>_<ColumnName>
Foreign keys must be of the form FK_<parentObjectSchema><parentObject>_REF_<referencedObjectSchema><referencedObject>XXX
Non-unique clustered keys must be of the form IXC_<schemaName<TableName>_<Column><Column><Column>…
Non-unique non-clustered keys must be of the form IX_<schemaName><TableName>_<Column><Column><Column>...
Unique clustered keys must be of the form IXUN_<schemaName><TableName>_<Column><Column><Column>…
Unique non-clustered keys must be of the form IXUN_<schemaName><TableName>_<ColumnColumnColumn>...
Primary keys must be of the form PK_<schemaName><tableName>
Stored procedure names should not contain underscores
Stored procedure names must begin with a capital letter
Table names must not contain underscores
Table names must begin with a capital letter

I’m not stating that you should agree with these naming conventions (I don’t necessarily agree with them myself – they were defined before I arrived on my current project), the point here is that all of these rules can be enforced and its very easy to do it. Here’s the code for the unit test that enforces the primary key naming convention:

   /*PK name is PK_<schemaName><tableName>*/       
   SET NOCOUNT ON       
   DECLARE @cnt    INT;       
        
   SELECT  *       
   INTO    #t       
   FROM    (       
           SELECT  OBJECT_NAME(c.[parent_object_id]) AS [TableName],OBJECT_SCHEMA_NAME(c.[parent_object_id]) AS [SchemaName],c.*       
           FROM    [sys].[key_constraints] c       
           INNER JOIN [sys].[tables] t        
               ON  c.[parent_object_id] = t.[object_id]       
           LEFT OUTER JOIN sys.extended_properties ep       
               ON  t.[object_id] = ep.major_id       
               AND ep.[name] = 'microsoft_database_tools_support'       
           WHERE ep.[major_id] IS NULL       
           AND c.[type] = 'PK'       
           )q       
   WHERE   [name] <> N'PK_' + [SchemaName] + [TableName]       
        
   SET     @cnt = @@ROWCOUNT;       
   IF  (@cnt > 0)       
   BEGIN       
           DECLARE @msg NVARCHAR(2048);       
           SELECT  @msg = '%d Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):' + STUFF((SELECT ', ' + [name] FROM #t a FOR XML PATH('') ),1,2,'')       
           FROM    (       
                   SELECT  1 AS [Id],*       
                   FROM    #t t       
                   )q       
           GROUP   BY q.[Id]       
           SELECT @msg       
           RAISERROR(@msg,11,1,@cnt);       
   END

Essentially all it does is pull all of the primary keys out of [sys].[key_constraints], checks to see what the name should be, then if it finds any that violate the naming convention raise an error containing the names of all the primary keys in question. Here’s the error obtained when running the test against [AdventureWorks] (I’ve highlighted the pertinent bit):

Test method Prs.SchemaTests.NamingConventions.PrimaryKeys threw exception:
System.Data.SqlClient.SqlException: 70 Primary Keys do not conform to naming convention (PK_<schemaName><tableName>):PK_ErrorLog_ErrorLogID, PK_Address_AddressID, PK_AddressType_AddressTypeID, PK_AWBuildVersion_SystemInformationID, PK_BillOfMaterials_BillOfMaterialsID, PK_Contact_ContactID, PK_ContactCreditCard_ContactID_CreditCardID, PK_ContactType_ContactTypeID, PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode, PK_CountryRegion_CountryRegionCode, PK_CreditCard_CreditCardID, PK_Culture_CultureID, PK_Currency_CurrencyCode, PK_CurrencyRate_CurrencyRateID, PK_Customer_CustomerID, PK_CustomerAddress_CustomerID_AddressID, PK_DatabaseLog_DatabaseLogID, PK_Department_DepartmentID, PK_Document_DocumentID, PK_Employee_EmployeeID, PK_EmployeeAddress_EmployeeID_AddressID, PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID, PK_EmployeePayHistory_EmployeeID_RateChangeDate, PK_Illustration_IllustrationID, PK_Individual_CustomerID, PK_JobCandidate_JobCandidateID, PK_Location_LocationID, PK_Product_ProductID, PK_ProductCategory_ProductCategoryID, PK_ProductCostHistory_ProductID_StartDate, PK_ProductDescription_ProductDescriptionID, PK_ProductDocument_ProductID_DocumentID, PK_ProductInventory_ProductID_LocationID, PK_ProductListPriceHistory_ProductID_StartDate, PK_ProductModel_ProductModelID, PK_ProductModelIllustration_ProductModelID_IllustrationID, PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID, PK_ProductPhoto_ProductPhotoID, PK_ProductProductPhoto_ProductID_ProductPhotoID, PK_ProductReview_ProductReviewID, PK_ProductSubcategory_ProductSubcategoryID, PK_ProductVendor_ProductID_VendorID, PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID, PK_PurchaseOrderHeader_PurchaseOrderID, PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID, PK_SalesOrderHeader_SalesOrderID, PK_SalesOrderHeaderSalesReason_SalesOrderID_SalesReasonID, PK_SalesPerson_SalesPersonID, PK_SalesPersonQuotaHistory_SalesPersonID_QuotaDate, PK_SalesReason_SalesReasonID, PK_SalesTaxRate_SalesTaxRateID, PK_SalesTerritory_Territor...

I am currently including these tests inside a C# test project inside Visual Studio 2010. Visual Studio has a rather nice feature that allows you to link to artefacts in other projects and hence we can host our single test class containing all of these tests in one place and link to it from whichever test project we want (typically you will have a test project per database) thus following the DRY principle. Here I show the dialog that demonstrates adding a link to an existing test class:

And how it appears in the project. Note that NamingConventions.cs exists in both test projects but one is just a link to the other:

Wrap-up

I’m not sure my colleagues are too happy about these new tests given that they’re now breaking the build more often but nonetheless I think they realise the value (I guess I’ll find out tomorrow when they read this!!!) All-in-all its working very well for us and I’m now a very happy bunny knowing that naming conventions are being enforced and will continue to be so with zero effort from here on in. I have made the test class that contains all of the tests that I detailed above available on my SkyDrive at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20101005/MIControlTest.zip. If you want to use it you should simply be able to drop it into an existing C# database test project and away you go (change the tests to suit your naming conventions of course though).

Hope this helps. If it does please let me know, I’d really love some feedback on this.

@Jamiet

How to Benefit from Unit Testing T-SQL: choosing what not to test

Alexander Kuznetsov — Thu, 30 Sep 2010 20:55:00 GMT

Ideal unit tests are easy to roll out, easy to maintain, run fast, and make a difference: without them making changes and troubleshooting are more difficult.

Some common examples of unit tests do not grade well against this criteria. For instance, writing unit tests to verify that a database or a table exists is surely easy, and is a nice learning excersize. However, such tests do not make troubleshooting any easier: if our code refers a non-existing table or database, the database engine will throw a clear to understand error message. If this error message does not get stored in the error log, we should improve error handling. To verify that the DDL in our version control is in sync with the real thing, we can use tools such as SQL Compare.

Explicitly testing database modifications is a very interesting challenge. We enjoyed solving it, and wrote an article describing the approach: Close These Loopholes - Testing Database Modifications. However, explicitly testing database modifications is rarely feasible: typically testing database modifications runs too slow, and the tests are quite brittle - we usually need to maintain them if the database schema changes. Suppose, for example, that we have added one more column to a table. If our select stored procedure does not use this new column, all existing unit tests invoking it can run as is. Unit tests verifying modifications of this table, however, usually need to change - they need to either ignore this new column, or to include this new column in their expected results.

Usually we just invoke stored procedures with database modifications to build test data, as follows:

EXEC Writers.InsertCustomer @ID=1, @LastName='Kirk', @FirstName='Burns'; EXEC Writers.InsertCustomer @ID=2, @LastName='Jason', @FirstName='Wu'; EXEC Writers.InsertCustomer @ID=3, @LastName='Jane', @FirstName='Larsen'; EXEC Writers.InsertCustomer @ID=4, @LastName='Dawn', @FirstName='Arrow'; EXEC Writers.UpdateCustomer @ID=1, @LastName='Kurt', @FirstName='Burns'; EXEC Writers.DeleteCustomer @ID=4;

The excpected results of such modifications are tested implicitly, when we run selects against this test data nd verify that we get the expected results.

If the data being selected includes identities, rowversion columns, or other columns that are not going to be the same the next time we run test, we can either exclude them from our checks, or make sure that they come out exactly the same every time we run test. Usually it is way simpler to make sure that the data is the same. Note that we cannot do that for rowversion columns - we have no cotrol over their values. Also if we insert multiple rows in one statement, we cannot guarantee which rows get which identities, unless we use IDENTITY_INSERT. The following script demonstrates it:

BEGIN TRY -- we need this because DBCC CHECKIDENT has some gotchas INSERT INTO dbo.Customer DEFAULT VALUES END TRY BEGIN CATCH PRINT '1' END CATCH GO DBCC CHECKIDENT('Customer', RESEED, 1); -- add your modifications here UPDATE dbo.Customer SET ModifiedBy = 'TestUser', LastModifiedDate = '20100930' ;
Note that because DBCC CHECKIDENT has some gotchas that look and feel like documented bugs, we really need this try block in the script, otherwise we shall not be getting consistent results.

Alternatively, we can exclude the identity, ModifiedBy, and LastModifiedDate from testing, but that somewhat complicates the process of rolling out tests.

This post continues the series on unit testing, the previous post is How to Benefit from Unit Testing T-SQL: choosing what to test

The next post is How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests