Database

This article documents a Zandronum-specific ACS feature which may not be supported by ZDoom and its other child ports.

Zandronum has database support, allowing you to persist data across server restarts.

Note: You need to have port 10666 forwarded to test this online.

Database Functions

Data retrieval and saving

Function	Parameters	Return	Description
GetDBEntry	string namespace, string key	int value	Retrieve a numeric value out of the database.
SetDBEntry	string namespace, string key, int value	void	Set a database row to a specific numeric value.
GetDBEntryString	string namespace, string key	string value	Retrieve a string value out of the database.
SetDBEntryString	string namespace, string key, string value	void	Set a database row to a specific string value.
IncrementDBEntry	string namespace, string key, int value	void	Increment a database row by a specific number.

Reading and writing data to a database is done through an easy-to-use namespace/key/value system. If you put data into the database with a specific namespace and key, you can retrieve that same data by supplying the same namespace and key later.

Namespaces are intended to be roughly analogous to "tables" in other databases, used to split up different sets of data by concern. Namespaces can also be iterated through and sorted.

Iteration and Sorting

Function	Parameters	Return	Description
GetDBEntryRank	string namespace, string key, bool descending	int position	Sort a namespace by value and return the position of the given key in that namespace.
GetDBEntries	string namespace	resource results	Return a resource that contains a pointer to all rows in a namespace.
SortDBEntries	string namespace, int limit, int offset, bool descending	resource results	Return a resource that contains a pointer to rows in the namespace sorted by value. Limit is the maximum number of rows to return. Offset is the starting position to return in the sorted table (a value of 1 will cause the returned database to start at the second highest value).
CountDBResults	resource results	int count	Count the number of rows a resource contains.
GetDBResultKeyString	resource results, int row	string key	Returns the key at the specific row of a resource.
GetDBResultValueString	resource results, int row	string value	Returns the value at the specific row of a resource as a string.
GetDBResultValue	resource results, int row	int value	Returns the value at the specific row of a resource as an integer.
FreeDBResults	resource results	void	Free a resource from memory.

Most of these functions deal with a resource. A resource is simply a number that points to results of a database query. The queries that Zandronum allows you to make is either retrieving all rows in a namespace at once, or retrieving a limited subset of them in a sorted fashion. Once you have a query result as a resource, you then use functions to figure out what exactly that result contains.

Transactions

Function	Parameters	Return	Description
BeginDBTransaction	void	void	Begin a database transaction.
EndDBTransaction	void	void	Commit the database transaction.

A transaction prevents functions that normally write to the database from writing immediately. Instead, they are kept in memory until you end the transaction, at which point it writes everything at once. If you need to write lots of rows to the database, writing them all at once in a transaction is much faster than writing them one by one. Transactions are also guaranteed to be atomic, which means that if Zandronum crashes during the transaction, either everything in a transaction is written to the database or nothing gets written - the database will never be left in a state where only half the rows were written.

A word of warning

Using any ACS database function stops the entire gamesim while it runs the database query. This is normally not much of an issue, but if you start to see performance issues with your modification, looking at how many database queries you're running is a good place to start. If your database queries are causing performance issues, try batching your queries up and running them all at once with a transaction.

Remote Database support

Zandronum does not support connecting to a remote database server over a network. Due to limitations of ACS, database functions lock up the entire game simulation until a result is found - such a scheme would be unworkable over a something as fickle and with as much latency as the Internet.

However, if you know a little bit of non-ACS programming or scripting, you can connect to the on-disk database with any language that supports SQLite3 and do whatever you want with it, including sending it someplace else over a network. If this sounds like fun, but you have no programming experience aside from ACS, this author recommends Python since it works on Windows, Linux and OSX and comes with sqlite3 support out of the box.

Examples

Lets say we want to make a script that keeps track of player deaths.

The following script increments (or if it doesn't exist, creates) a namespace with the player's authentication account name and initializes a section called "Deaths". Whenever a player dies, if they are logged in, their death will be recorded. One namespace can store multiple sections, so you could use the same player name to store "Deaths" and "Frags". The namespace does not need to be the player's authentication name, you could use their ingame name instead. This is a little bit unsafe, however, as it might cause problems when players share the same names.

script "IncrementPlayerDeaths" DEATH
{
    if (PlayerIsLoggedIn(PlayerNumber()))
        IncrementDBEntry(GetPlayerAccountName(PlayerNumber()), "Deaths", 1); 
}

However, instead of incrementing this value every time a player dies, you can do this before the map changes with a transaction. This is benefitial for performance on large databases with many players. Be aware that if a player leaves the game before the map change, their data will not be saved!

#define MAXPLAYERS 64

int global_playerdeaths[MAXPLAYERS];

script "IncrementPlayerDeaths" DEATH
{
    global_playerdeaths[PlayerNumber()]++;
}

script "StorePlayerDeaths" UNLOADING
{
    BeginDBTransaction();
    for (int i=0; i<MAXPLAYERS; i++)
        if (PlayerIsLoggedIn(i))
            IncrementDBEntry(GetPlayerAccountName(i)), "Deaths", global_playerdeaths[i]); 
    EndDBTransaction();
}

And the following script sorts the database and prints the name of the player with the most deaths, as well as how many times they died

script "ShowMostDeaths" (void)
{
    int result = GetDBEntries("Deaths");
    int count = CountDBResults(result);
    
    // Make sure the database has values
    if (count != 0)
    {
        // Free the memory allocated by the first GetDBEntries call
        FreeDBResults(result); 
        
        // Get the list of deaths, sort it, and then print who died the most
        result = SortDBEntries("Deaths", count, 0, true);
        PrintBold(s:"Player ", s:GetDBResultKeyString(result, 0), s:" has the most deaths, having died ", d:GetDBResultValue(result, 0), " time(s).");
    }
    
    // Free the memory allocated by SortDBEntries, or by CountDBResults if it returned zero.
    FreeDBResults(result); 
}