mirror of
https://github.com/navidrome/navidrome.git
synced 2026-08-31 07:30:32 +00:00
82fde00ecc
* feat(scrobbler): add scrobble_filter column to user * feat(scrobbler): validate scrobble filter criteria on user save * refactor(persistence): make smart playlist join helpers package-level * feat(scrobbler): add MediaFileRepository.MatchesCriteria * feat(scrobbler): filter external scrobbles with per-user criteria * feat(ui): add scrobble filter field to user form * fix(scrobbler): default scrobble_filter to empty string for existing users * refactor(scrobbler): also gate playback reports on the scrobble filter Playback reports carry the same track metadata to plugin scrobblers, so a filtered track leaked through that third dispatch path. Skip the filter evaluation entirely when no scrobbler is active. * refactor(persistence): move criteria join building into criteria_sql.go The join set a criteria needs was decided in criteria_sql.go but built in smart_playlist_repository.go, so both callers had to pair the two by hand. * refactor(persistence): unexport smartPlaylistCriteria methods The type never leaves the package, so the exported names advertised an API that callers outside persistence could never reach. Also disambiguates where/orderBy from squirrel's SelectBuilder methods of the same name. * fix(ui): cap the scrobble filter field width fullWidth stretched it across the whole page next to 256px inputs. Bounded at 40em, with two rows and a resize handle so JSON rules stay readable. * refactor(ui): move scrobble filter input in UserEdit component * feat(ui): add pt-BR translations for the scrobble filter * fix(scrobbler): take the filter verdict before incPlay incPlay mutates play counts and dates a filter can test on, so evaluating at dispatch time let one play decide differently on either side of the increment: a track could be scrobbled despite matching, or lose only its stopped report and strand presence plugins. Reject limit/offset too, rather than silently ignoring part of a rule copied from a smart playlist. * fix(scrobbler): filter the report from an expired session The expiry callback runs with a stub user carrying no filter, so evaluating there always returned false and leaked the track to plugin scrobblers. That is the normal path for clients that never send stopped, such as legacy Subsonic now-playing. Carry the last verdict on the session instead. * refactor(scrobbler): skip the now-playing enqueue instead of threading the verdict Queuing an entry only to drop it at dispatch also cancelled a pending announcement for the previous, unfiltered track, since the queue is keyed by player and a new entry replaces the old one. * fix(scrobbler): evaluate the filter regardless of active scrobblers The verdict is stored on the session and dispatched at expiry, so skipping evaluation when no scrobbler was active let a plugin enabled mid-session receive a filtered track. The empty-filter guard above already gives servers without scrobbling the same free path, so the shortcut only ever applied to users who had a filter set.
638 lines
34 KiB
Go
638 lines
34 KiB
Go
package persistence
|
|
|
|
import (
|
|
"fmt"
|
|
"strings"
|
|
"time"
|
|
|
|
"github.com/navidrome/navidrome/model"
|
|
"github.com/navidrome/navidrome/model/criteria"
|
|
. "github.com/onsi/ginkgo/v2"
|
|
. "github.com/onsi/gomega"
|
|
)
|
|
|
|
var _ = Describe("Smart playlist criteria SQL", func() {
|
|
BeforeEach(func() {
|
|
criteria.AddRoles([]string{"artist", "composer", "producer"})
|
|
criteria.AddTagNames([]string{"genre", "mood", "releasetype", "recordingdate", "replaygain_album_gain"})
|
|
criteria.AddNumericTags([]string{"rate"})
|
|
})
|
|
|
|
DescribeTable("expressions",
|
|
func(expr criteria.Expression, expectedSQL string, expectedArgs ...any) {
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(Equal(expectedSQL))
|
|
Expect(args).To(HaveExactElements(expectedArgs...))
|
|
},
|
|
Entry("all group",
|
|
criteria.All{criteria.Contains{"title": "love"}, criteria.Gt{"rating": 3}},
|
|
"(media_file.title LIKE ? AND annotation.rating > ?)", "%love%", 3),
|
|
Entry("any group",
|
|
criteria.Any{criteria.Is{"title": "Low Rider"}, criteria.Is{"album": "Best Of"}},
|
|
"(media_file.title = ? OR media_file.album = ?)", "Low Rider", "Best Of"),
|
|
Entry("is string", criteria.Is{"title": "Low Rider"}, "media_file.title = ?", "Low Rider"),
|
|
Entry("is bool", criteria.Is{"loved": true}, "annotation.starred = ?", true),
|
|
Entry("is numeric list", criteria.Is{"library_id": []int{1, 2}}, "media_file.library_id IN (?,?)", 1, 2),
|
|
Entry("is not", criteria.IsNot{"title": "Low Rider"}, "media_file.title <> ?", "Low Rider"),
|
|
Entry("gt", criteria.Gt{"playCount": 10}, "annotation.play_count > ?", 10),
|
|
Entry("lt", criteria.Lt{"playCount": 10}, "(annotation.play_count < ? OR annotation.play_count IS NULL)", 10),
|
|
Entry("contains", criteria.Contains{"title": "Low Rider"}, "media_file.title LIKE ?", "%Low Rider%"),
|
|
Entry("not contains", criteria.NotContains{"title": "Low Rider"}, "media_file.title NOT LIKE ?", "%Low Rider%"),
|
|
Entry("starts with", criteria.StartsWith{"title": "Low Rider"}, "media_file.title LIKE ?", "Low Rider%"),
|
|
Entry("ends with", criteria.EndsWith{"title": "Low Rider"}, "media_file.title LIKE ?", "%Low Rider"),
|
|
Entry("in range", criteria.InTheRange{"year": []int{1980, 1990}}, "(media_file.year >= ? AND media_file.year <= ?)", 1980, 1990),
|
|
Entry("before", criteria.Before{"lastPlayed": time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)}, "annotation.play_date < ?", time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)),
|
|
Entry("after", criteria.After{"lastPlayed": time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)}, "annotation.play_date > ?", time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)),
|
|
Entry("in playlist", criteria.InPlaylist{"id": "deadbeef-dead-beef"}, "media_file.id IN (SELECT media_file_id FROM playlist_tracks pl LEFT JOIN playlist on pl.playlist_id = playlist.id WHERE (pl.playlist_id = ? AND playlist.public = ?))", "deadbeef-dead-beef", 1),
|
|
Entry("not in playlist", criteria.NotInPlaylist{"id": "deadbeef-dead-beef"}, "media_file.id NOT IN (SELECT media_file_id FROM playlist_tracks pl LEFT JOIN playlist on pl.playlist_id = playlist.id WHERE (pl.playlist_id = ? AND playlist.public = ?))", "deadbeef-dead-beef", 1),
|
|
Entry("album annotation", criteria.Gt{"albumRating": 3}, "album_annotation.rating > ?", 3),
|
|
Entry("artist annotation", criteria.Is{"artistLoved": true}, "artist_annotation.starred = ?", true),
|
|
Entry("album column", criteria.Gt{"albumSongCount": 5}, "album.song_count > ?", 5),
|
|
Entry("album duration column", criteria.Lt{"albumDuration": 600}, "album.duration < ?", 600),
|
|
Entry("album size column", criteria.Gt{"albumSize": 1000}, "album.size > ?", 1000),
|
|
Entry("album date column", criteria.After{"albumDateAdded": time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)}, "album.created_at > ?", time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)),
|
|
Entry("album modified column", criteria.Before{"albumDateModified": time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)}, "album.updated_at < ?", time.Date(2021, 10, 1, 0, 0, 0, 0, time.Local)),
|
|
// Annotation fields use a COALESCE default (0 for numeric, false for bool) so that tracks
|
|
// with no annotation row behave as that default. To keep the annotation index usable, the
|
|
// COALESCE is dropped when the compared value cannot match the default (the missing-row
|
|
// case is then naturally excluded); otherwise an explicit `OR col IS NULL` preserves it.
|
|
Entry("is safe (value != default)", criteria.Is{"playCount": 3}, "annotation.play_count = ?", 3),
|
|
Entry("is unsafe (value == default)", criteria.Is{"playCount": 0},
|
|
"(annotation.play_count = ? OR annotation.play_count IS NULL)", 0),
|
|
Entry("is bool false (value == default)", criteria.Is{"loved": false},
|
|
"(annotation.starred = ? OR annotation.starred IS NULL)", false),
|
|
Entry("gt safe (value >= default)", criteria.Gt{"playCount": 0}, "annotation.play_count > ?", 0),
|
|
Entry("gt unsafe (value < default)", criteria.Gt{"playCount": -1},
|
|
"(annotation.play_count > ? OR annotation.play_count IS NULL)", -1),
|
|
Entry("lt safe (value <= default)", criteria.Lt{"playCount": 0}, "annotation.play_count < ?", 0),
|
|
Entry("lt unsafe (value > default)", criteria.Lt{"playCount": 5},
|
|
"(annotation.play_count < ? OR annotation.play_count IS NULL)", 5),
|
|
Entry("isNot annotation keeps null match", criteria.IsNot{"playCount": 3},
|
|
"(annotation.play_count <> ? OR annotation.play_count IS NULL)", 3),
|
|
Entry("isNot annotation value == default", criteria.IsNot{"playCount": 0},
|
|
"annotation.play_count <> ?", 0),
|
|
Entry("in range spanning default", criteria.InTheRange{"playCount": []int{-1, 5}},
|
|
"((annotation.play_count >= ? OR annotation.play_count IS NULL) AND (annotation.play_count <= ? OR annotation.play_count IS NULL))", -1, 5),
|
|
Entry("in range above default", criteria.InTheRange{"playCount": []int{1, 5}},
|
|
"(annotation.play_count >= ? AND (annotation.play_count <= ? OR annotation.play_count IS NULL))", 1, 5),
|
|
// A list value can't drive the index and a default-inclusive list has per-element NULL
|
|
// semantics, so the COALESCE form is kept to stay equivalent to the original.
|
|
Entry("is list keeps coalesce", criteria.Is{"playCount": []int{0, 3}},
|
|
"COALESCE(annotation.play_count, 0) IN (?,?)", 0, 3),
|
|
// LIKE operators can't use the column index, so annotation fields keep the COALESCE form to
|
|
// match missing-annotation rows exactly as before (a NULL column never matches LIKE).
|
|
Entry("contains annotation keeps coalesce", criteria.Contains{"playCount": 0},
|
|
"COALESCE(annotation.play_count, 0) LIKE ?", "%0%"),
|
|
Entry("starts with annotation keeps coalesce", criteria.StartsWith{"rating": 5},
|
|
"COALESCE(annotation.rating, 0) LIKE ?", "5%"),
|
|
Entry("not contains annotation keeps coalesce", criteria.NotContains{"playCount": 0},
|
|
"COALESCE(annotation.play_count, 0) NOT LIKE ?", "%0%"),
|
|
// Bool annotation fields only have a clean index-friendly form for equality; ordering
|
|
// comparators keep the COALESCE form so the missing-row default is honored exactly.
|
|
Entry("gt bool keeps coalesce", criteria.Gt{"loved": false},
|
|
"COALESCE(annotation.starred, false) > ?", false),
|
|
// A list value on a bool field is non-scalar, so it keeps the COALESCE form too (same as the
|
|
// numeric list case) — otherwise a NULL column would diverge from the original.
|
|
Entry("is bool list keeps coalesce", criteria.Is{"loved": []any{true}},
|
|
"COALESCE(annotation.starred, false) IN (?)", true),
|
|
Entry("tag is", criteria.Is{"genre": "Rock"}, "exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value' and value = ?)", "Rock"),
|
|
Entry("tag is not", criteria.IsNot{"genre": "Rock"}, "not exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value' and value = ?)", "Rock"),
|
|
Entry("tag contains", criteria.Contains{"genre": "Rock"}, "exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value' and value LIKE ?)", "%Rock%"),
|
|
Entry("tag not contains", criteria.NotContains{"genre": "Rock"}, "not exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value' and value LIKE ?)", "%Rock%"),
|
|
Entry("numeric tag", criteria.Lt{"rate": 6}, "exists (select 1 from json_tree(media_file.tags, '$.rate') where key='value' and CAST(value AS REAL) < ?)", 6),
|
|
Entry("tag alias", criteria.Is{"albumtype": "album"}, "exists (select 1 from json_tree(media_file.tags, '$.releasetype') where key='value' and value = ?)", "album"),
|
|
Entry("field alias via tag registration", criteria.Is{"recordingdate": "2024-01-01"}, "media_file.date = ?", "2024-01-01"),
|
|
Entry("role is", criteria.Is{"artist": "u2"}, "exists (select 1 from media_file_artists mfa join artist on artist.id = mfa.artist_id where mfa.media_file_id = media_file.id and mfa.role = ? and artist.name = ?)", "artist", "u2"),
|
|
Entry("role contains", criteria.Contains{"composer": "Lennon"}, "exists (select 1 from media_file_artists mfa join artist on artist.id = mfa.artist_id where mfa.media_file_id = media_file.id and mfa.role = ? and artist.name LIKE ?)", "composer", "%Lennon%"),
|
|
Entry("role not contains", criteria.NotContains{"artist": "u2"}, "not exists (select 1 from media_file_artists mfa join artist on artist.id = mfa.artist_id where mfa.media_file_id = media_file.id and mfa.role = ? and artist.name LIKE ?)", "artist", "%u2%"),
|
|
// ReplayGain fields
|
|
Entry("rgAlbumGain is", criteria.Is{"rgAlbumGain": 0}, "media_file.rg_album_gain = ?", 0),
|
|
Entry("rgAlbumGain gt", criteria.Gt{"rgAlbumGain": -6.0}, "media_file.rg_album_gain > ?", -6.0),
|
|
Entry("rgTrackPeak lt", criteria.Lt{"rgTrackPeak": 1.0}, "media_file.rg_track_peak < ?", 1.0),
|
|
// isMissing — tags
|
|
Entry("isMissing tag [true]", criteria.IsMissing{"genre": true},
|
|
"not exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value')"),
|
|
Entry("isMissing tag [false]", criteria.IsMissing{"genre": false},
|
|
"exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value')"),
|
|
// isMissing — roles
|
|
Entry("isMissing role [true]", criteria.IsMissing{"artist": true},
|
|
"not exists (select 1 from media_file_artists mfa where mfa.media_file_id = media_file.id and mfa.role = ?)", "artist"),
|
|
Entry("isMissing role [false]", criteria.IsMissing{"artist": false},
|
|
"exists (select 1 from media_file_artists mfa where mfa.media_file_id = media_file.id and mfa.role = ?)", "artist"),
|
|
// isPresent — tags
|
|
Entry("isPresent tag [true]", criteria.IsPresent{"genre": true},
|
|
"exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value')"),
|
|
Entry("isPresent tag [false]", criteria.IsPresent{"genre": false},
|
|
"not exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value')"),
|
|
// isPresent — roles
|
|
Entry("isPresent role [true]", criteria.IsPresent{"composer": true},
|
|
"exists (select 1 from media_file_artists mfa where mfa.media_file_id = media_file.id and mfa.role = ?)", "composer"),
|
|
Entry("isPresent role [false]", criteria.IsPresent{"composer": false},
|
|
"not exists (select 1 from media_file_artists mfa where mfa.media_file_id = media_file.id and mfa.role = ?)", "composer"),
|
|
// isMissing/isPresent — nullable column fields (ReplayGain)
|
|
Entry("isMissing rgAlbumGain [true]", criteria.IsMissing{"rgAlbumGain": true},
|
|
"(media_file.rg_album_gain IS NULL)"),
|
|
Entry("isMissing rgAlbumGain [false]", criteria.IsMissing{"rgAlbumGain": false},
|
|
"(media_file.rg_album_gain IS NOT NULL)"),
|
|
Entry("isPresent rgTrackPeak [true]", criteria.IsPresent{"rgTrackPeak": true},
|
|
"(media_file.rg_track_peak IS NOT NULL)"),
|
|
Entry("isPresent rgTrackPeak [false]", criteria.IsPresent{"rgTrackPeak": false},
|
|
"(media_file.rg_track_peak IS NULL)"),
|
|
// isMissing — replaygain_* tag-name alias resolves to the nullable column (issue #5584)
|
|
Entry("isMissing replaygain_album_gain alias [true]", criteria.IsMissing{"replaygain_album_gain": true},
|
|
"(media_file.rg_album_gain IS NULL)"),
|
|
Entry("isPresent replaygain_album_gain alias [true]", criteria.IsPresent{"replaygain_album_gain": true},
|
|
"(media_file.rg_album_gain IS NOT NULL)"),
|
|
// isMissing/isPresent — string column fields (empty string means missing)
|
|
Entry("isMissing mbz_recording_id [true]", criteria.IsMissing{"mbz_recording_id": true},
|
|
"(media_file.mbz_recording_id IS NULL OR media_file.mbz_recording_id = ?)", ""),
|
|
Entry("isMissing mbz_recording_id [false]", criteria.IsMissing{"mbz_recording_id": false},
|
|
"(media_file.mbz_recording_id IS NOT NULL AND media_file.mbz_recording_id <> ?)", ""),
|
|
Entry("isPresent mbz_album_id [true]", criteria.IsPresent{"mbz_album_id": true},
|
|
"(media_file.mbz_album_id IS NOT NULL AND media_file.mbz_album_id <> ?)", ""),
|
|
Entry("isPresent mbz_album_id [false]", criteria.IsPresent{"mbz_album_id": false},
|
|
"(media_file.mbz_album_id IS NULL OR media_file.mbz_album_id = ?)", ""),
|
|
// lyrics: absence is encoded as '' or '[]' (empty serialized LyricList)
|
|
Entry("isMissing lyrics [true]", criteria.IsMissing{"lyrics": true},
|
|
"(media_file.lyrics IS NULL OR media_file.lyrics = ? OR media_file.lyrics = ?)", "", "[]"),
|
|
Entry("isPresent lyrics [true]", criteria.IsPresent{"lyrics": true},
|
|
"(media_file.lyrics IS NOT NULL AND media_file.lyrics <> ? AND media_file.lyrics <> ?)", "", "[]"),
|
|
Entry("isMissing lyrics [false]", criteria.IsMissing{"lyrics": false},
|
|
"(media_file.lyrics IS NOT NULL AND media_file.lyrics <> ? AND media_file.lyrics <> ?)", "", "[]"),
|
|
Entry("isPresent lyrics [false]", criteria.IsPresent{"lyrics": false},
|
|
"(media_file.lyrics IS NULL OR media_file.lyrics = ? OR media_file.lyrics = ?)", "", "[]"),
|
|
// isMissing/isPresent — nullable numeric columns (BPM, BitDepth)
|
|
Entry("isMissing bpm [true]", criteria.IsMissing{"bpm": true},
|
|
"(media_file.bpm IS NULL)"),
|
|
Entry("isPresent bpm [true]", criteria.IsPresent{"bpm": true},
|
|
"(media_file.bpm IS NOT NULL)"),
|
|
Entry("isMissing bitdepth [true]", criteria.IsMissing{"bitdepth": true},
|
|
"(media_file.bit_depth IS NULL)"),
|
|
Entry("isPresent bitdepth [false]", criteria.IsPresent{"bitdepth": false},
|
|
"(media_file.bit_depth IS NULL)"),
|
|
// isMissing/isPresent — more string column fields (empty string means missing)
|
|
Entry("isMissing album [true]", criteria.IsMissing{"album": true},
|
|
"(media_file.album IS NULL OR media_file.album = ?)", ""),
|
|
Entry("isMissing comment [true]", criteria.IsMissing{"comment": true},
|
|
"(media_file.comment IS NULL OR media_file.comment = ?)", ""),
|
|
Entry("isMissing catalognumber [true]", criteria.IsMissing{"catalognumber": true},
|
|
"(media_file.catalog_num IS NULL OR media_file.catalog_num = ?)", ""),
|
|
Entry("isMissing discsubtitle [true]", criteria.IsMissing{"discsubtitle": true},
|
|
"(media_file.disc_subtitle IS NULL OR media_file.disc_subtitle = ?)", ""),
|
|
Entry("isMissing albumcomment [true]", criteria.IsMissing{"albumcomment": true},
|
|
"(media_file.mbz_album_comment IS NULL OR media_file.mbz_album_comment = ?)", ""),
|
|
Entry("isMissing sorttitle [true]", criteria.IsMissing{"sorttitle": true},
|
|
"(media_file.sort_title IS NULL OR media_file.sort_title = ?)", ""),
|
|
Entry("isMissing sortalbum [true]", criteria.IsMissing{"sortalbum": true},
|
|
"(media_file.sort_album_name IS NULL OR media_file.sort_album_name = ?)", ""),
|
|
Entry("isMissing sortartist [true]", criteria.IsMissing{"sortartist": true},
|
|
"(media_file.sort_artist_name IS NULL OR media_file.sort_artist_name = ?)", ""),
|
|
Entry("isMissing sortalbumartist [true]", criteria.IsMissing{"sortalbumartist": true},
|
|
"(media_file.sort_album_artist_name IS NULL OR media_file.sort_album_artist_name = ?)", ""),
|
|
Entry("isMissing explicitstatus [true]", criteria.IsMissing{"explicitstatus": true},
|
|
"(media_file.explicit_status IS NULL OR media_file.explicit_status = ?)", ""),
|
|
Entry("isPresent comment [true]", criteria.IsPresent{"comment": true},
|
|
"(media_file.comment IS NOT NULL AND media_file.comment <> ?)", ""),
|
|
)
|
|
|
|
Describe("playlist permissions", func() {
|
|
It("allows public or same-owner playlist references for regular users", func() {
|
|
sqlizer, err := newSmartPlaylistCriteria(
|
|
criteria.Criteria{Expression: criteria.InPlaylist{"id": "deadbeef-dead-beef"}},
|
|
withSmartPlaylistOwner(model.User{ID: "owner-id", IsAdmin: false}),
|
|
).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(Equal("media_file.id IN (SELECT media_file_id FROM playlist_tracks pl LEFT JOIN playlist on pl.playlist_id = playlist.id WHERE (pl.playlist_id = ? AND (playlist.public = ? OR playlist.owner_id = ?)))"))
|
|
Expect(args).To(HaveExactElements("deadbeef-dead-beef", 1, "owner-id"))
|
|
})
|
|
|
|
It("allows all playlist references for admins", func() {
|
|
sqlizer, err := newSmartPlaylistCriteria(
|
|
criteria.Criteria{Expression: criteria.InPlaylist{"id": "deadbeef-dead-beef"}},
|
|
withSmartPlaylistOwner(model.User{ID: "admin-id", IsAdmin: true}),
|
|
).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(Equal("media_file.id IN (SELECT media_file_id FROM playlist_tracks pl LEFT JOIN playlist on pl.playlist_id = playlist.id WHERE (pl.playlist_id = ?))"))
|
|
Expect(args).To(HaveExactElements("deadbeef-dead-beef"))
|
|
})
|
|
})
|
|
|
|
It("builds relative date expressions", func() {
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.InTheLast{"lastPlayed": 30}}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(Equal("annotation.play_date > ?"))
|
|
Expect(args).To(HaveExactElements(startOfPeriod(30, time.Now())))
|
|
})
|
|
|
|
It("builds negated relative date expressions", func() {
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.NotInTheLast{"lastPlayed": 30}}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(Equal("(annotation.play_date < ? OR annotation.play_date IS NULL)"))
|
|
Expect(args).To(HaveExactElements(startOfPeriod(30, time.Now())))
|
|
})
|
|
|
|
It("returns an error for unknown fields", func() {
|
|
_, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.EndsWith{"unknown": "value"}}).where()
|
|
|
|
Expect(err).To(MatchError("invalid field in criteria: unknown"))
|
|
})
|
|
|
|
It("returns an error when isMissing is used with a regular field", func() {
|
|
_, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.IsMissing{"year": true}}).where()
|
|
Expect(err).To(MatchError(ContainSubstring("isMissing/isPresent operator is not supported for field")))
|
|
})
|
|
|
|
It("returns an error when isPresent is used with a regular field", func() {
|
|
_, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.IsPresent{"title": true}}).where()
|
|
Expect(err).To(MatchError(ContainSubstring("isMissing/isPresent operator is not supported for field")))
|
|
})
|
|
|
|
It("returns an error when isMissing has a non-boolean value", func() {
|
|
_, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.IsMissing{"genre": "hello"}}).where()
|
|
Expect(err).To(MatchError(ContainSubstring("invalid boolean value for 'missing' expression")))
|
|
})
|
|
|
|
It("returns an error for a range over a tag/role field", func() {
|
|
_, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.InTheRange{"rate": []int{1, 5}}}).where()
|
|
Expect(err).To(MatchError(ContainSubstring("range operator not supported for tag/role field")))
|
|
})
|
|
|
|
It("returns a clear error for a malformed range value", func() {
|
|
_, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: criteria.InTheRange{"playCount": []int{1, 2, 3}}}).where()
|
|
Expect(err).To(MatchError(ContainSubstring("must be a [min, max] pair")))
|
|
})
|
|
|
|
Describe("sort", func() {
|
|
It("sorts by regular fields", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "title"}).orderBy()).To(Equal("media_file.title asc"))
|
|
})
|
|
|
|
It("sorts by tag fields", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "genre"}).orderBy()).To(Equal("COALESCE(json_extract(media_file.tags, '$.genre[0].value'), '') asc"))
|
|
})
|
|
|
|
It("sorts by role fields", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "artist"}).orderBy()).To(Equal("COALESCE(json_extract(media_file.participants, '$.artist[0].name'), '') asc"))
|
|
})
|
|
|
|
It("casts numeric tags when sorting", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "rate"}).orderBy()).To(Equal("CAST(COALESCE(json_extract(media_file.tags, '$.rate[0].value'), '') AS REAL) asc"))
|
|
})
|
|
|
|
It("sorts by albumtype alias", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "albumtype"}).orderBy()).To(Equal("COALESCE(json_extract(media_file.tags, '$.releasetype[0].value'), '') asc"))
|
|
})
|
|
|
|
It("sorts by random", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "random"}).orderBy()).To(Equal("random() asc"))
|
|
})
|
|
|
|
It("sorts by album columns bare, with no COALESCE default", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "-albumDateAdded,trackNumber"}).orderBy()).
|
|
To(Equal("album.created_at desc, media_file.track_number asc"))
|
|
})
|
|
|
|
It("sorts by multiple fields", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "title,-rating"}).orderBy()).To(Equal("media_file.title asc, COALESCE(annotation.rating, 0) desc"))
|
|
})
|
|
|
|
It("reverts order when order is desc", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "-date,artist", Order: "desc"}).orderBy()).To(Equal("media_file.date asc, COALESCE(json_extract(media_file.participants, '$.artist[0].name'), '') desc"))
|
|
})
|
|
|
|
It("ignores invalid sort fields", func() {
|
|
Expect(newSmartPlaylistCriteria(criteria.Criteria{Sort: "bogus,title"}).orderBy()).To(Equal("media_file.title asc"))
|
|
})
|
|
})
|
|
|
|
It("has SQL mappings for all non-tag/non-role criteria fields", func() {
|
|
for _, name := range criteria.AllFieldNames() {
|
|
info, ok := criteria.LookupField(name)
|
|
Expect(ok).To(BeTrue(), "field %q registered but LookupField fails", name)
|
|
if info.IsTag || info.IsRole {
|
|
continue
|
|
}
|
|
_, hasSQLField := smartPlaylistFields[info.Name()]
|
|
Expect(hasSQLField).To(BeTrue(), "criteria field %q (name=%q) has no entry in smartPlaylistFields", name, info.Name())
|
|
}
|
|
})
|
|
|
|
It("declares a joinType matching the table each field selects from", func() {
|
|
// Omitting the joinType still compiles, so without this the field would only fail at
|
|
// refresh time with "no such column".
|
|
joinByTable := map[string]smartPlaylistJoinType{
|
|
"media_file": smartPlaylistJoinNone,
|
|
"annotation": smartPlaylistJoinNone,
|
|
"album": smartPlaylistJoinAlbum,
|
|
"album_annotation": smartPlaylistJoinAlbumAnnotation,
|
|
"artist_annotation": smartPlaylistJoinArtistAnnotation,
|
|
}
|
|
for name, field := range smartPlaylistFields {
|
|
if field.expr == "" {
|
|
continue
|
|
}
|
|
table, _, ok := strings.Cut(field.expr, ".")
|
|
Expect(ok).To(BeTrue(), "field %q has expr %q with no table prefix", name, field.expr)
|
|
want, known := joinByTable[table]
|
|
Expect(known).To(BeTrue(), "field %q selects from unknown table %q", name, table)
|
|
Expect(field.joinType).To(Equal(want), "field %q selects from %q but declares the wrong joinType", name, table)
|
|
}
|
|
})
|
|
|
|
Describe("JSON condition merging", func() {
|
|
It("merges multiple role conditions in an OR group into a single EXISTS", func() {
|
|
expr := criteria.Any{
|
|
criteria.Contains{"artist": "Beatles"},
|
|
criteria.Contains{"artist": "Kraftwerk"},
|
|
criteria.Contains{"artist": "Pink Floyd"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(Equal("(exists (select 1 from media_file_artists mfa join artist on artist.id = mfa.artist_id where mfa.media_file_id = media_file.id and mfa.role = ? and (artist.name LIKE ? OR artist.name LIKE ? OR artist.name LIKE ?)))"))
|
|
Expect(args).To(HaveExactElements("artist", "%Beatles%", "%Kraftwerk%", "%Pink Floyd%"))
|
|
})
|
|
|
|
It("does not merge role conditions from different roles", func() {
|
|
expr := criteria.Any{
|
|
criteria.Contains{"artist": "Beatles"},
|
|
criteria.Contains{"composer": "Lennon"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, _, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(ContainSubstring("mfa.role = ?"))
|
|
// Two separate EXISTS since roles differ
|
|
Expect(strings.Count(sql, "exists")).To(Equal(2))
|
|
})
|
|
|
|
It("does not merge negated role conditions", func() {
|
|
expr := criteria.Any{
|
|
criteria.NotContains{"artist": "Beatles"},
|
|
criteria.NotContains{"artist": "Kraftwerk"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, _, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
// Two separate "not exists" since they are negated
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(2))
|
|
})
|
|
|
|
It("batches large groups to avoid SQLite expression tree depth limit", func() {
|
|
// Create jsonCondBatchSize + 1 conditions to trigger batching into 2 groups
|
|
anyExprs := make(criteria.Any, jsonCondBatchSize+1)
|
|
for i := range anyExprs {
|
|
anyExprs[i] = criteria.Contains{"artist": fmt.Sprintf("Artist%d", i)}
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: anyExprs}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
// Should produce 2 EXISTS subqueries (one batch of jsonCondBatchSize, one of 1)
|
|
Expect(strings.Count(sql, "exists")).To(Equal(2))
|
|
// First batch has jsonCondBatchSize patterns, second has 1 => total args:
|
|
// 2 roles + (jsonCondBatchSize + 1) patterns
|
|
Expect(args).To(HaveLen(2 + jsonCondBatchSize + 1))
|
|
})
|
|
|
|
It("merges role conditions while preserving non-role conditions", func() {
|
|
expr := criteria.Any{
|
|
criteria.Contains{"title": "Love"},
|
|
criteria.Contains{"artist": "Beatles"},
|
|
criteria.Contains{"artist": "Kraftwerk"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(ContainSubstring("media_file.title LIKE ?"))
|
|
Expect(sql).To(ContainSubstring("artist.name LIKE ? OR artist.name LIKE ?"))
|
|
Expect(args).To(HaveExactElements("%Love%", "artist", "%Beatles%", "%Kraftwerk%"))
|
|
})
|
|
|
|
It("merges multiple tag conditions in an OR group into a single EXISTS", func() {
|
|
expr := criteria.Any{
|
|
criteria.Contains{"genre": "Rock"},
|
|
criteria.Contains{"genre": "Metal"},
|
|
criteria.Contains{"genre": "Punk"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(sql).To(Equal("(exists (select 1 from json_tree(media_file.tags, '$.genre') where key='value' and (value LIKE ? OR value LIKE ? OR value LIKE ?)))"))
|
|
Expect(args).To(HaveExactElements("%Rock%", "%Metal%", "%Punk%"))
|
|
})
|
|
|
|
It("does not merge tag conditions from different tags", func() {
|
|
expr := criteria.Any{
|
|
criteria.Contains{"genre": "Rock"},
|
|
criteria.Contains{"mood": "Happy"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, _, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(strings.Count(sql, "exists")).To(Equal(2))
|
|
})
|
|
|
|
It("does not merge negated tag conditions", func() {
|
|
expr := criteria.Any{
|
|
criteria.NotContains{"genre": "Rock"},
|
|
criteria.NotContains{"genre": "Metal"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, _, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(2))
|
|
})
|
|
|
|
It("merges role and tag conditions independently", func() {
|
|
expr := criteria.Any{
|
|
criteria.Contains{"artist": "Beatles"},
|
|
criteria.Contains{"artist": "Kraftwerk"},
|
|
criteria.Contains{"genre": "Rock"},
|
|
criteria.Contains{"genre": "Metal"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
// Two merged EXISTS: one for roles, one for tags
|
|
Expect(strings.Count(sql, "exists")).To(Equal(2))
|
|
Expect(sql).To(ContainSubstring("artist.name LIKE ? OR artist.name LIKE ?"))
|
|
Expect(sql).To(ContainSubstring("value LIKE ? OR value LIKE ?"))
|
|
Expect(args).To(HaveLen(2 + 2 + 1)) // 2 tag patterns + 2 role patterns + 1 role name
|
|
})
|
|
|
|
It("merges negated role conditions in an AND group into a single NOT EXISTS", func() {
|
|
expr := criteria.All{
|
|
criteria.IsNot{"artist": "Beatles"},
|
|
criteria.IsNot{"artist": "Kraftwerk"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
// A single NOT EXISTS with both names ORed inside (De Morgan)
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(1))
|
|
Expect(sql).To(ContainSubstring("artist.name = ? OR artist.name = ?"))
|
|
Expect(args).To(HaveExactElements("artist", "Beatles", "Kraftwerk"))
|
|
})
|
|
|
|
It("merges negated notContains role conditions in an AND group", func() {
|
|
expr := criteria.All{
|
|
criteria.NotContains{"artist": "Beatles"},
|
|
criteria.NotContains{"artist": "Kraftwerk"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(1))
|
|
Expect(sql).To(ContainSubstring("artist.name LIKE ? OR artist.name LIKE ?"))
|
|
Expect(args).To(HaveExactElements("artist", "%Beatles%", "%Kraftwerk%"))
|
|
})
|
|
|
|
It("merges negated tag conditions in an AND group into a single NOT EXISTS", func() {
|
|
expr := criteria.All{
|
|
criteria.NotContains{"genre": "Rock"},
|
|
criteria.NotContains{"genre": "Metal"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(1))
|
|
Expect(sql).To(ContainSubstring("value LIKE ? OR value LIKE ?"))
|
|
Expect(args).To(HaveExactElements("%Rock%", "%Metal%"))
|
|
})
|
|
|
|
It("does not merge a single negated condition with a positive one of the same role in AND", func() {
|
|
// AND of mixed polarity must not be collapsed: NOT EXISTS(a) AND EXISTS(b)
|
|
// is not equivalent to any single merged subquery.
|
|
expr := criteria.All{
|
|
criteria.Contains{"artist": "Beatles"},
|
|
criteria.IsNot{"artist": "Kraftwerk"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, _, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
// One positive EXISTS and one negated NOT EXISTS, kept separate
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(1))
|
|
Expect(strings.Count(sql, "exists")).To(Equal(2)) // "not exists" contains "exists"
|
|
})
|
|
|
|
It("does not merge negated conditions of different roles in AND", func() {
|
|
expr := criteria.All{
|
|
criteria.IsNot{"artist": "Beatles"},
|
|
criteria.IsNot{"composer": "Lennon"},
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: expr}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, _, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(2))
|
|
})
|
|
|
|
It("batches large negated AND groups to avoid SQLite expression tree depth limit", func() {
|
|
allExprs := make(criteria.All, jsonCondBatchSize+1)
|
|
for i := range allExprs {
|
|
allExprs[i] = criteria.IsNot{"artist": fmt.Sprintf("Artist%d", i)}
|
|
}
|
|
sqlizer, err := newSmartPlaylistCriteria(criteria.Criteria{Expression: allExprs}).where()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
|
|
sql, args, err := sqlizer.ToSql()
|
|
Expect(err).ToNot(HaveOccurred())
|
|
// Two NOT EXISTS subqueries (one batch of jsonCondBatchSize, one of 1)
|
|
Expect(strings.Count(sql, "not exists")).To(Equal(2))
|
|
Expect(args).To(HaveLen(2 + jsonCondBatchSize + 1))
|
|
})
|
|
})
|
|
|
|
Describe("joins", func() {
|
|
It("excludes sort-only joins from expression joins", func() {
|
|
c := criteria.Criteria{Expression: criteria.All{criteria.Contains{"title": "love"}}, Sort: "albumRating"}
|
|
cSQL := newSmartPlaylistCriteria(c)
|
|
|
|
Expect(cSQL.expressionJoins()).To(Equal(smartPlaylistJoinNone))
|
|
Expect(cSQL.requiredJoins().has(smartPlaylistJoinAlbumAnnotation)).To(BeTrue())
|
|
})
|
|
|
|
It("includes expression-based joins", func() {
|
|
c := criteria.Criteria{Expression: criteria.All{criteria.Gt{"albumRating": 3}}}
|
|
|
|
Expect(newSmartPlaylistCriteria(c).expressionJoins().has(smartPlaylistJoinAlbumAnnotation)).To(BeTrue())
|
|
})
|
|
|
|
It("detects nested album and artist joins", func() {
|
|
c := criteria.Criteria{Expression: criteria.All{
|
|
criteria.Any{criteria.All{criteria.Is{"albumLoved": true}}},
|
|
criteria.Any{criteria.Gt{"artistPlayCount": 10}},
|
|
}}
|
|
|
|
joins := newSmartPlaylistCriteria(c).requiredJoins()
|
|
Expect(joins.has(smartPlaylistJoinAlbumAnnotation)).To(BeTrue())
|
|
Expect(joins.has(smartPlaylistJoinArtistAnnotation)).To(BeTrue())
|
|
})
|
|
|
|
It("detects join types from sort fields with direction prefixes", func() {
|
|
c := criteria.Criteria{Expression: criteria.All{criteria.Contains{"title": "love"}}, Sort: "-artistRating"}
|
|
|
|
Expect(newSmartPlaylistCriteria(c).requiredJoins().has(smartPlaylistJoinArtistAnnotation)).To(BeTrue())
|
|
})
|
|
|
|
It("keeps a sort-only album join out of the expression joins", func() {
|
|
c := criteria.Criteria{Expression: criteria.All{criteria.Contains{"title": "love"}}, Sort: "-albumDateAdded"}
|
|
cSQL := newSmartPlaylistCriteria(c)
|
|
|
|
Expect(cSQL.expressionJoins()).To(Equal(smartPlaylistJoinNone))
|
|
Expect(cSQL.requiredJoins().has(smartPlaylistJoinAlbum)).To(BeTrue())
|
|
})
|
|
|
|
It("distinguishes the album join from the album annotation join", func() {
|
|
c := criteria.Criteria{Expression: criteria.All{criteria.Gt{"albumRating": 3}}}
|
|
joins := newSmartPlaylistCriteria(c).requiredJoins()
|
|
|
|
Expect(joins.has(smartPlaylistJoinAlbumAnnotation)).To(BeTrue())
|
|
Expect(joins.has(smartPlaylistJoinAlbum)).To(BeFalse())
|
|
})
|
|
})
|
|
})
|