From 5effbf0ab6b3f8522dbdd1d909b3da148480053b Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Tue, 7 Dec 2021 13:04:41 +0100
Subject: Put login session table in main database.

---
 .gitignore           |  3 ---
 server.js            | 14 +++++---------
 tools/sql/schema.txt |  9 +++++++++
 3 files changed, 14 insertions(+), 12 deletions(-)

diff --git a/.gitignore b/.gitignore
index e8f12c9..c84acb6 100644
--- a/.gitignore
+++ b/.gitignore
@@ -3,9 +3,6 @@ node_modules
 db
 db-wal
 db-shm
-login
-login-wal
-login-shm
 log
 .env
 *.pem
diff --git a/server.js b/server.js
index 071271d..1cf23ec 100644
--- a/server.js
+++ b/server.js
@@ -44,15 +44,11 @@ if (process.env.MAIL_HOST && process.env.MAIL_PORT) {
  * Login session management.
  */
 
-const login_db = new sqlite3(process.env.LOGIN || "./login");
-login_db.pragma("journal_mode = WAL");
-login_db.pragma("synchronous = OFF");
-login_db.exec("create table if not exists sessions (sid integer primary key, user_id integer, expires real)");
-login_db.exec("delete from sessions where expires < julianday()");
-const login_sql_select = login_db.prepare("select user_id from sessions where sid = ? and expires > julianday()").pluck();
-const login_sql_insert = login_db.prepare("insert into sessions values (abs(random()) % (1<<48), ?, julianday() + 28) returning sid").pluck();
-const login_sql_delete = login_db.prepare("delete from sessions where sid = ?");
-const login_sql_touch = login_db.prepare("update sessions set expires = julianday() + 28 where sid=? and expires < julianday() + 27");
+db.exec("delete from logins where expires < julianday()");
+const login_sql_select = SQL("select user_id from logins where sid = ? and expires > julianday()").pluck();
+const login_sql_insert = SQL("insert into logins values (abs(random()) % (1<<48), ?, julianday() + 28) returning sid").pluck();
+const login_sql_delete = SQL("delete from logins where sid = ?");
+const login_sql_touch = SQL("update logins set expires = julianday() + 28 where sid = ? and expires < julianday() + 27");
 
 function login_cookie(req) {
 	let c = req.headers.cookie;
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index cd04c25..62bde47 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -22,6 +22,15 @@ create table if not exists roles (
 
 -- 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,
-- 
cgit v1.2.3