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

Looking for Resources for your mod/project? Want to share some of your creations so others can use them? Post them here!
User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#1

Post by Catastrophe » Sun Feb 14, 2016 7:46 pm

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.
Last edited by Catastrophe on Sun May 07, 2017 8:50 pm, edited 18 times in total.
Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

User avatar
Hypnotoad
Forum Regular
Posts: 527
Joined: Tue May 29, 2012 8:50 pm
Location: Britland

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

#2

Post by Hypnotoad » Mon Feb 15, 2016 12:57 am

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.
Last edited by Hypnotoad on Mon Feb 15, 2016 1:09 am, edited 1 time in total.

User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#3

Post by Catastrophe » Mon Feb 15, 2016 1:07 am

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.
Last edited by Catastrophe on Mon Feb 15, 2016 1:07 am, edited 1 time in total.
Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

User avatar
Hypnotoad
Forum Regular
Posts: 527
Joined: Tue May 29, 2012 8:50 pm
Location: Britland

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

#4

Post by Hypnotoad » Mon Feb 15, 2016 1:12 am

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.

User avatar
Sean
Zandronum Tester
Posts: 835
Joined: Thu Jan 16, 2014 9:09 pm
Location: England
Clan Tag: [TSPG]
Contact:

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

#5

Post by Sean » Mon Feb 15, 2016 7:53 am

Code: Select all

<Decay> csnxs try being less bitter and actually helpful to the community, thanks

User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#6

Post by Catastrophe » Mon Feb 15, 2016 7:55 am

Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

User avatar
Ænima
Addicted to Zandronum
Posts: 3346
Joined: Tue Jun 05, 2012 6:12 pm
Location: Shpongleland

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

#7

Post by Ænima » Mon Feb 15, 2016 12:01 pm

Awesome! I'll be writing some scripts tonight.


Thanks Blue. :hearts:
Image ­ ­
­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­
Doom64: Unabsolved: Doom64 and Diablo2 had a baby


ZandroSkins
: a pack made by our community
Squad Radio: a WASD-powered chat menu, add your own sounds
AeniPuffs: neat blood and puff effects

kristina
Banned
Posts: 20
Joined: Thu Feb 28, 2013 2:33 am
Banned: Permanently

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

#8

Post by kristina » Mon Feb 15, 2016 12:50 pm

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.

User avatar
Ivan
Addicted to Zandronum
Posts: 2115
Joined: Mon Jun 04, 2012 5:38 pm
Location: Omnipresent

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

#9

Post by Ivan » Mon Feb 15, 2016 3:12 pm

Good tutorial. Saves me a lot of time!
=== RAGNAROK DM ON ... uh... dead forever? ===
=== ALWAYS BET ON ... uh... dead forever? ===
=== Who wanta sum wang? ===
=== Death and Decay - A new Monster/Weapon replacer ===

User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#10

Post by Catastrophe » Mon Feb 15, 2016 6:29 pm

Thanks guys. I've also updated OP with Hypnotoad's use of beginDBTransaction and endDBTransaction. Hopefully my explanation is clear enough.
Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

dart620
Banned
Posts: 19
Joined: Fri May 01, 2015 1:44 am
Location: Australia
Contact:
Banned: Permanently

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

#11

Post by dart620 » Mon Feb 15, 2016 7:03 pm

Not trying to doubt your knowledge but "the files are permanent" <- I am pretty sure you can still delete those files.

User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#12

Post by Catastrophe » Mon Feb 15, 2016 8:45 pm

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.
Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

User avatar
mifu
Lead Administrator
Posts: 1024
Joined: Tue May 29, 2012 10:34 am
Location: Aussie Land
Clan: Demon RiderZ

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

#13

Post by mifu » Tue Feb 16, 2016 11:38 am

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
Last edited by mifu on Tue Feb 16, 2016 11:42 am, edited 1 time in total.
Image

User avatar
AlexMax
FNF Team
Posts: 237
Joined: Tue May 29, 2012 10:14 pm
Contact:

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

#14

Post by AlexMax » Sat Feb 27, 2016 1:51 am

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.
Last edited by AlexMax on Sat Feb 27, 2016 2:09 am, edited 1 time in total.
The only limit to my freedom is the inevitable closure of the
universe, as inevitable as your own last breath. And yet,
there remains time to create, to create, and escape.

Escape will make me God.

User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#15

Post by Catastrophe » Sat Feb 27, 2016 7:21 am

Good catch Alex, gonna add your post to OP. Also we have a saveDBEntry function?
Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

Samuzero15tlh
Forum Regular
Posts: 190
Joined: Wed Sep 09, 2015 2:21 pm
Location: In home, sweet Home
Clan Tag: <skr>

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

#16

Post by Samuzero15tlh » Wed Mar 23, 2016 4:54 am

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);

User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#17

Post by Catastrophe » Wed Mar 23, 2016 5:15 am

No, it will automatically all be in one file that you can set with the "databasefile" command.
Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

FascistCat
 
Posts: 98
Joined: Mon Jul 20, 2015 12:51 pm

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

#18

Post by FascistCat » Wed Jun 22, 2016 5:55 pm

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.

User avatar
Catastrophe
ZanStuff Reviewer
Posts: 2534
Joined: Sat Jun 02, 2012 2:44 am
Clan: Incredible

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

#19

Post by Catastrophe » Wed Jun 22, 2016 7:02 pm

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.
Youtube Channel
Projects: Super Demon | Idiotic LMS | ZCC | Cata WeaponZ

Currently hooked on Overwatch and PUBattlegrounds.

marco75
 
Posts: 21
Joined: Fri Jun 08, 2012 7:58 pm

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

#20

Post by marco75 » Fri Jan 06, 2017 2:04 am

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?

Post Reply