Playing SQL Chess in Player vs Database (PvD) Mode | Hacker Noon

June 23rd 2020

Author profile picture

@BassaganasJordi Bassagañas

Hi there! How are you today? I blog about technology, the Internet, SEO, programming tips, and more.

Twitter social icongithub social icon

Lately I’ve been wrapping my head around implementing a basic AI model that would allow to play chess with the help of a powerful, relational database running under the hood.

If you’ve heard before about the PGN format then you’ll probably know it stands for Portable Game Notation, which is a standard to represent chess games with text files.

The intent of the definition and propagation of PGN is to facilitate the sharing of public domain chess game data among chessplayers (both organic and otherwise), publishers, and computer chess researchers throughout the world.

So what if we had an SQL database with millions and millions of games of chess masters of all time?

Since chess games are in the public domain, yesterday I downloaded a bunch from several websites for free and ended up building a MySQL database containing a

games

table as described next.

mysql> describe games;
+-------------------+---------------+------+-----+---------+-------+
| Field             | Type          | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| Event             | char(64)      | YES  |     | NULL    |       |
| Site              | char(64)      | YES  |     | NULL    |       |
| Date              | char(16)      | YES  |     | NULL    |       |
| Round             | char(8)       | YES  |     | NULL    |       |
| White             | char(32)      | YES  |     | NULL    |       |
| Black             | char(32)      | YES  |     | NULL    |       |
| Result            | char(8)       | YES  |     | NULL    |       |
| FICSGamesDBGameNo | char(16)      | YES  |     | NULL    |       |
| WhiteTitle        | char(16)      | YES  |     | NULL    |       |
| BlackTitle        | char(16)      | YES  |     | NULL    |       |
| WhiteElo          | char(8)       | YES  |     | NULL    |       |
| BlackElo          | char(8)       | YES  |     | NULL    |       |
| WhiteUSCF         | char(8)       | YES  |     | NULL    |       |
| BlackUSCF         | char(8)       | YES  |     | NULL    |       |
| WhiteNA           | char(8)       | YES  |     | NULL    |       |
| BlackNA           | char(8)       | YES  |     | NULL    |       |
| WhiteType         | char(16)      | YES  |     | NULL    |       |
| BlackType         | char(16)      | YES  |     | NULL    |       |
| EventDate         | char(16)      | YES  |     | NULL    |       |
| EventSponsor      | char(32)      | YES  |     | NULL    |       |
| Section           | char(16)      | YES  |     | NULL    |       |
| Stage             | char(32)      | YES  |     | NULL    |       |
| Board             | char(8)       | YES  |     | NULL    |       |
| Opening           | char(32)      | YES  |     | NULL    |       |
| Variation         | char(32)      | YES  |     | NULL    |       |
| SubVariation      | char(32)      | YES  |     | NULL    |       |
| ECO               | char(32)      | YES  |     | NULL    |       |
| NIC               | char(32)      | YES  |     | NULL    |       |
| Time              | char(16)      | YES  |     | NULL    |       |
| TimeControl       | char(16)      | YES  |     | NULL    |       |
| UTCTime           | char(16)      | YES  |     | NULL    |       |
| UTCDate           | char(16)      | YES  |     | NULL    |       |
| WhiteClock        | char(16)      | YES  |     | NULL    |       |
| BlackClock        | char(16)      | YES  |     | NULL    |       |
| SetUp             | char(8)       | YES  |     | NULL    |       |
| FEN               | char(64)      | YES  |     | NULL    |       |
| Termination       | char(32)      | YES  |     | NULL    |       |
| Annotator         | char(32)      | YES  |     | NULL    |       |
| Mode              | char(16)      | YES  |     | NULL    |       |
| PlyCount          | char(4)       | YES  |     | NULL    |       |
| WhiteRD           | char(8)       | YES  |     | NULL    |       |
| BlackRD           | char(8)       | YES  |     | NULL    |       |
| movetext          | varchar(3072) | YES  |     | NULL    |       |
+-------------------+---------------+------+-----+---------+-------+
43 rows in set (0.01 sec)

