[sdz]

sourcemod LLC please grant me a free namechange ive been asking for 5 years

Taking an excerpt from my recently created deathrun plugin that I made out of pure boredom, we'll go from start to finish how a threaded sql (MySQL and SQLite or maybe that guy who made the postgre driver) would be deployed and executed in a real scenario
This tutorial assumes you have a basic understanding of SQL Syntax.

In this basic tutorial we'll be making a threaded connection, using that to load and save a players' data to a database go figure

Starting off, let's say we have some totally realistic base code like so:

PHP 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()
{
    //we need code here
} 


As you can see, we have our totally realistic player data that we'll be reading and writing to, and our database handle which is currently null because nice.
Continuing, we'll use a nice method from the Database methodmap called Database.Connect.
This is the threaded, newdecls version of SQL_TConnect and the one of the few I've ever used because I've never actually seen a reason to use non threaded SQL.

Reading the prototype of Database.Connect we need a special callback for threaded operations called a... SQLConnectCallback.

Skipping a few steps assuming you at least know how to use a basic callback, that will leave us with a little bit more code:

PHP 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
    */
    g_Database.Connect(T_Connect, "sick_gaming");
}

//THIS IS OUR COOL FUNCTION TO CONNECT TO OUR SICK GAMING DATABASE!
public void T_Connect(Database db, const char[] error, any data)
{
    //If this fucks up for any reason:
    if(db == null)
    {
        LogError("T_Connect returned invalid Database Handle");
        return;
    }

    g_Database = db;
    PrintToServer("[Realistic SQL] Connected to Database.");
    return;
} 


Now lets pretend in a perfect world we had no hitches or anything. We can move on to querying for a player's data.
Lets pretend we have the following schema going on:

