Post Reply 
 
Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Asynchronous SQL Usage (Especially for those who suffers handling huge data):
Author Message
Avatar
Journeyman
*

Posts: 172
Likes Given: 6
Likes Received: 27 in 12 posts
Joined: Apr 2012
Reputation: 5

The North Shield

Post: #1
Asynchronous SQL Usage (Especially for those who suffers handling huge data):
There are many people who complain about excessive usage of processes caused by overloading data to SQL while server running. So the main reason is that when you are using direct sql execution, whats happening is like server stops working until the data are completely transferred to database or server can shutdown because of overloading. For instance, somehow 1000 players manage registering to database and from sphere server running tries to register all of them at the same time. So guess ? What's going to happen ?. There is a solution for kind of problem I just explained. You can use Asynchronous processes that some of you might know it as aquery/aexecute.

For the detailed information please refer to https://technet.microsoft.com/en-us/libr...78253.aspx ( You can look what sync and async means)


The wiki is I think is not enough in terms of knowledge about this issue. Just the definition is there. As a matter of the fact, Sphere Server has ability to manage data handling as async. So let me explain with an example of how to register users from database to server.

First of all, let me list what functions I will use to register :

* My Function which I'm going to create myself.
* AQUERY - http://wiki.sphere.torfo.org/index.php/AQUERY

In order to register players to server I should first write database check function to call register :

PHP Code:
[function f_acc_check// Main register function to check
db.connect
// From this part, what I'm doing is calling a simple query which will be processed as background.
IF (<DB.AQUERY f_aquery_create_accSELECT FROM players WHERE status '0' and is_activated ='1'> == 0)
    
SERV.LOG There is a problem.
ELSE
    
SERV.LOG Operation successful.
ENDIF 

The next thing we should do is creating "the create account function" which we just typed above. So write your function like the following and even you can simply pass another function while the process is in background. Like;
PHP Code:
f_account_check 
                   
=> aquery => f_acc_create => aquery
                                                             
=> f_acc_pass
                                                             
=> f_acc_pin Pin another password type if you like you can simply set it)
                                                             => 
f_acc_mail so and so forth
I will follow the steps like I just showed you with arrows. Let me first create account creation function like the following: ( Note: You will see that very similar query type like in normal query about numrows. It is very simple and almost same with normal query type but the process management is different)


PHP Code:
[FUNCTION f_aquery_create_acc// Register
IF (<ARGN2> == 0
    
SERV.LOG The command failed to execute. (<ARGS>)
ELSE
    
SERV.LOG Task is done., <dLOCAL.NUMROWSaccount has been created. (<ARGS>)
    FOR 
<EVAL <LOCAL.NUMROWS> - 1>
        if  <
dLOCAL.NUMROWS> > 0    
            local
.account_name <LOCAL.<dLOCAL._FOR>.player_name// table contents => player_name column
            
local.memberid <LOCAL.<dLOCAL._FOR>.id// id => you can set by automatic increments to it and so you can simply link players easly.
            
serv.account add <local.acc_name// Create account // table contents => account_name column
            
serv.account <local.accnametag.email "<LOCAL.<dLOCAL._FOR>.email_address>" // set email as account.tag.email
            
serv.account <local.accnametag.memberid <LOCAL.<dLOCAL._FOR>.id_member>    // set id as account.tag.id
            
var.acc_name <LOCAL.<dLOCAL._FOR>.member_name// set global variable as var.acc_name because when you pass another function to get information about account's pin, status and etc, any information you set , let say, you will need "var.acc_name".
            
local.password <DB.AQUERY f_aquery_create_acc_passSELECT FROM players WHERE id '<eval <local.memberid>>'  // pass another query with the information taken from previous function.
            
local.pin <DB.AQUERY f_aquery_create_acc_pinSELECT FROM players WHERE id'<eval <local.memberid>>' // pass another query with the information taken from previous function.
            
local.status <DB.AEXECUTE f_execute_callbackUPDATE players SET status '1' where id'<eval <local.memberid>>' // Set the status as "1" since this account is not a new player anymore so that next time sphere server can understand what is going to be a new account.
        
endif    
    ENDFOR
ENDIF
RETURN 

Now, it is time to write functions we have already passed from previous function which was " FUNCTION f_aquery_create_acc". Its going to be same as previous functions.


Let me show account password function but before, I should say important issue. As we passed our global variable we can now use it when we update already registered account's password information like [undefined=undefined]serv.account "name" "password"[/undefined]

PHP Code:
[FUNCTION f_aquery_create_acc_pass// Acc Password
IF (<ARGN2> == 0)
    
SERV.LOG The command failed to execute. (<ARGS>)
ELSE
    FOR 
<EVAL <LOCAL.NUMROWS> - 1>
        
serv.account <var.acc_namepassword <LOCAL.<dLOCAL._FOR>.acc_password// you can update password right now since we just registered account and set its password yet. 
        
serv.account <var.acc_nametag.password"<LOCAL.<dLOCAL._FOR>.acc_password>" // Also you can set password as tag since you might use MD5 password type and it could be problematic to check whether password is somehow changed from your WEB SITE user panel. Its wise to store it to check next time whether it stays as it is or not.
    
ENDFOR    
ENDIF
RETURN 

So you can pass any function you like as long as you do right things. I tried to show you with an example of how to do query as background task. You can adapt it on anything you like, especially on the process which is really handling huge data.

You can ask me a question, i'll be glad to anwer.
(This post was last modified: 08-15-2015 06:06 AM by Avatar.)
08-15-2015 12:14 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
[+] 3 users Like Avatar's post
Ultima One
Journeyman
*

Posts: 241
Likes Given: 7
Likes Received: 10 in 6 posts
Joined: Jan 2013
Reputation: 6

Ultima One

Post: #2
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data):
Nice, I had similar scripts on my past servers, didn't know of AQUERY then though Smile

ULTIMA ONE
The modern, sphere powered Ultima Online server
08-15-2015 01:50 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
darksun84
Sir Spamalot
****

Posts: 1,673
Likes Given: 242
Likes Received: 161 in 150 posts
Joined: Mar 2012
Reputation: 34



Post: #3
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data):
Very interesting ! good job
08-15-2015 02:13 AM
Find all posts by this user Like Post Quote this message in a reply
Avatar
Journeyman
*

Posts: 172
Likes Given: 6
Likes Received: 27 in 12 posts
Joined: Apr 2012
Reputation: 5

The North Shield

Post: #4
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data):
Actually, these kind of things are available the script pack I just uploaded to community 1.5 years ago. But none knows, so I will randomly explain some stuff time to tome like this Smile
08-15-2015 06:07 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
XuN
Sphere Developer
*****

Posts: 855
Likes Given: 102
Likes Received: 161 in 121 posts
Joined: Jul 2013
Reputation: 30



Post: #5
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data):
Nice functions.

NOTE: You can get rid of VARs using CALL function, this will keep in this function the locals, etc you are using and will change their values inside the function, plus creating any new local in the main function which is being created on the CALLed one.
08-15-2015 06:17 AM
Find all posts by this user Like Post Quote this message in a reply
Avatar
Journeyman
*

Posts: 172
Likes Given: 6
Likes Received: 27 in 12 posts
Joined: Apr 2012
Reputation: 5

The North Shield

Post: #6
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data):
Yes, you are right. They can make call either. The things I just posted are stuffs from 2011 but at least give some idea about the topic Smile
(This post was last modified: 08-15-2015 06:26 AM by Avatar.)
08-15-2015 06:23 AM
Visit this user's website Find all posts by this user Like Post Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)

This forum uses Lukasz Tkacz MyBB addons.