The SQL NULL paradox

Level: 1 where 1 is noob and 5 is totally awesome
System: MS SQL Server or PostgreSQL

The Paradox 

One of the best things about computing, it is so logical. We can always trust in 1 + 1 = 2, 1 < 2 or 1 = 1. So now I'm going to throw 4 queries and their results, as an example:

SELECT CASE WHEN 42 > 42 THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN 42 < 42 THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN 42 != 42 THEN 'True' ELSE 'False' END;-- Result: False

SELECT CASE WHEN 42 = 42 THEN 'True' ELSE 'False' END; -- Result: True

Hopefully no surprise there. Now I'm going to throw 4 queries more, and their result:

SELECT CASE WHEN NULL > NULL THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN NULL < NULL THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN NULL != NULL THEN 'True' ELSE 'False' END;-- Result: False

SELECT CASE WHEN NULL = NULL THEN 'True' ELSE 'False' END; -- Result: False

The three first lines, seems logical, but wait a minute:-). How is this possible? If NULL != NULL then NULL = NULL  must be true, else it is illogical. Or is it?

The Explanation

Actually it is not paradox. The reason, some might expect NULL = NULL to be true, is because they misunderstand NULL. They mistake NULL for being a value. If NULL was a value, then NULL = NULL would be true. So if it is not a value, what is it then?

The ANSI SQL-92 Specification says about NULL:

"null value (null): A special value, or mark, that is used to indicate the absence of any data value."   

I can understand the confusion, because have just written, it is not a value. But you have to notice it says "A special value". Absence of data value, is the right way to see NULL,  unknown or missing.

To be able to handle NULL values defined in the SQL-92 specs. Databases servers such as SQL Server and PostgreSQL uses trinary logic and not binary logic. It means, logic operations can have 3 out comes: True, False or NULL.

Because NULL is considered to be unknown, any comparison to NULL, even NULL vs. NULL will have False as result, because it is unknown what we compares. While using any arithmetic operator with NULL, will have NULL as result. The latter makes good sense. Lets say you add something to unknown, then result is unknown.

So in the end, it's all logical. For more read Handling Null Values for SQL Server, or 9.2. Comparison Operators for PostgreSQL. Even better read them both:-)

Data Modelling with Immutability

Level: 3 where 1 is noob and 5 is totally awesome
Disclaimer: If you use anything from any of my blog entries, it is on your own responsibility.

Intro

