diff options
| -rw-r--r-- | server.js | 44 | ||||
| -rw-r--r-- | tools/sql/schema.txt | 729 | ||||
| -rw-r--r-- | views/create.pug | 4 | ||||
| -rw-r--r-- | views/index.pug | 2 | ||||
| -rw-r--r-- | views/join.pug | 2 | ||||
| -rw-r--r-- | views/message_inbox.pug | 2 |
6 files changed, 468 insertions, 315 deletions
@@ -208,7 +208,7 @@ const SQL_FIND_TOKEN = SQL("SELECT token FROM tokens WHERE user_id=? AND datetim const SQL_CREATE_TOKEN = SQL("INSERT OR REPLACE INTO tokens (user_id,token,time) VALUES (?, lower(hex(randomblob(16))), datetime('now')) RETURNING token").pluck(); const SQL_VERIFY_TOKEN = SQL("SELECT EXISTS ( SELECT 1 FROM tokens WHERE user_id=? AND datetime('now') < datetime(time, '+20 minutes') AND token=? )").pluck(); -const SQL_COUNT_INBOX = SQL("SELECT COUNT(*) FROM messages WHERE to_id=? AND read=0 AND deleted_from_inbox=0").pluck(); +const SQL_COUNT_INBOX = SQL("SELECT COUNT(*) FROM messages WHERE to_id=? AND is_read=0 AND is_deleted_from_inbox=0").pluck(); const SQL_USER_STATS = SQL(` SELECT title_name, scenario, SUM(role=result) AS won, count(*) AS total @@ -573,23 +573,23 @@ app.get('/chat/all', must_be_logged_in, function (req, res) { */ const MESSAGE_LIST_INBOX = db.prepare(` - SELECT message_id, from_name, subject, time, read + SELECT message_id, from_name, subject, time, is_read FROM message_view - WHERE to_id=? AND deleted_from_inbox=0 + WHERE to_id=? AND is_deleted_from_inbox=0 ORDER BY message_id DESC`); const MESSAGE_LIST_OUTBOX = db.prepare(` - SELECT message_id, to_name, subject, time, 1 as read + SELECT message_id, to_name, subject, time, 1 as is_read FROM message_view - WHERE from_id=? AND deleted_from_outbox=0 + WHERE from_id=? AND is_deleted_from_outbox=0 ORDER BY message_id DESC`); const MESSAGE_FETCH = db.prepare("SELECT * FROM message_view WHERE message_id=? AND ( from_id=? OR to_id=? )"); const MESSAGE_SEND = db.prepare("INSERT INTO messages (from_id,to_id,subject,body) VALUES (?,?,?,?)"); -const MESSAGE_MARK_READ = db.prepare("UPDATE messages SET read=1 WHERE message_id=? AND read = 0"); -const MESSAGE_DELETE_INBOX = db.prepare("UPDATE messages SET deleted_from_inbox=1 WHERE message_id=? AND to_id=?"); -const MESSAGE_DELETE_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox=1 WHERE message_id=? AND from_id=?"); -const MESSAGE_DELETE_ALL_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox=1 WHERE from_id=?"); +const MESSAGE_MARK_READ = db.prepare("UPDATE messages SET is_read=1 WHERE message_id=? AND is_read = 0"); +const MESSAGE_DELETE_INBOX = db.prepare("UPDATE messages SET is_deleted_from_inbox=1 WHERE message_id=? AND to_id=?"); +const MESSAGE_DELETE_OUTBOX = db.prepare("UPDATE messages SET is_deleted_from_outbox=1 WHERE message_id=? AND from_id=?"); +const MESSAGE_DELETE_ALL_OUTBOX = db.prepare("UPDATE messages SET is_deleted_from_outbox=1 WHERE from_id=?"); app.get('/inbox', must_be_logged_in, function (req, res) { LOG(req, "GET /inbox"); @@ -621,7 +621,7 @@ app.get('/message/read/:message_id', must_be_logged_in, function (req, res) { let message = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id); if (!message) return res.status(404).send("Invalid message ID."); - if (message.to_id === req.user.user_id && message.read === 0) { + if (message.to_id === req.user.user_id && message.is_read === 0) { MESSAGE_MARK_READ.run(message_id); req.user.unread --; } @@ -888,7 +888,7 @@ function load_rules() { load_rules(); -const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,private,random,description) VALUES (?,?,?,?,?,?,?)"); +const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,is_private,is_random,description) VALUES (?,?,?,?,?,?,?)"); const SQL_DELETE_GAME = SQL("DELETE FROM games WHERE game_id=? AND owner_id=?"); const SQL_SELECT_USER_CHAT = SQL("SELECT game_id,time,name,message FROM game_chat_view WHERE game_id IN ( SELECT DISTINCT game_id FROM players WHERE user_id=? ) ORDER BY chat_id DESC").raw(); @@ -900,14 +900,14 @@ const SQL_INSERT_GAME_CHAT = SQL("INSERT INTO game_chat (game_id,user_id,message const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?").pluck(); const SQL_UPDATE_GAME_STATE = SQL("INSERT OR REPLACE INTO game_state (game_id,state,active,mtime) VALUES (?,?,?,datetime('now'))"); const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=? WHERE game_id=?"); -const SQL_UPDATE_GAME_PRIVATE = SQL("UPDATE games SET private=1 WHERE game_id=?"); +const SQL_UPDATE_GAME_PRIVATE = SQL("UPDATE games SET is_private=1 WHERE game_id=?"); const SQL_INSERT_REPLAY = SQL("INSERT INTO game_replay (game_id,role,action,arguments) VALUES (?,?,?,?)"); const SQL_SELECT_GAME = SQL("SELECT * FROM games WHERE game_id=?"); const SQL_SELECT_GAME_VIEW = SQL("SELECT * FROM game_view WHERE game_id=?"); const SQL_SELECT_GAME_FULL_VIEW = SQL("SELECT * FROM game_full_view WHERE game_id=?"); const SQL_SELECT_GAME_TITLE = SQL("SELECT title_id FROM games WHERE game_id=?").pluck(); -const SQL_SELECT_GAME_RANDOM = SQL("SELECT random FROM games WHERE game_id=?").pluck(); +const SQL_SELECT_GAME_RANDOM = SQL("SELECT is_random FROM games WHERE game_id=?").pluck(); const SQL_SELECT_PLAYERS = SQL("SELECT * FROM players NATURAL JOIN user_view WHERE game_id=?"); const SQL_SELECT_PLAYERS_JOIN = SQL("SELECT role, user_id, name FROM players NATURAL JOIN users WHERE game_id=?"); @@ -924,9 +924,9 @@ const SQL_COUNT_OPEN_GAMES = SQL("SELECT COUNT(*) FROM games WHERE owner_id=? AN const SQL_SELECT_REMATCH = SQL("SELECT game_id FROM games WHERE status < 3 AND description=?").pluck(); const SQL_INSERT_REMATCH = SQL(` INSERT INTO games - (owner_id, title_id, scenario, options, private, random, description) + (owner_id, title_id, scenario, options, is_private, is_random, description) SELECT - $user_id, title_id, scenario, options, private, random, $magic + $user_id, title_id, scenario, options, is_private, is_random, $magic FROM games WHERE game_id = $game_id AND NOT EXISTS ( SELECT * FROM games WHERE description=$magic @@ -935,14 +935,14 @@ const SQL_INSERT_REMATCH = SQL(` const QUERY_LIST_GAMES = SQL(` SELECT * FROM game_view - WHERE private=0 AND status=? + WHERE is_private=0 AND status=? AND EXISTS ( SELECT 1 FROM players WHERE players.game_id = game_view.game_id AND user_id = game_view.owner_id ) ORDER BY mtime DESC `); const QUERY_LIST_GAMES_OF_TITLE = SQL(` SELECT * FROM game_view - WHERE private=0 AND title_id=? AND status=? + WHERE is_private=0 AND title_id=? AND status=? AND EXISTS ( SELECT 1 FROM players WHERE players.game_id = game_view.game_id AND user_id = game_view.owner_id ) ORDER BY mtime DESC LIMIT ? @@ -1096,8 +1096,8 @@ app.get('/create/:title_id', must_be_logged_in, function (req, res) { function options_json_replacer(key, value) { if (key === 'scenario') return undefined; if (key === 'description') return undefined; - if (key === 'random') return undefined; - if (key === 'private') return undefined; + if (key === 'is_random') return undefined; + if (key === 'is_private') return undefined; if (value === 'true') return true; if (value === 'false') return false; if (value === '') return undefined; @@ -1107,8 +1107,8 @@ function options_json_replacer(key, value) { app.post('/create/:title_id', must_be_logged_in, function (req, res) { let title_id = req.params.title_id; let descr = req.body.description; - let priv = req.body.private === 'true'; - let rand = req.body.random === 'true'; + let priv = req.body.is_private === 'true'; + let rand = req.body.is_random === 'true'; let user_id = req.user.user_id; let scenario = req.body.scenario; let options = JSON.stringify(req.body, options_json_replacer); @@ -1321,7 +1321,7 @@ app.get('/start/:game_id', must_be_logged_in, function (req, res) { let players = SQL_SELECT_PLAYERS.all(game_id); if (!RULES[game.title_id].ready(game.scenario, game.options, players)) return res.send("Invalid scenario/options/player configuration!"); - if (game.random) { + if (game.is_random) { assign_random_roles(game, players); players = SQL_SELECT_PLAYERS.all(game_id); update_join_clients_players(game_id); diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt index 13062ee..cd04c25 100644 --- a/tools/sql/schema.txt +++ b/tools/sql/schema.txt @@ -1,353 +1,506 @@ -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, - hidden BOOLEAN -) 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 ) +-- Blacklists -- + +create table if not exists blacklist_ip ( ip text primary key ) without rowid; +create table if not exists blacklist_mail ( mail text primary key ) without rowid; + +-- Titles and roles -- + +create table if not exists titles ( + title_id text + primary key, + title_name text, + bgg integer, + is_hidden boolean +) without rowid; + +create table if not exists roles ( + title_id text + references titles, + role text, + unique (title_id, role) ); -- Users -- -CREATE TABLE IF NOT EXISTS users ( - 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 users ( + user_id integer + primary key, + name text + unique + collate nocase, + mail text + unique + collate nocase, + notify boolean + default 0, + is_banned boolean + default 0, + ctime timestamp + default current_timestamp, + password text, + salt text, + about text ); -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 user_last_seen ( + user_id integer + primary key + references users + on delete cascade, + atime timestamp, + aip text ); -CREATE TABLE IF NOT EXISTS tokens ( - 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 tokens ( + user_id integer + primary key + references users + on delete cascade, + token text, + time timestamp ); -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; - -DROP VIEW IF EXISTS user_view; -CREATE VIEW user_view AS - SELECT +create table if not exists last_notified ( + game_id integer + references games + on delete cascade, + user_id integer + references users + on delete cascade, + time timestamp, + primary key (game_id, user_id) +) without rowid; + +drop view if exists user_view; +create view if not exists user_view as + select user_id, name, mail, notify - FROM users + from + users ; -DROP VIEW IF EXISTS user_login_view; -CREATE VIEW user_login_view AS - SELECT +drop view if exists user_profile_view; +drop view if exists user_login_view; +create view if not exists user_login_view as + select user_id, name, mail, notify, password, salt - FROM users + from + users ; -DROP VIEW IF EXISTS user_profile_view; -CREATE VIEW user_profile_view AS - SELECT +create view if not exists user_profile_view as + select user_id, name, mail, notify, ctime, atime, about - FROM users - NATURAL LEFT JOIN user_last_seen + 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 messages ( + message_id integer + primary key, + is_deleted_from_inbox boolean + default 0, + is_deleted_from_outbox boolean + default 0, + from_id integer + references users, + to_id integer + references users, + time timestamp + default current_timestamp, + is_read boolean + default 0, + subject text, + body text ); -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 view if exists message_view; +create view if not exists message_view as + select + messages.*, + users_from.name as from_name, + users_to.name as to_name + from + messages + left join users as users_from + on messages.from_id = users_from.user_id + left 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; +create index if not exists messages_inbox_idx + on + messages(to_id) + where + is_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; +create index if not exists messages_inbox_unread_idx + on + messages(to_id) + where + is_read = 0 and is_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 threads ( + thread_id integer + primary key, + author_id integer + references users, + subject text, + is_locked boolean + 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 +create table if not exists posts ( + post_id integer + primary key, + thread_id integer + references threads + on delete cascade, + author_id integer + references users, + ctime timestamp + default current_timestamp, + mtime timestamp + default current_timestamp, + body text ); -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 thread_view; +create view if not exists thread_view as + select + threads.*, + author.name as author_name, + ( + select + count(*) - 1 + from + posts + where + posts.thread_id = threads.thread_id + ) as replies, + ( + select + max(posts.mtime) + from + posts + where + posts.thread_id = threads.thread_id + ) as mtime + from + threads + left 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 view if exists post_view; +create view if not exists post_view as + select + posts.*, + author.name as author_name + from + posts + left 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); +create index if not exists posts_thread_idx on posts(thread_id); -- Games -- -CREATE TABLE IF NOT EXISTS games ( - 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 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 +create table if not exists games ( + game_id integer + primary key, + title_id text + references titles, + scenario text, + options text, + owner_id integer + references users, + ctime timestamp + default current_timestamp, + is_private boolean + default 0, + is_random boolean + default 0, + description text, + status integer + 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 index if not exists games_title_idx on games(title_id); +create index if not exists games_status_idx on games(status); + +create table if not exists game_state ( + game_id integer + primary key + references games + on delete cascade, + mtime timestamp, + active text, + state 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 game_chat ( + chat_id integer + primary key, + game_id integer + references games + on delete cascade, + time timestamp + default current_timestamp, + user_id integer + references users, + message 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 +drop view if exists game_chat_view; +create view if not exists game_chat_view as + select + chat_id, game_id, time, name, message + from + game_chat + natural join users ; -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, - action TEXT, - arguments TEXT +create index if not exists game_chat_idx on game_chat(game_id); + +create table if not exists game_replay ( + game_id integer + references games + on delete cascade, + time timestamp + default current_timestamp, + role text, + action text, + arguments 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; - -DROP INDEX IF EXISTS player_user_idx; -CREATE INDEX player_user_idx ON players(user_id); - -DROP INDEX IF EXISTS player_game_user_idx; -CREATE INDEX player_game_user_idx ON players(game_id,user_id); - -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 +create table if not exists players ( + game_id integer + references games + on delete cascade, + role text, + user_id integer + references users, + primary key (game_id, role) +) without rowid; + +create index if not exists player_user_idx on players(user_id); +create index if not exists player_game_user_idx on players(game_id, user_id); + +drop view if exists game_view; +create view if not exists 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 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 +drop view if exists game_full_view; +create view if not exists 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 ; -DROP VIEW IF EXISTS opposed_games; -CREATE VIEW opposed_games AS - SELECT * - FROM GAMES - WHERE - STATUS > 0 AND - ( - SELECT count(DISTINCT user_id) > 1 - FROM players - where players.game_id=games.game_id +drop view if exists opposed_games; +create view if not exists opposed_games as + select + * + from + games + where + status > 0 + and ( + select + count(distinct user_id) > 1 + from + players + where + players.game_id = games.game_id ) ; -DROP VIEW IF EXISTS your_turn_reminder; -CREATE VIEW your_turn_reminder AS - SELECT +drop view if exists your_turn_reminder; +create view if not exists 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') + from + game_full_view + join players using(game_id) + join users using(user_id) + where + status = 1 + and active in ('All', 'Both', role) + and is_solo = 0 + and notify = 1 + and datetime('now') > datetime(mtime, '+1 hour') ; -DROP VIEW IF EXISTS your_turn; -CREATE VIEW your_turn AS - SELECT game_id, user_id, role - FROM players - JOIN games USING(game_id) - JOIN game_state USING(game_id) - WHERE status=1 AND active IN ( 'All', 'Both', role ) +drop view if exists your_turn; +create view if not exists your_turn as + select + game_id, user_id, role + from + players + join games using(game_id) + join game_state using(game_id) + where + status = 1 + and active in ('All', 'Both', role) ; -DROP TRIGGER IF EXISTS no_part_on_active_game; -CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players -BEGIN - 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 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 - 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 +-- Triggers -- + +drop trigger if exists no_part_on_active_game; +create trigger no_part_on_active_game before delete on players +begin + 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 + 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 + 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' - AND new.role <> 'Random 7' + 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' + and new.role != 'Random 7' ; -END; - --- 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; -END; - --- Game Stats - -DROP VIEW IF EXISTS role_index_view; -CREATE VIEW role_index_view(t,i,r) AS - SELECT title_id, row_number() OVER ( PARTITION BY title_id ), role - FROM roles; - -DROP VIEW IF EXISTS game_stat_view; -CREATE VIEW game_stat_view AS - SELECT title_name, NULL AS scenario - , NULL as total - , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=1) AS r1 - , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=2) AS r2 - , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=3) AS r3 - , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=4) AS r4 - , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=5) AS r5 - , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=6) AS r6 - , (SELECT r FROM role_index_view WHERE t=titles.title_id AND i=7) AS r7 - FROM titles - WHERE hidden=0 - UNION - SELECT title_name, scenario - , count(*) as total - , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=1) AS r1 - , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=2) AS r2 - , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=3) AS r3 - , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=4) AS r4 - , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=5) AS r5 - , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=6) AS r6 - , sum((SELECT i FROM role_index_view WHERE t=title_id AND r=result)=7) AS r7 - FROM games - NATURAL JOIN titles - WHERE hidden=0 AND status=2 AND game_id IN (SELECT game_id FROM opposed_games) - GROUP BY title_id, scenario - ORDER BY title_name, total DESC NULLS FIRST +end; + +-- 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; +end; + +-- Game stats + +drop view if exists role_index_view; +create view if not exists role_index_view (t, i, r) as + select + title_id, + row_number() over (partition by title_id), + role + from + roles + ; + +drop view if exists game_stat_view; +create view if not exists game_stat_view as + select + title_name, + null as scenario, + null as total, + (select r from role_index_view where t=title_id and i=1) as r1, + (select r from role_index_view where t=title_id and i=2) as r2, + (select r from role_index_view where t=title_id and i=3) as r3, + (select r from role_index_view where t=title_id and i=4) as r4, + (select r from role_index_view where t=title_id and i=5) as r5, + (select r from role_index_view where t=title_id and i=6) as r6, + (select r from role_index_view where t=title_id and i=7) as r7 + from + titles + where + is_hidden = 0 + union + select + title_name, + scenario, + count(*) as total, + sum((select i from role_index_view where t=title_id and r=result) = 1) as r1, + sum((select i from role_index_view where t=title_id and r=result) = 2) as r2, + sum((select i from role_index_view where t=title_id and r=result) = 3) as r3, + sum((select i from role_index_view where t=title_id and r=result) = 4) as r4, + sum((select i from role_index_view where t=title_id and r=result) = 5) as r5, + sum((select i from role_index_view where t=title_id and r=result) = 6) as r6, + sum((select i from role_index_view where t=title_id and r=result) = 7) as r7 + from + games + natural join titles + where + is_hidden = 0 + and status = 2 + and game_id in (select game_id from opposed_games) + group by + title_id, + scenario + order by + title_name, + total desc nulls first ; diff --git a/views/create.pug b/views/create.pug index 9196aa9..a736dbe 100644 --- a/views/create.pug +++ b/views/create.pug @@ -30,11 +30,11 @@ html input(type="text" autocomplete="off" name="description" size=50) p label - input(type="checkbox" name="random" value="true") + input(type="checkbox" name="is_random" value="true") | Random player roles p label - input(type="checkbox" name="private" value="true") + input(type="checkbox" name="is_private" value="true") | Private p button(type="submit") Create diff --git a/views/index.pug b/views/index.pug index 8ada2fc..db6fcdc 100644 --- a/views/index.pug +++ b/views/index.pug @@ -38,7 +38,7 @@ html div.list each title in titles - unless title.hidden + unless title.is_hidden div a(href="/info/"+title.title_id) img(src="/"+title.title_id+"/cover.jpg") diff --git a/views/join.pug b/views/join.pug index be95075..8a04ad6 100644 --- a/views/join.pug +++ b/views/join.pug @@ -29,7 +29,7 @@ html a(href="/info/"+game.title_id): img.logo(src="/"+game.title_id+"/cover.jpg") p - if game.private + if game.is_private | Owner: #{game.owner_name} (private) else | Owner: #{game.owner_name} diff --git a/views/message_inbox.pug b/views/message_inbox.pug index 28563d8..9b0a1ab 100644 --- a/views/message_inbox.pug +++ b/views/message_inbox.pug @@ -19,7 +19,7 @@ html th Subject th Date each row in messages - tr(class=row.read?"read":"unread") + tr(class=row.is_read?"read":"unread") td: a(href="/user/"+row.from_name)= row.from_name td: a(href="/message/read/"+row.message_id)= row.subject td= row.time |