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..
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
DECLARE @Bit BIT
SET @Bit = 0
IF EXISTS (SELECT 1
FROM GamesBoard WITH (NOLOCK)
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)
END = 1
AND LEFT(Piece,1) = CASE @Side WHEN 'W' THEN 'B' ELSE 'W' END)
SET @Bit = 1
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...
| posted on Tuesday, October 07, 2003 5:39 PM