AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   nVault to MySQL on map load/change (https://forums.alliedmods.net/showthread.php?t=293225)

imindfreak 01-26-2017 01:06

nVault to MySQL on map load/change
 
Greetings,

I have a question in regards to saving data methods. I'm running a mod that saves xp data via nvault. Previously, I used the mod's MySQL saving method but it seems to cause a lag / delay when a user's xp/lvl is saved so I'm sticking to nvault. However, I was wondering if there's a way to dump/convert/export an nvault into a specific database table, preferably on a map load/change.

The reason why is because I want to be able to call PHP functions in order to retrieve data stored in that table/columns and format them accordingly to have a stats page on the web browser. Examples include retrieving the top 5 players and having a nicely formatted top 5 page that can be accessed via a web browser (ex - www.****.com/top5.php). I understand there's a client-side MOTD that can be called to display the same information that retrieves data stored in nvault and displays it via MOTD but I want to go beyond that using MySQL/PHP.

TL;DR - How can you convert/export nvault file to a MySQL database table?

JusTGo 01-26-2017 05:37

Re: nVault to MySQL on map load/change
 
check this thread https://forums.alliedmods.net/showthread.php?t=207922

imindfreak 01-27-2017 00:54

Re: nVault to MySQL on map load/change
 
Quote:

Originally Posted by JusTGo (Post 2490002)

Thanks, that definitely helped with the example plugin and it worked for what I needed. However, it only dumped about 5 entries (which is sufficient, but the whole nvault would be even better).

JusTGo 01-27-2017 05:42

Re: nVault to MySQL on map load/change
 
Quote:

Originally Posted by imindfreak (Post 2490212)
Thanks, that definitely helped with the example plugin and it worked for what I needed. However, it only dumped about 5 entries (which is sufficient, but the whole nvault would be even better).

show an example ?

imindfreak 01-27-2017 16:31

Re: nVault to MySQL on map load/change
 
Quote:

Originally Posted by JusTGo (Post 2490233)
show an example ?

I have edited the example plugin below. It will truncate the table and then populate it on plugin_init. This works fine but it only pulls 5 entries from the vault. I have attached the vault.

The MySQL table (Pokerank) - columns include "RankNumber" and "Info".
http://puu.sh/tCXN2/d563c9e9b7.png

Plugin code :
PHP Code:

// Here is an exampe.(The code about reading nvault data is copied from Bugsy)
#include <amxmodx>
#include <amxmisc>
#include <nvault>
#include <sqlx>

#define PLUGIN_NAME    "nVault2MySQL"
#define PLUGIN_VERSION    "1.0"
#define PLUGIN_AUTHOR    "Csoldjb"


new const szNURL[]="addons/amxmodx/data/vault/pm_save.vault" 


// BlackList
// Sheet format
/*
    BlackName    Info
    
    sbbaobao    sb123
    hzqst        456
    ...        ...
*/


new Handle:hDbTuple

new g_ERROR[512



const 
MaxKeyLen  1504
const MaxValLen  1504
const DataBuffer 376  



public plugin_init()
{
    
register_plugin(PLUGIN_NAMEPLUGIN_VERSIONPLUGIN_AUTHOR);
    
    
hDbTuple=SQL_MakeDbTuple("127.0.0.1","root","password","database"
    
    new 
iErrorCode
    
new Handle:hSqlConnection SQL_Connect(hDbTuple,iErrorCode,g_ERROR,511
    
    if(
hSqlConnection == Empty_Handle)
    {
        
console_print(1,"[SQL]SQL Connect Failed!")
        
set_fail_state("SQL Connect Failed!")
    }
    
SQL_FreeHandle(hSqlConnection

    
set_task(3.0"nvaultsql")    
}
public 
nvaultsql(id)
{
    
client_print(1,print_chat,"[ID:%i] MySQL Test Started",id)
    
    new 
iErrorCode
    
new Handle:hSqlConnection SQL_Connect(hDbTuple,iErrorCode,g_ERROR,511)
    
    if(
hSqlConnection == Empty_Handle)
    {
        
console_print(1,"[SQL]SQL Connect Failed!")
        
set_fail_state("SQL Connect Failed!")
    }
    
    new 
iFile
    
new iVaultMagic iVaultVersion iVaultEntries
    
new iKeyLen iValLen iTimeStamp
    
new szKeyMaxKeyLen ] , szValMaxValLen ] , RawDataDataBuffer ]
    
    
iFile fopen(szNURL,"rb")
    if ( !
iFile )
    {
        
client_print(1,print_chat,"[nVault]File Read Failed!")
        return
    }
    

    
fread_rawiFile RawData BLOCK_INT)
    
    
iVaultMagic RawData[0]
    if ( 
iVaultMagic != 0x6E564C54 )
    {
        
client_print(1,print_chat,"[nVault]Magic Vault!")
        return
    }
    
    
fread_rawiFile RawData BLOCK_SHORT )
    
iVaultVersion RawData[0] & 0xFFFF
    
if ( iVaultVersion != 0x0200 )
    {
        
client_print(1,print_chat,"[nVault]Vault Version!")
        return
    }
    
    
    
fread_rawiFile RawData BLOCK_INT )
    
