This one is a little fun for a Friday: implementing Conway’s Game of Life in SQL Anywhere 12. The game of life is a simple zero-player game where an infinite plane of cellular automatons live and die according to some simple rules. In this post, we will create a full version of this game (with GUI), in a single SQL statement. Taken from Wikipedia , the rules of Conway’s Game of Life are:

The universe of the Game of Life is an infinite two-dimensional orthogonal grid of square cells, each of which is in one of two possible states, live or dead. Every cell interacts with its eight neighbours, which are the cells that are horizontally, vertically, or diagonally adjacent. At each step in time, the following transitions occur:

- Any live cell with fewer than two live neighbours dies, as if caused by under-population.
- Any live cell with two or three live neighbours lives on to the next generation.
- Any live cell with more than three live neighbours dies, as if by overcrowding.
- Any dead cell with exactly three live neighbours becomes a live cell, as if by reproduction.

The initial pattern constitutes the seed of the system. The first generation is created by applying the above rules simultaneously to every cell in the seed—births and deaths occur simultaneously, and the discrete moment at which this happens is sometimes called a tick (in other words, each generation is a pure function of the preceding one). The rules continue to be applied repeatedly to create further generations.

It is unlikely that SQL would be your first choice for building an instance of the game of life. However, using some new features in SQL Anywhere 12 it can be accomplished remarkably easily. This implementation uses these new features of SQL Anywhere 12:

- Spatial support to hold the polygons that make up the world
- Spatial viewer inside of Interactive SQL to view the current state of the world
- Spatial functions to provide some mutations of the world
- Select from DML, enabling you to use the results of a DML statment as a derived table

The first thing to do is create a table to hold the state of the world. Trying to keep this as simple as possible, we will represent each cell as a 1×1 polygon. Note that the table is created with `SRID=0`

, which means we are using a Cartesian plane.

```
CREATE TABLE cells (
id INTEGER PRIMARY KEY DEFAULT AUTOINCREMENT,
position ST_POLYGON(SRID=0)
)
```

Additionally, we will create a simple helper procedure to insert live cells into the world. This procedure will be used to seed the world.

```
CREATE PROCEDURE addCell(IN r INTEGER, IN c INTEGER)
BEGIN
INSERT INTO cells(position) VALUES (NEW ST_POLYGON(NEW ST_POINT(r, c), NEW ST_POINT(r + 1, c + 1)));
END;
```

Now for the real workhorse, the statement that generates the next iteration of the world. This statement is actually pulling triple duty. Not only is it selecting the next iteration of the world, but it is also deleting the previous iteration of the world **and** inserting the new iteration back into the same table. This is possible by using the new select from DMLsupport in SQL Anywhere 12.

```
-- Select all the rows that were inserted into this iteration so that they
-- can be displayed in the spatial viewer
SELECT * FROM (
-- Insert the results of this iteration back into cells
-- Note that the previous generation of the cells has already been cleared
-- out by the DELETE statment below
INSERT INTO cells(position)
SELECT (c1.position).ST_Affine(1,0,0, 0,1,0, 0,0,1, r.row_num, c.row_num, 0) p
-- the row generators are used to build a 3x3 matrix that will be used by the
-- ST_Affine function to generate all the spaces surrounding a live cell
FROM sa_rowgenerator(-1,1,1) r,
sa_rowgenerator(-1,1,1) c,
-- the following line deletes the contents of cells
-- and makes it available as derived table 'c1'
(DELETE FROM cells) REFERENCING (OLD as c1)
GROUP BY p
HAVING -- cells that have 2 or 3 neighbours and are alive continue to live
(COUNT(*) IN (3,4) AND SUM(IF (r.row_num = 0 AND c.row_num = 0) THEN 1 ELSE 0 ENDIF) = 1)
-- cells that have exactly 3 neighbours and are not alive are 'born'
OR (COUNT(*) = 3 AND SUM(IF (r.row_num = 0 AND c.row_num = 0) THEN 1 ELSE 0 ENDIF) = 0))
REFERENCING (FINAL as n1)
```

Now all we need to do is seed the world with some cells using the `addCell`

procedure, execute the above statement in iSQL’s spatial viewer (*Tools * -> **Spatial Viewer**), and watch the results. I have included a time-lapse screen capture of the iterations of a Gosper’s Glider Gun . The seeding code for this can be found here.

Special thanks to Dan Farrar for his assistance in constructing the SQL for this post.

AnonymousAnonymousAnonymous