Raised This Month: $ Target: $400
 0% 

3d Array saving (MySQL) [90 Queires?]


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 06-27-2014 , 11:21   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #1

Guys thanks for explain, Backstabnoob i understand your API and is a n1ce API, but my only problem is in weapons, when players can use 3 ~ 10 weapons in a map, and disconnect from server, this is calling 10 queries.

For the rest i am using procedures to save stats is not soo hard and advoid big queries for me.
(I attached a file with procedures i am currently using)

Black Rose

i Am currently using (This partially solve my problem) this (I think is the same as your 2. option)

PHP Code:
    new sWeapon[32],bool:bWeapon false;
    
    for(new 
iWeapon;iWeapon sizeof(g_iWeapon[]);iWeapon++)
    {
        if(
g_iWeapon[id][iWeapon][Shots] || g_iWeapon[id][iWeapon][Deaths])
        {
            
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][Kills],
                
g_iWeapon[id][iWeapon][Deaths],
                
g_iWeapon[id][iWeapon][Headshots],
                
g_iWeapon[id][iWeapon][Shots],
                
g_iWeapon[id][iWeapon][Hits],
                
g_iWeapon[id][iWeapon][Damage],
                
sSteam,
                
sQuery
            
);
            
            
bWeapon true;
        }
    }
    
    if(
bWeapon)
    {
        
SQL_ThreadQuery(g_hSQL,"PugHandlerSQL",sQuery);
    } 
I think we need a 5 option: Use StringExplode / Implode or like as serialize from PHP, but i do not know about this in amxx.

Arkshine This certainly will be welcome to amxx, specially in AMXX.


@OFF -> Just think of this as a plugin attached. What is the best solution for this? in overall ?

I can put this as "START TRANSACTION; QUERY1; QUERY2; QUERY3; COMMIT" transaction, but i think is not really solve my problem.

EDIT: I can't remember who suggest this other option. but Has any possibility to make an plugin that will create a .sql file in server to in each changelevel upload to sql server?
Attached Files
File Type: zip PugStats Plugin.zip (8.8 KB, 51 views)
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 

Last edited by ^SmileY; 06-27-2014 at 11:31.
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-27-2014 , 12:25   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #2

Yeah, with my system it will be 1 query on their disconnect.
Backstabnoob is offline
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 06-27-2014 , 12:39   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #3

Quote:
Originally Posted by Backstabnoob View Post
Yeah, with my system it will be 1 query on their disconnect.
One query with ^N calls in db, does not solve my problem
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-27-2014 , 12:46   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #4

What? It is one query as any other. That's how you insert data to a SQL table in batch.

You don't seem to know much about what you're talking about, but I gave you a good solution. Well, whatever floats your boat dude.
Backstabnoob is offline
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 06-27-2014 , 14:43   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #5

For example, the loop above is storing the queries into a string and send it in one threaded query, for me is fine but the problem is: In db will call PugSaveWeapon many times in one time.

For me is not solve the problem
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-27-2014 , 14:58   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #6

I said mine solution dispatches the batched query like this:
REPLACE INTO table VALUES
(id, weaponid, ...),
(id, weaponid, ...),
(row3),
(row4),
...

As long as the output buffer allows it, then it cuts the query off, sends it and starts another one. Your arguments aren't making sense.

PHP Code:
UTIL_SavePlayerClass( Class: hClassid )
{
    
/* CONFUSING CODE
     *  
     * How this works:
     * The query is formatted into the g_szQueryCache buffer. We want to process as many rows as possible at once, however we can't process them
     * all blindly, because the text might reach the buffer and we would get the index out of bounds error. In order to get around this, once 
     * the query reaches the output maxlen (16384 by default), the query is cutoff and dispatched to the MySQL database. We then return back to
     * _StartOver and do that yet again, starting where we left off.
     *
     */
    
    
new aClassDataClassDataStruct ]
    
ArrayGetArrayg_aPlayerHandlers_hClass 1aClassData )
    
    new 
szAuthid34 ], iIdentifier
    
new Array: aResults
    
new Array: aColumns aClassData__aColumns ],    aColumnDataClassColumnStruct ], iTotalColumns ArraySizeaColumns )
    new 
szBufferROW_BUFFER_LEN ], iBufferLen, Array: aCurArrayszStringMAX_STRING_LEN ESCAPE_OVERHEAD ], iResultsiLen
    
    
    
/* Dirty method, but it works better than copying the whole saving process twice. 
     * If id = 0, all players are saved, if id != 0 then it's copied into the Array and 
     * the for( ) loop is processed only once on that particular player
     *  
     * There might be a better way that I'm not aware of, but this should do just fine.
     */
    
    
new aPlayers32 ], iNumidPlr
    
    
if( id == )
    {
        
get_playersaPlayersiNum )
    }
    else
    {
        
aPlayers] = id
        iNum 
1
    
}
    
    new 
iTaskIndex
    
    
// iStartAt holds the row we stopped the loop at due to reaching the output buffer limit, iStartPlayer does 
    // the same except it holds the last processed player
    // bInitialize is set to true each time we want to dispatch a new query. That way we start with REPLACE INTO again.
    
    
