* fix(db): normalize timestamps and fix recently added album sorting
SQLite stores timestamps as TEXT and uses string comparison for ORDER BY.
Timestamps in RFC3339 T-format ('2024-01-01T10:00:00Z') sort incorrectly
against space-format ('2024-01-01 10:00:00+00:00') because 'T' (ASCII 84)
> ' ' (ASCII 32), causing albums with T-format timestamps to appear as
newer than they are in the "Recently Added" list.
This adds a migration to normalize all T-format timestamps across all
tables to the space-format expected by go-sqlite3, wraps the
recently_added sort with datetime() to make it format-agnostic, and
replaces the plain album timestamp indexes with expression indexes to
maintain query performance.
* fix(test): improve recently_added sort test robustness
Use same-date timestamps (2024-01-15T08:00:00Z vs 2024-01-15 20:00:00)
so the T-vs-space character difference at position 10 actually triggers
the sorting bug. Initialize index variables to -1 and assert both test
albums are found before comparing positions.
* chore(db): update migration timestamp to 2026-03-16
* fix(persistence): add nil guards to cursor wrapping in folder and mediafile repos
Prevent SIGSEGV panic when queryWithStableResults yields a zero-value
struct on the rows.Err() path (e.g., "database is locked" during
concurrent scanning). Extract cursor wrapping into wrapFolderCursor and
wrapMediaFileCursor with nil checks matching the existing pattern in
album_repository.go.
Fixes#5138
* fix(persistence): wrap original cursor error in nil guard messages
Use %w to preserve the underlying error (e.g., "database is locked")
so callers can use errors.Is/As for root cause analysis. Tests now
verify the original error is accessible via errors.Is.
* fix(persistence): add nil guards and error wrapping in album, folder, and mediafile cursor functions
Signed-off-by: Deluan <deluan@navidrome.org>
---------
Signed-off-by: Deluan <deluan@navidrome.org>
* build: add sqlite_fts5 build tag to enable FTS5 support
* feat: add SearchBackend config option (default: fts)
* feat: add buildFTS5Query for safe FTS5 query preprocessing
* feat: add FTS5 search backend with config toggle, refactor legacy search
- Add searchExprFunc type and getSearchExpr() for backend selection
- Rename fullTextExpr to legacySearchExpr
- Add ftsSearchExpr using FTS5 MATCH subquery
- Update fullTextFilter in sql_restful.go to use configured backend
* feat: add FTS5 migration with virtual tables, triggers, and search_participants
Creates FTS5 virtual tables for media_file, album, and artist with
unicode61 tokenizer and diacritic folding. Adds search_participants
column, populates from JSON, and sets up INSERT/UPDATE/DELETE triggers.
* feat: populate search_participants in PostMapArgs for FTS5 indexing
* test: add FTS5 search integration tests
* fix: exclude FTS5 virtual tables from e2e DB restore
The restoreDB function iterates all tables in sqlite_master and
runs DELETE + INSERT to reset state. FTS5 contentless virtual tables
cannot be directly deleted from. Since triggers handle FTS5 sync
automatically, simply skip tables matching *_fts and *_fts_* patterns.
* build: add compile-time guard for sqlite_fts5 build tag
Same pattern as netgo: compilation fails with a clear error if
the sqlite_fts5 build tag is missing.
* build: add sqlite_fts5 tag to reflex dev server config
* build: extract GO_BUILD_TAGS variable in Makefile to avoid duplication
* fix: strip leading * from FTS5 queries to prevent "unknown special query" error
* feat: auto-append prefix wildcard to FTS5 search tokens for broader matching
Every plain search token now gets a trailing * appended (e.g., "love" becomes
"love*"), so searching for "love" also matches "lovelace", "lovely", etc.
Quoted phrases are preserved as exact matches without wildcards. Results are
ordered alphabetically by name/title, so shorter exact matches naturally
appear first.
* fix: clarify comments about FTS5 operator neutralization
The comments said "strip" but the code lowercases operators to
neutralize them (FTS5 operators are case-sensitive). Updated comments
to accurately describe the behavior.
* fix: use fmt.Sprintf for FTS5 phrase placeholders
The previous encoding used rune('0'+index) which silently breaks with
10+ quoted phrases. Use fmt.Sprintf for arbitrary index support.
* fix: validate and normalize SearchBackend config option
Normalize the value to lowercase and fall back to "fts" with a log
warning for unrecognized values. This prevents silent misconfiguration
from typos like "FTS", "Legacy", or "fts5".
* refactor: improve documentation for build tags and FTS5 requirements
Signed-off-by: Deluan <deluan@navidrome.org>
* refactor: convert FTS5 query and search backend normalization tests to DescribeTable format
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: add sqlite_fts5 build tag to golangci configuration
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: add UISearchDebounceMs configuration option and update related components
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: fall back to legacy search when SearchFullString is enabled
FTS5 is token-based and cannot match substrings within words, so
getSearchExpr now returns legacySearchExpr when SearchFullString
is true, regardless of SearchBackend setting.
* fix: add sqlite_fts5 build tag to CI pipeline and Dockerfile
* fix: add WHEN clauses to FTS5 AFTER UPDATE triggers
Added WHEN clauses to the media_file_fts_au, album_fts_au, and
artist_fts_au triggers so they only fire when FTS-indexed columns
actually change. Previously, every row update (e.g., play count, rating,
starred status) triggered an unnecessary delete+insert cycle in the FTS
shadow tables. The WHEN clauses use IS NOT for NULL-safe comparison of
each indexed column, avoiding FTS index churn for non-indexed updates.
* feat: add SearchBackend configuration option to data and insights components
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: enhance input sanitization for FTS5 by stripping additional punctuation and special characters
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: add search_normalized column for punctuated name search (R.E.M., AC/DC)
Add index-time normalization and query-time single-letter collapsing to
fix FTS5 search for punctuated names. A new search_normalized column
stores concatenated forms of punctuated words (e.g., "R.E.M." → "REM",
"AC/DC" → "ACDC") and is indexed in FTS5 tables. At query time, runs of
consecutive single letters (from dot-stripping) are collapsed into OR
expressions like ("R E M" OR REM*) to match both the original tokens and
the normalized form. This enables searching by "R.E.M.", "REM", "AC/DC",
"ACDC", "A-ha", or "Aha" and finding the correct results.
* refactor: simplify isSingleUnicodeLetter to avoid []rune allocation
Use utf8.DecodeRuneInString to check for a single Unicode letter
instead of converting the entire string to a []rune slice.
* feat: define ftsSearchColumns for flexible FTS5 search column inclusion
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: update collapseSingleLetterRuns to return quoted phrases for abbreviations
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: implement extractPunctuatedWords to handle artist/album names with embedded punctuation
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: implement extractPunctuatedWords to handle artist/album names with embedded punctuation
Signed-off-by: Deluan <deluan@navidrome.org>
* refactor: punctuated word handling to improve processing of artist/album names
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: add CJK support for search queries with LIKE filters
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: enhance FTS5 search by adding album version support and CJK handling
Signed-off-by: Deluan <deluan@navidrome.org>
* refactor: search configuration to use structured options
Signed-off-by: Deluan <deluan@navidrome.org>
* feat: enhance search functionality to support punctuation-only queries and update related tests
Signed-off-by: Deluan <deluan@navidrome.org>
---------
Signed-off-by: Deluan <deluan@navidrome.org>
* fix(db): Include items with no annotation for starred=false, handle has_rating=false
* hardcode starred instead
* test: ensure albums and artists without annotations are included in starred and has_rating filters
Signed-off-by: Deluan <deluan@navidrome.org>
* refactor: replace starred and has_rating filters with annotationBoolFilter for consistency
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: update annotationBoolFilter to handle boolean values correctly in SQL expressions
Signed-off-by: Deluan <deluan@navidrome.org>
---------
Signed-off-by: Deluan <deluan@navidrome.org>
Co-authored-by: Deluan <deluan@navidrome.org>
* feat(subsonic): add averageRating to API responses
Add averageRating attribute to Subsonic API responses for artists,
albums, and songs. The average is calculated across all user ratings.
* perf(db): add index for average rating queries
Add composite index on (item_id, item_type, rating) to optimize
the correlated subquery used for calculating average ratings.
Signed-off-by: Terry Raimondo <terry.raimondo@gmail.com>
* test: add tests for averageRating feature
Add tests for:
- Album.AverageRating calculation in persistence layer
- MediaFile.AverageRating calculation in persistence layer
- AverageRating mapping in subsonic response helpers
Signed-off-by: Terry Raimondo <terry.raimondo@gmail.com>
* test: improve averageRating rounding test with 3 users
Add third test user to fixtures and update rounding test to use
3 ratings (5 + 4 + 4) / 3 = 4.33 for proper decimal rounding coverage.
Signed-off-by: Terry Raimondo <terry.raimondo@gmail.com>
* perf: store avg_rating on entity tables instead of using subquery
- Add avg_rating column to album, media_file, and artist tables
- Update SetRating() to recalculate and store average when ratings change
- Read avg_rating directly from entity table in withAnnotation()
- Remove old annotation index migration (no longer needed)
This trades write-time computation for read-time performance by
pre-computing the average rating instead of using a correlated
subquery on every read.
* feat: add Subsonic.EnableAverageRating config option (default true)
Allow administrators to disable exposing averageRating in Subsonic API
responses if they don't want to expose other users' rating data.
The avg_rating column is still updated internally when users rate items,
but the value is only included in API responses when this option is enabled.
* address PR comments
- Use structs:"avg_rating" with db:"avg_rating" tag instead of SQL alias
- Remove avg_rating indexes (not needed)
- Populate avg_rating columns from existing ratings in migration
* Woops
* rename avg_rating column to average_rating
---------
Signed-off-by: Terry Raimondo <terry.raimondo@gmail.com>
* fix(deps): update wazero dependencies to resolve issues
Signed-off-by: Deluan <deluan@navidrome.org>
* fix(deps): update wazero dependency to latest version
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: correct track ordering when sorting playlists by album
Fixed issue #3177 where tracks within multi-disc albums were displayed out of order when sorting playlists by album. The playlist track repository was using an incomplete sort mapping that only sorted by album name and artist, missing the critical disc_number and track_number fields.
Changed the album sort mapping in playlist_track_repository from:
order_album_name, order_album_artist_name
to:
order_album_name, order_album_artist_name, disc_number, track_number, order_artist_name, title
This now matches the sorting used in the media file repository, ensuring tracks are sorted by:
1. Album name (groups by album)
2. Album artist (handles compilations)
3. Disc number (multi-disc album discs in order)
4. Track number (tracks within disc in order)
5. Artist name and title (edge cases with missing metadata)
Added comprehensive tests with a multi-disc test album to verify correct sorting behavior.
* chore: sync go.mod and go.sum with master
* chore: align playlist album sort order with mediafile_repository (use album_id)
* fix: clean up test playlist to prevent state leakage in randomized test runs
---------
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: prevent foreign key constraint error in album participants
Prevent foreign key constraint errors when album participants contain
artist IDs that don't exist in the artist table. The updateParticipants
method now filters out non-existent artist IDs before attempting to
insert album_artists relationships.
- Add defensive filtering in updateParticipants() to query existing artist IDs
- Only insert relationships for artist IDs that exist in the artist table
- Add comprehensive regression test for both albums and media files
- Fixes scanner errors when JSON participant data contains stale artist references
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: optimize foreign key handling in album artists insertion
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: improve participants foreign key tests
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: clarify comments in album artists insertion query
Signed-off-by: Deluan <deluan@navidrome.org>
* test: add cleanup to album repository tests
Added individual test cleanup to 4 album repository tests that create temporary
artists and albums. This ensures proper test isolation by removing test data
after each test completes, preventing test interference when running with
shuffle mode. Each test now cleans up its own temporary data from the artist,
library_artist, album, and album_artists tables using direct SQL deletion.
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: refactor participant JSON handling for simpler and improved SQL processing
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: update test command description in Makefile for clarity
Signed-off-by: Deluan <deluan@navidrome.org>
* fix: refactor album repository tests to use albumRepository type directly
Signed-off-by: Deluan <deluan@navidrome.org>
---------
Signed-off-by: Deluan <deluan@navidrome.org>
* Start migration to dbx package
* Fix annotations and bookmarks bindings
* Fix tests
* Fix more tests
* Remove remaining references to beego/orm
* Add PostScanner/PostMapper interfaces
* Fix importing SmartPlaylists
* Renaming
* More renaming
* Fix artist DB mapping
* Fix playlist updates
* Remove bookmarks at the end of the test
* Remove remaining `orm` struct tags
* Fix user timestamps DB access
* Fix smart playlist evaluated_at DB access
* Fix search3
This implements basic tests for functionality related to loading and
processing external album covers, both on the scanning size, and on the
display side.
- Create model.DataStore, with provision for transactions
- Change all layers dependencies on repositories to use DataStore
- Implemented persistence.SQLStore
- Removed iTunes Bridge/Importer support
Deluan Quintão