Sunday, December 22, 2013

How to Get the Last 5 Rows from SQL Server

"And hear, I think, the very latest counsel"
-- Shakespeare, Henry IV Part 2
Act IV, Scene V, Line 182

Intro


Say you have the following need.

Given the following trans table:

CREATE TABLE trans (
   id int identity(1,1) not null
  ,transactionId int not null
  ,transactionTime datetime2 not null default (sysutcdatetime())
  ,amount int not null
);

  And the following data:

INSERT INTO trans (transactionId, amount) VALUES(1, 100);
INSERT INTO trans (transactionId, amount) VALUES(1, 125);
INSERT INTO trans (transactionId, amount) VALUES(2, 400);
INSERT INTO trans (transactionId, amount) VALUES(4, 25);
INSERT INTO trans (transactionId, amount) VALUES(5, 20);
INSERT INTO trans (transactionId, amount) VALUES(6, 70);
INSERT INTO trans (transactionId, amount) VALUES(7, 100);
INSERT INTO trans (transactionId, amount) VALUES(8, 33);

** Note, done this way to have different transactionTime **

When I query the trans table

Then I want to get the last 5 records in the trans table as indicated by transactionTime

This setup will give us the following.

http://sqlfiddle.com/#!6/11a33/2

Luckily, SQL Server 2012 offers a few ways to look at the last few records of a table.

TOP N


SELECT TOP 5 *
  FROM trans
  ORDER BY transactionTime DESC
;

By ordering by the transactionTime in descending order we are able to use the TOP filter to obtain the last 5 records.

http://sqlfiddle.com/#!6/11a33/1

ORDER BY with FETCH and OFFSET


SELECT *
  FROM trans
  ORDER BY transactionTime DESC
  OFFSET 0 ROWS
  FETCH NEXT 5 ROWS ONLY
;

Using the ORDER BY clause, we can use the new to SQL Server 2012 FETCH and OFFSET we are able to follow the ISO SQL 2008 standard for our limiting.

http://sqlfiddle.com/#!6/11a33/3

Page Size option


DECLARE @PageSize INT = 5

SELECT *
  FROM trans
  ORDER BY transactionTime DESC
  OFFSET 0 ROWS
  FETCH NEXT (SELECT @PageSize) ROWS ONLY
;

In SQL Server 2012, the new FETCH on the ORDER BY clause allows for queries to obtain the number of rows to retrieve!

http://sqlfiddle.com/#!6/11a33/6

Using ROW_NUMBER


SELECT id, transactionId, transactionTime, amount
  FROM (
    SELECT
      ROW_NUMBER() OVER(ORDER BY transactionTime DESC) AS row
     ,*
      FROM trans
  ) AS r
  WHERE r.row <= 5
;

Using the ROW_NUMBER() windowing function, we can create a poor man's FETCH and OFFSET by filtering the row numbers returned to be the number of records we want to obtain.

This idea came from Suprotim Agarwal's blog post on SQL Server Curry.

http://sqlfiddle.com/#!6/11a33/7

Performance


"Inform the database whenever you don't need all rows."
-- Markus Winand, Use the Index Luke - Top-N Rows Queries

SQL Server offers many different ways to get the last 5 records, but one must think of performance.  If you have an indexed order by then the DBMS would not even have to perform the sort!

Sunday, December 15, 2013

Living with Function Programming OR Unit Testing F# with FsUnit

"Let there be some more test made of my metal
Before so noble and so great a figure
"
-- Shakespeare, Measure for Measure
Act I, Scene I, Lines 48-49

The Quest


One of the first things I asked myself while learning F# was, "This is great, how do I test this stuff?"  I did what any programmer would do and asked the Google, which pointed me to a question on Stackoverflow and a slide deck on slideshare.  Between the the top answer on Stackoverflow and the 5th slide in the deck.  I was off and running doing TDD in F# with FsUnit and NCrunch!

Intro to FsUnit


FsUnit uses the BDD should style of testing, which I personally am starting to prefer.  I hear, an example would be nice right about now; in the back of my head, here you go (these examples are taken from the FsUnit README.md):

1 |> should equal 1
1 |> should not' (equal 2)

"ships" |> should startWith "sh"
"ships" |> should not' (startWith "ss")
"ships" |> should endWith "ps"
"ships" |> should not' (endWith "ss")

[1] |> should contain 1
[] |> should not' (contain 1)


The pattern which FsUnit follows is:

{actual-result} |> should {statement-to-test} {expected-result}

Looking at the first line in the example (1 |> should equal 1) we have:

1 as the actual-result
equal as the statement-to-test
1 as the expected-result

