Raised This Month: $ Target: $400
 0% 

A short Threaded SQL guide


  
 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
Author Message
sdz
Senior Member
Join Date: Feb 2012
Old 10-15-2017 , 06:55   A short Threaded SQL guide
Reply With Quote #1

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[] errorany 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:
Code:
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(clientAuthId_Steam2steamidsizeof(steamid));
        
FormatEx(querysizeof(query), "SELECT * FROM sick_gaming_players WHERE steam_id = '%s'"steamid);
        
g_Database.Query(T_LoadDataqueryGetClientUserId(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 dbDBResultSet results, const char[] errorany data)
{
    
//If either are broken:
    
if(db == null || results == null)
    {
        
LogError("T_LoadData returned error: %s"error);
        return;
    }

    
int client GetClientOfUserId(data); 

    
int killColdeathColpointColtitleCol;
    
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(resultsdeathCol);
        
g_Points[client] = results.FetchInt(resultspointCol);
        
results.FetchString(titleColg_Title[client], sizeof(g_Title[]));
    }
    else
    {
        
//not found in db, insert data:
        
char steamid[32]; GetClientAuthId(clientAuthId_Steam2steamidsizeof(steamid));
        
char query[256]; Format(querysizeof(query), "INSERT INTO sick_gaming_players (steam_id) VALUES ('%s') ON DUPLICATE KEY UPDATE steam_id = '%s';"steamidsteamid);
        
db.Query(T_Genericquery);
    }
}

//THIS IS OUR OTHER COOL CALLBACK TO JUST DO NICE QUERIES WITHOUT RETURNED DATA!
public void T_Generic(Database dbDBResultSet results, const char[] errorany 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[] errorany 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 dbDBResultSet results, const char[] errorany data)
{
    
//If either are broken:
    
if(db == null || results == null)
    {
        
LogError("T_LoadData returned error: %s"error);
        return;
    }

    
int client GetClientOfUserId(data); 

    
int killColdeathColpointColtitleCol;
    
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(resultsdeathCol);
        
g_Points[client] = results.FetchInt(resultspointCol);
        
results.FetchString(titleColg_Title[client], sizeof(g_Title[]));
    }
    else
    {
        
//not found in db, insert data:
        
char steamid[32]; GetClientAuthId(clientAuthId_Steam2steamidsizeof(steamid));
        
char query[256]; Format(querysizeof(query), "INSERT INTO sick_gaming_players (steam_id) VALUES ('%s') ON DUPLICATE KEY UPDATE steam_id = '%s';"steamidsteamid);
        
db.Query(T_Genericquery);
    }

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(clientAuthId_Steam2steamidsizeof(steamid));
        
FormatEx(querysizeof(query), "SELECT * FROM sick_gaming_players WHERE steam_id = '%s'"steamid);
        
g_Database.Query(T_LoadDataqueryGetClientUserId(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 dbDBResultSet results, const char[] errorany data)
{
    
//If either are broken:
    
if(db == null || results == null)
    {
        
LogError("T_LoadData returned error: %s"error);
        return;
    }

    
int client GetClientOfUserId(data); 

    
int killColdeathColpointColtitleCol;
    
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(resultsdeathCol);
        
g_Points[client] = results.FetchInt(resultspointCol);
        
results.FetchString(titleColg_Title[client], sizeof(g_Title[]));
    }
    else
    {
        
//not found in db, insert data:
        
char steamid[32]; GetClientAuthId(clientAuthId_Steam2steamidsizeof(steamid));
        
char query[256]; Format(querysizeof(query), "INSERT INTO sick_gaming_players (steam_id) VALUES ('%s') ON DUPLICATE KEY UPDATE steam_id = '%s';"steamidsteamid);
        
db.Query(T_Genericquery);
    }
}

//THIS IS OUR OTHER COOL CALLBACK TO JUST DO NICE QUERIES WITHOUT RETURNED DATA!
public void T_Generic(Database dbDBResultSet results, const char[] errorany 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[] errorany 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

Last edited by sdz; 10-23-2017 at 04:16. Reason: renamed a variable reference
sdz is offline
 



Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 23:29.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode