Database

Database tables can be created with the file browser (with /DB/* rights), multi-column indices must be created manually be the server admin. Following function exist to access the database. See DB Reference for a full list of available functions.

• DB.load(tablename, options [, function(entries[]) { }]) - loads an array of DB entries, options specifies the attributes (exact match) and the third optional parameter is a callback to receive the entries, but using async/await is the suggested way to receive the entries.
• DB.save(tablename, options) - inserts or updates an object into the database. Unsupported attributes will make the call fail.
• DB.delete(tablename, options) - deletes DB entries, options specified the attributes (exact match)

Example insert:

function onUpdated(pl) {
    DB.save("supportticket", {
        userid: pl.id,
        date_created: new Date(),
        nickname: pl.name,
        category: "general",
        subject: "Test2",
        description: "Test " + uuid()
    });
}  

Example load and update:

async function onUpdated(pl) {
    let tickets = await DB.load("supportticket", {});
    this.say("Load tickets from DB: " + tickets.length + " - " + tickets[0].description);

    tickets[0].description = "Update Test " + uuid();
    DB.save("supportticket", tickets[0]);
}

Notice the above async in-front of function and the await keywords before DB.load. This is similar to using the old callback way to load entries, as seen below. DB.save and DB.delete also has an third optional parameter for callback when entries has been saved/deleted from database, incase you want to wait for it to properly be saved/deleted.

function onUpdated(pl) {
    let self = this;
    DB.load("supportticket", {}, function(tickets) {
        self.say("Load tickets from DB: " + tickets.length + " - " + tickets[0].description);

        tickets[0].description = "Update Test " + uuid();
        DB.save("supportticket", tickets[0]);
    });
}

Example delete:

function onUpdated(pl) {
    DB.delete("supportticket", {ticket_id: 2});
}

Joins & More

With the options parameter you can also specify limit & more:

• offset: number, can be used to skip a number of results
• limit: number, limit the number of the results
• orderby: string, specifies a column to sort the results by, use "field ASC" for ascending sort order (default) and "field DESC" for descending sort order
• join: array of objects, use this to fetch associated entries of another table, most common case is to specify "table1.field1": "table2.field2"
  • table: the table name, certain read-only tables can also be used preceded by "game."
  • as: rename the table in results
  • required: boolean, optional, set to true for "inner join" (must exists) or to false for "left join" (optional, default)
  • fieldname: value or other table field name which must match

Example of limit, offset, and join:

async function onUpdated() {
    let clanpolls = await DB.load("clanpolls", {
        clanid: 1,
        limit: 10,
        offset: 0,
        join:[
            {table:"clanpolltypes", "clanpolltypes.polltype":"clanpolls.polltype"},
            {table:"clanvotes", "clanvotes.pollid":"clanpolls.pollid", userid:pl.id, required:false},
            {table:"game.players", "players.userid":"clanpolls.userid"}
        ]
    };
    this.say("Clan polls from DB: " + clanpolls.length);
}

Example with orderby and join, see the use of "as" for joining the same table twice:

async function onUpdated() {
    let matches = await DB.load("tournament_match", {
        orderby: "id ASC",
        join:[
            {table:"game.players", as:"participant1", "participant1.userid":"tournament_match.participant1"},
            {table:"game.players", as:"participant2", "participant2.userid":"tournament_match.participant2"}
        ]
    });
    this.say("Tournament matches from DB: " + matches.length);
}