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
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)
ducks
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
The layout of the column name of the second command gets confused as it reserves space for 5 characters but only draws 4.
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)
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…
To bad. On MacOS's Terminal it shows all correctly.
Which makes me wonder whether the problem is really in the MySQL CLI.