F# is a functional language, so we should think of everything in the FsUnit test as a function.  Given that, we see that the should is looking for: an actual-result, a statement-to-test (equalnot'endWithcontain, ...), and the expected-result.  Using the |> pipe we get a readable form like, 1 should equal 1 (1 |> should equal ).

FsUnit with the Even or Odd example


Let us look at MSDN's favorite F# example, even or odd; using FsUnit?

Say we have the following:

let isOdd x =
  if x % 2 = 0 then false
  else true
  
let isEven x = isOdd x |> not

We can use the following to test these two functions using FsUnit and NUnit.

[<Test>]
let ``Given 2 isOdd is false`` () =
  isOdd 2 |> should be False

[<Test>]
let ``Given 2 isEven is true`` () =
   isEven 2 |> should be True

[<Test>]
let given_3_isOdd_is_true () =
  isOdd 3 |> should equal true

[<Test>]
let given_3_isEven_is_false () =
  isEven 3 |> should equal false

Looking at the tests we see that we can use backticks to allow for more readable names to our tests.  For boolean results we can also choose between should be False and should equal false, the first using FsUnit's keyword be with its False and the other using equal and F#'s false.  Both from my experience work the same, so it is more a question around readability and maintainability.

FsUnit FizzBuzz example


Using NUnit we can uses attributes like TestCase to pass in values to our FsUnit tests.  Again the voice in the back of my head is saying, "How about an example?", so here it is for FizzBuzz:

open NUnit.Framework
open FsUnit

 let (|DivisibleBy|_|) divisor n =
  if n % divisor = 0 then Some() else None

let fizzbuzz n =
  match n with
  | DivisibleBy 3 & DivisibleBy 5 -> "FizzBuzz"
  | DivisibleBy 3 -> "Fizz"
  | DivisibleBy 5 -> "Buzz"
  | _ -> string n

[<TestCase(2,"2")>]
[<TestCase(4,"4")>]
[<TestCase(3,"Fizz")>]
[<TestCase(9,"Fizz")>]
[<TestCase(5,"Buzz")>]
[<TestCase(25,"Buzz")>]
[<TestCase(15,"FizzBuzz")>]
[<TestCase(30,"FizzBuzz")>]
let ``Given this value the result is this`` (value, result) =
  fizzbuzz value |> should equal result

Using a parameterized active pattern (based on yet another Stackoverflow response from Tomas Petricek!) we are able to have a FizzBuzz solution which does not test the mod 15 directly and is very expandable (in case the business wants a Woof for 7!).

We see that using the NUnit TestCase attribute we can simply have one FsUnit test which is passed in different input for the argument to fizzbuzz and the expected result!

Sunday, December 8, 2013

FizzBuzz - Now in SQL Server

"Yet again? What do you here? Shall we give o'er and
drown? Have you a mind to sink?
"
-- Shakespeare, The Tempest
Act I, Scene I, Lines 38-39

The great thing about the FizzBuzz kata is that it is very easy to understand, but has nearly an unlimited number of ways to implement it.  I am fan of using the right tool for the job, but I do enjoy stretching things to see what they can and cannot do with the hope of gaining a different point of view.  Having said that let us take a look at implementing the FizzBuzz kata in SQL using SQL Server 2012.

Overview of FizzBuzz


The goal of the FizzBuzz kata is to create a list of values which move through a filter with the following rules:

  • If the number given is divisible by 3 then produce "Fizz"
  • If the number given is divisible by 5 then produce "Buzz"
  • If the number is not divisible by either 3 or 5 then produce the number given

FizzBuzz has one edge case:

  • If the number given is divisible by both 3 and 5 then produce "FizzBuzz"


An example would be the following:


We see that:
2  => 2
4  => 4
3  => Fizz
9  => Fizz
5  => Buzz
25 => Buzz
15 => FizzBuzz
45 => FizzBuzz

How to Generate the Dataset in SQL Server


Given that we are going to use SQL Server, the first question is how do we generate a dataset to run through our FizzBuzz?  We could create a table with all of the values that we need, but that is not very interesting.  Instead we will use a Common Table Expression (CTE) to generate our data.

We'll use a Tally Table as outline by Vinay Pugalia's blog post.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT * 
  FROM [range]
;

This will generate the number 1 to 20 in a dataset.

http://sqlfiddle.com/#!6/9cd0c/7

We can generate any dataset starting at @min up to @max using this CTE, not bad.

Setting up the Business Logic


With our Common Table Expression set up to generate whatever range of numbers we want, we can now focus on the Business Logic of the problem.  Since we are looking for particular values to produce a result in some cases it sounds like the perfect job for the CASE statement.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT
  CASE
    WHEN num % 15 = 0 THEN 'FizzBuzz'
    WHEN num % 3  = 0 THEN 'Fizz'
    WHEN num % 5  = 0 THEN 'Buzz'
    ELSE                   CAST(num AS VARCHAR)
  END AS value
  FROM [range]
