EndCore Framework

Database

The MySQL tables en-core creates and migrates on boot, their columns, and when player data is saved.

en-core stores characters, groups and placed world content in MySQL through oxmysql. You do not have to import anything: en-core creates and updates its own tables every time it starts.

Setup

  • Set mysql_connection_string in server.cfg and make sure oxmysql starts before en-core.
  • Importing encore.sql from the en-core folder is optional. It documents the players, encore_groups and encore_group_members tables. It does not include encore_content, which en-core creates on its own.
  • If setup fails, the console prints Database setup failed: ... with a hint to check mysql_connection_string, and the core will not work.
cfg
set mysql_connection_string "mysql://<user>:<password>@localhost/<database>?charset=utf8mb4"
ensure oxmysql
ensure en-ui
ensure en-core

Other EndCore resources create their own tables. Those are covered on each resource's page.

Tables

TableHoldsCreated by
playersOne row per characterserver/main.lua
encore_groupsPlayer groupsserver/groups.lua
encore_group_membersGroup membership and rankserver/groups.lua
encore_contentContent placed in gameserver/content.lua

players

One row per character. A license can own several characters (3 slots by default).

ColumnTypeNotes
idINT AUTO_INCREMENTPrimary key
citizenidVARCHAR(50)Unique
licenseVARCHAR(60)Plain index, so one license can have many characters
nameVARCHAR(255)"First Last"
moneyLONGTEXTJSON, for example {"cash":500,"bank":1000}
charinfoLONGTEXTJSON
jobLONGTEXTJSON of the active job
jobsLONGTEXT, nullableJSON map of job name to grade
metadataLONGTEXTJSON of every metadata key
positionLONGTEXTJSON { x, y, z, heading }
last_updatedTIMESTAMPUpdated on every write

The group a character belongs to is not stored here. It is attached at login from the group tables. See Player data for what the JSON columns contain.

encore_groups

ColumnTypeNotes
idINT AUTO_INCREMENTPrimary key
nameVARCHAR(32)Unique
tagVARCHAR(8)Unique
created_atTIMESTAMP

encore_group_members

ColumnTypeNotes
citizenidVARCHAR(50)Primary key, so a character can be in one group
group_idINTForeign key to encore_groups.id, deleted with the group
gradeTINYINT, default 0The member's rank. Named grade because RANK is a reserved word in MySQL 8.
joined_atTIMESTAMP

encore_content

ColumnTypeNotes
kindVARCHAR(40)Part of the primary key
idVARCHAR(80)Part of the primary key
dataLONGTEXTJSON
updated_byVARCHAR(100), nullableWho saved it
updated_atTIMESTAMPUpdated on every write

The primary key is (kind, id). See Content registry.

Full schema

This is what en-core creates when the tables do not exist yet:

sql
CREATE TABLE IF NOT EXISTS `players` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `citizenid` VARCHAR(50) NOT NULL,
    `license` VARCHAR(60) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `money` LONGTEXT NOT NULL,
    `charinfo` LONGTEXT NOT NULL,
    `job` LONGTEXT NOT NULL,
    `jobs` LONGTEXT,
    `metadata` LONGTEXT NOT NULL,
    `position` LONGTEXT NOT NULL,
    `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `citizenid` (`citizenid`),
    KEY `license` (`license`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `encore_groups` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL,
    `tag` VARCHAR(8) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `name` (`name`),
    UNIQUE KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `encore_group_members` (
    `citizenid` VARCHAR(50) NOT NULL PRIMARY KEY,
    `group_id` INT NOT NULL,
    `grade` TINYINT NOT NULL DEFAULT 0,
    `joined_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `group_id` (`group_id`),
    CONSTRAINT `fk_group_members_group` FOREIGN KEY (`group_id`)
        REFERENCES `encore_groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `encore_content` (
    `kind` VARCHAR(40) NOT NULL,
    `id` VARCHAR(80) NOT NULL,
    `data` LONGTEXT NOT NULL,
    `updated_by` VARCHAR(100) DEFAULT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`kind`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Boot migrations

Every start, en-core also fixes databases from older builds:

  1. Creates players if it does not exist.
  2. Drops any unique index on players.license and adds a plain index. Early builds limited each account to one character.
  3. If old gang or gangs columns exist and are NOT NULL, makes them nullable so new rows can be inserted. They are never deleted and never read.
  4. Creates the group tables, then loads all groups into memory.

The content table is created and loaded separately when the content registry starts.

When data is saved

WhenWhat is saved
Every updateInterval (5 minutes by default, config/shared.lua)Every online player, in one batched query
A player disconnectsTheir character, including last position
A player logs out to character selectionTheir character
/saveall or the SaveAllPlayers() exportEvery online player
en-core stopsEvery online player
txAdmin announces a shutdownEvery online player
Group changesWritten immediately
SaveContent / DeleteContentWritten in the background right away

player.Functions.Save() saves one character on demand. It reads the ped's position on the server and skips an invalid 0, 0, 0 position.

Warning

en-core keeps online players in memory and writes over their row when it saves. Don't edit an online character's row directly in the database; your change will be overwritten. Use the exports, or edit while the character is offline.

Examples

List the characters on a license:

sql
SELECT citizenid, name, last_updated
FROM players
WHERE license = 'license:<license>'
ORDER BY id ASC;

Find every member of a group with their character name:

sql
SELECT m.citizenid, p.name, m.grade, m.joined_at
FROM encore_group_members m
LEFT JOIN players p ON p.citizenid = m.citizenid
WHERE m.group_id = 4
ORDER BY m.grade DESC;

The same from Lua, for an offline edit:

lua
local player = exports['en-core']:GetOfflinePlayer('ENCAB12CD34')
if player then
    player.Functions.SetMoney('bank', 5000, 'Refund')
    player.Functions.Save()
end