database.md

Non-guest users are looked up by username in the user table. Only SELECT queries are run; no inserts or updates are done. Other fields may be added to the table.

• The sort_order field must be unique. An auto-incremented number is fine.

• The username field must be unique. The username entered in Aleph One will be checked against this field. Usernames should be ASCII, and Aleph One currently has a limit of 15 characters.

• The password field contains a bcrypt hash.

• The moderator field grants access to moderator-only commands, like .gag or .kick.

• The hide_in_room field, when set, prevents the user from being announced in player lists. This can be used to suppress join and leave messages from bots or system-monitoring tools.

• The meta_login_token field is used instead of password during HTTPS login authentication. The code that accepts an HTTPS request and populates this field is not provided here; see the Aleph One source code for the client-side API.

• The meta_login_token_date field stores the time meta_login_token was populated. The metaserver rejects logins using a token created more than 60 seconds ago.

    CREATE TABLE user (
      sort_order int,
      username varchar(255),
      password varchar(255),
      moderator boolean,
      hide_in_room boolean,
      meta_login_token varbinary(16),
      meta_login_token_date datetime );
  

Remote hub servers are manually registered in the remotehub table and are sent to clients when requested. Clients in this case are gatherers willing to use remote hubs. Each record corresponds to a running remote hub instance.

• The id field is automatically generated by the database and does not have to be manually inserted. It is used to lookup on which address to advertise and as a way to know which servers are already busy hosting for a gatherer.

• The host field can either contain a hostname or an ipv4. For hostnames, ipv4 resolving is done server side and only addresses are sent to clients.

• The port field contains the listening port of the remote hub server.

• The network_version field contains the Aleph One kNetworkSetupProtocolID. It is used to filter the remote hub list based on gatherers network version to send them only compatible servers.

     CREATE TABLE remotehub(
      id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      host VARCHAR(255) NOT NULL,
      port SMALLINT UNSIGNED NOT NULL,
      network_version VARCHAR(50) NOT NULL,
      UNIQUE (host, port) );
  

Other tables are used for logging. Only INSERT statements are run; no selects or updates are done. Other fields, such as an auto-increment ID, may be added to the tables. If logging is disabled in the config file, these tables do not need to be present.

Aleph One currently uses MacRoman character encoding for non-ASCII text. The server does no character conversion; strings are stored as-is in the database. Fields that may contain MacRoman text are designated below as varbinary or blob.

    CREATE TABLE chatlog (
      event_date datetime,
      event_type varchar(64),
      user_id int,
      username varchar(255),
      chatname varbinary(255),
      color_r int unsigned,
      color_g int unsigned,
      color_b int unsigned,
      target_user_id int,
      target_username varchar(255),
      target_chatname varbinary(255),
      message blob );
    
    CREATE TABLE eventlog (
      event_date datetime,
      event_type varchar(64),
      username varchar(255),
      user_id int,
      extradata blob );

    CREATE TABLE logindetail (
      event_date datetime,
      username varchar(255),
      user_id int,
      chatname varbinary(255),
      color_r int unsigned,
      color_g int unsigned,
      color_b int unsigned,
      team_color_r int unsigned,
      team_color_g int unsigned,
      team_color_b int unsigned,
      build_date datetime,
      platform_type int unsigned );