Time issues

[basketbojler]

Hello guys!

I'm new here so I have a real basic question here!

I was making my game when I came across this problem:

how is the timing in games like travian, where you upgrade some things made. For instance, if I upgrade my horse from level one to level two, it takes like 30 min to upgrade the thing. What is the code behind this?

Thank you guys!

[Nox]

Short one: In database you store something like: player X building Y, will be completed in Z
And then when needed (there is need to update thing), the scring compares current time to Z and if greater than building is removed and finished thing is encounted

There was a afaik thread containing exhausting debate about when to update, one way is, that when needed - either when the respective player building the thing
or when other player needs the updated data, for example - he is attacking him or spying on him or just viewing a ladder etc.
Other way is to periodicaly update everying...plus is, that update 100k players in 1 query is much much more quicker than use 100k querys per player and also it gets rid
of the constant checking of many rows on so many occasions, the downside might be that to update all rows might not be neccessary, and some other things I can't remember now probably

[Harkins]

The two recent threads were:

• http://community.bbgamezone.net/index.php/topic,1818.0.html
• http://community.bbgamezone.net/index.php/topic,1815.0.html

And welcome to the forums, basketbojler.

[basketbojler]

well I was thinking of doing it like nox said:

1. making a list of events in the database

the second issue is how do u make a script to check update. I thought of putting in into main site so basicaly when a player clicks the script goes trought, but I'm worried it may get the server stuck cause of too much queries.

And sorry for my lousy english

[Nox]

the second issue is how do u make a script to check update. I thought of putting in into main site so basicaly when a player clicks the script goes trought, but I'm worried it may get the server stuck cause of too much queries.

If you read it carefuly most of our posts talk about this and not your original question

[basketbojler]

so how is it with databases, how easily can u get them stuck with too many queries?

[Helderic]

so how is it with databases, how easily can u get them stuck with too many queries?

I'm guessing you probably won't have 100k users so it shouldn't be too much of an issue however you do it. The only problem issue I can see you having is with chewing up your of your servers resources if your using shared hosting.

[Scion]

so how is it with databases, how easily can u get them stuck with too many queries?

Its not really possible to answer this question....perhapes a metaphor might help.

how easy is it to build a bridge that you want to stay up but that falls down in the end?....it depends....are you a layman? if so then its probably more likely than if you have a degree in structural engineering.... So training helps build a better bridge....(ie you need to learn about DB design/structures/querry optimisation etc)....and similarly if your a novice bridge builder its more likely than if your a vetran of 25 years.....So Experience helps.....(ie you actually need to have tried things out for yourself a few times...or at least been along for the ride)...it can also depend on the scale of the project.....is it a bridge over the ditch round the back of your house.....or another crossing over the sanfransico harbour?....Scale matters.....(ie its more likely with a larger DB with more complex querries)

Having said that....ordinary people are able to build perfectly good bridges over streams and engineering firms still manage to produce bridges that colapse....

So back to the question...well it depends, whats your design like, what querries are you running, how much data is in the DB....the upshoot is the less experience you have the more likely it is youll screew things up.....but hey...screwing things up is just another opportunity to learn how to do it better next time

[jannesiera]

well I was thinking of doing it like nox said:

1. making a list of events in the database

the second issue is how do u make a script to check update. I thought of putting in into main site so basicaly when a player clicks the script goes trought, but I'm worried it may get the server stuck cause of too much queries.

And sorry for my lousy english

Read the second link Harkins gave you.

[astarum]

The way I have done it in the game I am devloping is to have a seperate table in the database that holds the build data.  i.e. when someone says "I want to build this" the build details are put into the table.

The time when the build should be finished is put into the table.

 I then have a python script running on the server that runs every 5 seconds, checking to see if anything in that table is due or past due for completion.  If it is, its removed from the build table and placed in the structures table.

Not sure if that the best way to do it or not, I just whacked it together a few months ago and it seems to work just fine

[Harkins]

One bug you'll see eventually see is that your game will use CPU/disk that the job takes more than 5 seconds to complete. So then there will be two copies of the script running giving out two buildings for the one record in the database. And of course having a second copy running means the whole machine is running slower, so soon you'll have three copies running, which means the machine is slower...

Lockrun is one solution; you could also rewrite your script to use transactions. That second forum thread linked above has a very different approach that works nicely.

But you'll probably be fine for a while as-is. Focus on getting your game running before you worry about bugs you haven't hit yet, and good luck.

[KoI]

A possible hitch in the 'update as the user is logged in and active' is that some resources might be required even when the user is not logged in.  For instance, the obvious use for gold would be to buy things, and if you're not logged in, you're not buying things, so your gold doesn't need to be updated via cron when you can just update it when the player next logs in.

But, if part of your combat system is to allow the winner of a battle to loot some of the gold of the loser, than that gold needs to be updated, even when the defending player isn't logged in.

I ran into this problem in my own game for both gold, and troops.  If I set troop creation at like 15 minutes (or whatever), and the person raises up 100 troops, and then logs out before it's complete, if I don't have a script running that says 'check to see if the troops are done', and that guy gets attacked a few hours later but hasn't logged in to update his troops, those troops will not be available, even though they should have been.

So I think if the item is part of an equation that can be influenced by other players (troops, gold, etc), then your only option is to use a cron, right?

Or I suppose you could run an update query on the player being attacked to make sure those items are where they need to be, prior to running your other calculations... perhaps that's the best solution.

[jannesiera]

Or I suppose you could run an update query on the player being attacked to make sure those items are where they need to be, prior to running your other calculations... perhaps that's the best solution.

