A chessboard in MySQL

Something to keep you warm during cold winter nights, or cool during hot summer days: a chessboard in MySQL!

Note: You should see chess pieces here below. If not, you’re not watching it using UTF-8, or get yourself a good browser!

CREATE TABLE `chessboard` (
  `x` tinyint unsigned NOT NULL,
  `a` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `b` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `c` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `d` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `e` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `f` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `g` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `h` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟')
) DEFAULT CHARSET=utf8;

Populating it with Python using MySQL Connector/Python (only piece of script shown):

def create_board(db):
    c = db.cursor()
    table = """CREATE TABLE chessboard (
        x tinyint unsigned not null,
        a ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        b ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        c ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        d ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        e ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        f ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        g ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        h ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F')
        ) default charset='utf8'"""
    c.execute("DROP TABLE IF EXISTS chessboard")
    c.execute(table)

def set_start_position(db):
    c = db.cursor()
    # Numbers correspondent to the ENUM fields
    wdata = { 'x' : 1,
        'a': 4, 'b': 6, 'c': 5,
        'd': 3, 'e': 2,
        'f': 5, 'g': 6, 'h': 4}
    bdata = { 'x' : 8,
        'a': 10, 'b': 12, 'c': 11,
        'd': 9, 'e': 8,
        'f': 11, 'g': 12, 'h': 10}

    stmt = """UPDATE chessboard SET a=%(a)s,b=%(b)s,c=%(c)s,
        d=%(d)s,e=%(e)s,f=%(f)s,g=%(g)s,h=%(h)s WHERE x = %(x)s"""
    c.executemany(stmt, [wdata,bdata])

    stmt = """UPDATE chessboard SET a=7,b=7,c=7,d=7,e=7,f=7,g=7,h=7
        WHERE x = 2"""
    c.execute(stmt)
    stmt = """UPDATE chessboard SET a=13,b=13,c=13,d=13,e=13,f=13,g=13,h=13
        WHERE x = 7"""
    c.execute(stmt)

Now you can select the chessboard:

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

The possibilities.. oh yeah!

Comments

Roland Bouman
Cool!

but…

I just wish someone would fix the command line client, because its ASCII art is clearly confused by multi-byte characters (not just with chessboards :p)
qu1j0t3
Pretty, but I would argue that the schema is not well normalised for actual chess :)

ducks
dveeden
The MySQL client still behaves badly with characters different than 0-9A-Za-z..

mysql> SELECT LENGTH('Cafe');
+----------------+
| LENGTH('Cafe') |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('Café');
+-----------------+
| LENGTH('Café') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)

http://bugs.mysql.com/bug.php?id=32271
dveeden
The output in the previous post is not very clear… just try it in your mysql commandline.

The layout of the column name of the second command gets confused as it reserves space for 5 characters but only draws 4.
Geert JM Vanderkelen
Maybe folks should use a good terminal? Or set it to UTF-8? :-)

In MacOS's Terminal:

mysql> set names 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE chessboard (
-> x tinyint unsigned NOT NULL,
-> a enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
-> b enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
-> c enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
-> d enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
-> e enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
-> f enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
-> g enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
-> h enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟')
-> ) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.68 sec)
Dgn
Cute :)
Roland Bouman
“Maybe folks should use a good terminal? Or set it to UTF-8? :-)”

so if you do that, and do the

select * from chessboard;

query, is the ascii art renderd like it should? In your post, the pipe are misaligned just like in my terminal…
Geert JM Vanderkelen
@Roland:

To bad. On MacOS's Terminal it shows all correctly.
Which makes me wonder whether the problem is really in the MySQL CLI.