diff options
| -rw-r--r-- | schema.sql | 29 | ||||
| -rw-r--r-- | server.js | 19 | ||||
| -rw-r--r-- | views/delete_account.pug | 29 | ||||
| -rw-r--r-- | views/profile.pug | 2 |
4 files changed, 77 insertions, 2 deletions
@@ -44,6 +44,11 @@ create table if not exists users ( about text ); +insert or ignore into + users (user_id, name, mail, ctime) + values (0, 'Deleted', 'deleted@rally-the-troops.com', datetime('1970-01-01')) +; + create table if not exists user_last_seen ( user_id integer primary key @@ -396,7 +401,8 @@ create view your_turn as and active in ('All', 'Both', role) ; --- Manual key management if pragma foreign_keys = off +-- Triggers to clean up without relying on foreign key cascades + drop trigger if exists trigger_delete_on_games; create trigger trigger_delete_on_games after delete on games begin @@ -406,3 +412,24 @@ begin delete from last_notified where game_id = old.game_id; delete from players where game_id = old.game_id; end; + +drop trigger if exists trigger_delete_on_users; +create trigger trigger_delete_on_users after delete on users +begin + delete from logins where user_id = old.user_id; + delete from tokens where user_id = old.user_id; + delete from user_last_seen where user_id = old.user_id; + delete from last_notified where user_id = old.user_id; + delete from messages where from_id = old.user_id or to_id = old.user_id; + delete from posts where author_id = old.user_id; + delete from threads where author_id = old.user_id; + delete from game_chat where user_id = old.user_id; + delete from players where user_id = old.user_id; + update games set owner_id = 0 where owner_id = old.user_id; +end; + +drop trigger if exists trigger_delete_on_threads; +create trigger trigger_delete_on_threads after delete on threads +begin + delete from posts where thread_id = old.thread_id; +end @@ -264,7 +264,9 @@ const SQL_EXISTS_USER_NAME = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE name const SQL_EXISTS_USER_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE mail=? )").pluck() const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail,password,salt,notify) VALUES (?,?,?,?,?) RETURNING user_id,name,mail,notify") +const SQL_DELETE_USER = SQL("DELETE FROM users WHERE user_id = ?") +const SQL_SELECT_LOGIN = SQL("SELECT * FROM user_login_view WHERE user_id=?") const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?") const SQL_SELECT_LOGIN_BY_MAIL = SQL("SELECT * FROM user_login_view WHERE mail=?") const SQL_SELECT_LOGIN_BY_NAME = SQL("SELECT * FROM user_login_view WHERE name=?") @@ -544,7 +546,7 @@ app.post('/change-password', must_be_logged_in, function (req, res) { let oldpass = req.body.password let newpass = req.body.newpass // Get full user record including password and salt - let user = SQL_SELECT_LOGIN_BY_MAIL.get(req.user.mail) + let user = SQL_SELECT_LOGIN.get(req.user.user_id) if (newpass.length < 4) return res.render('change_password.pug', { user: req.user, flash: "Password is too short!" }) if (newpass.length > 100) @@ -557,6 +559,21 @@ app.post('/change-password', must_be_logged_in, function (req, res) { return res.redirect('/profile') }) +app.get('/delete-account', must_be_logged_in, function (req, res) { + res.render('delete_account.pug', { user: req.user }) +}) + +app.post('/delete-account', must_be_logged_in, function (req, res) { + let password = req.body.password + // Get full user record including password and salt + let user = SQL_SELECT_LOGIN.get(req.user.user_id) + let hash = hash_password(password, user.salt) + if (hash !== user.password) + return res.render('delete_account.pug', { user: req.user, flash: "Wrong password!" }) + SQL_DELETE_USER.run(req.user.user_id) + return res.send("Goodbye!") +}) + app.get('/admin/ban-user/:who', must_be_administrator, function (req, res) { let who = req.params.who SQL_UPDATE_USER_IS_BANNED.run(1, who) diff --git a/views/delete_account.pug b/views/delete_account.pug new file mode 100644 index 0000000..905945c --- /dev/null +++ b/views/delete_account.pug @@ -0,0 +1,29 @@ +//- vim:ts=4:sw=4: +doctype html +html + head + include head + title Delete account + body + include header + article + h1 Delete account + if flash + p.error= flash + + form(method="post" action="/delete-account") + p Name: #{user.name} + p Mail: #{user.mail} + p + label Password: + br + input(type="password" name="password" required) + p + label Type DELETE to confirm: + br + input(type="text" name="confirm" pattern="^DELETE$" required) + p.warning WARNING: Deleting your account is permanent! + p All your games and messages will be lost forever. + p + button(onclick="window.location='/profile'") Cancel + button(type="submit") Delete! diff --git a/views/profile.pug b/views/profile.pug index 52bb0c0..7ed8eff 100644 --- a/views/profile.pug +++ b/views/profile.pug @@ -25,6 +25,8 @@ html | <a href="/change-name">Change user name</a> br | <a href="/change-about">Change profile text</a> + br + | <a href="/delete-account">Delete account</a> p <a href="/chat">Chat log</a> |