Tag: testing

Testing a Birthday Month Query

I ran across a post from a tester, wondering how to write a query for birthday months. This makes sense, as I’m sure some businesses want to notify or track customers that have birthdays this month and give them something.

The post was good, with DDL and test data, and ignoring the discussion about database design, how would you test htis? If you look through the test data, surely you might decide to check for Jan, or Feb, etc., but are you use as the query might change that you’re checking everything?

This is where testing can help. I’d start with a simple test. One that uses some data and checks that nothing is returned.

CREATE OR ALTER PROCEDURE tTSQLTests.[test birthday queries for a Jan with no birthdays in range]
/*
Description:

Changes
11/22/2017
*/
AS
BEGIN
     -------------
     -- Assemble
     -------------
     DECLARE @begin DATETIME = '1994-01-01' ,
             @end DATETIME = '2000-01-01' ,
             @month TINYINT = 1;

    EXEC tSQLt.FakeTable @TableName = 'birthdays', @SchemaName = 'dbo';
     INSERT dbo.birthdays
     (
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     )
     VALUES
     (94, N'Jamie', N'Aguiar', '2017-06-02 00:00:00.000') ,
     (346, N'Keith', N'Brady', '1993-03-29 00:00:00.000') ,
     (361, N'Kelsea', N'Britto', '1994-03-25 00:00:00.000') ,
     (715, N'Tia', N'Delguidice', '1999-02-04 00:00:00.000') ,
     (994, N'Holly', N'Hamilton', '2017-11-12 00:00:00.000') ,
     (1110, N'ISABELLE', N'HYDER', '1993-04-06 00:00:00.000') ,
     (1295, N'RAELYN', N'LITTLE', '1995-02-15 00:00:00.000') ,
     (1403, N'ALLISON', N'RIPA', '1993-10-14 00:00:00.000') ,
     (1486, N'Rayvon', N'Miller', '1984-11-09 00:00:00.000') ,
     (1559, N'Alexandra', N'Sousa', '1989-09-17 00:00:00.000') ,
     (1897, N'Patrick', N'Snow', '1976-10-10 00:00:00.000') ,
     (1749, N'Justine', N'Zienowicz', '1998-03-12 00:00:00.000'),
     (2209,N'Brittany',N'Kosboski','1987-01-22 00:00:00.000')
     ;

    CREATE TABLE #Expected
     (
         cust_id INTEGER NOT NULL PRIMARY KEY ,
         cust_fname NVARCHAR(50) NOT NULL ,
         cust_lname NVARCHAR(50) NOT NULL ,
         cust_dob DATETIME NOT NULL
     );




    SELECT TOP 0
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     INTO #Actual
     FROM #Expected;

    ---------------
     -- Act
     ---------------
     INSERT #Actual
     SELECT cust_id ,
            cust_fname ,
            cust_lname ,
            cust_dob
     FROM birthdays
     WHERE cust_dob >= @begin
           AND cust_dob < @end
           AND MONTH(cust_dob) = @month;

    ---------------
     -- Assert    
     ---------------
     EXEC tSQLt.AssertEqualsTable @Expected = N'#expected' ,
                                  @Actual = N'#actual' ,
                                  @Message = N'The query doesn''t work';

END;


GO
-- GRANT EXECUTE ON tTSQLTests.[test birthday queries for a month] to userrole
EXEC tsqlt.run 'tTSQLTests.[test birthday queries for a Jan with no birthdays in range]'

Then I’d test for one birthday.

CREATE OR ALTER PROCEDURE tTSQLTests.[test birthday queries for a Jan with one birthdays in range]
/*
Description:

Changes
11/22/2017
*/
AS
BEGIN
     -------------
     -- Assemble
     -------------
     DECLARE @begin DATETIME = '1984-01-01' ,
             @end DATETIME = '2000-01-01' ,
             @month TINYINT = 1;

    EXEC tSQLt.FakeTable @TableName = 'birthdays', @SchemaName = 'dbo';
     INSERT dbo.birthdays
     (
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     )
     VALUES
     (94, N'Jamie', N'Aguiar', '2017-06-02 00:00:00.000') ,
     (346, N'Keith', N'Brady', '1993-03-29 00:00:00.000') ,
     (361, N'Kelsea', N'Britto', '1994-03-25 00:00:00.000') ,
     (715, N'Tia', N'Delguidice', '1999-02-04 00:00:00.000') ,
     (994, N'Holly', N'Hamilton', '2017-11-12 00:00:00.000') ,
     (1110, N'ISABELLE', N'HYDER', '1993-04-06 00:00:00.000') ,
     (1295, N'RAELYN', N'LITTLE', '1995-02-15 00:00:00.000') ,
     (1403, N'ALLISON', N'RIPA', '1993-10-14 00:00:00.000') ,
     (1486, N'Rayvon', N'Miller', '1984-11-09 00:00:00.000') ,
     (1559, N'Alexandra', N'Sousa', '1989-09-17 00:00:00.000') ,
     (1897, N'Patrick', N'Snow', '1976-10-10 00:00:00.000') ,
     (1749, N'Justine', N'Zienowicz', '1998-03-12 00:00:00.000') ,
     (2209, N'Brittany', N'Kosboski', '1987-01-22 00:00:00.000');

    CREATE TABLE #Expected
     (
         cust_id INTEGER NOT NULL PRIMARY KEY ,
         cust_fname NVARCHAR(50) NOT NULL ,
         cust_lname NVARCHAR(50) NOT NULL ,
         cust_dob DATETIME NOT NULL
     );

    INSERT #Expected
     (
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     )
     VALUES
     (2209, N'Brittany', N'Kosboski', '1987-01-22 00:00:00.000');

    SELECT TOP 0
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     INTO #Actual
     FROM #Expected;

    ---------------
     -- Act
     ---------------
     INSERT #Actual
     SELECT cust_id ,
            cust_fname ,
            cust_lname ,
            cust_dob
     FROM birthdays
     WHERE cust_dob >= @begin
           AND cust_dob < @end
           AND MONTH(cust_dob) = @month;

    ---------------
     -- Assert    
     ---------------
     EXEC tSQLt.AssertEqualsTable @Expected = N'#expected' ,
                                  @Actual = N'#actual' ,
                                  @Message = N'The query doesn''t work';

