A short Threaded SQL guide
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
|