Tag: syndicated

This is day 5 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1.

As I worked through the puzzles, I decided that I should be testing using their test sets and solving the issues that way. This lets me use the sample data, but also add in my own sets to cover strange situations.

Here are the tests that I used for each part.

Part I

For part 1, only a short test is needed. Since we’re looking for a scalar value, I could easily just added an INT variable for the actual and expected values. I set this to the value given in the problem.

Then I fake the table and insert the test set. From here, I can call my proc that implements the algorithm and get the result value.

EXEC tsqlt.NewTestClass @ClassName = N'tDay5';
 GO
 CREATE OR ALTER PROCEDURE tDay5.[test day5 a initial set]
 AS
 BEGIN

-- Assemble
 DECLARE @actual INT = 0, @expected INT = 5;

EXEC tsqlt.FakeTable @TableName = N'Day5' , @Identity = 1
 INSERT Day5
 VALUES
 (0 ), (3), (0), (1), (-3);

-- Act
 EXEC @actual = SolveDay5a;

-- Assert
 EXEC tsqlt.AssertEquals @Expected = @expected, @Actual = @actual, @Message = N'Failed to account'

END

Part II

The test is the same, just calling a different procedure that implements the part II algorithm.

CREATE OR ALTER PROCEDURE tDay5.[test day5 initial set]
 AS
 BEGIN

-- Assemble
 DECLARE @actual INT = 0, @expected INT = 10;

EXEC tsqlt.FakeTable @TableName = N'Day5' , @Identity = 1
 INSERT Day5
 VALUES
 (0 ), (3), (0), (1), (-3);

-- Act
 EXEC @actual = SolveDay5b;

-- Assert
 EXEC tsqlt.AssertEquals @Expected = @expected, @Actual = @actual, @Message = N'Failed to account'

END

This is day 4 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. As I worked through the puzzles, I decided that I should be testing using their test sets and solving the issues that way. This lets me use the sample data, but also add in my own sets to cover strange situations.

Here are the tests that I used for each part of day 4.

Part I

This is a fairly simple test. I’m returning a result set since the solution is a single query, but this is really a scalar. In this cas,e I’ll create a one row, one column expected table and then get the results from my solution (inside a proc) and insert into Actual.

The rest is standard tSQLt testing framework. Fake a table, enter data.

CREATE OR ALTER PROCEDURE tDay4.[test Day4a sample data]
AS
BEGIN
     -- Assemble
     EXEC tsqlt.FakeTable @TableName = N'Day4';
     
     INSERT dbo.Day4
(
     passphrase
)
-- SQL Prompt formatting off
VALUES
    ('aa bb cc dd ee' )
  , ('aa bb cc dd aa')
  , ('aa bb cc dd aaa')

-- SQL Prompt formatting on
    CREATE TABLE #Expected (valid INT);
    INSERT #Expected
    ( valid)
    VALUES
    (1  );
    SELECT *
     INTO #actual
     FROM #Expected AS e
     WHERE 1 = 0;


     -- Act
   INSERT #actual
    EXEC dbo.Day4_a;

    -- Assert
     EXEC tsqlt.AssertEqualsTable @Expected = N'#Expected' ,
                                  @Actual = N'#Actual' ,
                                  @Message = N'Incorrect number of valid passphrases';
     
     
END
GO

EXEC tsqlt.run 'tDay4.[test Day4a sample data]';

Part II

This is the same as part I, but I change the inputs and results.

CREATE OR ALTER PROCEDURE tDay4.[test Day4b sample data]
 AS
 BEGIN
 -- Assemble
 EXEC tsqlt.FakeTable @TableName = N'Day4';

INSERT dbo.Day4
 (
 passphrase
 )
 -- SQL Prompt formatting off
 VALUES
 ('abcde fghij' )
 , ('abcde xyz ecdab')
 , ('a ab abc abd abf abj')
 , ('iiii oiii ooii oooi oooo')
 , ('oiii ioii iioi iiio')

-- SQL Prompt formatting on
 CREATE TABLE #Expected (valid INT);
 INSERT #Expected
 ( valid)
 VALUES
 (3  );
 SELECT *
 INTO #actual
 FROM #Expected AS e
 WHERE 1 = 0;

-- Act
 INSERT #actual
 EXEC dbo.Day4_b;

-- Assert
 EXEC tsqlt.AssertEqualsTable @Expected = N'#Expected' ,
 @Actual = N'#Actual' ,
 @Message = N'Incorrect number of valid passphrases';

END
 GO

EXEC tsqlt.run 'tDay4.[test Day4b sample data]';