TSQL Fizz Buzz

Sep27.17 12AM

Here’s another journey through the world of simple programming problems… FizzBuzz, T-SQL edition. So just like the rest of the Fizz Buzz solutions. The idea is simple, for every number 1-100, if it’s divisible by 3 print ‘fizz’, if it’s divisible by 5 print ‘buzz’, if both print ‘fizzbuzz’, otherwise print the number. So here is a basic SQL solution:
raw
WITH ctCounter AS
(
	SELECT 1 AS Number

	UNION ALL

	SELECT Number + 1 AS Number
	FROM ctCounter
	WHERE Number < 100
)
SELECT ISNULL(NULLIF(CONCAT(fizz.f, buzz.b), ''), c.Number) FROM ctCounter c
LEFT JOIN (VALUES('fizz')) fizz(f)
  ON c.Number % 3 = 0
LEFT JOIN (VALUES('buzz')) buzz(b)
  ON c.Number % 5 = 0
                            
                        
This is a common table expression, which generates a row for each number between 1 and 100. Then joins with the values ‘fizz’ and ‘buzz’. After that, it concatenates the fizz and buzz together, and if it still results in an empty string, selects the original number. The NULLIF function is useful for that, because it is basically the inverse of ISNULL. If the first parameter matches the second parameter, it returns null, otherwise returns the first parameter.



C# Async Lock

Sep25.17 12AM

Something which you shouldn’t be doing very often (or ever) is placing locks around chunks of asynchronous code. You’re almost always slowing down your code, and there has to be a better way to do it. Anyway, if you do try it, C# will give you a compile error along the lines of: ‘cannot await in the body of a lock statement’. This all makes sense, because locks are tied to a thread. If you are using the parallel task library, there is really no guarantee that your code is going to pick back up on the same thread where it originally left off. You can run into problems like this if you try to use a class like “ReadWriteLockSlim”, which doesn’t give compile warning about awaiting within the critical section, but will happily deadlock your code. So here is a solution to all your fears. Luckily SemaphoreSlim has async support.
This is a helper class which just makes a generic disposable for some coding sugar:
raw
    /// <summary>
    /// A class which can be disposed.
    /// </summary>
    public class Disposable : IDisposable
    {
        /// <summary>
        /// The action to perform on dispose.
        /// </summary>
        private readonly Action onDispose;

        /// <summary>
        /// Initializes a new dispoable.
        /// </summary>
        /// <param name="onDispose">The action to perform.</param>
        private Disposable(Action onDispose)
        {
            this.onDispose = onDispose;
        }

        /// <summary>
        /// Creates a disposable class.
        /// </summary>
        /// <param name="onDispose">The dispose action.</param>
        /// <returns>A disposable.</returns>
        public static IDisposable Create(Action onDispose)
        {
            return new Disposable(onDispose);
        }

        /// <summary>
        /// Dispose.
        /// </summary>
        public void Dispose()
        {
            onDispose();
        }
    }
                            
                        
This is a dummy object which needs a lock:
raw
public class LockableObject
    {
        private readonly SemaphoreSlim asyncLock = new SemaphoreSlim(1);

        /// <summary>
        /// Gets a lock on this object.
        /// </summary>
        /// <param name="token">The token.</param>
        /// <returns>A disposable which release the lock.</returns>
        public Task<IDisposable> GetLock(CancellationToken token)
        {
            return this.asyncLock.WaitAsync(token).ContinueWith(result =>
            {
                if (result.IsCompleted && !result.IsFaulted && !result.IsFaulted)
                {
                    return Disposable.Create(() => asyncLock.Release());
                }

                throw result.Exception?.InnerExceptions.FirstOrDefault() ?? result.Exception ??
                      new Exception("Failed to aquire the lock for an unknown reason.");
            });
        }
    }
                            
                        
