[Anarkien]

So recently I got back into coding servers after years away from SA-MP, and now I've got a "blocking" question that I can't seem to wrap my mind around.

Setting up the server and the database isn't the issue here. What I can't seem to understand is how the game state is synced with a database. Allow me to explain.

On server start I fetch the data from my database to have the initial game state.
On server close I store the data back into the database.

But what happens in between? What if I make a gamemode where users can buy a house. If the house is bought, nobody else can buy it. If I only sync data on server start and close, a house that has been bought within the "current" server session will not be displayed as bought for other players, because the state is not synced with the database.

So the kinda impractical solutions I've thought through are:

1. Will I need an additional "source of truth" (in-memory database or simply a massive object replicating the database structure) to handle all changes to the game state so that all players can immediately see changes (such as a house going from available to unavailable if someone buys it)
2. Sync with the database every time a player does something sync-worthy, like buying a house, dying, depositing cash, etc.
3. Sync on interval (performance hit?)

Are there any other solutions to this? I feel like I'm seriously over-complicating this...

[Vince]

Replicating the database structure, i.e. fetching all of the stuff and putting it in global variables, is in my opinion super redundant and unnecessary. It may be a bit faster that way but you lose all flexibility and with threaded queries it shouldn't even matter. Update data immediately when it changes. This has the advantage that you don't have overly long queries and that you usually don't need to separately save all the data on disconnect.

[Anarkien]

Quote:

Originally Posted by Vince Replicating the database structure, i.e. fetching all of the stuff and putting it in global variables, is in my opinion super redundant and unnecessary. It may be a bit faster that way but you lose all flexibility and with threaded queries it shouldn't even matter. Update data immediately when it changes. This has the advantage that you don't have overly long queries and that you usually don't need to separately save all the data on disconnect.

Thanks for your input on this.

By "Update data immediately [...]" you mean both executing a database update on the changed object as well as querying the database for the newly updated version of the data?

[AmigaBlizzard]

You could create many small function to adjust your server-data and the database at once.
Like:

PHP Code:

// This function adds the given Money and Score values to the given player
Player_Reward(playerid, Money, Score)
{
    new Query[128];
    // Add the given Money and Score to the player's account
    PlayerData[playerid][PlayerMoney] = PlayerData[playerid][PlayerMoney] + Money;
    PlayerData[playerid][PlayerScore] = PlayerData[playerid][PlayerScore] + Score;
    // Update money and score for this player in the player's account in MySQL
    mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET Money = '%i', Score = '%i' WHERE ID = '%i'", PlayerData[playerid][PlayerMoney], PlayerData[playerid][PlayerScore], PlayerData[playerid][SQLID]);
    mysql_tquery(SQL_db, Query, "", "");
    return 1;
} 


With your housing system, you can simply write a function that also saves the updated house-data to the database when a player buys the house.

Or you can write code to use the server-data instead of database-data to check if the house is owned or not.
Since you load everything from the database when your server is started, both have the same data anyway.
Looping through an array and finding the correct house based on the id of the nearby pickup is alot faster than sending a query, wait the mysql to search the database, compile a result, send it back to your script and examine the result and probably have extra code to account for every possible result (no rows returned, too many rows returned, ...).


Looping the array should only take a few microseconds as it happens in RAM, while a query can easily take a few milliseconds to execute (a few thousand times slower because your database is still a file on a harddrive).

I only use code to browse my database for gathering offline data, like when using the /topscore command.
This command gathers data from ALL players to create a sorted list of players based on their score, money, missions completed, ...

For displaying owned/unowned houses, you can browse through your loaded data in the server's memory instead (it's loaded anyways when server is started since you probably need to create and setup 3DTexts at the entrance of the houses) and it will be alot faster too.



I would personally recommend it to save your changes to the database immediately whenever they change on the server.
Why?

If you would have a sudden server-crash and nothing has been saved for days, you lose it all.
Your players won't be happy.

I wouldn't play on a server where the server only saves data when I disconnect.
Some players like myself play for hours at a time without disconnecting.
If the server would crash for some reason (power-failure at the host company, hacker, DDOS attack, rare bug in the script which could cause a endless loop, whatever), I would lose all my progress I made during my playing session and when I find out I'm back to where I was yesterday (the time when I logged in before the crash) and I lost a day of playtime, I wouldn't ever come back to that server.

