Geert JM Vanderkelen

A chessboard in MySQL: make your moves

A chessboard in MySQL: make your moves

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:

BEGIN;
UPDATE chessboard SET e='♙' WHERE x = 4;
UPDATE chessboard SET e='' WHERE x = 2;
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:

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,'','','','','','','',''),<br />  (3,'','','','','','','',''),
  (2,'♙','♙','♙','♙','♙','♙','♙','♙'),
  (1,'♖','♘','♗','♕','♔','♗','♘','♖');

2 thoughts on “A chessboard in MySQL: make your moves

  1. Gerry Narvaja

    Wow! When I read the 1st article I didn't give too much thought, but now that you showed how simple it is to implement a move a ton of ideas started flooding my mind:

    1. What about a trigger to record the moves including the timestamp?
    2. What about a 'game' table where you can store moves for each game to have an AI engine analyze them?
    3. What about an AI engine as stored procedure?
    4. What about hosting a massive DB on Amazon RDS against which people could store their games and make it available to the world to implement different chess playing systems using it?

    I propose creating an OpenChess community to build on your proposal … and I haven't even started with the EOY drinking yet.

    Happy New Year!

  2. Roland Bouman

    Hi!

    nice. But why not do:

    UPDATE chessboard
    SET e = CASE x
    WHEN 2 THEN ''
    WHEN 4 THEN '♙'
    END
    WHERE x IN (4, 2)

    YMMV, but I think this is simpler

Leave a Reply

Your email address will not be published. Required fields are marked *

8 + = 13

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>