AlliedModders

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

portocala 06-10-2010 15:33

SQL Stats
 
Hello,

I'm working on a plugin that records live statistics of players on my CS 1.6 Server (kills, deaths, hs, online time, etc) and I'm wondering if SQL is able to handle this without lag. I intend to display the results on a webpage.

I worry about DeathMsg event, because there will be more data traffic (think about a 22 players Deathmatch Server). Generaly speaking it's not much, but it is for SQL ?

Every kill / hs / death is send to MySQL Database with SQL_PrepareQuery and SQL_Execute.

I respectfully request your opinions about that.

Excuse me if I made mistakes of expression.

Brreaker 06-10-2010 16:43

Re: SQL Stats
 
I think MySQL can handle it, also, is your server performant enough to handle this ?

unnyquee 06-10-2010 17:10

Re: SQL Stats
 
Use threaded queries.

fang 06-10-2010 18:01

Re: SQL Stats
 
Try psychostats? :o

portocala 06-11-2010 02:56

Re: SQL Stats
 
Quote:

Originally Posted by Brreaker (Post 1205280)
I think MySQL can handle it, also, is your server performant enough to handle this ?

Yes. No problems about the server's configurations (CPU, RAM, etc).

Quote:

Originally Posted by unnyquee (Post 1205318)
Use threaded queries.

Do you mean SQL_ThreadQuery ? I had problems using this. Every map end / start I got errors ([MySQL] Thread worker was unable to start.), so I started code using SQL_PrepareQuery and SQL_Execute which by now works perfectly.

Can you explain the difference between SQL_ThreadQuery and SQL_PrepareQuery+SQL_Execute ? Honestly, I have searched but not found anything about it.

Quote:

Originally Posted by fang (Post 1205379)
Try psychostats? :o

I used Psychostats, but now I haven't an external server to perform the log processing, so I thought to build my own way to get these information on my website.



Thanks for the replies!


Edited:

If I have this:

Code:

new Handle:Query;

public event_death() {
    new killer = read_data(1);
    new victim = read_data(2);
    new name_killer[32]; get_user_name(killer, name_killer, 31);
    new name_victim[32]; get_user_name(victim, name_victim, 31);
   
    SQL_QuoteString(g_SqlConnection, name_killer, 31, name_killer)
    SQL_QuoteString(g_SqlConnection, name_victim, 31, name_victim)
   
    Query = SQL_PrepareQuery(g_SqlConnection, "SELECT kills FROM %s WHERE name='%s';", table, name_killer)
    SQL_Execute(Query);
   
    if(SQL_NumResults(Query)) {
        new kills = SQL_ReadResult(Query, 0);
        SQL_FreeHandle(Query);
        Query = SQL_PrepareQuery(g_SqlConnection, "UPDATE %s SET kills='%d' WHERE name='%s';",table, kills + 1, name_killer)
        SQL_Execute(Query);
    }
   
    SQL_FreeHandle(Query);
}


What happens when 'double/triple-kill' ? Is it OK for SQL ?

Brreaker 06-11-2010 03:39

Re: SQL Stats
 
It should be, as far as I know you may enter up to 150 data/second :)

Sylwester 06-11-2010 05:57

Re: SQL Stats
 
Quote:

Originally Posted by portocala (Post 1205639)
Do you mean SQL_ThreadQuery ? I had problems using this. Every map end / start I got errors ([MySQL] Thread worker was unable to start.), so I started code using SQL_PrepareQuery and SQL_Execute which by now works perfectly.

Can you explain the difference between SQL_ThreadQuery and SQL_PrepareQuery+SQL_Execute ? Honestly, I have searched but not found anything about it.

It's all explained in sqlx tutorial:
Quote:

Originally Posted by Hawk552 (Post 398785)
The true power lies in threaded querying. Threaded querying means nothing short of a rewrite of an entire plugin that's written with DBI, but has huge advantages, especially on slow connections. The idea of a threaded query is essentially that a new thread (a sub-process) with which its only goal is to send, monitor, and inform of the things that happen to a query. Because it is on a seperate thread, a query that would normally take 1 second (and 1 second of total connection loss for all clients in the server) still takes the same amount of time, but does not interrupt gameplay

So, forget about prepare/execute query and go back to using threaded queries. I recently posted sqlx example that uses sqlite (file on localhost):
http://forums.alliedmods.net/showthr...66#post1204466
Maybe it will help you fix your problems with threaded queries.