It took me around 25 minutes on an average mainstream laptop to filter, validate and populate this table with about 400,000 games.

In a nutshell, the PGN Chess database is created this way:
php cli/db-create.php
This will remove the current PGN Chess database and the data will be lost.
Do you want to proceed? (Y/N): y
bash/load.sh
This will load the PGN files stored in the data/prod folder. Are you sure to continue? (y|n) y
Good! This is a valid PGN file. 3179 games were inserted into the database.
Loading games for 15 s...
Good! This is a valid PGN file. 1313 games were inserted into the database.
Loading games for 20 s...
Good! This is a valid PGN file. 1900 games were inserted into the database.
Loading games for 28 s...
Good! This is a valid PGN file. 776 games were inserted into the database.
Loading games for 30 s...
Good! This is a valid PGN file. 1661 games were inserted into the database.
Loading games for 36 s...
Good! This is a valid PGN file. 2180 games were inserted into the database.
Loading games for 46 s...
Character encoding detected: . Needs to be UTF-8.
Loading games for 46 s...
Good! This is a valid PGN file. 3828 games were inserted into the database.
Loading games for 64 s...
I know, most probably, the loading time may be improved in multiple ways, however keep in mind that a validation process is run to make sure all games are syntactically valid, as per the unit tests described below.
<?php

namespace PGNChessTestsUnitPGNValidate;

use PGNChessPGNSymbol;
use PGNChessPGNValidate;
use PGNChessTestsAbstractUnitTestCase;

class MovetextTest extends AbstractUnitTestCase
{
    public static $validData = [
        '1.d4 Nf6 2.Nf3 e6 3.c4 Bb4+ 4.Nbd2 O-O 5.a3 Be7 6.e4 d6 7.Bd3 c5',
        '1.e4 Nf6 2.e5 Nd5 3.d4 d6 4.Nf3 dxe5 5.Nxe5 c6 6.Be2 Bf5 7.c3 Nd7',
        '1.e4 c5 2.Nf3 Nc6 3.d4 cxd4 4.Nxd4 Nf6 5.Nc3 e5 6.Ndb5 d6 7.Bg5 a6 8.Na3',
        '1.d4 Nf6 2.c4 e6 3.Nc3 Bb4 4.e3 O-O 5.a3 Bxc3+ 6.bxc3 b6 7.Bd3 Bb7 8.f3 c5',
        '1.Nf3 Nf6 2.c4 c5 3.g3 b6 4.Bg2 Bb7 5.O-O e6 6.Nc3 a6 7.d4 cxd4 8.Qxd4 d6',
    ];

    /**
     * @dataProvider validData
     * @test
     */
    public function valid($movetext)
    {
        $this->assertEquals($movetext, Validate::movetext($movetext));
    }

    /**
     * @dataProvider commentsRemovedData
     * @test
     */
    public function comments_removed($expected, $movetext)
    {
        $this->assertEquals($expected, Validate::movetext($movetext));
    }

    /**
     * @dataProvider tooManySpacesData
     * @test
     */
    public function too_many_spaces($expected, $movetext)
    {
        $this->assertEquals($expected, Validate::movetext($movetext));
    }

    /**
     * @dataProvider wrongNumbersData
     * @test
     */
    public function wrong_numbers($movetext)
    {
        $this->assertFalse(Validate::movetext($movetext));
    }

    /**
     * @dataProvider invalidMovesData
     * @test
     */
    public function invalid_moves($movetext)
    {
        $this->assertFalse(Validate::movetext($movetext));
    }

    public function validData()
    {
        return [
            self::$validData,
        ];
    }

