SQL Overview

This project contains several SQL files and embedded queries that populate or extract data from a MySQL database. The sections below document the tables involved and the fields they map.

Social Definitions

The area/socials.sql script loads emote definitions into a socials table. Each INSERT statement specifies a social name and up to eight context-sensitive messages:

Code Name	Human Name
`name`	Social command
`message1`	Actor text when used without a target
`message2`	Room text when used without a target
`message3`	Actor text when targeting another player
`message4`	Room text when targeting another player
`message5`	Target text when another player is specified
`message6`	Message when the target is absent
`message7`	Actor text when targeting self
`message8`	Room text when targeting self

Example record:

INSERT INTO socials
  (name, message1, message2, message3, message4, message5, message6, message7, message8)
VALUES
  ('kowtow', 'You kowtow$h before the heavens.', '$n$h kowtows before the heavens.',
   'You kowtow$h before $N. May $E live a thousand years.', '$n kowtows$h before $N.',
   '$n kowtows$h before you. May you live a thousand years.',
   'You kowtow to the spirit of the person you seek.',
   'You kowtow$h to yourself. You wish your life is long and prosperous.',
   '$n kowtows$h to $mself. May he live a long and prosperous life.');

Game Statistics Logging

Two C modules record overall game statistics by inserting rows into a gamestats table.

src/mysql.c implements insert_gamestats_db, which composes a single INSERT statement capturing totals such as creations, deletions, logins, locks, and configuration flags.
src/sql.c contains a CREATE TABLE gamestats definition mirroring these fields and a duplicate insert_gamestats_db helper used by the do_insertstats command.

Fields written to gamestats include:

Code Name	Human Name
`tcreations`	Players created since last reboot
`creations`	Total players created
`tdeletions`	Players deleted since last reboot
`deletions`	Total players deleted
`tlogins`	Logins since last reboot
`logins`	Total logins
`bank_hours`	Bank hour mode (0=unlimited, 1=regular)
`mortlock`	Mortal-lock active flag
`wizlock`	Wizard-lock active flag
`newlock`	Newbie-lock active flag
`arena_status`	Arena running flag
`color_notes`	Colored notes allowed
`color_notes_level`	Minimum level for colored notes
`zombies`	Zombie plague active
`bonus_charms`	Bonus charms active
`auto_police`	Spam auto-policing enabled
`spam_threshold`	Spam message threshold
`spam_level`	Max level subject to spam policing

Note: Use madmode mysql <gamestats|items|helps|mobiles> <on|off> in game to control MySQL exports without recompiling. The command persists your choice to config.cfg, so you can keep items, helps, or mobiles disabled while leaving gamestats active when desired. Use madmode bonus <0-1000> to tune the global experience bonus, which is stored alongside the other madmode settings in config.cfg.

Data Export Routines

In-game MySQL export routines were removed to improve stability, and the server no longer serialises runtime data to MySQL. Any tooling that previously read from src/export.c should instead rely on the offline CSV snapshots in tools/ and item_data.csv.

Player Statistics Tooling

The legacy Perl scripts (quick.pl and output.pl) were retired in favour of the PHP front end that drives the MadROM stats dashboard. Two components now handle leaderboards and game summaries:

A shared database helper pools mysqli connections for the main, gamestats, and socials databases, sourcing credentials from environment variables before falling back to the legacy development defaults.
The public leaderboard endpoint runs predefined SELECT queries (experience, deaths, kills, clan points, saves, and played time) and renders the latest gamestats snapshot for the web dashboard.

These scripts keep SQL strings in code, avoid exposing credentials in documentation, and close pooled connections when each request finishes.

Note: Do not publish live database passwords in changelogs or guides. Use the environment overrides defined in includes/db.php and includes/db2.php for deployment-specific secrets.

Code Name	Human Name
`name`	Player name
`exp`	Experience points
`kills`	Total kills
`deaths`	Total deaths
`zaps`	Zaps received
`hp`	Current hit points
`age`	Character age
`levl`	Level