BUG: Dead/inconsistent PRAGMA case_sensitive_like in get_db_connection (affects no queries today, latent footgun)

Open Beginner friendly
#1,346 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
68/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Quiet
Tech stack
python, sqlite
Domain
database

Research direction

Read backend/app/database/connection.py and backend/app/database/folders.py first, then inspect the four get_db_connection call sites in albums.py and the folder-prefix query. Determine whether the pragma is dead configuration or intended behavior; done means the chosen behavior is applied consistently without silently changing subfolder prefix matching.

Written by the indexing model from the issue text.

Description

Is there an existing issue for this?
  • I have searched the existing issues
What happened?

backend/app/database/connection.py:23 sets PRAGMA case_sensitive_like = ON inside get_db_connection(). But that helper is only used in ~4 call sites (all in albums.py), and the only LIKE query in the codebase - db_get_folder_ids_by_path_prefix (backend/app/database/folders.py:344, WHERE folder_path LIKE ? || '%') — uses a raw sqlite3.connect() that never runs the pragma.

So today the pragma changes the behaviour of zero queries (dead config). It becomes a latent footgun the moment any LIKE query is migrated onto get_db_connection(): subfolder prefix matching would silently turn case-sensitive and miss subfolders on case-insensitive filesystems (macOS/Windows).

Fix: remove the pragma (LIKE is case-insensitive by default), or - if case-sensitive LIKE is actually intended - apply it consistently and migrate the folder-prefix query onto the shared helper.

Record
  • I agree to follow this project's Code of Conduct
Dominant language
Python
Stars
289
Forks
685
Avg merge
4d 6h
Merged PRs (30d)
6

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from AOSSIE-Org/PictoPy

All issues in AOSSIE-Org/PictoPy

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.