iVaultEntries RawData[0]
    
    
client_print(1,print_chat,"[nVault] Entries Found:%d",iVaultEntries)
    
    new 
Handle:QueryTruncate
    
new szTruncate[25]
        
format (szTruncate24"TRUNCATE table Pokerank"
        
QueryTruncate SQL_PrepareQuery(hSqlConnection,szTruncate)
        if(!
SQL_Execute(QueryTruncate))
        {
            
client_print(1,print_chat,"[DATA] Convery Failed!")
        }
        
client_print(1,print_chat,"[DATA] Convery Success!")
        
SQL_FreeHandle(QueryTruncate)
    
    for ( new 
iEntry iEntry iVaultEntries iEntry++ )
    {
        
fread_rawiFile RawData BLOCK_INT )
        
iTimeStamp RawData]
        
        
fread_rawiFile RawData BLOCK_BYTE )
        
iKeyLen RawData] & 0xFF
        
        fread_raw
iFile RawData BLOCK_SHORT )
        
iValLen RawData] & 0xFFFF
        
        fread_raw
iFile RawData iKeyLen BLOCK_CHAR )
        
ReadStringszKey iKeyLen charsmaxszKey ) , RawData )
    
        
fread_rawiFile RawData iValLen BLOCK_CHAR );
        
ReadStringszVal iValLen charsmaxszVal ) , RawData )

        
//server_print( "Entry=%d KeyLen=%d ValLen=%d TimeStamp=%d" , iEntry , iKeyLen , iValLen , iTimeStamp )
        
        
client_print(1,print_chat,"[DATA][Key:%s][Value:%s]",szKey,szVal)
        
        
        new 
Handle:Query
        
new szQuery[256]
        
        
        
format(szQuery,255,"INSERT Pokerank VALUES (^'%s^',^'%s^')"szKeyszVal)
        
        
Query SQL_PrepareQuery(hSqlConnection,szQuery)

        if(!
SQL_Execute(Query))
        {
            
client_print(1,print_chat,"[DATA] Convery Failed!")
            continue
        }
        
client_print(1,print_chat,"[DATA] Convery Success!")
        
SQL_FreeHandle(Query)
    }
    
    
fcloseiFile )
}
stock ReadStringszDestString[] , iLen iMaxLen SourceData[] )
{
    new 
iStrPos = -1;
    new 
iRawPos 0;
    
    while ( ( ++
iStrPos iLen ) && ( iStrPos iMaxLen ) && ( iRawPos DataBuffer ) )
    {
        
szDestStringiStrPos ] = ( SourceDataiRawPos ] >> ( ( iStrPos ) * ) ) & 0xFF;
        
        if ( 
iStrPos && ( ( iStrPos ) == ) ) iRawPos++
    }
    
    
szDestStringiStrPos ] = EOS


