From 8633f92b6dbc7d2c1e7069ddedf10c54f4d31961 Mon Sep 17 00:00:00 2001
From: "Jannis M. Hoffmann" <jannis@fehcom.de>
Date: Mon, 4 Nov 2024 20:01:54 +0100
Subject: add sqlite session timeout storage

---
 src/jwebmail/__init__.py   |  2 +-
 src/jwebmail/read_mails.py | 75 +++++++++++++++++++++++++++++++++++++++++-----
 2 files changed, 68 insertions(+), 9 deletions(-)

(limited to 'src/jwebmail')

diff --git a/src/jwebmail/__init__.py b/src/jwebmail/__init__.py
index 875bcb5..442590b 100644
--- a/src/jwebmail/__init__.py
+++ b/src/jwebmail/__init__.py
@@ -36,7 +36,7 @@ else:
 
     toml_read_file = dict(load=toml_load, text=True)
 
-__version__ = "2.3.0.dev4"
+__version__ = "2.4.0.dev0"
 
 csrf = CSRFProtect()
 
diff --git a/src/jwebmail/read_mails.py b/src/jwebmail/read_mails.py
index 404a242..05c6e10 100644
--- a/src/jwebmail/read_mails.py
+++ b/src/jwebmail/read_mails.py
@@ -35,6 +35,9 @@ class RedisTimeoutSession:
     def get(self, key):
         return self.conn.getex(f"jwm:user:{key}", self.timeout)
 
+    def close(self):
+        self.conn.close()
+
 
 class MysqlTimeoutSession:
     def __init__(self, username, passwd, timeout, database="jwebmaildb1", port=3306):
@@ -76,14 +79,72 @@ class MysqlTimeoutSession:
                 self.conn.commit()
                 return row[0]
 
+    def close(self):
+        self.conn.close()
+
+
+class SqliteTimeoutSession:
+    def __init__(self, _username, _passwd, timeout, database):
+        import sqlite3
+
+        self.timeout = timeout
+
+        self.conn = sqlite3.connect(database, autocommit=False)
+        cur = self.conn.cursor()
+        cur.execute(
+            "CREATE TABLE IF NOT EXISTS session (user text PRIMARY KEY, password text, timeout real NOT NULL) STRICT"
+        )
+        cur.execute("CREATE INDEX IF NOT EXISTS timeout_idx ON session (timeout)")
+
+    def set(self, key, value):
+        timeout = datetime.now() + timedelta(seconds=self.timeout)
+
+        with closing(self.conn.cursor()) as cur:
+            cur.execute(
+                "REPLACE INTO session VALUES (?, ?, unixepoch(?, 'subsec'))",
+                [key, value, timeout],
+            )
+            self.conn.commit()
+
+    def get(self, key):
+        with closing(self.conn.cursor()) as cur:
+            cur.execute("DELETE FROM session WHERE timeout < unixepoch('subsec')")
+            cur.execute("SELECT password FROM session WHERE user = ?", [key])
+            row = cur.fetchone()
+
+            if row is None:
+                self.conn.commit()
+                return None
+            else:
+                timeout = datetime.now() + timedelta(seconds=self.timeout)
+                cur.execute(
+                    "UPDATE session SET timeout = unixepoch(?, 'subsec') WHERE user = ?",
+                    [timeout, key],
+                )
+                self.conn.commit()
+                return row[0]
+
+    def close(self):
+        self.conn.close()
+
 
 def select_timeout_session():
     session_type = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_TYPE"]
 
+    user = "jwebmail"
+    passwd = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_STORE_PASSWD"]
+    args = dict()
+    db_name = current_app.config["JWEBMAIL"]["READ_MAILS"].get("SESSION_STORE_DB_NAME")
+    if db_name:
+        args["database"] = db_name
+
     if session_type == "REDIS":
-        return RedisTimeoutSession
+        return RedisTimeoutSession(user, passwd, EXPIRATION_SEC)
     elif session_type == "MYSQL":
-        return MysqlTimeoutSession
+        return MysqlTimeoutSession(user, passwd, EXPIRATION_SEC, **args)
+    elif session_type == "SQLITE":
+        args.setdefault("database", "/var/local/lib/jwebmail/jwebmail.sqlite3")
+        return SqliteTimeoutSession(user, passwd, EXPIRATION_SEC, **args)
     else:
         raise ValueError(f"unknown session_type {session_type!r}")
 
@@ -104,17 +165,15 @@ def login(username, password):
 
 
 def add_user(user: JWebmailUser):
-    passwd = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_STORE_PASSWD"]
-
-    r = select_timeout_session()("jwebmail", passwd, EXPIRATION_SEC)
+    r = select_timeout_session()
     r.set(user.get_id(), user.password)
+    r.close()
 
 
 def load_user(username: str) -> JWebmailUser:
-    ss_password = current_app.config["JWEBMAIL"]["READ_MAILS"]["SESSION_STORE_PASSWD"]
-
-    r = select_timeout_session()("jwebmail", ss_password, EXPIRATION_SEC)
+    r = select_timeout_session()
     passwd = r.get(username)
+    r.close()
     if passwd is None:
         return None
     return JWebmailUser(username, passwd)
-- 
cgit v1.2.3