User:WhiteWizard/SQL FB Authentication

From Tapestries MUCK

Revision as of 07:39, 7 March 2008 (view source) WhiteWizard (Talk | contribs) (→Character Table) ← Older edit		Latest revision as of 17:19, 7 March 2008 (view source) WhiteWizard (Talk | contribs) (→Player Table)
(10 intermediate revisions not shown)
Line 1:		Line 1:
		+	==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==		==Character Table==
Line 11:		Line 31:
	dbref          INTEGER,		dbref          INTEGER,
	created        BIGINT,		created        BIGINT,
		+	password        VARCHAR (30),
	-- Administrative information		-- Administrative information
-	suspended      BIGINT,  -- NULL = active, 0 = Suspended indefinably, > 0 = time_t when available again.	+	banned          BIGINT,  -- NULL = active, 0 = Suspended indefinably, > 0 = time_t when available again.
	toaded          BOOLEAN		toaded          BOOLEAN
	);		);
	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 (
-	time            BIGINT PRIMARY KEY,	+	time            BIGINT NOT NULL,
	player_id      INTEGER,		player_id      INTEGER,
	character_id    INTEGER,		character_id    INTEGER,
Line 28:		Line 49:
	info            text		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_player_id ON character_log (player_id);
	CREATE INDEX character_log_character_id ON character_log (character_id);		CREATE INDEX character_log_character_id ON character_log (character_id);
	CREATE INDEX character_log_ipaddr ON character_log (ipaddr);		CREATE INDEX character_log_ipaddr ON character_log (ipaddr);

---

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);