As you can see, the vault has many entries. Only PokeMod_Rank0-4 are added to the database table. Any help with this would be appreciated. Thanks!

Bugsy 01-27-2017 17:56

Re: nVault to MySQL on map load/change
 
Code:

// BlackList
// Sheet format
/*
    BlackName    Info
   
    sbbaobao    sb123
    hzqst        456
    ...        ...
*/

Assuming your field names are 'BlockName' and 'Info' within table 'Pokerank' in your db file 'database', this should work. The only issue you're going to run into is duplicates, so you will need to clear your table with each map change, too, before exporting nVault. For that, just run a query "DELETE FROM Pokerank;"

Edit: Will not export data with keys beginning with '[BOT]' (case sensitive)
PHP Code:



#include <amxmodx>
#include <nvault_util>
#include <sqlx>

new const Version[] = "0.1";

#define IsBotData(%1)    (%1[0]=='[' && %1[1]=='B' && %1[2]=='O' && %1[3]=='T' && %1[4]==']')

public plugin_init()
{
    
register_plugin"nVault to SQL Export" Version "bugsy" );
    
    
ExportData();
}
    
public 
ExportData()
{
    new 
iVault iDBInfo] , szError128 ];
    new 
Handle:hDbTuple Handle:hSqlConnection Handle:hQuery iErrorCode;
    
    
hDbTuple SQL_MakeDbTuple"127.0.0.1" "root" "password" "database" );
    
    
hSqlConnection SQL_ConnecthDbTuple iErrorCode szError charsmaxszError ) );
    
    if ( 
hSqlConnection == Empty_Handle )
    {
        
set_fail_state("SQL Connect Failed!");
    }
    
    
hQuery SQL_PrepareQueryhSqlConnection "DELETE FROM Pokerank;" );
    
SQL_ExecutehQuery );
    
SQL_FreeHandlehQuery );
    
    
iVault nvault_util_open"pm_save" );
    
    
iDBInfo] = _:hSqlConnection;
    
    
nvault_util_readalliVault "nVault_Export" iDBInfo sizeofiDBInfo ) );
    
nvault_util_closeiVault );
    
SQL_FreeHandlehDbTuple );
}

public 
nVault_ExportiCurrentRow iTotalRows , const szKey[] , const szVal[] , iTimeStamp iData[] )
{
    static 
szQuery256 ] , Handle:hQuery;
    
    if ( !
IsBotDataszKey ) )
    {
        
formatexszQuery charsmaxszQuery ) , "INSERT INTO Pokerank (BlockName,Info) VALUES (^"%s^",^"%s^");" szKey szVal );
        
hQuery SQL_PrepareQueryHandle:iData] , szQuery );
        
SQL_ExecutehQuery );
        
SQL_FreeHandlehQuery );
    }


I loaded test data into a vault using name1-name5 as BlockName and data1-data5 as Info and it loaded into the database:
https://dl.dropboxusercontent.com/u/...X/nVExport.PNG

imindfreak 01-27-2017 21:59

Re: nVault to MySQL on map load/change
 
1 Attachment(s)
Quote:

Originally Posted by Bugsy (Post 2490386)
Code:

// BlackList
// Sheet format
/*
    BlackName    Info
   
    sbbaobao    sb123
    hzqst        456
    ...        ...
*/

Assuming your field names are 'BlockName' and 'Info' within table 'Pokerank' in your db file 'database', this should work. The only issue you're going to run into is duplicates, so you will need to clear your table with each map change, too, before exporting nVault. For that, just run a query "DELETE FROM Pokerank;"

PHP Code:


#include <amxmodx>
#include <nvault_util>
#include <sqlx>

new const Version[] = "0.1";

public 
plugin_init()
{
    
register_plugin"nVault to SQL Export" Version "bugsy" );
    
    
ExportData();
}
    
public 
ExportData()
{
    new 
iVault iDBInfo] , szError128 ];
    new 
