AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   sql (https://forums.alliedmods.net/showthread.php?t=333781)

lexzor 08-05-2021 05:00

sql
 
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(szQuerycharsmax(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(SqlConnectionszQuery);

    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(idg_PlayerData[id][STEAMID], charsmax(g_PlayerData[][STEAMID]));
    
get_user_name(idg_PlayerData[id][NAME], charsmax(g_PlayerData[][NAME]));
}
public 
client_putinserver(id)
{
    new 
szQuery[512];
    new 
szData[1];

    
szData[0] = id;

    
formatex(szQuerycharsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'"g_szTableg_PlayerData[id][STEAMID]);
    
SQL_ThreadQuery(g_SqlTuple"GetPoints"szQueryszData1);
}
public 
GetPoints(FailStateHandle:QueryszError[], ErrorCodeszData[], iSize)
{
    if(
FailState || ErrorCode)
    {
        
server_print(" ");
        
server_print("[%s] SQL ERROR: %s"szErrorg_szPluginName);
        
server_print(" ");
    }

    new 
id szData[0];

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

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

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

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

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

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

    
formatex(szQuerycharsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'"g_szTableg_PlayerData[id][STEAMID]);
    
SQL_ThreadQuery(g_SqlTuple"Points"szQueryszData2);
}

public 
Points(FailStateHandle:QueryszError[], ErrorCodeszData[], iSize)
{
    if(
FailState || ErrorCode)
    {
        
server_print(" ");
        
server_print("[%s] SQL ERROR: %s"szErrorg_szPluginName);
        
server_print(" ");
    }

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

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

    
SQL_ThreadQuery(g_SqlTuple"FreeHandle"szQuery);
}

public 
FreeHandle(FailStateHandle:QueryszError[], ErrorCodeszData[], iSize)
{
    if(
FailState || ErrorCode)
    {
        
server_print(" ");
        
server_print("[%s] SQL ERROR: %s"szErrorg_szPluginName);
        
server_print(" ");
    }
    
    
SQL_FreeHandle(Query);


Thanks!

Dragos 08-05-2021 09:03

Re: sql
 
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 08-05-2021 09:15

Re: sql
 
Quote:

Originally Posted by Dragos (Post 2754549)
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(szQuerycharsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'"g_szTableg_PlayerData[id][STEAMID]);
    
SQL_ThreadQuery(g_SqlTuple"GetPoints"szQueryszData1);


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"szErrorg_szPluginName);
        
server_print(" "); 

:arrow:

PHP Code:

server_print("^n[%s] SQL ERROR: %s^n"szErrorg_szPluginName); 


Dragos 08-05-2021 09:20

Re: sql
 
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 08-05-2021 09:31

Re: sql
 
Quote:

Originally Posted by Natsheh (Post 2754551)
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(szQuerycharsmax(szQuery), "SELECT * FROM `%s` WHERE `steamid` = '%s'"g_szTableg_PlayerData[id][STEAMID]);
    
SQL_ThreadQuery(g_SqlTuple"GetPoints"szQueryszData1);


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"szErrorg_szPluginName);
        
server_print(" "); 

:arrow:

PHP Code:

server_print("^n[%s] SQL ERROR: %s^n"szErrorg_szPluginName); 


Thanks!

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

[MySQL] Thread worker was unable to start.

Natsheh 08-05-2021 09:35

Re: sql
 
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"szErrorg_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 08-05-2021 09:40

Re: sql
 
Quote:

Originally Posted by lexzor (Post 2754554)
Thanks!

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

[MySQL] Thread worker was unable to start.

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

SQL_ThreadQuery() 

:arrow:
PHP Code:

SQL_Execute() 

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

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 08-05-2021 09:46

Re: sql
 
Quote:

Originally Posted by Shadows Adi (Post 2754556)
You can avoid that error simply but not using Threaded Query:
PHP Code:

SQL_ThreadQuery() 

:arrow:
PHP Code:

SQL_Execute() 

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

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 08-05-2021 11:23

Re: sql
 
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 08-05-2021 12:54

Re: sql
 
Quote:

Originally Posted by Bugsy (Post 2754563)
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?


All times are GMT -4. The time now is 02:39.

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