AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   MySQL prune database (https://forums.alliedmods.net/showthread.php?t=295461)

^SmileY 03-26-2017 08:09

MySQL prune database
 
hi, i am re-writing my CS-stats plugin, someone know what is the bes way to sincronize with database?
Since do a direct query i already use, i have seen in some other plugins that use a temp file in server before sync it to database.

Someone can help?

thanks.

Bugsy 03-26-2017 09:21

Re: MySQL prune database
 
The topic title and your question do not seem like the same thing. What are you trying to accomplish?

^SmileY 03-26-2017 20:11

Re: MySQL prune database
 
Quote:

Originally Posted by Bugsy (Post 2506908)
The topic title and your question do not seem like the same thing. What are you trying to accomplish?

I want to know if has a better way to synchronize a server (Like an stats plugin) with MySQL than do a directly query in a plugin. I did not remember in what plugin i have seen this (DB prune) a time ago. Or a better way to do it.

i hope that you can understand, thanks to help

Bugsy 03-27-2017 21:13

Re: MySQL prune database
 
I would just do the db calls as needed using threaded queries (SQLx). I'm still not sure of what the big picture is of your request. If you want to prune, just do a DELETE FROM with date range query.

^SmileY 03-28-2017 15:31

Re: MySQL prune database
 
is not prune, is sincronize with database some player stats. I do not know if do a direct call is the correct way.
Here is a example (With procedures), that i not know if has a better way to do it.

PHP Code:

#include <amxmodx>
#include <fakemeta>
#include <csx>
#include <sqlx>

#include <PugConst>
#include <PugStocks>
#include <PugNatives>
#include <PugForwards>
#include <PugCS>

#pragma semicolon 1

#define isPlayer(%0) (1 <= %0 <= MaxClients)

new bool:g_bStats;

new 
g_pHost;
new 
g_pUser;
new 
g_pPass;
new 
g_pDBSE;

new 
g_pRankedServer;
new 
g_pStatsURL;

enum _:eStats
{
    
eKills,
    
eAssists,
    
eDeaths,
    
eHeadshots,
    
eShots,
    
eHits,
    
eDamage,
    
Float:eRWS,
    
eRoundsPlayed,
    
eRoundsLose,
    
eRoundsWin,
    
eMatchsPlayed,
    
eMatchsLose,
    
eMatchsWin
};

new 
g_iStats[MAX_PLAYERS][eStats];

enum _:eBomb
{
    
bDefuses,
    
bDefused,
    
bPlants,
    
bExplosions
};

new 
g_iBomb[MAX_PLAYERS][eBomb];

enum _:eStreak
{
    
K1,
    
K2,
    
K3,
    
K4,
    
K5
};

new 
g_iStreak[MAX_PLAYERS][eStreak];

enum _:eVersus
{
    
V1,
    
V2,
    
V3,
    
V4,
    
V5
};

new 
g_iVersus[MAX_PLAYERS][eVersus];

enum _:eWeaponStats
{
    
wKills,
    
wDeaths,
    
wHeadshots,
    
wShots,
    
wHits,
    
wDamage
};

new 
g_iWeapon[MAX_PLAYERS][MAX_PLAYERS][eWeaponStats];

enum _:eMatch
{
    
mServer[32],
    
mAddress[23],
    
mMap[32]
};

new 
g_aMatch[eMatch];

new 
g_iKills[MAX_PLAYERS];
new 
g_iDamage[MAX_PLAYERS][MAX_PLAYERS];
new 
g_iLastManVersus[MAX_PLAYERS];
new 
g_iGunsEventsIdBitSum;

new 
Handle:g_hSQL;

public 
plugin_init()
{
    
register_plugin("Pug MOD (Stats)",PUG_MOD_VERSION,PUG_MOD_AUTHOR);
    
    
register_dictionary("PugStats.txt");
    
    
g_pHost get_cvar_pointer("pug_sql_host");
    
g_pUser get_cvar_pointer("pug_sql_user");
    
g_pPass get_cvar_pointer("pug_sql_pass");
    
g_pDBSE get_cvar_pointer("pug_sql_db");
    
    
g_pRankedServer create_cvar("pug_ranked_server","1",FCVAR_NONE,"Rank the server to database");
    
g_pStatsURL create_cvar("pug_web_url","http://localhost",FCVAR_NONE,"URL of stats pages for pug mod");
    
    new const 
sGunsEvents[][] =
    {
        
"events/awp.sc","events/g3sg1.sc","events/ak47.sc","events/scout.sc","events/m249.sc",
        
"events/m4a1.sc","events/sg552.sc","events/aug.sc","events/sg550.sc","events/m3.sc",
        
"events/xm1014.sc","events/usp.sc","events/mac10.sc","events/ump45.sc","events/fiveseven.sc",
        
"events/p90.sc","events/deagle.sc","events/p228.sc","events/glock18.sc","events/mp5n.sc",
        
"events/tmp.sc","events/elite_left.sc","events/elite_right.sc","events/galil.sc","events/famas.sc"
    
};
    
    for(new 
i;sizeof(sGunsEvents);++i)
    {
        
g_iGunsEventsIdBitSum |= << engfunc(EngFunc_PrecacheEvent,1,sGunsEvents[i]);
    }
    
    
register_forward(FM_PlaybackEvent,"FwPlaybackEvent");
    
    
PugRegisterCommand("stats","PugCommandStats",ADMIN_ALL,"PUG_DESC_STATS");
    
PugRegisterCommand("rank","PugCommandRank",ADMIN_ALL,"PUG_DESC_RANK");
    
PugRegisterCommand("match","PugCommandMatch",ADMIN_ALL,"PUG_DESC_MATCH");
}

public 
plugin_end()
{
    if(
g_hSQL != Empty_Handle)
    {
        
SQL_FreeHandle(g_hSQL);
    }
}

public 
client_putinserver(id)
{
    if(
get_pcvar_num(g_pRankedServer))
    {
        
arrayset(g_iBomb[id],0,sizeof(g_iBomb[]));
        
arrayset(g_iStats[id],0,sizeof(g_iStats[]));
        
arrayset(g_iStreak[id],0,sizeof(g_iStreak[]));
        
arrayset(g_iVersus[id],0,sizeof(g_iVersus[]));
        
        for(new 
iWeapon;iWeapon sizeof(g_iWeapon[]);iWeapon++)
        {
            
arrayset(g_iWeapon[id][iWeapon],0,sizeof(g_iWeapon[][]));
        }
    }
}

public 
client_disconnected(id)
{
    if(
get_pcvar_num(g_pRankedServer) && !is_user_bot(id) && !is_user_hltv(id))
    {
        if(
g_iStats[id][eRoundsPlayed])
        {
            
PugSaveStats(id);
        }
    }
}

public 
PugEventWarmup()
{
    new 
sHost[32],sUser[32],sPass[32],sDBSE[32];
    
    
get_pcvar_string(g_pHost,sHost,charsmax(sHost));
    
get_pcvar_string(g_pUser,sUser,charsmax(sUser));
    
get_pcvar_string(g_pPass,sPass,charsmax(sPass));
    
get_pcvar_string(g_pDBSE,sDBSE,charsmax(sDBSE));
    
    
g_hSQL SQL_MakeDbTuple(sHost,sUser,sPass,sDBSE);
    
    if(
get_pcvar_num(g_pRankedServer))
    {
        
g_bStats false;
        
        
get_mapname(g_aMatch[mMap],charsmax(g_aMatch[mMap]));
        
get_cvar_string("hostname",g_aMatch[mServer],charsmax(g_aMatch[mServer]));
        
get_cvar_string("net_address",g_aMatch[mAddress],charsmax(g_aMatch[mAddress]));
    }
}

public 
PugEventFirstHalf()
{
    if(
get_pcvar_num(g_pRankedServer))
    {
        
g_bStats true;
    }
}

public 
PugEventHalfTime()
{
    if(
get_pcvar_num(g_pRankedServer))
    {
        
g_bStats false;
    }
}

public 
PugEventSecondHalf()
{
    if(
get_pcvar_num(g_pRankedServer))
    {
        
g_bStats true;
    }
}

public 
PugEventOvertime()
{
    if(
get_pcvar_num(g_pRankedServer))
    {
        
g_bStats true;
    }
}

public 
PugEventEnd(iWinner)
{
    if(
get_pcvar_num(g_pRankedServer))
    {
        
g_bStats false;
    
        new 
iTeam;
        
        for(new 
1;<= MaxClients;i++)
        {
            if(
is_user_connected(i))
            {
                
iTeam get_user_team(i);
                
                if(
<= iTeam <= 2)
                {
                    
g_iStats[i][eMatchsPlayed]++;
                    
                    if(
iTeam == iWinner)
                    {
                        
g_iStats[i][eMatchsWin]++;
                    }
                    else
                    {
                        
g_iStats[i][eMatchsLose]++;
                    }
                }
            }
        }
        
        new 
sQuery[256];
        
        
format
        
(
            
sQuery,
            
charsmax(sQuery),
            
"CALL PugSaveMatch('%s', '%s', '%s', %i, %i, %f)",
            
g_aMatch[mServer],
            
g_aMatch[mAddress],
            
g_aMatch[mMap],
            
PugGetTeamScore(1),
            
PugGetTeamScore(2),
            
get_gametime()
        );
        
        
SQL_ThreadQuery(g_hSQL,"PugHandlerSQL",sQuery);
    }
}

public 
client_death(iKiller,iVictim,iWeapon,iPlace,TK)
{
    if(
g_bStats)
    {
        if(
iKiller != iVictim)
        {
            
g_iStats[iKiller][eKills]++;
            
g_iStats[iVictim][eDeaths]++;
    
            
g_iWeapon[iKiller][iWeapon][wKills]++;
            
g_iWeapon[iVictim][iWeapon][wDeaths]++;
            
            
g_iKills[iKiller]++;
            
            if(
iPlace == HIT_HEAD)
            {
                
g_iStats[iKiller][eHeadshots]++;
                
g_iWeapon[iKiller][iWeapon][wHeadshots]++;
            }
        }
        
        for(new 
1;<= MaxClients;i++)
        {
            if(
is_user_connected(i))
            {
                if((
g_iDamage[i][iVictim] >= 50) && (!= iKiller))
                {
                    
g_iStats[i][eAssists]++;
                }
                else
                {
                    
g_iDamage[i][iVictim] = 0;
                }
                
                if(
IsAlone(i) && !g_iLastManVersus[i])
                {
                    
g_iLastManVersus[i] = GetAliveEnemies(i);
                }
            }
        }
    }
}

public 
client_damage(iAttacker,iVictim,iDamage,iWeapon,iPlace,iTA)
{
    if(
g_bStats && (iAttacker != iVictim))
    {
        
g_iStats[iAttacker][eHits]++;
        
g_iStats[iAttacker][eDamage] += iDamage;
        
        
g_iDamage[iAttacker][iVictim] += iDamage;
        
        
g_iWeapon[iAttacker][iWeapon][wHits]++;
        
g_iWeapon[iAttacker][iWeapon][wDamage] += iDamage;
    }
}

public 
FwPlaybackEvent(iFlags,id,iEvent)
{
    if(
g_bStats && isPlayer(id) && (g_iGunsEventsIdBitSum & (<< iEvent)))
    {
        
g_iStats[id][eShots]++;
        
g_iWeapon[id][get_user_weapon(id)][wShots]++;
    }
}

public 
bomb_planted(iPlanter)
{
    if(
g_bStats)
    {
        
g_iBomb[iPlanter][bPlants]++;
    }
}

public 
bomb_defusing(iDefuser)
{
    if(
g_bStats)
    {
        
g_iBomb[iDefuser][bDefuses]++;
    }
}
public 
bomb_defused(iDefuser)
{
    if(
g_bStats)
    {
        
g_iBomb[iDefuser][bDefused]++;
    }
}

public 
bomb_explode(iPlanter,iDefuser)
{
    if(
g_bStats)
    {
        
g_iBomb[iPlanter][bExplosions]++;
    }
}

public 
PugEventRoundStart()
{
    if(
g_bStats)
    {
        
arrayset(g_iKills,0,sizeof(g_iKills));
        
arrayset(g_iLastManVersus,0,sizeof(g_iLastManVersus));
        
        for(new 
i;sizeof(g_iDamage);i++)
        {
            
arrayset(g_iDamage[i],0,sizeof(g_iDamage[]));
        }
    }
}

public 
PugEventRoundWinner(iWinner)
{
    if(
g_bStats)
    {
        new 
i,iTeam[MAX_PLAYERS],iStats[8],iBody[8],iTeamDamage,iDamageDone[MAX_PLAYERS];
        
        for(
1;<= MaxClients;i++)
        {
            if(
is_user_connected(i))
            {
                
iTeam[i] = get_user_team(i);
                
                if(
<= iTeam[i] <= 2)
                {
                    if(
iTeam[i] == iWinner)
                    {
                        
g_iStats[i][eRoundsWin]++;
        
                        if(
IsAlone(i))
                        {
                            
g_iVersus[i][g_iLastManVersus[i]]++;
                        }
                        
                        
get_user_rstats(i,iStats,iBody);
                        
iTeamDamage += (iDamageDone[i] = iStats[6]);
                    }
                    else
                    {
                        
g_iStats[i][eRoundsLose]++;
                    }
                
                    
g_iStats[i][eRoundsPlayed]++;
                    
                    if(
g_iKills[i])
                    {
                        
g_iStreak[i][g_iKills[i]]++;
                    }
                }
            }
        }
     
        for(
1;<= MaxClients;i++)
        {
            if(
is_user_connected(i) && (iTeam[i] == iWinner))
            {
                
g_iStats[i][eRWS] += float(iDamageDone[i]) / float(iTeamDamage);
            }
        }
    }
}

bool:IsAlone(id)
{
    if(
is_user_alive(id))
    {
        new 
sTeam[12];
        
        if(
<= get_user_team(id,sTeam,charsmax(sTeam)) <= 2)
        {
            new 
iPlayers[32],iNum;
            
get_players(iPlayers,iNum,"ae",sTeam);
            
            return (
iNum == 1) ? true false;
        }
    }

    return 
false;
}

GetAliveEnemies(id)
{
    if(
is_user_alive(id))
    {
        new 
iPlayers[32],iNum;
        
get_players(iPlayers,iNum,"ae",(get_user_team(id) == 1) ? "CT" "TERRORIST");
    
        return 
iNum;
    }

    return -
1;
}

PugSaveStats(id)
{
    new 
sQuery[1536];
    
    new 
sSteam[35];
    
get_user_authid(id,sSteam,charsmax(sSteam));
    
    
format
    
(
        
sQuery,
        
charsmax(sQuery),
        
"CALL PugSaveStats('%s', %i, %i, %i, %i, %i, %i, %i, %f, %i, %i, %i, %i, %i, %i)",
        
sSteam,
        
g_iStats[id][eKills],
        
g_iStats[id][eAssists],
        
g_iStats[id][eDeaths],
        
g_iStats[id][eHeadshots],
        
g_iStats[id][eShots],
        
g_iStats[id][eHits],
        
g_iStats[id][eDamage],
        
g_iStats[id][eRWS],
        
g_iStats[id][eRoundsPlayed],
        
g_iStats[id][eRoundsLose],
        
g_iStats[id][eRoundsWin],
        
g_iStats[id][eMatchsPlayed],
        
g_iStats[id][eMatchsLose],
        
g_iStats[id][eMatchsWin]
    );
    
    
format
    
(
        
sQuery,
        
charsmax(sQuery),
        
"%s;CALL PugSaveBomb('%s', %i, %i, %i, %i)",
        
sQuery,
        
sSteam,
        
g_iBomb[id][bDefuses],
        
g_iBomb[id][bDefused],
        
g_iBomb[id][bPlants],
        
g_iBomb[id][bExplosions]
    );
    
    
format
    
(
        
sQuery,
        
charsmax(sQuery),
        
"%s;CALL PugSaveStreak('%s', %i, %i, %i, %i, %i)",
        
sQuery,
        
sSteam,
        
g_iStreak[id][K1],
        
g_iStreak[id][K2],
        
g_iStreak[id][K3],
        
g_iStreak[id][K4],
        
g_iStreak[id][K5]
    );
    
    
format
    
(
        
sQuery,
        
charsmax(sQuery),
        
"%s;CALL PugSaveVersus('%s', %i, %i, %i, %i, %i)",
        
sQuery,
        
sSteam,
        
g_iVersus[id][V1],
        
g_iVersus[id][V2],
        
g_iVersus[id][V3],
        
g_iVersus[id][V4],
        
g_iVersus[id][V5]
    );
    
    
format(sQuery,charsmax(sQuery),"%s;CALL PugCalcStats('%s')",sQuery,sSteam);

    
SQL_ThreadQuery(g_hSQL,"PugHandlerSQL",sQuery);
    
    
sQuery "^0";
    new 
sWeapon[32];
    
    for(new 
iWeapon;iWeapon sizeof(g_iWeapon[]);iWeapon++)
    {
        if(
g_iWeapon[id][iWeapon][wShots] || g_iWeapon[id][iWeapon][wDeaths])
        {
            
get_weaponname(iWeapon,sWeapon,charsmax(sWeapon));
            
            
format
            
(
                
sQuery,
                
charsmax(sQuery),
                
"CALL PugSaveWeapon(%i, '%s', %i, %i, %i, %i, %i, %i, '%s');%s",
                
iWeapon,
                
sWeapon,
                
g_iWeapon[id][iWeapon][wKills],
                
g_iWeapon[id][iWeapon][wDeaths],
                
g_iWeapon[id][iWeapon][wHeadshots],
                
g_iWeapon[id][iWeapon][wShots],
                
g_iWeapon[id][iWeapon][wHits],
                
g_iWeapon[id][iWeapon][wDamage],
                
sSteam,
                
sQuery
            
);
        }
    }
    
    if(
sQuery[0])
    {
        
SQL_ThreadQuery(g_hSQL,"PugHandlerSQL",sQuery);
    }
}

public 
PugHandlerSQL(iState,Handle:hQuery,sError[],iError,sData[],iData)
{
    if(
iState != TQUERY_SUCCESS)
    {
        if(
iError)
        {
            
server_print(sError);
        }
    }
    
    
SQL_FreeHandle(hQuery);
}

public 
PugCommandStats(id)
{
    new 
sAlias[35];
    
read_args(sAlias,charsmax(sAlias));
    
remove_quotes(sAlias);
    
    if(!
sAlias[0])
    {
        
get_user_authid(id,sAlias,charsmax(sAlias));
    }
    
    new 
sURL[128];
    
get_pcvar_string(g_pStatsURL,sURL,charsmax(sURL));
    
    
format(sURL,charsmax(sURL),"%s/stats.php?Alias=%s",sURL,sAlias);
    
    
show_motd(id,sURL,sAlias);
    
    return 
PLUGIN_HANDLED;
}

public 
PugCommandRank(id)
{
    new 
sTitle[32];
    
format(sTitle,charsmax(sTitle),"%L",LANG_PLAYER,"PUG_MOTD_TOP");
    
    new 
sURL[128];
    
get_pcvar_string(g_pStatsURL,sURL,charsmax(sURL));
    
    
add(sURL,charsmax(sURL),"/top.php");
    
    
show_motd(id,sURL,sTitle);
    
    return 
PLUGIN_HANDLED;
}

public 
PugCommandMatch(id)
{
    new 
sTitle[32];
    
format(sTitle,charsmax(sTitle),"%L",LANG_PLAYER,"PUG_MOTD_MATCH");
    
    new 
sURL[128];
    
get_pcvar_string(g_pStatsURL,sURL,charsmax(sURL));
    
    
add(sURL,charsmax(sURL),"/match.php");
    
    
show_motd(id,sURL,sTitle);

    return 
PLUGIN_HANDLED;



Bugsy 03-30-2017 17:36

Re: MySQL prune database
 
Your plugin basically starts everyone at 0 every time they connect. You should call a Load procedure at client_authorized() to get their previous stats.

^SmileY 03-30-2017 18:02

Re: MySQL prune database
 
Quote:

Originally Posted by Bugsy (Post 2508060)
Your plugin basically starts everyone at 0 every time they connect. You should call a Load procedure at client_authorized() to get their previous stats.

I am not needed to load stats on every connect, since i use a external php script in a website. And display using motd and parameters in url to display specific stats.

The problem is a better way to save it. i am thinking on a way that store match and when server changelevel upload player stats (Not only when player disconnect it)

Thanks for read.

Bugsy 03-30-2017 18:14

Re: MySQL prune database
 
But you do realize your db will only show data for a person's single connection to your server. Shouldn't this show overall stats over time (cumulative)?

Hectik17 03-30-2017 19:18

Re: MySQL prune database
 
HEY MAN i would be interested in this plugin, do you have links to resource? what im really in need of is a way to get player match data IE pug match win/loss in a team. dint meant to jack this, but Ive been in search of it for a wile now.

^SmileY 03-31-2017 08:16

Re: MySQL prune database
 
Quote:

Originally Posted by Bugsy (Post 2508067)
But you do realize your db will only show data for a person's single connection to your server. Shouldn't this show overall stats over time (cumulative)?

I did not understand you question, i only using a webpage to display match, top and player stats :D
Is cumulative, in dbo have procedures and tables to store in cumulative way. I do not need to retrieve stats to count.

Quote:

Originally Posted by Hectik17 (Post 2508082)
HEY MAN i would be interested in this plugin, do you have links to resource? what im really in need of is a way to get player match data IE pug match win/loss in a team. dint meant to jack this, but Ive been in search of it for a wile now.

Look at my github bro :D


All times are GMT -4. The time now is 18:01.

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