diff options
| -rw-r--r-- | app.py | 107 |
1 files changed, 69 insertions, 38 deletions
@@ -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() + 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] + 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() + 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: + 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"])) + 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"])) - 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) - # 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() + 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: |