    public function commentsRemovedData()
    {
        return [
            [
                self::$validData[0], '{This is foo} 1.d4 Nf6 2.Nf3 e6 3.c4 Bb4+ 4.Nbd2 O-O 5.a3 Be7 6.e4 d6 7.Bd3 c5',
                self::$validData[1], '1.e4 Nf6 {This is foo} 2.e5 Nd5 3.d4 d6 4.Nf3 dxe5 5.Nxe5 c6 6.Be2 Bf5 7.c3 Nd7',
                self::$validData[2], '1.e4 c5 2.Nf3 {This is foo} Nc6 3.d4 cxd4 4.Nxd4 Nf6 5.Nc3 e5 6.Ndb5 d6 7.Bg5 a6 8.Na3',
                self::$validData[3], '1.d4 Nf6 2.c4 e6 3.Nc3 Bb4 4.e3 O-O 5.a3 Bxc3+ 6.bxc3 b6 7.Bd3 Bb7 8.f3 c5 {This is foo}',
                self::$validData[4], '1.Nf3 Nf6 2.c4 c5 3.g3 b6 4.Bg2 Bb7 5.O-O e6 6.Nc3 a6 7.d4 cxd4 8.Qxd4 {This is foo} d6',
            ],
        ];
    }

    public function tooManySpacesData()
    {
        return [
            [
                self::$validData[0], '1  .  d4    Nf6 2.Nf3 e6 3.c4    Bb4+ 4.Nbd2 O-O 5.a3 Be7 6.e4 d6 7.Bd3 c5',
                self::$validData[1], '1.e4 Nf6 2.   e5 Nd5 3.d4 d6 4.Nf3 dxe5 5.Nxe5 c6 6.   Be2 Bf5 7.c3 Nd7',
                self::$validData[2], '1.e4  c5   2.Nf3   Nc6 3.d4     cxd4 4   .  Nxd4 Nf6 5.Nc3 e5 6.Ndb5 d6 7.Bg5 a6 8.Na3',
                self::$validData[3], '1.d4 Nf6 2.c4 e6 3.Nc3 Bb4 4.e3 O-O 5.a3 Bxc3+    6.bxc3 b6   7.Bd3   Bb7   8.f3   c5',
                self::$validData[4], '1.Nf3   Nf6 2.c4   c5  3.g3  b6  4.Bg2  Bb7  5.O-O e6 6.Nc3 a6 7.d4  cxd4  8.Qxd4  d6',
            ],
        ];
    }

    public function wrongNumbersData()
    {
        return [
            [
                '2.d4 Nf6 2.Nf3 e6 3.c4 Bb4+ 4.Nbd2 O-O 5.a3 Be7 6.e4 d6 7.Bd3 c5',
                '1.e4 Nf6 2.e5 Nd5 4.d4 d6 4.Nf3 dxe5 5.Nxe5 c6 6.Be2 Bf5 7.c3 Nd7',
                'e4 c5 2.Nf3 Nc6 3.d4 cxd4 4.Nxd4 Nf6 5.Nc3 e5 6.Ndb5 d6 7.Bg5 a6 8.Na3',
                '1.d4 Nf6 2.c4 e6 3.Nc3 Bb4 23.e3 O-O 5.a3 Bxc3+ 6.bxc3 b6 7.Bd3 Bb7 8.f3 c5',
                '1.Nf3 Nf6 2.c4 c5 3.g3 b6 4.Bg2 Bb7 5.O-O e6 6.Nc3 a6 7.d4 cxd4 10.Qxd4 d6',
            ],
        ];
    }

    public function invalidMovesData()
    {
        return [
            [
                '1.d4 Nf6 2.Nf3 FOO 3.c4 Bb4+ 4.Nbd2 O-O 5.a3 Be7 6.e4 d6 7.Bd3 c5',
                '1.e4 Nf6 2.e5 Nd5 3.d4 d6 4.Nf3 dxe5 5.BAR c6 6.Be2 Bf5 7.c3 Nd7',
                '1.e4 c5 2.Nf3 Nc6 3.FOO cxd4 4.Nxd4 Nf6 5.Nc3 e5 6.Ndb5 d6 7.Bg5 a6 8.Na3',
                '1.d4 Nf6 2.c4 e6 3.Nc3 Bb4 4.e3 O-O 5.a3 Bxc3+ 6.bxc3 b6 7.Bd3 Bb7 8.f3 BAR',
                '1.Nf3 Nf6 2.c4 c5 3.g3 BAR 4.Bg2 FOO 5.O-O e6 6.FOOBAR 7.d4 cxd4 8.Qxd4 d6',
            ],
        ];
    }
}

