* fix(smartplaylists): merge negated artist/tag rules in AND groups
Smart playlists with many negated role/tag conditions ANDed together (e.g.
100+ "isNot artist" rules, issue #5511) generated one correlated NOT EXISTS
subquery per rule, scanning media_file_artists for every candidate row. On
large libraries this took minutes and triggered API timeouts and SQLite lock
contention.
By De Morgan, "NOT EXISTS(role=X) AND NOT EXISTS(role=Y)" is equivalent to
"NOT EXISTS(role=X OR role=Y)", so multiple negated conditions for the same
field can be collapsed into a single batched NOT EXISTS. This mirrors the
existing OR-group merge that #5515 added for positive conditions.
The shared grouping/batching logic is extracted into mergeSameFieldConds,
parameterized by polarity, so the OR/positive and AND/negated paths reuse one
algorithm instead of duplicating it. roleCondGroup/tagCondGroup gain a 'not'
flag to emit the negated subquery.
Benchmark (323k tracks, 120 isNot artist rules, reporter's exact shape):
merged ~54ms vs unmerged ~8.7s steady-state (~160x faster).
* docs: trim redundant comments on merge helpers
The De Morgan explanation was repeated across three doc comments. Keep it in
one place (mergeNegatedJsonConds, where negation is introduced) and reduce the
shared core and group-type comments to concise one-liners.
* fix(server): optimize smart playlist role queries for large criteria (#5511)
Role-based smart playlist criteria (artist, composer, etc.) now query
the indexed media_file_artists join table instead of parsing JSON via
json_tree() on every row. Multiple conditions for the same role within
an OR group are merged into a single EXISTS subquery (batched at 200
to stay under SQLite's expression tree depth limit).
A composite index (media_file_id, role) replaces the now-redundant
single-column (media_file_id) index on media_file_artists.
Benchmark (40k tracks, 500 patterns, 3 artists/track):
- Merged join-table: 15ms (9.3x faster)
- Merged json_tree: 30ms (4.6x faster)
- Unmerged baseline: 137ms
* refactor: simplify role condition SQL generation and benchmark
Extract shared roleCondSQL/roleExistsSQL helpers to deduplicate the
EXISTS template between roleCond and roleCondGroup. Use slices.Chunk
for batching per project convention. Extract runBenchQuery helper to
eliminate triplicated benchmark execution loop.
* chore: raise roleCondBatchSize to 350
The empirical SQLite limit is 496 conditions per merged EXISTS
subquery. Raising from 200 to 350 reduces the number of batches
(e.g. 500 patterns now splits into 2 batches instead of 3).
* fix(server): apply OR-merge optimization to tag conditions too
Generalize mergeRoleConds into mergeJsonConds to also collapse multiple
tag conditions for the same tag (e.g. genre) within OR groups. This
gives the same ~5x speedup for tag-heavy smart playlists as the role
optimization gives for artist-heavy ones.
* refactor: benchmark uses real criteria pipeline instead of hand-built SQL
The "Current" sub-benchmark now builds criteria.Criteria expressions and
runs them through the actual newSmartPlaylistCriteria → Where() → ToSql()
pipeline, validating the real production code path. The baseline still
uses hand-built SQL representing the old json_tree approach.
* fix: stabilize merged group ordering and close rows before error check
Sort group keys in mergeJsonConds so the merged additions have
deterministic order across runs, improving SQLite statement cache reuse.
Move rows.Close() before rows.Err() in benchmark helper.