Data modelling with immutability, is a thing I have been thinking about for a while now, and if I had green field project I would think it in, from start. The concept is simple, instead of change states on objects, we create new ones(Well, sometimes it makes more sense to use mutable entities, but I'll get back on that). It is a concept, which makes it possible to create better data models, which resembles the things, we want to model more precisely.

Through out this blog post, I'll use the following Person-Address model, when clarifying the concept. The model is simple. It resembles a person and an address for this person.

The Concept

The conception is as mention before; instead of change the state of entities in the data model, we create a new ones. As an example, let us look, at our Person-Address model. Lets say we have a Person, who decides to move, which is the same as change address. A way to model this change of address, could be updating the address entity with the new address. But, not only from a modelling perspective is this wrong, but it can also give some technical challenges later on. Such querying the person former addressed, can be difficult.

The right thing to do, is to somehow mark the old address entity obsolete(but do not delete), create a new address entity, with the new address, and connect it to the person as the current address. Why is this right? It is right, because when a person is moving, they a moving to a new location/address. The new address has another house, while the old house is still standing at the old address. So by modelling address a immutable, we have the old address and the new address, just like in the real world. Actually, if the address entity was updated with a new address, it would be the same as saying, we are  dumping a new address and a house, on the old address. Not really possible in reality, eh?

The tricky part of modelling with immutability, is identifying what should be immutable and what should be mutable. Lets take a look at the Person-Address model again. Person. No matter the name of a person, the person would ideally always be the same. Thereby the person is mutable. So if a person changes name, the person entity should be updated. The entity should not be replaced, like in the in address example. If the entity was replaced, it would be same as saying it is a new person. So thing which remains the same, no matter the state, should be mutable.

Versioning and event sourcing

First a quick definition, of versioning and event sourcing. Event sourcing, is storing the event which caused an entity to change. A version is the result of an event.

Some might think, by using immutable entities in a model, we would get event sourcing, as an positive side-effect. Well, if we have to be absolute correctly, it wouldn't be event sourcing. Because we are not storing an event, we ares storing a result of an event a.k.a. a version. We are also only getting versioning for the immutable entities, because we are creating a new entity for every change. Mutable entities state changes should, like immutable entities events, be event sourced.

By modelling this way, you should always have the most true data model. As positive side effect, you should always be able, to query versions and events for every entity in your model. Happy modelling.

T-SQL: Comma Separating Values into Fields

Level: 2 where 1 is noob and 5 is totally awesome
System: SQL Server from 2005 and up

Visualising data

When querying data from databases, I'm always trying to get resultsets which are most presentable as possible. It save me a lot of time, because I then don't need to transform data further. The upsides are, I can either save a resultset as csv or just copy paste it to some spreadsheet or to a mail, right after the querying. The down side is, I have to type a bit more SQL.

In this blog post, I'll present one of my favorites methods, to easily visualize data in a smart way. I'm going to explain the method, using these 2 tables:

Items:
Id          Name                                               ColorId
----------- -------------------------------------------------- -----------
1           Cranberries                                        1
2           Plants                                             2
3           Shrimps                                            1
4           Ocean                                              3
5           Sky                                                3
6           Roses                                              1  

And

Colors:
Id          Color
----------- --------------------------------------------------
1           Red
2           Green
3           Blue

The most used way to show, the color for each item would be to join the tables like:

 SELECT Items.Id, Items.Name, Color FROM Items  
 INNER JOIN Colors ON Items.ColorId = Colors.Id  

Which gives:

Name                                               Color
-------------------------------------------------- -----------------
Cranberries                                        Red
Plants                                             Green
Shrimps                                            Red
Ocean                                              Blue
Sky                                                Blue
Roses                                              Red

It is readable, and if we sort by color it might be more readable. It might be most readable, if we could group the items by color. It requires a bit more SQL; but the result is better:

 SELECT   
  STUFF(  
      (SELECT ', ' + Name  
      FROM Items  
      where Items.ColorId = Colors.Id  
      FOR XML PATH (''))  
      , 1, 1, '') AS Items,  
 Color  
 FROM Colors;  

Gives:

Items                                Color
------------------------------------ ------------------------------------------
Cranberries, Shrimps, Roses          Red
Plants                               Green
Ocean, Sky                           Blue

Explanation

The magic happens in the inner SQL. FOR XML PATH is used as a smart way for concatenating string columns to one string column. By adding the ',' to Name, we are getting a comma separated list, where the first char is ','. For more about FOR XML PATH see: http://technet.microsoft.com/en-us/library/ms189885.aspx

STUFF are used to remove the first char(in this case ','), from the comma separated lists. Stuff is a sql command for inserting strings into strings, but is can also remove chars. In this case we are overwriting first char with a empty string. For more about STUFF see: http://technet.microsoft.com/en-us/library/ms188043.aspx

The rest should be self explanatory :-)

Starting and Running an Open Source Project

Level: 1 - 5 where 1 is noob and 5 is totally awesome

Introduction

This post is about my experiences and thoughts about starting and running an open source project. Let me start by introducing my own open source project called HaveBox. HaveBox is one the fastest IoC containers written for .NET. For more about HaveBox see http://www.havebox.net. Through developing and running the HaveBox project, I got some experiences which might help others with their projects. Some of the stuff in this post, might be common knowledge for some, and gold nugets for others.

Getting started

You might have a lot of reasons to start a project. Maybe you are unsatisfied with existing solutions, maybe you got a smart idea or maybe you just want to learn something. The most important factor is, for what ever the reason you choose to start a project, it has to be fun. The more fun, the more a project is likely to get released.

Make the project for your own sake only. Listen to others opinions, will be healthy for your project. Listening to much can throw your project of the course you did set for it. In general, only do what you feel is best for you project, because it is your project.

