Database

MG-Contest uses oxmysql. Import install/mg_contest.sql once before the first start. All tables use InnoDB and utf8mb4.

Tables

Table	Stores
mg_contest_clans	Clans: name, color, logo, leader, ELO, wins, losses, win streak, kills, deaths, flags, contests played.
mg_contest_clan_members	Clan members, their rank and personal stats. Deleted with the clan.
mg_contest_player_stats	Lifetime stats per character, kept even without a clan.
mg_contest_invites	Pending, accepted and declined clan invites.
mg_contest_bans	Contest bans from the admin panel, with reason and expiry.
mg_contest_history	Finished contests: zone, winner, participants (JSON), duration and MVP.
mg_contest_player_settings	Tablet settings: wallpaper and notifications.
mg_contest_battlepass_progress	Player XP per season.
mg_contest_battlepass_clan_progress	Clan XP per season.
mg_contest_battlepass_claims	Claimed tiers per player per season.

Players are linked by citizenid. A character can be in one clan at a time. Deleting a clan also deletes its members and invites.

Useful queries

Top 10 clans

SELECT name, elo, wins, losses
FROM mg_contest_clans
ORDER BY elo DESC
LIMIT 10;

Reset all ELO

UPDATE mg_contest_clans
SET elo = 0, wins = 0, losses = 0, win_streak = 0;

Remove expired bans

DELETE FROM mg_contest_bans
WHERE expires_at < UNIX_TIMESTAMP();

Clear old seasons

-- Replace with your current season ID
DELETE FROM mg_contest_battlepass_progress      WHERE season_id <> 'contest_season_2';
DELETE FROM mg_contest_battlepass_clan_progress WHERE season_id <> 'contest_season_2';
DELETE FROM mg_contest_battlepass_claims        WHERE season_id <> 'contest_season_2';

DANGER

Stop the resource and back up your database before running queries that change data.

Uninstalling

After a backup, drop the tables if you no longer need the data:

DROP TABLE IF EXISTS
  mg_contest_battlepass_claims,
  mg_contest_battlepass_clan_progress,
  mg_contest_battlepass_progress,
  mg_contest_player_settings,
  mg_contest_history,
  mg_contest_bans,
  mg_contest_invites,
  mg_contest_clan_members,
  mg_contest_player_stats,
  mg_contest_clans;