SQLite

Store data in a local file with SQLite, sync or async.

SQLite needs no server: the database is a file. It is ideal for small and medium servers.

Open and create

knight

let DB = 0;

system::start() {
    DB = sqlite_open("data/server.db");
    sqlite_exec(DB, "CREATE TABLE IF NOT EXISTS accounts (name TEXT PRIMARY KEY, hash TEXT, score INTEGER DEFAULT 0)");
};

Synchronous queries

knight

// Returns a result index, or null on error.
let r = sqlite_query(DB, "SELECT score FROM accounts WHERE name = ?", PlayerName(playerid));
if (r != null) {
    let score = cache_get_value_name_int(r, 0, "score");
}

// Returns null on success, or an error string.
let err = sqlite_exec(DB, "INSERT OR REPLACE INTO accounts (name, score) VALUES (?, ?)", PlayerName(playerid), 10);

Warning

Synchronous queries run on the tick loop. A slow query stalls every player. Use the async form for anything that might be slow.

Async queries

knight

function onLoaded(result, err) {
    if (err != "") { Log("error: " + err); return; }
    Log("rows = " + str(cache_get_row_count(result)));
}

player::command("/load") {
    sqlite_query_async(DB, "onLoaded", "SELECT * FROM accounts");
};

Path

The path is relative to the server's working directory. Use an absolute path or create the folder first.

Encryption and backups

SQLite files can be copied while the server runs if you use VACUUM INTO 'backup.db'.
Passwords are hashed with hash_password / verify_password (bcrypt).
Never store plaintext passwords.