[lexzor]

hello. i want to make a plugin but first i have to be sure that i use sql correct.

I have a piece of code that works without problems. Can someone look over it and tell me what i can improve or if i did something that can cause bugs?

PHP Code:

#include <amxmodx>
#include <sqlx>

#define VERSION "1.0"

#if !defined MAX_IP_LENGTH_WITHOUT_PORT
    #define MAX_IP_LENGTH_WITHOUT_PORT 16
#endif

#pragma semicolon 1

///// ENUM
enum _:PDATA
{
    NAME[MAX_NAME_LENGTH],
    STEAMID[MAX_AUTHID_LENGTH],
    IP[MAX_IP_LENGTH_WITHOUT_PORT],
    CASH,
    DONATE
};

//// SQL
new Handle:g_SqlTuple;
new g_Error[512];
new const g_szTable [] = "bank";

new const g_szSqlData[][] =  
{
    "",
    "",
    "",
    ""
};

//// VARIABLES
new const g_szPluginName[] = "BANK";
new g_PlayerData[MAX_PLAYERS + 1][PDATA];

public plugin_init()
{
    register_plugin("Bank System", VERSION, "lexzor");

    register_clcmd("points", "add_points");

    set_task(0.1, "MySql_Init");
}
public MySql_Init()
{
    g_SqlTuple = SQL_MakeDbTuple(g_szSqlData[0], g_szSqlData[1],g_szSqlData[2], g_szSqlData[3]);
       
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error));
       
    if(SqlConnection == Empty_Handle)
    {
        set_fail_state(g_Error);
    }
    
    new Handle:Queries;
    new szQuery[1024];
    
    formatex(szQuery, charsmax(szQuery), "CREATE TABLE IF NOT EXISTS %s ( `id` INT(11) NOT NULL AUTO_INCREMENT , `steamid` VARCHAR(36) NOT NULL , `points` INT(11) NOT NULL, PRIMARY KEY (`id`))", g_szTable);
    Queries = SQL_PrepareQuery(SqlConnection, szQuery);

    if(!SQL_Execute(Queries))
    {
        SQL_QueryError(Queries,g_Error,charsmax(g_Error));
        set_fail_state(g_Error);
    }        
    
    SQL_FreeHandle(Queries);
    SQL_FreeHandle(SqlConnection);
} 
public plugin_end()
{
    SQL_FreeHandle(g_SqlTuple);
}
public client_authorized(id)
{
    get_user_authid(id, g_PlayerData[id][STEAMID], charsmax(g_PlayerData[][STEAMID]));
    get_user_name(id, g_PlayerData[id][NAME], charsmax(g_PlayerData[][NAME]));
}
public client_putinserver(id)
{
    new szQuery[512];
    new szData[1];

    szData[0] = id;

    formatex(szQuery, charsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'", g_szTable, g_PlayerData[id][STEAMID]);
    SQL_ThreadQuery(g_SqlTuple, "GetPoints", szQuery, szData, 1);
}
public GetPoints(FailState, Handle:Query, szError[], ErrorCode, szData[], iSize)
{
    if(FailState || ErrorCode)
    {
        server_print(" ");
        server_print("[%s] SQL ERROR: %s", szError, g_szPluginName);
        server_print(" ");
    }

    new id = szData[0];

    if(SQL_NumResults(Query) != 0)
    {
        g_PlayerData[id][CASH] = SQL_ReadResult(Query, 2);
    }
    else 
    {
        g_PlayerData[id][CASH] = 0;
    }

    server_print("[%s] %s has %i points.", g_szPluginName, g_PlayerData[id][STEAMID], g_PlayerData[id][CASH]);
}

public add_points(id)
{
    new iArg = read_argv_int(id);
    server_print("%i", iArg);

    switch(iArg)
    {
        case 1: g_PlayerData[id][CASH] += 300;
        case 2: g_PlayerData[id][CASH] -= 300;
    }

    new szQuery[512];
    new szData[2];

    szData[0] = id;
    szData[1] = g_PlayerData[id][CASH];

    formatex(szQuery, charsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'", g_szTable, g_PlayerData[id][STEAMID]);
    SQL_ThreadQuery(g_SqlTuple, "Points", szQuery, szData, 2);
}

public Points(FailState, Handle:Query, szError[], ErrorCode, szData[], iSize)
{
    if(FailState || ErrorCode)
    {
        server_print(" ");
        server_print("[%s] SQL ERROR: %s", szError, g_szPluginName);
        server_print(" ");
    }

    new szQuery[512];
    new id = szData[0];
    new iCash = szData[1];

    if(SQL_NumResults(Query) != 0)
    {
        formatex(szQuery, charsmax(szQuery), "UPDATE `%s` SET `points` = '%i' WHERE `steamid` = '%s'", g_szTable, iCash, g_PlayerData[id][STEAMID]);
    }
    else 
    {
        formatex(szQuery, charsmax(szQuery), "INSERT INTO `%s` (`steamid`,`points`) VALUES ('%s','%i')", g_szTable, g_PlayerData[id][STEAMID], g_PlayerData[id][CASH]);
    }

    SQL_ThreadQuery(g_SqlTuple, "FreeHandle", szQuery);
}

public FreeHandle(FailState, Handle:Query, szError[], ErrorCode, szData[], iSize)
{
    if(FailState || ErrorCode)
    {
        server_print(" ");
        server_print("[%s] SQL ERROR: %s", szError, g_szPluginName);
        server_print(" ");
    }
    
    SQL_FreeHandle(Query);
} 


Thanks!

[Dragos]

One thing that I saw as useless was this,

PHP Code:

set_task(0.1, "MySql_Init"); 


You can simply call the function "MySql_Init()" without using set_task for every 0.10 seconds because that function will be automatically called when the plugin is loaded.

Using your method can supercharge the CPU of that database and crash it.

[Natsheh]

Originally Posted by Dragos One thing that I saw as useless was this, PHP Code: set_task(0.1, "MySql_Init");  You can simply call the function "MySql_Init()" without using set_task for every 0.10 seconds because that function will be automatically called when the plugin is loaded. Using your method can supercharge the CPU of that database and crash it.

What is this nonsense?

Using a delay is not a bad idea, imagine alot of plugins connecting on plugin_init this will cause a bit server lag until all the data is processed.

How ever i saw some potential mistakes you might want to avoid

client_putinserver(id) forward is not guaranteed to be called after client_authorized(id) so you might want to do the following code in client_authorized(id) body because its guaranteed that steam id is ready to be retrieved.

PHP Code:

{
    new szQuery[512];
    new szData[1];

    szData[0] = id;

    formatex(szQuery, charsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'", g_szTable, g_PlayerData[id][STEAMID]);
    SQL_ThreadQuery(g_SqlTuple, "GetPoints", szQuery, szData, 1);
} 


Also you can you use static to initialize big arrays, for a better speed performance.


---------- optimization -------

PHP Code:

server_print(" ");
        server_print("[%s] SQL ERROR: %s", szError, g_szPluginName);
        server_print(" "); 


PHP Code:

server_print("^n[%s] SQL ERROR: %s^n", szError, g_szPluginName); 


[Dragos]

Much time I thought that using set_task will lag your server, but hearing you, you changed my opinion.

Now I found out why it took so long time to change the map on some king of servers I tested it.

[lexzor]

Originally Posted by Natsheh What is this nonsense? Using a delay is not a bad idea, imagine alot of plugins connecting on plugin_init this will cause a bit server lag until all the data is processed. How ever i saw some potential mistakes you might want to avoid client_putinserver(id) forward is not guaranteed to be called after client_authorized(id) so you might want to do the following code in client_authorized(id) body because its guaranteed that steam id is ready to be retrieved. PHP Code: {     new szQuery[512];     new szData[1];     szData[0] = id;     formatex(szQuery, charsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'", g_szTable, g_PlayerData[id][STEAMID]);     SQL_ThreadQuery(g_SqlTuple, "GetPoints", szQuery, szData, 1); }  Also you can you use static to initialize big arrays, for a better speed performance. ---------- optimization ------- PHP Code: server_print(" ");         server_print("[%s] SQL ERROR: %s", szError, g_szPluginName);         server_print(" ");  PHP Code: server_print("^n[%s] SQL ERROR: %s^n", szError, g_szPluginName);

Thanks!

I'm using set_task because i m trying to avoid this error:

[MySQL] Thread worker was unable to start.

[Natsheh]

Also on threaded query fail stop the rest of the code from executing for example

PHP Code:

if(FailState || ErrorCode)
    {
        server_print(" ");
        server_print("[%s] SQL ERROR: %s", szError, g_szPluginName);
        server_print(" ");
        return;
    } 


Also
There is no need to free the handle in threaded query function data callback ((FreeHandle))(SQL_FreeHandle(Query))

[Shadows Adi]

Originally Posted by lexzor Thanks! I'm using set_task because i m trying to avoid this error: [MySQL] Thread worker was unable to start.

You can avoid that error simply but not using Threaded Query:

PHP Code:

SQL_ThreadQuery() 


PHP Code:

SQL_Execute() 


http://www.amxmodx.org/api/sqlx/SQL_Execute

Note
You can call this multiple times as long as its parent connection is kept open.
Each time the result set from the previous call will be freed.

[Natsheh]

Originally Posted by Shadows Adi You can avoid that error simply but not using Threaded Query: PHP Code: SQL_ThreadQuery()  PHP Code: SQL_Execute()  http://www.amxmodx.org/api/sqlx/SQL_Execute Note You can call this multiple times as long as its parent connection is kept open. Each time the result set from the previous call will be freed.

Its a bad idea infact never ever use SQL_execute unless its going to be used a couple of times per map, using it alot might interfier with the gameplay and cause the server to lag alot.

[Bugsy]

I only use execute on queries that I need executed immediately. Examples are the creation and pruning of tables at plugin_init/plugin_cfg.

For everything else, including loading and saving player data during gameplay, threaded queries should be used.

[Shadows Adi]

Originally Posted by Bugsy I only use execute on queries that I need executed immediately. Examples are the creation and pruning of tables at plugin_init/plugin_cfg. For everything else, including loading and saving player data during gameplay, threaded queries should be used.

Can it cause server lagging or CPU overload if is used 2-3 times in a function?