From b0204b30ea5bf54010d351f314ca123514021930 Mon Sep 17 00:00:00 2001
From: Jan Tuomi <jan@jantuomi.fi>
Date: Mon, 13 Apr 2026 20:22:35 +0300
Subject: Improve SQLite configuration

---
 app.py | 117 +++++++++++++++++++++++++++++++++++++++++------------------------
 1 file changed, 74 insertions(+), 43 deletions(-)

(limited to 'app.py')

diff --git a/app.py b/app.py
index 0c864c6..1f51107 100644
--- a/app.py
+++ b/app.py
@@ -26,12 +26,33 @@ AUTHOR = os.environ["AUTHOR"]
 IG_LINK = os.environ.get("IG_LINK", None)
 BASE_URL = os.environ["BASE_URL"]
 
+# SQLite connection configuration per kerkour.com/sqlite-for-servers
+_write_lock = threading.Lock()
+
+def _configure_connection(conn):
+    conn.execute("PRAGMA journal_mode = WAL")
+    conn.execute("PRAGMA busy_timeout = 5000")
+    conn.execute("PRAGMA synchronous = NORMAL")
+    conn.execute("PRAGMA cache_size = 1000000000")
+    conn.execute("PRAGMA foreign_keys = true")
+    conn.execute("PRAGMA temp_store = memory")
+
+def get_read_db():
+    conn = sqlite3.connect(DB_FILE)
+    _configure_connection(conn)
+    return conn
+
+def get_write_db():
+    conn = sqlite3.connect(DB_FILE, isolation_level=None)
+    _configure_connection(conn)
+    return conn
+
 # Initialize DB
 def init_db():
-    # Create dir if not exists
     os.makedirs(os.path.dirname(DB_FILE), exist_ok=True)
 
-    with sqlite3.connect(DB_FILE) as conn:
+    conn = get_write_db()
+    try:
         conn.execute("""
             CREATE TABLE IF NOT EXISTS files (
                 id TEXT PRIMARY KEY,
@@ -39,8 +60,10 @@ def init_db():
                 description TEXT,
                 date TEXT,
                 thumbnail TEXT
-            )
+            ) STRICT
         """)
+    finally:
+        conn.close()
 
 # Get image files from the given Google Drive folder (non-recursive)
 def list_drive_files(folder_id):
@@ -82,46 +105,51 @@ def save_files_periodically():
             drive_files = list_drive_files(GDRIVE_FOLDER)
             current_ids = set()
 
-            with sqlite3.connect(DB_FILE) as conn:
-                cur = conn.cursor()
-
-                for f in drive_files:
-                    current_ids.add(f["id"])
-                    date_match = ISO_DATE_REGEX.match(f["name"])
-                    date = date_match.group(0) if date_match else f["createdTime"][:10]
-
-                    cur.execute("SELECT description FROM files WHERE id = ?", (f["id"],))
-                    row = cur.fetchone()
-
-                    if row is None:
-                        # File not in DB — insert new with thumbnail
-                        thumb_path = download_and_create_thumbnail(f["id"])
-                        cur.execute("""
-                            INSERT OR IGNORE INTO files (id, name, description, date, thumbnail)
-                            VALUES (?, ?, ?, ?, ?)
-                        """, (f["id"], f["name"], f.get("description", ""), date, thumb_path))
-                    else:
-                        # File exists — update description if changed
-                        existing_description = row[0] or ""
-                        new_description = f.get("description", "")
-                        if existing_description != new_description:
+            with _write_lock:
+                conn = get_write_db()
+                try:
+                    conn.execute("BEGIN IMMEDIATE")
+                    cur = conn.cursor()
+
+                    for f in drive_files:
+                        current_ids.add(f["id"])
+                        date_match = ISO_DATE_REGEX.match(f["name"])
+                        date = date_match.group(0) if date_match else f["createdTime"][:10]
+
+                        cur.execute("SELECT description FROM files WHERE id = ?", (f["id"],))
+                        row = cur.fetchone()
+
+                        if row is None:
+                            thumb_path = download_and_create_thumbnail(f["id"])
                             cur.execute("""
-                                UPDATE files
-                                SET name = ?, description = ?, date = ?
-                                WHERE id = ?
-                            """, (f["name"], new_description, date, f["id"]))
-
-                    conn.commit()
-
-                # Delete removed files
-                cur.execute("SELECT id, thumbnail FROM files")
-                all_local = cur.fetchall()
-                for file_id, thumb_path in all_local:
-                    if file_id not in current_ids:
-                        cur.execute("DELETE FROM files WHERE id = ?", (file_id,))
-                        if os.path.exists(thumb_path):
-                            os.remove(thumb_path)
-                conn.commit()
+                                INSERT OR IGNORE INTO files (id, name, description, date, thumbnail)
+                                VALUES (?, ?, ?, ?, ?)
+                            """, (f["id"], f["name"], f.get("description", ""), date, thumb_path))
+                        else:
+                            existing_description = row[0] or ""
+                            new_description = f.get("description", "")
+                            if existing_description != new_description:
+                                cur.execute("""
+                                    UPDATE files
+                                    SET name = ?, description = ?, date = ?
+                                    WHERE id = ?
+                                """, (f["name"], new_description, date, f["id"]))
+
+                    # Delete removed files
+                    cur.execute("SELECT id, thumbnail FROM files")
+                    all_local = cur.fetchall()
+                    for file_id, thumb_path in all_local:
+                        if file_id not in current_ids:
+                            cur.execute("DELETE FROM files WHERE id = ?", (file_id,))
+                            if os.path.exists(thumb_path):
+                                os.remove(thumb_path)
+
+                    conn.execute("COMMIT")
+                except Exception:
+                    conn.execute("ROLLBACK")
+                    raise
+                finally:
+                    conn.close()
 
         except Exception as e:
             print("Error updating file list:", e)
@@ -144,10 +172,13 @@ app = create_app()
 # Route: Gallery
 @app.route("/")
 def gallery():
-    with sqlite3.connect(DB_FILE) as conn:
+    conn = get_read_db()
+    try:
         cur = conn.cursor()
         cur.execute("SELECT id, name, description, date, thumbnail FROM files ORDER BY date DESC")
         files = cur.fetchall()
+    finally:
+        conn.close()
 
     grouped = defaultdict(list)
     for file in files:
-- 
cgit v1.3