Most of all, don't be afraid to reinvent something, because your solution could end up being smarter or faster, or in general better. Also if you think about it. Maybe some of the established solutions is to matured. Maybe their concept has evolved over the years, and to keep up with evolution, they have been extended to a point where is impossible to add more. You maybe at a point in time, where all the basic features is thought of, so you can incorporate them from start, and leaving room for your own smart features.

Developing your project

When I develop open source for free, I would like my tool to be free to, or as cheap as possible. I also look at how much of my work, I can unload to the web. Ex. Version control and hosting. Specially if you are on your own, then reduce your devops tasks, and use your time on developing.

People sometimes ask me, why HaveBox is hosted at BitBucket and not at the more popular GitHub. The reason is BitBucket suits my workflow better. I really likes to work on things in private, and then release it in public when I feel my projects is ready. This kind of workflow cost money at GitHub, even thou it is an open source project when released. At BitBucket it is free, to have release and dev branches.

Selecting a language and a platform can be straight forward in some cases, but in many cases challenging decision. Some platforms and languages are more suited than others, for your project. Actually this might be the hardest part. Personally, I would like to do my private developing, on platforms and languages, which are not the same, as the ones I use in my professional work. This gives me the chance, to get experience with some more exotic an new stuff, plus it gives a broad knowledge.

Using hyped/new languages and platforms can be lot of fun, but I would only choose those for smaller project. The reason is, my experience says no matter how promising a hyped language seems for being consolidating, it only seems to last a couple of years. E.g. Ruby was the new black a couple of years, and even now I have heard talks about Node is yesterdays news. I would hate to have a big project, which had to be converted, because I choose a language which failed to settle.

Releasing the project

Develop and releasing your project, is actually a part of running the project. But usually there is no running of the project until first official release. 

Keep your release small, first release should contains only most basically functionality. Then else only a few new features per release. If you try to make all fit into one release, the project might be to boring and then it will die. Also smaller and often releases, might give some feedback which could point the project in a better direction.

Every time you release your project, remember to versioning it. I recommend using versioning like http://semver.org. Also the source code for each version should be available. Sometimes, people are using older versions of your project, and need the source to debug, then it will be a great help if they can use how your code works in that particular version.

Running the project

If you have done your  project and documented it properly, it should run it self more or less. Sometimes people wants to ask you questions, and you should think of how people should be able to ask you question.

Earlier I encouraged people to ask questions on Stack Overflow, so more people could gain from the answers.  My experience is, when you answer questions, and referring your documentation on the projects homepage, Stack Overflow thinks your are promoting your project and they don't like it. The consequence could be, they delete all your answers and write you a mail suggestion you can pay for promoting your project via Stack Overflow. Now I just encourage people, to write me directly or use the Issue Tracker on BitBucket. I refrain from crawling the web for HaveBox questions, it takes to much time, which could be used for development, so I only focus on direct contact now.

Most of the feedback I have received, has been good and constructive. Sometimes I also have received, harsh and unconstructive feedback. I have found the way to handle this, is to advise the feedbacker to use another product. If the feedbacker has misunderstood something, I will of course try to straight it out.

Most of all put yourself and your private life before your project. Don't feel bad about not developing on your project. Nothing good comes out of using force, so when an error is reported, sit back and analyse how big the problem is. Don't rush release, because of errors, but keep your users informed.  

How to promote you project

Promoting you project, is like selling a product. You have to identify what channels you can use for promoting. Personally I use Facebook, LinkedIn, 2 IoC container's benchmark pages and local .NET user groups. It all seems to work. 

If you get the chance, to give a speak about you project, take it. It is a very valuable way to promote your project. Even if it might sounds scary.

Do not use your project at work

Avoid using your project at work, because suddenly it can be hard to determine when it is your private project or a property of you company.

Also don't force your friends to use your project. Let them use the components for their project, they feel safe with. If you makes sure you project always works, and let it mature, then they might feel safe with it and start using it.

Good luck with your project

That is more or less, what I have for now. I might update this post when I some more. If you have something you would like to add, feel free to write me.

Key and Mouse events with NSViewController

