Tag: syndicated

My Second Pester Test
I should write about my first one, but I just copied Rob’s test, so that’s not so exciting. Instead, I decided to take his advice and write some code, then decide how I test it. This isn’t really TDD, but I need to understand how Posh returns things, so I’ll figure that out first.

I decided to work with SQL Clone, since that’s an area I’m working in a bit already. SQL Clone has a set of PoSh cmdlets that you can use to create database clones, so I thought that would be a good test.

What do I test?

My process is to remove a clone if it exists and then add one back. For testing, I need to consider what actually is happening here.
- If a cloned database exists, remove it
- Create a new cloned database
The result of this process means that I’ll have a Clone, no matter whether I created a new one or had to remove one and recreate a new one. Or, if I had a Clone already and my script failed. This last one is something a tester needs to be aware of.

In my case, I can determine if there is a new clone by looking at the created date of the database object. If that was later than the beginning of my test, then I could likely assume my clone was new.

To start with, I created a function that would destroy and create a simple clone. Once I had that, I could now write my Pester test.

I started with a “describe” element, in which I loaded my function and set a starting time.

Then in the “it” section, I run my function and then check the instance, getting the CreateDate property of my clone database. If my function has worked, this will be a new clone, created since my test started. I compare that to complete the test.

I ran this with Invoke-Pester, and it worked.

This wasn’t simple. I had to test my test a few times, and use PoSh commands to verify it was doing what I thought. I also changed my function with a hardcoded db clone name to ensure the test fails. Results for that one here:

Of course, I changed things back and tested again. Now, as I update my function to include adding in the instance name and image name, this test should still pass. Of course, I can add in other tests, or change this one, to allow me to test on different images and instances as well.

A simple test, maybe a silly one, but I learned a few things about my PoSh code (and how to write it cleaner) as well as Pester and adding in another unit test framework. Now I can expand this to test other PoSh items I have and practice writing better tests that will give me confidence my code works in a variety of situations.
December 7, 2017
Join Me Online Next Week for SQL in the City

It’s one week to SQL in the City 2017, once again streamed. I’ll be traveling this weekend to the Redgate office and getting setup. After two days of rehearsing, I’ll be ready for the live broadcast Dec 13.

Here’s a quick promo video as well:

Once again, we’ll be covering a number of topics that you will be interested in such as code analysis, performance counters, Database DevOps, and more. Perhaps a topic that most of us need to learn more about is data protection and privacy. We’ll have a bit to discuss here as well. This is a topic that is important for us as GDPR will take effect in 2018 and will impact us, as well as many of you.

You can see the full schedule, which is going to be broadcast twice. A lunch show for Europe and an evening show (for me) for the afternoon in the US. We’ll be live both times, so tune in and bring your

A long day for me, though I hope to get out to the gym between the shows. We’ll see as I’m sure I’ll have a few meetings with product groups as well.

Register today and I’ll see you online next week. Brings lots of questions and we’ll do our best to give you the answers you need to improve your database development.

December 6, 2017
SQL Prompt Gets Even Better

SQL Prompt v9 came out recently, and just when you thought they couldn’t make it better, they found a way. If you click the menu for SQL Prompt, you’ll see a couple new items.

Code Analysis!!! Finally, a first step towards some sort of better analysis of code. These are a set of rules from our SQL Code Guard acquisition and we’ve integrated these into Prompt. Now you’ll get some green squiggly’s to alert you to potential issues.

For example, I get two in this short, poorly written code. The first item is a TOP without an ORDER BY, which usually isn’t a good thing.

The second item is one of those that I would like to put in all CAPs for developers. No asterisks.

Right now there are a set of static rules, but we are working to add more and allow you to customize these items. We’re also looking for feedback on how you might like to apply or surface these rules to your staff. Please, if you have comments or questions, ask at the Redgate Hub.

For now, you can disable analysis, or disable selected rules. Click the Manage link, and you’ll see a list of rules, which you can turn off as needed.

Static analysis of SQL code has a long way to go, but I am looking forward to seeing more improvements appear in SQL Prompt and our other products over time.

December 5, 2017

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

Tag: syndicated

My Second Pester Test

What do I test?

Join Me Online Next Week for SQL in the City

SQL Prompt Gets Even Better

Testing a Birthday Month Query