CREATE TABLE IF NOT EXISTS `sick_gaming_players` (
	`kills` INT NOT NULL DEFAULT 0,
	`deaths` INT NOT NULL DEFAULT 0,
	`points` INT NOT NULL DEFAULT 0,
    `title` VARCHAR(32) NOT NULL DEFAULT ' ',
	`steam_id` VARCHAR(32) NOT NULL,
	PRIMARY KEY(`steam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and we want to pull all that data when they join, so skipping a few more steps will lead us to this code:

PHP 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;
bool g_Connected = false;

public void OnPluginStart()
{
    /*
    * Database.Connect passes data to our two handles in our callback as such:
    * new database handle in db
    */
    g_Database.Connect(T_Connect, "sick_gaming");
}

//update from original based on words of peace-maker and others -
//this is called when the client has a steamid on the serverside:
public void OnClientAuthorized(int client)
{
    //idk if querying from a steamid of BOT is a great idea
    if(!IsFakeClient(client))
    {
        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);
        g_Database.Query(T_LoadData, query, GetClientUserId(client));

        //Database.Query will send the following to the threaded callback:
        //Database = db
        //Results = results
    }
}

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

    int client = GetClientOfUserId(data); 

    int killCol, deathCol, pointCol, titleCol;
    results.FieldNameToNum("kills", killCol);
    results.FieldNameToNum("deaths", deathCol);
    results.FieldNameToNum("points", pointCol);
    results.FieldNameToNum("title", titleCol);

    //If a row set was returned:
    if(results.FetchRow())
    {
        g_Kills[client] = results.FetchInt(killCol);
        g_Deaths[client] = results.FetchInt(results, deathCol);
        g_Points[client] = results.FetchInt(results, pointCol);
        results.FetchString(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);
        db.Query(T_Generic, query);
    }
}

//THIS IS OUR OTHER COOL CALLBACK TO JUST DO NICE QUERIES WITHOUT RETURNED DATA!
public void T_Generic(Database db, DBResultSet results, const char[] error, any data)
{
    if(db == 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(Database db, const char[] error, any data)
{
    //If this fucks up for any reason:
    if(db == null)
    {
        LogError("T_Connect returned invalid Database Handle");
        return;
    }

    g_Database = db;
    PrintToServer("[Realistic SQL] Connected to Database.");
    return;
} 


Here we're pretty much in the final stretch.
As you can see in our T_LoadData callback, we're doing some basic stuff here. To break it down:

PHP Code:

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

    int client = GetClientOfUserId(data); 

    int killCol, deathCol, pointCol, titleCol;
    results.FieldNameToNum("kills", killCol);
    results.FieldNameToNum("deaths", deathCol);
    results.FieldNameToNum("points", pointCol);
    results.FieldNameToNum("title", titleCol);

    //If a row set was returned:
    if(results.FetchRow())
    {
        g_Kills[client] = results.FetchInt(killCol);
        g_Deaths[client] = results.FetchInt(results, deathCol);
        g_Points[client] = results.FetchInt(results, pointCol);
        results.FetchString(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);
        db.Query(T_Generic, query);
    }
} 


Ideally at this point you'll probably be knowing what you're doing in terms of the absolute basics.
Our finished base code:

PHP 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
    */
    g_Database.Connect(T_Connect, "sick_gaming");
}

//update from original based on words of peace-maker and others -
//this is called when the client has a steamid on the serverside:
public void OnClientAuthorized(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);
        g_Database.Query(T_LoadData, query, GetClientUserId(client));

        //TQuery will send the following to the threaded callback:
        //Database = db
        //Results = results
    }
}

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

    int client = GetClientOfUserId(data); 

    int killCol, deathCol, pointCol, titleCol;
    results.FieldNameToNum("kills", killCol);
    results.FieldNameToNum("deaths", deathCol);
    results.FieldNameToNum("points", pointCol);
    results.FieldNameToNum("title", titleCol);

    //If a row set was returned:
    if(results.FetchRow())
    {
        g_Kills[client] = results.FetchInt(killCol);
        g_Deaths[client] = results.FetchInt(results, deathCol);
        g_Points[client] = results.FetchInt(results, pointCol);
        results.FetchString(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);
        db.Query(T_Generic, query);
    }
}

//THIS IS OUR OTHER COOL CALLBACK TO JUST DO NICE QUERIES WITHOUT RETURNED DATA!
public void T_Generic(Database db, DBResultSet results, const char[] error, any data)
{
    if(db == 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(Database db, const char[] error, any data)
{
    //If this fucks up for any reason:
    if(db == null)
    {
        LogError("T_Connect returned invalid Database Handle");
        return;
    }

    g_Database = db;
    PrintToServer("[Realistic SQL] Connected to Database.");
    return;
} 


Feel free to use it as a base. I don't really care about credits since anyone willing to leech this wouldn't even give credits anyway.
if i fucked something up in the code it was because i made this at 7 AM for someone

[Drixevel]

#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.

[Peace-Maker]

Nice tutorial! Some notes:
Right now you're connecting to the sqilte database twice - a second time in the T_Connect callback using the non-threaded SQLite_UseDatabase while leaking the first database handle.
You don't really need to check the database driver unless you want to run some driver specific queries (like AUTO_INCREMENT in mysql vs. AUTOINCREMENT in sqlite)

You should never try to get the steamid of a player in OnClientPutInServer. Always use OnClientAuthorized to avoid STEAM_ID_STOP_IGNORING_RETVALS in your database AND always check the return value of GetClientAuthId.

There is a superceding Database methodmap interface you could take a look at. Especially the new Database.Connect function and the new callback prototype. This will save you some of the retagging when saving the database handle.

[Starbish]

you can change your nickname

https://forums.alliedmods.net/username.php

maybe?

[Addicted.]

Originally Posted by Starbish you can change your nickname https://forums.alliedmods.net/username.php maybe?

Originally Posted by EasSidezz free namechange

[sdz]

Originally Posted by Peace-Maker Nice tutorial! Some notes: Right now you're connecting to the sqilte database twice - a second time in the T_Connect callback using the non-threaded SQLite_UseDatabase while leaking the first database handle. You don't really need to check the database driver unless you want to run some driver specific queries (like AUTO_INCREMENT in mysql vs. AUTOINCREMENT in sqlite)

didn't even realize that TConnect would work with sqlite and mysql, thanks a ton! also thought the threaded callback disposed of handles automatically in a similar fashion to some other callback

Originally Posted by Peace-Maker You should never try to get the steamid of a player in OnClientPutInServer. Always use OnClientAuthorized to avoid STEAM_ID_STOP_IGNORING_RETVALS in your database AND always check the return value of GetClientAuthId.

was waiting for that one, get that error very rarely and wasn't sure if that was the reason, explains a lot.

Originally Posted by Peace-Maker There is a superceding Database methodmap interface you could take a look at. Especially the new Database.Connect function and the new callback prototype. This will save you some of the retagging when saving the database handle.

that database.connect methodmap gave me anxiety because i wasn't sure if it was threaded or not, I actually rewrote the entire tutorial in purely new syntax but wasn't sure of what all worked.


Thanks for the advice, rewrote the code of the tutorial to incorporate what you've suggested

[hmmmmm]

PHP Code:

public void T_LoadData(Database db, DBResultSet results, const char[] error, any data)
{
    //If either are broken:
    if(owner == null || results == null)
    {
        LogError("T_LoadData returned error: %s", error);
        return;
    }
} 


I think theres something wrong here. owner isn't one of the parameters, so this would throw an error.

[sdz]

Originally Posted by hmmmmm PHP Code: public void T_LoadData(Database db, DBResultSet results, const char[] error, any data) {     //If either are broken:     if(owner == null || results == null)     {         LogError("T_LoadData returned error: %s", error);         return;     } }  I think theres something wrong here. owner isn't one of the parameters, so this would throw an error.

100% didnt even catch that, thanks. fixed

[iskenderkebab33]

hello, i know this is a old post, but i need help to UPDATE my DB, so how can i do this, example i like to UPDATE user kills deaths and points when he disconnect from server. thanks.