AOSSIE-Org / AOSSIE-Org/PictoPy

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

Đang mở Phù hợp với người mới
#1,500 7 bình luận 0 reaction 0 người được giao Xem trên GitHub
bug
Ngôn ngữ chính
Python
Star
284
Fork
680
Merge trung bình
7 ngày 5 giờ
Pull request đã merge (30 ngày)
4

Mô tả

### 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

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Hướng nghiên cứu

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.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Đánh giá

Công nghệ
python, sqlite
Lĩnh vực
backend, database
Loại issue
Lỗi
Độ khó
2/5
Thời gian dự kiến
1-3 giờ
Mức độ hoạt động
Sôi nổi
Độ rõ ràng
Đặc tả rõ ràng
Mức phù hợp với người mới
84/100

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.