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:
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(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