[Bugsy]

Just prevent things from going wrong..

[damage220]

Originally Posted by Alon2k2 Thank you, Too bad there isn't, is something very important when working with database

You may try to use START TRANSACTION, COMMIT, and ROLLBACK SQL statements directly. I have not tested it but it should work. At least when you write them in single query.

PHP Code:

START TRANSACTION;
UPDATE tblPoints SET Points=(current points - 1) WHERE ID=PlayerA
UPDATE tblPoints SET Points=(current points + 1) WHERE ID=PlayerB
COMMIT; 


[damage220]

Originally Posted by Bugsy Just prevent things from going wrong..

How is it possible to prevent power loss or engineering works of your ISP?

[Bugsy]

How about this:

1. Execute the SQL transaction to deduct a point
2. In the callback/handler for that transaction, decide what to do based on success/failure (if success, add point for player, if failed, re-send or do nothing)

[Bugsy]

Not tested

PHP Code:

#include <amxmodx>
#include <sqlx>

enum TransType
{
    ttTakePoints,
    ttGivePoints
}

enum TransInfo
{
    tiPlayerIDLosePoints,
    tiPlayerIDGainPoints,
    TransType:ttType,
    tiNumPoints
}

new Handle:g_SQLTuple;
new g_szBuffer[ 512 ];
new tiData[ TransInfo ];


public plugin_init() 
{
    SQL_SetAffinity( "sqlite" );
    
    g_SQLTuple = SQL_MakeDbTuple( "" , "" , "" , "TestPlugin" );
    
    new Handle:SQLConnection , ErrorCode;
    SQLConnection = SQL_Connect( g_SQLTuple , ErrorCode , g_szBuffer , charsmax( g_szBuffer ) );
    
    if( SQLConnection == Empty_Handle ) 
        set_fail_state( g_szBuffer );
        
    RunQuery( SQLConnection , "CREATE TABLE IF NOT EXISTS 'tblPlayers' ( `ID` INTEGER PRIMARY KEY AUTOINCREMENT, `steamid` TEXT UNIQUE, `points` INTEGER );" );    
}

public plugin_end() 
{
    SQL_FreeHandle( g_SQLTuple );
}

public TransferPoints( idLosePoint , idGainPoint , iNumPoints )
{
    new szAuthID[ 35 ];
    get_user_authid( idLosePoint , szAuthID , charsmax( szAuthID ) );
    
    tiData[ tiPlayerIDLosePoints ] = idLosePoint;
    tiData[ tiPlayerIDGainPoints ] = idGainPoint;
    tiData[ ttType ] = ttTakePoints;
    tiData[ tiNumPoints ] = iNumPoints;
    
    formatex( g_szBuffer , charsmax( g_szBuffer ) , "UPDATE `tblPlayers` SET Points=Points-%d WHERE `SteamID='%s';" , iNumPoints , szAuthID );
    
    SQL_ThreadQuery( g_SQLTuple , "SQLCallBack" , g_szBuffer , _:tiData[ tiPlayerIDLosePoints ] , _:TransInfo );
}

public SQLCallBack(FailState, Handle:Query, Error[], Errcode, Data[ TransInfo ], DataSize)
{
    new idGainPoint = _:Data[ tiPlayerIDGainPoints ];
    
    //No error and taking points was successful
    if ( !Errcode && ( Data[ ttType ] == ttTakePoints ) )
    {
        //Change transaction type to give
        Data[ ttType ] = ttGivePoints;
        
        new szAuthID[ 35 ];
        get_user_authid( idGainPoint , szAuthID , charsmax( szAuthID ) );
    
        formatex( g_szBuffer , charsmax( g_szBuffer ), "UPDATE `tblPlayers` SET Points=Points+%d WHERE `SteamID='%s';" , Data[ tiNumPoints ] , szAuthID );
    
        SQL_ThreadQuery( g_SQLTuple , "SQLCallBack" , g_szBuffer ,  Data[  tiPlayerIDLosePoints ] , _:TransInfo  );
    }
    else if ( Errcode )
    {
        switch ( Data[ ttType ] )
        {
            case ttTakePoints:
            {
                //Take points failed, retry? 
            }
            case ttGivePoints:
            {
                //Give points failed, retry?     
            }
        }
    }
} 

//Here's how to execute a trans immediately (non asynchronous)
public RunQuery( Handle:SQLConnection , const szQuery[] )
{
    new Handle:Query = SQL_PrepareQuery( SQLConnection , szQuery );
    
    if( !SQL_Execute( Query ) )
    {
        set_fail_state( g_szBuffer );
    }
} 


[Bugsy]

Good news

Transactions do work with SQLx, though I am getting odd results with this, it's subtracting and adding 3 opposed to 1 point.

PHP Code:

#include <amxmodx>
#include <sqlx>

new Handle:g_SQLTuple;
new g_szBuffer[ 512 ];

public plugin_init() 
{
    new Handle:SQLConnection , ErrorCode;
    
    SQL_SetAffinity( "sqlite" );
    g_SQLTuple = SQL_MakeDbTuple( "" , "" , "" , "TestPlugin" );
    SQLConnection = SQL_Connect( g_SQLTuple , ErrorCode , g_szBuffer , charsmax( g_szBuffer ) );
    
    if ( SQLConnection == Empty_Handle ) 
    {
        set_fail_state( g_szBuffer );
    }
    else
    {
        copy( g_szBuffer , charsmax( g_szBuffer ) , "BEGIN TRANSACTION; UPDATE `playerData` SET Points=Points-1 WHERE SteamID='STEAM:0:12345'; UPDATE `playerData` SET Points=Points+1 WHERE SteamID='STEAM:0:11111'; COMMIT;"  );
        RunQuery( SQLConnection , g_szBuffer );
    }
}

public plugin_end() 
{
    SQL_FreeHandle( g_SQLTuple );
}

public RunQuery( Handle:SQLConnection , const szQuery[] )
{
    new Handle:Query = SQL_PrepareQuery( SQLConnection , szQuery );
    new szErr[ 33 ];
    
    if ( !SQL_Execute( Query ) )
    {
        SQL_QueryError( Query  , szErr , charsmax( szErr ) );
        set_fail_state( szErr );
    }
}