Every online game server saves progress regularly.

Don't expect your server to be online 24/7 and never have issues.

Save regularly to avoid data-loss.
I save everything whenever it changes.
Of course, if I would log the distance which a player has traveled by car, I won't update that data every millisecond, that's not the idea either.
For fast changing data, it should be sufficient to save that every minute using a timer.

And you won't risk updating data that hasn't been changed either.
I see many scripts saving ALL player-data when they disconnect.
Even admin-level, vip-status, even the character's sex on roleplay servers.
Such data is rarely changed, why update it when it hasn't changed?

Sometimes players login to check on something (if a friend is online perhaps) and logout again.
Such scripts would update all loaded data again, but nothing has changed, not even their money, score, or ownership of a house and more.

Just save when it changes and you'll be fine.

[AmigaBlizzard]

Double-post, sorry

[Anarkien]

Thanks a bunch, AmigaBlizzard. Thorough explanation. I will definitely go that way

[Mauzen]

Actually, there is no house, unless you got it in your script. The "database house" is just a set of numbers and strings without any meaning, like a savegame file from a game - its not the game itself, it just tells the game where to start. The "script house" though, probably some kind of enumerated array, is the stuff that syncs with other players, and the stuff you want to store in your database. The players/clients never access the database, they only access the server and the script, I think you got that wrong somehow.
On server start, you load the house from the database into that array, so the script has direct access to it, and so makes the house actually a house that everyone can use, buy, and see. The script itself shouldnt access the database directly most of the time. There are only very few cases outside the load/save process where this would make sense.

Saving leaves you with 3 possible methods. (save on close, save on changes, save on interval) It turns out that a mixture of all of them is probably the best way to go.
Theres fast changing data. Money, certain XP points, player position, etc. Saving it whenever it changes would be stupid of course. So this should be stored on server close and on player disconnect for best performance.
Then theres data that only changes once in a while, like the ownership of a house. This kind of data can be saved to the database immediately (but remember that this is not needed to sync the house with the other players). It might also be useful to save relevant "fast data" together with it to prevent asynchronous data in case of a server crash (e.g. also save the players money when buying a house, but NOT when buying something cheap that you buy often - food, guns, etc). For most servers, a large part of the data will be such "slow data". Handling this correctly can save you a lot of trouble.
The advantage of having those two types of data saving is that you can ignore the "slow data" in all other saving processes. This leads to method 3, saving in certain intervals. Saving everything every 10 minutes would be a long and painful process that could cause server lag, but leaving out all the "slow data" makes it possible. This method is highly recommended, but optional, and only a fail-save in case the server crashes before saving the data. You dont want your players to lose all the data since their last login, but losing just a few minutes wont hurt that much.

Best thing probably is to take a look at some well-made gamemodes to see how their database management works.

[Anarkien]

Quote:

Originally Posted by Mauzen Actually, there is no house, unless you got it in your script. The "database house" is just a set of numbers and strings without any meaning, like a savegame file from a game - its not the game itself, it just tells the game where to start. The "script house" though, probably some kind of enumerated array, is the stuff that syncs with other players, and the stuff you want to store in your database. The players/clients never access the database, they only access the server and the script, I think you got that wrong somehow. On server start, you load the house from the database into that array, so the script has direct access to it, and so makes the house actually a house that everyone can use, buy, and see. The script itself shouldnt access the database directly most of the time. There are only very few cases outside the load/save process where this would make sense. Saving leaves you with 3 possible methods. (save on close, save on changes, save on interval) It turns out that a mixture of all of them is probably the best way to go. Theres fast changing data. Money, certain XP points, player position, etc. Saving it whenever it changes would be stupid of course. So this should be stored on server close and on player disconnect for best performance. Then theres data that only changes once in a while, like the ownership of a house. This kind of data can be saved to the database immediately (but remember that this is not needed to sync the house with the other players). It might also be useful to save relevant "fast data" together with it to prevent asynchronous data in case of a server crash (e.g. also save the players money when buying a house, but NOT when buying something cheap that you buy often - food, guns, etc). For most servers, a large part of the data will be such "slow data". Handling this correctly can save you a lot of trouble. The advantage of having those two types of data saving is that you can ignore the "slow data" in all other saving processes. This leads to method 3, saving in certain intervals. Saving everything every 10 minutes would be a long and painful process that could cause server lag, but leaving out all the "slow data" makes it possible. This method is highly recommended, but optional, and only a fail-save in case the server crashes before saving the data. You dont want your players to lose all the data since their last login, but losing just a few minutes wont hurt that much. Best thing probably is to take a look at some well-made gamemodes to see how their database management works.

Great stuff, lots of moving parts to think about either way it seems like!
I'll definitely take into consideration all of this. I assume I'll go with a combination of all three methods.

[Slice]

Personally, I link variables to database entries using PVars to store last known values and compare to them every time I do a sync (every 60sec).
The rowid is saved in a PVar so the SQLite overhead becomes as little as possible, since it knows exactly where to perform the update right away, and only update if the value has been changed.
I'll hopefully get around to open-sourcing this code eventually, but for now I'm too busy working on my gamemode.


Just to clarify, I only use PVars to dynamically store the variable name, rowid, and previous value. The script modifies a normal variable just like any other.

The DB is structured like this:

Code:

CREATE TABLE `users` ( `oid` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` text NOT NULL COLLATE NOCASE, `passhash` blob NOT NULL );
CREATE UNIQUE INDEX `oid_users` ON `users` (`oid`);
CREATE UNIQUE INDEX `name_users` ON `users` (`name`);

CREATE TABLE `users_info` ( `uid` integer NOT NULL, `key` text NOT NULL COLLATE NOCASE, `index` integer DEFAULT(NULL), `type` integer NOT NULL, `value` NOT NULL );
CREATE INDEX `key_index_users_info` ON `users_info` (`key`, `index`);
CREATE UNIQUE INDEX `uid_key_index_users_info` ON `users_info` (`uid`, `key`, `index`);
s

Note that "oid" will automatically link to SQLite's rowid. In users_info, however, I just refer to `rowid` in my queries.


The code to sync users is this:

pawn Code:

forward SaveUsers();
public SaveUsers()
{
    new i = 0;

    forEachPlayer (playerid) if (g_LoggedIn[playerid]) {
        if (g_SaveTimer[playerid] != -1) {
            KillTimer(g_SaveTimer[playerid]);
        }

        g_SaveTimer[playerid] = SetTimerEx(#SaveUserData, ++i * 100, false, "i", playerid);
    }

    SetTimer(#LastSaveFinished, i * 100, false);
}

forward LastSaveFinished();
public LastSaveFinished()
{
    CallRemoteFunction(#OnUserDataSave, "");
}

Why? Because it will limit the size of save queries. I my script prints a warning if saving a single user takes more than 3ms, and I have not seen a warning yet..

I did something similar in the past, btw: https://sampforum.blast.hk/showthread.php?tid=311773

[Mauzen]

[QUOTE=Slice;3658917]Personally, I link variables to database entries using PVars to store last known values and compare to them every time I do a sync (every 60sec).
The rowid is saved in a PVar so the SQLite overhead becomes as little as possible, since it knows exactly where to perform the update right away, and only update if the value has been changed.
I'll hopefully get around to open-sourcing this code eventually, but for now I'm too busy working on my gamemode.


Wouldnt it be even more economical if you ignore the last value, and just set a "modified"-flag whenever you change one of the monitored variables in the script (or maybe check the old value before setting it)? That way you could save all those "did it change" checks every 60 seconds, just checking that flag should be measureable faster when working with many variables. (and thats actually a question, I dont know if its actually faster. Probably depends on the script/how often it writes duplicate values to variables)
Though I still think that the slow/fast-changing data split is the best way to reduce script- and query-overhead. Variable saving is a pretty static thing, the script very well knows when they change, and the scripter knows how often they change. Adding many runtime checks to detect variable changes that you already know at script compilation can just make things slower.
Sure, its no plug-and-play solution and requires more planning, but most scripts could use more planning anyways.