Split and clean up user table into more sub-tables.HEADmaster

2 files changed, 46 insertions, 19 deletions

diff --git a/schema.sql b/schema.sql
index d321295..a5a900e 100644
--- a/schema.sql
+++ b/schema.sql
@@ -38,18 +38,32 @@ create table if not exists users (
 	mail text unique collate nocase,
 	notify integer default 0,
 	is_verified boolean default 0,
-	is_banned boolean default 0,
-	ctime datetime default current_timestamp,
-	password text,
-	salt text,
-	about text
+	is_banned boolean default 0
 );
 
 insert or ignore into
-	users (user_id, name, mail, ctime)
-	values (0, 'Deleted', 'deleted@nowhere', null)
+	users (user_id, name, mail)
+	values (0, 'Deleted', 'deleted@nowhere')
 ;
 
+create table if not exists user_password (
+	user_id integer primary key,
+	password text,
+	salt text
+);
+
+create table if not exists user_about (
+	user_id integer primary key,
+	ctime datetime,
+	about text
+);
+
+create table if not exists user_first_seen (
+	user_id integer primary key,
+	ctime datetime,
+	ip text
+);
+
 create table if not exists user_last_seen (
 	user_id integer primary key,
 	atime datetime,
@@ -84,6 +98,7 @@ create view user_login_view as
 		user_id, name, mail, notify, password, salt
 	from
 		users
+		left join user_password using(user_id)
 	;
 
 drop view if exists user_profile_view;
@@ -92,7 +107,9 @@ create view user_profile_view as
 		user_id, name, mail, notify, ctime, atime, about, is_banned
 	from
 		users
+		left join user_first_seen using(user_id)
 		left join user_last_seen using(user_id)
+		left join user_about using(user_id)
 	;
 
 drop view if exists user_dynamic_view;
@@ -892,10 +909,13 @@ end;
 drop trigger if exists trigger_delete_on_users;
 create trigger trigger_delete_on_users after delete on users
 begin
+	delete from user_password where user_id = old.user_id;
+	delete from user_first_seen where user_id = old.user_id;
+	delete from user_last_seen where user_id = old.user_id;
+	delete from user_about where user_id = old.user_id;
+	delete from webhooks where user_id = old.user_id;
 	delete from logins where user_id = old.user_id;
 	delete from tokens where user_id = old.user_id;
-	delete from webhooks where user_id = old.user_id;
-	delete from user_last_seen where user_id = old.user_id;
 	delete from read_threads where user_id = old.user_id;
 	delete from unread_chats where user_id = old.user_id;
 	delete from contacts where me = old.user_id or you = old.user_id;
diff --git a/server.js b/server.js
index 37a292e..54f6038 100644
--- a/server.js
+++ b/server.js
@@ -421,14 +421,15 @@ const SQL_BLACKLIST_NAME = SQL("select exists ( select 1 from blacklist_name whe
 const SQL_EXISTS_USER_NAME = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE name=? )").pluck()
 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) VALUES (?,?,?,?) RETURNING user_id,name,mail")
+const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail) VALUES (?,?) RETURNING user_id,name,mail")
 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_VIEW = SQL("SELECT * FROM user_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=?")
+
+const SQL_SELECT_USER_VIEW = SQL("SELECT * FROM user_view WHERE user_id=?")
+const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?")
 const SQL_SELECT_USER_PROFILE = SQL("SELECT * FROM user_profile_view WHERE name=?")
 const SQL_SELECT_USER_DYNAMIC = SQL("select * from user_dynamic_view where user_id=?")
 const SQL_SELECT_USER_ID = SQL("SELECT user_id FROM users WHERE name=?").pluck()
@@ -440,9 +441,12 @@ const SQL_UPDATE_USER_NOTIFY = SQL("UPDATE users SET notify=? WHERE user_id=?")
 const SQL_UPDATE_USER_NAME = SQL("UPDATE users SET name=? WHERE user_id=?")
 const SQL_UPDATE_USER_MAIL = SQL("UPDATE users SET mail=? WHERE user_id=?")
 const SQL_UPDATE_USER_VERIFIED = SQL("UPDATE users SET is_verified=? WHERE user_id=?")