;

Gives use the output we would want:

http://sqlfiddle.com/#!6/9cd0c/12

This gets us what we want, but personally I do not like repeating the logic for divisible by 3 and 5 in the WHEN num % 15 = 0 case.

How about if we join to a set for the Fizz and another set for the Buzz?  If we could do that we could use the CONCAT string operation to cover the divisible by 15 edge case.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT
  CASE
    WHEN CONCAT(fizz.value, buzz.value) <> '' 
      THEN CONCAT(fizz.value, buzz.value)
    ELSE CAST(num AS VARCHAR)
  END AS value
  FROM [range]
  LEFT OUTER JOIN (
    SELECT 
      num AS id
    ,'Fizz' AS value
    FROM [range] WHERE num % 3 = 0
  ) AS fizz
    ON fizz.id = [range].num
  LEFT OUTER JOIN (
    SELECT 
      num AS id
    ,'Buzz' AS value
    FROM [range] WHERE num % 5 = 0
  ) AS buzz
    ON buzz.id = [range].num
;

We create subselects which give us the Fizz and Buzz filters we are looking for.  We then use the CONCAT to join a Fizz to a Buzz which will give us FizzBuzz on numbers divisible by both 3 and 5.  If we do not have a Fizz or a Buzz the CONCAT will give us '' (empty string), which means we'll want to return the number as a string.

This gives us:

http://sqlfiddle.com/#!6/9cd0c/20

We can make this a bit more readable using two more CTEs.

DECLARE 
 @min AS INT = 1
,@max AS INT = 20

;WITH [range] AS (
  SELECT @min AS num
  UNION ALL
  SELECT num + 1 FROM [range] WHERE num < @max
)
, fizz AS (
  SELECT 
      num AS id
    ,'Fizz' AS value
    FROM [range] WHERE num % 3 = 0
)
, buzz AS (
  SELECT 
      num AS id
    ,'Buzz' AS value
    FROM [range] WHERE num % 5 = 0
)
SELECT
  CASE
    WHEN CONCAT(fizz.value, buzz.value) <> '' 
      THEN CONCAT(fizz.value, buzz.value)
    ELSE CAST(num AS VARCHAR)
  END AS value
  FROM [range]
  LEFT OUTER JOIN fizz
    ON fizz.id = [range].num
  LEFT OUTER JOIN buzz
    ON buzz.id = [range].num
;

Which still gives us the same output.

http://sqlfiddle.com/#!6/9cd0c/22

Personally I find the three CTEs a bit cleaner to read and understand, but I understand that not everyone will feel the same way.

There you have it FizzBuzz using SQL Server.  You can find the solution on SQL Fiddle (and see all my typing errors to get to it).

"They say there's no such place... as Paradise. Even if you search to the ends of the Earth, there's nothing there. No matter how far you walk, it's always the same road. It just goes on and on. But, in spite of that... Why am I so driven to find it? A voice calls to me... It says, 'Search for Paradise.'"
-- Keiko Nobumoto, Wolf's Rain
Final Lines

Saturday, November 30, 2013

Surprises with JavaScript's Async Single Threadness

"Must wait till you be called for."
-- Shakespeare, Henry VIII
Act V, Scene II, Line 6.1

Quick question what is the output of the following JavaScript code?

for (var i = 1; i < 4; i++){
  setTimeout(function(){console.log(i);}, 0);
};

A) 1, 2, and then 3 in that order
B) 1, 2, and 3 in some order
C) 4, 4, and then 4 again
D) The example is wrong and will not even run!

Knowing myself I would bet on D, but the correct answer is drum roll ....

C!

C:\async_examples\queueing\lib>node setTimeoutExample.js
4
4
4


Yep, the code above will output 4, 4, and then 4 again.  Why is this?

For normally everyday uses, JavaScript is single-threaded in all major implementations meaning: normal browsers, IE, Node.js, ...  Spending most of my career with Java I found this very surprising when I read about it in Async JavaScript by Trevor Burnham, which is where I got this example. (I do not expect to get anything for linking to the book, other than getting others to read it and write good JavaScript.)

OK, single-threading is fine, but why is the output not 1, 2, 3?!?  I mean we are setting the timeout to 0!  JavaScript uses queues for its asynchronous events and since it is single-threaded the code must complete before the queue is processed.

I believe a picture would be helpful right about now.



Looking at the flow above we see that once we are done with the loop, which is placing events on the timeout queue, the system then processes all the items in the queue.