Handle:hDbTuple Handle:hSqlConnection Handle:hQuery iErrorCode;
    
    
hDbTuple SQL_MakeDbTuple"127.0.0.1" "root" "password" "database" );
    
    
hSqlConnection SQL_ConnecthDbTuple iErrorCode szError charsmaxszError ) );
    
    if ( 
hSqlConnection == Empty_Handle )
    {
        
set_fail_state("SQL Connect Failed!");
    }
    
    
hQuery SQL_PrepareQueryhSqlConnection "DELETE FROM Pokerank;" );
    
    
SQL_ExecutehQuery );
    
    
SQL_FreeHandlehQuery );
    
    
iVault nvault_util_open"pm_save" );
    
    
iDBInfo] = _:hSqlConnection;
    
    
nvault_util_readalliVault "nVault_Export" iDBInfo sizeofiDBInfo ) );
    
    
nvault_util_closeiVault );
    
    
SQL_FreeHandlehDbTuple );
}

public 
nVault_ExportiCurrentRow iTotalRows , const szKey[] , const szVal[] , iTimeStamp iData[] )
{
    static 
szQuery256 ] , Handle:hQuery;
    
    
formatexszQuery charsmaxszQuery ) , "INSERT INTO Pokerank (BlockName,Info) VALUES (^"%s^",^"%s^");" szKey szVal );
    
    
hQuery SQL_PrepareQueryHandle:iData] , szQuery );

    
SQL_ExecutehQuery );

    
SQL_FreeHandlehQuery );


I loaded test data into a vault using name1-name5 as BlockName and data1-data5 as Info and it loaded into the database:
https://dl.dropboxusercontent.com/u/...X/nVExport.PNG

Sorry for not attaching the vault on the earlier post. Not sure why this didn't work. It did however truncate/delete the table. The only thing I modified was BlockNumber into RankNumber, as that's how it's listed in my table. Also, my first column is RankNumber and second column is Info, both being VARCHAR (I used this datatype because some nvault entry values are longer than 255 characters).

This is the structure of the table named 'Pokerank' with columns RankNumber and Info in database called 'database'.
http://puu.sh/tDkn9/6b13cfed64.png

PHP Code:

#include <amxmodx>
#include <nvault_util>
#include <sqlx>

new const Version[] = "0.1";

public 
plugin_init()
{
    
register_plugin"nVault to SQL Export" Version "bugsy" );
    
    
ExportData();
}
    
public 
ExportData()
{
    new 
iVault iDBInfo] , szError128 ];
    new 
Handle:hDbTuple Handle:hSqlConnection Handle:hQuery iErrorCode;
    
    
hDbTuple SQL_MakeDbTuple"127.0.0.1" "root" "password" "database" );
    
    
hSqlConnection SQL_ConnecthDbTuple iErrorCode szError charsmaxszError ) );
    
    if ( 
hSqlConnection == Empty_Handle )
    {
        
set_fail_state("SQL Connect Failed!");
    }
    
    
hQuery SQL_PrepareQueryhSqlConnection "DELETE FROM Pokerank;" );
    
    
SQL_ExecutehQuery );
    
    
SQL_FreeHandlehQuery );
    
    
iVault nvault_util_open"pm_save" );
    
    
iDBInfo] = _:hSqlConnection;
    
    
nvault_util_readalliVault "nVault_Export" iDBInfo sizeofiDBInfo ) );
    
    
nvault_util_closeiVault );
    
    
SQL_FreeHandlehDbTuple );
}

public 
nVault_ExportiCurrentRow iTotalRows , const szKey[] , const szVal[] , iTimeStamp iData[] )
{
    static 
szQuery256 ] , Handle:hQuery;
    
    
formatexszQuery charsmaxszQuery ) , "INSERT INTO Pokerank (RankNumber,Info) VALUES (^"%s^",^"%s^");" szKey szVal );
    
    
hQuery SQL_PrepareQueryHandle:iData] , szQuery );

    
SQL_ExecutehQuery );

    
SQL_FreeHandlehQuery );


I'm going to attach the vault I would like to be exported so you can see how the vault is structured. Thanks for your help Bugsy.

