diff options
Diffstat (limited to 'tools')
| -rw-r--r-- | tools/sql/schema.txt | 462 |
1 files changed, 241 insertions, 221 deletions
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt index 6344470..5e80ec6 100644 --- a/tools/sql/schema.txt +++ b/tools/sql/schema.txt @@ -1,272 +1,292 @@ +CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID; + +CREATE TABLE IF NOT EXISTS titles ( + title_id TEXT NOT NULL PRIMARY KEY, + title_name TEXT NOT NULL, + bgg INTEGER +) WITHOUT ROWID; + +CREATE TABLE IF NOT EXISTS roles ( + title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE, + role TEXT NOT NULL, + UNIQUE ( title_id, role ) +); + +-- Users -- + CREATE TABLE IF NOT EXISTS users ( - user_id INTEGER PRIMARY KEY, - name TEXT UNIQUE COLLATE NOCASE, - mail TEXT UNIQUE COLLATE NOCASE, - about TEXT, - password TEXT, - salt TEXT, - ctime TIMESTAMP, - cip TEXT, - atime TIMESTAMP, - aip TEXT, - notifications INTEGER + user_id INTEGER NOT NULL PRIMARY KEY, + name TEXT NOT NULL UNIQUE COLLATE NOCASE, + mail TEXT NOT NULL UNIQUE COLLATE NOCASE, + notify BOOLEAN NOT NULL DEFAULT 0, + banned BOOLEAN NOT NULL DEFAULT 0, + ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + password TEXT NOT NULL, + salt TEXT NOT NULL, + about TEXT ); -CREATE TABLE IF NOT EXISTS notifications ( - user_id INTEGER, - game_id INTEGER, - time TIMESTAMP, - UNIQUE ( user_id, game_id ) +CREATE TABLE IF NOT EXISTS user_last_seen ( + user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE, + atime TIMESTAMP NOT NULL, + aip TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tokens ( - user_id INTEGER PRIMARY KEY, - token TEXT, - time TIMESTAMP + user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE, + token TEXT NOT NULL, + time TIMESTAMP NOT NULL ); -CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT PRIMARY KEY ); -CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT PRIMARY KEY ); +CREATE TABLE IF NOT EXISTS last_notified ( + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE, + time TIMESTAMP NOT NULL, + PRIMARY KEY ( game_id, user_id ) +) WITHOUT ROWID; -CREATE TABLE IF NOT EXISTS titles ( - title_id TEXT UNIQUE, - title_name TEXT, - bgg INTEGER +DROP VIEW IF EXISTS user_view; +CREATE VIEW user_view AS + SELECT + user_id, name, mail, notify + FROM users + ; + +DROP VIEW IF EXISTS user_login_view; +CREATE VIEW user_login_view AS + SELECT + user_id, name, mail, notify, password, salt + FROM users + ; + +DROP VIEW IF EXISTS user_profile_view; +CREATE VIEW user_profile_view AS + SELECT + user_id, name, mail, notify, ctime, atime, about + FROM users + NATURAL LEFT JOIN user_last_seen + ; + +-- Messages -- + +CREATE TABLE IF NOT EXISTS messages ( + message_id INTEGER NOT NULL PRIMARY KEY, + deleted_from_inbox BOOLEAN NOT NULL DEFAULT 0, + deleted_from_outbox BOOLEAN NOT NULL DEFAULT 0, + from_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + to_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + read BOOLEAN NOT NULL DEFAULT 0, + subject TEXT NOT NULL, + body TEXT NOT NULL ); -CREATE TABLE IF NOT EXISTS roles ( - title_id TEXT, - role TEXT, - UNIQUE ( title_id, role ) +DROP VIEW IF EXISTS message_view; +CREATE VIEW message_view AS + SELECT * + , users_from.name AS from_name + , users_to.name AS to_name + FROM messages + JOIN users AS users_from ON messages.from_id = users_from.user_id + JOIN users AS users_to ON messages.to_id = users_to.user_id + ; + +DROP INDEX IF EXISTS messages_inbox_idx; +CREATE INDEX messages_inbox_idx ON messages(to_id) WHERE deleted_from_inbox=0; + +DROP INDEX IF EXISTS messages_inbox_unread_idx; +CREATE INDEX messages_inbox_unread_idx ON messages(to_id) WHERE read=0 AND deleted_from_inbox=0; + +-- Forum -- + +CREATE TABLE IF NOT EXISTS threads ( + thread_id INTEGER NOT NULL PRIMARY KEY, + author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + subject TEXT NOT NULL, + locked BOOLEAN NOT NULL DEFAULT 0 ); +CREATE TABLE IF NOT EXISTS posts ( + post_id INTEGER NOT NULL PRIMARY KEY, + thread_id INTEGER NOT NULL REFERENCES threads(thread_id) ON DELETE CASCADE, + author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + body TEXT NOT NULL +); + +DROP VIEW IF EXISTS thread_view; +CREATE VIEW thread_view AS + SELECT * + , author.name AS author_name + , ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies + , ( SELECT MAX(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime + FROM threads + JOIN users AS author ON threads.author_id = author.user_id + ; + +DROP VIEW IF EXISTS post_view; +CREATE VIEW post_view AS + SELECT * + , author.name AS author_name + FROM posts + JOIN users AS author ON posts.author_id = author.user_id + ; + +DROP INDEX IF EXISTS posts_thread_idx; +CREATE INDEX posts_thread_idx ON posts(thread_id); + +-- Games -- + CREATE TABLE IF NOT EXISTS games ( - game_id INTEGER PRIMARY KEY, - title_id TEXT, + game_id INTEGER NOT NULL PRIMARY KEY, + title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE, scenario TEXT, options TEXT, - owner_id INTEGER, - private BOOLEAN, - random BOOLEAN, - description TEXT, - ctime TIMESTAMP, - mtime TIMESTAMP, - status INTEGER, - result TEXT, + owner_id INTEGER DEFAULT 0 REFERENCES users(user_id) ON DELETE SET NULL, + ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + private BOOLEAN NOT NULL DEFAULT 0, + random BOOLEAN NOT NULL DEFAULT 0, + description TEXT NOT NULL DEFAULT '', + status INTEGER NOT NULL DEFAULT 0, + result TEXT +); + +DROP INDEX IF EXISTS games_title_idx; +CREATE INDEX games_title_idx ON games(title_id); + +DROP INDEX IF EXISTS games_status_idx; +CREATE INDEX games_status_idx ON games(status); + +CREATE TABLE IF NOT EXISTS game_state ( + game_id INTEGER NOT NULL PRIMARY KEY REFERENCES games(game_id) ON DELETE CASCADE, + mtime TIMESTAMP NOT NULL, active TEXT, state TEXT ); -CREATE TABLE IF NOT EXISTS replay ( - game_id INTEGER, - time TIMESTAMP, - role TEXT, - action TEXT, - arguments TEXT +CREATE TABLE IF NOT EXISTS game_chat ( + chat_id INTEGER NOT NULL PRIMARY KEY, + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + message TEXT ); -CREATE TABLE IF NOT EXISTS chats ( - game_id INTEGER PRIMARY KEY, - time TIMESTAMP, - chat TEXT -); +DROP VIEW IF EXISTS game_chat_view; +CREATE VIEW game_chat_view AS + SELECT chat_id, game_id, time, name, message + FROM game_chat + NATURAL JOIN users + ; -CREATE TABLE IF NOT EXISTS players ( - user_id INTEGER, - game_id INTEGER, +DROP INDEX IF EXISTS game_chat_idx; +CREATE INDEX game_chat_idx ON game_chat(game_id); + +CREATE TABLE IF NOT EXISTS game_replay ( + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, role TEXT, - UNIQUE ( game_id, role ) + action TEXT, + arguments TEXT ); -CREATE TABLE IF NOT EXISTS forums ( - forum_id INTEGER PRIMARY KEY, - title TEXT -); +CREATE TABLE IF NOT EXISTS players ( + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + role TEXT NOT NULL, + user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + PRIMARY KEY ( game_id, role ) +) WITHOUT ROWID; -CREATE TABLE IF NOT EXISTS threads ( - thread_id INTEGER PRIMARY KEY, - forum_id INTEGER DEFAULT 1, - author_id INTEGER, - subject TEXT, - ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - post_count INTEGER DEFAULT 0, - locked BOOLEAN DEFAULT 0 - -- or first_post_id and last_post_id ? -); +DROP INDEX IF EXISTS player_user_idx; +CREATE INDEX player_user_idx ON players(user_id); -CREATE TABLE IF NOT EXISTS posts ( - post_id INTEGER PRIMARY KEY, - thread_id INTEGER, - author_id INTEGER, - ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - body TEXT -); +DROP INDEX IF EXISTS player_game_user_idx; +CREATE INDEX player_game_user_idx ON players(game_id,user_id); -CREATE TABLE IF NOT EXISTS messages ( - message_id INTEGER PRIMARY KEY, - from_id INTEGER, - to_id INTEGER, - time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - subject TEXT, - body TEXT, - read BOOLEAN DEFAULT 0, - deleted_from_inbox BOOLEAN DEFAULT 0, - deleted_from_outbox BOOLEAN DEFAULT 0 -); +DROP VIEW IF EXISTS game_view; +CREATE VIEW game_view AS + SELECT games.* + , titles.title_name + , owner.name AS owner_name + , game_state.mtime + , game_state.active + FROM games + NATURAL LEFT JOIN game_state + NATURAL JOIN titles + JOIN users AS owner ON owner.user_id=games.owner_id + ; -DROP TRIGGER IF EXISTS update_reply_count; -CREATE TRIGGER update_reply_count AFTER INSERT ON posts -BEGIN - UPDATE threads - SET - post_count = ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = new.thread_id ), - mtime = datetime('now') - WHERE threads.thread_id = new.thread_id +DROP VIEW IF EXISTS game_full_view; +CREATE VIEW game_full_view AS + SELECT *, + ( SELECT group_concat(name, ', ') + FROM players + NATURAL JOIN users + WHERE players.game_id=game_view.game_id + ) AS player_names, + ( SELECT COUNT(DISTINCT user_id) = 1 + FROM players + WHERE players.game_id=game_view.game_id + ) AS is_solo + FROM game_view ; -END; -DROP TRIGGER IF EXISTS update_reply_time; -CREATE TRIGGER update_reply_time AFTER UPDATE ON posts -BEGIN - UPDATE threads SET mtime = datetime('now') WHERE threads.thread_id = new.thread_id ; -END; +DROP VIEW IF EXISTS your_turn_reminder; +CREATE VIEW your_turn_reminder AS + SELECT + game_id, role, user_id, name, mail, notify + FROM game_full_view + JOIN players USING(game_id) + JOIN users USING(user_id) + WHERE status = 1 AND is_solo = 0 AND notify = 1 + AND active IN ( 'All', 'Both', role ) + AND datetime('now') > datetime(mtime, '+1 hour') + ; DROP TRIGGER IF EXISTS no_part_on_active_game; CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players BEGIN - SELECT CASE - WHEN ( SELECT status FROM games WHERE game_id = old.game_id ) > 0 - THEN RAISE(ABORT, "Cannot remove players from started games.") - END; + SELECT RAISE(ABORT, "Cannot remove players from started games.") + WHERE ( SELECT status FROM games WHERE games.game_id = old.game_id ) > 0 ; END; DROP TRIGGER IF EXISTS no_join_on_active_game; CREATE TRIGGER no_join_on_active_game BEFORE INSERT ON players BEGIN - SELECT CASE - WHEN ( SELECT status FROM games WHERE game_id = new.game_id ) > 0 - THEN RAISE(ABORT, "Cannot add players to started games.") - END; + SELECT RAISE(ABORT, "Cannot add players to started games.") + WHERE ( SELECT status FROM games WHERE games.game_id = new.game_id ) > 0 ; END; DROP TRIGGER IF EXISTS must_be_valid_role; CREATE TRIGGER must_be_valid_role BEFORE INSERT ON players BEGIN - SELECT CASE - WHEN ( SELECT COUNT(*) FROM roles, games WHERE - roles.title_id = games.title_id AND - games.game_id = new.game_id AND - roles.role = new.role ) <> 1 - AND new.role <> 'Random 1' - AND new.role <> 'Random 2' - AND new.role <> 'Random 3' - AND new.role <> 'Random 4' - AND new.role <> 'Random 5' - AND new.role <> 'Random 6' - THEN RAISE(ABORT, "Invalid role for that title.") - END; + SELECT + RAISE(ABORT, "Invalid role.") + WHERE + NOT EXISTS ( + SELECT 1 + FROM roles + WHERE roles.title_id = ( SELECT title_id FROM games WHERE games.game_id = new.game_id ) + AND roles.role = new.role + ) + AND new.role <> 'Random 1' + AND new.role <> 'Random 2' + AND new.role <> 'Random 3' + AND new.role <> 'Random 4' + AND new.role <> 'Random 5' + AND new.role <> 'Random 6' + ; END; -DROP TRIGGER IF EXISTS purge_players; -CREATE TRIGGER purge_players AFTER DELETE ON games +-- Manual key management if pragma foreign_keys = OFF +DROP TRIGGER IF EXISTS trigger_delete_on_games; +CREATE TRIGGER trigger_delete_on_games AFTER DELETE ON games BEGIN + DELETE FROM game_state WHERE game_id = old.game_id; + DELETE FROM game_chat WHERE game_id = old.game_id; + DELETE FROM game_replay WHERE game_id = old.game_id; + DELETE FROM last_notified WHERE game_id = old.game_id; DELETE FROM players WHERE game_id = old.game_id; - DELETE FROM notifications WHERE game_id = old.game_id; - DELETE FROM chats WHERE game_id = old.game_id; - DELETE FROM replay WHERE game_id = old.game_id; END; - -DROP VIEW IF EXISTS player_view; -DROP VIEW IF EXISTS player_list_view; -DROP VIEW IF EXISTS game_view; - -CREATE VIEW player_view AS - SELECT players.game_id - , players.role AS role - , players.user_id AS user_id - , users.name AS user_name - FROM players - JOIN users ON players.user_id = users.user_id - ; - -CREATE VIEW player_list_view AS - SELECT game_id - , group_concat(name, ', ') AS player_names - , COUNT(DISTINCT user_id) AS user_count - , COUNT(user_id) AS role_count - FROM players - JOIN users USING ( user_id ) - GROUP BY game_id - ; - -CREATE VIEW game_view AS - SELECT games.game_id - , games.title_id - , titles.title_name - , games.scenario - , games.options - , games.owner_id - , owner.name AS owner_name - , players.player_names - , players.user_count = 1 AS is_solo - , players.user_count <> players.role_count AS is_shared - , games.private - , games.random - , games.description - , games.ctime - , games.mtime - , games.status - , games.result - , games.active AS active_role - -- , games.state - FROM games - JOIN titles USING ( title_id ) - JOIN users AS owner ON games.owner_id = owner.user_id - LEFT JOIN player_list_view AS players USING ( game_id ) - ; - -DROP VIEW IF EXISTS thread_view; -CREATE VIEW thread_view AS - SELECT threads.thread_id - , threads.author_id - , author.name AS author_name - , threads.post_count - 1 AS reply_count - , threads.ctime - , threads.mtime - , threads.subject - FROM threads - JOIN users AS author ON threads.author_id = author.user_id - ; - -DROP VIEW IF EXISTS post_view; -CREATE VIEW post_view AS - SELECT posts.post_id - , posts.thread_id - , posts.author_id - , author.name AS author_name - , posts.ctime - , posts.mtime - , posts.body - FROM posts - JOIN users AS author ON posts.author_id = author.user_id - ; - -DROP VIEW IF EXISTS message_view; -CREATE VIEW message_view AS - SELECT messages.message_id - , messages.from_id - , users_from.name AS from_name - , messages.to_id - , users_to.name AS to_name - , messages.time - , messages.subject - , messages.body - , messages.read - , messages.deleted_from_inbox - , messages.deleted_from_outbox - FROM messages - JOIN users AS users_from ON messages.from_id = users_from.user_id - JOIN users AS users_to ON messages.to_id = users_to.user_id - ; |