Here is a little demo test program:
raw
class Program
    {
        static void Main(string[] args)
        {
            // This wont work...
            ////var obj = new object();
            ////
            ////lock (obj)
            ////{
            ////    await TestLock();
            ////}

            TestLock().GetAwaiter().GetResult();
        }

        /// <summary>
        /// Test to see if this lock works
        /// </summary>
        /// <returns>An async task.</returns>
        public static async Task TestLock()
        {
            const int number = 10000;
            var someObject = new LockableObject();

            var parallelSum = 0L;

            using (var barrier = new ManualResetEventSlim(false))
            {

                var tasks = Enumerable.Range(0, number).Select(i => Task.Run(async () =>
                {
                    // Queue up everything behind this barrier so things try to do this all at once.
                    barrier.Wait();

                    using (await someObject.GetLock(CancellationToken.None)) // Comment out this line to make it fail
                    {
                        // Critical Section
                        var t = parallelSum;
                        parallelSum = t + 1;
                    }
                }));

                barrier.Set();

                await Task.WhenAll(tasks);

            }

            if (parallelSum != number)
            {
                throw new Exception($"The counter is wrong. Expected {number}, but got {parallelSum}");
            }
        }
    }
                            
                        
A major caveat here is that this lock is not at all recursive/re-entrant. If you try to call it within the same call stack, you will most certainly deadlock. Other than that, happy locking.



Looking around online, I could not find a great example of how to draw Pascal’s Triangle in SQL. The solution is quite cool in SQL, because management studio prints out a nice grid for you. Here are three examples. The first one is simple and well documented, the second one is a little more squished, and the third one is a complete dumpster fire that I got too by looking up how to code golf in SQL.
raw
CREATE PROCEDURE PascalsTriangle
    @Levels INT
AS
BEGIN
    SET NOCOUNT ON

    -- Declare variables
    DECLARE @counter        INT = 2
    DECLARE @innerCount     INT
    DECLARE @tmp            DECIMAL(38,0)
    DECLARE @tmp2           DECIMAL(38,0)
    DECLARE @dquery         NVARCHAR(2048)

    -- Create the results table with its one column
    CREATE TABLE #result
    (
        [1] DECIMAL(38,0)
    )
    INSERT INTO #result VALUES (1)

    -- For every level beyond 1
    WHILE(@counter <= @Levels)
    BEGIN
        
        -- Add another column to to the table
        EXEC('ALTER TABLE #result ADD [' + @counter + '] DECIMAL(38,0)')

        -- Select the previous row into a temp table
        SELECT *
        INTO #row
        FROM #result
        ORDER BY 1
        OFFSET (@counter - 2) ROWS
        FETCH NEXT 1 ROWS ONLY

        -- Re-initialize the temp variables, and loop counter
        SET @tmp = 1
        SET @tmp2 = 1
        SET @innerCount = 2

        -- For every column beyond 1
        WHILE(@innerCount <= @counter)
        BEGIN

            -- Create a query which selects the current value of the cell to back it up.
            SET @dquery = 'SELECT @t = [' + CAST(@innerCount AS NVARCHAR(10)) + '] FROM #row'
            EXEC sp_executesql @dquery, N'@t DECIMAL(38,0) OUTPUT', @t = @tmp2 OUTPUT

            -- Set the value of this cell in this row to that + @tmp
            EXEC('UPDATE #row SET [' + @innerCount + '] = ISNULL([' + @innerCount + '], 0) + ' + @tmp)

            -- Reset the temp variable
            SET @tmp = @tmp2

            -- Increment the count
            SET @innerCount += 1

        END

        -- Insert the row into result
        INSERT INTO #result
        SELECT * FROM #row

        -- Increment the counter
        SET @counter += 1

        -- Drop the row temp table
        DROP TABLE #row
    END

    -- Output the result
    SELECT * FROM #result
    DROP TABLE #result
