[ThatKidWhoGames]

Hey all,

I just wanted to know if what I coded is the most efficient way of stat tracking with MySQL.

PHP Code:

#include <sourcemod>
#include <tf2_stocks>

Database g_hDatabase;

public void OnPluginStart()
{
    Database.Connect(DB_OnConnection, "stats");

    HookEvent("player_death", Event_PlayerDeath);
}

/*===================================================
=                    C L I E N T S                    =
===================================================*/

public void OnClientPostAdminCheck(int client)
{
    InsertClientIntoDB(client);
}

/*=================================================
=                    E V E N T S                      =
=================================================*/

public void Event_PlayerDeath(Event event, const char[] name, bool dontBroadcast)
{
    if (g_hDatabase && GameRules_GetRoundState() == RoundState_RoundRunning && !(event.GetInt("death_flags") & TF_DEATHFLAG_DEADRINGER))
    {
        char steamid[21], query[256];
        int victim_userid = event.GetInt("userid");
        int victim           = GetClientOfUserId(victim_userid);
        if (victim && IsClientInGame(victim))
        {
            if (!GetClientAuthId(victim, AuthId_Steam2, steamid, sizeof(steamid)))
            {
                LogError("Failure retrieving the SteamID of victim %N (UserID: %i) Callback: Event_PlayerDeath()", victim, victim_userid);
            } else {
                g_hDatabase.Format(query, sizeof(query), "UPDATE `stats` SET `deaths` = `deaths` + '1', `points` = `points` - '1' WHERE `steamid` = '%s';", steamid);
                g_hDatabase.Query(DB_OnUpdateClient, query, victim_userid);
            }
        }

        int attacker_userid = event.GetInt("attacker");
        int attacker        = GetClientOfUserId(attacker_userid);
        if (attacker && IsClientInGame(attacker))
        {
            if (!GetClientAuthId(attacker, AuthId_Steam2, steamid, sizeof(steamid)))
            {
                LogError("Failure retrieving the SteamID of attacker %N (UserID: %i) Callback: Event_PlayerDeath()", attacker, attacker_userid);
            } else {
                g_hDatabase.Format(query, sizeof(query), "UPDATE `stats` SET `kills` = `kills` + '1', `points` = `points` + '2' WHERE `steamid` = '%s';", steamid);
                g_hDatabase.Query(DB_OnUpdateClient, query, attacker_userid);
            }
        }

        int assister_userid = event.GetInt("assister");
        int assister        = GetClientOfUserId(assister_userid);
        if (assister && IsClientInGame(assister))
        {
            if (!GetClientAuthId(assister, AuthId_Steam2, steamid, sizeof(steamid)))
            {
                LogError("Failure retrieving the SteamID of assister %N (UserID: %i) Callback: Event_PlayerDeath()", assister, assister_userid);
            } else {
                g_hDatabase.Format(query, sizeof(query), "UPDATE `stats` SET `assists` = `assists` + '1', `points` = `points` + '1' WHERE `steamid` = '%s';", steamid);
                g_hDatabase.Query(DB_OnUpdateClient, query, assister_userid);
            }
        }
    }
}

/*===============================================
=                    M Y S Q L                      =
===============================================*/

public void DB_OnConnection(Database db, const char[] error, any data)
{
    if (!db)
    {
        SetFailState("Failure establishing a connection to the database! Error: %s", error);
    } else {
        g_hDatabase = db;
        CreateTable();
    }
}

void CreateTable()
{
    char[] query = "CREATE TABLE IF NOT EXISTS `stats` ("
                ..."`steamid` varchar(21) NOT NULL PRIMARY KEY, "
                ..."`name` varchar(32) NOT NULL, "
                ..."`kills` int(11) NOT NULL DEFAULT '0', "
                ..."`deaths` int(11) NOT NULL DEFAULT '0', "
                ..."`assists` int(11) NOT NULL DEFAULT '0', "
                ..."`points` int(11) NOT NULL DEFAULT '0');";
    g_hDatabase.Query(DB_OnCreateTable, query);
}

public void DB_OnCreateTable(Database db, DBResultSet results, const char[] error, any data)
{
    if (!results)
    {
        delete g_hDatabase;
        SetFailState("Failure creating the database table! Error: %s", error);
    } else if (GetClientCount()) {
        InsertAllIntoDB();
    }
}

