At PGConf.eu in Athens this year, alongside the usual PostgreSQL feature talks, attendees could sign up for something different: a chess tournament. The motivating idea was to give database people a way to connect that wasn't another migration war story or a deployment flexibility debate.
The tournament needed software. With the conference dates approaching, there was no room for a conventional build cycle, so the organizers turned to CYPEX, a rapid application development tool that runs on top of PostgreSQL, to get something functional in time.
What the application had to do
- Player Management
- Game Management
- Scoring System
- Leaderboard
Schema design
The core tables live in a chess_tournament schema:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE SCHEMA chess_tournament; SET search_path TO chess_tournament; CREATE TABLE t_player ( id serial PRIMARY KEY, first_name text NOT NULL, last_name text NOT NULL, created_at timestamp with time zone default now(), updated_at timestamp with time zone default now() ); CREATE TABLE t_tournament ( id serial PRIMARY KEY, tournament_name text NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, format text DEFAULT 'Full freedom', created_at timestamp with time zone default now(), updated_at timestamp with time zone default now() ); |
The schema was also built with later features in mind, in the same way a chess player plans several moves ahead. Alongside the essential tables, it carries additional tables for more advanced functionality:
Scoring and ranking
Because participants would not all play the same number of games, scoring and ranking needed more than a simple win count. Derk contributed ideas for the advanced SQL this required.
Aggregating each player's score starts with a "Direct scores for players" view:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
WITH scores AS ( SELECT p.id, sum(w.result) AS score, count(1) AS total FROM (chess_tournament.t_player p JOIN chess_tournament.t_match w ON ((p.id = w.player1_id))) GROUP BY p.id UNION ALL SELECT p.id, sum(((1)::double precision - b.result)) AS score, count(1) AS total FROM (chess_tournament.t_player p JOIN chess_tournament.t_match b ON ((p.id = b.player2_id))) GROUP BY p.id ) SELECT scores.id AS player, sum(scores.score) AS score, sum(scores.total) AS total FROM scores GROUP BY scores.id ORDER BY scores.id; |
The scores CTE merges results from games where the player appeared as white and as black; the outer SELECT then totals score and games per player, combining both perspectives.
Direct scores alone reward beating weak opponents as much as strong ones. Resistance points correct for that, separating players who share a direct score but faced opponents of differing strength:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
WITH r AS ( SELECT player.id, sum((scores.score / (scores.total)::double precision)) AS score FROM ((chess_tournament.t_player player JOIN chess_tournament.t_match game ON ((game.player1_id = player.id))) JOIN cypex_generated.direct_scores_for_players scores ON ((game.player2_id = scores.player))) GROUP BY player.id UNION ALL SELECT player.id, sum((scores.score / (scores.total)::double precision)) AS score FROM ((chess_tournament.t_player player JOIN chess_tournament.t_match game ON ((game.player2_id = player.id))) JOIN cypex_generated.direct_scores_for_players scores ON ((game.player1_id = scores.player))) GROUP BY player.id ) SELECT r.id AS player, sum(r.score) AS resistance FROM r GROUP BY r.id; |
With both figures available, the "Current Ranking" view produces the standings:
|
1 2 3 4 5 6 7 8 9 |
SELECT RANK() OVER (ORDER BY s.score DESC, r.resistance DESC) AS rank, s.player, p.first_name, p.last_name, s.score, r.resistance FROM ((cypex_generated.direct_scores_for_players s JOIN cypex_generated.resistance_points r USING (player)) JOIN chess_tournament.t_player p ON ((s.player = p.id))); |
Building the dashboard with low-code
The CYPEX work was mostly about logic rather than syntax. A dashboard for managing the tournament was built as a fully functional interface:
CYPEX provides a real-time WYSIWYG layout editor; dragging the needed tables and forms onto a page took only moments, and the functional part arrived once each element was wired to its data source. Custom JavaScript expressions built into CYPEX connect the elements to one another, so a change in one input propagates through the dashboard — submitting a form updates the other components and keeps the data synchronized.
The finished application was used live at the conference, and watching players compete with it prompted plans for further features. A Telegram channel has been set up for future tournaments at PostgreSQL conferences.