END
                            
                        
There is another post in PowerShell which does a much better job actually explaining details about how this triangle works. But long story short, every item is the sum of the two above it added together. So now let’s make the script a little smaller.
raw
CREATE PROCEDURE PascalsTriangle2
	@Levels INT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @o INT=2
	DECLARE @ INT
	DECLARE @1 DECIMAL(38,0)
	DECLARE @2 DECIMAL(38,0)
	DECLARE @q NVARCHAR(2048)
	CREATE TABLE #o([1] DECIMAL(38,0))
	INSERT #o VALUES(1)
	o:
 		EXEC('ALTER TABLE #o ADD['+@o+']DECIMAL(38,0)')
 		SELECT *INTO #r FROM #o ORDER BY 1 OFFSET(@o-2)ROWS	FETCH NEXT 1 ROWS ONLY
 		SET @1 = 1
 		SET @2 = 1
 		SET @ = 2
 		i:
			SET @q = CONCAT('DECLARE @ TABLE (val DECIMAL(38,0));UPDATE #r SET [',@,']=ISNULL([',@,'], 0)+',@1,'OUTPUT deleted.[',@,']INTO @;SET @t=(SELECT *FROM @)')
			EXEC sp_executesql @q,N'@t DECIMAL(38,0) OUTPUT',@t=@2 OUTPUT  			
  			SET @1 = @2
  			SET @ += 1
 		IF @ <= @o GOTO i
 		INSERT #o
 		SELECT * FROM #r
 		SET @o += 1
 		DROP TABLE #r
	IF @o <= @Levels GOTO o
	SELECT * FROM #o
	DROP TABLE #o