portocala 06-11-2010 07:31

Re: SQL Stats
 
OK, Thank you!

I rewrote the code using SQL_ThreadQuery but it is not working at all.

I get:

Code:

L 06/11/2010 - 12:41:57: Function "event_death" was not found
L 06/11/2010 - 12:41:57: [AMXX] Displaying debug trace (plugin "stats_gg.amxx")
L 06/11/2010 - 12:41:57: [AMXX] Run time error 19: function not found
L 06/11/2010 - 12:41:57: [AMXX]    [0] stats_gg.sma::plugin_init (line 23)
L 06/11/2010 - 12:42:02: [MySQL] Invalid database handle: 0
L 06/11/2010 - 12:42:02: [AMXX] Displaying debug trace (plugin "stats_gg.amxx")
L 06/11/2010 - 12:42:02: [AMXX] Run time error 10: native error (native "SQL_PrepareQuery")
L 06/11/2010 - 12:42:02: [AMXX]    [0] stats_gg.sma::authorizedx (line 76)
L 06/11/2010 - 12:42:02: [AMXX]    [1] stats_gg.sma::client_putinserver (line 57)
.
.
.

And there is my entire plugin:

Code:

#include <amxmodx>
#include <amxmisc>
#include <cstrike>
#include <sqlx>
#include <fakemeta>

#define PLUGINNAME    "gg stats"
#define VERSION        "1.0"
#define AUTHOR        "me"

#define table          "gg_statsx13"
#define table2          "gg_stats"

new Handle:g_SqlX;
new Handle:g_SqlConnection;
new g_error[512];
new clientcheck[32];

public plugin_init()
{
    register_plugin(PLUGINNAME, VERSION, AUTHOR);
    register_event("DeathMsg", "event_death", "a");
    sql_init();
}

