132 lines
4.9 KiB
Go
132 lines
4.9 KiB
Go
package catalog
|
|
|
|
import (
|
|
"strings"
|
|
"testing"
|
|
)
|
|
|
|
// TestQueryExecutor_NamePrefix_PushedIntoWHERE asserts that when AccessFilter
|
|
// carries a NamePrefix, the resulting paged SQL contains a prefix-anchored
|
|
// dual-column LIKE predicate: first arm matches the idx_media_items_sort_key
|
|
// expression (migration 102), second arm matches LOWER(title) /
|
|
// idx_media_items_search_exact_title (migration 001). Both arms are required
|
|
// so items with a curated sort_title that differs from title (e.g.
|
|
// title="The Office", sort_title="Office, The") are not silently dropped on
|
|
// prefix="the". The LIKE pattern argument is anchored with no leading wildcard.
|
|
func TestQueryExecutor_NamePrefix_PushedIntoWHERE(t *testing.T) {
|
|
exec := &QueryExecutor{Scope: "movie", BaseRelationSQL: "media_items mi"}
|
|
access := AccessFilter{NamePrefix: "Star"}
|
|
|
|
sql, args, err := exec.buildPreviewPageSQL(QueryDefinition{}, access, 20, 0, true)
|
|
if err != nil {
|
|
t.Fatalf("buildPreviewPageSQL returned error: %v", err)
|
|
}
|
|
|
|
// First arm: sort-key expression matching idx_media_items_sort_key.
|
|
if !strings.Contains(sql, "LOWER(COALESCE(NULLIF(BTRIM(mi.sort_title), ''), mi.title)) LIKE") {
|
|
t.Fatalf("expected sort-key LIKE arm matching idx_media_items_sort_key; got %q", sql)
|
|
}
|
|
// Second arm: LOWER(title) matching idx_media_items_search_exact_title;
|
|
// required so curated sort_title doesn't drop title-prefix matches.
|
|
if !strings.Contains(sql, "LOWER(mi.title) LIKE") {
|
|
t.Fatalf("expected LOWER(mi.title) LIKE arm; got %q", sql)
|
|
}
|
|
if strings.Contains(sql, "LIKE '%") {
|
|
t.Fatalf("expected LIKE pattern to be parameterized (no leading literal wildcard); got %q", sql)
|
|
}
|
|
|
|
foundArg := false
|
|
for _, a := range args {
|
|
s, ok := a.(string)
|
|
if !ok {
|
|
continue
|
|
}
|
|
if strings.HasPrefix(strings.ToLower(s), "star") &&
|
|
strings.HasSuffix(s, "%") &&
|
|
!strings.HasPrefix(s, "%") {
|
|
foundArg = true
|
|
break
|
|
}
|
|
}
|
|
if !foundArg {
|
|
t.Fatalf("expected an args entry like \"star%%\" (lowercased, trailing-only wildcard); got args=%v", args)
|
|
}
|
|
}
|
|
|
|
func TestQueryExecutor_NamePrefix_UsesEpisodeSortKeyForEpisodeScope(t *testing.T) {
|
|
exec := &QueryExecutor{}
|
|
access := AccessFilter{NamePrefix: "Pilot"}
|
|
|
|
sql, args, err := exec.buildPreviewPageSQL(
|
|
QueryDefinition{MediaScope: "episode", LibraryIDs: []int{2}},
|
|
access,
|
|
20,
|
|
0,
|
|
true,
|
|
)
|
|
if err != nil {
|
|
t.Fatalf("buildPreviewPageSQL returned error: %v", err)
|
|
}
|
|
if !strings.Contains(sql, "mi.sort_key LIKE") {
|
|
t.Fatalf("expected episode prefix to use projected sort_key; got %q", sql)
|
|
}
|
|
if strings.Contains(sql, "BTRIM(mi.sort_title)") {
|
|
t.Fatalf("episode prefix should not recompute sort_title expression; got %q", sql)
|
|
}
|
|
if len(args) < 2 || args[1] != "pilot%" {
|
|
t.Fatalf("expected episode library args followed by prefix arg; got %v", args)
|
|
}
|
|
}
|
|
|
|
// TestBrowseFilters_NamePrefix_BothArmsSargable asserts that the dual-column
|
|
// LIKE in BrowseRepository's WHERE clause uses an expression that matches
|
|
// idx_media_items_sort_key (migration 102) on the first arm and
|
|
// idx_media_items_search_exact_title (migration 001, on LOWER(title)) on the
|
|
// second arm. Both arms must be index-using; otherwise the planner falls back
|
|
// to a seqscan on /Items?NameStartsWith=X queries against ~200K-row catalogs.
|
|
//
|
|
// Regression guard for the post-perf-overhaul code review (2026-05): the
|
|
// initial first-arm form was LOWER(COALESCE(NULLIF(BTRIM(sort_title),”), ”))
|
|
// which fell back to ” instead of title and matched no index expression.
|
|
func TestBrowseFilters_NamePrefix_BothArmsSargable(t *testing.T) {
|
|
_, where, _, earlyEmpty := filterWhereClauseForSource(
|
|
BrowseFilters{NamePrefix: "Star"}, "media_items mi", "")
|
|
if earlyEmpty {
|
|
t.Fatalf("unexpected earlyEmpty for NamePrefix-only filter")
|
|
}
|
|
// First arm matches idx_media_items_sort_key.
|
|
if !strings.Contains(where, "LOWER(COALESCE(NULLIF(BTRIM(mi.sort_title), ''), mi.title)) LIKE") {
|
|
t.Fatalf("expected sort-key LIKE arm matching idx_media_items_sort_key; got %s", where)
|
|
}
|
|
// Second arm matches idx_media_items_search_exact_title.
|
|
if !strings.Contains(where, "LOWER(mi.title) LIKE") {
|
|
t.Fatalf("expected LOWER(mi.title) LIKE arm; got %s", where)
|
|
}
|
|
// Reject the previous broken form that used '' as the COALESCE fallback.
|
|
if strings.Contains(where, "BTRIM(mi.sort_title), ''), ''))") {
|
|
t.Fatalf("first arm still uses '' fallback (defeats idx_media_items_sort_key); got %s", where)
|
|
}
|
|
}
|
|
|
|
// TestEscapePrefixForLike checks that the helper lowercases input and escapes
|
|
// the LIKE wildcards (% _) and the escape character itself (\).
|
|
func TestEscapePrefixForLike(t *testing.T) {
|
|
cases := []struct {
|
|
in string
|
|
want string
|
|
}{
|
|
{"Star", "star"},
|
|
{" Star ", "star"},
|
|
{"50%", `50\%`},
|
|
{"a_b", `a\_b`},
|
|
{`back\slash`, `back\\slash`},
|
|
{`%_\`, `\%\_\\`},
|
|
}
|
|
for _, tc := range cases {
|
|
got := escapePrefixForLike(tc.in)
|
|
if got != tc.want {
|
|
t.Errorf("escapePrefixForLike(%q) = %q, want %q", tc.in, got, tc.want)
|
|
}
|
|
}
|
|
}
|