Stats Database Schema for SQLite3
From Hopmod Wiki
Contents |
Tables
Games
This table contains all games played on the server.
| Column | Data Type | Description |
|---|---|---|
| id | INTEGER PRIMARY KEY | Unique id assigned to each game |
| datetime | INTEGER DEFAULT 0 | Timestamp of the game |
| gamemode | TEXT DEFAULT "" | The gamemode for this game |
| mapname | TEXT DEFAULT "" | The name of the map played |
| duration | INTEGER DEFAULT 0 | The duration of the game in minutes |
| finished | BOOLEAN DEFAULT 0 | Whether the game was completed. A game is finished when time left reaches 0 and intermission occurs, a reason for this not happening is players pass a vote to change the map/mode. |
| players | INTEGER DEFAULT 0 | Count of the total number of players that played in the game; includes players that disconnected early. |
| bots | INTEGER DEFAULT 0 | Count of the total number of bots that played in the game. |
Teams
This table contains a list of all the teams used in all the games. The game_id indicates which game this teams statistics are from.
| Column | Data Type | Description |
|---|---|---|
| id | INTEGER PRIMARY KEY | Unique id assigned to each team |
| game_id | INTEGER REFERENCES games(id) | Link team record to game record; game_id is a valid game.id key. |
| name | TEXT | The name of this team |
| score | INTEGER DEFAULT 0 | The score of the team |
| win | BOOLEAN DEFAULT 0 | Whether this team was the winner |
| draw | BOOLEAN DEFAULT 0 | Whether the game was a draw (all teams had equal scores) |
Players
This table contains player statistics on a per game basis. The game_id indicates which game this players statistics are from.
| Column | Data Type | Description |
|---|---|---|
| id | INTEGER PRIMARY KEY | Unique id assigned to each player |
| game_id | INTEGER REFERENCES games(id) | Link player record to game record; game_id is a valid game.id key. |
| team_id | INTEGER REFERENCES teams(id) DEFAULT 0 | Link player record to team record; a value of 0 means no link which is useful for singles-based game modes, for team-based gamemodes, team_id is a valid team.id key. |
| name | TEXT | The player's name |
| ipaddr | TEXT | The player's IP address |
| country | TEXT | The player's country (a two-letter country code) |
| score | INTEGER DEFAULT 0 | The player's score. Score calculation: +1 per frag, -1 per suicide and -1 per teamkill. |
| frags | INTEGER DEFAULT 0 | Frags count (includes teamkills) |
| deaths | INTEGER DEFAULT 0 | Deaths count (includes suicides) |
| suicides | INTEGER DEFAULT 0 | Suicides count |
| teamkills | INTEGER DEFAULT 0 | Teamkills count |
| hits | INTEGER DEFAULT 0 | Count the number of shots that hit |
| shots | INTEGER DEFAULT 0 | Count the number of shots |
| damage | INTEGER DEFAULT 0 | The amount of damage delivered |
| damagewasted | INTEGER DEFAULT 0 | The amount of damage wasted |
| timeplayed | INTEGER DEFAULT 0 | The amount of time played; a value in seconds. |
| finished | BOOLEAN DEFAULT 0 | Whether the player finished the game, as in they were still in the game when the end of game event occurred. One reason for a player not finishing a game is they disconnected before the game ended. |
| win | BOOLEAN DEFAULT 0 | The meaning of winner depends on the type of game mode. For singles-based (ffa, instagib etc) game modes, the winning player is the player who has the most frags (exact sort order is: frags DESC, deaths ASC, accuracy DESC, ping ASC). For team-based game modes, winning means being on the winning team, all players on the winning team have a win value of 1, and those on the losing team a win value of 0. |
| rank | INTEGER DEFAULT 0 | Rank position, based on frag count. For team game modes, rank position is calculated within the team. It's possible for players to have joint rank if they have an equal number of frags. |
| botskill | INTEGER DEFAULT 0 | -1 value means unspecified skill (randomly chosen), 0 value means human player (not a bot) and values between 1 and 101 are determined bot skill levels. |
Playertotals
This table contains aggregate data collected against all games for a given player. This table is automatically updated with triggers when data is inserted, updated or deleted into the players table.
| Column | Data Type | Description |
|---|---|---|
| id | INTEGER PRIMARY KEY | Unique id assigned to each player |
| name | TEXT UNIQUE | The player's name |
| ipaddr | TEXT | The player's IP Address |
| country | TEXT | The player's country (a two-letter country code) |
| first_game | TEXT | Timestamp of the first recorded game |
| last_game | TEXT | Timestamp of the last recorded game |
| frags | INTEGER DEFAULT 0 | Count number of frags for all games recorded |
| deaths | INTEGER DEFAULT 0 | Count number of deaths for all games recorded |
| suicides | INTEGER DEFAULT 0 | Count number of suicides for all games recorded |
| teamkills | INTEGER DEFAULT 0 | Count number of teamkills for all games recorded |
| hits | INTEGER DEFAULT 0 | Count number of shot hits for all games recorded |
| shots | INTEGER DEFAULT 0 | Count number of shots for all games recorded |
| damage | INTEGER DEFAULT 0 | Sum up damage delivered for all games recorded |
| damagewasted | INTEGER DEFAULT 0 | Sum up damage wasted for all games recorded |
| wins | INTEGER DEFAULT 0 | Count of the number of winning games |
| losses | INTEGER DEFAULT 0 | Count of the number of losing games |
| games | INTEGER DEFAULT 0 | The total number of games played |
| withdraws | INTEGER DEFAULT 0 | The number of times this player left before the game was finished |
| timeplayed | INTEGER DEFAULT 0 | The total time played across all games; a value in seconds. |
