AOSSIE-Org / AOSSIE-Org/PictoPy

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

Ouverte Adaptée aux débutants
#1,500 7 commentaires 0 réactions 0 personnes assignées Voir sur GitHub
bug
Langage dominant
Python
Étoiles
284
Forks
680
Merge moyen
7 j 5 h
PR mergées (30 j)
4

Description

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

Guide de contribution

Ouvrir le guide de contribution

Piste de recherche

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.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
python, sqlite
Domaine
backend, database
Type d'issue
Bug
Difficulté
2/5
Temps estimé
1-3 heures
Activité
Active
Clarté
Clairement spécifiée
Accessibilité débutants
84/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.