Scripting SQL vs Variables

[Nerks]

Good day to all,

May i hear your suggestion what is the ADVANTAGE and DISADVANTAGE creating a script using VARIABLES and SQL.

I create a PVP script on my own using SQL BASE in order to view as well the TOP 10 PVP Player.

All i want to know is the bad side using a script and send it to database, while scripting using variable.

 

Thanks in advance

[Skorm]

  On 9/18/2017 at 9:48 AM, Nerks said:

Good day to all,

May i hear your suggestion what is the ADVANTAGE and DISADVANTAGE creating a script using VARIABLES and SQL.

I create a PVP script on my own using SQL BASE in order to view as well the TOP 10 PVP Player.

All i want to know is the bad side using a script and send it to database, while scripting using variable.

 

Thanks in advance

Expand  

If the users can instantiate SQL queries on a whim they could flood your SQL server with many requests. If controlled SQL makes it very easy todo things that would normally be time consuming in scripts with variables.
 

For example:

SELECT * FROM item_db_re ORDER BY price_buy DESC LIMIT 5;

With one line I can easily find what are the most expensive items to purchase in the game. You could easily apply something like this to a pvp ranker.

If you plan on keeping all your data for the players in variables they get hard to manage and remove, and the sql table for perm variables gets pinged by the server quite often so it's best to keep that as small as you can. This is where I would recommend using an SQL database.

 

Without getting into it any further what I'm trying to say is. If used correctly SQL can be a powerful tool that should be taken advantage of, and for bigger scripts with lots of numbers and information there's no reason not to use SQL.

 

[Z3R0]

  On 9/18/2017 at 9:00 PM, Skorm said:

If the users can instantiate SQL queries on a whim they could flood your SQL server with many requests. If controlled SQL makes it very easy todo things that would normally be time consuming in scripts with variables.
 

For example:

SELECT * FROM item_db_re ORDER BY price_buy DESC LIMIT 5;

With one line I can easily find what are the most expensive items to purchase in the game. You could easily apply something like this to a pvp ranker.

If you plan on keeping all your data for the players in variables they get hard to manage and remove, and the sql table for perm variables gets pinged by the server quite often so it's best to keep that as small as you can. This is where I would recommend using an SQL database.

 

Without getting into it any further what I'm trying to say is. If used correctly SQL can be a powerful tool that should be taken advantage of, and for bigger scripts with lots of numbers and information there's no reason not to use SQL.

 

Expand  

Just have to say, full agree. 

Most NPC's can easily call an OnInit: function, that serves as the "basis" to pull information from SQL... This is a on time call, but grants you the ability to manipulate the server on the "fly" without having to deal with remembering what variables are what... You can make a change to the DB, call a refresh method and BAM, settings updated... (almost too magical...)

Any time you are going to be doing cross game-browser, chances are you are going to need a SQL Table to handle the data... but like Skorm said, making sure it's not hammering it is definitely something to note...

 

For instance, you don't want a click handler calling the DB every time someone clicks the NPC... (ew, no)... 

Now, say for instance it runs after a PVP Event... ok, well that's 1 time per event... not horrible... 

So yes, while you may need local and npc and temporary variables to manage the workings of the script, by all means, push them to sql when complete so you can query that data from your web browser or retrieve it later with a refresh function

[Nerks]

@Skorm & @Z3R0

OMG!!! so it means it's not adviceable to use my SQL SCRIPT

I create a PVP SCRIPT that every time a player kill, their point in database base will add plus 1 and when they die they will loose 1 point.. So technically my script is horrible because it will always make a query.. In due time my if there are many player on my server and they all goes to PVP my database will surely collapse

Oh my oh my how can i create my own pvp npc with pointing system without database.. i can do only manipulate on database

I guess i need to learn more about avariable manipulation

 

Both of you thank you for your suggestion

[Skorm]

  On 9/19/2017 at 1:49 AM, Nerks said:

@Skorm & @Z3R0

OMG!!! so it means it's not adviceable to use my SQL SCRIPT

I create a PVP SCRIPT that every time a player kill, their point in database base will add plus 1 and when they die they will loose 1 point.. So technically my script is horrible because it will always make a query.. In due time my if there are many player on my server and they all goes to PVP my database will surely collapse

Oh my oh my how can i create my own pvp npc with pointing system without database.. i can do only manipulate on database

I guess i need to learn more about avariable manipulation

 

Both of you thank you for your suggestion

Expand  

I really don't think you have to worry about your database collapsing but if you're really concerned.

Store all the kills in a character defined variable and then push them to the database when they logout.

OnPCLogoutEvent:
	if( @kills || @deaths )
		query_sql("UPDATE `pvp_table` SET kills += "+@kills+", deaths += "+@deaths+" WHERE charid == "+getcharid(0)+";");

