Voice of the DBA

Author: way0utwest

Advent of Code Day 1, Puzzle B
As I continue through the Advent of Code, albeit slowly, I’m solving each puzzle 3 days. I worked through 6 of them in Python before moving on to other languages, and this is my chance to catch up with both PowerShell and T-SQL. I likely won’t post all solutions, but I was having fun rewriting code, so here are the ways I looked at things.

Note: You should try this on your own. I logged into the AventofCode with GitHub and things worked great for me.

Go on, give it a try.

I’ll wait.

Solutions coming.

Python

I started here, using iterations, which are very powerful in Python. In this case, I took advantage of the multiple variable assignment in Python to enumerate the array and get each value and index. I think use a comparison to determine if I add or subtract one. Finally, an IF returns the current index if I hit –1. I should probably have a break in there for efficiency as well.
```
def calculate_negative(directions):
    start = 0
    for i, c in enumerate(directions):
        if c == '(':
            start += 1
        else:
            start -= 1
        if start == -1:
            print(i)
```
PowerShell

This was a bit trickier for me. I wasn’t sure how to work with a string and pull out values. I did some searching and ran across the .ToCharArray function. That doesn’t feel like the best way to do this, but I decided to use it.

The rest of the function is similar and gave me the correct answer, so there you go.
```
$count = 0
$floor = 0
foreach ($c in $input.ToCharArray())
  { 
   if ($c -eq '(') {$floor += 1} 
   elseif ($c -eq ')') {$floor -= 1 }
   $count += 1
   if ($floor -eq -1)
     {
     $count
     break
     }
  }
```
T-SQL

A more complex situation here, given that we need to work through a string, calculating a running total. I broke the input up using a tally table and a splitter with substring. This was fairly easy, and not complex as I was just getting individual charaters.

Then it was a simple running total of the CTE to get me the totals at each point in time. This worked well, but I needed the first –1 total.

Finally, I used the outer query to get the min value out of the code and use that, which gave me the lowest value where the –1 occurred.
```
WITH tally (n)
AS
( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)        -- 10
   CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)  -- x 10 = 100
   CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)  -- x 10 = 1000
   CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) d(n)  -- x 10 = 10000
)
, direction (n, d)
AS
(
SELECT tally.n
   , d = CASE WHEN SUBSTRING(@input, n, 1) = '(' THEN 1 ELSE -1 end
 FROM tally
), currfloor 
as
(SELECT 
   d.n
   , 'currentfloor' = SUM(d.d) OVER (ORDER BY d.n ROWS UNBOUNDED PRECEDING)
FROM direction d
)
SELECT MIN(currfloor.n)
 FROM currfloor
WHERE currentfloor = -1
```
January 8, 2016
A Bit of History

This is a bit off topic, away from the world of databases, but it certainly does touch on the power of computers.

I had a great 2015 holiday season, taking not one, but two vacations. Our family had their annual ski trip to Steamboat Springs, which is always enjoyable. However before Christmas, I took my 17 year old on a birthday trip to Florida. We’ve gotten away from parties and gifts, instead trying to give kids memories.

In this case, we journeyed to Cape Canaveral, spending a few days at the Kennedy Space Center viewing exhibits and going on tours. My son was thrilled to see real rockets, especially the Saturn 5, and also tour one of the launch control rooms. In fact, we could see the countdown clock active for the launch of a SpaceX Falcon 9 rocket on our tour of the Launch Control Center.

That was exciting to see. I’d tried to schedule the trip near a launch but couldn’t get away during the launch dates with school and work commitments. However the day we left, I saw the announcement that the previously TBD SpaceX launch had been scheduled during our trip. After a scrub one night with wind and rain, the launch took place our last night in town.

The official video is better than mine, which I recorded from the Cape Canaveral causeway, about 5-6 miles away. It’s an amazing piece of history. Other than my wedding day and the births of my children, this was the most amazing thing I’d seen, even from a distance. I’m interested in space and technology, but not a big space geek. However I was definitely giddy at seeing this bit of history.

Computing technology has helped us achieve some amazing things, and in conjunction with incredible engineering, as humans, we’ve managed to accomplish some amazing feats with rockets and space travel. I look forward to seeing what will happen across the next few decades as more companies and countries tackle new challenges in space.

And if you ever have any doubt about the power of the computers we use, it appears the Falcon rocket uses x86 processors, like so many of us.

Steve Jones

The Voice of the DBA Podcast

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