Bugsy 01-27-2017 22:58

Re: nVault to MySQL on map load/change
 
Query string is too small, change static szQuery[ 256 ] to static szQuery[ 1024 ] in nVault_Export().

Your data looks like it's a mix of names/steam-id's/and other things as the key. How exactly are you organizing/saving data in your plugin/vault?

PartialCloning 01-28-2017 08:16

Re: nVault to MySQL on map load/change
 
Quote:

Originally Posted by imindfreak (Post 2490364)
As you can see, the vault has many entries. Only PokeMod_Rank0-4 are added to the database table. Any help with this would be appreciated. Thanks!

In the vault file I only see PokeMod_Rank0-4 (5 total). You also seem to be saving bot data, doesn't make sense.

imindfreak 01-28-2017 19:40

Re: nVault to MySQL on map load/change
 
Quote:

Originally Posted by Bugsy (Post 2490437)
Query string is too small, change static szQuery[ 256 ] to static szQuery[ 1024 ] in nVault_Export().

Your data looks like it's a mix of names/steam-id's/and other things as the key. How exactly are you organizing/saving data in your plugin/vault?

I changed it to 1024, 9999, and 99999 to test but unfortunately no good.
I'm going to post below a snippet of the code for the saving method used from PokeMod (xp.inl).
It saves all of the player's pokemon numbers followed by the XP for those pokemon.
Afterwards, there is a colon as a separator added and then the current timestamp followed by another colon as a separator.
It will then save their HUD position settings. Another colon and finally their pokerank number.

Example vault entry (the key is the player's SteamID. If player is a bot, then [Bot]Name).

Key :
PHP Code:

STEAM_0:0:12345678 

Value :
PHP Code:

155 32 296 1 16 1 163 1 19 1 369 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1485534210 18 0 

Example Pokemon # followed by XP.
155 32 296 1 16 1 163 1 19 1 369 1
# XP # XP # XP # XP # XP # XP.
The zero's are empty slots because a player can hold 100 pokemon.
1485534210 is the time stamp. 18 0 is the HUD X and Y position. 9 is their Pokerank number.


xp.inl snippet :
PHP Code:

 public load_all()
 {
    for( new 
x=0MAX_PLAYERSx++ )
        
LoadXP(x);
    
#if POKERANK_SYSTEM==1
        
LoadRanks()
    
#endif
 
}
 
 public 
save_all()
 {
    if( !
PM_Loaded )
        return;

    
poke_print(0print_center"Saving All Data");
    new 
players[MAX_PLAYERS], pnum;
    
poke_get_players(playerspnum);
    for( new 
i=0i<pnumi++)
        
set_task1.5*(i+1), "delayed_save"players[i]);

    
#if POKERANK_SYSTEM==1
        
SaveRanks();
    
#endif
 
}
 public 
delayed_save(id)
    
SaveXP(id, -1);

 
SaveXP(idforced=1)
 {
    if(
g_isWild[id]>0)
        return 
false;

    static 
key[35]
    static 
Float:last_save[MAX_PLAYERS+1], Float:time_leftFloat:current_time;

    if( 
id ){
        
current_time get_gametime();
        
time_left current_time last_save[id];

        if( 
forced && time_left SAVE_SPAM){
            if( !
forced )
                
poke_printlang(id,"POKEMOD_SAVESPAM"SAVE_SPAM-time_left);
            return 
false;
        }
        
last_save[id] = current_time;
    }

    if( 
Pokemod_Connected(id) && getSaveKey(idkey) && SaveXPon() )
    {
        
RefreshPoke(id)

        new 
Data[1501], iLen 0i

        
//save their pokemon and xp
        
for(0i<MAX_POKESi++)
            
iLen += formatex(Data[iLen],(1501-iLen),"%d %d "PlayersPoke[id][i], PlayersXP[id][i])

        
//save the time
        
iLen += copy(Data[iLen],(1501-iLen),": ")
        
iLen += formatex(Data[iLen],(1501-iLen),"%d ",get_systime())
        
iLen += copy(Data[iLen],(1501-iLen),": ")

        
//save their settings
        
for(0i<MAX_SETSi++)
            
iLen += formatex(Data[iLen],(1501-iLen),"%d "PlayersSet[id][i])

        
//save their pokerank crap
        
iLen += formatex(Data[iLen],(1501-iLen),": %d",PlayersCatch[id])

        
writeData(idkeyData)

        
debugMessage(id,5,"XP""saved data with save key %s"key)
        return 
true;
    }
    return 
false;
 }

 