public sql_init()
{
    new host[64], user[64], pass[64], db[64], errorcode;

    get_cvar_string("amx_sql_host", host, 63);
    get_cvar_string("amx_sql_user", user, 63);
    get_cvar_string("amx_sql_pass", pass, 63);
    get_cvar_string("amx_sql_db", db, 63);

    g_SqlX = SQL_MakeDbTuple(host, user, pass, db);
    g_SqlConnection = SQL_Connect(g_SqlX,errorcode,g_error,511);
   
    if (g_SqlConnection == Empty_Handle) set_fail_state(g_error);
    else
    {
        new query1[1001]; format(query1, 1000, "CREATE TABLE IF NOT EXISTS `%s`(`name` varchar(100) NOT NULL default '', `kills` int(10) NOT NULL default '0',`hs` int(10) NOT NULL default '0',`deaths` int(10) NOT NULL default '0', `time` int(10) NOT NULL default '0',`firstseen` varchar(100) NOT NULL default '',", table);
        new query2[1001]; format(query2, 1000, "`lastseen` varchar(100) NOT NULL default '',`steam` int(10) NOT NULL default '0',`nonsteam` int(10) NOT NULL default '0',`lastip` varchar(100) NOT NULL default '',`wins` int(10) NOT NULL default '0',`points` int(10) NOT NULL default '0');");
        new cache[1001]; format(cache, 1000, "%s%s", query1, query2);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public client_connect(id)
{
    clientcheck[id] = 0;
}

public client_putinserver(id)
{
    if(clientcheck[id] == 1) authorizedx(id);
    else clientcheck[id] = 1;
}

public client_authorized(id)
{
    if(clientcheck[id] == 1) authorizedx(id);
    else clientcheck[id] = 1;
}

public authorizedx(id)
{
    new name[32]; get_user_name(id, name, 31);
    new authid[32]; get_user_authid(id, authid, 31);
    new ip[32]; get_user_ip(id, ip, 31, 1);
    SQL_QuoteString(g_SqlConnection, name, 31, name);
   
    new cache[1001];
   
    format(cache, 1000, "SELECT * FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_firstcheck", cache, name, 31);
   
    format(cache, 1000, "SELECT wins FROM %s WHERE name='%s';", table2, name);
    SQL_ThreadQuery(g_SqlX, "handle_wins", cache, name, 31);
   
    format(cache, 1000, "SELECT points FROM %s WHERE name='%s';", table2, name);
    SQL_ThreadQuery(g_SqlX, "handle_points", cache, name, 31);
   
    format(cache, 1000, "UPDATE %s SET lastip='%s' WHERE name='%s';", table, ip, name);
    SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
   
    if(contain(authid, "STEAM_0") != -1)
    {
        format(cache, 1000, "SELECT steam FROM %s WHERE name='%s';", table, name);
        SQL_ThreadQuery(g_SqlX, "handle_steam", cache, name, 31);
    }
    else
    {
        format(cache, 1000, "SELECT nonsteam FROM %s WHERE name='%s';", table, name);
        SQL_ThreadQuery(g_SqlX, "handle_nonsteam", cache, name, 31);
    }
       
}

public handle_firstcheck(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(!SQL_NumResults(Query))
    {
        new currentTime[32]; get_time("%d %b %Y - %H:%M", currentTime, 31);
        new cache[1000];
        format(cache, 1000, "INSERT INTO %s (name, kills, hs, deaths, time, firstseen, lastseen, steam, nonsteam, lastip, wins, points) values ('%s', '0', '0', '0', '0', '%s', '%s', '0', '0', '', 0, 0);",table, Data, currentTime, currentTime);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_wins(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new wins = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET wins='%d' WHERE name='%s';",table, wins, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_points(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new points = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET points='%d' WHERE name='%s';",table, points, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_steam(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new steam = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET steam='%d' WHERE name='%s';",table, steam + 1, Data) ;
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_nonsteam(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new nonsteam = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET nonsteam='%d' WHERE name='%s';",table, nonsteam + 1, Data) ;
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public event_death()
{
    new killer = read_data(1);
    new victim = read_data(2);
    new name_killer[32]; get_user_name(killer, name_killer, 31);
    new name_victim[32]; get_user_name(victim, name_victim, 31);
    SQL_QuoteString(g_SqlConnection, name_killer, 31, name_killer);
    SQL_QuoteString(g_SqlConnection, name_victim, 31, name_victim);
   
    new cache[1001];
   
    if(read_data(3))
    {
        format(cache, 1000, "SELECT hs FROM %s WHERE name='%s';", table, name_killer);
        SQL_ThreadQuery(g_SqlX, "handle_hs", cache, name_killer, 31);
    }
    else
    {
        format(cache, 1000, "SELECT kills FROM %s WHERE name='%s';", table, name_killer);
        SQL_ThreadQuery(g_SqlX, "handle_kills", cache, name_killer, 31);
    }
   
    format(cache, 1000, "SELECT deaths FROM %s WHERE name='%s';", table, name_victim);
    SQL_ThreadQuery(g_SqlX, "handle_deaths", cache, name_victim, 31);
}

public handle_hs(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new hs = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET hs='%d' WHERE name='%s';",table, hs + 1, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_kills(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new kills = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET kills='%d' WHERE name='%s';",table, kills + 1, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_deaths(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new deaths = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET deaths='%d' WHERE name='%s';",table, deaths + 1, Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public client_disconnect(id)
{
    clientcheck[id] = 0;

    new name[32]; get_user_name(id, name, 31);
    new currentTime[32]; get_time("%d %b %Y - %H:%M", currentTime, 31);
    SQL_QuoteString(g_SqlConnection, name, 31, name);
   
    new cache[1001];
   
    format(cache, 1000, "SELECT time FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_time", cache, name, 31);
   
    format(cache, 1000, "UPDATE %s SET lastseen='%s' WHERE name='%s';",table, currentTime, name);
    SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
}

public handle_time(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new timex = SQL_ReadResult(Query, 0);
        new player = find_player("a", Data);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET time='%d' WHERE name='%s';",table, timex + get_user_time(player, 1), Data);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);   
    }
}

public DefaultHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
   
}

public plugin_end()
{
    SQL_FreeHandle(g_SqlX);
}

There must be some scripting mistakes, but I can't see where.
I don't expect you to do all the work for me, I just need some tips.

Thanks again!

ProIcons 06-11-2010 07:47

Re: SQL Stats
 
If you make the SQL Update while a Player Kills Someone else, if mysql is on another machine from your server the server will be freeze from 1 as to 5 seconds, I Suggest you to save players's stats on variables and when the Map finish or when you want with a command to update all the kills,

unnyquee 06-11-2010 08:02

Re: SQL Stats
 
Code:

L 06/11/2010 - 12:42:02: [MySQL] Invalid database handle: 0
Write:
PHP Code:

server_cmd"exec sql.cfg" );
server_exec( ); 

Before catching the amx_sql_* CVARs.
That should fix this.


All times are GMT -4. The time now is 05:20.

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