END
                            
                        
As you can see, I removed all the pesky comments which were just getting in the way of looking small. Also, the while loops were all turned into GOTO loops to take up a little less space. The variable names were shortened a little as well.
raw
DECLARE @s NVARCHAR(MAX)='CREATE PROCEDURE PascalsTriangle3
@Levels INT
AS
BEGIN
	SET NOCOUNT ON
	~o |=2~ |~1 &~2 &~q NVARCHAR(2048)CREATE TABLE #o([1] &)INSERT #o VALUES(1)
	o:EXEC(''ALTER TABLE #o ADD[''+@o+'']&'')$|O #r ^#o ORDER BY 1 OFFSET(@o-2)ROWS	FETCH NEXT 1 ROWS ONLY!1 = 1!2 = 1! = 2
 		i:!q = CONCAT(''~ TABLE (val &);UPDATE #r SET ['',@,'']=ISNULL(['',@,''], 0)+'',@1,''OUTPUT deleted.['',@,'']|O @;!t=($^@)'')EXEC sp_executesql @q,N''@t & OUTPUT'',@t=@2 OUTPUT!1 = @2!+=1IF @ <= @o GOTO i
 		INSERT #o$ ^#r!o += 1%#r IF @o <= @Levels goto o$ ^#o%#o
END'
SELECT @s=REPLACE(@s,LEFT(i,1),SUBSTRING(i,2,20))
FROM(VALUES('~ DECLARE @'),('! SET @'),('& DECIMAL(38,0)'),('% DROP TABLE '),('$ SELECT *'),('^ FROM '),('| INT'))a(i)
EXEC(@s)
                            
                        
Now there’s the real beast. It does a bunch of replacements on a string, to make some longer valid SQL, then executes the SQL dynamically to produce the stored procedure. That’s some production quality code.



A programming question that I’m sure everyone has been asked at some point during the interview is ‘Pascal’s Triangle’. Besides solving it, nothing would impress your interviewer more than solving it in PowerShell, with support for massively huge numbers. Wikipedia could probably explain this better than I can, but the premise is simple: start with ‘1’, then each row is one element longer than the previous and is made up of the to values above it added together (Except for the values on either end, which are always 1).
raw
function Pascals-Triangle
{
    [CmdLetBinding()]
    param
    (
        # Single parameter is the number of levels
        [Parameter(Mandatory=$true)][ValidateRange(1, [int]::MaxValue)][int]$Levels
    )
    process
    {
        # Create a dummy previous array, just so that we know what is going on.
        $prev = [System.Numerics.BigInteger[]]::new(0)

        # Now for each level
        for($l = 0; $l -lt $Levels; $l++)
        {
            # Create the current working array
            $current = [System.Numerics.BigInteger[]]::new($prev.Length + 1)
            
            # We know for sure that the first and the last element are 1 (This avoids bounds checking in the loop below)
            $current[0] = 1
            $current[$current.Length - 1] = 1

            # Now for all other elements add the previous two together
            for($i = 1; $i -lt $current.Length - 1; $i++)
            {
                $current[$i] = $prev[$i - 1] + $prev[$i]
            }

            # Write out our result
            Write-Output ([string]::Join(' ', $current))

            # Set the previous to the current for the next iteration
            $prev = $current
        }

    }
}

                            
                        
This is most likely the solution that any interviewer would be expecting, because it is by far the simplest to follow. The idea is simple, start with an array of zero length and for every level of the triangle allocate an array which is 1 element longer, and populate it by adding together elements from the previous array. I’m not sure why, but whenever I got this question in college I wanted to do it recursively, which totally screwed me. Also, the reason behind using ‘BigInteger’, is that these number expand very rapidly. Try it out with ‘int’ or even ‘ulong’, and you wont be able to do too many levels. Anyway, the results in powershell end up looking like this.
1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1
1 6 15 20 15 6 1
So now, to mix things up, lets do it in a way that doesn’t require us to keep reallocating arrays. This next solution does the whole thing in place, reusing the same array each time. This is not a big deal, because we always know how long the array needs to be when we start. (Length = Levels). We can just go through and add up the values of the previous row each time.
raw
function Pascals-Triangle2
{
    [CmdLetBinding()]
    param
    (
        # Single parameter is the number of levels
        [Parameter(Mandatory=$true)][ValidateRange(1, [int]::MaxValue)][int]$Levels
    )
    process
    {
        # Create our working array
        $row = [System.Numerics.BigInteger[]]::new($Levels)

        # Now for each level
        for($l = 1; $l -le $Levels; $l++)
        {
            # The first index is always 1, and set our previous
            $iPrevious = $row[0] = 1
            for($i = 1; $i -lt $l; $i++)
            {
                # Back up the current value
                $temp = $row[$i]

                # Add in the previous value
                $row[$i] += $iPrevious

                # Swap out the previous
                $iPrevious = $temp
            }

            # Write out our result
            Write-Output ([string]::Join(' ', ($row | select -First $l)))
        }

    }
}
        
                            
                        
And there we have it. So what about a more obfuscated version for those really hard core interviewees? Lets do it. Taking solution 2 and squishing everything together as much as possible we get a horribly confusing solution.
raw
function Pascals-Triangle3
{
    [CmdLetBinding()]
    param
    (
        # Single parameter is the number of levels
        [Parameter(Mandatory=$true)][ValidateRange(1, [int]::MaxValue)][int]$Levels
    )
    process
    {
        ($r=[System.Numerics.BigInteger[]]::new(($l=$Levels)+1))[0]=1
        1..$l|%{"$($r|?{$_-ne0})";$t=1;1..$_|%{$a=$r[$_];$r[$_]+=$t;$t=$a}}
    }
}
                            
                        
Im sure everyone is better at code golf than I am, but this solution already confuses so it should be a good start.



How to create an arbitrary list in SQL. Its a little easier said than done. Heres a code snippet which will do just that. The biggest power of this, is running queries to figure out what kind of ranges do not have any data. For example, consider a table of values between 1 and 10000. If someone were to ask which unique set of numbers existed in the table, that would be pretty easy. But if someone were to ask which set of number did not exist in the table, that query would be pretty tough. You have nothing to join against.
raw
DECLARE @lowInclusive  INT = 3
DECLARE @highInclusive INT = 55

-- Declare a common table expression
;WITH generator AS
(
    -- In the base case, just select our first number as a row
    SELECT
        number
    FROM (VALUES(@lowInclusive)) AS base(number)

    UNION ALL

    -- Now select recursively from the common table until we reach our high number
    SELECT
        number + 1
    FROM generator
    WHERE number < @highInclusive
)
SELECT * FROM generator

                            
                        
There you have it. A row list of numbers. You can expand this do work with things like dates as well.
raw
DECLARE @lowInclusive  Date = GETUTCDATE()
DECLARE @highInclusive Date = DATEADD(DAY, 30, @lowInclusive)

-- Declare a common table expression
;WITH generator AS
(
    -- In the base case, just select our first number as a row
    SELECT
        d
    FROM (VALUES(@lowInclusive)) AS base(d)

    UNION ALL

    -- Now select recursively from the common table until we reach our high number
    SELECT
        DATEADD(DAY, 1, d)
    FROM generator
    WHERE d < @highInclusive
)
SELECT * FROM generator

                            
                        
There you have it. The result ends up looking like this
day
2017-08-12
2017-08-13
2017-08-14
2017-08-15
2017-08-16
2017-08-17
2017-08-18
2017-08-19
2017-08-20
2017-08-21
2017-08-22
2017-08-23
2017-08-24
2017-08-25
2017-08-26
2017-08-27
2017-08-28
2017-08-29
2017-08-30
2017-08-31
2017-09-01
2017-09-02
2017-09-03
2017-09-04
2017-09-05
2017-09-06
2017-09-07
2017-09-08
2017-09-09
2017-09-10
2017-09-11
And now you can easily join to figure out which days of the month someone forgot to pay their bills, or whatever.



Some messy C# code.

Mar09.17 07AM

Ever been asked FizzBuzz in a programming interview, and thought to yourself, what could I do here that would really throw them off? Well, this. Do this.
raw
private static void FizzBuzz1()
{              
	// Declare all of our variables right here at the top.
	int i, i2;
	string buff;
	Action<string> fb = null;
	goto assignVars;
	loop:
	{
		if (0 == i2 % 3)
		goto dofizz;
		if (0 == i2 % 5)
		goto dobuzz;
		goto donumber;
	}
	endoftheloop:
	if (i --> 0 && (i2 = 100 - i) > 0)
	goto loop;
	goto end;
	dofizz:
	buff += "fizz";
	if (0 == i2 % 5)
	goto dobuzz;
	goto writer;
	dobuzz:
	buff += "buzz";
	goto writer;
	donumber:
	buff += i2;
	writer:
	fb(buff + Environment.NewLine);
	goto justBuff;
	// Do this safely at the end of the function.
	assignVars:
	i = 100;
	fb = Console.Write;
	justBuff:
	buff = string.Empty;
	goto endoftheloop;
	end: ;
}
                            
                        
Nobody can be tooooo mad, since it really is a completely working fizzbuzz. Some may question who taught you that goto works in c#, and why you dont seem to know how to use a for-loop. Some may even question why you assigned Console.Write to an action, or why you didn't just use Console.WriteLine. All good questions. Seems like you would have a lot to talk about during your interview.
Theres nothing like a single method chain which does all of Fizz Buzz. So here's some awesome.
raw
private static void FizzBuzz2()
{
  Console.WriteLine(
    string.Join(
      Environment.NewLine,
      Enumerable.Range(1, 100)
        .Select(
          i =>
            string.Join(
              string.Empty,
              i % 3 == 0 ? "fizz" : null,
              i % 5 == 0 ? "buzz" : null,
              !(i % 5 == 0 || i % 3 == 0) ? $"{i}" : null))));
}
                            
                        



This post is to check if if images work! Will see what happens. This may be a mess.
If this picture isn't currently ruining the entire page, then this is a success.



Here is one to get the people going. Have you ever had an issue, where you tried to mount too many disks on your machine at one time, and ran out of driver letters? Well have no fear. It is a totally normal problem. Instead of mounting to drive letters... of which there are only 26. We can add access paths to partitions on the disk. You know what the best news is? The limit on access paths is totally crazy. I have once mounted 50 disks at the same time, and everything still seemed to work.
So here is the rub. This example is going to use a bunch of powershell cmdlets. But all of these are just wrappers around the root\microsoft\windows\storage CIM namespace. We could do this the badass way using only CIM, but it would be much longer, and I need something to write about tomorrow.
Anyway, this is what the code looks like.
raw
#
# Mount a virtual hard disk to a folder
#
function Mount-VHDToFolder
{
    [CmdLetBinding()]
    param
    (
        [Parameter(Mandatory=$true)][String]$Image,
        [Parameter(Mandatory=$true)][String]$MountFolder
    )
    process
    {
        try
        {
            # Gets a mounted disk image
            $mountedDisk = Mount-DiskImage -ImagePath $Image -NoDriveLetter -PassThru -ErrorAction Stop | Get-DiskImage
        }
        catch
        {
            Write-Error "Failed to mount disk"
            return
        }

        # Get all of the partitions
        $partitions = Get-Partition -DiskNumber $mountedDisk.Number

        foreach($partition in $partitions)
        {
            $partFolder = Join-Path $MountFolder $partition.PartitionNumber

            # Clean up this folder if it exists
            if(Test-Path $partFolder)
            {
                rmdir -Force $partFolder
            }

            # Make the new folder
            mkdir $partFolder | Out-Null

            try
            {
                # Add the access path for the disk
                Add-PartitionAccessPath -InputObject $partition -AccessPath $partFolder -ErrorAction Stop
            }
            catch
            {
                Write-Warning "Could not add access path '$($partFolder)' for partition '$($partition.PartitionNumber)'"
            }
        }
    }
}
                            
                        
This is going to create a folder for each partition number in the mount folder which you provide. It will try to add an access path for each partition. FYI, the reason I check if the folder exists, and delete it, is because AddAccessPath will fail if there is a broken symbolic link on the folder. AKA, you dismounted a disk which was using that folder as an access path without removing the access path first.
And there you have it! It is pretty simple really. Mounting the disk image gives us a MSFT_Disk cim object. We can use that disk to get all of the related MSFT_Partition cim objects. The MSFT_Partition object has a method called "AddAccessPath". Powershell wraps all of this stuff up for us super nicely so we won't need to mess with it.



New Post

Sep30.16 08AM

this is test test section



So here is the solution to an annoying one. Have you ever made an asp.net website and tried to make custom error pages? So here is the issue. I could not, for the longest time, figure out how to make my custom error pages actually show up for the user. Seriously, everything worked fantastic, until my HTTP response was anything other than 200. My solution was to return a "soft" 404 page. Which web crawlers really hate.
Soft Error - When you catch the error on the server, and return an error page with a success error code. For example a user browses to a link on your site which does not exist, causing a 404. You serve up a 404 page, but with a "200 OK" response code. From a user's perspective this is fine, but bots have no idea what is going on.
To be entirely honest, I made up most of that definition. Wikipedia refers to it as a "phony 404 error". Realistically you can do the same thing for any type of error on the server side. Actually, you can throw response codes out the window and return whatever crap you want all the time. Do it... Return a 500 for every single page. Users will never notice, but bots will lose their minds. (Actually you'll just never make it anywhere near the top on any search engine).
Ok So let's get back to the point here. Instead of a "Soft Error", you should always return the proper error code. IIS doesn't like to make this easy for you by default, so here is what you need to configure to make sure everything works properly.
raw
  <system.webServer>
    <httpErrors existingResponse="PassThrough"></httpErrors>
  </system.webServer>
                            
                        
Just stick this in the System.webServer section in your web.config. "existingResponse=PassThrough" pretty much means that you are going to take matters into your own hands when it comes to errors. If an error response comes down the pipe (aka: http code >= 400) then IIS will leave the message untouched if a response exists.
Hope this helps. My ones of users have been consistently complaining about poor error handling issues, so I dedicated my night (after this glorious Donald vs. Hillary debate) to fixing the issue. Thanks for your feedback Daniel.



© 2017 - Peter Sulucz | disclaimer

log in