The Game of Life

At the start of this year I took 2 months off to enjoy my family and farm.

During a particularly rainy period (precious precious rain) I decided to sharpen up my SQL skills by building games into SQL.

First of the bat was Poker, then BlackJack. 2 fairly easy games to implement in SQL, with the only trick being randomisation of the cards.

Next on the list was the classic Game Of Life. Although not really a game, it does make for a great exercise. If you don't know what it is have a search on google...

So here we go... A Users table The standard Numbers Table The Life table And 2 extra tables that give us interesting starting patterns. Patterns & PatternsCells

3 UDF's.. 1 for Births, 1 for Deaths and 1 for Active Cells. 3 stored procedures

1 for building the empty Life game with the designated grid size. 1 for building the Life Game from a pattern 1 for each iteration.

And finally a UDF for printing the results in QA...

If anybody is interested in the script I will post it in the comments.

But probably the hardest game I have attempted was Chess

The goal here was to enforce every rule into the Database. I followed the international chess rules and was able to implement them all except for the 3 minute "think" window which I felt was not applicable for a stateless chess game (Web).

In the end the User was exposed to 2 procedures.. upGames_New and upMove.

There are 2 User defined data types Piece and Square with all the necessary check contraints attached to enforce those domains.

A mapping UDF that converts Chess notation to X,Y notation although not mandatory does make the coding easier.

But the most difficulty in this project was the detecting and verifying Check.

All possible opposition pieces must be checked to see if the King is under attack.

Here is the UDF that I eventually settled for..

CREATE FUNCTION IsSquareInCheck (@WhiteUser VARCHAR(25), @BlackUser VARCHAR(25), @Game# SMALLINT, @Side CHAR(1), @ToSquare CHAR(2))


--Return value DECLARE @Bit BIT --Default Bit SET @Bit = 0


WHERE (WhiteUser =@WhiteUser AND BlackUser =@BlackUser AND Game# = @Game#)


WHEN 'C' THEN dbo.CastleValidMove(@WhiteUser, @BlackUser, @Game#, Square, @ToSquare)

WHEN 'B' THEN dbo.BishopValidMove(@WhiteUser, @BlackUser, @Game#, Square, @ToSquare)

WHEN 'Q' THEN dbo.QueenValidMove(@WhiteUser, @BlackUser, @Game#, Square, @ToSquare)

WHEN 'P' THEN dbo.PawnValidAttack(Piece, Square, @ToSquare)

WHEN 'N' THEN dbo.KnightValidMove(Square,@ToSquare) ELSE 0 END = 1

AND LEFT(Piece,1) = CASE @Side WHEN 'W' THEN 'B' ELSE 'W' END) SET @Bit = 1 RETURN @Bit END

It basically says...

"For all the pieces in the opposing team, check if they pose a threat to that square (@ToSquare). Each piece has its own UDF which calculates if the move is going to be valid and wether its attack path cross the @ToSquare.

The most coding was down on the Pawns. For a start they behave differently wether in attack or not as well as the inital double move..

Again if anybody is interested I will post the full code in...

Print | posted on Tuesday, October 07, 2003 5:39 PM


# re: The Game of Life

left by Jeff at 10/7/2003 10:35 PM Gravatar
I actually played with this idea myself .. i had a set-based routine which calculated all possible moves for the board ... let me know if you're interested in checking it out (probably clunky but it might be interesting to you) and I can email it to you (if I can find it).

- Jeff

# re: The Game of Life

left by DavidM at 10/8/2003 10:07 AM Gravatar
Ship it in Jeff...

# re: The Game of Life

left by Shannon J Hager at 10/8/2003 11:49 AM Gravatar
I would love to see the full code for these SQL games.

# re: The Game of Life

left by Anon at 10/8/2003 1:14 PM Gravatar

# re: The Game of Life

left by Stoad at 10/16/2003 10:21 PM Gravatar
It must be posted in the Script Library!!

# re: The Game of Life

left by TimG at 10/22/2003 5:12 PM Gravatar
So you are a "castle guy" not a "rook guy". Interesting.

# re: The Game of Life

left by jeanette at 8/7/2005 6:40 PM Gravatar
To send me a code of the game of life

# re: The Game of Life

left by jeanette at 8/7/2005 6:41 PM Gravatar
to send me a code of a game of life in C
Comments have been closed on this topic.