Chess in SQL(dbpro.app) |
Chess in SQL(dbpro.app) |
I had the idea of building a working Chess game using purely SQL.
The chess framing is a bit of a trojan horse, honestly. The actual point is that SQL can represent any stateful 2D grid. Calendars, heatmaps, seating plans, game of life. The schema is always the same: two coordinate columns and a value. The pivot query doesn't change.
A few people have asked why not just use a 64-char string or an array type. You could! But you lose all the relational goodness: joins, aggregations, filtering by piece type. SELECT COUNT(*) FROM board WHERE piece = '♙' just works.
“Pivot tables”: I often have a list of dates, then categories that I want to become columns. SQL can’t do that so there is a technique of spreading values to each column then doing a MAX of each value per date. It is clumsy and verbose but works perfectly… as long as categories are known in advance and fixed. There should be an SQL instruction to pivot those rows into columns.
Example: SELECT date, category, metric; -- I want to show 1 row per date only, with each category as a column.
``` SELECT date,
MAX( CASE category WHEN ‘page_hits’ THEN metric END ) as “Page Hits”,
MAX( CASE category WHEN ‘user_count’ THEN metric END ) as “User Count”
GROUP BY date;
^ Without MAX and GROUP BY: 2026-03-30 Value1 NULL 2026-03-30 NULL Value2 2026-03-31 Value1 NULL (etc) The MAX just merges all rows of the same date. ```
SQL should just have an instruction like: SELECT date, PIVOT(category, metric); to display as many columns as categories.
This thought should be extended for more than 2 dimensions.
R*Trees are what you are looking for. The sqlite implementation supports up to 5 dimensions.
$ sqlite :memory:
create table t (product,revenue, year);
insert into t values ('a',10,2020),('b',14,2020),('c',24,2020),('a',20,2021),('b',24,2021),('c',34,2021);
select product,sum(revenue) filter (where year=2020) as '2020',sum(revenue) filter (where year=2021) as '2021' from t group by product;Just FYI your statement for the checkmate state in the opera game appears to be incorrect
OP's example, for reference, was:
SELECT rank,
MAX(CASE WHEN file = 1 THEN COALESCE(piece, '·') END) AS a,
MAX(CASE WHEN file = 2 THEN COALESCE(piece, '·') END) AS b,
MAX(CASE WHEN file = 3 THEN COALESCE(piece, '·') END) AS c,
MAX(CASE WHEN file = 4 THEN COALESCE(piece, '·') END) AS d,
The broader pattern is, in successive CTEs:
1. Group by Date w/ Aggregates
2. "Melt" to [optional groupings +] month + measure_name + value tuples:
select group, month, '# Bookings' as measure_name, num_bookings as value from base_data
UNION ALL
select group, month, 'Revenue', total_revenue from base_data
MAX(CASE WHEN month = '2019-01' THEN value END) AS "2019-01",
MAX(CASE WHEN month = '2019-02' THEN value END) AS "2019-02",
MAX(CASE WHEN month = '2019-03' THEN value END) AS "2019-03",
And while the column breakouts are painful to type out by hand - they're very amenable to LLM generation!
I'm interested in whether others are oversensitive or I'm not sensitive enough... :)
I'm still working on an idea to have a "state" check to know when checkmate happens but that's gonna take a wee bit more time.
But, the idea is very novel and very thought provoking and has provided me with a refreshing distraction from the boring problem I was working on before seeing your post.
At the scales these games operate, enterprisey oracle clusters start to look like a pretty good solution if you don't already have some custom tech stack that perfectly solves the problem.
I'd never heard of dbpro.app until now - and this article is just so awesome.
Nice job!
i once published a "translation" of the Opera Game (chess annotation as a literary device) after reading too much Lautremont so it is disgusting
> Let's build it.
Cool concept; but every blog post sounds exactly the same nowadays. I mean it’s like they are all written by the exact same person /s
> Not "store chess moves in a database." Not "track game state in a table." Actually render a chess board. With pieces. That you can move around. In your browser. Using nothing but SELECT, UPDATE, and a bit of creative thinking.
Please, just write like a person.
"crosstab ( source_sql text, category_sql text ) → setof record"
https://www.postgresql.org/docs/current/tablefunc.html
VIA https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgres... as a current googlable reference/guide
The game servers are all C++ and don't use stored procedures for general gameplay management. They do handle inventory management so that item duping is generally not possible, and complex things like cross-server character transfer use stored procedures.