if( g_bPluginEnd )
    {
        
iTaskIndex OnPluginEnd
    
}
    else
    {
        
iTaskIndex id == OnAllPlayersSave OnSinglePlayerSave
    
}
    
    new 
iMaxSize g_iPreparedQueryBit iTaskIndex MAX_PREPARED_SIZE charsmaxg_szQueryCache )
    
    
    new 
iStartAtiStartPlayerboolbInitialize trueiTotal

_StartOver


    
// we loop through all the players
    
for( new iStartPlayeriNum++ )
    {
        
idPlr aPlayers]
        
        
aResults UTIL_GetPlayerClassDatahClassidPlr )
        
iResults ArraySizeaResults )
        
        if( !
iResults ) continue
        
        if( 
bInitialize )
        {
            
iLen formatexg_szQueryCachecharsmaxg_szQueryCache ), "REPLACE INTO `%s` VALUES "aClassData__szClassName ] )
            
bInitialize false
        
}
        
        
get_user_authididPlrszAuthidcharsmaxszAuthid ) )
        
iIdentifier iSteamIDszAuthid )
        
        
// we loop through all the rows per player
        
for( new iStartAtiResults++ )
        {
            
aCurArray = Array: ArrayGetCellaResults)
            
            
// if the row was deleted, we do nothing and move on
            
if( aCurArray == Invalid_Array )
            {
                continue
            }
            
            
// here we format the actual row. The output will look something alike this: (14, 'string', -1500, 'string2'),
            
iBufferLen formatexszBuffercharsmaxszBuffer ), "(%d"iIdentifier )
    
            for( new 
kiTotalColumns++ )
            {
                
ArrayGetArrayaColumnskaColumnData )
                
                if( 
aColumnData__iColumn ] == Column_Int )
                {
                    
iBufferLen += formatexszBufferiBufferLen ], charsmaxszBuffer ) - iBufferLen",%d"ArrayGetCellaCurArray) )
                }
                else
                {
                    
ArrayGetStringaCurArraykszStringcharsmaxszString ) )
                    
UTIL_SQLEscapeszStringsizeofszString ) )
                    
                    
iBufferLen += formatexszBufferiBufferLen ], charsmaxszBuffer ) - iBufferLen",'%s'"szString )
                }
            }
            
            
szBufferiBufferLen ++ ] = ')'
            
szBufferiBufferLen ++ ] = ','
            
szBufferiBufferLen ] = EOS
            iTotal 
++
            
            
            
// formatting is done, let's check if the row doesn't fit into the buffer
            
            
if( iLen iBufferLen iMaxSize )
            {
                
// it doesn't, so we save the loop cycle we left at
                
iStartAt j
                iStartPlayer 
i
                bInitialize 
true
                
                g_szQueryCache
iLen ] = EOS

                
// we dispatch the query, increase the amount of queries and start again from _StartOver
                
                
UTIL_DispatchCurrentTaskiTaskIndex )
                
g_iTotalQueries ++
                
                goto 
_StartOver
            
}
            
            
// it does, so we add it to the output buffer
            
iLen += copyg_szQueryCacheiLen ], iMaxSize iLenszBuffer )
        }
    }
    
    
// last row for the last player formatted, process the final query
    
if( iTotal )
    {
        
g_szQueryCacheiLen ] = EOS
        
        UTIL_DispatchCurrentTask
iTaskIndex )
        
g_iTotalQueries++
    }
        
    return 
1

This is a snippet from my API. I don't understand why do you want to make your life harder when I already wrote a plugin for stuff like this, but if that's what you want then it's your problem.

Last edited by Backstabnoob; 06-27-2014 at 15:00.
Backstabnoob is offline
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 06-27-2014 , 15:13   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #7

Using REPLACE INTO ? cool
I need to ON DUPLICATE KEY UPDATE
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 

Last edited by ^SmileY; 06-27-2014 at 15:13.
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-27-2014 , 15:56   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #8

Well that's exactly what REPLACE INTO does if you have an unique key. It deletes the row from the table and inserts a new one with the updated data. This is good if you need to update more data at once which you can't with the UPDATE statement.
Backstabnoob is offline
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 06-27-2014 , 16:17   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #9

Quote:
Originally Posted by Backstabnoob View Post
Well that's exactly what REPLACE INTO does if you have an unique key. It deletes the row from the table and inserts a new one with the updated data. This is good if you need to update more data at once which you can't with the UPDATE statement.
For me replace into aways delete old value, and insert new one, but i need to sum the new values with old values.

REPLACE can do same thing of the ON DUPLICATE KEY UPDATE?

If yes, i can still using procedures to do this
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-27-2014 , 17:48   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #10

No, replace requires the total value. What's wrong with caching it on player connect and incrementing it? You can update multiple rows with one query using this method, which is miles ahead of the UPDATE statement for what you want to do, even though it requires additional query on player connect (insignificant).
Backstabnoob is offline
Reply



Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 15:51.


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