Raised This Month: $ Target: $400
 0% 

SQL Stats


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 06-11-2010 , 05:57   Re: SQL Stats
Reply With Quote #1

Quote:
Originally Posted by portocala View Post
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 View Post
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.
__________________
Impossible is Nothing
Sylwester is offline
portocala
Member
Join Date: Jun 2010
Old 06-11-2010 , 07:31   Re: SQL Stats
Reply With Quote #2

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!
portocala is offline
Reply



Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


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


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