September 21, 2015 at 9:48 pm
Comments posted to this topic are about the item Implementing John Conway's Game of Life in Microsoft SQL Server
September 22, 2015 at 2:13 am
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.
September 22, 2015 at 3:35 am
Thank you for the comment and thanks for sharing the article! I don't know how I could have missed it!
September 22, 2015 at 4:20 am
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@BoardSizeSMALLINT-- width and height
DECLARE@LooperSMALLINT-- for initialising
DECLARE@xSMALLINT
DECLARE@ySMALLINT
DECLARE@ContainsMerkelBIT
DECLARE@NeighboursSMALLINT
DECLARE@InitialiseBIT-- if true create the table and insert merkles
DECLARE@IterationSMALLINT-- for the game itself
DECLARE@MaxIterationsSMALLINT
DECLARE@VisualiserVARCHAR(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
WHERECellId = @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))
)
ANDContainsMerkle = 1
-- apply the rules and save
IF (@ContainsMerkel = 0 AND @Neighbours = 3)
BEGIN
UPDATE#tmpBoard
SETContainsMerkleNextRound = 1
WHERECellId = @Looper
END
ELSE IF (@ContainsMerkel = 1 AND @Neighbours < 2)
BEGIN
UPDATE#tmpBoard
SETContainsMerkleNextRound = 0
WHERECellId = @Looper
END
ELSE IF (@ContainsMerkel = 1 AND @Neighbours > 3)
BEGIN
UPDATE#tmpBoard
SETContainsMerkleNextRound = 0
WHERECellId = @Looper
END
ELSE IF (@ContainsMerkel = 1)
BEGIN
UPDATE#tmpBoard
SETContainsMerkleNextRound = 1
WHERECellId = @Looper
END
ELSE
BEGIN
UPDATE#tmpBoard
SETContainsMerkleNextRound = 0
WHERECellId = @Looper
END
SET@Looper = @Looper + 1
CONTINUE
END
UPDATE#tmpBoard
SETContainsMerkle = ContainsMerkleNextRound,
ContainsMerkleNextRound = 0
SET@Iteration = @Iteration + 1
CONTINUE
END
September 22, 2015 at 7:12 am
Thanks for posting this Paul. It's great fun!
Terry
September 22, 2015 at 7:15 am
A good fun article, thanks.
September 22, 2015 at 9:42 am
Have you tried implementing a Genetic Algorithm in SQL?
September 22, 2015 at 10:46 am
Thank you for this! Implementing Life was always my project of choice when I learned a new computer language.
September 23, 2015 at 2:58 am
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply