Page 1 of 2

Databases, accounts, and you. Part 1: basic tutorial.

Posted: Sun Feb 14, 2016 7:46 pm
by Catastrophe
Disclaimer: This tutorial assumes you have some background knowledge in ACS and have a basic understanding of running servers.

What are databases?

In simple terms, databases are basically files that hold values. What makes databases so special is that since values are stored on a file independent of Zandronum itself, they don't get "reset" like normal variables do in ACS after you close-out of Zandronum. So if you had a value stored on a database that says "x" = 5, then "x" will remain 5 even after the server shuts down.

IMPORTANT: For this feature to even work properly you need to create a database file for Zandronum to use, otherwise the values will be saved in a temporary file and deleted after Zandronum is closed. To create a database file, simply type in "databasefile namehere" on the server console. You will get a message afterwards if it was created successfully.

What are accounts?

Zandronum's account system is a feature used to uniquely identify people. This is mainly used to link variables from the database to specific accounts. To use accounts, you need to provide an authentication server for users to login. Fortunately there are already authentication servers set up already so don't worry too much. Do keep in mind that if two Zandro servers are using different authentication servers (ie grandvoid and funcrusher), then user will have to create an account for both of them. Just remember, the only thing authentication servers do is log you in and tell the Zandronum server that you are who you are, it does not store database files~~~!!!

IMPORTANT: You must set an authentication server for this feature to even work. To set an authentication server just do authhostname "authhosturl:porthere". Don't use your own IP for this, I recommend using Zandronum's official authentication server as it has the most registered users. You also need to restart the server for this to take effect, if you cannot restart I recommend using a config. Adding an auth server to a config is simple; just add "authhostname auth.zandronum.com:16666" anywhere on your config if you wanna use Zandronum's official auth server.

If the user has created an account on the authentication server, then they can log-in by typing in "login username password" in the console, it will give you a message saying "login successful" if you did it right. Yes they must do this every time, I recommend either binding this to a key or use ZCC's automatic login feature instead of typing it out every time. Also keep in mind changing maps using the "map" command will force all users to be logged-off. I highly recommend that you disable map votes entirely and use changemap instead.

Why should I use this?

In my opinion, databasefiles and accounts are one of the best things to have ever come out of Zandronum. By being able to save values on a database and then link the values to an account allows us to create many features such as Hiscores, Shops, EXP systems, Statistics, etc - all with JUST acs.

Some notable examples are Funcrushers ranked duel ELO system, Idiotic LMS's hiscores, etc. There aren't really that many mods utilizing this amazing feature unfortunately. Hopefully this tutorial will change that.

How do I use this?

Since this tutorial is aimed for beginners, the basic database ACS functions you need to know are stated here.
The account-related ACS functions you need to know are playerIsLoggedIn() and getPlayerAccountName().
You will also need to update your ACS compiler so it recognizes these commands, you can download the up-to-date compiler here.
I also recommend that you use database reading program such as sqlite browser to see what's in the databasefile for debugging. For example, here is what Idiotic LMS's database looks like.

For my first example, I'll create a simple script that permanently keeps track of how many times a user has died in non-cooperative gamemodes (So deathmatch). Note that this script will only run if you're using a valid authentication-server, a databasefile, and the user is logged-in. Also note that this script is NOT good but gets the job done. I will explain why below.

Here is the death-tracking script.

If you run this script and die a bunch of times, puking script 2 will show how many times you've died even if the server restarts. But there is a drawback. If you are new to the server and have never had a "deaths" counter made for you before, it has to be created for your account in the databasefile. This will cause the server to momentarily freeze until it has initialized the "deaths" variable for your account. In our case, the server will freeze momentarily on your first death to initialize the "deaths" variable for your account, but it won't freeze afterwards because it has been initialized. This means that for every new account's first death, the server will momentarily freeze.

This can be quite a problem in a live-game. Unfortunately there is no way to prevent the server from freezing, but we can control when the server freezes. What I usually do is keep track of deaths for each player using an array, and then add those deaths to the databasefile after the round is over so it doesn't interrupt the game. New code is marked as "[NEW]"

Here is a possible solution.

The benefits of this solution is that the server won't freeze during the game, and instead when the round ends. The drawback is that if the player disconnects mid-game his deaths won't be added to the counter. This is a trade-off that you will just have to deal with. But there is also one more improvement we can make.

Solution improved.

This improved solution only changes script 3, where we introduce two new functions called beginDBTransaction and endDBTransaction. These two functions causes script 3 to not run any database functions between beginDBTransaction and endDBTransaction, and instead merges the database functions into one big function and executes it when we hit endDBTransaction.

The main benefit of this is that it makes our script more efficient. To put it simply, beginDBTransaction and endDBTransaction allows us to merge multiple database functions into one call. This is good because then we're only writing to the database all at once instead of multiple times. Don't use this if you are only reading values from the database. Using this with getDBEntry is a big no no because that function doesn't actually write to the database.

That's all from me, here's an important message from AlexMax (the guy who actually coded this system) about the importance of incrementDBEntry.
Spoiler: AlexMax (Open)
I think I should call special attention to incrementDbEntry. If you ever need to add or subtract a number from a database entry, you should ALWAYS use this function, instead of getDBEntry, adding or subtracting in ACS, and then saveDBEntry.

Why? It's very simple. If you ever have two servers that share the database for something common, and they both attempt to modify the same key at the same time, bad things will happen. For example, let's say that we have two servers that are both interested in the "count" key, and want to increment it by one to keep track of...something, it doesn't matter. Most of the time, this will happen:
  • The database starts with the value 0 in the "count" key.
  • Server 1 gets the "count" as 0
  • Server 1 adds 1 to its count and saves the "count" as 1
  • Some time later, the server 2 gets the "count" as 1
  • Server 2 adds 1 to its count and saves the "count" as 2
Things work as expected....until both of the servers try to update the count at the exact same time. Then, this happens:
  • The database starts with the value 0 in the "count" key.
  • Server 1 gets the "count" as 0
  • Before Server 1 can save anything, server 2 also gets the "count" as 0
  • Server 1 adds 1 to its count and saves the "count" as 1
  • Server 2 adds 1 to its count and also saves the "count" as 1
This is called a race condition. You don't want race conditions. Instead, use incrementDBentry, and this will happen:
  • The database starts with the value 0 in the "count" key.
  • Server 1 tells the database to increment by 1 - "count" is now 1.
  • Server 2 tells the database to increment by 1 - "count" is now 2.
See? There is no round-trip time from the database to ACS and back again, thus there is no window of opportunity for two servers to get mixed up.

Obviously, doing this is not always possible - sometimes you need to run a complicated formula like ELO calculations on the value, which is outside the scope of the incrementor function. However, there are also sometimes mitigating factors - for example, if you are updating somebody's ELO, they're more than likely not on more than one server at the same time.

The point is, the question of "What would happen if two servers ran these database functions at almost the same time" should always be foremost in your mind.
....And that's all for now. If you have questions, comment below.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 12:57 am
by Hypnotoad
I should mention that the stock ACC does not support EVENT scripts, so you'll either have to use Zandronum's new ACC ( https://bitbucket.org/Torr_Samaho/acc ), or use Positron's BCC: http://zandronum.com/forum/showthread.php?tid=4864

Also remember to always use transactions (see the wiki) when you are writing multiple entries in one instance.

edit: so for instance in the second script you could save further bandwidth and cpu load by doing this

Code: Select all

script 3 (int type, int arg1, int arg2) EVENT
{
    // This if-statement will only run when the round ends
    if(type == GAMEEVENT_ROUND_ENDS) 
    {
        BeginDBTransaction();
        // We will now loop through each player and add deaths accordingly.
        for(int i = 0; i < 64; i++)   
        {
            if(!playerIsLoggedIn(i)) continue; // If the player "i" isn't logged in, we skip them.
            
            incrementDbEntry(getPlayerAccountName(i), "deaths", deathTracker[i]); // The player is logged in if we reach here, so increment.
            deathTracker[i] = 0; // We set the array to 0 to be safe.
        }
        EndDBTransaction();
    }
}
This waits for the loop to finish, and then writes all the data all at once to the database, rather than writing to the database up to 64 times, which is much safer and less computationally expensive.

Also note that I replaced '6' with GAMEEVENT_ROUND_ENDS, BCC should understand this.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 1:07 am
by Catastrophe
Hypnotoad wrote: I should mention that the stock ACC does not support EVENT scripts, so you'll either have to use Zandronum's new ACC ( https://bitbucket.org/Torr_Samaho/acc ), or use Positron's BCC: http://zandronum.com/forum/showthread.php?tid=4864

Also remember to always use transactions (see the wiki) when you are writing multiple entries in one instance.
That link to torr's doesn't help anyone. You should link to the actual zip file instead of the source.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 1:12 am
by Hypnotoad
Catastrophe wrote:
Hypnotoad wrote: I should mention that the stock ACC does not support EVENT scripts, so you'll either have to use Zandronum's new ACC ( https://bitbucket.org/Torr_Samaho/acc ), or use Positron's BCC: http://zandronum.com/forum/showthread.php?tid=4864

Also remember to always use transactions (see the wiki) when you are writing multiple entries in one instance.
That link to torr's doesn't help anyone. You should link to the actual zip file instead of the source.
Unfortunately I'm not sure the binary is hosted anywhere currently - which is one of the reasons I've been using BCC.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 7:53 am
by Sean

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 7:55 am
by Catastrophe

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 12:01 pm
by Ænima
Awesome! I'll be writing some scripts tonight.


Thanks Blue. :hearts:

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 12:50 pm
by kristina
Was not mifo going to make the auth server for everyone? Or was that just a lie? Now your telling people to use alexMax auth. This will lead to chaos.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 3:12 pm
by Ivan
Good tutorial. Saves me a lot of time!

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 6:29 pm
by Catastrophe
Thanks guys. I've also updated OP with Hypnotoad's use of beginDBTransaction and endDBTransaction. Hopefully my explanation is clear enough.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 7:03 pm
by dart620
Not trying to doubt your knowledge but "the files are permanent" <- I am pretty sure you can still delete those files.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Mon Feb 15, 2016 8:45 pm
by Catastrophe
dart620 wrote: Not trying to doubt your knowledge but "the files are permanent" <- I am pretty sure you can still delete those files.
Yes, you are correct. I'll fix the wording on that.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Tue Feb 16, 2016 11:38 am
by mifu
kristina wrote: Was not mifo going to make the auth server for everyone? Or was that just a lie? Now your telling people to use alexMax auth. This will lead to chaos.
1. Nice typo.
2. Yes there is going to be an auth server. We are working out things like data migration n shit. The biggest lie here is your post.
3. You imply Cata is dumb. Im sure he will update the information when the auth server is ready.

PS: Catastrophe.. nice tutorial man. This has helped me out alot. Cheers

RE: Databases, accounts, and you. A basic tutorial.

Posted: Sat Feb 27, 2016 1:51 am
by AlexMax
Cata, I think you meant to use beginDBTransaction and endDBTransaction in the final paragraph of your original post.

Also, I think I should call special attention to incrementDbEntry. If you ever need to add or subtract a number from a database entry, you should ALWAYS use this function, instead of getDBEntry, adding or subtracting in ACS, and then saveDBEntry.

Why? It's very simple. If you ever have two servers that share the database for something common, and they both attempt to modify the same key at the same time, bad things will happen. For example, let's say that we have two servers that are both interested in the "count" key, and want to increment it by one to keep track of...something, it doesn't matter. Most of the time, this will happen:
  • The database starts with the value 0 in the "count" key.
  • Server 1 gets the "count" as 0
  • Server 1 adds 1 to its count and saves the "count" as 1
  • Some time later, the server 2 gets the "count" as 1
  • Server 2 adds 1 to its count and saves the "count" as 2
Things work as expected....until both of the servers try to update the count at the exact same time. Then, this happens:
  • The database starts with the value 0 in the "count" key.
  • Server 1 gets the "count" as 0
  • Before Server 1 can save anything, server 2 also gets the "count" as 0
  • Server 1 adds 1 to its count and saves the "count" as 1
  • Server 2 adds 1 to its count and also saves the "count" as 1
This is called a race condition. You don't want race conditions. Instead, use incrementDBentry, and this will happen:
  • The database starts with the value 0 in the "count" key.
  • Server 1 tells the database to increment by 1 - "count" is now 1.
  • Server 2 tells the database to increment by 1 - "count" is now 2.
See? There is no round-trip time from the database to ACS and back again, thus there is no window of opportunity for two servers to get mixed up.

Obviously, doing this is not always possible - sometimes you need to run a complicated formula like ELO calculations on the value, which is outside the scope of the incrementor function. However, there are also sometimes mitigating factors - for example, if you are updating somebody's ELO, they're more than likely not on more than one server at the same time.

The point is, the question of "What would happen if two servers ran these database functions at almost the same time" should always be foremost in your mind.

RE: Databases, accounts, and you. A basic tutorial.

Posted: Sat Feb 27, 2016 7:21 am
by Catastrophe
Good catch Alex, gonna add your post to OP. Also we have a saveDBEntry function?

Re: Databases, accounts, and you. Part 1: basic tutorial.

Posted: Wed Mar 23, 2016 4:54 am
by Samuzero15tlh
Thanks cata! I was crazy for a tutorial of this kind!
I got a lil ask: In the name space part, I really need to create database file for each player name? is there a way to create them in a single database file?
incrementDbEntry(getPlayerAccountName(playernumber()), "deaths", 1);

Re: Databases, accounts, and you. Part 1: basic tutorial.

Posted: Wed Mar 23, 2016 5:15 am
by Catastrophe
No, it will automatically all be in one file that you can set with the "databasefile" command.

Re: Databases, accounts, and you. Part 1: basic tutorial.

Posted: Wed Jun 22, 2016 5:55 pm
by FascistCat
Question: This "databasefile" is a SQLIte3 file that is selected using the console? If so, we can add a command to our config file so it automatically "mounts" the database, right?

Also, for being able to use databases, all of this should be done on a private server, right? So no possibilities of using db's on automated clusters.

Re: Databases, accounts, and you. Part 1: basic tutorial.

Posted: Wed Jun 22, 2016 7:02 pm
by Catastrophe
FascistCat wrote:Question: This "databasefile" is a SQLIte3 file that is selected using the console? If so, we can add a command to our config file so it automatically "mounts" the database, right?
Yep, in the config just do "databasefile namehere" and it will automatically load the database if it exists, or generate it otherwise.
FascistCat wrote:Also, for being able to use databases, all of this should be done on a private server, right? So no possibilities of using db's on automated clusters.
Using it on a non-automated server cluster is ideal, but using BE is ok as long as no one knows what databasefile you are using. If someone figures it out they can potentially create wads that can tamper with it.

Re: Databases, accounts, and you. Part 1: basic tutorial.

Posted: Fri Jan 06, 2017 2:04 am
by marco75
None of the hastebin.com links work for me, have they expired?

Can you edit post to show code with

Code: Select all

code
tag instead?

How do i export the database to a file if I'm running the Zandronum client?