From Tapestries MUCK
Player Table
CREATE TABLE players (
id SERIAL PRIMARY KEY,
name VARCHAR(41) NOT NULL,
password VARCHAR(30) NOT NULL,
email VARCHAR(80) NOT NULL,
r_question VARCHAR(80),
r_answer VARCHAR(30),
-- Administrative information
banned BIGINT, -- NULL = active, 0 = Suspended indefinably, > 0 = time_t when available again.
-- Creation / Recovery
status VARCHAR(20), -- NULL = normal/active. NEW_NEED_KEY, RECOVER_NEED_KEY, ...
key VARCHAR(40),
date_requested BIGINT -- When was this account requested, for use in purging requested and never verified accounts
);
CREATE UNIQUE INDEX players_name ON players (lower(name));
CREATE UNIQUE INDEX players_email ON players (lower(email));
CREATE INDEX players_status ON players (status);
Character Table
CREATE TABLE muck_characters (
id SERIAL PRIMARY KEY,
player_id INTEGER,
-- Stuff that must match up with the Muck
name VARCHAR(30) NOT NULL,
dbref INTEGER,
created BIGINT,
password VARCHAR (30),
-- Administrative information
banned BIGINT, -- NULL = active, 0 = Suspended indefinably, > 0 = time_t when available again.
toaded BOOLEAN
);
CREATE UNIQUE INDEX muck_characters_muck_info ON muck_characters (name, dbref, created); -- Can't have dups...
CREATE INDEX muck_characters_player_id ON muck_characters (player_id);
CREATE INDEX muck_characters_name ON muck_characters (lower(name));
CREATE TABLE character_log (
time BIGINT NOT NULL,
player_id INTEGER,
character_id INTEGER,
ipaddr inet,
type text, -- LOGIN, LOGOUT, MCREATED, CREATED, TOADED, MREJECT, etc. etc.
info text
);
CREATE INDEX character_log_time ON character_log (time);
CREATE INDEX character_log_player_id ON character_log (player_id);
CREATE INDEX character_log_character_id ON character_log (character_id);
CREATE INDEX character_log_ipaddr ON character_log (ipaddr);