nVault to MySQL on map load/change

imindfreak · **Author**

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 · *Last edited by imindfreak; 01-27-2017 at 00:54.*

Quote:

Originally Posted by JusTGo

check this thread https://forums.alliedmods.net/showthread.php?t=207922

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

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 · *Last edited by imindfreak; 01-27-2017 at 16:32.*

Quote:

Originally Posted by JusTGo

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_NAME, PLUGIN_VERSION, PLUGIN_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 szKey[ MaxKeyLen + 1 ] , szVal[ MaxValLen + 1 ] , RawData[ DataBuffer ]
    
    iFile = fopen(szNURL,"rb")
    if ( !iFile )
    {
        client_print(1,print_chat,"[nVault]File Read Failed!")
        return
    }
    

    fread_raw( iFile , RawData , 1 , BLOCK_INT)
    
    iVaultMagic = RawData[0]
    if ( iVaultMagic != 0x6E564C54 )
    {
        client_print(1,print_chat,"[nVault]Magic Vault!")
        return
    }
    
    fread_raw( iFile , RawData , 1 , BLOCK_SHORT )
    iVaultVersion = RawData[0] & 0xFFFF
    if ( iVaultVersion != 0x0200 )
    {
        client_print(1,print_chat,"[nVault]Vault Version!")
        return
    }
    
    
    fread_raw( iFile , RawData , 1 , BLOCK_INT )
    iVaultEntries = RawData[0]
    
    client_print(1,print_chat,"[nVault] Entries Found:%d",iVaultEntries)
    
    new Handle:QueryTruncate
    new szTruncate[25]
        format (szTruncate, 24, "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 = 0 ; iEntry < iVaultEntries ; iEntry++ )
    {
        fread_raw( iFile , RawData , 1 , BLOCK_INT )
        iTimeStamp = RawData[ 0 ]
        
        fread_raw( iFile , RawData , 1 , BLOCK_BYTE )
        iKeyLen = RawData[ 0 ] & 0xFF
        
        fread_raw( iFile , RawData , 1 , BLOCK_SHORT )
        iValLen = RawData[ 0 ] & 0xFFFF
        
        fread_raw( iFile , RawData , iKeyLen , BLOCK_CHAR )
        ReadString( szKey , iKeyLen , charsmax( szKey ) , RawData )
    
        fread_raw( iFile , RawData , iValLen , BLOCK_CHAR );
        ReadString( szVal , iValLen , charsmax( szVal ) , 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^')", szKey, szVal)
        
        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)
    }
    
    fclose( iFile )
}
stock ReadString( szDestString[] , iLen , iMaxLen , SourceData[] )
{
    new iStrPos = -1;
    new iRawPos = 0;
    
    while ( ( ++iStrPos < iLen ) && ( iStrPos < iMaxLen ) && ( iRawPos < DataBuffer ) )
    {
        szDestString[ iStrPos ] = ( SourceData[ iRawPos ] >> ( ( iStrPos % 4 ) * 8 ) ) & 0xFF;
        
        if ( iStrPos && ( ( iStrPos % 4 ) == 3 ) ) iRawPos++
    }
    
    szDestString[ iStrPos ] = 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** · *Last edited by Bugsy; 01-30-2017 at 22:29.*

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[ 1 ] , szError[ 128 ];
    new Handle:hDbTuple , Handle:hSqlConnection , Handle:hQuery , iErrorCode;
    
    hDbTuple = SQL_MakeDbTuple( "127.0.0.1" , "root" , "password" , "database" );
    
    hSqlConnection = SQL_Connect( hDbTuple , iErrorCode , szError , charsmax( szError ) );
    
    if ( hSqlConnection == Empty_Handle )
    {
        set_fail_state("SQL Connect Failed!");
    }
    
    hQuery = SQL_PrepareQuery( hSqlConnection , "DELETE FROM Pokerank;" );
    SQL_Execute( hQuery );
    SQL_FreeHandle( hQuery );
    
    iVault = nvault_util_open( "pm_save" );
    
    iDBInfo[ 0 ] = _:hSqlConnection;
    
    nvault_util_readall( iVault , "nVault_Export" , iDBInfo , sizeof( iDBInfo ) );
    nvault_util_close( iVault );
    SQL_FreeHandle( hDbTuple );
}

public nVault_Export( iCurrentRow , iTotalRows , const szKey[] , const szVal[] , iTimeStamp , iData[] )
{
    static szQuery[ 256 ] , Handle:hQuery;
    
    if ( !IsBotData( szKey ) )
    {
        formatex( szQuery , charsmax( szQuery ) , "INSERT INTO Pokerank (BlockName,Info) VALUES (^"%s^",^"%s^");" , szKey , szVal );
        hQuery = SQL_PrepareQuery( Handle:iData[ 0 ] , szQuery );
        SQL_Execute( hQuery );
        SQL_FreeHandle( hQuery );
    }
}

I loaded test data into a vault using name1-name5 as BlockName and data1-data5 as Info and it loaded into the database:

imindfreak · *Last edited by imindfreak; 01-27-2017 at 22:02.*

Quote:

