AOSSIE-Org / AOSSIE-Org/PictoPy

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

未关闭 适合新手
#1,500 7 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看
bug
主要语言
Python
星标
284
派生
680
平均合并
7 天 5 小时
30 天内合并 PR
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 摘要。