223 lines
6.4 KiB
SQL
223 lines
6.4 KiB
SQL
-- LegacyWorlds Beta 5
|
|
-- PostgreSQL database scripts
|
|
--
|
|
-- 12-main-tables.sql
|
|
--
|
|
-- Initialises the part of the database that contains
|
|
-- the user accounts, logs ...
|
|
--
|
|
-- Copyright(C) 2004-2007, DeepClone Development
|
|
-- --------------------------------------------------------
|
|
|
|
|
|
|
|
-- Connect to the database
|
|
\c legacyworlds legacyworlds_admin
|
|
|
|
|
|
|
|
--
|
|
-- Create the account table
|
|
--
|
|
|
|
CREATE TABLE main.account (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
name VARCHAR(15) NOT NULL UNIQUE,
|
|
email VARCHAR(128) NOT NULL UNIQUE,
|
|
password VARCHAR(64) NOT NULL,
|
|
status VARCHAR(6) NOT NULL DEFAULT 'NEW' REFERENCES main.acnt_status (txt),
|
|
conf_code VARCHAR(16) NULL,
|
|
reason TEXT NULL,
|
|
vac_credits INT NOT NULL DEFAULT 30 CHECK(vac_credits BETWEEN 0 AND 240),
|
|
vac_start INT NULL CHECK(vac_start IS NULL OR (vac_start IS NOT NULL AND vac_credits > 0)),
|
|
quit_ts INT NULL CHECK(quit_ts IS NULL OR quit_ts > 0),
|
|
last_login INT NULL CHECK(last_login IS NULL OR last_login > 0),
|
|
last_logout INT NULL CHECK(last_logout IS NULL OR last_logout > 0),
|
|
pw_conf VARCHAR(16) NULL,
|
|
admin BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
|
|
CREATE INDEX account_status ON main.account(status);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.account TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.account_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- The following table stores queued game registrations.
|
|
--
|
|
|
|
CREATE TABLE main.reg_queue (
|
|
account BIGINT NOT NULL PRIMARY KEY REFERENCES main.account (id)
|
|
ON DELETE CASCADE,
|
|
game VARCHAR(16) NOT NULL
|
|
);
|
|
GRANT SELECT,INSERT,DELETE ON main.reg_queue TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Create the table that stores kick requests
|
|
--
|
|
|
|
CREATE TABLE main.adm_kick (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
to_kick BIGINT NOT NULL REFERENCES main.account (id),
|
|
requested_by BIGINT NOT NULL REFERENCES main.account (id),
|
|
requested_at INT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW())),
|
|
reason TEXT NOT NULL,
|
|
status CHAR(1) NOT NULL CHECK(status IN ('P','Y','N')),
|
|
examined_by BIGINT REFERENCES main.account (id)
|
|
);
|
|
|
|
CREATE INDEX adm_kick_to_kick ON main.adm_kick (to_kick);
|
|
CREATE INDEX adm_kick_req_by ON main.adm_kick (requested_by);
|
|
CREATE INDEX adm_kick_exam_by ON main.adm_kick (examined_by);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.adm_kick TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.adm_kick_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Create the table to handle tracking cookies
|
|
--
|
|
|
|
CREATE TABLE main.web_tracking (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
cookie VARCHAR(32) NOT NULL UNIQUE,
|
|
created INT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW())),
|
|
last_used INT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW())),
|
|
ip_addr VARCHAR(15) NOT NULL,
|
|
browser VARCHAR(255) NOT NULL,
|
|
stored_data TEXT NOT NULL DEFAULT 'a:0:{}'
|
|
);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.web_tracking TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.web_tracking_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Create the session storage table
|
|
--
|
|
|
|
CREATE TABLE main.web_session (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
cookie VARCHAR(32) NOT NULL UNIQUE,
|
|
created INT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW())),
|
|
last_used INT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW())),
|
|
ip_addr VARCHAR(15) NOT NULL,
|
|
account BIGINT REFERENCES main.account(id),
|
|
stored_data TEXT NOT NULL DEFAULT 'a:0:{}',
|
|
tracking BIGINT NOT NULL REFERENCES main.web_tracking(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX web_session_tracking ON main.web_session (tracking);
|
|
CREATE INDEX web_session_account ON main.web_session (account);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.web_session TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.web_session_id_seq TO legacyworlds;
|
|
|
|
|
|
|
|
--
|
|
-- Create the account log table
|
|
--
|
|
CREATE TABLE main.account_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
account BIGINT NOT NULL REFERENCES main.account (id) ON DELETE CASCADE,
|
|
tracking BIGINT NULL REFERENCES main.web_tracking (id) ON DELETE SET NULL,
|
|
ip_addr VARCHAR(18) NOT NULL DEFAULT 'AUTO',
|
|
action VARCHAR(6) NOT NULL REFERENCES main.acnt_log_entry_type (txt),
|
|
t INT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW()))
|
|
);
|
|
|
|
CREATE INDEX acnt_log_account ON main.account_log (account);
|
|
CREATE INDEX acnt_log_trk ON main.account_log (tracking);
|
|
CREATE INDEX acnt_log_act ON main.account_log (action);
|
|
|
|
GRANT SELECT,INSERT,DELETE ON TABLE main.account_log TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.account_log_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Create the table that stores cache references
|
|
--
|
|
|
|
CREATE TABLE main.web_cache (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
rtype VARCHAR(5) NOT NULL,
|
|
md5 VARCHAR(32) NOT NULL,
|
|
last_used BIGINT NOT NULL DEFAULT INT4(EXTRACT(EPOCH FROM NOW())),
|
|
UNIQUE (rtype, md5)
|
|
);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.web_cache TO legacyworlds;
|
|
GRANT SELECT,UPDATE ON main.web_cache_id_seq TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Tables that will store rankings definitions, texts, and
|
|
-- game relations
|
|
--
|
|
|
|
-- Definitions
|
|
CREATE TABLE main.ranking_def (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
version VARCHAR(16) NOT NULL,
|
|
name VARCHAR(16) NOT NULL,
|
|
more BOOLEAN NOT NULL DEFAULT FALSE,
|
|
UNIQUE (version, name)
|
|
);
|
|
|
|
GRANT SELECT ON TABLE main.ranking_def TO legacyworlds;
|
|
|
|
-- Descriptions
|
|
CREATE TABLE main.ranking_text (
|
|
ranking BIGINT NOT NULL REFERENCES main.ranking_def (id),
|
|
lang VARCHAR(4) NOT NULL REFERENCES main.lang (txt),
|
|
name VARCHAR(32) NOT NULL,
|
|
description text NOT NULL,
|
|
PRIMARY KEY (ranking, lang)
|
|
);
|
|
|
|
CREATE INDEX rk_txt_lang ON main.ranking_text (lang);
|
|
GRANT SELECT ON TABLE main.ranking_text TO legacyworlds;
|
|
|
|
|
|
-- Games
|
|
CREATE TABLE main.ranking_game (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
ranking BIGINT NOT NULL REFERENCES main.ranking_def (id),
|
|
game VARCHAR(16) NOT NULL,
|
|
UNIQUE (ranking, game)
|
|
);
|
|
|
|
GRANT SELECT ON TABLE main.ranking_game TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Table that will store the rankings themselves
|
|
--
|
|
CREATE TABLE main.ranking (
|
|
r_type BIGINT NOT NULL REFERENCES main.ranking_game (id) ON DELETE CASCADE,
|
|
id VARCHAR(32) NOT NULL,
|
|
additional TEXT,
|
|
points BIGINT NOT NULL DEFAULT 0,
|
|
ranking BIGINT NOT NULL,
|
|
PRIMARY KEY (r_type, id)
|
|
);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.ranking TO legacyworlds;
|
|
|
|
|
|
--
|
|
-- Table for user preferences
|
|
--
|
|
CREATE TABLE main.user_preferences (
|
|
id VARCHAR(32) NOT NULL,
|
|
version VARCHAR(16) NOT NULL,
|
|
account BIGINT NULL REFERENCES main.account (id) ON DELETE CASCADE,
|
|
value VARCHAR(255) NOT NULL,
|
|
UNIQUE (id, version, account)
|
|
);
|
|
|
|
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE main.user_preferences TO legacyworlds;
|