-const SQL_UPDATE_USER_ABOUT = SQL("UPDATE users SET about=? WHERE user_id=?")
-const SQL_UPDATE_USER_PASSWORD = SQL("UPDATE users SET password=?, salt=? WHERE user_id=?")
-const SQL_UPDATE_USER_LAST_SEEN = SQL("INSERT OR REPLACE INTO user_last_seen (user_id,atime,ip) VALUES (?,datetime(),?)")
+
+const SQL_SELECT_USER_ABOUT = SQL("SELECT about FROM user_about WHERE user_id=?").pluck()
+const SQL_UPDATE_USER_ABOUT = SQL("insert or replace into user_about (user_id,about) values (?,?)")
+const SQL_UPDATE_USER_PASSWORD = SQL("insert or replace into user_password (user_id,password,salt) values (?,?,?)")
+const SQL_UPDATE_USER_FIRST_SEEN = SQL("insert or replace into user_first_seen (user_id,ctime,ip) values (?,datetime(),?)")
+const SQL_UPDATE_USER_LAST_SEEN = SQL("insert or replace into user_last_seen (user_id,atime,ip) values (?,datetime(),?)")
 const SQL_UPDATE_USER_IS_BANNED = SQL("update users set is_banned=? where name=?")
 
 const SQL_SELECT_WEBHOOK = SQL("SELECT * FROM webhooks WHERE user_id=?")
@@ -543,6 +547,7 @@ app.post("/signup", must_pass_altcha, function (req, res) {
 	function err(msg) {
 		res.render("signup.pug", { flash: msg })
 	}
+	let ip = req.headers["x-real-ip"] || req.ip || req.connection.remoteAddress || "0.0.0.0"
 	let name = req.body.username
 	let mail = req.body.mail
 	let password = req.body.password
@@ -561,7 +566,9 @@ app.post("/signup", must_pass_altcha, function (req, res) {
 		return err("Password is too long!")
 	let salt = crypto.randomBytes(32).toString("hex")
 	let hash = hash_password(password, salt)
-	let user = SQL_INSERT_USER.get(name, mail, hash, salt)
+	let user = SQL_INSERT_USER.get(name, mail)
+	SQL_UPDATE_USER_FIRST_SEEN.run(user.user_id, ip)
+	SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt)
 	login_insert(res, user.user_id)
 	res.redirect("/profile")
 })
@@ -653,7 +660,7 @@ app.post("/reset-password", function (req, res) {
 		return err("Invalid or expired token!")
 	let salt = crypto.randomBytes(32).toString("hex")
 	let hash = hash_password(password, salt)
-	SQL_UPDATE_USER_PASSWORD.run(hash, salt, user.user_id)
+	SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt)
 	SQL_UPDATE_USER_VERIFIED.run(1, user.user_id)
 	login_insert(res, user.user_id)
 	return res.redirect("/profile")
@@ -677,7 +684,7 @@ app.post("/change-password", must_be_logged_in, function (req, res) {
 		return res.render("change_password.pug", { user: req.user, flash: "Wrong password!" })
 	let salt = crypto.randomBytes(32).toString("hex")
 	let hash = hash_password(newpass, salt)
-	SQL_UPDATE_USER_PASSWORD.run(hash, salt, user.user_id)
+	SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt)
 	return res.redirect("/profile")
 })
 
@@ -805,7 +812,7 @@ app.get("/change-about", must_be_logged_in, function (req, res) {
 })
 
 app.post("/change-about", must_be_logged_in, function (req, res) {
-	SQL_UPDATE_USER_ABOUT.run(req.body.about, req.user.user_id)
+	SQL_UPDATE_USER_ABOUT.run(req.user.user_id, req.body.about)
 	return res.redirect("/profile")
 })