Geert JM Vanderkelen

Playing chess within MySQL? Over the network? In the .. cloud? Yes!

This is a follow-up post of my ‘A chessboard in MySQL’ where we create and populate a chessboard. But pieces need to move, and a few wondered how. Easy!

As an example, white openes with 1.e4:

  mysql> BEGIN;  mysql> UPDATE chessboard SET e='♙' WHERE x = 4;  mysql> UPDATE chessboard SET e='' WHERE x = 2;  mysql> COMMIT;

Pretty obvious. Now lets put it in a stored procedure (source included in post) so the next move is easier on the fingers and more fun to play. Lets do a 1…e5:

  mysql> CALL move_piece('e','7','e',5);

The result is the following:

mysql> SELECT * FROM chessboard;+---+------+------+------+------+------+------+------+------+| x | a    | b    | c    | d    | e    | f    | g    | h    |+---+------+------+------+------+------+------+------+------+| 8 | ♜    | ♞    | ♝    | ♛    | ♚    | ♝    | ♞    | ♜    || 7 | ♟    | ♟    | ♟    | ♟    |      | ♟    | ♟    | ♟    || 6 |      |      |      |      |      |      |      |      || 5 |      |      |      |      | ♟    |      |      |      || 4 |      |      |      |      | ♙    |      |      |      || 3 |      |      |      |      |      |      |      |      || 2 | ♙    | ♙    | ♙    | ♙    |      | ♙    | ♙    | ♙    || 1 | ♖    | ♘    | ♗    | ♕    | ♔    | ♗    | ♘    | ♖    |+---+------+------+------+------+------+------+------+------+

Here is the stored procedure. It’s very, very basic, and of course, one can add much more!

DROP PROCEDURE IF EXISTS move_piece;delimiter //CREATE PROCEDURE move_piece(    psrcCol CHAR(1),    psrcRow TINYINT,    pdstCol CHAR(1),    pdstRow TINYINT)BEGIN    SET @srcCol = psrcCol;    SET @srcRow = psrcRow;    SET @dstCol = pdstCol;    SET @dstRow = pdstRow;    SET @piece = 0;    SET @blank = '';

    -- Get the piece we are moving    SET @stmt = CONCAT('SELECT ',@srcCol,        '+0 INTO @piece FROM chessboard WHERE x = ?');    PREPARE preStmt FROM @stmt;    EXECUTE preStmt USING @srcRow;    DEALLOCATE PREPARE preStmt;

    IF ((@piece > 1 AND @piece <= 14) AND @piece is not NULL)    THEN        -- Move the piece        SET @stmt = CONCAT('UPDATE chessboard SET ',            @dstCol,'=? WHERE x = ?');        PREPARE preStmt FROM @stmt;        EXECUTE preStmt USING @piece,@dstRow;        DEALLOCATE PREPARE preStmt;        SET @stmt = CONCAT('UPDATE chessboard SET ',            @srcCol,'=? WHERE x = ?');        PREPARE preStmt FROM @stmt;        EXECUTE preStmt USING @blank,@srcRow;        DEALLOCATE PREPARE preStmt;    ELSE        SELECT "No piece found at given position." AS Error;    END IF;END;//

Some thoughts for future expansion:

• You could save the moves in a separate table to record time it took.
• Implement some other movement notation.
• Lock a player after a move.
• Build some other stored routines to populate and reset the chessboard.
• Have multiple chessboard tables.
• Event scheduler can be used to implement the non-human player!

Anyway, this was all about fun and Unicode testing. There will be probably no follow-up on this post. If somebody is crazy enough to actually implement a chess game in MySQL: awesome!

(Disclaimer: I am not a chess player.)

UPDATE 2009-12-31: Here is the insert statement for populating the chessboard:

INSERT INTO `chessboard` VALUES  (8,'♜','♞','♝','♛','♚','♝','♞','♜'),  (7,'♟','♟','♟','♟','♟','♟','♟','♟'),  (6,'','','','','','','',''),  (5,'','','','','','','',''),  (4,'','','','','','','',''),  (3,'','','','','','','',''),  (2,'♙','♙','♙','♙','♙','♙','♙','♙'),  (1,'♖','♘','♗','♕','♔','♗','♘','♖');