#if SAVE_ID != 0
 
public client_infochanged(id)
 {
    new 
savekey[35], newname[NAME_LENGTH];
    
getSaveKey(idsavekey);
    
get_user_info(id"name"newnameNAME_LENGTH-1);
    if( !
equal(savekeynewname) ){
        
SaveXP(id);
        
LoadXP(id);
    }
 }
 
#endif
 //thanks to SHmod
 
getSaveKey(idkey[35]=""bool:update=false)
 {
    static 
player_key[MAX_PLAYERS+1][35];
    if( 
update ) {
        
#if SAVE_ID == 0
        
if(is_user_bot(id)) {
            new 
botname[32]
            
get_user_name(id,botname,31)

            
//Get Rid of BOT Tag

            //PODBot
            
replace(botname,31,"[POD]",EMPTY)
            
replace(botname,31,"[P*D]",EMPTY)
            
replace(botname,31,"[P0D]",EMPTY)

            
//CZ Bots
            
replace(botname,31,"[BOT] ",EMPTY)

            
//Attempt to get rid of the skill tag so we save with bots true name
            
new lastchar strlen(botname) - 1
            
if ( equal(botname[lastchar],")",1) ) {
                new 
x
                
for ( lastchar 10x--) {
                    if ( 
equal(botname[x],"(",1) ) {
                        
botname[1] = 0
                        
break
                    }
                    if ( !
isdigit(botname[x]) ) break
                }
            }
            if (
strlen(botname) > ) {
                
#if SAVE==SAVE_MYSQL
                
replace_all(botname,31,"`","\\`")
                
replace_all(botname,31,"'","\\'")
                
#endif

                
replace_all(botname,31," ","_")
                
formatexplayer_key[id], 34"[BOT]%s"botname )
            }
        }
        
//Hack for STEAM's retardedness with listen servers
        
else if (!is_dedicated_server() && id == 1) {
            
copyplayer_key[id], 34"loopback" )
        }
        else {
            if (
get_pcvar_num(sv_lan) == 1) {
                
get_user_ipidplayer_key[id], 34)        // by ip without port
            
}
            else {
                
get_user_authididplayer_key[id], 34 )        // by steamid
                
if( equali(player_key[id],"STEAM_ID_LAN") || equali(player_key[id],"4294967295") ){
                    
get_user_ipidplayer_key[id], 34)        // by ip without port
                
}
            }
        }
        
#else
        
get_user_nameidplayer_key[id], 34 )
        
#endif
        
debugMessage(id,5,"XP""updated savekey");
    }

    
//Check to make sure we got something useable
    
if( equali(player_key[id], "STEAM_ID_PENDING") || equali(player_key[id], EMPTY) ){
        
debugMessage(id,5,"XP""does not have a valid savekey ('%s')"player_key[id]);
        return 
false;
    }

    
copykey34player_key[id] );
    return 
true;
 } 

Whole xp.inl code :
https://github.com/Liquidbullets/AMX...pokemon/xp.inl

Hope this clears up any questions. Thank you again Bugsy.

Quote:

Originally Posted by PartialCloning (Post 2490518)
In the vault file I only see PokeMod_Rank0-4 (5 total). You also seem to be saving bot data, doesn't make sense.

PartialCloning, bot data is saved due to bots having XP in PokeMod.
The vault appears to have 46 entries. Some player data, some bot data, and ranking data.
http://puu.sh/tEAUH/d81120685f.png


All times are GMT -4. The time now is 21:04.

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