Geert JM Vanderkelen

A chessboard in MySQL

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!

8 thoughts on “A chessboard in MySQL

  1. 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)

  2. 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

  3. 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.

  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)

  5. 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…

  6. 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.