Looking at the state of the memory after the loop, we see that the queue is full of console.log(i).  By the time that the console.log(i) is processed the value of i is 4.  Thus, we will get three 4's as our output.

The beauty of this type of model is that you do not have to worry about your code being interrupted.  The downside is that this is unlike what most programmers have used before.

Saturday, November 23, 2013

On NULL

"Nothing will come of nothing."
Shakespeare, King Lear
Act I, Scene I, Line 90

"For we brought nothing into this world, and it is certain we can carry nothing out."
King James Bible
1 Timothy 6:7

What does it mean for something to be NULL?  Even better what is the meaning of meaning?  Even more interesting why does no one care?

These are interesting questions, but not really in the realm of a pragmatic programer.  How about an easier question, how does NULL work with different operations in different environments?  Now this is a question that we can search out an answer for and maybe learn something along the way.

NULL in SQL Server 2012


Databases and NULLs, nothing is more hotly debated (well maybe NoSQL v. RDBS, but I digress).  Lets look at how NULL works with subtraction, addition, multiplication, and division in SQL Server 2012.

SELECT '9 - NULL' AS Problem, 9 - NULL AS 'Answer'

UNION ALL

SELECT 'NULL - 9' AS Problem, NULL - 9 AS 'Answer'

UNION ALL

SELECT 'NULL - NULL' AS Problem, NULL - NULL AS 'Answer'

UNION ALL

SELECT '9 + NULL' AS Problem, 9 + NULL AS 'Answer'

UNION ALL

SELECT 'NULL + 9' AS Problem, NULL + 9 AS 'Answer'

UNION ALL

SELECT 'NULL + NULL' AS Problem, NULL + NULL AS 'Answer'

UNION ALL

SELECT '9 * NULL' AS Problem, 9 * NULL AS 'Answer'

UNION ALL

SELECT 'NULL * 9' AS Problem, NULL * 9 AS 'Answer'

UNION ALL

SELECT 'NULL * NULL' AS Problem, NULL * NULL AS 'Answer'

UNION ALL

SELECT '9 / NULL' AS Problem, 9 / NULL AS 'Answer'

UNION ALL

SELECT 'NULL / 9' AS Problem, NULL / 9 AS 'Answer'

UNION ALL

SELECT 'NULL / NULL' AS Problem, NULL / NULL AS 'Answer'

Gives us the following on SQLFiddle.

http://sqlfiddle.com/#!6/6dc1f/2

All NULL, interesting.


NULL in MySQL 5.6.6 m9


How about MySQL?  (I do not use MySQL, so I just picked the newest version on SQLFiddle)

After a quick check of the MySQL documentation we see that we can use the exact same syntax to SELECT from no table (FROM DUAL can also be used).

SELECT '9 - NULL' AS Problem, 9 - NULL AS 'Answer'

UNION ALL

SELECT 'NULL - 9' AS Problem, NULL - 9 AS 'Answer'

UNION ALL

SELECT 'NULL - NULL' AS Problem, NULL - NULL AS 'Answer'

UNION ALL

SELECT '9 + NULL' AS Problem, 9 + NULL AS 'Answer'

UNION ALL

SELECT 'NULL + 9' AS Problem, NULL + 9 AS 'Answer'

UNION ALL

SELECT 'NULL + NULL' AS Problem, NULL + NULL AS 'Answer'

UNION ALL

SELECT '9 * NULL' AS Problem, 9 * NULL AS 'Answer'

UNION ALL

SELECT 'NULL * 9' AS Problem, NULL * 9 AS 'Answer'

UNION ALL

SELECT 'NULL * NULL' AS Problem, NULL * NULL AS 'Answer'

UNION ALL

SELECT '9 / NULL' AS Problem, 9 / NULL AS 'Answer'

UNION ALL

SELECT 'NULL / 9' AS Problem, NULL / 9 AS 'Answer'

UNION ALL

SELECT 'NULL / NULL' AS Problem, NULL / NULL AS 'Answer'

Let's see what we get.

http://sqlfiddle.com/#!9/6dc1f/1

Results are exactly the same as SQL Server, everything is NULL.

NULL in SQLite (SQL.js)


How about using NULL in something a bit different, how about running it in SQL.js?

I've never really used SQLite, but a quick look at StackOverflow says that we can continue with our example of SELECT with no table.

SELECT '9 - NULL' AS Problem, 9 - NULL AS 'Answer'

UNION ALL

SELECT 'NULL - 9' AS Problem, NULL - 9 AS 'Answer'

UNION ALL

SELECT 'NULL - NULL' AS Problem, NULL - NULL AS 'Answer'

UNION ALL

SELECT '9 + NULL' AS Problem, 9 + NULL AS 'Answer'

UNION ALL

SELECT 'NULL + 9' AS Problem, NULL + 9 AS 'Answer'

