In this Part 1 of this article I discussed the principles of test-driven database development (TD3) along with Try/Catch and SqlCmd functionality. The article continues by demonstrating more development of the WeatherData database.
The WeatherData database is real – I built it to hold data collected by my weather station.
I can hear you thinking "Just what is a solution, Andy?" Excellent question!
The Solution / Project / Artifact paradigm has been around for a while. Visual Studio has used this model for years. As in the diagram below, Database Solutions contain one or more Database Projects. In turn, Database Projects contain database project artifacts. The project artifacts for a database project are Connections and Queries. Database projects can also hold other artifacts – these will go in the Misc folder.
But first… this is test-driven development. So let's create a test.
Failure: The Raw schema does not exist.
Ok, Now Let's Create a Table!
This table is going to hold raw data imported from CSV files. My Oregon-Scientific WMR100NA weather station updates CSV files every hour on the hour. I'll eventually use SSIS to pump the data from the CSV files into the WeatherData database. When I do, I'll land it in a staging table.
This stored procedure will return the latest average temperature (AvgT) and measurement datetime (MeasDateTime) stored in the raw.StageTemperature table. The temperature is stored in Celsius (Centirgrade) degrees, so I'll also return a converted value for Fahrenheit degrees.
Add to the existing script.
Success: The Raw schema exists.
Failure: Raw.StageTemperature does not exist.
Failure: GetLatestAvgTemp does not exist.
This output is beginning to look like a useful artifact too – a deployment log.
What's really cool about all this is the fact that it's re-executable. You can run the same script against a new instance of SQL Server where WeatherData has never been deployed, or you can run it where only version 1.0 has been deployed. I like that a lot.
Finally! Something More Than Existence Tests!
Up until now, we've been writing existence tests. We've tested for the existence of the database, schema, table, and a stored procedure. Let's do some unit testing!
Depending on whom you talk to and when, you'll get different definitions of "unit test." I first heard the phrase when studying electronics engineering. In electronics engineering, a unit test is sometimes referred to as a "smoke test" (my former lab partners can explain why…) and consisted of powering up a circuit and observing the results. To power it up, we usually constructed a "harness" – a collection of wires tied together with wire ties and maybe terminated with some NEMA, ANSI, or IEC connectors to transfer power from the power supply (or supplies) to the circuit (does any of this language sound familiar?). We weren't looking for everything to work perfectly – at best it was a way to generate first-pass results. But seriously, we were really checking to see if the circuit survived a power-up. If it did, these results were used to validate both calculations and assumptions.
This idea of unit testing, applied to software in general, tests functions or methods or even code snippets to see if they too survive "power-up." In the strictest sense, a unit test determines if the object under test performs or smokes during normal use.
That said, many people mean something very different when they say "unit test." Most folks are looking for valid results from a unit test. Testing for valid results requires different types of testing, ranging from functional testing to exception testing to performance testing. Lots of people lump all these test types together under an umbrella called unit testing. Is it all semantics? It depends on who you're talking to.
Why tear down? It's not mandatory. I've seen lots of production database with test data in them. Some manage it with negative integers in the artificial primary keys, I've seen a couple with a bit field that exists solely for the purpose of marking the row as test data. It's a preference thing for me. I don't like test data in my production database.
So now the testing script. This will be a functional test. I'm going to look for the normal human body temperature as my test. To conduct the test I'll run the setup, execute a test script (yet to be written), and then the tear down.
I'd really like to expand this test. Right now it merely checks to see that everything goes as expected. That's good, but most database (and application) developers check for the expected result. This is an important part of testing but it's not all of it.
This article has been long but we covered a lot of good ground. We talked about scaling deployment. We talked about lots of types of testing and demonstrated three types: unit, functional, and null tests. We used these tests to describe test setup and tear down scripts and test harnesses.
In this article I will try to address some of the design suggestions and experiences that I had in managing similar database.
Part 15 of this series examines what cost is paid when tables are separated into multiple databases on the same server.
This is one in a series of articles dealing with Database Design following the design of a single system from start to finish.