AOSSIE-Org / AOSSIE-Org/PictoPy

BUG:SQLite OperationalError crashes indexing when scanning large directory trees (e.g., Google Takeout exports)

オープン 初心者向け
#1,500 コメント 7 件 リアクション 0 件 担当者 0 名 GitHub で見る
bug
主要言語
Python
スター
284
フォーク
680
平均マージ
7日 5時間
マージ済み PR(30日)
4

説明

### Is there an existing issue for this?

- [x] I have searched the existing issues

### What happened?

### Description
When indexing a directory that contains a large number of subfolders, the folder sync process extracts all subfolder IDs and passes them directly to `db_get_images_by_folder_ids` and `db_get_image_sync_state_by_folder_ids`.

These functions dynamically construct an `IN (?, ?, ...)` clause with one placeholder for every folder ID, without batching them. If the number of subfolders exceeds SQLite's maximum variable limit (999 or 32766 depending on the SQLite version), an `OperationalError: too many SQL variables` is thrown, which silently breaks the entire folder indexing workflow.

**Why this matters in the real world:**
This bug heavily impacts the most common use cases for a photo manager:
1. **Google Takeout / iCloud Exports:** When users export their photos to migrate to an offline app like PictoPy, the export structures the data by creating a new subfolder for almost every single day or album. A medium-sized library of 10,000 photos can easily generate 2,000+ subfolders, instantly triggering this crash.
2. **Root Directory Imports:** Users often point photo managers directly at their `C:\Users\Name\Pictures` folder or external hard drives and tell it to "sync everything". Deep directory trees will quickly exceed this limit.

Image

### Steps to Reproduce
1. Create a parent folder with over 35,000 subdirectories (to exceed even modern SQLite 3.32+ limits).
2. Attempt to add the parent folder to PictoPy for indexing.
3. The indexing will silently fail.
4. Check the backend logs to find an `OperationalError: too many SQL variables` thrown from `app/database/images.py`.

*(Note: I've attached a screenshot demonstrating this exact crash traceback simulated on the backend).*

### Expected Behavior
The database query should handle an arbitrarily large list of `folder_ids` by chunking them, ensuring SQLite limits are respected and large libraries can be imported seamlessly.

### Actual Behavior
The query crashes, halting the entire indexing process for that directory tree.

### Code Pointers
- `backend/app/database/images.py`: Both `db_get_images_by_folder_ids` and `db_get_image_sync_state_by_folder_ids` use `placeholders = ",".join("?" for _ in folder_ids)` directly.
- *Note:* The codebase already implements the correct chunking pattern for this exact issue elsewhere! `db_get_embeddings_for_image_ids` correctly utilizes `SQLITE_ID_CHUNK = 500`. These two folder functions simply missed getting the same protection.

### Suggested Fix
Refactor the queries to chunk the `folder_ids` array into batches of `SQLITE_ID_CHUNK` and aggregate the results, bringing them inline with the safe query patterns already established in `images.py`.

If the maintainers agree this should be resolved to support large library imports, I'd love to submit a PR to fix it! Please assign it to me if so.

### Record

- [x] I agree to follow this project's Code of Conduct

コントリビューションガイド

コントリビューションガイドを開く

調査の方向性

Start in backend/app/database/images.py with db_get_images_by_folder_ids and db_get_image_sync_state_by_folder_ids, then compare the existing chunking pattern in db_get_embeddings_for_image_ids. Reproduce the failure with more folder IDs than SQLite permits and trace the folder-sync indexing path. Done means both queries handle large folder ID lists without OperationalError and indexing completes.

索引モデルが issue の本文から書いたものです。

評価

技術スタック
python, sqlite
領域
backend, database
issue の種類
バグ
難易度
2/5
見積もり時間
1〜3時間
活発さ
活発
明瞭さ
明確に書かれている
初心者へのやさしさ
84/100

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。