AOSSIE-Org / AOSSIE-Org/PictoPy

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

Offen Anfängerfreundlich
#1,500 7 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen
bug
Vorherrschende Sprache
Python
Sterne
284
Forks
680
Ø Merge
7 T. 5 Std.
Gemergte PRs (30 T.)
4

Beschreibung

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

Beitragsleitfaden

Beitragsleitfaden öffnen

Rechercherichtung

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.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Bewertung

Tech-Stack
python, sqlite
Bereich
backend, database
Issue-Typ
Bug
Schwierigkeit
2/5
Geschätzter Aufwand
1-3 Stunden
Aktivitätsstatus
Aktiv
Klarheit
Klar beschrieben
Anfängerfreundlichkeit
84/100

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.