navidrome/utils/natural/natural.go
Deluan Quintão 3e55886195
feat: add optional natural sort order for names and titles (#6015)
* feat: add optional natural sort order for names and titles

Album, artist, song and playlist lists sort with a plain text comparison, so
names containing numbers come out as "Foo 1, Foo 10, Foo 2" instead of
"Foo 1, Foo 2, Foo 10" (issue #4554).

Adds an EnableNaturalSorting option, default off, that switches those sorts to
a NATSORT collation registered on every connection and backed by
natural.CompareFold. natural.Compare gained an ASCII case-folding variant
because it replaces 'collate nocase': sort_* columns hold raw tag values, so
without folding they would order uppercase before lowercase.

Applying the collation only inside mapSortOrder would have missed the default
configuration entirely, since that mapper runs only when PreferSortTags is on.
setSortMappings now also rewrites the order_* columns when natural sorting is
enabled on its own. Sorts over plain text columns that are not order_* columns
(playlist.name, album.name, media_file.title, playlist_tracks title) are
wrapped explicitly, and qualified with their table because 'user' is joined and
also has a 'name' column.

The option defaults to off because the collation cannot use the existing
indexes: measured on a synthetic 110k album library, the first page of an
album-by-name listing goes from 0.03ms to 14ms. Indexing the expression was
rejected outright - an index declared with a custom collation makes the whole
database unreadable to any tool that does not register it, including the
sqlite3 CLI, which fails even on 'select count(*)' and 'pragma integrity_check'.

* refactor: fold the two sort-order mappers into one

mapSortOrder and mapNaturalOrder shared the same regex and loop, differing only
in the expression they substituted, and setSortMappings picked between them with
a two-case switch. mapSortOrder now selects the column shape itself and defers to
collatedSort for the collation, so the 'collate' clause is emitted in one place
and the caller only has to decide whether any mapping is needed at all.

The mapper tests were three near-identical cases that each hard-coded one flag
combination; they are now a DescribeTable covering all four combinations of
PreferSortTags and EnableNaturalSorting, which the previous set did not. The
album sorting specs collapse the same way. Behavior is unchanged.

* fix: leave plain sort columns alone when natural sorting is off

collatedSort wrapped its column unconditionally, so the tiebreakers added for
plain text columns picked up 'collate nocase' even with EnableNaturalSorting
off. media_file.title, the playlist_tracks alias of it, and user.user_name are
all declared without a collation, so a default install would have silently
switched those tiebreaks from binary to case-insensitive ordering. Only
playlist.name was already NOCASE and genuinely unaffected.

The helper is now naturalSort and returns the column untouched unless the option
is on, so the default path keeps the collation each column was declared with.
sortCollation had a single remaining caller and folded into mapSortOrder.

Tests: the CompareFold table body was a verbatim copy of the Compare one, so
both now go through one expectOrder helper, and the album sorting specs inline
two single-use closures.

* fix(natural): defer the leading-zero tie-break to keep ordering transitive

Compare applied the padding difference between numerically equal digit runs only
when one side ended at the digit boundary, and ignored it mid-string. That made
the relation intransitive: CompareFold("1","1a") < 0 and CompareFold("1a","01a")
== 0, yet CompareFold("1","01a") > 0.

SQLite requires a collating function to be transitive and leaves ORDER BY
undefined otherwise, so registering this as NATSORT was not safe. Reproduced with
the real driver on three artist names that occur in practice - "3", "3 doors
down" and "03 greedo" - where paging one row at a time returned "03 greedo"
twice and dropped "3" entirely.

The padding difference is now carried as a tie-break that is applied only when
the strings are otherwise equal, which restores transitivity while keeping the
documented intent (a01 < a1, a0 < a00). Three existing entries changed: each
asserted that two distinct strings compare equal, which was the same defect seen
from the other side.

Found by the Codex review on #6015.
2026-08-23 14:31:37 -04:00

119 lines
2.9 KiB
Go

// Package natural provides natural (alphanumeric) string comparison.
// When both strings have digit sequences at the same position, they are
// compared numerically (so "file2" < "file10"); otherwise bytes are
// compared one-by-one. No allocations are made.
package natural
import "strings"
// Compare returns a negative value if a < b, zero if a == b,
// or a positive value if a > b using natural sort ordering.
//
// When two numeric segments are numerically equal (e.g. "01" vs "1"),
// comparison continues with the remaining suffixes, and the padding
// difference is kept as a final tie-breaker that only decides strings
// that are otherwise equal (e.g. "a01" < "a1", "a0" < "a00"). Deferring
// it that way is what keeps the ordering transitive, which SQLite
// requires of a collating function.
func Compare(a, b string) int {
return compare(a, b, false)
}
// CompareFold is Compare with ASCII case folding, matching SQLite's NOCASE
// collation: only A-Z fold, bytes >= 0x80 are compared as-is.
func CompareFold(a, b string) int {
return compare(a, b, true)
}
func compare(a, b string, fold bool) int {
ia, ib := 0, 0
// Set when two runs are numerically equal but differently padded. Applying it
// immediately would break transitivity, so it only decides otherwise-equal strings.
padTie := 0
for ia < len(a) && ib < len(b) {
ca, cb := a[ia], b[ib]
da, db := isDigit(ca), isDigit(cb)
if fold {
ca, cb = lower(ca), lower(cb)
}
switch {
case da && db:
// Both are in digit sequences — compare numerically.
endA := ia
for endA < len(a) && isDigit(a[endA]) {
endA++
}
endB := ib
for endB < len(b) && isDigit(b[endB]) {
endB++
}
if c := compareNumbers(a[ia:endA], b[ib:endB]); c != 0 {
return c
}
if t := strings.Compare(a[ia:endA], b[ib:endB]); t != 0 {
padTie = t
}
ia = endA
ib = endB
case da != db:
return int(ca) - int(cb)
default:
if ca != cb {
return int(ca) - int(cb)
}
ia++
ib++
}
}
if c := (len(a) - ia) - (len(b) - ib); c != 0 {
return c
}
return padTie
}
// compareNumbers compares two digit strings numerically.
// Leading zeros are stripped before comparison.
func compareNumbers(a, b string) int {
// Strip leading zeros.
sa := stripZeros(a)
sb := stripZeros(b)
// Different lengths after stripping means different magnitude.
if len(sa) != len(sb) {
return len(sa) - len(sb)
}
// Same length — compare digit by digit.
for i := range len(sa) {
if sa[i] != sb[i] {
return int(sa[i]) - int(sb[i])
}
}
return 0
}
// stripZeros returns s with leading '0' bytes removed.
// If s is all zeros, returns the last byte (a single "0").
func stripZeros(s string) string {
i := 0
for i < len(s) && s[i] == '0' {
i++
}
if i == len(s) && len(s) > 0 {
return s[len(s)-1:]
}
return s[i:]
}
func isDigit(c byte) bool {
return c >= '0' && c <= '9'
}
func lower(c byte) byte {
if c >= 'A' && c <= 'Z' {
return c + 'a' - 'A'
}
return c
}