View Single Post
Drixevel
AlliedModders Donor
Join Date: Sep 2009
Location: Somewhere headbangin'
Old 10-15-2017 , 07:15   Re: A short Threaded SQL guide
Reply With Quote #2

Code:
#include <sourcemod> 

//Totally realistic player data stuff: 
int g_Points[MAXPLAYERS + 1] = {0, ...}; 
int g_Kills[MAXPLAYERS + 1] = {0, ...}; 
int g_Deaths[MAXPLAYERS + 1] = {0, ...}; 
char g_Title[MAXPLAYERS + 1][32]; 

//Database Handle yay 
Database g_Database = null; 

public void OnPluginStart() 
{ 
    /* 
    * SQL_TConnect passes data to our two handles in our callback as such: 
    * new database handle in db or the 2nd param (db) 
    * driver handle in owner or the 1st param (owner) 
    */ 
    SQL_TConnect(T_Connect, "sick_gaming"); 
} 

//if you really wanted to be risky you could do OnClientAuthorized 
public void OnClientPutInServer(int client) 
{ 
    //idk if querying from a steamid of BOT is a great idea 
    if(!IsFakeClient(client) && g_Database != null) 
    { 
        char query[256], steamid[32]; 
        GetClientAuthId(client, AuthId_Steam2, steamid, sizeof(steamid)); 
        FormatEx(query, sizeof(query), "SELECT * FROM sick_gaming_players WHERE steam_id = '%s'", steamid); 
        SQL_TQuery(g_Database, T_LoadData, query, GetClientUserId(client)); 

        //TQuery will send the following to the threaded callback: 
        //Database = owner (param 1) 
        //Results = results or child (param 2) 
    } 
} 

//THIS IS OUR EVEN COOLER CALLBACK TO LOAD PLAYER DATA! 
public void T_LoadData(Handle owner, Handle results, const char[] error, any data) 
{ 
    if(owner == null || results == null) 
    { 
        LogError("T_LoadData returned error: %s", error); 
        return; 
    } 

    //We sent this in data because we need a steamid to pull incase nothing was pulled. 
    int client = GetClientOfUserId(data);  
	
    //Client is no longer valid for some reason, skip the results.
    if (client == 0)
    {
	return;
    }

    //This is much easier when you're working with the wildcard in a SELECT query 
    int killCol, deathCol, pointCol, titleCol; 
    SQL_FieldNameToNum(results, "kills", killCol); 
    SQL_FieldNameToNum(results, "deaths", deathCol); 
    SQL_FieldNameToNum(results, "points", pointCol); 
    SQL_FieldNameToNum(results, "title", titleCol); 

    //If a row set was returned: 
    if(SQL_FetchRow(results)) 
    { 
        g_Kills[client] = SQL_FetchInt(results, killCol); //Pretty much uses our number given from FieldNameToNum, not required. 
        g_Deaths[client] = SQL_FetchInt(results, deathCol); 
        g_Points[client] = SQL_FetchInt(results, pointCol); 
        SQL_FetchString(results, titleCol, g_Title[client], sizeof(g_Title[])); 
    } 
    else 
    { 
        //not found in db, insert data: 
        char steamid[32]; GetClientAuthId(client, AuthId_Steam2, steamid, sizeof(steamid)); 
        char query[256]; Format(query, sizeof(query), "INSERT INTO sick_gaming_players (steam_id) VALUES ('%s') ON DUPLICATE KEY UPDATE steam_id = '%s';", steamid, steamid); 
        SQL_TQuery(g_Database, T_Generic, query); 
    } 
} 

//THIS IS OUR OTHER COOL CALLBACK TO JUST DO NICE QUERIES WITHOUT RETURNED DATA! 
public void T_Generic(Handle owner, Handle results, const char[] error, any data) 
{ 
    if(owner == null || results == null) 
    { 
        LogError("T_Generic returned error: %s", error); 
        return; 
    } 
} 

//THIS IS OUR COOL CALLBACK TO CONNECT TO OUR SICK GAMING DATABASE! 
public void T_Connect(Handle owner, Handle db, const char[] error, any data) 
{ 
    //This is going to get our driver for multi-configurability: 
    //I wanted to make some code to check if the driver was messed up or something but could never figure it out honestly. 
    DBDriver driver = view_as<DBDriver>(owner); 
    char driverName[16]; driver.GetIdentifier(driverName, sizeof(driverName)); //This is the driver string as identified in databases.cfg  
	
    //Don't duplicate handles if we already have a valid handle.
    if (g_Database != null)
    {
	delete db;
	return;
    }

    //if our thing in databases.cfg is set as "mysql" or default is "mysql" 
    if(StrEqual("mysql", driverName, false)) 
    { 
        g_Database = view_as<Database>(db); 

        //If this fucks up for any reason: 
        if(g_Database == null) 
        { 
            LogError("T_Connect returned invalid MySQL Handle"); 
            return; 
        } 

        PrintToServer("[Realistic SQL] Connected to MySQL Database."); 
        return; 
    } 

    //likewise with sqlite 
    else if(StrEqual("sqlite", driverName, false)) 
    { 
        char _error[256]; 
        g_Database = SQLite_UseDatabase("sick_gaming", _error, sizeof(_error)); 

        //If this fucks up for any reason: 
        if(g_Database == null) 
        { 
            LogError("T_Connect returned invalid SQLite Handle"); 
            return; 
        } 

        PrintToServer("[Realistic SQL] Connected to SQLite Database."); 
        return; 
    } 
}
Added a client index check after the query is completed, made sure we don't duplicate database handles and made slight changes that are very minor.

Last edited by Drixevel; 10-15-2017 at 07:16.
Drixevel is offline