Implementing John Conway's Game of Life in Microsoft SQL Server

  • Comments posted to this topic are about the item Implementing John Conway's Game of Life in Microsoft SQL Server

  • This article has 3 possible solutions, 1 that uses geometry, 1 that uses set based queries and 1 that uses Hekaton in-memory tables and procedures. It also has a nice way to visualize the patterns in a spatial grid - http://www.sqlservercentral.com/articles/Spatial/126016/

    The objective was to use cellular automation and the game of life as a framework for a stress testing application.

    SQL Server 2008/2012 - CPU (geometry intersects method) , IO (set based queries)

    SQL Server 2014 - Hekaton in-memory tables and procedures.

    Just run the setup script and call the procedures in multiple concurrent sessions to generate a load. It's simple, no client tools to install, virtually no learning curve, mathematically precise, repeatable, and underrated as a tool in my opinion.

  • Thank you for the comment and thanks for sharing the article! I don't know how I could have missed it!

  • Reminds me, I had a go at this in SQL once in a bored afternoon...my version doesn't show the generation number though. If you run in SSMS with results set to text it's formatted so that you can sort of watch it animate. Parameters at the top allow you to try different configs. Feel free to play and improve!

    -- Game Of Life in SQL

    DECLARE @BoardSize SMALLINT -- width and height

    DECLARE @Looper SMALLINT -- for initialising

    DECLARE @x SMALLINT

    DECLARE @y SMALLINT

    DECLARE @ContainsMerkel BIT

    DECLARE @Neighbours SMALLINT

    DECLARE @Initialise BIT -- if true create the table and insert merkles

    DECLARE @Iteration SMALLINT -- for the game itself

    DECLARE @MaxIterations SMALLINT

    DECLARE @Visualiser VARCHAR(MAX) -- text to visualise the board

    SET @BoardSize = 16

    SET @Initialise = 1

    SET @MaxIterations = 100

    IF (@Initialise = 1)

    BEGIN

    DROP TABLE #tmpBoard

    CREATE TABLE #tmpBoard (CellId SMALLINT, x SMALLINT, y SMALLINT, ContainsMerkle BIT, ContainsMerkleNextRound BIT)

    SET @Looper = 1

    WHILE (@Looper <= (@BoardSize * @BoardSize))

    BEGIN

    INSERT #tmpBoard

    SELECT @Looper, CASE WHEN (@Looper % @BoardSize) = 0 THEN 8 ELSE (@Looper % @BoardSize) END, FLOOR((@Looper - 1) / @BoardSize) + 1, ROUND(RAND(), 0), 0 -- randomises merkle distribution

    SET @Looper = @Looper + 1

    CONTINUE

    END

    END

    SET NOCOUNT ON

    --SELECT * FROM #tmpBoard

    -- Begin game iteration

    SET @Iteration = 1

    WHILE (@Iteration <= @MaxIterations)

    BEGIN

    -- loop to generate visualisation first

    SET @Visualiser = ''

    SET @Looper = 1

    WHILE (@Looper <= (@BoardSize * @BoardSize))

    BEGIN

    SELECT @Visualiser = @Visualiser +

    CASE WHEN ContainsMerkle = 1 THEN 'O' ELSE '.' END + ' '

    FROM #tmpBoard

    WHERE CellId = @Looper

    IF ((CONVERT(FLOAT, @Looper) / CONVERT(FLOAT, @BoardSize)) = FLOOR(@Looper / @BoardSize))

    SET @Visualiser = @Visualiser + CHAR(10)

    SET @Looper = @Looper + 1

    CONTINUE

    END

    SELECT 'Iteration ' + CONVERT(VARCHAR, @Iteration) + CHAR(10) + CHAR(10) + @Visualiser

    -- loop again to apply rules

    SET @Looper = 1

    WHILE (@Looper <= (@BoardSize * @BoardSize))

    BEGIN

    SELECT @x = x, @y = y, @ContainsMerkel = ContainsMerkle FROM #tmpBoard WHERE CellId = @Looper

    --SELECT @Looper, @x, @y

    -- find neighbours

    SELECT @Neighbours = COUNT(*)

    FROM #tmpBoard

    WHERE (

    (ABS(x - @x) = 1 AND ABS(y - @y) IN (0,1))

    OR (ABS(y - @y) = 1 AND ABS(x - @x) IN (0,1))

    )

    AND ContainsMerkle = 1

    -- apply the rules and save

    IF (@ContainsMerkel = 0 AND @Neighbours = 3)

    BEGIN

    UPDATE #tmpBoard

    SET ContainsMerkleNextRound = 1

    WHERE CellId = @Looper

    END

    ELSE IF (@ContainsMerkel = 1 AND @Neighbours < 2)

    BEGIN

    UPDATE #tmpBoard

    SET ContainsMerkleNextRound = 0

    WHERE CellId = @Looper

    END

    ELSE IF (@ContainsMerkel = 1 AND @Neighbours > 3)

    BEGIN

    UPDATE #tmpBoard

    SET ContainsMerkleNextRound = 0

    WHERE CellId = @Looper

    END

    ELSE IF (@ContainsMerkel = 1)

    BEGIN

    UPDATE #tmpBoard

    SET ContainsMerkleNextRound = 1

    WHERE CellId = @Looper

    END

    ELSE

    BEGIN

    UPDATE #tmpBoard

    SET ContainsMerkleNextRound = 0

    WHERE CellId = @Looper

    END

    SET @Looper = @Looper + 1

    CONTINUE

    END

    UPDATE #tmpBoard

    SET ContainsMerkle = ContainsMerkleNextRound,

    ContainsMerkleNextRound = 0

    SET @Iteration = @Iteration + 1

    CONTINUE

    END

  • Thanks for posting this Paul. It's great fun!

    Terry

  • A good fun article, thanks.

  • Have you tried implementing a Genetic Algorithm in SQL?

  • Thank you for this! Implementing Life was always my project of choice when I learned a new computer language.

  • Regarding the COALESCE(SUM(...)):

    COALESCE - in contrast to ISNULL - will internal replaced by an CASE WHEN <xxx> IS NOT NULL THEN <xxx> ELSE <yyyy>

    This means, that <xxx> will be calculated twice. This is no problem, if <xxx> is a static value or table field, but if <xxx> is a function call or an aggregat it will cost twice CPU / IO

    -> your query would run faster, if you would use ISNULL instead

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply