path: root/tools/sql/schema.txt

CREATE TABLE IF NOT EXISTS users (
	user_id INTEGER PRIMARY KEY,
	name TEXT UNIQUE COLLATE NOCASE,
	mail TEXT UNIQUE COLLATE NOCASE,
	password TEXT,
	salt TEXT,
	ctime TIMESTAMP,
	cip TEXT,
	atime TIMESTAMP,
	aip TEXT,
	notifications INTEGER
);

CREATE TABLE IF NOT EXISTS notifications (
	user_id INTEGER,
	game_id INTEGER,
	time TIMESTAMP,
	UNIQUE ( user_id, game_id )
);

CREATE TABLE IF NOT EXISTS tokens (
	user_id INTEGER PRIMARY KEY,
	token TEXT,
	time TIMESTAMP
);

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 titles (
	title_id TEXT UNIQUE,
	title_name TEXT,
	bgg INTEGER
);

CREATE TABLE IF NOT EXISTS roles (
	title_id TEXT,
	role TEXT,
	UNIQUE ( title_id, role )
);

CREATE TABLE IF NOT EXISTS games (
	game_id INTEGER PRIMARY KEY,
	title_id TEXT,
	scenario TEXT,
	options TEXT,
	owner_id INTEGER,
	private BOOLEAN,
	random BOOLEAN,
	description TEXT,
	ctime TIMESTAMP,
	mtime TIMESTAMP,
	status INTEGER,
	result TEXT,
	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 chats (
	game_id INTEGER PRIMARY KEY,
	time TIMESTAMP,
	chat TEXT
);

CREATE TABLE IF NOT EXISTS players (
	user_id INTEGER,
	game_id INTEGER,
	role TEXT,
	UNIQUE ( game_id, role )
);

CREATE TABLE IF NOT EXISTS forums (
	forum_id INTEGER PRIMARY KEY,
	title TEXT
);

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

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

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

DROP TRIGGER IF EXISTS purge_players;
CREATE TRIGGER purge_players AFTER DELETE ON games
BEGIN
	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
	;