FEP 002 - FujiNetWIFI/fujinet-firmware GitHub Wiki
| Name | Value |
|---|---|
| PEP ID | 002 |
| Title | GameEvents and Leaderboard in Lobby |
| Author | Andrew Diller |
| Status | Draft |
| Type | Informational |
| Created | 2025-05-09 |
| Version | 1.0 |
| Input | EricC, RogerS |
This document defines a standard approach for an enhancement to the existing lobby server service by introducing a persistent leaderboard system based on multiplayer game outcomes. Specifically, it involves the creation of a new gameResult table within the service’s SQLite database to store detailed player performance data—including game identifiers, server information, player names, win status, and player type. In parallel, a new HTML page titled “leaderboard” will be implemented to present aggregated player statistics in two formats: (1) a global ranking of players by total wins, and (2) per-server listings of the top 10 winning players. This addition aims to provide persistent tracking and visibility into player achievements across all hosted games.
As GameServer (GS) events flow into the LobbyServer (LS) it processes them.
It will look for a new key, gameResult in the body of the POST.
If gameResult exists:
- create a new ID for this game (UUID)
- loop over the array of players in the gameResult
- for each player create a new row in the gameResult db table for
- gameID, gameName (game), gameServer (server), playerName (name), playerWinner (winner), playerType (type), datetime (current)
else:
- continue with normal processing
- bounce the entire payload to any evtaddr hosts specified when the server was instantiated
ProposedTable: gameResult
Top Players by Wins (All Games)
SELECT playerName, COUNT(*) AS wins
FROM gameResult
WHERE playerWinner = 1 AND playerType = 'human'
GROUP BY playerName
ORDER BY wins DESC;
Top 10 Players Per Server
SELECT gameServer, playerName, COUNT(*) AS wins
FROM gameResult
WHERE playerWinner = 1 AND playerType = 'human'
GROUP BY gameServer, playerName
HAVING wins > 0
ORDER BY gameServer, wins DESC;
For top 10 per server, SQLite doesn’t support ROW_NUMBER() until 3.25+, so this workaround uses a correlated subquery
SELECT *
FROM (
SELECT gameServer, playerName, COUNT(*) AS wins
FROM gameResult
WHERE playerWinner = 1 AND playerType = 'human'
GROUP BY gameServer, playerName
)
WHERE (
SELECT COUNT(*) FROM gameResult AS gr
WHERE gr.playerWinner = 1 AND gr.playerType = 'human'
AND gr.gameServer = gameResult.gameServer
AND (
SELECT COUNT(*) FROM gameResult
WHERE playerWinner = 1 AND playerType = 'human'
AND gameServer = gr.gameServer
AND playerName = gr.playerName
) <= 10
);
Sample Page
<!DOCTYPE html>
<html>
<head>
<title>Game Leaderboard</title>
<style>
body { font-family: sans-serif; margin: 20px; }
h2 { margin-top: 40px; }
table { border-collapse: collapse; width: 100%; margin-bottom: 40px; }
th, td { border: 1px solid #ccc; padding: 8px; text-align: left; }
th { background-color: #f8f8f8; }
</style>
</head>
<body>
<h1>Game Leaderboards</h1>
<h2>Top Players (All Games)</h2>
<table>
<thead>
<tr><th>Rank</th><th>Player Name</th><th>Wins</th></tr>
</thead>
<tbody>
<!-- Example Row -->
<tr><td>1</td><td>andyXEL</td><td>42</td></tr>
<!-- Replace with dynamic server-side or JS rendering -->
</tbody>
</table>
<h2>Top 10 Players Per Server</h2>
<table>
<thead>
<tr><th>Server</th><th>Player Name</th><th>Wins</th></tr>
</thead>
<tbody>
<!-- Example Rows -->
<tr><td>AI Room - 2 bots</td><td>andyXEL</td><td>25</td></tr>
<tr><td>AI Room - 2 bots</td><td>frank</td><td>18</td></tr>
<!-- Replace dynamically -->
</tbody>
</table>
</body>
</html>