UNION ALL

SELECT 'NULL + NULL' AS Problem, NULL + NULL AS 'Answer'

UNION ALL

SELECT '9 * NULL' AS Problem, 9 * NULL AS 'Answer'

UNION ALL

SELECT 'NULL * 9' AS Problem, NULL * 9 AS 'Answer'

UNION ALL

SELECT 'NULL * NULL' AS Problem, NULL * NULL AS 'Answer'

UNION ALL

SELECT '9 / NULL' AS Problem, 9 / NULL AS 'Answer'

UNION ALL

SELECT 'NULL / 9' AS Problem, NULL / 9 AS 'Answer'

UNION ALL

SELECT 'NULL / NULL' AS Problem, NULL / NULL AS 'Answer'

http://sqlfiddle.com/#!5/6dc1f/1

Interesting, we get nothing, no text, no NULL, nothing.  Hmmm, what does this mean?  How about another test.

SELECT NULL AS 'value'

UNION ALL

SELECT '' AS 'value'

UNION ALL

SELECT 9 AS 'value'
;

INSERT INTO notNeeded (id)
VALUES(NULL),(8);

SELECT * FROM notNeeded; -- Needed now!

Hmmm, we get the following from SQLFiddle now.

http://sqlfiddle.com/#!5/6dc1f/7

A quick Google search and we find the following blog post which states.

"The .nullvalue command tells the SQLite to show NULL values as NULL. SQLite shows empty strings for NULL values by default"
-- Jan Bodnar, Inserting, updating and deleting data

As I was thinking, so the results are the same as SQL Server and MySQL, just NULL all the way down.

NULL in F#


How about a strongly typed language like F#?

We get the following:

9 - null;;
gives us
  9 - null;;
  --^

stdin(5,3): error FS0043: The type 'int' does not have 'null' as a proper value

Hmmm, how about this:

null - null;;
gives us
  null - null;;
  -----^

stdin(3,6): error FS0071: Type constraint mismatch when applying the default type 'int' for a type inference variable. The type 'int' does not have 'null' as a proper value Consider adding further type constraints

No luck, it will not even compile.  Looks like strong typing is working.

NULL in JavaScript


How about JavaScript (using node.js 0.10)?

C:\Documents and Settings\Mike Harris>node
> 9 - null
9
> null - 9
-9
> null - null
0
>

JavaScript is built to not "fail" and as such it treats null the same as 0.  Hmmm, how about if we divide by null?

> 9 / null
Infinity
> null / 9
0
> null / null
NaN
>

Interesting.  Let's look at the other odd values in JavaScript like [], {}, and NaN.

> 9 - []
9
> [] - 9
-9
> [] - []
0
> 9 - {}
NaN
> {} - 9
NaN
> {} - {}
NaN
> NaN - 9
NaN
> 9 - NaN
NaN
> NaN - NaN
NaN
>

Hmmm, [] is like 0 while {} and NaN are the same!  Interesting.

How about if we mix them?

> [] - {}
NaN
> {} - []
NaN
> NaN - {}
NaN
> NaN - []
NaN
> {} - NaN
NaN
> [] - NaN
NaN
> [] / {}
NaN
>

Gary Bernhardt did something very similar (but a lot better and funnier) at CodeMash 2012.

"With that harsh, noble, simple nothing"
-- Shakespeare, Cymbeline
Act III, Scene IV, Line 134

Saturday, November 16, 2013

Working with Multiple Values in a Column with SQL Server

"Any hard lesson that may do thee good."
-- Shakespeare, Much Ado About Nothing
Act I, Scene I, Line 272

SQL is a good tool when you have large sets of relational records--whether or not these kinds of relationships exist in the real world is not the aim of this post.  However, SQL is not very good at normalizing multiple records in a single attribute.

I know, an example would be good right about now.


As we see above Attribute 2 has three different values in it (Value 1, Value 2, and Value 3).

Say we want to do the following.

We want to take the Order File's Order Records and place the Order ID and others into the Order Table while placing the Order ID and Flags into the Order Flag Table.

Well we have a problem, how are we going to take the Flags and normalize them into the Order Flag Table?

Say the Flags are separated by spaces so Flags looks like this in an Order Record:


How would we do this in SQL?  Hmmm, well SQL is not really good at this kind of process so we need to do a kind of hack.

In SQL Server 2012 (should work in 2008 too) we can transform the Flags into XML that looks like this:

CHS
XMS
Once the data looks like this we can SQL Server's built in XML processing to create a virtual table to obtain the values from.

We can set up a test case using the following a CTE.

