DatabaseLayout

Introduction

The database for Earthcall consists of several tables. This page details the current layout of the database and provides the CREATE TABLE statements needed to create each table. Please note, these are all subject to change, and some have already changed several times.

Earthcall uses a SQLITE3 Database stored in a single file. The file must be named "Earthcall.db" and stored in "system/Earthcall.db" (relative path from server binary).

Table config

Simple table to store basic data for the server (server IP and Max. Table numbers).

CREATE TABLE config (
  'serverip' TEXT, 
  'maxtables' INTEGER
);

Table users

Stores the information of a user, including gender, country, clothes and others. Also, save the creation and update date according to PR references.

CREATE TABLE users (
  'username' TEXT, 
  'pass' TEXT, 
  'protectcode' TEXT, 
  'age' TEXT, 
  'sex' TEXT, 
  'region' TEXT, 
  'color' TEXT, 
  'language' TEXT, 
  'birthdate' TEXT, 
  'intelligence' TEXT, 
  'amability' TEXT, 
  'aspect' TEXT, 
  'hasmicro' TEXT, 
  'dt_first' TEXT, 
  'dt_latest' TEXT
);

Table messages

Stores all the messages that the users send between them, except the confirmation messages.

CREATE TABLE messages (
  'sender' TEXT, 
  'destination' TEXT, 
  'content' TEXT
);

Table onlineusers

This table represents the online status of the players. It has the active users, where they are (location) and if they are playing or not a game. Everytime the server stats, this table is reset.

CREATE TABLE onlineusers (
  'username' TEXT, 
  'ip' TEXT, 
  'hasmicro' INTEGER, 
  'location' INTEGER, 
  'status' INTEGER, 
  'timestamp' TEXT, 
  'tableid' TEXT, 
  'position' TEXT, 
  'socknumber' INTEGER
);

Table rankings

Saves the high scores for every game of the four games. Each game can has multiple modes and number of players.

CREATE TABLE rankings (
  'username' TEXT, 
  'location' INTEGER, 
  'playersin' INTEGER, 
  'rankmode' INTEGER, 
  'score' BLOB
);

Table tables

Contains the status of the tables, with the following info:

• ID starts from 0
• Must be from one of the attractions.
• Contains info of the players (gender and clothes).

• Has a min.players, max.players and status (waiting / playing).

  CREATE TABLE tables(
  'gameid' TEXT,
  'tableid' TEXT,
  'status' TEXT,
  'playersin' TEXT,
  'maxplayers' TEXT,
  'playersinfo' TEXT,
  'minplayers' TEXT,
  'cl1' TEXT,
  'cl2' TEXT,
  'cl3' TEXT,
  'cl4' TEXT,
  'gen1' TEXT,
  'gen2' TEXT,
  'gen3' TEXT,
  'gen4' TEXT
  );

Table mistery (optional)

This is a table used in TEML panels to show a joke to the user. Editing in TEML.cpp to disable.

CREATE TABLE mistery (
  'content' TEXT
);