+- SphereCommunity (https://forum.spherecommunity.net)
+-- Forum: General Discussion (/Forum-General-Discussion)
+--- Forum: Documentation/Tutorials (/Forum-Documentation-Tutorials)
+--- Thread: Asynchronous SQL Usage (Especially for those who suffers handling huge data): (/Thread-Asynchronous-SQL-Usage-Especially-for-those-who-suffers-handling-huge-data)
Asynchronous SQL Usage (Especially for those who suffers handling huge data): - Avatar - 08-15-2015 12:14 AM
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/library/cc978253.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_acc, SELECT * 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.
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.NUMROWS> account has been created. (<ARGS>)
FOR 0 <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.accname> tag.email "<LOCAL.<dLOCAL._FOR>.email_address>" // set email as account.tag.email
serv.account <local.accname> tag.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_pass, SELECT * 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_pin, SELECT * FROM players WHERE id= '<eval <local.memberid>>' // pass another query with the information taken from previous function.
local.status <DB.AEXECUTE f_execute_callback, UPDATE 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 0 <EVAL <LOCAL.NUMROWS> - 1>
serv.account <var.acc_name> password <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_name> tag.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.
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data): - Ultima One - 08-15-2015 01:50 AM
Nice, I had similar scripts on my past servers, didn't know of AQUERY then though
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data): - darksun84 - 08-15-2015 02:13 AM
Very interesting ! good job
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data): - Avatar - 08-15-2015 06:07 AM
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
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data): - XuN - 08-15-2015 06:17 AM
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.
RE: Asynchronous SQL Usage (Especially for those who suffers handling huge data): - Avatar - 08-15-2015 06:23 AM
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