Software development is often seen as a "man vs machine" endeavour. In reality, it's still people who do the work, a work that often has a clear artistic component. It is work we do with others, for others. And here are some of my thoughts on that

Thursday, December 13, 2012

I showed my implementation of Conway's Game of Life in SQL to a few people, a co-worker pondered if it could be done without the stored procedure. I gave it some thought, and came up with an implementation that relies on views. I used views for the simple reason that it made things easier to see and understand. If a query can be written using views, the it can be written as straight SQL, but the results are generally uglier, messier, and can often be unreadable. Since I'm using MySQL, I realize how bad views can be when it comes to performance, but since this is a proof of concept, and a toy anyway, I'm not too worried. I honestly doubt any real-life application would ever require me to implement the Game of Life in SQL!

I started as I had earlier, by defining the table as holding all the live cells for each generation.

Then, I created a view to represent all the candidate position on the grids where we might find live cells on the next generation. We can only find cells where there are cells for the current generation, and in all 8 neighbour positions of those cells:

Next, I counted the number of live cells in the 3x3 square around each candidate. This isn't quite the same as counting the neighbours, since it includes the current cell in the count. We will need to take that into account in a bit.

From the previous post, the rules of the game can be reduced to: a cell is alive in the next generation if

it has 3 neighbours

it has 2 neighbours and was alive in the current generation

Let's write this as a truth table:

Live?

Neighbours

Alive in the next generation

yes

less than 2

no

yes

2

YES

yes

3

YES

yes

more than 3

no

no

less than 3

no

no

3

YES

no

more than 3

no

If we count the cell in the neighbour count, we change our truth table:

Live?

# cells in 3x3 square

Alive in the next generation

yes

less than 3

no

yes

3

YES

yes

4

YES

yes

more than 4

no

no

less than 3

no

no

3

YES

no

more than 3

no

A cell will be alive in the next generation if it's alive and the number of live cells in the square is 3 or 4, or if the cell is dead and there are 3 cells in the square. Let's rewrite the rules... again.

A cell will be alive in the next generation if

The 3x3 square has exactly 3 cells

The 3x3 square has exactly 4 cells and the current cell is alive.

So, we'll include a cell in the next generation according to these rules. We can tell if a cell is alive by doing a left outer join on the cell table: