Since version 2.13, GunGame has optional built-in SQL stats storage. It's generally much faster and more efficient than the flat-file alternative. If you wish to use it, simply compile
gungame.sma with the
#define SQL line near the top of the file uncommented. If you download the "SQL stats" package, this has already been done for you. (To be clear: download
only the "flat-file stats" package or
only the "SQL stats" package, depending on the method of stats storage you wish to use.)
You can go here to convert your old flat-file stats files into SQL queries which you can use to populate your stats database.
When you run GunGame with SQL for the first time, it will automatically create any tables that it needs. (If you previously used the old GunGame SQL plugin, please delete or rename your old gg_stats table, or make sure that gungame.amxx uses a different table name.) SQL stats will be pruned just like flat-file stats based on gg_stats_prune. A list of all the SQL-specific cvars can be seen below.
- SQL Cvars
- gg_sql_host (default: "localhost")
- The SQL hostname to use for connecting.
- gg_sql_user (default: "root")
- The SQL user to use for connecting.
- gg_sql_pass (default: "")
- The SQL password to use for connecting.
- gg_sql_db (default: "gungame")
- The database name to use.
- gg_sql_table (default: "gg_stats")
- The table name where the regular stats are stored.
- gg_sql_streak_table (default: "gg_streaks")
- The table name where the win streaks are stored.
- gg_sql_winmotd_table (default: "gg_winmotd")
- The table name where the MOTD player information is stored.
Below are descriptions of all of the tables used by GunGame.
gg_sql_table holds most of the important stats information for all of the players -- it could have a lot of rows.
REFRESH_TIMESTAMP_ON_JOIN is a #define near the top of
gungame.sma that is turned off by default for
gungame.amxx but is on by default for
gungame_with_sql.amxx.
| gg_sql_table |
| Field Name | Type | Description |
| authid | varchar(31) | The player's authid/IP/name (depending on gg_stats_ip). |
| wins | smallint(6) | The player's wins (overall or non-teamplay, depending on gg_stats_split). |
| name | varchar(31) | The player's most recent name upon completion of a round of GunGame. |
| timestamp | int(10) | The server's UNIX timestamp the last time this was "refreshed." If REFRESH_TIMESTAMP_ON_JOIN
was used, it's refreshed whenever the player joins the server. Otherwise, it's refreshed whenever they finish a round of GunGame. |
| points | mediumint(9) | The player's points (overall or non-teamplay, depending on gg_stats_split). |
| streak | smallint(6) | The player's record win streak (overall or non-teamplay, depending on gg_stats_split). |
| wins_tp | smallint(6) | The player's teamplay-specific wins (if using gg_stats_split). |
| points_tp | mediumint(9) | The player's teamplay-specific points (if using gg_stats_split). |
| streak_tp | smallint(6) | The player's teamplay-specific win streak (if using gg_stats_split). |
| serverip | varchar(63) | The server IP, with port (e.g. 123.456.789.123:45678). |
The gg_sql_streak_table is used to keep track of the all-time record streaks and also the current running streaks of individual players. It theoretically shouldn't ever have more than 66 rows for a given server IP.
| gg_sql_streak_table |
| Field Name | Type | Description |
| type | enum('0C', '0R', '1C', '1R') | The type of entry that this row is. A leading 0 means this row is for overall or non-teamplay, and a leading 1 means this is for teamplay-specific (all according to gg_stats_split). A trailing C means this row is for keeping track of a current running streak, while a trailing R means this is a record streak. |
| authid | varchar(31) | The player's authid/IP/name (depending on gg_stats_ip). |
| streak | smallint(6) | The value of the win streak in question (currently running if C-type, or record if R-type). |
| name | varchar(31) | The player's name when this streak was last updated (C-type) or when this record was made (R-type). |
| timestamp | int(10) | The server's UNIX timestamp when this streak was last updated (C-type) or when this record was made (R-type). |
| serverip | varchar(63) | The server IP, with port (e.g. 123.456.789.123:45678). |
The gg_sql_winmotd_table is used to temporarily store the information of each player at the end of each GunGame round for the purpose of using it on your own custom win MOTD page.
This table is only updated if gg_winner_motd is set to 2 or a URL address. It shouldn't ever have more than 33 rows for a given server IP. The wins, points, streak, and stats position fields refer to all-around stats (gg_stats_split is 0), non-teamplay stats (gg_stats_split is 1 but gg_teamplay is 0), or teamplay-specific stats (gg_stats_split is 1 and gg_teamplay is 1).
Additionally, a special "information row" with the id set to 0 is also inserted that contains the values of miscellaneous pertinent information amongst the fields.
| gg_sql_winmotd_table |
| Field Name | Type | Description |
| id | tinyint(4) | The player's unique in-game ID (1-32). If 0, this is the information row. |
| authid | varchar(31) | The player's authid/IP/name (depending on gg_stats_ip). If information row, blank. |
| name | varchar(31) | The player's name. If information row, the value of amx_nextmap. |
| team | tinyint(4) | The player's team (0-3). If information row, the value of gg_stats_mode. |
| level | tinyint(4) | The player's level. If information row, the value of gg_stats_split. |
| weapon | varchar(23) | The name of the player's level weapon. If information row, blank. |
| flags | tinyint(4) | Bitwise flags for special things. 1 = player was on winning team; 2 = player was on losing team; 4 = player was the final killer (if on winning team) or the final victim (if on losing team); 8 = player just set a new server record streak. If information row, the value of gg_teamplay. |
| wins | smallint(6) | The player's new total wins for this game mode. If information row, the value of gg_teamplay_timeratio. |
| points | mediumint(9) | The number of points the player just earned. If information row, the in-game ID of the final killer (1-32). |
| new_points | mediumint(9) | The player's new total points for this game mode. If information row, the in-game ID of the final victim (0-32). |
| record_streak | smallint(6) | The player's previous record streak for this game mode. If information row, 1 if the final victim was disconnected, or 0 if not. |
| current_streak | smallint(6) | The player's current running streak for this game mode. If information row, the winning team (1 or 2). |
| stats_position | smallint(6) | The player's new stats position for this game mode. If information row, the losing team (1 or 2). |
| teamtime_winning | smallint(6) | The time in seconds that the player spent on the winning team. If information row, the value of gg_map_iterations. |
| teamtime_losing | smallint(6) | The time in seconds that the player spent on the losing team. If information row, the number of GunGame rounds left before changing maps (a negative number means infinity). |
| timestamp | int(10) | The server's UNIX timestamp at the end of the GunGame round. |
| serverip | varchar(63) | The server IP, with port (e.g. 123.456.789.123:45678). |
Additionally, if using a custom gg_winner_motd web page, the page is queried by each user like so:
URL_ADDRESS?i=PLAYER_ID&l=PLAYER_LANG
where
URL_ADDRESS is the value of
gg_winner_motd,
PLAYER_ID is the individual player's unique in-game ID (
1-
32), and
PLAYER_LANG is the player's two-digit language code (e.g.
en). This way you can figure out which player is requesting the current page.
The same thing happens when using gg_top10_ppp as a custom web page.