;WITH cte(value, id) AS (
  SELECT *
    FROM (
         VALUES
          ('HERP DREP CHS XMS', 1)
         ,('HELLO WORLD', 2)
       ) AS x(a, b)
)
SELECT *
FROM cte

Which looks like this in SQL Fiddle:


We see that we have two records:  ('HERP DREP CHS XMS', 1) and ('HELLO WORLD', 2).

Now lets take the Flags and transform them using an XML trick.


;WITH cte(value, id) AS (
  SELECT *
    FROM (
         VALUES
          ('HERP DREP CHS XMS', 1)
         ,('HELLO WORLD', 2)
       ) AS x(a, b)
)
SELECT
  flags.flag.value('.', 'varchar(50)') AS Flag
 ,id AS "Order ID"
 ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS "KEY"
FROM (
  SELECT
     CAST('<flag>' + REPLACE(value, ' ', '</flag><flag>') + '</flag>' AS XML)
    ,id
  FROM cte
) AS f(x, id)
CROSS APPLY f.x.nodes('flag') AS flags(flag)


We now have this in SQL Fiddle.

http://sqlfiddle.com/#!6/aed15/2

This gives us each Flag with its Order ID (Herp with 1, Derp with 1, CHS with 1, XMS with 1, Hello with 2, and World with 2).

This is almost what we want but we need an unique Key for each Flag across all of the records.  Now in the ETL tool I was using when I faced this problem this week, it needed an unique Key for each of the Flag records that would come out of the SQL used, even though it would be mapping this unique Key to an Identity column in SQL Server.  Yes, this is very dumb but

When tools do dumb things, one must make sure that one does not do dumb things himself. 
-- Me, this blog post

With this in mind, let us create an unique Key in a smart way for each Flag record coming out of our SQL hack.


This gives us the following in SQL Fiddle.

http://sqlfiddle.com/#!6/aed15/3

Now we have an unique Key!

This gives us each Flag with its Order ID and Key (Herp with 1 and 1, Derp with 1 and 2, CHS with 1 and 3, XMS with 1 and 4, Hello with 2 and 5, and World with 2 and 6).

Nice now we can get back to delivering real value to our customers and not just figuring out tools and strange data.

If you want to see other examples using CTEs and SELECT NULL see these other post by me.
http://comp-phil.blogspot.com/2013/02/the-case-of-not-having-unique-identity.html
http://comp-phil.blogspot.com/2013/02/how-to-remove-duplicate-records-using.html

Sunday, November 10, 2013

Code is Part of the Programmer and the Programmer is Part of their Code

"My songs cease, I abandon them,
From behind the screen where I hid I advance personally solely
    to you.

Camerado, this is no book,
Who touches this touches a man,
(Is it night? are we here together alone?)
It is I you hold and who holds you,
I spring from the pages into your arms—decease calls me forth.
"
-- Walt Whitman, Leaves of Grass (1891-1892, Deathbed edition)
So Long!, page 382

Being a programmer, a creator of worlds, is both humbling and invigorating at the sametime.  Everytime that I work my craft, I truly have endless potential, I could create something which changes lives or I could write a shitty monkey patch to just get something "done".  Even if it does not seem like it, the choice is always up to me.

Today, more so than any other time in human history, we software developers have created a small part of the world that exist.  Our creations have influenced and altered human civilization.  Don't believe me, post something to your Facebook that you know will get a response from family or friends.

When my first nephew William was born, my wife and I were let to see him only an hours or so after he was born.  I did what anyone else would do of my age group, I took pictures of him and us which I shared on Facebook.  Almost instantly I got a ton of likes and comments attached to these pictures, it then dawned on me that these were most likely the first images of Will that a lot of people would see.


And if you gaze for long into an abyss, the abyss gazes also into you.

-- Friedrich Nietzsche
Beyond Good and Evil, Aphorism 146
(generated from The Nietzsche Family Circus)

We creators of programs become part of our programs, which become part of us.  These programs become part of the user and the user becomes part of the program.  Thus, we programmers much like a poet, become one with our users and they become one with us.  This is really a humbling thing to think of.

Most of us do not work for Facebook or on code which is released to the general public, but our code is hopefully used.  When our code is used by someone or something it becomes a small part of that someone or something.  The point being, we become one with the world we consume and it becomes one with us.  When you stare into the abyss it stares back into you.

So what does this mean?  Which would you rather be associated with?


I have used my share of crappy software and third party vendor add-ons, when I use these "products" I cannot help but feel that the people who created them simply do not care.  I know this is faulty logic, in fact it is the fundamental attribution error, but still, WTF!  I am not alone in this feeling, in fact Uncle Bob recently blogged about his experience with Healthcare.gov and upgrading his Mac (neither of which were good experiences).

Becoming one with the world is not limited to programmer's end products!  This oneness with the world extends to their code they commit to source control!

