Software developer blog

SQL on steroids

Few weeks ago we held an internal Coderetreat at Emarsys for all programmers within the company. When the business intelligence team heard about it they started to joke around, that they should drop by and implement Conway's Game of Life in SQL. I'm not much of an SQL wizard, but I rose to the challenge. First I tried it in MySQL, since that's the dialect I have the most experience with:

CREATE TABLE generations (`id` INT UNSIGNED, `x` BIGINT, `y` BIGINT);
INSERT INTO generations VALUES (0, 0, -1), (0, 1, 0), (0, -1, 1), (0, 0, 1), (0, 1, 1);
 
CREATE TABLE offsets (`x` BIGINT, `y` BIGINT);
INSERT INTO offsets VALUES (-1,-1),(-1, 0),(-1,1),(0,-1),(0,0),(0,1),(1,-1),(1,0),(1,1);
 
INSERT INTO generations 
  SELECT g.id+1 id, g.x+o.x x, g.y+o.y y
    FROM generations g 
      INNER JOIN offsets o 
    WHERE g.id=(SELECT MAX(id) FROM generations)
    GROUP BY g.x+o.x, g.y+o.y
      HAVING (MAX(o.x=0 AND o.y=0) = 1 AND COUNT(1) - 1 BETWEEN 2 AND 3)
        OR (MAX(o.x=0 AND o.y=0) = 0 AND COUNT(1) = 3);

It works, and it's not even that complicated. First we join the last population with an offset table to generate the set of living cells and their neighbors. Grouping by the cell coordinates plus the offsets we can get all necessary information. The expression max(o.x=0 and o.y=0) is one exactly if the cell was alive in the previous generation, otherwise it's zero. Using that we can count the number of neighbors too: count(1)-max(o.x=0 and o.y=0). The having clause utilizes that knowledge to filter the grouped rows to contain only the cells in the next generation.

@ //

How to refactor your legacy

A lot has been said about good and bad design decisions all over the web, and I hardly think I could add anything new to the list of code smells. Yet they still keep popping up in every single piece of legacy code I come across. My theory is that many of these patterns do not simply arise as the evil doings of a single bad programer, but more as a result of collaboration between brilliant programers, who just fail to realize the problem early. If you look at it that way it becomes apparent that bad designs are inevitable in the long run, and so eventually they need to be fixed.

Or do they? Well... it all depends of course. There is nothing more harmful than putting countless hours of work into changing code for the sole purpose of aesthetics. I'm an idealist at heart, and I'd love to spend all the time I can find on prettifying the existing code base before I ever change behaviour, but the reality is that beautiful code won't earn you a single penny unless it delivers business value. On the other hand we all know that bad code can hurt us big time: not only makes it expensive to add behaviour, it also gets hard just to keep things from falling apart.

Refactoring legacy

Refactoring code without tests feels like tightrope walking for the first time.

Are you confused yet? Should you refactor or even rewrite that piece of ugly spaghetti code tomorrow morning, or not? Let's say that by default you should never touch legacy code that works as expected. (It gives me enormous pain to even think that this is a good idea...)  Accepting the premise here is a list of the most important exceptions to this rule, when you should most definitely become obsessed with cleaning your code.

@ //