END;


GO
-- GRANT EXECUTE ON tTSQLTests.[test birthday queries for a month] to userrole
EXEC tSQLt.Run 'tTSQLTests.[test birthday queries for a Jan with no birthdays in range]';
EXEC tSQLt.Run 'tTSQLTests.[test birthday queries for a Jan with one birthdays in range]';

Next, I’d add a few more tests for other cases, perhaps checking that leap years, etc. run correctly.

Is this hard to setup? Well, I might argue that the time you spend examining result sets and checking random queries is about the same. Once I’ve written this test, which took about 10 minutes with a template, I can easily copy/paste the test, change the name and move data around. Ideally I’d stick this query in some procedure instead and run the test that way, which makes it easy for me to alter the query and re-run a ton of tests quickly.

The time saved isn’t in the initial development, but in the checking when I touch this code again, or tune it. What if I decided to replace the MONTH() check with a computed column, as suggested by some responders? Then my tests should still pass with a new query.

Testing builds better software. Not perfect, but better. And I can get better as developing over time by adding more tests.

December 4, 2017

A Christmas Bug

It’s not really a big deal for me, despite the fact that I travel to Europe in a few days for two weeks. At least, I’m fairly confident this won’t be an issue for me. A few of my fellow #sqlfamily might not be so lucky, as they use a different airline.

Apparently American Airlines has a bug in their scheduling system. It’s not a safety bug, don’t worry about that, but it’s potentially a huge bug for the company in terms of profit and cost. The bug is a part of the system that lets pilots request holiday time off. The system allowed too many pilots to take vacation, and as a result, American Airlines (AA) doesn’t have enough pilots to fly their planes.

In case you wonder about the scope of this, it’s 15,000+ flights that are affected. AA flies about 6,700 flights a day and are the world’s largest airline by some measure. They are offering pilots 150% of their normal rate, which is apparently the max allowed by contract. I’m sure some pilots will take the offer, but I’m also sure there may be some flight cancellations. After all, pilots have likely made their own plans, which they may not want to change. Apparently some have as the news as of this writing is a few hundred flights.

The complexity of airline scheduling software has to be quite high, and while this won’t really endanger lives, it will affect the profitability of AA. This is definitely a development mistake, and one that should have been caught in testing. I don’t know how often this software is updated, nor what their process is. Someone suggested this is outsourced software, but that doesn’t matter. We clearly have a software development failure here.

I certainly think developers are responsible for testing their code and meeting requirements. This includes not assuming the happy path is followed. However, this is also a place where we need (as an industry) to learn to better test the bounds and limits of our software. We need to be sure that we account for not only security, but pushing the limits of use by legitimate users. Software mistakes cost money, and as we continue to use computing more and more, we need to become better at testing our code. Our systems are very complex these days, more complex than a single person can handle. We need better testing everywhere, including the database.

Please, make an effort to test better next year, in both your application and database. Use larger data sets at some point (both numbers and dates), add automated testing as a part of your development routine, and assume that users will do stupid, silly, and malicious things with inputs.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

December 4, 2017
Installing Pester

One of the things that I believe strongly in is that we need better testing of software. Actually, what we really need is better habits and understanding of how to test our software. I don’t think we need to test everything, but we do need to know how to test some parts of our software, and we need to test those well.

Enough on testing. Let’s talk Pester. I saw Rob Sewell (b | t) talk about Pester a few months back, and it’s been on my list to try something, but time and pressure have kept it on the list and not in the code editor. A week ago that changed when I saw Rob write Write Your First Pester Test. That spurred me to devote a few minutes after I read the piece.

However, I ran into a snag. When I went to run Install-Module Pester, I got an error.

I tweeted a bit with Rob and Mike Fal (b | t), both of whom are some of my PoSh go-to people. They noted the Force option for install, which did work, but I not by itself.

As you can see there’s a security check. A good thing, but like many developers, I’m confident the Pester module on the Powershell Gallery at MS is OK. It’s got a different signature than the previous version. I could remove that, but I decided to add the –SkipPublisherCheck and move forward.

This what I wanted to see.

Now I can move forward with Rob’s article and try some Pester tests.

November 27, 2017
Unit Testing T-SQL Code
Abstract

Unit testing has become an integrated, expected part of most software development teams. Many database developers have yet to implement unit testing as a regular habit. This session will look at two-unit testing frameworks and show how to implement tests for common types of non-trivial T-SQL queries. You’ll examine the tSQLt framework as well as the Microsoft Unit Testing framework for SQL Server.

You will learn:
- How to structure and build unit tests for database code with tSQLt
- How to structure and build unit tests for database code with database projects
- Understand the challenges of test data and how to solve them
Level: 200

Demos

These are the testing demos for this talk:
- Loading and reloading test data
- Checking standards
- Checking joins, specifically outer join refactoring
- Checking function calculations
- Checking boundary conditions
- Using the 0-1-Some pattern
- Checking NULLs
PPTX and code on github:

Data Platform Summit – https://github.com/way0utwest/UnitTestingTSQL/tree/master

VS Live 2017 – Anaheim – https://github.com/way0utwest/UnitTestingTSQL/tree/vs2017Anaheim
October 18, 2017