At SCNA this Saturday, (November 9, 2013), Katrina Owen in her Here be Dragons talk linked the Prisoner's Dilemma to the development of software.  You see everytime you check in code you can either check in something that will be a pleasure for someone in the future (which might be you) or something that will cause a ton of swearing.

The choice is up to you, but the choice is even deeper than simply what you d.  Believe it or not what you check in influences other people.

Humans generally want to be cooperative and helpful to each other, but when crossed, O-boy the claws come out!  This applies to your code in the following way.  

Say that you are generally careful with your code and do all kinds of well thought of things (today that would be BDD, unit testing, "clean code", ...).  Others will see this and will most likely than not follow suit and raise the bar even higher.  The code base will generally become better overtime and general happiness will ensue.  The team will get along and will become happy with their code and the code will become better ...

What happens if someone has a bad day and checks in some crappy code?  One of two things could happen: either people retaliate with crappy code which could cause a downward spiral or they could let it slide, fix it, and continue checking in careful, well thought out code.


This my friends is the Prisoner's Dilemma!

With just one commit we have a simple Prisoner's Dilemma, but most programmers will work with each other for an undetermined number of commits across many days, months, years, and sometimes even decades!  This moves us from a simple single round of the Prisoner's Dilemma to that of an Iterated Prisoner's Dilemma.  In this type of game, the best strategy is what is known as tit-for-tat.

A tit-for-tat strategy:

1) start by being cooperative
2) do what the other player did

Tit-for-tat starts by being cooperative, thus starting things off on the right foot.  After the first cooperate, the tit-for-tat player will do whatever the other player did previously, thus if the player was cooperative the tit-for-tat player will be cooperative, if the other players defects, then the tit-for-tat player will defect. Defecting when the other player defects can lead to hell  A slightly more efficient tit-for-tat strategy will cooperate at random when in a defect spiral, this will allow for one to get out of a defect spiral, if one can.

With code (and everything else) things are not as simple as cooperate and defect, this is for many reasons, the most important one being not everyone agrees on what "Clean" and "Crap" code is, but we are humans and have a sense of being treated fairly and not fairly.  Thus, one can know if they are in a defect spiral or not.  If you find yourself in a defect spiral you can either see if things will change by issuing a corporate or simply leave.


Whoever fights monsters should see to it that in the process he does not become a monster.

-- Friedrich Nietzsche
(generated from The Nietzsche Family Circus)

If you are in a defect spiral, leave before you to become a monster and always start by committing well thought out clean code.  Thus, be what you want the world to be, for your code is more than just code, it is part of you and you are part of it.

Sunday, November 3, 2013

Introduction to Nodejs and Mocha

"All the world's a stage,
And all the men and women merely players;

They have their exits and their entrances,
And one man in his time plays many part
s"
-- Shakespeare, As You Like It
Act II, Scene VII, Lines 140.2 - 143

Prelude


Places, places, the show is about to begin.  Javascript has hit the big time!  Javascript has gone from a way to annoy, to a full blown language and we have Node.js to thank (well maybe AJAX, but Node.js helped pushed me over the edge).  If you are like me, you had written off Javascript as an annoyance that Web Programmers had to deal with, but now thanks to Node.js we can all find a use for Javascript.

Now do not get me wrong Javascript has more than its fair share of bad parts, but the more I use it, the more I like it.  It has just enough of functional elements to make me happy and yet it has enough object oriented elements to allow for any C#, Java, or Ruby programmer to feel at home.  Plus Node.js' package management system (npm) is simply wonderful.

Installing Node.js


To install Node.js head over to http://www.nodejs.org/ and install the correct installation for your system.


I just stuck with the default install options and have had no problems.

Hello World


The default installation gave me a Node.js folder on my start menu, launch the Node.js command prompt.



This will bring up a command prompt.

Run Node.js by typing, node at the prompt.  This will bring up a REPL (read evaluate print loop), you can use the REPL to try things out.  I normally have a REPL open along with a mocha --watch when I am programming with Node.js.