That's exactly what you should do. You have an "update" script that gets called whenever you need it. So in all situations with player interaction like trading, battling, etc.

[Scion]

once again the only correct answer is ...'It depends'

The two main approaches are to calculate on an as needed approach, or in advance. Neither of which suits all situations, the as needed aproach is probably preferable when the value is changing on different time lines for each user. Building new buildings, raising troops all strike me as classic examples of this, Each user may start building or raising troops at a different time, and to build a house should take the same duration for all players. However if you have a variable that opperates on the same time line for all users then the cron approach may be better, especially if you can make the update into a bulk update query. Turn generation seems to be a classic example of this where a cron for a single query probably suits the situation best.

With the as needed approach you just need to make sure that any value that can change over time is accurate when you use it. For a simple calculation it may be possible to simply calculate every time. For a more complicated ones you will generally do a check to see if it needs to be recalculated first. A variation of the as needed aproach is to invalidate a value as soon as you know its no longer valid forcing you to recalculate it the next time you need to access it.

[tellmore]

A possible hitch in the 'update as the user is logged in and active' is that some resources might be required even when the user is not logged in.  For instance, the obvious use for gold would be to buy things, and if you're not logged in, you're not buying things, so your gold doesn't need to be updated via cron when you can just update it when the player next logs in.

But, if part of your combat system is to allow the winner of a battle to loot some of the gold of the loser, than that gold needs to be updated, even when the defending player isn't logged in.

I ran into this problem in my own game for both gold, and troops.  If I set troop creation at like 15 minutes (or whatever), and the person raises up 100 troops, and then logs out before it's complete, if I don't have a script running that says 'check to see if the troops are done', and that guy gets attacked a few hours later but hasn't logged in to update his troops, those troops will not be available, even though they should have been.

So I think if the item is part of an equation that can be influenced by other players (troops, gold, etc), then your only option is to use a cron, right?

Or I suppose you could run an update query on the player being attacked to make sure those items are where they need to be, prior to running your other calculations... perhaps that's the best solution.

well, the approach has some flaws, but you may even set up an endless script.
sortha like..
start of loop
get current time (A),
query the events database and do the things needed,
get time again (B)
calculate execution time (B-A)
if execution time is less than 5 sec, then sleep(5-execution time)
end of loop

supposedly it will not allow the script to double-update.

to make sure the hdd is not whacked by the query/update things, you can simply use a database stored in RAM memory, and every 24, or 12, or 6 hours write the contents of the whole database to the disk (copy it) to make sure things don't get lost if something goes wrong. (so you can roll back..) Also, database in ram will allow swift updates, less likely the script has to run right after its finished running.
If things get ugly even whit this solution, and your processing power is limited, you can do a nice trick.
IF the execution time is longer than 5 seconds, it means the server is not fas enough to serve all needs.
You can make a variable that would add a sleep command to all players when they update the  page (or click on something) they will see a server lag of a short time. this lets the server have enough time to finish the events table. Also, after the loop is complete ( everything updated so execution time is less than 5 seconds) you need to reset the variable to zero. (so no sleep, no artificial lag). This helps to handle overload on the server, and will annoy your players. Might as well LOG when something like this happens, so you will know your server has reached its limits.

this should keep everything up to date. don't forget to sort the event table by the timestamp. this will make sure things happen in order.

so in essence, you have a table that holds CURRENT STATUS
sortha like..
player X, building Y, building LVL 
(building lvl 0 to whatever is the maximum, and -1 means its allready under construction/upgrade, -2 is not available yet, or whatever)

and you got an events table,
sortha like
type_of_event, variable 1 ,variable 2, variable 3,...., timestamp

where timestamp indicates when should it be done.
course, applying this is not all that easy as it looks.
when you query the database, you should only care for rows, where things are in the past compared to current timestamp. (timestamp A in my example loop)
so when querying you should select WHERE timestamp is less OR equal to timestamp A, and order the result according to timestamp.
then, you need to process every line (for eatch loop),
supposedly you can make a function that can deal with that.
for this event table might be useful to store variables that help you select what to update.

I don't know if its possible to make a mysql query update multiple value whit in 1 single query.
probably it would be more useful than doing a query for every single value that needs to be updated.
but, thats another story.

also, there are things that do not need to be so accurate.
like .. if players earn gold from a mine with a rate of X / hr, you may not want to trash the event table with that. one side is, player may need to wait for 5 valuable seconds for the gold to start building his wall, in order to finish it before the enemy arrives. other side is, gold is not used all the time, for 500 players it would be extra 500 lines to check in the event table.

so you make that "on demand" update. when the player opens up build panel, the amount of resources are calculated, and shown to the player. this reduces the load on the event table.

in order to do that, values that are updated "on demand" has to be stored with a timestamp.
sortha like:
Player X, resource Y, income rate, timestamp

when you display anything, where the resource could be spent, you take the difference of current and stored timestamp, calculate NEW resource available, update the table with new values, and then show it to the player.

and this is the point, where things get rather complicated. If player upgrades his gold mine, you have to make sure to recalculate the resources WHEN the new mine is built, whit the original income rate. Then store that, update the tables with new lvl for the gold mine, and new income rate.
may ask why..
well, if player did not visit any page that would update his resources for a long time, and his mine upgrade also took a long time, his resources would be upgraded for the WHOLE  time period with the higher income rate.

it is critical to have a very clean function to handle the events table, in order to get rid of such bugs.
Its late in the night here, sorry if my post was long/disturbed. I was just thinking my input can be useful.