This is just to give you an idea you'll still have to check to see that a table has been created for that user before you can update it. There are plenty of other ways you could do something like this, but honestly you probably don't even have to worry about it unless you have an inane amount of players.

[Nerks]

  On 9/19/2017 at 5:28 AM, Skorm said:

I really don't think you have to worry about your database collapsing but if you're really concerned.

Store all the kills in a character defined variable and then push them to the database when they logout.

OnPCLogoutEvent:
	if( @kills || @deaths )
		query_sql("UPDATE `pvp_table` SET kills += "+@kills+", deaths += "+@deaths+" WHERE charid == "+getcharid(0)+";");

This is just to give you an idea you'll still have to check to see that a table has been created for that user before you can update it. There are plenty of other ways you could do something like this, but honestly you probably don't even have to worry about it unless you have an inane amount of players.

Expand  

Hi @Skorm question..

Does the npc OnPCKillEvent label.

how does the code running into that? 1 npc instance share MANY Player OR 1 npc Instance for 1 player

[Z3R0]

You would simply have 1 npc with multiple functions...

 

-	script	StatTracker	-1,{

	OnPCLoginEvent: 
		// Gather Information from SQL, store in @vars

	OnPCLogoutEvent:
		// Push @vars back to SQL

	OnPCKillEvent:
		// Increase @pvp vars + 1

	OnNPCKillEvent:
		// Increase @mobs killed + 1

	OnPCDieEvent:
		// Decrease Death Vars + 1

}

Something similar to that

Edited September 19, 2017 by Z3R0
cuz I wanted to

[llchrisll]

You could take a look at my script: https://github.com/llchrisll/lllchrislll-scripts/blob/master/released/pvp-gvg-mvp.txt

It might not be the best example but still.

Regards,

Chris

[Nerks]

  On 9/19/2017 at 2:00 PM, Z3R0 said:

You would simply have 1 npc with multiple functions...

 

-	script	StatTracker	-1,{

	OnPCLoginEvent: 
		// Gather Information from SQL, store in @vars

	OnPCLogoutEvent:
		// Push @vars back to SQL

	OnPCKillEvent:
		// Increase @pvp vars + 1

	OnNPCKillEvent:
		// Increase @mobs killed + 1

	OnPCDieEvent:
		// Decrease Death Vars + 1

}

Something similar to that

Expand  

Thanks sir i get your point

Back to may question... Does the code NPC only trigger on 1 player?

Example.

Player 1 click the NPC that has a variable .@playername still on process of npc

then suddenly

Player 2 click the same NPC with a variable .@playername.

My question is..

Does the Player 1 change his variable .@playername data because the Player 2 click the same NPC while the Player 1 still ongoing on that NPC?

OR .@playername didn't change?

[sader1992]

".@"     - A scope variable.
           They are unique to the instance and scope. Each instance has its
           own scope that ends when the script ends. Calling a function with
           callsub/callfunc starts a new scope, returning from the function
           ends it. When a scope ends, its variables are converted to values
           ('return .@var;' returns a value, not a reference).

.@ only inside the method you are in , for that you see something like for(.@i=0  more then one time in the script (here the .@i is declared only inside for loop) and if you put .@somthing = 0; in the start of the script then it will be 0 unless the npc calling for change

and it will end when the player close the npc or when the script end or when calling func/sub

if  npc1 == .@something && npc2  == .@something

npc1 != npc2

.@something from npc1 != .@something from npc2

each npc have it's own .@something and for each player

 

Edited September 20, 2017 by sader1992

[Nerks]

  On 9/20/2017 at 9:42 AM, sader1992 said:

".@"     - A scope variable.
           They are unique to the instance and scope. Each instance has its
           own scope that ends when the script ends. Calling a function with
           callsub/callfunc starts a new scope, returning from the function
           ends it. When a scope ends, its variables are converted to values
           ('return .@var;' returns a value, not a reference).

.@ only inside the method you are in , for that you see something like for(.@i=0  more then one time in the script (here the .@i is declared only inside for loop) and if you put .@somthing = 0; in the start of the script then it will be 0 unless the npc calling for change

and it will end when the player close the npc or when the script end or when calling func/sub

if  npc1 == .@something && npc2  == .@something

npc1 != npc2

.@something from npc1 != .@something from npc2

each npc have it's own .@something and for each player

 

Expand  

Sir @sader1992 so basically it will not change even other player interact with the same NPC?

[sader1992]

  On 9/21/2017 at 1:22 AM, Nerks said:

Sir @sader1992 so basically it will not change even other player interact with the same NPC?

Expand  

yes

[Nerks]

  On 9/21/2017 at 11:00 AM, sader1992 said:

yes

Expand  

Thanks for the info sir. Now I fully understand the code on the NPC....

Am i right? all the codes on the NPC is for 1 player only.... another reset of code for another player how will interact again?