public void DB_OnInsertClient(Database db, DBResultSet results, const char[] error, int userid)
{
    int client = GetClientOfUserId(userid);
    if (client && IsClientInGame(client))
    {
        if (!results)
        {
            LogError("Failure inserting client %N (UserID: %i) into the database! Error: %s", client, userid);
        }
    }
}

public void DB_OnUpdateClient(Database db, DBResultSet results, const char[] error, int userid)
{
    int client = GetClientOfUserId(userid);
    if (client && IsClientInGame(client) && !results)
    {
        LogError("Failure updating the stats of client %N (UserID: %i) in the database! Error: %s", client, userid, error);
    }
}

void InsertClientIntoDB(int client)
{
    if (!IsFakeClient(client))
    {
        int userid = GetClientUserId(client);
        char steamid[21];
        if (!GetClientAuthId(client, AuthId_Steam2, steamid, sizeof(steamid)))
        {
            LogError("Failure retrieving the SteamID of client %N (UserID: %i) Function: InsertClientIntoDB()", client, userid);
        } else {
            char query[256];
            g_hDatabase.Format(query, sizeof(query), "INSERT INTO `stats` (`steamid`, `name`) VALUES ('%s', '%N') ON DUPLICATE KEY UPDATE `name` = '%N';", steamid, client, client);
            g_hDatabase.Query(DB_OnInsertClient, query, userid);
        }
    }
}

void InsertAllIntoDB()
{
    for (int i = 1; i <= MaxClients; i++)
    {
        if (IsClientInGame(i))
        {
            InsertClientIntoDB(i);
        }
    }
} 


Thanks,
Grant

[P4UL]

I'm not that experienced with SQL but I think it is a lot more efficient to store the amount of deaths/kills etc locally until either the map ends or the player disconnects so it doesn't have to make a query to the database every single time

[Bacardi]

Originally Posted by P4UL I'm not that experienced with SQL but I think it is a lot more efficient to store the amount of deaths/kills etc locally until either the map ends or the player disconnects so it doesn't have to make a query to the database every single time

+1

[headline]

Apart from the previous comment; your code looks fine in terms of efficiency. Good work.

[Psyk0tik]

A little off-topic but...

What does the "..." do in this function and what's the proper term for it?

PHP Code:

void CreateTable()
{
    char[] query = "CREATE TABLE IF NOT EXISTS `stats` ("
                ..."`steamid` varchar(21) NOT NULL PRIMARY KEY, "
                ..."`name` varchar(32) NOT NULL, "
                ..."`kills` int(11) NOT NULL DEFAULT '0', "
                ..."`deaths` int(11) NOT NULL DEFAULT '0', "
                ..."`assists` int(11) NOT NULL DEFAULT '0', "
                ..."`points` int(11) NOT NULL DEFAULT '0');";
    g_hDatabase.Query(DB_OnCreateTable, query);
} 


I've only seen the "..." used in function parameters for VFormat() usage before.

[headline]

Originally Posted by Crasher_3637 A little off-topic but... What does the "..." do in this function and what's the proper term for it? PHP Code: void CreateTable() {     char[] query = "CREATE TABLE IF NOT EXISTS `stats` ("                 ..."`steamid` varchar(21) NOT NULL PRIMARY KEY, "                 ..."`name` varchar(32) NOT NULL, "                 ..."`kills` int(11) NOT NULL DEFAULT '0', "                 ..."`deaths` int(11) NOT NULL DEFAULT '0', "                 ..."`assists` int(11) NOT NULL DEFAULT '0', "                 ..."`points` int(11) NOT NULL DEFAULT '0');";     g_hDatabase.Query(DB_OnCreateTable, query); }  I've only seen the "..." used in function parameters for VFormat() usage before.

In sourcepawn, it’s a string concatenation operator. Similar to the + operator in languages like python and java. The limitation in sourcepawn is that it can only be used with string literals.

[ThatKidWhoGames]

Thanks guys!!

[Psyk0tik]

Originally Posted by Headline In sourcepawn, it’s a string concatenation operator. Similar to the + operator in languages like python and java. The limitation in sourcepawn is that it can only be used with string literals.

That makes sense. Thanks!

[Impact123]

It's a bit late but don't forget to escape client names.

[headline]

Originally Posted by Impact123 It's a bit late but don't forget to escape client names.

Database.Format escapes for you