Originally Posted by Bugsy

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[ 1 ] , szError[ 128 ];
    new Handle:hDbTuple , Handle:hSqlConnection , Handle:hQuery , iErrorCode;
    
    hDbTuple = SQL_MakeDbTuple( "127.0.0.1" , "root" , "password" , "database" );
    
    hSqlConnection = SQL_Connect( hDbTuple , iErrorCode , szError , charsmax( szError ) );
    
    if ( hSqlConnection == Empty_Handle )
    {
        set_fail_state("SQL Connect Failed!");
    }
    
    hQuery = SQL_PrepareQuery( hSqlConnection , "DELETE FROM Pokerank;" );
    
    SQL_Execute( hQuery );
    
    SQL_FreeHandle( hQuery );
    
    iVault = nvault_util_open( "pm_save" );
    
    iDBInfo[ 0 ] = _:hSqlConnection;
    
    nvault_util_readall( iVault , "nVault_Export" , iDBInfo , sizeof( iDBInfo ) );
    
    nvault_util_close( iVault );
    
    SQL_FreeHandle( hDbTuple );
}

public nVault_Export( iCurrentRow , iTotalRows , const szKey[] , const szVal[] , iTimeStamp , iData[] )
{
    static szQuery[ 256 ] , Handle:hQuery;
    
    formatex( szQuery , charsmax( szQuery ) , "INSERT INTO Pokerank (BlockName,Info) VALUES (^"%s^",^"%s^");" , szKey , szVal );
    
    hQuery = SQL_PrepareQuery( Handle:iData[ 0 ] , szQuery );

    SQL_Execute( hQuery );

    SQL_FreeHandle( hQuery );
}

I loaded test data into a vault using name1-name5 as BlockName and data1-data5 as Info and it loaded into the database:

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'.

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[ 1 ] , szError[ 128 ];
    new Handle:hDbTuple , Handle:hSqlConnection , Handle:hQuery , iErrorCode;
    
    hDbTuple = SQL_MakeDbTuple( "127.0.0.1" , "root" , "password" , "database" );
    
    hSqlConnection = SQL_Connect( hDbTuple , iErrorCode , szError , charsmax( szError ) );
    
    if ( hSqlConnection == Empty_Handle )
    {
        set_fail_state("SQL Connect Failed!");
    }
    
    hQuery = SQL_PrepareQuery( hSqlConnection , "DELETE FROM Pokerank;" );
    
    SQL_Execute( hQuery );
    
    SQL_FreeHandle( hQuery );
    
    iVault = nvault_util_open( "pm_save" );
    
    iDBInfo[ 0 ] = _:hSqlConnection;
    
    nvault_util_readall( iVault , "nVault_Export" , iDBInfo , sizeof( iDBInfo ) );
    
    nvault_util_close( iVault );
    
    SQL_FreeHandle( hDbTuple );
}

public nVault_Export( iCurrentRow , iTotalRows , const szKey[] , const szVal[] , iTimeStamp , iData[] )
{
    static szQuery[ 256 ] , Handle:hQuery;
    
    formatex( szQuery , charsmax( szQuery ) , "INSERT INTO Pokerank (RankNumber,Info) VALUES (^"%s^",^"%s^");" , szKey , szVal );
    
    hQuery = SQL_PrepareQuery( Handle:iData[ 0 ] , szQuery );

    SQL_Execute( hQuery );

    SQL_FreeHandle( hQuery );
}

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** · *Last edited by Bugsy; 01-27-2017 at 23:00.*

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

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

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 : 9

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=0; x < MAX_PLAYERS; x++ )
        LoadXP(x);
    #if POKERANK_SYSTEM==1
        LoadRanks()
    #endif
 }
 
 public save_all()
 {
    if( !PM_Loaded )
        return;

    poke_print(0, print_center, "Saving All Data");
    new players[MAX_PLAYERS], pnum;
    poke_get_players(players, pnum);
    for( new i=0; i<pnum; i++)
        set_task( 1.5*(i+1), "delayed_save", players[i]);

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

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

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

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

        if( forced < 1 && 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(id, key) && SaveXPon() )
    {
        RefreshPoke(id)

        new Data[1501], iLen = 0, i

        //save their pokemon and xp
        for(i = 0; i<MAX_POKES; i++)
            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(i = 0; i<MAX_SETS; i++)
            iLen += formatex(Data[iLen],(1501-iLen),"%d ", PlayersSet[id][i])

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

        writeData(id, key, Data)

        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(id, savekey);
    get_user_info(id, "name", newname, NAME_LENGTH-1);
    if( !equal(savekey, newname) ){
        SaveXP(id);
        LoadXP(id);
    }
 }
 #endif
 //thanks to SHmod
 getSaveKey(id, key[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 ( x = lastchar - 1; x > 0; x--) {
                    if ( equal(botname[x],"(",1) ) {
                        botname[x - 1] = 0
                        break
                    }
                    if ( !isdigit(botname[x]) ) break
                }
            }
            if (strlen(botname) > 0 ) {
                #if SAVE==SAVE_MYSQL
                replace_all(botname,31,"`","\\`")
                replace_all(botname,31,"'","\\'")
                #endif

                replace_all(botname,31," ","_")
                formatex( player_key[id], 34, "[BOT]%s", botname )
            }
        }
        //Hack for STEAM's retardedness with listen servers
        else if (!is_dedicated_server() && id == 1) {
            copy( player_key[id], 34, "loopback" )
        }
        else {
            if (get_pcvar_num(sv_lan) == 1) {
                get_user_ip( id, player_key[id], 34, 1 )        // by ip without port
            }
            else {
                get_user_authid( id, player_key[id], 34 )        // by steamid
                if( equali(player_key[id],"STEAM_ID_LAN") || equali(player_key[id],"4294967295") ){
                    get_user_ip( id, player_key[id], 34, 1 )        // by ip without port
                }
            }
        }
        #else
        get_user_name( id, player_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;
    }

    copy( key, 34, player_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

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.