January 8, 2016
Advent of Code Day 1, Puzzle A
I have been working my way through the Advent of Code series across the last few weeks. This is a side project, allowing me to use some Python skills to solve the various puzzles. I also started to use PowerShell to solve the puzzles, mostly becauase this means I practice new skills.

Note: I urge you to try to work on this yourself, withough reading my solutions. It’s fun and worth some practice. Once you’ve done that, feel free to read on.

Go ahead

Write your own.

Here’s the input:

DECLARE @input VARCHAR(MAX) = ‘()(((()))(()()()((((()(((())(()(()((((((()(()(((())))((()(((()))((())(()((()()()()(((())(((((((())))()()(()(()(())(((((()()()((())(((((()()))))()(())(((())(())((((((())())))(()())))()))))()())()())((()()((()()()()(()((((((((()()())((()()(((((()(((())((())(()))()((((()((((((((())()((()())(())((()))())((((()())(((((((((((()()(((((()(()))())(((()(()))())((()(()())())())(()(((())(())())()()(()(()((()))((()))))((((()(((()))))((((()(()(()())())()(((()((((())((((()(((()()(())()()()())((()((((((()((()()))()((()))()(()()((())))(((()(((()))((()((()(()))(((()()(()(()()()))))()()(((()(((())())))))((()(((())()(()(())((()())))((((())))(()(()(()())()((()())))(((()((()(())()()((()((())(()()((())(())()))()))((()(())()))())(((((((()(()()(()(())())))))))(()((((((())((((())((())())(()()))))()(())(()())()())((())(()))))(()))(()((()))()(()((((((()()()()((((((((()(()(())((()()(()()))(())()())()((())))()))()())(((()))(())()(())()))()((()((()(()()())(())()()()((())())))((()()(()()((()(())()()())(((()(()()))))(())))(()(()())()))()()))))))()))))((((((())))())))(()(())())(()())))))(()))()))))))()((()))))()))))(()(()((()())())(()()))))(((())()))())())())(((()(()()))(())()(())(())((((((()()))))((()(()))))))(()))())(((()()(()))()())()()()())))))))))))))(())(()))(()))((()(())(()())(())())(()())(())()()(()())))()()()))(())())()))())())(())((())))))))(())))(())))))()))))((())(()(((()))))(()))()((()(())))(()())(((((()))()())()()))))()))))()))())(()(()()()))()))))))((()))))))))))()((()))((()(())((())()()(()()))()(()))))()()(()))()))(((())))(())()((())(())(()())()())())))))))())))()((())))()))(()))()()))(((((((()))())(()()))(()()(()))()(()((()())()))))))(((()()()())))(())()))()())(()()))()()))))))))(())))()))()()))))))()))()())))()(())(())))))()(())()()(()()))))())((()))))()))))(()(((((()))))))))())))())()(())()()))))(())))())()()())()()())()(()))))()))()))))))))())))((()))()))()))())))()())()()())))())))(()((())()((()))())))))())()(())((())))))))))))())()())(())())())(()))(()))()))())(()(())())()())()()(()))))(()(())))))))(())))())(())))))))())()()(())())())))(())))))()))()(()())()(()))())())))))()()(()))()))))())))))))))()))))()))))))())()())()()))))()())))())))))))))))()()))))()()(((()))()()(())()))))((()))))(()))(())())))(())()))))))(()))()))))(())())))))()))(()())))))))))))))())))))))))()((()())(()())))))))((()))))(())(())))()(()())())))())())(()()()())))()))))))())))))())()()())))))))))))()()(()))))()())()))((()())(()))))()(()))))))))))()())())(((())(()))))())()))()))()))))))()))))))(()))))()))))()(())))(())))(()))())()()(()()))()))(()()))))))))()))(()))())(()()(()(()())()()))()))))))))(())))))((()()(()))())())))))()))())(()())()()))())))()(()()()()))((())())))())()(()()))()))))))))(()))(())))()))))(()(()())(()))))()())())()))()()))())))))))))))())()))))))()))))))))())))))()))))())(()())))(())()))())())))))()()(()()())(()())))()()))(((()))(()()()))))()))))()))))((())))()((((((()()))))))())))))))))))(((()))))))))))))(())())))))())(()))))))(()))((()))())))()(()((()))()))()))))))))))())()))()(()()))))())))())(())()(()))()))())(()))()))))(()()))()()(())))))()))(())(()(()()))(()()())))))(((()))))))()))))))))))))(())(()))))()())())()()((()()))())))))(()))))())))))))()()()))))))))())))()(((()()))(())))))(((())())))))((()))()(()))(()))))(()())))(()))())))))()))))(())(())))()((()))(())())))()()))()))))))))()))(()()()(()()()(()))())(())()())(((()))(())))))))))(((()())))()()))))))))()(())(()))()((((())(())(()())))()))(((())()()()))((()))(()))())())))())))(()))())()())())(()(())())()()()(())))())(())))(())))(())()))()))(()((()))))))))())(()))))))())(()()))()()))()(()(()())))()()(()((()((((((()))(())))()()()))())()))((()()(()))())((()(()(()))(()()))))()())))()))()())))))))()()((()())(())))()))(()))(())(()))())(()(())))()()))))))(((()(((()()))()(()(())())((()()))()))()))()))()(()()()(()))((()())()(())))()()))(((())()()())(())()((()()()()(()(())(()()))()(((((()())))((())))))(()()()))))(((()(())))()))((()((()(())()(()((())))((()())()(()))(((()())()()(()))(())(((()((()())()((())()())(((()()))((()((())(()))(()())(()()()))((()))(())(()((()()())((()))(())))(())(())(())))(()())))(((((()(()(((((()())((((()(()())(())(()()(((())((()(((()()(((()()((((((())))())(()((((((()(()))()))()()((()((()))))()(()()(()((()()))))))(((((()(((((())()()()(())())))))))()))((()()(())))(())(()()()())))))(()((((())))))))()()(((()(()(()(()(()())()()()(((((((((()()())()(()))((()()()()()(((((((()())()((())()))((((((()(()(()(()())(((()(((((((()(((())(((((((((())(())())()))((()(()))(((()()())(())(()(()()(((()(())()))())))(())((((((())(()()())()()(((()(((())(()(((())(((((((()(((((((((()))(())(()(()(()))))((()))()(())())())((()(()((()()))((()()((()(())(())(()((())(((())(((()()()((((((()()(())((((())()))))(())((()(()((())))(((((()(()()())())((())())))((())((()((()()((((((())(((()()(()())())(()(()))(()(()))())())()(((((((()(((()(())()()((())((()(()()((()(()()(((((((((((())((())((((((())((()((((()(()((((()(((((((())()((()))))())()((()((((()(()(((()((()())))(())())(((()(((())((((((()(((((((((()()(())))(()(((((()((((()())))((()((()((()(()()(((())((((((((((((()(((())(()(((((()))(()()(()()()()()()((())(((((((())(((((())))))())()(()()(()(()(((()()(((((())(()((()((()(((()()((()((((())()))()((((())(())))()())(((())(())(()()((()(((()()((((((((((()()(()())())(((((((((())((((()))()()((((())(()((((()(((())())(((((((((((()((((())))(())(()(((()(((()((())(((((()((()()(()(()()((((((()((((()((()(()((()(()((((((()))))()()(((((()((()(()(())()))(())(((((((()((((()())(()((()((()(()))())))(())((()))))(((((((()()()())(()))(()()((()())()((()((()()()(()(()()))(()())(())(((((()(((((((((((()((()(((()(((((((()()((((((()(((((()(()((()(((((())((((((()))((((())((()()((())(((())()(((((()()(((((()((()(()(((((((()(((((()((()((()((())(())((())(()))()()))(()()(()(()()(((((((()(((()(((())()(((((()((((((()())((((())()((()((()(()()())(()))((((()()((((((()((()(()(()((((()((()((())((((((()(()(())((((((()((((((((((()((())()))()(()(()(((((()()()))((())))()(()((((((((((((((()(((()((((()((())((()((()(((()()(()(((()((())(()()())))()(()(()(((((()()(()(()((((()(((((())()(()(()))(((((()()(((()()(())((((((((((((((())((())(((((((((((())()()()(())()(()(()(((((((((())(((()))(()()())(()((((()(())(((((()())(())((((((((())()((((()((((((())(()((()(())(((()((((()))(((((((((()()))((((()(())()()()(())(()((())((()()))()(((())(((((())((((((()()))(((((((((()((((((())))(((((((()((()(()(())))())(()(()))()(((((()())(()))()(()(())(((()))))())()())))(((((()))())()((()(()))))((()()()((((((()))()()((((((((())((()(()(((()(()((())((()())(()((((())(()(((()()()(()(()()))())())((((((((((())())((()))()((())(())(())))())()(()()(())))())(()))(((()(()()(((()(((())))()(((()(())()((((((())()))()))()((((((()(()(((((()())))()))))())()()(((()(((((())((()()(()((()((()(()(()(())))(()()()()((()(())(((()((()))((((()))())(())))())(()))()()()())()))(((()()())()((())))(())(()()()()(()())((()(()()((((())))((()((()(())((()(()((())()(()()(((()())()()())((()))((())(((()()(())))()()))(((()((())()(((((()())(())((())()())())((((((()(()(((((()))(()(‘;

Now solve it.

Your turn.

Here come my solutions, so don’t scroll down until you’re ready.

Python

I’ve been trying to learn Python, so this was a good chance to practice. Day 1 is fairy simple in Python, since I can do a .count of a string. In my case, I made a function that takes the input and does a count of a particular string.
```
def calculate_floor(directions):
    print(str(directions.count('(') - directions.count(')')))
```
This function takes the input and counts the number of open parenthesis, an up, and then subtracts the count of the close parenthesis, the down. I print that, and with the input given on the site, this gives me the correct answer.

PowerShell

I have been trying to improve my PowerShell skills, and this doesn’t really help with lots of the types of things I do in PoSh, but it does help me learn a bit more about the language and practice some code.

In this case, I take a similar approach, which may not be the best. I start by converting the input to an array and then using Where-Object to look for the open parenthesis. I do the same for the close, and then subtract. I don’t know if I could do this in one statement, and didn’t research much. What I found wasn’t helpful, but I was able to solve the puzzle. Perhaps someone will tell me if I can assign two variables at once with different counts.

I did try RegEx, but coulnd’t get it to work, so I moved on. Perhaps I’ll go back to that. Input abbreviated.

$input = ‘()(((()))(()()()((((()(((())(()(()((((((()(()(((()’

$up = ($input.ToCharArray() | Where-Object {$_ -eq ‘(‘} ).Count
$down = ($input.ToCharArray() | Where-Object {$_ -eq ‘)’} ).Count

Write-Host(“Up:” + $up)
Write-Host(“Down:” + $up)
Write-Host(“Final:” + ($up-$down))

T-SQL

This was interesting to me since some of the puzzles I did in Python (I was 6 ahead when I tried new languages), required some parsing. In this case, I decided that I’d do some replacement. I thought that I wanted to count all open parens. How could I do this?

The easy way is to pull out the opens, or remove the closes. REPLACE() does this, so my up was a CTE

WITH UpFloors (UpCount)
AS
( SELECT LEN(REPLACE(@input, ‘)’, ”)) ‘UpFloors’)

Then I did the same for the closes.

WITH UpFloors (DownCount)
AS
( SELECT LEN(REPLACE(@input, ‘(‘, ”)) ‘DownFloors’)

Now I can put them together.

WITH UpFloors (UpCount)
AS
( SELECT LEN(REPLACE(@input, ‘)’, ”)) ‘UpFloors’)
,
DownFloors (DownCount)
AS
( SELECT LEN(REPLACE(@input, ‘(‘, ”)) ‘DownFloors’)
SELECT UpFloors.UpCount – DownFloors.DownCount
FROM UpFloors, DownFloors
January 7, 2016
Rulebreaking Developers

The VW emissions scandal fascinates me. Maybe because it’s a case of software being used to fundamentally alter a product to deceive us. Maybe because I’m a car guy and have a soft spot for VW and the old Scirocco I used to drive in college. Maybe because we have blame being passed between management and software developers that could potentially affect our industry.

In any case, the latest news I saw was of a press conference where the upper management of VW seems to indicate that the software developers on the project decided to alter the software. Certainly I’m sure some managers were involved, but I still fundamentally find it hard to believe that a small team engineered these changes independently. Even if they did, I think that ultimately upper management throughout the company has to take responsibility. After all, they took credit for the sales.

There are a couple interesting points made by the company. First, they are upgrading IT systems to track work better. I’m hoping they’re moving to more of a DevOps practice, with transparency and openness, as well as lean processes that can be changed quickly. I suspect they might be moving to more overhead and auditing, but time will tell.

The other interesting thing is that the company noted that even when technical solutions were available, they weren’t used. To me, this directly points to the problems so many of us face as technologists. When we have better solutions, or we can correct deficiencies, management doesn’t want to spend the time or effort to do so.

That is one of the fundamental things that makes software so powerful. We can make changes, we can improve things, we can implement better security in a way that’s a fraction of the cost of making similar changes in the analog world. However it seems that even smaller efforts aren’t worth making if there is a little more profit to be squeezed out. At least, that’s what managers seem to think.

That’s a part of business culture that I’d like to see evolve. Let’s look to continually improve things, making them better, more secure, and less bug ridden, even if it slightly slows down new enhancements or costs a little money.

Steve Jones

The Voice of the DBA Podcast

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

January 7, 2016