From 1f060bf2ef8b3d475b0d37d32bab73e1a601ea11 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Fri, 31 Dec 2021 22:05:18 +0100 Subject: Parameterize site name and domain. --- INSTALL.md | 62 +++++++ LICENSE | 2 +- NOTES.md | 43 ----- README.md | 3 +- schema.sql | 515 +++++++++++++++++++++++++++++++++++++++++++++++++++ server.js | 27 ++- tools/sql/data.txt | 29 --- tools/sql/schema.txt | 515 --------------------------------------------------- views/about.pug | 8 +- views/head.pug | 4 +- views/index.pug | 10 +- views/profile.pug | 4 +- 12 files changed, 610 insertions(+), 612 deletions(-) create mode 100644 INSTALL.md create mode 100644 schema.sql delete mode 100644 tools/sql/data.txt delete mode 100644 tools/sql/schema.txt diff --git a/INSTALL.md b/INSTALL.md new file mode 100644 index 0000000..1d1ecba --- /dev/null +++ b/INSTALL.md @@ -0,0 +1,62 @@ +## Setting up the server + +All data is stored in an SQLite3 database. + +The server and game rules are implemented in Javascript. +The game state is stored in the database as a JSON blob. +The server runs on Node with the Express.js and Socket.io frameworks. + +Check out the game submodules: + +``` +git clone https://github.com/rally-the-troops/julius-caesar.git public/julius-caesar +``` + +Initialize the database: + +``` +sqlite3 db < schema.sql +sqlite3 db < public/julius-caesar/title.sql +``` + +Redirect port 80 and 443 to 8080 and 8443: + +``` +sudo iptables -A PREROUTING -t nat -p tcp --dport 80 -j REDIRECT --to-ports 8080 +sudo iptables -A PREROUTING -t nat -p tcp --dport 443 -j REDIRECT --to-ports 8443 +``` + +Create SSL certificate with Let's Encrypt certbot, or self-signed with OpenSSL: + +``` +openssl req -nodes -new -x509 -keyout key.pem -out cert.pem +``` + +Configure the server using the .env file: + +``` +NODE_ENV=production + +SITE_NAME=YOUR_SITE_NAME +SITE_URL=https://YOUR_DOMAIN + +HTTP_PORT=8080 + +HTTPS_PORT=8443 +SSL_KEY=/etc/letsencrypt/live/YOUR_DOMAIN/privkey.com +SSL_CERT=/etc/letsencrypt/live/YOUR_DOMAIN/fullchain.pem + +MAIL_FROM=YOUR_SITE_NAME +MAIL_HOST=localhost +MAIL_PORT=25 +``` + +If the HTTPS_PORT is missing, the server will only serve HTTP. + +If MAIL_HOST/PORT/FROM are not present, the server will not send notification emails. + +Start the server: + +``` +node server.js +``` diff --git a/LICENSE b/LICENSE index 78803fc..4f0a455 100644 --- a/LICENSE +++ b/LICENSE @@ -10,7 +10,7 @@ game titles. ISC License -Copyright 2021 Tor Andersson +Copyright 2021, 2022 Tor Andersson Permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby granted, provided that the above diff --git a/NOTES.md b/NOTES.md index 51b622e..b9dd646 100644 --- a/NOTES.md +++ b/NOTES.md @@ -1,45 +1,3 @@ -## Setting up the server - -All data is stored in an SQLite3 database. - -The server and game rules are implemented in Javascript. -The game state is stored in the database as a JSON blob. -The server runs on Node with the Express.js and Socket.io frameworks. - -Redirect port 80 and 443 to 8080 and 8443: - -``` -sudo iptables -A PREROUTING -t nat -p tcp --dport 80 -j REDIRECT --to-ports 8080 -sudo iptables -A PREROUTING -t nat -p tcp --dport 443 -j REDIRECT --to-ports 8443 -``` - -Create SSL certificate with Let's Encrypt certbot, or self-signed with OpenSSL: - -``` -openssl req -nodes -new -x509 -keyout key.pem -out cert.pem -``` - -Configure the server using the .env file: - -``` -NODE_ENV=production - -HTTP_PORT=8080 - -HTTPS_PORT=8443 -SSL_KEY=/etc/letsencrypt/live/YOUR_DOMAIN/privkey.com -SSL_CERT=/etc/letsencrypt/live/YOUR_DOMAIN/fullchain.pem - -MAIL_FROM="Your Site Name " -MAIL_HOST=localhost -MAIL_PORT=25 -``` - -If the HTTPS_PORT is missing, the server will only serve HTTP. -If MAIL_HOST/PORT are not present, the server will not send notification emails. - -## Resources - Icons are sourced from various places: * https://game-icons.net/ @@ -58,4 +16,3 @@ Image processing software: * https://github.com/svg/svgo * http://optipng.sourceforge.net/ * http://potrace.sourceforge.net/ - diff --git a/README.md b/README.md index 94a1ec6..6754935 100644 --- a/README.md +++ b/README.md @@ -2,7 +2,8 @@ ## About -Rally the Troops! is an open source website for playing historical games. +Rally the Troops! is website for playing historical board games. + All titles are published on rally-the-troops.com with permission from their respective designers and/or publishers. If you intend to use any title for your own use or on a different website, please contact the applicable designer diff --git a/schema.sql b/schema.sql new file mode 100644 index 0000000..419da0d --- /dev/null +++ b/schema.sql @@ -0,0 +1,515 @@ +-- 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 logins ( + sid integer + primary key, + user_id integer + references users + on delete cascade, + expires real +); + +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 + primary key + references users + on delete cascade, + atime timestamp, + aip text +); + +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 + 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 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 + 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 + 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 + ; + +create index if not exists messages_inbox_idx + on + messages(to_id) + where + is_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 + primary key, + author_id integer + references users, + subject text, + is_locked boolean + default 0 +); + +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 + 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 + posts.*, + author.name as author_name + from + posts + left join users as author + on posts.author_id = author.user_id + ; + +create index if not exists posts_thread_idx on posts(thread_id); + +-- Games -- + +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 +); + +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 + 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 + ; + +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 + 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 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 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 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 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) + ; + +-- 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' + ; +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=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/server.js b/server.js index 50fa751..e4b3802 100644 --- a/server.js +++ b/server.js @@ -11,6 +11,9 @@ const sqlite3 = require('better-sqlite3'); require('dotenv').config(); +const SITE_URL = process.env.SITE_URL || "http://localhost:8080"; +const SITE_NAME = process.env.SITE_NAME || "Untitled"; + /* * Main database. */ @@ -29,7 +32,7 @@ function SQL(s) { */ let mailer = null; -if (process.env.MAIL_HOST && process.env.MAIL_PORT) { +if (process.env.MAIL_HOST && process.env.MAIL_PORT && process.env.MAIL_FROM) { mailer = require('nodemailer').createTransport({ host: process.env.MAIL_HOST, port: process.env.MAIL_PORT, @@ -93,6 +96,7 @@ app.set('view engine', 'pug'); app.use(compression()); app.use(express.static('public', { etag: false, cacheControl: false, setHeaders: set_static_headers })); app.use(express.urlencoded({extended:false})); +app.locals.SITE_NAME = SITE_NAME; let http_port = process.env.HTTP_PORT || 8080; let http_server = http.createServer(app); @@ -1381,8 +1385,8 @@ app.get('/:title_id/play\::game_id', function (req, res) { * MAIL NOTIFICATIONS */ -const MAIL_FROM = process.env.MAIL_FROM || "Rally the Troops! "; -const MAIL_FOOTER = "You can unsubscribe from notifications on your profile page:\nhttps://rally-the-troops.com/profile\n"; +const MAIL_FROM = process.env.MAIL_FROM || "user@localhost"; +const MAIL_FOOTER = `You can unsubscribe from notifications on your profile page:\n${SITE_URL}/profile\n`; const SQL_SELECT_NOTIFIED = SQL("SELECT datetime('now') < datetime(time,?) FROM last_notified WHERE game_id=? AND user_id=?").pluck(); const SQL_INSERT_NOTIFIED = SQL("INSERT OR REPLACE INTO last_notified (game_id,user_id,time) VALUES (?,?,datetime('now'))"); @@ -1409,14 +1413,17 @@ function mail_describe(game) { } function mail_password_reset_token(user, token) { - let subject = "Rally the Troops - Password reset request"; + let subject = "Password reset request"; let body = "Your password reset token is: " + token + "\n\n" + - "https://rally-the-troops.com/reset-password/" + user.mail + "/" + token + "\n\n" + + SITE_URL + "/reset-password/" + user.mail + "/" + token + "\n\n" + "If you did not request a password reset you can ignore this mail.\n"; - console.log("SENT MAIL:", mail_addr(user), subject); - if (mailer) + if (mailer) { + console.log("SENT MAIL:", mail_addr(user), subject); mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); + } else { + console.log("DID NOT SEND MAIL:", mail_addr(user), subject); + } } function mail_new_message(user, msg_id, msg_from, msg_subject, msg_body) { @@ -1424,7 +1431,7 @@ function mail_new_message(user, msg_id, msg_from, msg_subject, msg_body) { let body = "Subject: " + msg_subject + "\n\n" + msg_body + "\n\n--\n" + "You can reply to this message at:\n" + - "https://rally-the-troops.com/message/read/" + msg_id + "\n\n"; + SITE_URL + "/message/read/" + msg_id + "\n\n"; console.log("SENT MAIL:", mail_addr(user), subject); if (mailer) mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); @@ -1438,7 +1445,7 @@ function mail_your_turn_notification(user, game_id, interval) { let subject = game.title_name + " - " + game_id + " - Your turn!"; let body = mail_describe(game) + "It's your turn.\n\n" + - "https://rally-the-troops.com/play/" + game_id + "\n\n--\n" + + SITE_URL + "/play/" + game_id + "\n\n--\n" + MAIL_FOOTER; console.log("SENT MAIL:", mail_addr(user), subject); if (mailer) @@ -1458,7 +1465,7 @@ function mail_ready_to_start_notification(user, game_id, interval) { let subject = game.title_name + " - " + game_id + " - Ready to start!"; let body = mail_describe(game) + "Your game is ready to start.\n\n" + - "https://rally-the-troops.com/join/" + game_id + "\n\n--\n" + + SITE_URL + "/join/" + game_id + "\n\n--\n" + MAIL_FOOTER; console.log("SENT MAIL:", mail_addr(user), subject); if (mailer) diff --git a/tools/sql/data.txt b/tools/sql/data.txt deleted file mode 100644 index e9fc882..0000000 --- a/tools/sql/data.txt +++ /dev/null @@ -1,29 +0,0 @@ -PRAGMA foreign_keys=0; - -INSERT OR REPLACE INTO titles VALUES ( '300-earth-and-water', '300: Earth & Water', 267058, 0 ); -INSERT OR REPLACE INTO roles VALUES ( '300-earth-and-water', 'Persia' ); -INSERT OR REPLACE INTO roles VALUES ( '300-earth-and-water', 'Greece' ); - -INSERT OR REPLACE INTO titles VALUES ( 'crusader-rex', 'Crusader Rex', 8481, 0 ); -INSERT OR REPLACE INTO roles VALUES ( 'crusader-rex', 'Franks' ); -INSERT OR REPLACE INTO roles VALUES ( 'crusader-rex', 'Saracens' ); - -INSERT OR REPLACE INTO titles VALUES ( 'julius-caesar', 'Julius Caesar', 37836, 0 ); -INSERT OR REPLACE INTO roles VALUES ( 'julius-caesar', 'Caesar' ); -INSERT OR REPLACE INTO roles VALUES ( 'julius-caesar', 'Pompeius' ); - -INSERT OR REPLACE INTO titles VALUES ( 'hammer-of-the-scots', 'Hammer of the Scots', 3685, 0 ); -INSERT OR REPLACE INTO roles VALUES ( 'hammer-of-the-scots', 'England' ); -INSERT OR REPLACE INTO roles VALUES ( 'hammer-of-the-scots', 'Scotland' ); - -INSERT OR REPLACE INTO titles VALUES ( 'richard-iii', 'Richard III', 25277, 0 ); -INSERT OR REPLACE INTO roles VALUES ( 'richard-iii', 'York' ); -INSERT OR REPLACE INTO roles VALUES ( 'richard-iii', 'Lancaster' ); - -INSERT OR REPLACE INTO titles VALUES ( 'shores-of-tripoli', 'Shores of Tripoli', 237860, 0 ); -INSERT OR REPLACE INTO roles VALUES ( 'shores-of-tripoli', 'Tripolitania' ); -INSERT OR REPLACE INTO roles VALUES ( 'shores-of-tripoli', 'United States' ); - -INSERT OR REPLACE INTO titles VALUES ( 'wilderness-war', 'Wilderness War', 1822, 1 ); -INSERT OR REPLACE INTO roles VALUES ( 'wilderness-war', 'French' ); -INSERT OR REPLACE INTO roles VALUES ( 'wilderness-war', 'British' ); diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt deleted file mode 100644 index 419da0d..0000000 --- a/tools/sql/schema.txt +++ /dev/null @@ -1,515 +0,0 @@ --- 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 logins ( - sid integer - primary key, - user_id integer - references users - on delete cascade, - expires real -); - -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 - primary key - references users - on delete cascade, - atime timestamp, - aip text -); - -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 - 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 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 - 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 - 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 - ; - -create index if not exists messages_inbox_idx - on - messages(to_id) - where - is_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 - primary key, - author_id integer - references users, - subject text, - is_locked boolean - default 0 -); - -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 - 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 - posts.*, - author.name as author_name - from - posts - left join users as author - on posts.author_id = author.user_id - ; - -create index if not exists posts_thread_idx on posts(thread_id); - --- Games -- - -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 -); - -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 - 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 - ; - -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 - 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 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 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 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 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) - ; - --- 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' - ; -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=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/about.pug b/views/about.pug index 2a0a9a2..fabc580 100644 --- a/views/about.pug +++ b/views/about.pug @@ -3,17 +3,17 @@ doctype html html head include head - title Rally the Troops! + title= SITE_NAME style. li img { height: 1.0em; vertical-align: middle; } body include header article - h1 Rally the Troops! + h1= SITE_NAME - p Rally the Troops! is created and maintained by Tor Andersson. It is an open source project. + p Rally the Troops! is created and maintained by Tor Andersson. It is a free software project. - p Please submit problem reports and make suggestions for improvements on #[a(href="https://github.com/ccxvii/rally-the-troops/issues") GitHub]. + p Please submit problem reports and make suggestions for improvements on #[a(href="https://github.com/rally-the-troops/") GitHub]. h2 Tips & Tricks diff --git a/views/head.pug b/views/head.pug index 3c65c23..68fa904 100644 --- a/views/head.pug +++ b/views/head.pug @@ -9,9 +9,9 @@ mixin social(title,description,game) meta(property="og:title" content=title) meta(property="og:type" content="website") if game - meta(property="og:image" content="https://rally-the-troops.com/"+game+"/cover.2x.jpg") + meta(property="og:image" content=SITE_URL+"/"+game+"/cover.2x.jpg") else - meta(property="og:image" content="https://rally-the-troops.com/images/rally-the-troops.png") + meta(property="og:image" content=SITE_URL+"/images/rally-the-troops.png") meta(property="og:description" content=description) mixin gamecover(title_id) diff --git a/views/index.pug b/views/index.pug index 98d5ef7..ec07e03 100644 --- a/views/index.pug +++ b/views/index.pug @@ -3,9 +3,9 @@ doctype html html head include head - +social("Rally the Troops!", "Play historic board games on the web.") + +social(SITE_NAME, "Play historic board games on the web.") meta(name="keywords" content="wargames, war games, block games") - title Rally the Troops! + title= SITE_NAME style. div.list { max-width: 800px; @@ -31,9 +31,9 @@ html body include header article - h1 Rally the Troops! + h1= SITE_NAME - p Rally the Troops! is a website where you can play historic board games online. + p #{SITE_NAME} is a website where you can play historic board games online. p Registration and use is free, and there are no ads. @@ -47,4 +47,4 @@ html p: a(href="/games") List of all open and active games. - p Join the #[a(href="https://discord.gg/CBrTh8k84A") Discord] server to find players or report bugs. + p!= process.env.SITE_INVITE diff --git a/views/profile.pug b/views/profile.pug index 77cc597..6fb777a 100644 --- a/views/profile.pug +++ b/views/profile.pug @@ -3,13 +3,13 @@ doctype html html head include head - title Rally the Troops! + title= SITE_NAME if active_games.length > 0 meta(http-equiv="refresh" content=300) body include header article - h1 Rally the Troops! + h1= SITE_NAME a(href="https://gravatar.com/"): img.avatar(src=avatar) p Welcome, #{user.name}! -- cgit v1.2.3