User:WhiteWizard/SQL FB Authentication
From Tapestries MUCK
< User:WhiteWizard(Difference between revisions)
WhiteWizard (Talk | contribs) (→Player Table) |
WhiteWizard (Talk | contribs) (→Player Table) |
||
(One intermediate revision not shown) | |||
Line 15: | Line 15: | ||
status VARCHAR(20), -- NULL = normal/active. NEW_NEED_KEY, RECOVER_NEED_KEY, ... | status VARCHAR(20), -- NULL = normal/active. NEW_NEED_KEY, RECOVER_NEED_KEY, ... | ||
key VARCHAR(40), | key VARCHAR(40), | ||
- | date_requested BIGINT | + | 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_name ON players (lower(name)); | ||
CREATE UNIQUE INDEX players_email ON players (lower(email)); | CREATE UNIQUE INDEX players_email ON players (lower(email)); | ||
Line 39: | Line 39: | ||
CREATE UNIQUE INDEX muck_characters_muck_info ON muck_characters (name, dbref, created); -- Can't have dups... | 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_player_id ON muck_characters (player_id); | ||
- | CREATE INDEX muck_characters_name ON muck_characters (name); | + | CREATE INDEX muck_characters_name ON muck_characters (lower(name)); |
CREATE TABLE character_log ( | CREATE TABLE character_log ( |
Latest revision as of 17:19, 7 March 2008
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);