Put this way, the validated games are a good starting point for further processing requiring preconditions, also if we were to train a model to learn chess the data set available would be more accurate than otherwise.

Once the SQL database is ready, let’s play chess in Player vs Database mode without using any e-brain for the time being. A PGN Chess Server needs to be started first in order to listen to Telnet connections from clients.
php cli/t-server.php
Welcome to PGN Chess Server
Commands available:
/captures Gets the pieces captured by both players.
/help Provides information on the commands available.
/history The current game's history.
/ischeck Finds out if the game is in check.
/ismate Finds out if the game is over.
/metadata Metadata of the current game.
/piece {"position":"square"} Gets a piece by its position on the board. The "position" parameter is mandatory.
/pieces {"color":["w","b"]} Gets the pieces on the board by color. The "color" parameter is mandatory.
/play {"color":["w","b"],"pgn":"move"} Plays a chess move on the board. All parameters are mandatory.
/quit Quits a game.
/start {"mode":["pva","pvd","pvp","pvt"],"color":["w","b"]} Starts a new game. The "color" parameter is not required in pvt (player vs themselves) mode.
/status The current game status.

Listening to commands...
/start pvd w
{"message":"Game started in pvd mode."}
/play w e4
{"I":"w e4","d":"b e5"}
/play w Nf3
{"I":"w Nf3","d":"b Nc6"}
/play w Bb5
{"I":"w Bb5","d":"b Nf6"}
/metadata
{"metadata":{"Event":"20th European Teams","Site":"Reykjavik ISL","Date":"2015.11.16","Round":"4.5","White":"Adams,Mi","Black":"Aronian,L","Result":"1-0","WhiteElo":"2744","BlackElo":"2781","ECO":"C67","movetext":"1.e4 e5 2.Nf3 Nc6 3.Bb5 Nf6 4.O-O Nxe4 5.d4 Nd6 6.Bxc6 dxc6 7.dxe5 Nf5 8.Qxd8+ Kxd8 9.h3 Ke8 10.Nc3 h5 11.Ne2 Be7 12.Bg5 Be6 13.Nf4 Bd5 14.Nxd5 cxd5 15.Rad1 c6 16.Rfe1 h4 17.Rd3 Rh5 18.Bxe7 Kxe7 19.Red1 Rd8 20.c4 d4 21.b4 b6 22.Kf1 c5 23.bxc5 bxc5 24.Ra3 Rd7 25.Ne1 Rh6 26.Nd3 Rc6 27.Rb1 Rdc7 28.Nf4 Rd7 29.Rb8 g5 30.Nd5+ Ke6 31.Re8+ Ne7 32.Ke2 Rb6 33.Kd3 Rb2 34.Ra6+ Rb6 35.Nxb6 axb6 36.Rxb6+ Kxe5 37.Rc6 Kf5 38.Rxc5+ Kf6 39.Rb8 Ng6 40.Rb6+ Ke7 41.Rxg5 Nf4+ 42.Ke4 1-0"}}
/play w Ng5
{"I":"w Ng5","d":null,"message":"Mmm, sorry. There are no chess moves left in the database."}
$result = Pdo::getInstance()
                    ->query("SELECT * FROM games WHERE movetext LIKE '$movetext%' ORDER BY RAND() LIMIT 1")
                    ->fetch(PDO::FETCH_ASSOC);

As you can see, chances are that the course of action will inevitably get to a point where there aren’t any more chess moves available left in the database, but there, in the middle game, is where the AI comes to the rescue.

In the example above one might conclude Ng5 is a bad move because no chess master made that one at an early stage of the game while developing their pieces in the opening.

/play w Ng5
{"I":"w Ng5","d":null,"message":"Mmm, sorry. There are no chess moves left in the database."}

And this is all for the time being, thanks so much for reading. If you enjoyed today’s post, you might also want to discuss what algorithms are best suited in this particular case after Ng5.

Author profile picture

Read my stories

Hi there! How are you today? I blog about technology, the Internet, SEO, programming tips, and more.

Tags

The Noonification banner

Subscribe to get your daily round-up of top tech stories!

read original article here