diff options
| author | Cody Robibero <cody@robibe.ro> | 2026-07-26 16:18:49 -0400 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2026-07-26 16:18:49 -0400 |
| commit | 1e4d126cb9f1590cfdc0e731647b93836d1a8867 (patch) | |
| tree | baf2e7ba19fc2026508d7e67567cc4f2f66c9336 | |
| parent | b04614e18d2aeed8d3d3de920c381c6bd5b97bdb (diff) | |
| parent | f3a1d56c563c71573ed2e7681b347533897ee70c (diff) | |
Merge pull request #17422 from Shadowghost/performance
Reduce correlated subqueries to improve query performance
13 files changed, 2183 insertions, 144 deletions
diff --git a/Emby.Server.Implementations/Dto/DtoService.cs b/Emby.Server.Implementations/Dto/DtoService.cs index 8cbf42585d..71c3b24907 100644 --- a/Emby.Server.Implementations/Dto/DtoService.cs +++ b/Emby.Server.Implementations/Dto/DtoService.cs @@ -1088,7 +1088,7 @@ namespace Emby.Server.Implementations.Dto dto.ParentId = item.DisplayParentId; } - AddInheritedImages(dto, item, options, owner); + AddInheritedImages(dto, item, options, owner, artistsBatch); if (options.ContainsField(ItemFields.Path)) { @@ -1519,11 +1519,11 @@ namespace Emby.Server.Implementations.Dto } } - private BaseItem? GetImageDisplayParent(BaseItem currentItem, BaseItem originalItem) + private BaseItem? GetImageDisplayParent(BaseItem currentItem, BaseItem originalItem, IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch) { if (currentItem is MusicAlbum musicAlbum) { - var artist = musicAlbum.GetMusicArtist(new DtoOptions(false)); + var artist = GetBatchedAlbumArtist(musicAlbum, artistsBatch) ?? musicAlbum.GetMusicArtist(new DtoOptions(false)); if (artist is not null) { return artist; @@ -1540,7 +1540,20 @@ namespace Emby.Server.Implementations.Dto return parent; } - private void AddInheritedImages(BaseItemDto dto, BaseItem item, DtoOptions options, BaseItem? owner) + private static MusicArtist? GetBatchedAlbumArtist(MusicAlbum album, IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch) + { + if (artistsBatch is null) + { + return null; + } + + var name = album.AlbumArtists.Count > 0 ? album.AlbumArtists[0] : null; + return !string.IsNullOrEmpty(name) && artistsBatch.TryGetValue(name, out var artists) && artists.Length > 0 + ? artists[0] + : null; + } + + private void AddInheritedImages(BaseItemDto dto, BaseItem item, DtoOptions options, BaseItem? owner, IReadOnlyDictionary<string, MusicArtist[]>? artistsBatch) { if (item is UserView { ViewType: CollectionType.playlists } playlistsView && options.GetImageLimit(ImageType.Primary) > 0 @@ -1585,7 +1598,7 @@ namespace Emby.Server.Implementations.Dto || (!(imageTags is not null && imageTags.ContainsKey(ImageType.Thumb)) && thumbLimit > 0) || parent is Series) { - parent ??= isFirst ? GetImageDisplayParent(item, item) ?? owner : parent; + parent ??= isFirst ? GetImageDisplayParent(item, item, artistsBatch) ?? owner : parent; if (parent is null) { break; @@ -1644,7 +1657,7 @@ namespace Emby.Server.Implementations.Dto break; } - parent = GetImageDisplayParent(parent, item); + parent = GetImageDisplayParent(parent, item, artistsBatch); } } diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs index 6fe8563bf5..8e917f6951 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs @@ -4,6 +4,7 @@ using System; using System.Collections.Generic; using System.Linq; using Jellyfin.Data.Enums; +using Jellyfin.Database.Implementations; using Jellyfin.Database.Implementations.Entities; using MediaBrowser.Controller.Entities; using MediaBrowser.Model.Dto; @@ -109,7 +110,6 @@ public sealed partial class BaseItemRepository IsNews = filter.IsNews, IsSeries = filter.IsSeries }) - .Where(e => e.MediaStreams != null) .SelectMany(e => e.MediaStreams!) .Where(e => e.StreamType == (MediaStreamTypeEntity)mediaStreamType) .Select(s => string.IsNullOrEmpty(s.Language) ? "und" : s.Language) // und = undetermined @@ -168,21 +168,16 @@ public sealed partial class BaseItemRepository IsSeries = filter.IsSeries }); - // Keep this as an IQueryable sub-select. Materializing to a list would inline one - // bound parameter per CleanValue and hit SQLite's variable cap on libraries with - // high-cardinality value types (e.g. tens of thousands of artists). - var matchingCleanValues = context.ItemValuesMap - .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type)) - .Join( - innerQueryFilter, - ivm => ivm.ItemId, - g => g.Id, - (ivm, g) => ivm.ItemValue.CleanValue) - .Distinct(); - var innerQuery = PrepareItemQuery(context, filter) .Where(e => e.Type == returnType) - .Where(e => matchingCleanValues.Contains(e.CleanName!)); + .Where(e => context.ItemValuesMap + .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type) && ivm.ItemValue.CleanValue == e.CleanName) + .Join( + innerQueryFilter, + ivm => ivm.ItemId, + g => g.Id, + (ivm, g) => ivm.ItemId) + .Any()); var outerQueryFilter = new InternalItemsQuery(filter.User) { @@ -205,32 +200,42 @@ public sealed partial class BaseItemRepository ExcludeItemIds = filter.ExcludeItemIds }; - // Collapse rows that share a PresentationUniqueKey (e.g. alternate versions) by picking - // the lowest Id per group. For MusicArtist, prefer the entity from a library the user - // can actually access,since the same artist can have a folder in multiple libraries. - // Keep as an IQueryable sub-select so paging is applied AFTER - // ApplyOrder runs the caller's actual sort. + // Collapse rows that share a PresentationUniqueKey (e.g. alternate versions) into one + // representative id per group, then materialize the representative ids once. var masterQuery = TranslateQuery(innerQuery, context, outerQueryFilter); var isMusicArtist = returnType == _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicArtist]; - var representativeIds = isMusicArtist - ? masterQuery + List<Guid> representativeIds; + if (isMusicArtist) + { + // For MusicArtist, prefer the entity from a library the user can actually access. + // Materialize to prevent correlated per-group first-row queries which hurt performance. + var topParentIds = filter.TopParentIds; + representativeIds = masterQuery + .Select(e => new { e.Id, e.PresentationUniqueKey, e.TopParentId }) + .AsEnumerable() .GroupBy(e => e.PresentationUniqueKey) .Select(g => g - .OrderBy(e => filter.TopParentIds.Contains(e.TopParentId ?? Guid.Empty) ? 0 : 1) + .OrderBy(e => topParentIds.Contains(e.TopParentId ?? Guid.Empty) ? 0 : 1) .ThenBy(e => e.Id) .First().Id) - : masterQuery + .ToList(); + } + else + { + representativeIds = masterQuery .GroupBy(e => e.PresentationUniqueKey) - .Select(g => g.Min(e => e.Id)); + .Select(g => g.Min(e => e.Id)) + .ToList(); + } var result = new QueryResult<(BaseItemDto, ItemCounts?)>(); if (filter.EnableTotalRecordCount) { - result.TotalRecordCount = representativeIds.Count(); + result.TotalRecordCount = representativeIds.Count; } var query = ApplyNavigations( - context.BaseItems.AsNoTracking().AsSingleQuery().Where(e => representativeIds.Contains(e.Id)), + context.BaseItems.AsNoTracking().AsSingleQuery().WhereOneOrMany(representativeIds, e => e.Id), filter); query = ApplyOrder(query, filter, context); @@ -311,8 +316,8 @@ public sealed partial class BaseItemRepository var itemIds = itemCountQuery.Select(e => e.Id); // Rewrite query to avoid SelectMany on navigation properties (which requires SQL APPLY, not supported on SQLite) - // Instead, start from ItemValueMaps and join with BaseItems - return context.ItemValuesMap + // Instead, start from ItemValueMaps and join with BaseItems. + var rawCounts = context.ItemValuesMap .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type)) .Where(ivm => itemIds.Contains(ivm.ItemId)) .Join( @@ -322,18 +327,47 @@ public sealed partial class BaseItemRepository (ivm, e) => new { CleanName = ivm.ItemValue.CleanValue, e.Type }) .GroupBy(x => new { x.CleanName, x.Type }) .Select(g => new { g.Key.CleanName, g.Key.Type, Count = g.Count() }) - .GroupBy(x => x.CleanName) - .ToDictionary( - g => g.Key, - g => new ItemCounts + .AsEnumerable(); + + var countsByCleanName = new Dictionary<string, ItemCounts>(); + foreach (var group in rawCounts.GroupBy(x => x.CleanName)) + { + var counts = new ItemCounts(); + foreach (var row in group) + { + if (row.Type == seriesTypeName) + { + counts.SeriesCount += row.Count; + } + else if (row.Type == episodeTypeName) + { + counts.EpisodeCount += row.Count; + } + else if (row.Type == movieTypeName) + { + counts.MovieCount += row.Count; + } + else if (row.Type == musicAlbumTypeName) + { + counts.AlbumCount += row.Count; + } + else if (row.Type == musicArtistTypeName) { - SeriesCount = g.Where(x => x.Type == seriesTypeName).Sum(x => x.Count), - EpisodeCount = g.Where(x => x.Type == episodeTypeName).Sum(x => x.Count), - MovieCount = g.Where(x => x.Type == movieTypeName).Sum(x => x.Count), - AlbumCount = g.Where(x => x.Type == musicAlbumTypeName).Sum(x => x.Count), - ArtistCount = g.Where(x => x.Type == musicArtistTypeName).Sum(x => x.Count), - SongCount = g.Where(x => x.Type == audioTypeName).Sum(x => x.Count), - TrailerCount = g.Where(x => x.Type == trailerTypeName).Sum(x => x.Count), - }); + counts.ArtistCount += row.Count; + } + else if (row.Type == audioTypeName) + { + counts.SongCount += row.Count; + } + else if (row.Type == trailerTypeName) + { + counts.TrailerCount += row.Count; + } + } + + countsByCleanName[group.Key] = counts; + } + + return countsByCleanName; } } diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs index dc16c3b1b3..1ff8d8f863 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.Querying.cs @@ -126,38 +126,54 @@ public sealed partial class BaseItemRepository if (collectionType is CollectionType.movies) { - // Group by PresentationUniqueKey, pick the newest item per group. - var topGroupItems = baseQuery + // Pick, per PresentationUniqueKey, the newest item; return the newest `limit` of those. + // Build up until limit by streaming through results and deduplicating on the fly. + var orderedIds = baseQuery .Where(e => e.PresentationUniqueKey != null) - .GroupBy(e => e.PresentationUniqueKey) - .Select(g => new - { - MaxDate = g.Max(e => e.DateCreated), - FirstId = g.OrderByDescending(e => e.DateCreated).ThenByDescending(e => e.Id).Select(e => e.Id).First() - }) - .OrderByDescending(g => g.MaxDate); + .OrderByDescending(e => e.DateCreated) + .ThenByDescending(e => e.Id) + .Select(e => new { e.Id, e.PresentationUniqueKey }); - var firstIdsQuery = filter.Limit.HasValue - ? topGroupItems.Take(filter.Limit.Value).Select(g => g.FirstId) - : topGroupItems.Select(g => g.FirstId); + // DistinctBy and Take are lazy, so enumeration stops as soon as limit distinct keys are read. + var firstIds = orderedIds + .AsEnumerable() + .DistinctBy(row => row.PresentationUniqueKey) + .Select(row => row.Id) + .Take(limit ?? int.MaxValue) + .ToList(); - return LoadLatestByIds(context, firstIdsQuery, filter); + return LoadLatestByIds(context, firstIds, filter); } - // Albums whose Id is the parent of any track matching the user's filter. - var albumIdsWithMatchingTrack = context.AncestorIds - .Join(baseQuery, ai => ai.ItemId, t => t.Id, (ai, _) => ai.ParentItemId); - var musicAlbumTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.MusicAlbum]!; - var topAlbumsQuery = context.BaseItems.AsNoTracking() - .Where(album => album.Type == musicAlbumTypeName) - .Where(album => albumIdsWithMatchingTrack.Contains(album.Id)) + IQueryable<BaseItemEntity> topAlbumsQuery; + + // When the query is scoped to whole libraries, read the newest albums directly by their own TopParentId. + if (filter.TopParentIds.Length > 0) + { + topAlbumsQuery = context.BaseItems.AsNoTracking() + .Where(album => album.Type == musicAlbumTypeName + && !album.IsVirtualItem + && album.TopParentId.HasValue) + .WhereOneOrMany(filter.TopParentIds, album => album.TopParentId!.Value); + } + else + { + // Fallback (e.g. AncestorIds-scoped callers): albums that are the parent of a matching track. + var albumIdsWithMatchingTrack = context.AncestorIds + .Join(baseQuery, ai => ai.ItemId, t => t.Id, (ai, _) => ai.ParentItemId); + topAlbumsQuery = context.BaseItems.AsNoTracking() + .Where(album => album.Type == musicAlbumTypeName) + .Where(album => albumIdsWithMatchingTrack.Contains(album.Id)); + } + + var orderedAlbums = topAlbumsQuery .OrderByDescending(album => album.DateCreated) .ThenByDescending(album => album.Id); - var albumIdsQuery = filter.Limit.HasValue - ? topAlbumsQuery.Take(filter.Limit.Value).Select(a => a.Id) - : topAlbumsQuery.Select(a => a.Id); + var albumIdsQuery = limit.HasValue + ? orderedAlbums.Take(limit.Value).Select(a => a.Id) + : orderedAlbums.Select(a => a.Id); return LoadLatestByIds(context, albumIdsQuery, filter); } @@ -181,6 +197,29 @@ public sealed partial class BaseItemRepository .ToArray()!; } + private IReadOnlyList<BaseItemDto> LoadLatestByIds( + JellyfinDbContext context, + List<Guid> ids, + InternalItemsQuery filter) + { + if (ids.Count == 0) + { + return []; + } + + var itemsQuery = ApplyNavigations( + context.BaseItems.AsNoTracking().WhereOneOrMany(ids, e => e.Id), + filter); + + return itemsQuery + .OrderByDescending(e => e.DateCreated) + .ThenByDescending(e => e.Id) + .AsEnumerable() + .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)) + .Where(dto => dto != null) + .ToArray()!; + } + /// <summary> /// Gets the latest TV show items with smart Season/Series container selection. /// </summary> diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs index f19df6259e..47f8a40b9c 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs @@ -434,20 +434,34 @@ public sealed partial class BaseItemRepository if (filter.IsLiked.HasValue) { - var isLiked = filter.IsLiked.Value; - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue) == isLiked); - } + var likedIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue) + .Select(ud => ud.ItemId); - if (filter.IsFavoriteOrLiked.HasValue) - { - var isFavoriteOrLiked = filter.IsFavoriteOrLiked.Value; - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavoriteOrLiked); + baseQuery = filter.IsLiked.Value + ? baseQuery.Where(e => likedIds.Contains(e.Id)) + : baseQuery.Where(e => !likedIds.Contains(e.Id)); } - if (filter.IsFavorite.HasValue) + if (filter.IsFavoriteOrLiked.HasValue || filter.IsFavorite.HasValue) { - var isFavorite = filter.IsFavorite.Value; - baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavorite); + var favoriteIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) + .Select(ud => ud.ItemId); + + if (filter.IsFavoriteOrLiked.HasValue) + { + baseQuery = filter.IsFavoriteOrLiked.Value + ? baseQuery.Where(e => favoriteIds.Contains(e.Id)) + : baseQuery.Where(e => !favoriteIds.Contains(e.Id)); + } + + if (filter.IsFavorite.HasValue) + { + baseQuery = filter.IsFavorite.Value + ? baseQuery.Where(e => favoriteIds.Contains(e.Id)) + : baseQuery.Where(e => !favoriteIds.Contains(e.Id)); + } } if (filter.IsPlayed.HasValue) @@ -560,16 +574,19 @@ public sealed partial class BaseItemRepository // Only in-progress siblings can eliminate a candidate: a version without progress has a NULL max LastPlayedDate, // which is never greater and never ties. Restricting the sibling scan to the in-progress set keeps this bounded by // the user's Continue Watching count instead of forcing a full BaseItems scan (COALESCE keys are non-indexable) per row. - baseQuery = baseQuery.Where(e => e.Type == seriesTypeName || !context.BaseItems - .Where(s => s.Id != e.Id - && inProgressIds.Contains(s.Id) - && (s.PrimaryVersionId ?? s.Id) == (e.PrimaryVersionId ?? e.Id)) - .Any(s => - inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) - > inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) - || (inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) - == inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) - && s.Id.CompareTo(e.Id) < 0))); + // Items in no version group at all have no sibling that could eliminate them, so short-circuit the scan for those. + baseQuery = baseQuery.Where(e => e.Type == seriesTypeName + || (e.PrimaryVersionId == null && !context.BaseItems.Any(a => a.PrimaryVersionId == e.Id)) + || !context.BaseItems + .Where(s => s.Id != e.Id + && inProgressIds.Contains(s.Id) + && (s.PrimaryVersionId ?? s.Id) == (e.PrimaryVersionId ?? e.Id)) + .Any(s => + inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) + > inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) + || (inProgress.Where(su => su.ItemId == s.Id).Max(su => su.LastPlayedDate) + == inProgress.Where(eu => eu.ItemId == e.Id).Max(eu => eu.LastPlayedDate) + && s.Id.CompareTo(e.Id) < 0))); } else { diff --git a/Jellyfin.Server.Implementations/Item/NextUpService.cs b/Jellyfin.Server.Implementations/Item/NextUpService.cs index 725b4cfaac..f478daef23 100644 --- a/Jellyfin.Server.Implementations/Item/NextUpService.cs +++ b/Jellyfin.Server.Implementations/Item/NextUpService.cs @@ -98,7 +98,7 @@ public class NextUpService : INextUpService .Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)); lastWatchedBase = _queryHelpers.ApplyAccessFiltering(context, lastWatchedBase, filter); - // Use lightweight projection + client-side grouping to avoid correlated scalar subquery + // Use lightweight projection + client-side dedup to avoid the correlated scalar subquery // per group that EF generates for GroupBy+OrderByDescending+FirstOrDefault. var allPlayedLite = lastWatchedBase .Select(e => new @@ -110,15 +110,11 @@ public class NextUpService : INextUpService }) .ToList(); - var lastWatchedInfo = new Dictionary<string, Guid>(); - foreach (var group in allPlayedLite.GroupBy(e => e.SeriesPresentationUniqueKey)) - { - var lastWatched = group - .OrderByDescending(e => e.ParentIndexNumber) - .ThenByDescending(e => e.IndexNumber) - .First(); - lastWatchedInfo[group.Key!] = lastWatched.Id; - } + var lastWatchedInfo = allPlayedLite + .OrderByDescending(e => e.ParentIndexNumber) + .ThenByDescending(e => e.IndexNumber) + .DistinctBy(e => e.SeriesPresentationUniqueKey) + .ToDictionary(e => e.SeriesPresentationUniqueKey!, e => e.Id); Dictionary<string, Guid> lastWatchedByDateInfo = new(); if (includeWatchedForRewatching) @@ -144,11 +140,10 @@ public class NextUpService : INextUpService (e, ud) => new { EpisodeId = e.Id, e.SeriesPresentationUniqueKey, ud.LastPlayedDate }) .ToList(); - foreach (var group in playedWithDates.GroupBy(x => x.SeriesPresentationUniqueKey)) - { - var mostRecent = group.OrderByDescending(x => x.LastPlayedDate).First(); - lastWatchedByDateInfo[group.Key!] = mostRecent.EpisodeId; - } + lastWatchedByDateInfo = playedWithDates + .OrderByDescending(x => x.LastPlayedDate) + .DistinctBy(x => x.SeriesPresentationUniqueKey) + .ToDictionary(x => x.SeriesPresentationUniqueKey!, x => x.EpisodeId); } var allLastWatchedIds = lastWatchedInfo.Values diff --git a/Jellyfin.Server.Implementations/Item/OrderMapper.cs b/Jellyfin.Server.Implementations/Item/OrderMapper.cs index aac85d0131..25ad81ec6c 100644 --- a/Jellyfin.Server.Implementations/Item/OrderMapper.cs +++ b/Jellyfin.Server.Implementations/Item/OrderMapper.cs @@ -29,19 +29,30 @@ public static class OrderMapper /// <returns>Func to be executed later for sorting query.</returns> public static Expression<Func<BaseItemEntity, object?>> MapOrderByField(ItemSortBy sortBy, InternalItemsQuery query, JellyfinDbContext jellyfinDbContext) { + if (sortBy == ItemSortBy.DatePlayed) + { + // An item's played date is the newest of its own progress and that of its alternate versions, + // which track progress under their own ids. Matching both in one predicate ORs them together, + // which no index can serve: the user's whole UserData table gets scanned per sorted row. + // Two indexed lookups combined by MAX cost a seek each instead. + var userData = query.User is null + ? jellyfinDbContext.UserData + : jellyfinDbContext.UserData.Where(w => w.UserId == query.User.Id); + + return e => userData + .Where(w => w.ItemId == e.Id) + .Select(w => w.LastPlayedDate) + .Concat(userData + .Where(w => w.Item!.PrimaryVersionId == e.Id) + .Select(w => w.LastPlayedDate)) + .Max(); + } + return (sortBy, query.User) switch { (ItemSortBy.AirTime, _) => e => e.SortName, (ItemSortBy.Runtime, _) => e => e.RunTimeTicks, (ItemSortBy.Random, _) => e => EF.Functions.Random(), - (ItemSortBy.DatePlayed, not null) => e => - jellyfinDbContext.UserData - .Where(w => w.UserId == query.User.Id && (w.ItemId == e.Id || w.Item!.PrimaryVersionId == e.Id)) - .Max(f => f.LastPlayedDate), - (ItemSortBy.DatePlayed, null) => e => - jellyfinDbContext.UserData - .Where(w => w.ItemId == e.Id || w.Item!.PrimaryVersionId == e.Id) - .Max(f => f.LastPlayedDate), (ItemSortBy.PlayCount, _) => e => e.UserData!.Where(f => f.UserId.Equals(query.User!.Id)).OrderBy(f => f.CustomDataKey).FirstOrDefault()!.PlayCount, (ItemSortBy.IsFavoriteOrLiked, _) => e => e.UserData!.Where(f => f.UserId.Equals(query.User!.Id)).OrderBy(f => f.CustomDataKey).Select(f => (bool?)f.IsFavorite).FirstOrDefault() ?? false, (ItemSortBy.IsFolder, _) => e => e.IsFolder, diff --git a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs index eb87b525fe..9611c5c13a 100644 --- a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs +++ b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs @@ -79,7 +79,11 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I public IReadOnlyList<string> GetPeopleNames(InternalPeopleQuery filter) { using var context = _dbProvider.CreateDbContext(); - var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter).Select(e => e.Name).Distinct(); + + IQueryable<string> dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter) + .Select(e => e.Name) + .Distinct() + .OrderBy(e => e); if (filter.StartIndex.HasValue && filter.StartIndex > 0) { @@ -88,7 +92,7 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I if (filter.Limit > 0) { - dbQuery = dbQuery.OrderBy(e => e).Take(filter.Limit); + dbQuery = dbQuery.Take(filter.Limit); } return dbQuery.ToArray(); diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs index 1af7460540..fec37ce723 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs @@ -70,15 +70,24 @@ public static class JellyfinQueryHelperExtensions bool invert = false) { var itemFilter = OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, f => f.Id); - var typeFilter = OneOrManyExpressionBuilder<ItemValue, ItemValueType>(itemValueTypes, iv => iv.Type); - - return baseQuery.Where(item => - context.ItemValues - .Where(typeFilter) - .Join(context.ItemValuesMap, e => e.ItemValueId, e => e.ItemValueId, (itemVal, map) => new { itemVal, map }) - .Any(val => - context.BaseItems.Where(itemFilter).Any(e => e.CleanName == val.itemVal.CleanValue) - && val.map.ItemId == item.Id) == EF.Constant(!invert)); + var typeFilter = OneOrManyExpressionBuilder<ItemValueMap, ItemValueType>(itemValueTypes, m => m.ItemValue.Type); + + // Flat sub-selects + Contains instead of a nested correlated .Any(...Any(...)). + var referencedCleanValues = context.BaseItems + .Where(itemFilter) + .Select(e => e.CleanName); + + var matchingItemIds = context.ItemValuesMap + .Where(typeFilter) + .Where(m => referencedCleanValues.Contains(m.ItemValue.CleanValue)) + .Select(m => m.ItemId); + + if (invert) + { + return baseQuery.Where(e => !matchingItemIds.Contains(e.Id)); + } + + return baseQuery.Where(e => matchingItemIds.Contains(e.Id)); } /// <summary> @@ -102,13 +111,21 @@ public static class JellyfinQueryHelperExtensions var itemFilter = OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, f => f.Id); - return item => - context.ItemValues - .Join(context.ItemValuesMap, e => e.ItemValueId, e => e.ItemValueId, (item, map) => new { item, map }) - .Any(val => - val.item.Type == itemValueType - && context.BaseItems.Where(itemFilter).Any(e => e.CleanName == val.item.CleanValue) - && val.map.ItemId == item.Id) == EF.Constant(!invert); + // Flat sub-selects + Contains instead of a nested correlated .Any(...Any(...)). + var referencedCleanValues = context.BaseItems + .Where(itemFilter) + .Select(e => e.CleanName); + + var matchingItemIds = context.ItemValuesMap + .Where(m => m.ItemValue.Type == itemValueType && referencedCleanValues.Contains(m.ItemValue.CleanValue)) + .Select(m => m.ItemId); + + if (invert) + { + return item => !matchingItemIds.Contains(item.Id); + } + + return item => matchingItemIds.Contains(item.Id); } /// <summary> @@ -224,14 +241,14 @@ public static class JellyfinQueryHelperExtensions var containsMethodInfo = _containsQueryCache.GetOrAdd(typeof(TProperty), static (key) => _containsMethodGenericCache.MakeGenericMethod(key)); - // Threshold picked from microbenchmarks on SQLite: inline IN(const,...) beats a - // parameterized array lookup by ~5-10% up to ~32 elements. - if (oneOf.Count <= 32) - { - return Expression.Lambda<Func<TEntity, bool>>(Expression.Call(null, containsMethodInfo, Expression.Constant(oneOf), property.Body), parameter); - } - - return Expression.Lambda<Func<TEntity, bool>>(Expression.Call(null, containsMethodInfo, Expression.Call(null, _efParameterInstruction.MakeGenericMethod(oneOf.GetType()), Expression.Constant(oneOf)), property.Body), parameter); + // Always wrap the collection in EF.Parameter so EF Core caches a single compiled plan and reuses it across calls. + return Expression.Lambda<Func<TEntity, bool>>( + Expression.Call( + null, + containsMethodInfo, + Expression.Call(null, _efParameterInstruction.MakeGenericMethod(oneOf.GetType()), Expression.Constant(oneOf)), + property.Body), + parameter); } internal static class ParameterReplacer diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs index 8556fb7bb3..ee36be035b 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs @@ -61,6 +61,11 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity> builder.HasIndex(e => new { e.TopParentId, e.MediaType, e.IsVirtualItem, e.DateCreated }); // resume builder.HasIndex(e => new { e.MediaType, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey }); + // alternate versions of an item, e.g. resolving the played date of a version onto its primary. + // Filtered: almost no item has a primary version, and an index covering those rows too would tempt + // the planner into serving "PrimaryVersionId IS NULL" - true for the whole library - out of it. + builder.HasIndex(e => e.PrimaryVersionId) + .HasFilter("\"PrimaryVersionId\" IS NOT NULL"); // sorted library queries (e.g., Series sorted by SortName) builder.HasIndex(e => new { e.Type, e.TopParentId, e.SortName }); // NextUp: per-series episode ordering (index seek + range scan on season/episode) diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.Designer.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.Designer.cs new file mode 100644 index 0000000000..bc23ad7faa --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.Designer.cs @@ -0,0 +1,1813 @@ +// <auto-generated /> +using System; +using Jellyfin.Database.Implementations; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; + +#nullable disable + +namespace Jellyfin.Server.Implementations.Migrations +{ + [DbContext(typeof(JellyfinDbContext))] + [Migration("20260724185102_AddPrimaryVersionIdIndex")] + partial class AddPrimaryVersionIdIndex + { + /// <inheritdoc /> + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder.HasAnnotation("ProductVersion", "10.0.10"); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("DayOfWeek") + .HasColumnType("INTEGER"); + + b.Property<double>("EndHour") + .HasColumnType("REAL"); + + b.Property<double>("StartHour") + .HasColumnType("REAL"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId"); + + b.ToTable("AccessSchedules"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ActivityLog", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<DateTime>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<string>("ItemId") + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property<int>("LogSeverity") + .HasColumnType("INTEGER"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<string>("Overview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<string>("ShortOverview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<string>("Type") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("DateCreated"); + + b.ToTable("ActivityLogs"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AncestorId", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<Guid>("ParentItemId") + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "ParentItemId"); + + b.HasIndex("ParentItemId"); + + b.ToTable("AncestorIds"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AttachmentStreamInfo", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("Index") + .HasColumnType("INTEGER"); + + b.Property<string>("Codec") + .HasColumnType("TEXT"); + + b.Property<string>("CodecTag") + .HasColumnType("TEXT"); + + b.Property<string>("Comment") + .HasColumnType("TEXT"); + + b.Property<string>("Filename") + .HasColumnType("TEXT"); + + b.Property<string>("MimeType") + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "Index"); + + b.ToTable("AttachmentStreamInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("Album") + .HasColumnType("TEXT"); + + b.Property<string>("AlbumArtists") + .HasColumnType("TEXT"); + + b.Property<string>("Artists") + .HasColumnType("TEXT"); + + b.Property<int?>("Audio") + .HasColumnType("INTEGER"); + + b.Property<Guid?>("ChannelId") + .HasColumnType("TEXT"); + + b.Property<string>("CleanName") + .HasColumnType("TEXT"); + + b.Property<float?>("CommunityRating") + .HasColumnType("REAL"); + + b.Property<float?>("CriticRating") + .HasColumnType("REAL"); + + b.Property<string>("CustomRating") + .HasColumnType("TEXT"); + + b.Property<string>("Data") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateLastMediaAdded") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateLastRefreshed") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateLastSaved") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("DateModified") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("EndDate") + .HasColumnType("TEXT"); + + b.Property<string>("EpisodeTitle") + .HasColumnType("TEXT"); + + b.Property<string>("ExternalId") + .HasColumnType("TEXT"); + + b.Property<string>("ExternalSeriesId") + .HasColumnType("TEXT"); + + b.Property<string>("ExternalServiceId") + .HasColumnType("TEXT"); + + b.Property<int?>("ExtraType") + .HasColumnType("INTEGER"); + + b.Property<string>("ForcedSortName") + .HasColumnType("TEXT"); + + b.Property<string>("Genres") + .HasColumnType("TEXT"); + + b.Property<int?>("Height") + .HasColumnType("INTEGER"); + + b.Property<int?>("IndexNumber") + .HasColumnType("INTEGER"); + + b.Property<int?>("InheritedParentalRatingSubValue") + .HasColumnType("INTEGER"); + + b.Property<int?>("InheritedParentalRatingValue") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsFolder") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsInMixedFolder") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsLocked") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsMovie") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsRepeat") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsSeries") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsVirtualItem") + .HasColumnType("INTEGER"); + + b.Property<float?>("LUFS") + .HasColumnType("REAL"); + + b.Property<string>("MediaType") + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .HasColumnType("TEXT"); + + b.Property<float?>("NormalizationGain") + .HasColumnType("REAL"); + + b.Property<string>("OfficialRating") + .HasColumnType("TEXT"); + + b.Property<string>("OriginalLanguage") + .HasColumnType("TEXT"); + + b.Property<string>("OriginalTitle") + .HasColumnType("TEXT"); + + b.Property<string>("Overview") + .HasColumnType("TEXT"); + + b.Property<Guid?>("OwnerId") + .HasColumnType("TEXT"); + + b.Property<Guid?>("ParentId") + .HasColumnType("TEXT"); + + b.Property<int?>("ParentIndexNumber") + .HasColumnType("INTEGER"); + + b.Property<string>("Path") + .HasColumnType("TEXT"); + + b.Property<string>("PreferredMetadataCountryCode") + .HasColumnType("TEXT"); + + b.Property<string>("PreferredMetadataLanguage") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("PremiereDate") + .HasColumnType("TEXT"); + + b.Property<string>("PresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property<Guid?>("PrimaryVersionId") + .HasColumnType("TEXT"); + + b.Property<string>("ProductionLocations") + .HasColumnType("TEXT"); + + b.Property<int?>("ProductionYear") + .HasColumnType("INTEGER"); + + b.Property<long?>("RunTimeTicks") + .HasColumnType("INTEGER"); + + b.Property<Guid?>("SeasonId") + .HasColumnType("TEXT"); + + b.Property<string>("SeasonName") + .HasColumnType("TEXT"); + + b.Property<Guid?>("SeriesId") + .HasColumnType("TEXT"); + + b.Property<string>("SeriesName") + .HasColumnType("TEXT"); + + b.Property<string>("SeriesPresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property<string>("ShowId") + .HasColumnType("TEXT"); + + b.Property<long?>("Size") + .HasColumnType("INTEGER"); + + b.Property<string>("SortName") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("StartDate") + .HasColumnType("TEXT"); + + b.Property<string>("Studios") + .HasColumnType("TEXT"); + + b.Property<string>("Tagline") + .HasColumnType("TEXT"); + + b.Property<string>("Tags") + .HasColumnType("TEXT"); + + b.Property<Guid?>("TopParentId") + .HasColumnType("TEXT"); + + b.Property<int?>("TotalBitrate") + .HasColumnType("INTEGER"); + + b.Property<string>("Type") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<string>("UnratedType") + .HasColumnType("TEXT"); + + b.Property<int?>("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("Name"); + + b.HasIndex("OwnerId"); + + b.HasIndex("ParentId"); + + b.HasIndex("Path"); + + b.HasIndex("PresentationUniqueKey"); + + b.HasIndex("PrimaryVersionId") + .HasFilter("\"PrimaryVersionId\" IS NOT NULL"); + + b.HasIndex("SeasonId"); + + b.HasIndex("SeriesId"); + + b.HasIndex("SeriesName"); + + b.HasIndex("ExtraType", "OwnerId"); + + b.HasIndex("TopParentId", "Id"); + + b.HasIndex("Type", "CleanName"); + + b.HasIndex("TopParentId", "Type", "IsVirtualItem") + .HasFilter("\"PrimaryVersionId\" IS NULL AND (\"OwnerId\" IS NULL OR \"ExtraType\" IS NOT NULL)"); + + b.HasIndex("Type", "TopParentId", "Id"); + + b.HasIndex("Type", "TopParentId", "PresentationUniqueKey"); + + b.HasIndex("Type", "TopParentId", "SortName"); + + b.HasIndex("Type", "TopParentId", "StartDate"); + + b.HasIndex("MediaType", "TopParentId", "IsVirtualItem", "PresentationUniqueKey"); + + b.HasIndex("TopParentId", "IsFolder", "IsVirtualItem", "DateCreated"); + + b.HasIndex("TopParentId", "MediaType", "IsVirtualItem", "DateCreated"); + + b.HasIndex("TopParentId", "Type", "IsVirtualItem", "DateCreated"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "IsFolder", "IsVirtualItem"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "ParentIndexNumber", "IndexNumber"); + + b.HasIndex("Type", "SeriesPresentationUniqueKey", "PresentationUniqueKey", "SortName"); + + b.HasIndex("IsFolder", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated"); + + b.HasIndex("Type", "TopParentId", "IsVirtualItem", "PresentationUniqueKey", "DateCreated"); + + b.ToTable("BaseItems"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + + b.HasData( + new + { + Id = new Guid("00000000-0000-0000-0000-000000000001"), + IsFolder = false, + IsInMixedFolder = false, + IsLocked = false, + IsMovie = false, + IsRepeat = false, + IsSeries = false, + IsVirtualItem = false, + Name = "This is a placeholder item for UserData that has been detached from its original item", + Type = "PLACEHOLDER" + }); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemImageInfo", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<byte[]>("Blurhash") + .HasColumnType("BLOB"); + + b.Property<DateTime?>("DateModified") + .HasColumnType("TEXT"); + + b.Property<int>("Height") + .HasColumnType("INTEGER"); + + b.Property<int>("ImageType") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("Path") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<int>("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("ItemId", "ImageType"); + + b.ToTable("BaseItemImageInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemMetadataField", b => + { + b.Property<int>("Id") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("Id", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("BaseItemMetadataFields"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemProvider", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("ProviderId") + .HasColumnType("TEXT"); + + b.Property<string>("ProviderValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "ProviderId"); + + b.HasIndex("ProviderId", "ItemId", "ProviderValue"); + + b.ToTable("BaseItemProviders"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemTrailerType", b => + { + b.Property<int>("Id") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("Id", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("BaseItemTrailerTypes"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Chapter", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("ChapterIndex") + .HasColumnType("INTEGER"); + + b.Property<DateTime?>("ImageDateModified") + .HasColumnType("TEXT"); + + b.Property<string>("ImagePath") + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .HasColumnType("TEXT"); + + b.Property<long>("StartPositionTicks") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "ChapterIndex"); + + b.ToTable("Chapters"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.CustomItemDisplayPreferences", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("Key") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<string>("Value") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "ItemId", "Client", "Key") + .IsUnique(); + + b.ToTable("CustomItemDisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("ChromecastVersion") + .HasColumnType("INTEGER"); + + b.Property<string>("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<string>("DashboardTheme") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<bool>("EnableNextVideoInfoOverlay") + .HasColumnType("INTEGER"); + + b.Property<int?>("IndexBy") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("ScrollDirection") + .HasColumnType("INTEGER"); + + b.Property<bool>("ShowBackdrop") + .HasColumnType("INTEGER"); + + b.Property<bool>("ShowSidebar") + .HasColumnType("INTEGER"); + + b.Property<int>("SkipBackwardLength") + .HasColumnType("INTEGER"); + + b.Property<int>("SkipForwardLength") + .HasColumnType("INTEGER"); + + b.Property<string>("TvHome") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "ItemId", "Client") + .IsUnique(); + + b.ToTable("DisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.HomeSection", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("DisplayPreferencesId") + .HasColumnType("INTEGER"); + + b.Property<int>("Order") + .HasColumnType("INTEGER"); + + b.Property<int>("Type") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("DisplayPreferencesId"); + + b.ToTable("HomeSection"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ImageInfo", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<DateTime>("LastModified") + .HasColumnType("TEXT"); + + b.Property<string>("Path") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property<Guid?>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId") + .IsUnique(); + + b.ToTable("ImageInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemDisplayPreferences", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<int?>("IndexBy") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<bool>("RememberIndexing") + .HasColumnType("INTEGER"); + + b.Property<bool>("RememberSorting") + .HasColumnType("INTEGER"); + + b.Property<string>("SortBy") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property<int>("SortOrder") + .HasColumnType("INTEGER"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<int>("ViewType") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("UserId"); + + b.ToTable("ItemDisplayPreferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValue", b => + { + b.Property<Guid>("ItemValueId") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("CleanValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<int>("Type") + .HasColumnType("INTEGER"); + + b.Property<string>("Value") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("ItemValueId"); + + b.HasIndex("Type", "CleanValue"); + + b.HasIndex("Type", "Value") + .IsUnique(); + + b.ToTable("ItemValues"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValueMap", b => + { + b.Property<Guid>("ItemValueId") + .HasColumnType("TEXT"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.HasKey("ItemValueId", "ItemId"); + + b.HasIndex("ItemId"); + + b.ToTable("ItemValuesMap"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.KeyframeData", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.PrimitiveCollection<string>("KeyframeTicks") + .HasColumnType("TEXT"); + + b.Property<long>("TotalDuration") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId"); + + b.ToTable("KeyframeData"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.LinkedChildEntity", b => + { + b.Property<Guid>("ParentId") + .HasColumnType("TEXT"); + + b.Property<Guid>("ChildId") + .HasColumnType("TEXT"); + + b.Property<int>("ChildType") + .HasColumnType("INTEGER"); + + b.Property<int?>("SortOrder") + .HasColumnType("INTEGER"); + + b.HasKey("ParentId", "ChildId"); + + b.HasIndex("ChildId", "ChildType"); + + b.HasIndex("ParentId", "ChildType"); + + b.HasIndex("ParentId", "SortOrder"); + + b.ToTable("LinkedChildren", (string)null); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaSegment", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<long>("EndTicks") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<string>("SegmentProviderId") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<long>("StartTicks") + .HasColumnType("INTEGER"); + + b.Property<int>("Type") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.ToTable("MediaSegments"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaStreamInfo", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("StreamIndex") + .HasColumnType("INTEGER"); + + b.Property<string>("AspectRatio") + .HasColumnType("TEXT"); + + b.Property<float?>("AverageFrameRate") + .HasColumnType("REAL"); + + b.Property<int?>("BitDepth") + .HasColumnType("INTEGER"); + + b.Property<int?>("BitRate") + .HasColumnType("INTEGER"); + + b.Property<int?>("BlPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<string>("ChannelLayout") + .HasColumnType("TEXT"); + + b.Property<int?>("Channels") + .HasColumnType("INTEGER"); + + b.Property<string>("Codec") + .HasColumnType("TEXT"); + + b.Property<string>("CodecTag") + .HasColumnType("TEXT"); + + b.Property<string>("CodecTimeBase") + .HasColumnType("TEXT"); + + b.Property<string>("ColorPrimaries") + .HasColumnType("TEXT"); + + b.Property<string>("ColorSpace") + .HasColumnType("TEXT"); + + b.Property<string>("ColorTransfer") + .HasColumnType("TEXT"); + + b.Property<string>("Comment") + .HasColumnType("TEXT"); + + b.Property<int?>("DvBlSignalCompatibilityId") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvLevel") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvProfile") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvVersionMajor") + .HasColumnType("INTEGER"); + + b.Property<int?>("DvVersionMinor") + .HasColumnType("INTEGER"); + + b.Property<int?>("ElPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<bool?>("Hdr10PlusPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<int?>("Height") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsAnamorphic") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsAvc") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsDefault") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsExternal") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsForced") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsHearingImpaired") + .HasColumnType("INTEGER"); + + b.Property<bool?>("IsInterlaced") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsOriginal") + .HasColumnType("INTEGER"); + + b.Property<string>("KeyFrames") + .HasColumnType("TEXT"); + + b.Property<string>("Language") + .HasColumnType("TEXT"); + + b.Property<float?>("Level") + .HasColumnType("REAL"); + + b.Property<string>("NalLengthSize") + .HasColumnType("TEXT"); + + b.Property<string>("Path") + .HasColumnType("TEXT"); + + b.Property<string>("PixelFormat") + .HasColumnType("TEXT"); + + b.Property<string>("Profile") + .HasColumnType("TEXT"); + + b.Property<float?>("RealFrameRate") + .HasColumnType("REAL"); + + b.Property<int?>("RefFrames") + .HasColumnType("INTEGER"); + + b.Property<int?>("Rotation") + .HasColumnType("INTEGER"); + + b.Property<int?>("RpuPresentFlag") + .HasColumnType("INTEGER"); + + b.Property<int?>("SampleRate") + .HasColumnType("INTEGER"); + + b.Property<int>("StreamType") + .HasColumnType("INTEGER"); + + b.Property<string>("TimeBase") + .HasColumnType("TEXT"); + + b.Property<string>("Title") + .HasColumnType("TEXT"); + + b.Property<int?>("Width") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "StreamIndex"); + + b.ToTable("MediaStreamInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.People", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<string>("PersonType") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("Name"); + + b.ToTable("Peoples"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.PeopleBaseItemMap", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<Guid>("PeopleId") + .HasColumnType("TEXT"); + + b.Property<string>("Role") + .HasColumnType("TEXT"); + + b.Property<int?>("ListOrder") + .HasColumnType("INTEGER"); + + b.Property<int?>("SortOrder") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "PeopleId", "Role"); + + b.HasIndex("PeopleId"); + + b.HasIndex("ItemId", "ListOrder"); + + b.HasIndex("ItemId", "SortOrder"); + + b.ToTable("PeopleBaseItemMap"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Permission", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("Kind") + .HasColumnType("INTEGER"); + + b.Property<Guid?>("Permission_Permissions_Guid") + .HasColumnType("TEXT"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<Guid?>("UserId") + .HasColumnType("TEXT"); + + b.Property<bool>("Value") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "Kind") + .IsUnique() + .HasFilter("[UserId] IS NOT NULL"); + + b.ToTable("Permissions"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Preference", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<int>("Kind") + .HasColumnType("INTEGER"); + + b.Property<Guid?>("Preference_Preferences_Guid") + .HasColumnType("TEXT"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<Guid?>("UserId") + .HasColumnType("TEXT"); + + b.Property<string>("Value") + .IsRequired() + .HasMaxLength(65535) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("UserId", "Kind") + .IsUnique() + .HasFilter("[UserId] IS NOT NULL"); + + b.ToTable("Preferences"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.ApiKey", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("AccessToken") + .IsUnique(); + + b.ToTable("ApiKeys"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.Device", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property<string>("AppName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property<string>("AppVersion") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateCreated") + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property<DateTime>("DateModified") + .HasColumnType("TEXT"); + + b.Property<string>("DeviceId") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property<string>("DeviceName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property<bool>("IsActive") + .HasColumnType("INTEGER"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("AccessToken", "DateLastActivity"); + + b.HasIndex("DeviceId", "DateLastActivity"); + + b.HasIndex("UserId", "DeviceId"); + + b.ToTable("Devices"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.DeviceOptions", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property<string>("CustomName") + .HasColumnType("TEXT"); + + b.Property<string>("DeviceId") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("DeviceId") + .IsUnique(); + + b.ToTable("DeviceOptions"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.TrickplayInfo", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<int>("Width") + .HasColumnType("INTEGER"); + + b.Property<int>("Bandwidth") + .HasColumnType("INTEGER"); + + b.Property<int>("Height") + .HasColumnType("INTEGER"); + + b.Property<int>("Interval") + .HasColumnType("INTEGER"); + + b.Property<int>("ThumbnailCount") + .HasColumnType("INTEGER"); + + b.Property<int>("TileHeight") + .HasColumnType("INTEGER"); + + b.Property<int>("TileWidth") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "Width"); + + b.ToTable("TrickplayInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.User", b => + { + b.Property<Guid>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property<string>("AudioLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<string>("AuthenticationProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<string>("CastReceiverId") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property<bool>("DisplayCollectionsView") + .HasColumnType("INTEGER"); + + b.Property<bool>("DisplayMissingEpisodes") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableAutoLogin") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableLocalPassword") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableNextEpisodeAutoPlay") + .HasColumnType("INTEGER"); + + b.Property<bool>("EnableUserPreferenceAccess") + .HasColumnType("INTEGER"); + + b.Property<bool>("HidePlayedInLatest") + .HasColumnType("INTEGER"); + + b.Property<long>("InternalId") + .HasColumnType("INTEGER"); + + b.Property<int>("InvalidLoginAttemptCount") + .HasColumnType("INTEGER"); + + b.Property<DateTime?>("LastActivityDate") + .HasColumnType("TEXT"); + + b.Property<DateTime?>("LastLoginDate") + .HasColumnType("TEXT"); + + b.Property<int?>("LoginAttemptsBeforeLockout") + .HasColumnType("INTEGER"); + + b.Property<int>("MaxActiveSessions") + .HasColumnType("INTEGER"); + + b.Property<int?>("MaxParentalRatingScore") + .HasColumnType("INTEGER"); + + b.Property<int?>("MaxParentalRatingSubScore") + .HasColumnType("INTEGER"); + + b.Property<bool>("MustUpdatePassword") + .HasColumnType("INTEGER"); + + b.Property<string>("NormalizedUsername") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<string>("Password") + .HasMaxLength(65535) + .HasColumnType("TEXT"); + + b.Property<string>("PasswordResetProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<bool>("PlayDefaultAudioTrack") + .HasColumnType("INTEGER"); + + b.Property<bool>("RememberAudioSelections") + .HasColumnType("INTEGER"); + + b.Property<bool>("RememberSubtitleSelections") + .HasColumnType("INTEGER"); + + b.Property<int?>("RemoteClientBitrateLimit") + .HasColumnType("INTEGER"); + + b.Property<uint>("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property<string>("SubtitleLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property<int>("SubtitleMode") + .HasColumnType("INTEGER"); + + b.Property<int>("SyncPlayAccess") + .HasColumnType("INTEGER"); + + b.Property<string>("Username") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("NormalizedUsername") + .IsUnique(); + + b.HasIndex("Username") + .IsUnique(); + + b.ToTable("Users"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.UserData", b => + { + b.Property<Guid>("ItemId") + .HasColumnType("TEXT"); + + b.Property<Guid>("UserId") + .HasColumnType("TEXT"); + + b.Property<string>("CustomDataKey") + .HasColumnType("TEXT"); + + b.Property<int?>("AudioStreamIndex") + .HasColumnType("INTEGER"); + + b.Property<bool>("IsFavorite") + .HasColumnType("INTEGER"); + + b.Property<DateTime?>("LastPlayedDate") + .HasColumnType("TEXT"); + + b.Property<bool?>("Likes") + .HasColumnType("INTEGER"); + + b.Property<int>("PlayCount") + .HasColumnType("INTEGER"); + + b.Property<long>("PlaybackPositionTicks") + .HasColumnType("INTEGER"); + + b.Property<bool>("Played") + .HasColumnType("INTEGER"); + + b.Property<double?>("Rating") + .HasColumnType("REAL"); + + b.Property<DateTime?>("RetentionDate") + .HasColumnType("TEXT"); + + b.Property<int?>("SubtitleStreamIndex") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "UserId", "CustomDataKey"); + + b.HasIndex("ItemId", "UserId", "IsFavorite"); + + b.HasIndex("ItemId", "UserId", "LastPlayedDate"); + + b.HasIndex("ItemId", "UserId", "PlaybackPositionTicks"); + + b.HasIndex("ItemId", "UserId", "Played"); + + b.HasIndex("UserId", "IsFavorite", "ItemId"); + + b.HasIndex("UserId", "ItemId", "LastPlayedDate"); + + b.HasIndex("UserId", "Played", "ItemId"); + + b.ToTable("UserData"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("AccessSchedules") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AncestorId", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Parents") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "ParentItem") + .WithMany("Children") + .HasForeignKey("ParentItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("ParentItem"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AttachmentStreamInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany() + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Owner") + .WithMany("Extras") + .HasForeignKey("OwnerId") + .OnDelete(DeleteBehavior.NoAction); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "DirectParent") + .WithMany("DirectChildren") + .HasForeignKey("ParentId") + .OnDelete(DeleteBehavior.Cascade); + + b.Navigation("DirectParent"); + + b.Navigation("Owner"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemImageInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Images") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemMetadataField", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("LockedFields") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemProvider", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Provider") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemTrailerType", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("TrailerTypes") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Chapter", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Chapters") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("DisplayPreferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.HomeSection", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.DisplayPreferences", null) + .WithMany("HomeSections") + .HasForeignKey("DisplayPreferencesId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ImageInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithOne("ProfileImage") + .HasForeignKey("Jellyfin.Database.Implementations.Entities.ImageInfo", "UserId") + .OnDelete(DeleteBehavior.Cascade); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemDisplayPreferences", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("ItemDisplayPreferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValueMap", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("ItemValues") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.ItemValue", "ItemValue") + .WithMany("BaseItemsMap") + .HasForeignKey("ItemValueId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("ItemValue"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.KeyframeData", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany() + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.LinkedChildEntity", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Child") + .WithMany("LinkedChildOfEntities") + .HasForeignKey("ChildId") + .OnDelete(DeleteBehavior.NoAction) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Parent") + .WithMany("LinkedChildEntities") + .HasForeignKey("ParentId") + .OnDelete(DeleteBehavior.NoAction) + .IsRequired(); + + b.Navigation("Child"); + + b.Navigation("Parent"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaStreamInfo", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("MediaStreams") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.PeopleBaseItemMap", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("Peoples") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.People", "People") + .WithMany("BaseItems") + .HasForeignKey("PeopleId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("People"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Permission", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("Permissions") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Preference", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", null) + .WithMany("Preferences") + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.Security.Device", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.User", "User") + .WithMany() + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("User"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.UserData", b => + { + b.HasOne("Jellyfin.Database.Implementations.Entities.BaseItemEntity", "Item") + .WithMany("UserData") + .HasForeignKey("ItemId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Jellyfin.Database.Implementations.Entities.User", "User") + .WithMany() + .HasForeignKey("UserId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Item"); + + b.Navigation("User"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemEntity", b => + { + b.Navigation("Chapters"); + + b.Navigation("Children"); + + b.Navigation("DirectChildren"); + + b.Navigation("Extras"); + + b.Navigation("Images"); + + b.Navigation("ItemValues"); + + b.Navigation("LinkedChildEntities"); + + b.Navigation("LinkedChildOfEntities"); + + b.Navigation("LockedFields"); + + b.Navigation("MediaStreams"); + + b.Navigation("Parents"); + + b.Navigation("Peoples"); + + b.Navigation("Provider"); + + b.Navigation("TrailerTypes"); + + b.Navigation("UserData"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.DisplayPreferences", b => + { + b.Navigation("HomeSections"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.ItemValue", b => + { + b.Navigation("BaseItemsMap"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.People", b => + { + b.Navigation("BaseItems"); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.User", b => + { + b.Navigation("AccessSchedules"); + + b.Navigation("DisplayPreferences"); + + b.Navigation("ItemDisplayPreferences"); + + b.Navigation("Permissions"); + + b.Navigation("Preferences"); + + b.Navigation("ProfileImage"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.cs new file mode 100644 index 0000000000..069104244c --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.cs @@ -0,0 +1,28 @@ +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace Jellyfin.Server.Implementations.Migrations +{ + /// <inheritdoc /> + public partial class AddPrimaryVersionIdIndex : Migration + { + /// <inheritdoc /> + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.CreateIndex( + name: "IX_BaseItems_PrimaryVersionId", + table: "BaseItems", + column: "PrimaryVersionId", + filter: "\"PrimaryVersionId\" IS NOT NULL"); + } + + /// <inheritdoc /> + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropIndex( + name: "IX_BaseItems_PrimaryVersionId", + table: "BaseItems"); + } + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs index fd18c035e6..0916f72d9c 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs @@ -15,7 +15,7 @@ namespace Jellyfin.Server.Implementations.Migrations protected override void BuildModel(ModelBuilder modelBuilder) { #pragma warning disable 612, 618 - modelBuilder.HasAnnotation("ProductVersion", "10.0.12"); + modelBuilder.HasAnnotation("ProductVersion", "10.0.10"); modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => { @@ -373,6 +373,9 @@ namespace Jellyfin.Server.Implementations.Migrations b.HasIndex("PresentationUniqueKey"); + b.HasIndex("PrimaryVersionId") + .HasFilter("\"PrimaryVersionId\" IS NOT NULL"); + b.HasIndex("SeasonId"); b.HasIndex("SeriesId"); diff --git a/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs index 96625ae670..6b6240e116 100644 --- a/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs +++ b/tests/Jellyfin.Server.Implementations.Tests/Dto/DtoServiceImageInheritanceTests.cs @@ -1,4 +1,5 @@ using System; +using System.Collections.Generic; using Emby.Server.Implementations.Dto; using Emby.Server.Implementations.Playlists; using Jellyfin.Data.Enums; @@ -7,9 +8,9 @@ using MediaBrowser.Controller.Chapters; using MediaBrowser.Controller.Drawing; using MediaBrowser.Controller.Dto; using MediaBrowser.Controller.Entities; +using MediaBrowser.Controller.Entities.Audio; using MediaBrowser.Controller.Library; using MediaBrowser.Controller.LiveTv; -using MediaBrowser.Controller.MediaEncoding; using MediaBrowser.Controller.Providers; using MediaBrowser.Controller.Trickplay; using MediaBrowser.Model.Entities; @@ -99,9 +100,72 @@ public class DtoServiceImageInheritanceTests Assert.Equal("/images/generated.png", dto.ImageTags[ImageType.Primary]); } + [Fact] + public void GetBaseItemDtos_MusicAlbums_ResolveInheritedThumbFromArtistBatch_WithoutPerAlbumLookup() + { + var artist = new MusicArtist + { + Id = Guid.NewGuid(), + Name = "Some Artist", + ImageInfos = + [ + new ItemImageInfo + { + Type = ImageType.Thumb, + Path = "/images/artist-thumb.jpg", + DateModified = new DateTime(2026, 1, 1, 0, 0, 0, DateTimeKind.Utc) + } + ] + }; + + static MusicAlbum MakeAlbum() => new MusicAlbum + { + Id = Guid.NewGuid(), + Name = "Album", + AlbumArtists = ["Some Artist"], + ImageInfos = [] + }; + + var libraryManager = new Mock<ILibraryManager>(); + + // DtoService resolves every album-artist name in ONE batch (GetArtists). The album's inherited + // Thumb/Backdrop images must come from that batch, not a per-album GetArtist/GetItemList lookup + // (the N+1). GetArtist is intentionally left unset: a regression to the per-album path would + // resolve no artist and fail the assertions below. + libraryManager + .Setup(x => x.GetArtists(It.IsAny<IReadOnlyList<string>>())) + .Returns(new Dictionary<string, MusicArtist[]>(StringComparer.OrdinalIgnoreCase) + { + ["Some Artist"] = [artist] + }); + + var dtoService = BuildDtoService(libraryManager); + + var dtos = dtoService.GetBaseItemDtos([MakeAlbum(), MakeAlbum()], new DtoOptions(false)); + + Assert.Equal(2, dtos.Count); + foreach (var dto in dtos) + { + Assert.Equal(artist.Id, dto.ParentThumbItemId); + Assert.Equal("/images/artist-thumb.jpg", dto.ParentThumbImageTag); + } + + // The artist lookup is batched once for the whole set, never once per album. + libraryManager.Verify(x => x.GetArtists(It.IsAny<IReadOnlyList<string>>()), Times.Once); + libraryManager.Verify(x => x.GetArtist(It.IsAny<string>(), It.IsAny<DtoOptions>()), Times.Never); + } + private static DtoService BuildDtoService(BaseItem displayParent) { var libraryManager = new Mock<ILibraryManager>(); + libraryManager + .Setup(x => x.GetItemById(displayParent.Id)) + .Returns(displayParent); + return BuildDtoService(libraryManager); + } + + private static DtoService BuildDtoService(Mock<ILibraryManager> libraryManager) + { var userDataManager = new Mock<IUserDataManager>(); var imageProcessor = new Mock<IImageProcessor>(); var providerManager = new Mock<IProviderManager>(); @@ -113,10 +177,6 @@ public class DtoServiceImageInheritanceTests var chapterManager = new Mock<IChapterManager>(); var logger = new Mock<Microsoft.Extensions.Logging.ILogger<DtoService>>(); - libraryManager - .Setup(x => x.GetItemById(displayParent.Id)) - .Returns(displayParent); - imageProcessor .Setup(x => x.GetImageCacheTag(It.IsAny<BaseItem>(), It.IsAny<ItemImageInfo>())) .Returns<BaseItem, ItemImageInfo>((_, image) => image.Path); |