To write a Hello World in Node.js simply enter console.log("Hello World!); on the REPL.  Boom, you now have done Hello World using Node.js!


Hit Ctrl+c twice to exit.  O-if you are wondering, the undefined you see after Hello World! is the value of the result of the call to console.log.


TDD using Mocha

With Hello World out of the way, we can legally do something more interesting.  Lets download Mocha, which is the best testing framework I've found for working with Node.js.  To download and install Mocha use the Node package by running the Node Package Manager you installed when you installed Node.js by running the following at the command prompt: npm install -g mocha

npm install -g mocha, will download and install globally (that is what the -g means) the Mocha testing framework.



Mocha is a testing framework which allows for either the should BDD style or the assert Unit test style, personally I am starting to prefer the BDD style, but to each their own.

Let's create our first Node.js project!

In order for Mocha to be able to work you need a test directory.  Personally I like this structure for my Node.js projects.

  • project-name
    • src
      • source-file.js
    • test
      • file-spec.js

Nothing that I know of will force you to have a src directory, but I find it cleaner to have it (maybe because I am an old Java programmer).  Let's make the project structure, run the follow commands (assuming you are using Windows or an Unix based machine (I believe this will work on a Mac)):

mkdir  kata
cd kata
mkdir helloer
cd helloer
mkdir src
mkdir test


This will give you a kata directory and your first kata helloer.


Fire up your favorite text editor, I have really taken to Sublime Text lately, so that is what I will be using.

In the src directory create a file called helloer.js leave it empty for now.  In the test directory we'll create a file called helloerSpec.js with the following line of code.

var sut = require("../src/helloer");

This line will create a variable called sut (system under test) which will use to test the helloer code.  Not the syntax on the require, you need to use a .. to say go down a directory and the /src/helloer says where to find the helloer functions.

Let's run this with Mocha from the command prompt type mocha
You should see output like the following (if not you may have the directory structure different than what I have or you have something typed wrong).


Now let's actually do something!

First add should to your system by using the following in the helloer directory:

npm install should


This will install the should module which we'll us in our test file.

In the helloerSpec.js file add the following and save:

var sut = require("../src/helloer"),
 should = require("should");

 describe("helloer will say hello to whatever you pass in", function(){
  it("Nothing will just say hello", function(){
  sut.helloer().should.equal("Hello ");
  });
 });

The describe is a high level grouping for our tests, the it is our test case, in this case we are saying we want a function that when given nothing will produce the string "Hello ".

From the command line run mocha --watch

This will start a continuous testing environment (if this is your first time using one, it will change the way you write your code).


Congratulations you have your first failing test in a continuous testing environment with Mocha!  Now let's make the failing test pass.

Add the following lines to the helloer.js file to get the failing test to pass.

var helloer = function(){
return "Hello ";
};

exports.helloer = helloer;

As soon as you save the continuous testing environment with Mocha will pick up on the changes and rerun the tests.


The exports gives a list of things (variables, functions, objects, ...) to expose.  In this case we are exposing helloer, note that the name on exports is will the caller sees not the local name.

When I do TDD I like to use as little amount of code as I can to get the test to pass (like playing golf).  I find that this leads to better code.  As the old saying goes, the code that you do not write has zero bugs.

Let's write our next failing test.

We'll say hello to Mike using the following code (in bold) to our helloerSpec.js file:

var sut = require("../src/helloer"),
 should = require("should");

 describe("helloer will say hello to whatever you pass in", function(){
  it("Nothing will just say hello", function(){
  sut.helloer().should.equal("Hello ");
  });

  it("Given Mike, hello will hello Mike", function(){
  sut.helloer("Mike").should.equal("Hello Mike");
  });
 });



Mocha will tells us that we have one failing test and that it is failing because it is receiving "Hello " and is expecting "Hello Mike".

Let's make this pass.

Add the following (in bold) to helloer.js:

var helloer = function(name){
return "Hello " + (name ? name : "");
};

exports.helloer = helloer;


The line (name ? name : "") may seem odd, but remember in Javascript the string value of undefined is "undefined" when found on the other side of a concatenation (+ with two strings), yeah odd.


Well, believe it or not we are done, but let's add in some more tests to make sure.

var sut = require("../src/helloer"),
 should = require("should");

 describe("helloer will say hello to whatever you pass in", function(){
  it("Nothing will just say hello", function(){
  sut.helloer().should.equal("Hello ");
  });

  it("Given Mike, hello will hello Mike", function(){
  sut.helloer("Mike").should.equal("Hello Mike");
  });

  it("Given 5, hello will hello 5", function(){
  sut.helloer(5).should.equal("Hello 5");
  });
 });



Congratulations, we have just finished some TDD with Node.js and Mocha.

In case you missed it, full files

src\helloer.js

var helloer = function(name){
return "Hello " + (name ? name : "");
};

exports.helloer = helloer;


test\helloerSpec.js

var sut = require("../src/helloer"),
 should = require("should");

 describe("helloer will say hello to whatever you pass in", function(){
  it("Nothing will just say hello", function(){
  sut.helloer().should.equal("Hello ");
  });

  it("Given Mike, hello will hello Mike", function(){
  sut.helloer("Mike").should.equal("Hello Mike");
  });

  it("Given 5, hello will hello 5", function(){
  sut.helloer(5).should.equal("Hello 5");
  });
 });