The Slonik's Gambit

12.2024 / Category: How To / Tags: conference | pgconf.eu | sql help

In the world of databases, we’re often consumed by abstracts. Yet, every so often, an opportunity arises to connect the cerebral with the tangible, the technical with the human. This year at PGConf.eu in Athens, among talks about the latest PostgreSQL features, a different kind of challenge was brewing — not one of data migration intricacies or deployment flexibility, but one of kings, queens, and strategic battles fought on 64 squares. The idea was simple: host a chess tournament to bring database enthusiasts together in a new way.

Table of Contents

The CfP committee and Cybertec’s Marketing and Sales team saw an opportunity not just to play but to build. And so, the journey of developing a chess tournament application using CYPEX began.

It all started over a conversation with people who are key figures in the PostgreSQL community. What if we built an application to manage the tournament? Something quick, efficient, and, in the spirit of the conference, powered by PostgreSQL. With the conference dates upcoming, we had limited time to develop a functional application. Traditional development methods were out of the question. This is where CYPEX came into play — a tool known for its rapid application development capabilities atop PostgreSQL databases.

Every application starts with understanding the requirements.

Core Features

Player Management:
- App administrator was responsible for creating and managing player profiles.
- A unique identifier for each participant.
Game Management:
- Recording game results with opponent selection.
- Enforcing the rule that each pair plays only once.
Scoring System:
- Calculating scores based on wins, draws, and losses.
- Implementing resistance points to account for the strength of opponents.
Leaderboard:
- Real-time rankings are displayed throughout the tournament.
- Highlight not just top performers but also participants in prime-numbered positions — a quirky twist.

Setting Up the Database Schema for the chess tournament

First, we established the core tables in our chess_tournament schema:

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()
);

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()

);

Our initial focus was on getting the core functionality up and running for the tournament, but we also designed the database schema with future enhancements in mind. Just as a chess player thinks several moves ahead, anticipating potential developments, we structured our schema to be scalable and adaptable. Beyond the essential tables I previously described, the schema includes several additional tables intended to support more advanced features:

To ensure fairness and competitiveness, we needed a method to calculate player scores and rankings, especially considering that not all participants would play the same number of games.
Derk helped me a lot with ideas on how to write advanced SQL to cover these needs.

For example, to aggregate the scores for each player, we created the “Direct scores for players” view:

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;

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;

Here, CTE “scores” combines the results from games where the player played as both white and black. The final SELECT aggregates the total score and total games for each player, consolidating scores from both perspectives.

Introducing Resistance Points

While direct scores indicate how many points a player has earned, they don’t account for the strength of their opponents. The idea is that defeating strong players should be more rewarding than defeating weaker ones. Resistance points help differentiate players who may have the same direct score but faced opponents of varying skill levels.

We calculated resistance points using the following view:

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;

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;

Final Ranking

With both direct scores and resistance points calculated, we can determine the players' rankings. The “Current Ranking” view brings it all together:

 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)));

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)));

After defining the tables and composing complex queries — it was time to let the application ferment — transforming our carefully selected components into a full-bodied user experience.

Low-Code, High Efficiency

Using CYPEX was an exercise in focusing on logic over syntax. To create a more flexible user interface, I started building a fully functional dashboard to manage the chess tournament.

One of CYPEX’s features is its real-time layout editor - a WYSIWYG (What You See Is What You Get) tool that feels so natural to use. It took me just a few moments to drag and drop the necessary tables and forms onto the page. The real magic happened once all the elements were connected to the right data sources.
CYPEX’s built-in JavaScript custom expressions acted like the synapses in a neural network, allowing for complete functional collaboration between elements. Much like the cascading effect of signals in the brain leading to complex behaviors — changing one input causes the entire system to respond. So, when a form is submitted, other components of the dashboard update accordingly, enabling dynamic data synchronization.

Finally, the application was ready. Seeing the players interact, compete, and enjoy the experience was a pleasant reward. The success of the tournament inspired us to consider future enhancements to the application, so more features are planned to be integrated. This tournament demonstrated how, with a bit of creativity and the right technology, we could bring people together again and again.

We’d love to have more people join the chess tournament next time. It’s a fun way to connect with others at the conference. We’ve set up a Telegram Channel for future tournaments at the PostgreSQL Conferences. Feel free to join us there to stay updated!

The Slonik's Gambit

Every application starts with understanding the requirements.

Core Features

Setting Up the Database Schema for the chess tournament

Introducing Resistance Points

Final Ranking

Low-Code, High Efficiency

Leave a Reply Cancel reply

Svitlana Lytvynenko

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts