[imindfreak]

Hi everyone,

I tried to convert this code snippet into SQLX, however, after many and many attempts, I could not get it to work. I experienced a server crash almost every time. With cvar pm_debug set to 10, it will display ALL debugMessages. After many attempts, I was only able to get up to this point (starting the server) before the server would crash (with SQLX being implemented)

PHP Code:

L 02/16/2019 - 02:30:59: [pokemod.amxx] DEBUG XP: updated savekey
L 02/16/2019 - 02:30:59: [pokemod.amxx] DEBUG XP: does not have a valid savekey ('')
L 02/16/2019 - 02:30:59: [pokemod.amxx] DEBUG XP: updated savekey
L 02/16/2019 - 02:30:59: [pokemod.amxx] DEBUG XP: Trying to load XP data from MySQL database
L 02/16/2019 - 02:31:00: [pokemod.amxx] DEBUG XP: No Saved XP Loaded for UNKNOWN: '' 


I'm going to post the original DBI (non SQLX) snippet below -

Here's the globals -

PHP Code:

 //--Save stuff--//
 #if SAVE==SAVE_MYSQL
    new gPersistent, gPersistentTemp = 0
    new bool:CreateTables = true
    new Sql:gMySQL = SQL_FAILED
    new SQL_TYPE = SQL_MYSQL
    new SQLtype[16]
    new const g_MySQL[] = "MySQL"
    new const g_SQLite[] = "SQLite"
 #endif 


Here is the original code (without SQLX).
Basically, looking for every DBI methods to be changed to SQLX methods.

PHP Code:

 /*
 // Use this to create the table in MySQL

 CREATE TABLE `pm_save` ( 
    `SAVE_KEY` varchar(34) binary NOT NULL default '', 
    `XPS` varchar(2000) binary NOT NULL default '', 
    `LAST_PLAY_DATE` timestamp(6) NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, 
    PRIMARY KEY  (`SAVE_KEY`) 
) ENGINE=MyISAM COMMENT='PokeMod Saving Table';
 
 */

 // This makes sure theres no gaps in PlayersPoke
 //        Example:
 //            From:    5 390 0 0 10 200 16 1000
 //            To:        5 390 10 200 16 1000 0 0
 public RefreshPoke(id)
 {
    if(Safari() || !id || !Pokemod_Connected(id))
        return

    new PlaceHolder[3][MAX_POKES]

    new i, p = 0;
    for(i = 0; i<MAX_ACTIVE_POKES; i++){
        if( ValidPoke( PlayersPoke[id][i] ) ){
            PlaceHolder[0][p] = PlayersPoke[id][i];
            PlaceHolder[1][p] = PlayersXP[id][i];
            PlaceHolder[2][p] = PlayersLevels[id][i];
            p++;
        }
    }
    for(i = p; i<MAX_ACTIVE_POKES; i++){
        PlayersPoke[id][i] = 0;
        PlayersXP[id][i] = 0;
        PlayersLevels[id][i] = 0;
    }

    p = MAX_ACTIVE_POKES;
    for(i = MAX_ACTIVE_POKES; i<MAX_POKES; i++){
        if( ValidPoke( PlayersPoke[id][i] ) ){
            PlaceHolder[0][p] = PlayersPoke[id][i];
            PlaceHolder[1][p] = PlayersXP[id][i];
            PlaceHolder[2][p] = PlayersLevels[id][i];
            p++;
        }
    }
    for(i = p; i<MAX_POKES; i++){
        PlayersPoke[id][i] = 0;
        PlayersXP[id][i] = 0;
        PlayersLevels[id][i] = 0;
    }

    for(i = 0; i<MAX_POKES; i++){
        PlayersPoke[id][i] = PlaceHolder[0][i];
        PlayersXP[id][i] = PlaceHolder[1][i];
        PlayersLevels[id][i] = PlaceHolder[2][i];
    }

    if(!SwitchPoke(id, Pokemon[id])){
        for(i=0; i<MAX_ACTIVE_POKES; i++){
            if(SwitchPoke(id, PlayersPoke[id][i]))
                break;
        }
    }
 }

 ///////////////////////////////////////////
 //  For MYSQL, find the database to use  //
 ///////////////////////////////////////////
 #if SAVE==SAVE_MYSQL 
 public saving_init()
 {
    // Verify the type of DBI module that is loaded is supported
    dbi_type(SQLtype, 15);
    if (equali(SQLtype, g_MySQL))
    {
        SQL_TYPE = SQL_MYSQL;
    }
    else if (equali(SQLtype, g_SQLite))
    {
        SQL_TYPE = SQL_SQLITE;
    }
    else
    {
        SQL_TYPE = SQL_NONE;
        debugMessage(_,_,"XP", "Unsupported database type found (%s), the supported databases are %s or %s", SQLtype, g_MySQL, g_SQLite);
        return;
    }

    debugMessage( 0,7,"XP", "MYSQL registering done" )
 }

 public mySQLConnect()
 {
    gPersistent = get_ppcvar_num(pm_mysql_persistent)

    if ( gMySQL >= SQL_OK ) {
        if (!gPersistent && !gPersistentTemp) close_mysql()
        else return
    }

    new host[64],user[32],pass[32],db[32],error[128]

    get_ppcvar_string(pm_mysql_host,host,63)
    get_ppcvar_string(pm_mysql_user,user,31)
    get_ppcvar_string(pm_mysql_pass,pass,31)
    get_ppcvar_string(pm_mysql_db,db,31)

    if ( !strlen(host) && !strlen(user) && !strlen(db) ) {
        get_cvar_string("amx_mysql_host",host,63)
        get_cvar_string("amx_mysql_user",user,31)
        get_cvar_string("amx_mysql_pass",pass,31)
        get_cvar_string("amx_mysql_db",db,31)
    }

    // Set a default DB if it's SQLite and the user didn't supply one
    if ( SQL_TYPE == SQL_SQLITE && strlen(db) < 1 )
        copy( db, 127, "addons/amxmodx/data/amxx.db" );

    gMySQL = dbi_connect(host,user,pass,db,error,127)
    if (gMySQL <= SQL_FAILED) {
        debugMessage(0,0,"XP", "MySQL connect error: '%s' (%s,%s,%s)",error,host,user,db)
        return
    }
    //Only try to create the tables once
    else if (CreateTables) {
        new sql[512]
        new Result:retval

        if ( SQL_TYPE == SQL_SQLITE ){
            if ( !sqlite_table_exists( gMySQL, "pm_save" ) ){

                copy(sql, 511, "CREATE TABLE `pm_save` ( `SAVE_KEY` varchar(34) binary NOT NULL default '', `XPS` varchar(2000) binary NOT NULL default '', `LAST_PLAY_DATE` timestamp(6) NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY  (`SAVE_KEY`) ) ENGINE=MyISAM COMMENT='PokeMod Saving Table'")
                retval = dbi_query(gMySQL,sql)
                if (retval <= RESULT_FAILED) {
                    dbi_error(gMySQL,error,127)
                    debugMessage(0,0,"XP", "Error Making Tables: '%s' - '%s'", error, sql)
                    return
                }

                if ( SQL_TYPE == SQL_SQLITE ) 
                {
                    new IntegrityCheck[64];
                    copy(sql, 511, "PRAGMA integrity_check");
                    new Result:res = dbi_query(gMySQL, sql);
                    
                    // Check for an error
                    if ( res < RESULT_NONE )
                    {
                        dbi_error(gMySQL,error,127)
                        debugMessage(0,0,"XP", "Error Making Tables: '%s' - '%s'", error, sql)
                        return;
                    }
                    
                    // Get the integrity check value
                    while ( res && dbi_nextrow(res) > 0 )
                    {
                        dbi_result(res, "integrity_check", IntegrityCheck, 63);
                    }

                    // Free the result
                    dbi_free_result(res);

                    // Check to make sure the integrity check passed
                    if ( !equali(IntegrityCheck, "ok") )
                    {
                        // Should we disable saving here?
                        debugMessage(_,_,"XP", "SQL Lite integrity check failed");
                        return;
                    }
                        
                    // Do some synchronous crap
                    format(sql, 511, "PRAGMA synchronous = %d", SQLITE_SYNC_OFF);
                    dbi_query(gMySQL, sql);
                }
            }
        }
        else {
            copy(sql, 511, "CREATE TABLE IF NOT EXISTS `pm_save` ( `SAVE_KEY` varchar(34) binary NOT NULL default '', `XPS` varchar(2000) binary NOT NULL default '', `LAST_PLAY_DATE` timestamp(6) NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY  (`SAVE_KEY`) ) ENGINE=MyISAM COMMENT='PokeMod Saving Table'")
            retval = dbi_query(gMySQL,sql)
            if (retval <= RESULT_FAILED) {
                dbi_error(gMySQL,error,127)
                debugMessage(0,0,"XP", "Error Making Tables: '%s' - '%s'", error, sql)
                return
            }
        }
        CreateTables = false
    }
 }
 writeData(x, key[], xps_to_save[])
 {
    mySQLConnect()
    if ( gMySQL <= SQL_FAILED ) return

    debugMessage( x, 8,"XP", "Trying to save XP data: '%s' - '%s'", key, xps_to_save)

    new error[128],sql[2000]
    new Result:retval, Result:retvalins

    //Thanks to HC for help with writing more efficient queries
    //Check if this user has an entry already, if not make one
    formatex(sql, charsmax(sql), "SELECT * FROM `pm_save` WHERE `SAVE_KEY` = '%s'", key)
    retval = dbi_query(gMySQL,sql)

    if (retval <= RESULT_FAILED) {
        dbi_error(gMySQL,error,127)
        debugMessage(0,0,"XP", "Error Querying MySQL DB for %s: '%s' - '%s'", key, error, sql)
        return
    }
    else if (!dbi_nextrow(retval)) {
        formatex(sql, charsmax(sql), "INSERT INTO `pm_save` (SAVE_KEY) VALUES ('%s')", key )
        retvalins = dbi_query(gMySQL,sql)
        if (retvalins <= RESULT_FAILED) {
            dbi_error(gMySQL,error,127)
            debugMessage(0,0,"XP", "Error Writing MySQL XP for %s: '%s' - '%s'", key, error, sql)
            return
        }
    }

    if (retval >= RESULT_OK) dbi_free_result(retval)

    //Update users entry with current data
    
    formatex(sql, charsmax(sql), "UPDATE `pm_save` SET `XPS`='%s' WHERE (SAVE_KEY='%s')",xps_to_save,key)
    retval = dbi_query(gMySQL,sql)
    if (retval <= RESULT_FAILED) {
        dbi_error(gMySQL,error,127)
        debugMessage(0,0,"XP", "Error Writing MySQL XP for %s: '%s' - '%s'", key, error, sql)
        formatex(sqlx, charsmax(sql),"THE ERROR = '%s' | WHAT SQL HAS = '%s'", error, sql)
        retval = dbi_query(gMySQL,sqlx)
        return
    }

    //Closes the connection if set not to be persistent
    close_mysql()
 }
 public close_mysql()
 {
    if (gMySQL <= SQL_FAILED || gPersistent || gPersistentTemp) return
    dbi_close(gMySQL)
 }
 public saving_end()
 {
    if ( gMySQL <= SQL_FAILED ) return
    dbi_close(gMySQL)
 }
 #endif

 public LoadXP(id)
 {
    if( !PM_Loaded ){
        Pokemod_Connect(id,0);
        return false;
    }

    if( g_isWild[id] > 0 )
        return true
    if( !SaveXPon() )
        return true

    new key[35]
    if( getSaveKey(id, key, true) )
    {
        #if SAVE==SAVE_MYSQL
            new Right[1501]
            new sql[512], error[128]
            new Result:retvalxp

            mySQLConnect()
            if ( gMySQL <= SQL_FAILED ) return false

            formatex(sql, 511, "SELECT `XPS` FROM `pm_save` WHERE `SAVE_KEY` = '%s'", key)
            retvalxp = dbi_query(gMySQL,sql)

            if(retvalxp <= RESULT_FAILED){
                dbi_error(gMySQL,error,127)
                debugMessage(id,0,"XP", "Error Querying MySQL DB for %s: '%s' - '%s'", key, error, sql)

                //Closes the connection if set not to be persistent
                close_mysql()

                return false
            }
            else if (!dbi_nextrow(retvalxp)) {
                dbi_error(gMySQL,error,127)
                debugMessage(id,3,"XP", "No Saved XP Loaded for %s: '%s'", key, error)

                if (retvalxp >= RESULT_OK) dbi_free_result(retvalxp)

                //Closes the connection if set not to be persistent
                close_mysql()

                Pokemod_Connect(id,-1);
                return true
            }
            dbi_field(retvalxp, 1, Right, 500)
        #endif

            return HandleLoad(id,Right)
    }
    Pokemod_Connect(id,0);
    return false;
 }
 HandleLoad(id, Right[1501])
 {
    debugMessage(id,8,"XP", "loading %s",Right );

    //we don't have any saved data
    if(equali(Right,EMPTY))
        return NewPlayerSettings(id);

    new Left[51], i;
    for( i = 0; i<MAX_POKES; i++)
    {
        argbreak(Right, Left, 50, Right, charsmax(Right))
        if( equal(Left, COLON, 1) ) break    //stop if this is the end of the data we want to look at

        PlayersPoke[id][i] = str_to_num(Left)

        argbreak(Right, Left, 50, Right, charsmax(Right))
        if( equal(Left, COLON, 1) ) break    //stop if this is the end of the data we want to look at

        PlayersXP[id][i] = str_to_num(Left)
        PlayersLevel(id, i, true);
    }

    while( !equal(Left, COLON, 1) && !equali(Right,EMPTY)){ //if we didn't stop because we found a :, lets get the next one
        argbreak(Right, Left, 50, Right, charsmax(Right))
    }

    //we ran out of data
    if(equali(Right,EMPTY))
        return NewPlayerSettings(id, true);

    argbreak(Right, Left, 50, Right, charsmax(Right))
    if( !equal(Left, COLON, 1) ){    //we found the time, and not the next seperator
        PlayersTime[id] = str_to_num(Left)
    }

    while( !equal(Left, COLON, 1)  && !equali(Right,EMPTY)) //if we didn't stop because we found a :, lets get the next one
        argbreak(Right, Left, 50, Right, charsmax(Right))

    //argbreak(Right, Left, 50, Right, charsmax(Right))
    if( equal(Left, COLON, 1) ){    //we found the seperator, now this will break it down in here
        for( i = 0; i<MAX_SETS; i++)
        {
            argbreak(Right, Left, 50, Right, charsmax(Right))
            if( equal(Left, COLON, 1) ) break    //stop if this is the end of the data we want to look at

            PlayersSet[id][i] = str_to_num(Left)
        }
    }

    while( !equal(Left, COLON, 1) && !equali(Right,EMPTY)){ //if we didn't stop because we found a :, lets get the next one
        argbreak(Right, Left, 50, Right, charsmax(Right))
    }

    argbreak(Right, Left, 50, Right, charsmax(Right))
    if( equal(Left, COLON, 1) ){    //we found their name
        //do nothing
    }
    
    while( !equal(Left, COLON, 1) && !equali(Right,EMPTY)) //if we didn't stop because we found a :, lets get the next one
        argbreak(Right, Left, 50, Right, charsmax(Right))

    argbreak(Right, Left, 50, Right, charsmax(Right))
    if( !equal(Left, COLON, 1) ){    //we found their catches
        PlayersCatch[id] = str_to_num(Left)
    }

    if( is_user_bot(id) )
        BotSelect(id);

    return ConnectPlayer(id)
 }
 NewPlayerSettings(id, bool:had_pokemon=false)
 {
    PlayersSet[id][SET_HUD_Y] = 18;
    PlayersLocation[id][LOCATION] = 0;
    PlayersRegion[id][REGION] = 0;
    if( !had_pokemon )
        Pokemod_Connect(id,-1);
    else
        return ConnectPlayer(id);
    return true;
 }
 ConnectPlayer(id)
 {
    Pokemod_Connect(id,1);
    RefreshPoke(id)
    return true
 }
 
 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, szName[NAME_LENGTH], i
        get_user_name(id, szName, charsmax(szName))

        //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 name
        iLen += copy(Data[iLen],(1501-iLen),": ")
        iLen += formatex(Data[iLen], (1501-iLen), "%s ", szName) 
        iLen += copy(Data[iLen],(1501-iLen),": ")
        
        //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;
 }
 ////////////////////////////////
 //  Gets rid of Pokemod info  /////////////  TY SHmod  ///////////
 ////////////////////////////////
 cleanXP(bool:clear_all=false)
 {
    #if SAVE==SAVE_MYSQL
        new error[127], sql[255]
        new Result:retval

        mySQLConnect()
        if( gMySQL <= SQL_FAILED ) return

        if(clear_all){
            copy(sql, 254, "TRUNCATE TABLE `pm_save`")
            retval = dbi_query(gMySQL,sql)
            if (retval <= RESULT_FAILED) {
                dbi_error(gMySQL,error,127)
                debugMessage(0,0,"XP", "Couldn't Erase XP: '%s' - '%s' - %d", error, sql, gMySQL)
                return
            }
        }
        //Closes the connection if set not to be persistent
        close_mysql()
    #endif
 } 


Link to complete mod for testing is in here (unmodified) -
https://forums.alliedmods.net/showth...021#post897021

I can post my SQLX implementation as needed but it's all over the place.


One last thing I want to mention how my XPS data structured (differently from the unmodified original Mod)

SAVE_KEY is a STEAM ID

XPS is a value - 85 9873 412 24020 137 601 410 24093 25 24345 215 11570 30 1079 395 24021 272 1 263 1 10 2570 25 18930 123 6010 33 6770 104 1 204 1 77 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 : 1550038227 : 18 0 : playersnamegoeshere : 2297

Here's the explanation on that -
Basically, it's a Pokemod ID# (up to 3 digits) followed by it's XP amount (up to 5 digits).
It will repeat this 107 times. A semi-colon ( is the delimiter.
The next number (1550038227) is a timestamp (10 digits)
The 18 0 is a X and Y HUD coordinates (say 4 digits each)
playersnamegoeshere is the player's in-game name (32 chars)
2297 is a rank number (up to 10 digits)

I've calculated all that at extreme conditions (max values for all possible fields) to be about 1136.
So, if we are storing the XPS value, the String size of about 1500 would be fine just for good measure (this data storage function is in the HandleLoad(id, Right[1501]) method that does this)


If anyone could help out, I would appreciate it!

[PartialCloning]

How is your data different to the original? Other than the addition of the player name?