Technical Level: 1 to 3 out of 5, where level 1 is noob and level 5 is totally awesome.
XCode version: 5

The Problem

Sometimes it is convenient for some apps, to be able to handle key and/or mouse events extraordinary.

While handling key and mouse events in many frameworks are straight forward. It is for what ever reason unnecessary complex in osx cocoa, when using the NSViewController class. Osx cocoa developers are encourage to use the NSViewController for their apps, and out of the box, it is suitable for most apps without extraordinary key handling.

NSViewController inherits from NSResponder, so theoretically, it should be possible to handle key/mouse events just by overriding the methods keyDown, keyUp, mouseDown or mouseUp. In practice, it doesn't work, yeah. Googling the solution to the problem, can just as frustrating as the problem, because information is very sparse. Plus the answers, can be hard to interpret for new cocoa developers.

The problem is, even thou NSViewController inherits from NSResponder, it is not added to the responder chain when initialising. Only responders on the responder chain, is able to handle events. We have to add NSViewControllers to the responder chain, for being able to handle key and mouse events. The only problem is, NSViewController is not capable to do this, by it self.
For more about the responder chain see: https://developer.apple.com/library/mac/documentation/cocoa/conceptual/eventoverview/EventArchitecture/EventArchitecture.html#//apple_ref/doc/uid/10000060i-CH3-SW2

The Solution
Actually the best solution can be found here: http://www.cocoawithlove.com/2008/07/better-integration-for-nsviewcontroller.html

So what I'm going to do, is to emphasise the solution cocoawithlove.com and making it clear, by making a walkthrough of how to implement it.

In short, we are going to create a subclass of the class NSView. We are going to use methods on the NSView class, to add the NSViewController class to the responder chain. The tricky part is to connect the NSViewController to the NSView, but we will go through that.

The next steps, may for some experienced cocoa developers, be extremely elementary. So if you know how to setup project with NSViewController, you might want to skip to the section: Implement keyDown and mouseDown
 

Setting up project with NSViewController

Create new project, and select Cocoa Application.

Fill out the properties for the new project.

Select a nice place for the project.

If everything went okay, you should have a screen more or less similar, to the one above.

Test the app, by pressing the play button. A window should pop up. When you see the window, you should stop the app, by pressing the stop button.

We are going to create a NSViewController. Select AppDelegate.m(to make sure the files we are going to create, ends up the in right folder) and press ⌘n. Should your newly created files, end in the supporting files folder. You can just drag them up to the KeyAndMouseEventExample folder.

Select Objective-C class

We are prefixing with App, just to prefix with something, because ViewController is an invalid name. Make sure With XIB for user interface is checked. Press next, and then Create on next page.

Click on AppDelegate.m and correct the code to:

 //  
 // AppDelegate.m  
 // KeyAndMouseEventExample  
 //  
 // Created by Christian Henrik Reich on 01/01/14.  
 //  
 #import "AppDelegate.h"  
 #include "AppViewController.h"  
 @interface AppDelegate()  
 @property (nonatomic,strong) IBOutlet AppViewController *ViewController;  
 @end  
 @implementation AppDelegate  
 - (void)applicationDidFinishLaunching:(NSNotification *)aNotification  
 {  
   self.ViewController = [[AppViewController alloc] initWithNibName:@"AppViewController"bundle:nil];  
   [self.window.contentView addSubview:self.ViewController.view];  
   self.ViewController.view.frame = ((NSView*)self.window.contentView).bounds;  
 }  
 @end  

Press the Play button again, to see everything works. Close app again with stop button.

I guess many of you have this NSViewController structure in your apps already. Nevertheless, we have something to build on now.

Implement keyDown and mouseDown

First we are going to create a subclass of NSView. Select AppDelegate.m(to make sure the files we are going to create, ends up the in right folder) and press ⌘n.

Select Objective-C class 

We are prefixing with App, just to prefix with something, because ViewController is an invalid name. NSView is properly not selectable in the combo box, but it can be typed in.

