From ac5110e022dd69cd7b6b667a2349f654cb683b0d Mon Sep 17 00:00:00 2001
From: "Jannis M. Hoffmann" <jannis@fehcom.de>
Date: Tue, 5 Nov 2024 23:45:22 +0100
Subject: optimize sql queries

---
 src/jwebmail/__init__.py   |  2 +-
 src/jwebmail/read_mails.py | 51 ++++++++++++++++------------------------------
 2 files changed, 19 insertions(+), 34 deletions(-)

(limited to 'src/jwebmail')

diff --git a/src/jwebmail/__init__.py b/src/jwebmail/__init__.py
index fffb551..04b9e2a 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.4.0.dev1"
+__version__ = "2.4.0.dev2"
 
 csrf = CSRFProtect()
 
diff --git a/src/jwebmail/read_mails.py b/src/jwebmail/read_mails.py
index cae04b1..324b4d0 100644
--- a/src/jwebmail/read_mails.py
+++ b/src/jwebmail/read_mails.py
@@ -1,5 +1,4 @@
 from contextlib import closing
-from datetime import datetime, timedelta
 
 from flask import current_app, g
 from flask_login import UserMixin, current_user
@@ -53,31 +52,25 @@ class MysqlTimeoutSession:
         )
 
     def set(self, key, value):
-        timeout = datetime.now() + timedelta(seconds=self.timeout)
-
         with closing(self.conn.cursor()) as cur:
-            cur.execute("DELETE FROM session WHERE user = %s", [key])
             cur.execute(
-                "INSERT INTO session VALUES (%s, %s, %s)", [key, value, timeout]
+                "REPLACE INTO session VALUES (%s, %s, now() + INTERVAL %s SECOND)",
+                [key, value, self.timeout],
             )
             self.conn.commit()
 
     def get(self, key):
         with closing(self.conn.cursor()) as cur:
-            cur.execute("DELETE FROM session WHERE timeout < NOW()")
+            cur.execute("DELETE FROM session WHERE timeout < now()")
+            cur.execute(
+                "UPDATE session SET timeout = now() + INTERVAL %s SECOND WHERE user = %s",
+                [self.timeout, key],
+            )
             cur.execute("SELECT password FROM session WHERE user = %s", [key])
             row = cur.fetchone()
+            self.conn.commit()
 
-            if row is None:
-                self.conn.commit()
-                return None
-            else:
-                timeout = datetime.now() + timedelta(seconds=self.timeout)
-                cur.execute(
-                    "UPDATE session SET timeout = %s WHERE user = %s", [timeout, key]
-                )
-                self.conn.commit()
-                return row[0]
+            return None if row is None else row[0]
 
     def close(self):
         self.conn.close()
@@ -89,7 +82,7 @@ class SqliteTimeoutSession:
 
         self.timeout = timeout
 
-        self.conn = sqlite3.connect(database)
+        self.conn = sqlite3.connect(database, isolation_level="IMMEDIATE")
         cur = self.conn.cursor()
         cur.execute(
             "CREATE TABLE IF NOT EXISTS session (user text PRIMARY KEY, password text, timeout integer NOT NULL) STRICT"
@@ -97,32 +90,24 @@ class SqliteTimeoutSession:
         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(?))",
-                [key, value, timeout],
+                "REPLACE INTO session VALUES (?, ?, unixepoch('now', format('%d seconds', ?)))",
+                [key, value, self.timeout],
             )
             self.conn.commit()
 
     def get(self, key):
         with closing(self.conn.cursor()) as cur:
             cur.execute("DELETE FROM session WHERE timeout < unixepoch()")
-            cur.execute("SELECT password FROM session WHERE user = ?", [key])
+            cur.execute(
+                "UPDATE session SET timeout = unixepoch('now', format('%d seconds', ?)) WHERE user = ? RETURNING password",
+                [self.timeout, key],
+            )
             row = cur.fetchone()
+            self.conn.commit()
 
-            if row is None:
-                self.conn.commit()
-                return None
-            else:
-                timeout = datetime.now() + timedelta(seconds=self.timeout)
-                cur.execute(
-                    "UPDATE session SET timeout = unixepoch(?) WHERE user = ?",
-                    [timeout, key],
-                )
-                self.conn.commit()
-                return row[0]
+            return None if row is None else row[0]
 
     def close(self):
         self.conn.close()
-- 
cgit v1.2.3