optimize sql queries

3 files changed, 21 insertions, 36 deletions

diff --git a/README.md b/README.md
index 872fa20..102d867 100644
--- a/README.md
+++ b/README.md
@@ -45,6 +45,6 @@ Make sure the config file can only be read by user/group `jwebmail` when you spe
         CREATE TABLE session (user char(64) PRIMARY KEY, password varchar(255), timeout timestamp NOT NULL);
         CREATE INDEX timeout_idx ON session (timeout); -- Optional
 
-4. Grant privileges to the user jwebmail for the above table for at least SELECT, INSERT, UPDATE and DELETE
+4. Grant privileges to the user jwebmail for the above table for at least SELECT, INSERT, REPLACE, UPDATE and DELETE
 
-        GRANT SELECT, INSERT, UPDATE, DELETE PRIVILEGES ON 'jwebmaildb1'.'session' TO 'jwebmail'@'localhost';
+        GRANT SELECT, INSERT, REPLACE, UPDATE, DELETE PRIVILEGES ON 'jwebmaildb1'.'session' TO 'jwebmail'@'localhost';
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()