Select AppView.h and correct the code to:

 //  
 // AppView.h  
 // KeyAndMouseEventExample  
 //  
 // Created by Christian Henrik Reich on 01/01/14.  
 //  
 #import <Cocoa/Cocoa.h>  
 @interface AppView : NSView  
 {  
   IBOutlet NSViewController *viewController;  
 }  
 @end  

Select AppView.m and correct the code to:

 //  
 // AppView.m  
 // KeyAndMouseEventExample  
 //  
 // Created by Christian Henrik Reich on 01/01/14.  
 // Copyright (c) 2014 Christian Henrik Reich. All rights reserved.  
 //  
 #import "AppView.h"  
 @implementation AppView  
 - (void)setViewController:(NSViewController *)newController  
 {  
   if (viewController)  
   {  
     NSResponder *controllerNextResponder = [viewController nextResponder];  
     [super setNextResponder:controllerNextResponder];  
     [viewController setNextResponder:nil];  
   }  
   viewController = newController;  
   if (newController)  
   {  
     NSResponder *ownNextResponder = [self nextResponder];  
     [super setNextResponder: viewController];  
     [viewController setNextResponder:ownNextResponder];  
   }  
 }  
 - (void)setNextResponder:(NSResponder *)newNextResponder  
 {  
   if (viewController)  
   {  
     [viewController setNextResponder:newNextResponder];  
     return;  
   }  
   [super setNextResponder:newNextResponder];  
 }  
 - (BOOL)acceptsFirstResponder {  
   return YES;  
 }  
 @end  

Select AppViewController and correct the code to:

 //  
 // AppViewController.m  
 // KeyAndMouseEventExample  
 //  
 // Created by Christian Henrik Reich on 01/01/14.  
 //  
 #import "AppViewController.h"  
 @implementation AppViewController  
 - (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil  
 {  
   self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];  
   if (self) {  
     // Initialization code here.  
   }  
   return self;  
 }  
 -(void)keyDown:(NSEvent*)theEvent  
 {  
   NSLog(@"Caught key event");  
 }  
 -(void)mouseDown:(NSEvent*)theEvent  
 {  
   NSLog(@"Caught mouse event");  
 }  
 @end  

If you start the application, you will notice it is not working yet. We have to connect AppViewController to AppView's member viewController(See AppView.m), before it is working. Here comes the tricky and magic part :-)

Select AppViewController.xib

Select Custom View, and change Custom Class to AppView.

Select AppView formerly known as Custom View, it should be placed at the green circle to the left. Hold down control, and drag it to file owner.

On the outlet popup, select viewController

Press Play, to test the app. Click on the app's window, it should write "Caught mouse event" to output. Try to press some keys and see the "Caught key event" messages in output. As you might have noticed, handling of key events is not possible before, you have clicked the window once. Often it is best, if key events are available, when the app starts. To fix this issue, we have to set the first responder of the window holding AppView, to AppView.

It is done by adding 

[self.window makeFirstResponder:self.ViewController.view];

At the end of the applicationDidFinishLaunching method in AppDelegate.m

So the AppDelegate.m looks like:

 //  
 // AppDelegate.m  
 // KeyAndMouseEventExample  
 //  
 // Created by Christian Henrik Reich on 01/01/14.  
 //  
 #import "AppDelegate.h"  
 #include "AppViewController.h"  
 @interface AppDelegate()  
 @property (nonatomic,strong) IBOutlet AppViewController *ViewController;  
 @end  
 @implementation AppDelegate  
 - (void)applicationDidFinishLaunching:(NSNotification *)aNotification  
 {  
   self.ViewController = [[AppViewController alloc] initWithNibName:@"AppViewController"bundle:nil];  
   [self.window.contentView addSubview:self.ViewController.view];  
   self.ViewController.view.frame = ((NSView*)self.window.contentView).bounds;  
   [self.window makeFirstResponder:self.ViewController.view];  
 }  
 @end  

That's it. For more about the NSResponderClass see https://developer.apple.com/library/mac/documentation/cocoa/reference/applicationkit/classes/NSResponder_Class/Reference/Reference.html#//apple_ref/occ/instm/NSResponder/keyDown:

Enjoy and happy developing.

Cheers,

Christian Henrik Reich