From 912a963a2bf7f9534e9395d1a2da8d910f249b5b Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 17 Jan 2026 15:58:00 +0100 Subject: Add folder-aware filter extensions and descendant query provider - Add FolderAwareFilterExtensions for LinkedChildren-based filtering - Add IDescendantQueryProvider interface for database-specific queries - Add MatchCriteria classes for folder filtering - Add SqliteDescendantQueryProvider implementation --- .../Item/FolderAwareFilterExtensions.cs | 65 ++++++++++++++++++++++ 1 file changed, 65 insertions(+) create mode 100644 Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs (limited to 'Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs') diff --git a/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs new file mode 100644 index 0000000000..c63d99d54d --- /dev/null +++ b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs @@ -0,0 +1,65 @@ +using System; +using System.Linq; +using System.Linq.Expressions; +using Jellyfin.Database.Implementations; +using Jellyfin.Database.Implementations.Entities; + +namespace Jellyfin.Server.Implementations.Item; + +/// +/// Extension methods for applying folder-aware filters that check items and their descendants. +/// +internal static class FolderAwareFilterExtensions +{ + /// + /// Filters items where either the item matches the condition (for non-folders) + /// or any descendant matches (for folders). Uses reverse traversal through AncestorIds. + /// + /// The query to filter. + /// The database context. + /// The condition to check on BaseItemEntity. + /// Filtered query. + public static IQueryable WhereItemOrDescendantMatches( + this IQueryable query, + JellyfinDbContext context, + Expression> condition) + { + var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id); + var foldersWithMatchingDescendants = context.AncestorIds + .Where(a => matchingIds.Contains(a.ItemId)) + .Select(a => a.ParentItemId) + .Union(context.LinkedChildren + .Where(lc => matchingIds.Contains(lc.ChildId)) + .Select(lc => lc.ParentId)); + + return query.Where(e => + matchingIds.Contains(e.Id) + || foldersWithMatchingDescendants.Contains(e.Id)); + } + + /// + /// Filters items where neither the item matches the condition (for non-folders) + /// nor any descendant matches (for folders). Uses reverse traversal for infinite depth. + /// + /// The query to filter. + /// The database context. + /// The condition that should NOT match. + /// Filtered query. + public static IQueryable WhereNeitherItemNorDescendantMatches( + this IQueryable query, + JellyfinDbContext context, + Expression> condition) + { + var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id); + var foldersWithMatchingDescendants = context.AncestorIds + .Where(a => matchingIds.Contains(a.ItemId)) + .Select(a => a.ParentItemId) + .Union(context.LinkedChildren + .Where(lc => matchingIds.Contains(lc.ChildId)) + .Select(lc => lc.ParentId)); + + return query.Where(e => + !matchingIds.Contains(e.Id) + && !foldersWithMatchingDescendants.Contains(e.Id)); + } +} -- cgit v1.2.3 From 89427af41cdcad519bf865ee14278acf1ce1baed Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 17 Jan 2026 19:39:12 +0100 Subject: Fixes after rebase --- .../Item/BaseItemRepository.cs | 168 +++++++++++++-------- .../Item/FolderAwareFilterExtensions.cs | 21 +-- 2 files changed, 116 insertions(+), 73 deletions(-) (limited to 'Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs') diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs index bfaaa4b24a..e961f34a85 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs @@ -709,41 +709,37 @@ public sealed class BaseItemRepository lastWatchedEpisodes = lwQuery.ToDictionary(e => e.Id); } - var allNextUnwatchedCandidates = context.BaseItems + var allCandidatesWithPlayedStatus = context.BaseItems .AsNoTracking() .Where(e => e.Type == episodeTypeName) .Where(e => e.SeriesPresentationUniqueKey != null && seriesKeys.Contains(e.SeriesPresentationUniqueKey)) .Where(e => e.ParentIndexNumber != 0) .Where(e => !e.IsVirtualItem) - .Where(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played)) - .Select(e => new - { - e.Id, - e.SeriesPresentationUniqueKey, - e.ParentIndexNumber, - EpisodeNumber = e.IndexNumber - }) + .GroupJoin( + context.UserData.AsNoTracking().Where(ud => ud.UserId == userId), + e => e.Id, + ud => ud.ItemId, + (episode, userData) => new + { + episode.Id, + episode.SeriesPresentationUniqueKey, + episode.ParentIndexNumber, + EpisodeNumber = episode.IndexNumber, + IsPlayed = userData.Any(ud => ud.Played) + }) + .ToList(); + + var allNextUnwatchedCandidates = allCandidatesWithPlayedStatus + .Where(c => !c.IsPlayed) + .Select(c => new { c.Id, c.SeriesPresentationUniqueKey, c.ParentIndexNumber, c.EpisodeNumber }) .ToList(); List<(Guid Id, string? SeriesKey, int? Season, int? Episode)> allNextPlayedCandidates = new(); if (includeWatchedForRewatching) { - allNextPlayedCandidates = context.BaseItems - .AsNoTracking() - .Where(e => e.Type == episodeTypeName) - .Where(e => e.SeriesPresentationUniqueKey != null && seriesKeys.Contains(e.SeriesPresentationUniqueKey)) - .Where(e => e.ParentIndexNumber != 0) - .Where(e => !e.IsVirtualItem) - .Where(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)) - .Select(e => new - { - e.Id, - e.SeriesPresentationUniqueKey, - e.ParentIndexNumber, - EpisodeNumber = e.IndexNumber - }) - .AsEnumerable() - .Select(e => (e.Id, e.SeriesPresentationUniqueKey, e.ParentIndexNumber, e.EpisodeNumber)) + allNextPlayedCandidates = allCandidatesWithPlayedStatus + .Where(c => c.IsPlayed) + .Select(c => (c.Id, c.SeriesPresentationUniqueKey, c.ParentIndexNumber, c.EpisodeNumber)) .ToList(); } @@ -1308,12 +1304,38 @@ public sealed class BaseItemRepository context.SaveChanges(); + var itemsWithAncestors = tuples + .Where(t => t.Item.SupportsAncestors && t.AncestorIds != null) + .Select(t => t.Item.Id) + .ToList(); + + var allExistingAncestorIds = itemsWithAncestors.Count > 0 + ? context.AncestorIds + .Where(e => itemsWithAncestors.Contains(e.ItemId)) + .ToList() + .GroupBy(e => e.ItemId) + .ToDictionary(g => g.Key, g => g.ToList()) + : new Dictionary>(); + + var allRequestedAncestorIds = tuples + .Where(t => t.Item.SupportsAncestors && t.AncestorIds != null) + .SelectMany(t => t.AncestorIds!) + .Distinct() + .ToList(); + + var validAncestorIdsSet = allRequestedAncestorIds.Count > 0 + ? context.BaseItems + .Where(e => allRequestedAncestorIds.Contains(e.Id)) + .Select(f => f.Id) + .ToHashSet() + : new HashSet(); + foreach (var item in tuples) { if (item.Item.SupportsAncestors && item.AncestorIds != null) { - var existingAncestorIds = context.AncestorIds.Where(e => e.ItemId == item.Item.Id).ToList(); - var validAncestorIds = context.BaseItems.Where(e => item.AncestorIds.Contains(e.Id)).Select(f => f.Id).ToArray(); + var existingAncestorIds = allExistingAncestorIds.GetValueOrDefault(item.Item.Id) ?? new List(); + var validAncestorIds = item.AncestorIds.Where(id => validAncestorIdsSet.Contains(id)).ToArray(); foreach (var ancestorId in validAncestorIds) { var existingAncestorId = existingAncestorIds.FirstOrDefault(e => e.ParentItemId == ancestorId); @@ -1335,10 +1357,36 @@ public sealed class BaseItemRepository context.AncestorIds.RemoveRange(existingAncestorIds); } + } + + var folderIds = tuples + .Where(t => t.Item is Folder) + .Select(t => t.Item.Id) + .ToList(); + + var videoIds = tuples + .Where(t => t.Item is Video) + .Select(t => t.Item.Id) + .ToList(); + + var allLinkedChildrenByParent = new Dictionary>(); + if (folderIds.Count > 0 || videoIds.Count > 0) + { + var allParentIds = folderIds.Concat(videoIds).Distinct().ToList(); + var allLinkedChildren = context.LinkedChildren + .Where(e => allParentIds.Contains(e.ParentId)) + .ToList(); + + allLinkedChildrenByParent = allLinkedChildren + .GroupBy(e => e.ParentId) + .ToDictionary(g => g.Key, g => g.ToList()); + } + foreach (var item in tuples) + { if (item.Item is Folder folder) { - var existingLinkedChildren = context.LinkedChildren.Where(e => e.ParentId == item.Item.Id).ToList(); + var existingLinkedChildren = allLinkedChildrenByParent.GetValueOrDefault(item.Item.Id)?.ToList() ?? new List(); if (folder.LinkedChildren.Length > 0) { #pragma warning disable CS0618 // Type or member is obsolete - legacy path resolution for old data @@ -1427,8 +1475,9 @@ public sealed class BaseItemRepository // Handle Video alternate versions if (item.Item is Video video) { - var existingLinkedChildren = context.LinkedChildren - .Where(e => e.ParentId == video.Id && ((int)e.ChildType == 2 || (int)e.ChildType == 3)) + // Use batch-fetched data and filter for alternate version types (2 = LocalAlternateVersion, 3 = LinkedAlternateVersion) + var existingLinkedChildren = (allLinkedChildrenByParent.GetValueOrDefault(video.Id) ?? new List()) + .Where(e => (int)e.ChildType == 2 || (int)e.ChildType == 3) .ToList(); var newLinkedChildren = new List<(Guid ChildId, LinkedChildType Type)>(); @@ -2827,19 +2876,35 @@ public sealed class BaseItemRepository if (filter.IsLiked.HasValue) { baseQuery = baseQuery - .Where(e => e.UserData!.FirstOrDefault(f => f.UserId == filter.User!.Id)!.Rating >= UserItemData.MinLikeValue); + .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.Rating >= UserItemData.MinLikeValue)); } if (filter.IsFavoriteOrLiked.HasValue) { - baseQuery = baseQuery - .Where(e => e.UserData!.FirstOrDefault(f => f.UserId == filter.User!.Id)!.IsFavorite == filter.IsFavoriteOrLiked); + if (filter.IsFavoriteOrLiked.Value) + { + baseQuery = baseQuery + .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + } + else + { + baseQuery = baseQuery + .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + } } if (filter.IsFavorite.HasValue) { - baseQuery = baseQuery - .Where(e => e.UserData!.FirstOrDefault(f => f.UserId == filter.User!.Id)!.IsFavorite == filter.IsFavorite); + if (filter.IsFavorite.Value) + { + baseQuery = baseQuery + .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + } + else + { + baseQuery = baseQuery + .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + } } if (filter.IsPlayed.HasValue) @@ -2847,35 +2912,19 @@ public sealed class BaseItemRepository // We should probably figure this out for all folders, but for right now, this is the only place where we need it if (filter.IncludeItemTypes.Length == 1 && filter.IncludeItemTypes[0] == BaseItemKind.Series) { - // Get distinct SeriesPresentationUniqueKeys that have at least one played episode - var playedSeriesKeys = context.BaseItems + // Use subquery to find series with played episodes - stays in SQL instead of materializing to HashSet + var playedSeriesKeysSubquery = context.BaseItems .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesPresentationUniqueKey != null) .Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Played)) - .Select(e => e.SeriesPresentationUniqueKey!) - .Distinct() - .ToHashSet(); + .Select(e => e.SeriesPresentationUniqueKey!); if (filter.IsPlayed.Value) { - if (playedSeriesKeys.Count == 0) - { - baseQuery = baseQuery.Where(e => false); - } - else - { - baseQuery = baseQuery.Where(e => playedSeriesKeys.Contains(e.PresentationUniqueKey!)); - } + baseQuery = baseQuery.Where(e => playedSeriesKeysSubquery.Contains(e.PresentationUniqueKey!)); } else { - if (playedSeriesKeys.Count == 0) - { - // No played episodes - all series are unplayed, no filter needed - } - else - { - baseQuery = baseQuery.Where(e => !playedSeriesKeys.Contains(e.PresentationUniqueKey!)); - } + baseQuery = baseQuery.Where(e => !playedSeriesKeysSubquery.Contains(e.PresentationUniqueKey!)); } } else if (filter.IsPlayed.Value) @@ -2895,12 +2944,12 @@ public sealed class BaseItemRepository if (filter.IsResumable.Value) { baseQuery = baseQuery - .Where(e => e.UserData!.FirstOrDefault(f => f.UserId == filter.User!.Id)!.PlaybackPositionTicks > 0); + .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.PlaybackPositionTicks > 0)); } else { baseQuery = baseQuery - .Where(e => e.UserData!.FirstOrDefault(f => f.UserId == filter.User!.Id)!.PlaybackPositionTicks == 0); + .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.PlaybackPositionTicks > 0)); } } @@ -3103,7 +3152,8 @@ public sealed class BaseItemRepository if (filter.ExtraTypes.Length > 0) { - var extraTypeValues = filter.ExtraTypes.Cast().ToArray(); + // Convert ExtraType enum to BaseItemExtraType enum via int cast (same underlying values) + var extraTypeValues = filter.ExtraTypes.Select(e => (BaseItemExtraType?)(int)e).ToArray(); baseQuery = baseQuery.Where(e => e.ExtraType != null && extraTypeValues.Contains(e.ExtraType)); } diff --git a/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs index c63d99d54d..d7b2567f37 100644 --- a/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs +++ b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs @@ -1,3 +1,5 @@ +#pragma warning disable RS0030 // Do not use banned APIs + using System; using System.Linq; using System.Linq.Expressions; @@ -24,17 +26,13 @@ internal static class FolderAwareFilterExtensions JellyfinDbContext context, Expression> condition) { + // Use correlated Any() subqueries instead of UNION + Contains for better index utilization var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id); - var foldersWithMatchingDescendants = context.AncestorIds - .Where(a => matchingIds.Contains(a.ItemId)) - .Select(a => a.ParentItemId) - .Union(context.LinkedChildren - .Where(lc => matchingIds.Contains(lc.ChildId)) - .Select(lc => lc.ParentId)); return query.Where(e => matchingIds.Contains(e.Id) - || foldersWithMatchingDescendants.Contains(e.Id)); + || context.AncestorIds.Any(a => a.ParentItemId == e.Id && matchingIds.Contains(a.ItemId)) + || context.LinkedChildren.Any(lc => lc.ParentId == e.Id && matchingIds.Contains(lc.ChildId))); } /// @@ -51,15 +49,10 @@ internal static class FolderAwareFilterExtensions Expression> condition) { var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id); - var foldersWithMatchingDescendants = context.AncestorIds - .Where(a => matchingIds.Contains(a.ItemId)) - .Select(a => a.ParentItemId) - .Union(context.LinkedChildren - .Where(lc => matchingIds.Contains(lc.ChildId)) - .Select(lc => lc.ParentId)); return query.Where(e => !matchingIds.Contains(e.Id) - && !foldersWithMatchingDescendants.Contains(e.Id)); + && !context.AncestorIds.Any(a => a.ParentItemId == e.Id && matchingIds.Contains(a.ItemId)) + && !context.LinkedChildren.Any(lc => lc.ParentId == e.Id && matchingIds.Contains(lc.ChildId))); } } -- cgit v1.2.3 From 2789532aa88ccc899ff8497537642e1d78b31ef5 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 31 Jan 2026 19:19:26 +0100 Subject: Optimize Validator and Filter Performance --- .../Library/LibraryManager.cs | 6 + .../Library/Validators/ArtistsValidator.cs | 12 +- .../Library/Validators/GenresValidator.cs | 17 +- .../Library/Validators/StudiosValidator.cs | 17 +- Jellyfin.Api/Controllers/FilterController.cs | 44 +- .../Item/BaseItemRepository.cs | 139 +- .../Item/FolderAwareFilterExtensions.cs | 47 +- .../Item/PeopleRepository.cs | 3 +- .../Users/UserManager.cs | 4 +- MediaBrowser.Controller/Library/ILibraryManager.cs | 7 + .../Persistence/IItemRepository.cs | 7 + .../ModelConfiguration/BaseItemConfiguration.cs | 1 + .../PeopleBaseItemMapConfiguration.cs | 1 + .../ModelConfiguration/UserDataConfiguration.cs | 2 + ...20260130232147_AddBaseItemNameIndex.Designer.cs | 1801 ++++++++++++++++++++ .../20260130232147_AddBaseItemNameIndex.cs | 45 + .../Migrations/JellyfinDbModelSnapshot.cs | 6 + 17 files changed, 2072 insertions(+), 87 deletions(-) create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260130232147_AddBaseItemNameIndex.Designer.cs create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260130232147_AddBaseItemNameIndex.cs (limited to 'Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs') diff --git a/Emby.Server.Implementations/Library/LibraryManager.cs b/Emby.Server.Implementations/Library/LibraryManager.cs index 830c918541..15705dee96 100644 --- a/Emby.Server.Implementations/Library/LibraryManager.cs +++ b/Emby.Server.Implementations/Library/LibraryManager.cs @@ -3495,5 +3495,11 @@ namespace Emby.Server.Implementations.Library { return _itemRepository.RerouteLinkedChildren(fromChildId, toChildId); } + + /// + public QueryFiltersLegacy GetQueryFiltersLegacy(InternalItemsQuery query) + { + return _itemRepository.GetQueryFiltersLegacy(query); + } } } diff --git a/Emby.Server.Implementations/Library/Validators/ArtistsValidator.cs b/Emby.Server.Implementations/Library/Validators/ArtistsValidator.cs index 8de3bff7ab..b196b1e038 100644 --- a/Emby.Server.Implementations/Library/Validators/ArtistsValidator.cs +++ b/Emby.Server.Implementations/Library/Validators/ArtistsValidator.cs @@ -55,6 +55,8 @@ public class ArtistsValidator IncludeItemTypes = [BaseItemKind.MusicArtist] }).ToHashSet(); + var existingArtists = _libraryManager.GetArtists(names); + var numComplete = 0; var count = names.Count; var refreshed = 0; @@ -63,7 +65,15 @@ public class ArtistsValidator { try { - var item = _libraryManager.GetArtist(name); + MusicArtist? item = null; + if (existingArtists.TryGetValue(name, out var artists) && artists.Length > 0) + { + item = artists.OrderBy(i => i.IsAccessedByName ? 1 : 0).First(); + } + + // Fall back to GetArtist if not found (creates new item if needed) + item ??= _libraryManager.GetArtist(name); + if (!existingArtistIds.Contains(item.Id)) { await item.RefreshMetadata(cancellationToken).ConfigureAwait(false); diff --git a/Emby.Server.Implementations/Library/Validators/GenresValidator.cs b/Emby.Server.Implementations/Library/Validators/GenresValidator.cs index 09ede8bb8d..badd6f1183 100644 --- a/Emby.Server.Implementations/Library/Validators/GenresValidator.cs +++ b/Emby.Server.Implementations/Library/Validators/GenresValidator.cs @@ -54,6 +54,13 @@ public class GenresValidator IncludeItemTypes = [BaseItemKind.Genre] }).ToHashSet(); + var existingGenres = _libraryManager.GetItemList(new InternalItemsQuery + { + IncludeItemTypes = [BaseItemKind.Genre] + }).Cast() + .GroupBy(g => g.Name, StringComparer.OrdinalIgnoreCase) + .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase); + var numComplete = 0; var count = names.Count; var refreshed = 0; @@ -62,7 +69,15 @@ public class GenresValidator { try { - var item = _libraryManager.GetGenre(name); + Genre? item = null; + if (existingGenres.TryGetValue(name, out var existingGenre)) + { + item = existingGenre; + } + + // Fall back to GetGenre if not found (creates new item if needed) + item ??= _libraryManager.GetGenre(name); + if (!existingGenreIds.Contains(item.Id)) { await item.RefreshMetadata(cancellationToken).ConfigureAwait(false); diff --git a/Emby.Server.Implementations/Library/Validators/StudiosValidator.cs b/Emby.Server.Implementations/Library/Validators/StudiosValidator.cs index dd124feece..9fb6869171 100644 --- a/Emby.Server.Implementations/Library/Validators/StudiosValidator.cs +++ b/Emby.Server.Implementations/Library/Validators/StudiosValidator.cs @@ -55,6 +55,13 @@ public class StudiosValidator IncludeItemTypes = [BaseItemKind.Studio] }).ToHashSet(); + var existingStudios = _libraryManager.GetItemList(new InternalItemsQuery + { + IncludeItemTypes = [BaseItemKind.Studio] + }).Cast() + .GroupBy(s => s.Name, StringComparer.OrdinalIgnoreCase) + .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase); + var numComplete = 0; var count = names.Count; var refreshed = 0; @@ -63,7 +70,15 @@ public class StudiosValidator { try { - var item = _libraryManager.GetStudio(name); + Studio? item = null; + if (existingStudios.TryGetValue(name, out var existingStudio)) + { + item = existingStudio; + } + + // Fall back to GetStudio if not found (creates new item if needed) + item ??= _libraryManager.GetStudio(name); + if (!existingStudioIds.Contains(item.Id)) { await item.RefreshMetadata(cancellationToken).ConfigureAwait(false); diff --git a/Jellyfin.Api/Controllers/FilterController.cs b/Jellyfin.Api/Controllers/FilterController.cs index 3f9aa93a64..5ad127ad8c 100644 --- a/Jellyfin.Api/Controllers/FilterController.cs +++ b/Jellyfin.Api/Controllers/FilterController.cs @@ -68,53 +68,27 @@ public class FilterController : BaseJellyfinApiController item = _libraryManager.GetParentItem(parentId, user?.Id); } - var query = new InternalItemsQuery + if (item is not Folder folder) + { + return new QueryFiltersLegacy(); + } + + var query = new InternalItemsQuery(user) { - User = user, MediaTypes = mediaTypes, IncludeItemTypes = includeItemTypes, Recursive = true, EnableTotalRecordCount = false, + AncestorIds = [folder.Id], DtoOptions = new DtoOptions { - Fields = new[] { ItemFields.Genres, ItemFields.Tags }, + Fields = [], EnableImages = false, EnableUserData = false } }; - if (item is not Folder folder) - { - return new QueryFiltersLegacy(); - } - - var itemList = folder.GetItemList(query); - return new QueryFiltersLegacy - { - Years = itemList.Select(i => i.ProductionYear ?? -1) - .Where(i => i > 0) - .Distinct() - .Order() - .ToArray(), - - Genres = itemList.SelectMany(i => i.Genres) - .DistinctNames() - .Order() - .ToArray(), - - Tags = itemList - .SelectMany(i => i.Tags) - .Distinct(StringComparer.OrdinalIgnoreCase) - .Order() - .ToArray(), - - OfficialRatings = itemList - .Select(i => i.OfficialRating) - .Where(i => !string.IsNullOrWhiteSpace(i)) - .Distinct(StringComparer.OrdinalIgnoreCase) - .Order() - .ToArray() - }; + return _libraryManager.GetQueryFiltersLegacy(query); } /// diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs index 22178e57f7..55ef5972d4 100644 --- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs +++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs @@ -993,7 +993,7 @@ public sealed class BaseItemRepository dbQuery = dbQuery.Include(e => e.Extras); } - return dbQuery; + return dbQuery.AsSingleQuery(); } private IQueryable ApplyQueryPaging(IQueryable dbQuery, InternalItemsQuery filter) @@ -1046,6 +1046,62 @@ public sealed class BaseItemRepository return dbQuery.Count(); } + /// + public QueryFiltersLegacy GetQueryFiltersLegacy(InternalItemsQuery filter) + { + ArgumentNullException.ThrowIfNull(filter); + PrepareFilterQuery(filter); + + using var context = _dbProvider.CreateDbContext(); + var baseQuery = PrepareItemQuery(context, filter); + baseQuery = TranslateQuery(baseQuery, context, filter); + + // Get matching item IDs as a subquery (not materialized) + var matchingItemIds = baseQuery.Select(e => e.Id); + + // Query distinct years directly from the database + var years = baseQuery + .Where(e => e.ProductionYear != null && e.ProductionYear > 0) + .Select(e => e.ProductionYear!.Value) + .Distinct() + .OrderBy(y => y) + .ToArray(); + + // Query distinct official ratings directly from the database + var officialRatings = baseQuery + .Where(e => e.OfficialRating != null && e.OfficialRating != string.Empty) + .Select(e => e.OfficialRating!) + .Distinct() + .OrderBy(r => r) + .ToArray(); + + // Tags via ItemValuesMap JOIN - uses subquery for matching items + var tags = context.ItemValuesMap + .Where(ivm => ivm.ItemValue.Type == ItemValueType.Tags) + .Where(ivm => matchingItemIds.Contains(ivm.ItemId)) + .Select(ivm => ivm.ItemValue.CleanValue) + .Distinct() + .OrderBy(t => t) + .ToArray(); + + // Genres via ItemValuesMap JOIN - uses subquery for matching items + var genres = context.ItemValuesMap + .Where(ivm => ivm.ItemValue.Type == ItemValueType.Genre) + .Where(ivm => matchingItemIds.Contains(ivm.ItemId)) + .Select(ivm => ivm.ItemValue.CleanValue) + .Distinct() + .OrderBy(g => g) + .ToArray(); + + return new QueryFiltersLegacy + { + Years = years, + OfficialRatings = officialRatings, + Tags = tags, + Genres = genres + }; + } + /// public ItemCounts GetItemCounts(InternalItemsQuery filter) { @@ -1229,8 +1285,6 @@ public sealed class BaseItemRepository } } - context.SaveChanges(); - var itemValueMaps = tuples .Select(e => (e.Item, Values: GetItemValuesToSave(e.Item, e.InheritedTags))) .ToArray(); @@ -1255,7 +1309,6 @@ public sealed class BaseItemRepository Value = f.Value }).ToArray(); context.ItemValues.AddRange(missingItemValues); - context.SaveChanges(); var itemValuesStore = existingValues.Concat(missingItemValues).ToArray(); var valueMap = itemValueMaps @@ -1291,8 +1344,6 @@ public sealed class BaseItemRepository context.ItemValuesMap.RemoveRange(itemMappedValues); } - context.SaveChanges(); - var itemsWithAncestors = tuples .Where(t => t.Item.SupportsAncestors && t.AncestorIds != null) .Select(t => t.Item.Id) @@ -1619,7 +1670,8 @@ public sealed class BaseItemRepository .Include(e => e.LockedFields) .Include(e => e.UserData) .Include(e => e.Images) - .Include(e => e.LinkedChildEntities); + .Include(e => e.LinkedChildEntities) + .AsSingleQuery(); var item = dbQuery.FirstOrDefault(e => e.Id == id); if (item is null) @@ -2780,7 +2832,7 @@ public sealed class BaseItemRepository if (filter.TrailerTypes.Length > 0) { var trailerTypes = filter.TrailerTypes.Select(e => (int)e).ToArray(); - baseQuery = baseQuery.Where(e => trailerTypes.Any(f => e.TrailerTypes!.Any(w => w.Id == f))); + baseQuery = baseQuery.Where(e => e.TrailerTypes!.Any(w => trailerTypes.Contains(w.Id))); } if (filter.IsAiring.HasValue) @@ -2896,29 +2948,31 @@ public sealed class BaseItemRepository if (filter.IsFavoriteOrLiked.HasValue) { + var favoriteItemIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) + .Select(ud => ud.ItemId); if (filter.IsFavoriteOrLiked.Value) { - baseQuery = baseQuery - .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + baseQuery = baseQuery.Where(e => favoriteItemIds.Contains(e.Id)); } else { - baseQuery = baseQuery - .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + baseQuery = baseQuery.Where(e => !favoriteItemIds.Contains(e.Id)); } } if (filter.IsFavorite.HasValue) { + var favoriteItemIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) + .Select(ud => ud.ItemId); if (filter.IsFavorite.Value) { - baseQuery = baseQuery - .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + baseQuery = baseQuery.Where(e => favoriteItemIds.Contains(e.Id)); } else { - baseQuery = baseQuery - .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.IsFavorite)); + baseQuery = baseQuery.Where(e => !favoriteItemIds.Contains(e.Id)); } } @@ -2927,44 +2981,56 @@ public sealed class BaseItemRepository // We should probably figure this out for all folders, but for right now, this is the only place where we need it if (filter.IncludeItemTypes.Length == 1 && filter.IncludeItemTypes[0] == BaseItemKind.Series) { - // Use subquery to find series with played episodes - stays in SQL instead of materializing to HashSet - var playedSeriesKeysSubquery = context.BaseItems - .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesPresentationUniqueKey != null) - .Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Played)) - .Select(e => e.SeriesPresentationUniqueKey!); + // Get played series IDs by joining episodes to UserData via SeriesId (Guid foreign key). + // Don't filter episodes by TopParentIds here - the series will be filtered by baseQuery anyway. + // This allows the materialized list to be reused across library-scoped queries. + var playedSeriesIdList = context.BaseItems + .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue) + .Join( + context.UserData.Where(ud => ud.UserId == filter.User!.Id && ud.Played), + episode => episode.Id, + ud => ud.ItemId, + (episode, ud) => episode.SeriesId!.Value) + .Distinct() + .ToList(); if (filter.IsPlayed.Value) { - baseQuery = baseQuery.Where(e => playedSeriesKeysSubquery.Contains(e.PresentationUniqueKey!)); + baseQuery = baseQuery.Where(s => playedSeriesIdList.Contains(s.Id)); } else { - baseQuery = baseQuery.Where(e => !playedSeriesKeysSubquery.Contains(e.PresentationUniqueKey!)); + baseQuery = baseQuery.Where(s => !playedSeriesIdList.Contains(s.Id)); } } - else if (filter.IsPlayed.Value) - { - baseQuery = baseQuery - .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.Played)); - } else { - baseQuery = baseQuery - .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.Played)); + var playedItemIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.Played) + .Select(ud => ud.ItemId); + if (filter.IsPlayed.Value) + { + baseQuery = baseQuery.Where(e => playedItemIds.Contains(e.Id)); + } + else + { + baseQuery = baseQuery.Where(e => !playedItemIds.Contains(e.Id)); + } } } if (filter.IsResumable.HasValue) { + var resumableItemIds = context.UserData + .Where(ud => ud.UserId == filter.User!.Id && ud.PlaybackPositionTicks > 0) + .Select(ud => ud.ItemId); if (filter.IsResumable.Value) { - baseQuery = baseQuery - .Where(e => e.UserData!.Any(f => f.UserId == filter.User!.Id && f.PlaybackPositionTicks > 0)); + baseQuery = baseQuery.Where(e => resumableItemIds.Contains(e.Id)); } else { - baseQuery = baseQuery - .Where(e => !e.UserData!.Any(f => f.UserId == filter.User!.Id && f.PlaybackPositionTicks > 0)); + baseQuery = baseQuery.Where(e => !resumableItemIds.Contains(e.Id)); } } @@ -3681,9 +3747,12 @@ public sealed class BaseItemRepository private static (int Played, int Total) GetPlayedAndTotalCountFromQuery(IQueryable query, Guid userId) { + // GroupBy with a constant key aggregates all rows into a single group for server-side counting. + // OrderBy is required before FirstOrDefault to avoid EF Core warnings about unpredictable results. var result = query .Select(b => b.UserData!.Any(u => u.UserId == userId && u.Played)) - .GroupBy(_ => 1) // Hack to aggregate over entire set + .GroupBy(_ => 1) + .OrderBy(g => g.Key) .Select(g => new { Total = g.Count(), diff --git a/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs index d7b2567f37..888dacd16b 100644 --- a/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs +++ b/Jellyfin.Server.Implementations/Item/FolderAwareFilterExtensions.cs @@ -26,13 +26,25 @@ internal static class FolderAwareFilterExtensions JellyfinDbContext context, Expression> condition) { - // Use correlated Any() subqueries instead of UNION + Contains for better index utilization - var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id); + // Get IDs of items that directly match the condition + var directMatchIds = context.BaseItems.Where(condition).Select(b => b.Id); - return query.Where(e => - matchingIds.Contains(e.Id) - || context.AncestorIds.Any(a => a.ParentItemId == e.Id && matchingIds.Contains(a.ItemId)) - || context.LinkedChildren.Any(lc => lc.ParentId == e.Id && matchingIds.Contains(lc.ChildId))); + // Get parent IDs where a descendant (via AncestorIds) matches + var ancestorMatchIds = context.AncestorIds + .Where(a => directMatchIds.Contains(a.ItemId)) + .Select(a => a.ParentItemId); + + // Get parent IDs where a linked child matches + var linkedMatchIds = context.LinkedChildren + .Where(lc => directMatchIds.Contains(lc.ChildId)) + .Select(lc => lc.ParentId); + + var allMatchingIds = directMatchIds + .Concat(ancestorMatchIds) + .Concat(linkedMatchIds) + .Distinct(); + + return query.Where(e => allMatchingIds.Contains(e.Id)); } /// @@ -48,11 +60,24 @@ internal static class FolderAwareFilterExtensions JellyfinDbContext context, Expression> condition) { - var matchingIds = context.BaseItems.Where(condition).Select(b => b.Id); + // Get IDs of items that directly match the condition + var directMatchIds = context.BaseItems.Where(condition).Select(b => b.Id); + + // Get parent IDs where a descendant (via AncestorIds) matches + var ancestorMatchIds = context.AncestorIds + .Where(a => directMatchIds.Contains(a.ItemId)) + .Select(a => a.ParentItemId); + + // Get parent IDs where a linked child matches + var linkedMatchIds = context.LinkedChildren + .Where(lc => directMatchIds.Contains(lc.ChildId)) + .Select(lc => lc.ParentId); + + var allMatchingIds = directMatchIds + .Concat(ancestorMatchIds) + .Concat(linkedMatchIds) + .Distinct(); - return query.Where(e => - !matchingIds.Contains(e.Id) - && !context.AncestorIds.Any(a => a.ParentItemId == e.Id && matchingIds.Contains(a.ItemId)) - && !context.LinkedChildren.Any(lc => lc.ParentId == e.Id && matchingIds.Contains(lc.ChildId))); + return query.Where(e => !allMatchingIds.Contains(e.Id)); } } diff --git a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs index 355ed64797..022b452e85 100644 --- a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs +++ b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs @@ -62,10 +62,9 @@ public class PeopleRepository(IDbContextFactory dbProvider, I using var context = _dbProvider.CreateDbContext(); var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter).Select(e => e.Name).Distinct(); - // dbQuery = dbQuery.OrderBy(e => e.ListOrder); if (filter.Limit > 0) { - dbQuery = dbQuery.Take(filter.Limit); + dbQuery = dbQuery.OrderBy(e => e).Take(filter.Limit); } return dbQuery.ToArray(); diff --git a/Jellyfin.Server.Implementations/Users/UserManager.cs b/Jellyfin.Server.Implementations/Users/UserManager.cs index 501cb4fbe8..7292e9c7a9 100644 --- a/Jellyfin.Server.Implementations/Users/UserManager.cs +++ b/Jellyfin.Server.Implementations/Users/UserManager.cs @@ -93,7 +93,7 @@ namespace Jellyfin.Server.Implementations.Users _users = new ConcurrentDictionary(); using var dbContext = _dbProvider.CreateDbContext(); foreach (var user in dbContext.Users - .AsSplitQuery() + .AsSingleQuery() .Include(user => user.Permissions) .Include(user => user.Preferences) .Include(user => user.AccessSchedules) @@ -607,6 +607,7 @@ namespace Jellyfin.Server.Implementations.Users .Include(u => u.Preferences) .Include(u => u.AccessSchedules) .Include(u => u.ProfileImage) + .AsSingleQuery() .FirstOrDefault(u => u.Id.Equals(userId)) ?? throw new ArgumentException("No user exists with given Id!"); @@ -651,6 +652,7 @@ namespace Jellyfin.Server.Implementations.Users .Include(u => u.Preferences) .Include(u => u.AccessSchedules) .Include(u => u.ProfileImage) + .AsSingleQuery() .FirstOrDefault(u => u.Id.Equals(userId)) ?? throw new ArgumentException("No user exists with given Id!"); diff --git a/MediaBrowser.Controller/Library/ILibraryManager.cs b/MediaBrowser.Controller/Library/ILibraryManager.cs index 48859de04b..adb590ddbe 100644 --- a/MediaBrowser.Controller/Library/ILibraryManager.cs +++ b/MediaBrowser.Controller/Library/ILibraryManager.cs @@ -723,5 +723,12 @@ namespace MediaBrowser.Controller.Library /// The child ID to re-route to. /// Number of references updated. int RerouteLinkedChildReferences(Guid fromChildId, Guid toChildId); + + /// + /// Gets legacy query filters for filtering UI. + /// + /// The query filter. + /// Aggregated filter values. + QueryFiltersLegacy GetQueryFiltersLegacy(InternalItemsQuery query); } } diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs index 504adff86c..a063debbb5 100644 --- a/MediaBrowser.Controller/Persistence/IItemRepository.cs +++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs @@ -218,6 +218,13 @@ public interface IItemRepository /// List of parent IDs that reference the child. IReadOnlyList GetManualLinkedParentIds(Guid childId); + /// + /// Gets legacy query filters (Years, Genres, Tags, OfficialRatings) aggregated directly from the database. + /// + /// The query filter. + /// Aggregated filter values. + QueryFiltersLegacy GetQueryFiltersLegacy(InternalItemsQuery filter); + /// /// Updates LinkedChildren references from one child to another, preserving SortOrder. /// Handles duplicates: if parent already references toChildId, removes the old reference instead. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs index 4ec1b972dd..965da7ffd2 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs @@ -36,6 +36,7 @@ public class BaseItemConfiguration : IEntityTypeConfiguration builder.HasIndex(e => e.Path); builder.HasIndex(e => e.ParentId); builder.HasIndex(e => e.OwnerId); + builder.HasIndex(e => e.Name); builder.HasIndex(e => e.ExtraType); builder.HasIndex(e => new { e.ExtraType, e.OwnerId }); builder.HasIndex(e => e.PresentationUniqueKey); diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs index f7694aeda0..32ede86c96 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs @@ -15,6 +15,7 @@ public class PeopleBaseItemMapConfiguration : IEntityTypeConfiguration new { e.ItemId, e.PeopleId, e.Role }); builder.HasIndex(e => new { e.ItemId, e.SortOrder }); builder.HasIndex(e => new { e.ItemId, e.ListOrder }); + builder.HasIndex(e => e.PeopleId); builder.HasOne(e => e.Item); builder.HasOne(e => e.People); } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs index 223b2f8784..42848c6c4e 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs @@ -18,6 +18,8 @@ public class UserDataConfiguration : IEntityTypeConfiguration builder.HasIndex(d => new { d.ItemId, d.UserId, d.IsFavorite }); builder.HasIndex(d => new { d.ItemId, d.UserId, d.LastPlayedDate }); builder.HasIndex(d => new { d.UserId, d.ItemId, d.LastPlayedDate }); + builder.HasIndex(d => new { d.UserId, d.Played, d.ItemId }); + builder.HasIndex(d => new { d.UserId, d.IsFavorite, d.ItemId }); builder.HasOne(e => e.Item).WithMany(e => e.UserData); } } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260130232147_AddBaseItemNameIndex.Designer.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260130232147_AddBaseItemNameIndex.Designer.cs new file mode 100644 index 0000000000..1b396a707c --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260130232147_AddBaseItemNameIndex.Designer.cs @@ -0,0 +1,1801 @@ +// +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.Database.Providers.Sqlite.Migrations +{ + [DbContext(typeof(JellyfinDbContext))] + [Migration("20260130232147_AddBaseItemNameIndex")] + partial class AddBaseItemNameIndex + { + /// + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder.HasAnnotation("ProductVersion", "10.0.2"); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.AccessSchedule", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("DayOfWeek") + .HasColumnType("INTEGER"); + + b.Property("EndHour") + .HasColumnType("REAL"); + + b.Property("StartHour") + .HasColumnType("REAL"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("ItemId") + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property("LogSeverity") + .HasColumnType("INTEGER"); + + b.Property("Name") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("Overview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("ShortOverview") + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("Type") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property("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("ItemId") + .HasColumnType("TEXT"); + + b.Property("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("ItemId") + .HasColumnType("TEXT"); + + b.Property("Index") + .HasColumnType("INTEGER"); + + b.Property("Codec") + .HasColumnType("TEXT"); + + b.Property("CodecTag") + .HasColumnType("TEXT"); + + b.Property("Comment") + .HasColumnType("TEXT"); + + b.Property("Filename") + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("Album") + .HasColumnType("TEXT"); + + b.Property("AlbumArtists") + .HasColumnType("TEXT"); + + b.Property("Artists") + .HasColumnType("TEXT"); + + b.Property("Audio") + .HasColumnType("INTEGER"); + + b.Property("ChannelId") + .HasColumnType("TEXT"); + + b.Property("CleanName") + .HasColumnType("TEXT"); + + b.Property("CommunityRating") + .HasColumnType("REAL"); + + b.Property("CriticRating") + .HasColumnType("REAL"); + + b.Property("CustomRating") + .HasColumnType("TEXT"); + + b.Property("Data") + .HasColumnType("TEXT"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("DateLastMediaAdded") + .HasColumnType("TEXT"); + + b.Property("DateLastRefreshed") + .HasColumnType("TEXT"); + + b.Property("DateLastSaved") + .HasColumnType("TEXT"); + + b.Property("DateModified") + .HasColumnType("TEXT"); + + b.Property("EndDate") + .HasColumnType("TEXT"); + + b.Property("EpisodeTitle") + .HasColumnType("TEXT"); + + b.Property("ExternalId") + .HasColumnType("TEXT"); + + b.Property("ExternalSeriesId") + .HasColumnType("TEXT"); + + b.Property("ExternalServiceId") + .HasColumnType("TEXT"); + + b.Property("ExtraType") + .HasColumnType("INTEGER"); + + b.Property("ForcedSortName") + .HasColumnType("TEXT"); + + b.Property("Genres") + .HasColumnType("TEXT"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("IndexNumber") + .HasColumnType("INTEGER"); + + b.Property("InheritedParentalRatingSubValue") + .HasColumnType("INTEGER"); + + b.Property("InheritedParentalRatingValue") + .HasColumnType("INTEGER"); + + b.Property("IsFolder") + .HasColumnType("INTEGER"); + + b.Property("IsInMixedFolder") + .HasColumnType("INTEGER"); + + b.Property("IsLocked") + .HasColumnType("INTEGER"); + + b.Property("IsMovie") + .HasColumnType("INTEGER"); + + b.Property("IsRepeat") + .HasColumnType("INTEGER"); + + b.Property("IsSeries") + .HasColumnType("INTEGER"); + + b.Property("IsVirtualItem") + .HasColumnType("INTEGER"); + + b.Property("LUFS") + .HasColumnType("REAL"); + + b.Property("MediaType") + .HasColumnType("TEXT"); + + b.Property("Name") + .HasColumnType("TEXT"); + + b.Property("NormalizationGain") + .HasColumnType("REAL"); + + b.Property("OfficialRating") + .HasColumnType("TEXT"); + + b.Property("OriginalTitle") + .HasColumnType("TEXT"); + + b.Property("Overview") + .HasColumnType("TEXT"); + + b.Property("OwnerId") + .HasColumnType("TEXT"); + + b.Property("ParentId") + .HasColumnType("TEXT"); + + b.Property("ParentIndexNumber") + .HasColumnType("INTEGER"); + + b.Property("Path") + .HasColumnType("TEXT"); + + b.Property("PreferredMetadataCountryCode") + .HasColumnType("TEXT"); + + b.Property("PreferredMetadataLanguage") + .HasColumnType("TEXT"); + + b.Property("PremiereDate") + .HasColumnType("TEXT"); + + b.Property("PresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property("PrimaryVersionId") + .HasColumnType("TEXT"); + + b.Property("ProductionLocations") + .HasColumnType("TEXT"); + + b.Property("ProductionYear") + .HasColumnType("INTEGER"); + + b.Property("RunTimeTicks") + .HasColumnType("INTEGER"); + + b.Property("SeasonId") + .HasColumnType("TEXT"); + + b.Property("SeasonName") + .HasColumnType("TEXT"); + + b.Property("SeriesId") + .HasColumnType("TEXT"); + + b.Property("SeriesName") + .HasColumnType("TEXT"); + + b.Property("SeriesPresentationUniqueKey") + .HasColumnType("TEXT"); + + b.Property("ShowId") + .HasColumnType("TEXT"); + + b.Property("Size") + .HasColumnType("INTEGER"); + + b.Property("SortName") + .HasColumnType("TEXT"); + + b.Property("StartDate") + .HasColumnType("TEXT"); + + b.Property("Studios") + .HasColumnType("TEXT"); + + b.Property("Tagline") + .HasColumnType("TEXT"); + + b.Property("Tags") + .HasColumnType("TEXT"); + + b.Property("TopParentId") + .HasColumnType("TEXT"); + + b.Property("TotalBitrate") + .HasColumnType("INTEGER"); + + b.Property("Type") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("UnratedType") + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("ExtraType"); + + b.HasIndex("Name"); + + b.HasIndex("OwnerId"); + + b.HasIndex("ParentId"); + + b.HasIndex("Path"); + + b.HasIndex("PresentationUniqueKey"); + + b.HasIndex("ExtraType", "OwnerId"); + + b.HasIndex("TopParentId", "Id"); + + b.HasIndex("Type", "TopParentId", "Id"); + + b.HasIndex("Type", "TopParentId", "PresentationUniqueKey"); + + b.HasIndex("Type", "TopParentId", "StartDate"); + + b.HasIndex("Id", "Type", "IsFolder", "IsVirtualItem"); + + 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", "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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("Blurhash") + .HasColumnType("BLOB"); + + b.Property("DateModified") + .HasColumnType("TEXT"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("ImageType") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("Path") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.HasIndex("ItemId"); + + b.HasIndex("ItemId", "ImageType"); + + b.ToTable("BaseItemImageInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemMetadataField", b => + { + b.Property("Id") + .HasColumnType("INTEGER"); + + b.Property("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("ItemId") + .HasColumnType("TEXT"); + + b.Property("ProviderId") + .HasColumnType("TEXT"); + + b.Property("ProviderValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.HasKey("ItemId", "ProviderId"); + + b.HasIndex("ProviderId", "ProviderValue", "ItemId"); + + b.ToTable("BaseItemProviders"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.BaseItemTrailerType", b => + { + b.Property("Id") + .HasColumnType("INTEGER"); + + b.Property("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("ItemId") + .HasColumnType("TEXT"); + + b.Property("ChapterIndex") + .HasColumnType("INTEGER"); + + b.Property("ImageDateModified") + .HasColumnType("TEXT"); + + b.Property("ImagePath") + .HasColumnType("TEXT"); + + b.Property("Name") + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("Key") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("ChromecastVersion") + .HasColumnType("INTEGER"); + + b.Property("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("DashboardTheme") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("EnableNextVideoInfoOverlay") + .HasColumnType("INTEGER"); + + b.Property("IndexBy") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("ScrollDirection") + .HasColumnType("INTEGER"); + + b.Property("ShowBackdrop") + .HasColumnType("INTEGER"); + + b.Property("ShowSidebar") + .HasColumnType("INTEGER"); + + b.Property("SkipBackwardLength") + .HasColumnType("INTEGER"); + + b.Property("SkipForwardLength") + .HasColumnType("INTEGER"); + + b.Property("TvHome") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("DisplayPreferencesId") + .HasColumnType("INTEGER"); + + b.Property("Order") + .HasColumnType("INTEGER"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("LastModified") + .HasColumnType("TEXT"); + + b.Property("Path") + .IsRequired() + .HasMaxLength(512) + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Client") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("IndexBy") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("RememberIndexing") + .HasColumnType("INTEGER"); + + b.Property("RememberSorting") + .HasColumnType("INTEGER"); + + b.Property("SortBy") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property("SortOrder") + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("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("ItemValueId") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("CleanValue") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("Type") + .HasColumnType("INTEGER"); + + b.Property("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("ItemValueId") + .HasColumnType("TEXT"); + + b.Property("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("ItemId") + .HasColumnType("TEXT"); + + b.PrimitiveCollection("KeyframeTicks") + .HasColumnType("TEXT"); + + b.Property("TotalDuration") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId"); + + b.ToTable("KeyframeData"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.LinkedChildEntity", b => + { + b.Property("ParentId") + .HasColumnType("TEXT"); + + b.Property("ChildId") + .HasColumnType("TEXT"); + + b.Property("ChildType") + .HasColumnType("INTEGER"); + + b.Property("SortOrder") + .HasColumnType("INTEGER"); + + b.HasKey("ParentId", "ChildId"); + + b.HasIndex("ChildId"); + + b.HasIndex("ParentId"); + + 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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("EndTicks") + .HasColumnType("INTEGER"); + + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("SegmentProviderId") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("StartTicks") + .HasColumnType("INTEGER"); + + b.Property("Type") + .HasColumnType("INTEGER"); + + b.HasKey("Id"); + + b.ToTable("MediaSegments"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.MediaStreamInfo", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("StreamIndex") + .HasColumnType("INTEGER"); + + b.Property("AspectRatio") + .HasColumnType("TEXT"); + + b.Property("AverageFrameRate") + .HasColumnType("REAL"); + + b.Property("BitDepth") + .HasColumnType("INTEGER"); + + b.Property("BitRate") + .HasColumnType("INTEGER"); + + b.Property("BlPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("ChannelLayout") + .HasColumnType("TEXT"); + + b.Property("Channels") + .HasColumnType("INTEGER"); + + b.Property("Codec") + .HasColumnType("TEXT"); + + b.Property("CodecTag") + .HasColumnType("TEXT"); + + b.Property("CodecTimeBase") + .HasColumnType("TEXT"); + + b.Property("ColorPrimaries") + .HasColumnType("TEXT"); + + b.Property("ColorSpace") + .HasColumnType("TEXT"); + + b.Property("ColorTransfer") + .HasColumnType("TEXT"); + + b.Property("Comment") + .HasColumnType("TEXT"); + + b.Property("DvBlSignalCompatibilityId") + .HasColumnType("INTEGER"); + + b.Property("DvLevel") + .HasColumnType("INTEGER"); + + b.Property("DvProfile") + .HasColumnType("INTEGER"); + + b.Property("DvVersionMajor") + .HasColumnType("INTEGER"); + + b.Property("DvVersionMinor") + .HasColumnType("INTEGER"); + + b.Property("ElPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("Hdr10PlusPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("IsAnamorphic") + .HasColumnType("INTEGER"); + + b.Property("IsAvc") + .HasColumnType("INTEGER"); + + b.Property("IsDefault") + .HasColumnType("INTEGER"); + + b.Property("IsExternal") + .HasColumnType("INTEGER"); + + b.Property("IsForced") + .HasColumnType("INTEGER"); + + b.Property("IsHearingImpaired") + .HasColumnType("INTEGER"); + + b.Property("IsInterlaced") + .HasColumnType("INTEGER"); + + b.Property("KeyFrames") + .HasColumnType("TEXT"); + + b.Property("Language") + .HasColumnType("TEXT"); + + b.Property("Level") + .HasColumnType("REAL"); + + b.Property("NalLengthSize") + .HasColumnType("TEXT"); + + b.Property("Path") + .HasColumnType("TEXT"); + + b.Property("PixelFormat") + .HasColumnType("TEXT"); + + b.Property("Profile") + .HasColumnType("TEXT"); + + b.Property("RealFrameRate") + .HasColumnType("REAL"); + + b.Property("RefFrames") + .HasColumnType("INTEGER"); + + b.Property("Rotation") + .HasColumnType("INTEGER"); + + b.Property("RpuPresentFlag") + .HasColumnType("INTEGER"); + + b.Property("SampleRate") + .HasColumnType("INTEGER"); + + b.Property("StreamType") + .HasColumnType("INTEGER"); + + b.Property("TimeBase") + .HasColumnType("TEXT"); + + b.Property("Title") + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.HasKey("ItemId", "StreamIndex"); + + b.HasIndex("StreamIndex"); + + b.HasIndex("StreamType"); + + b.HasIndex("StreamIndex", "StreamType"); + + b.HasIndex("StreamIndex", "StreamType", "Language"); + + b.ToTable("MediaStreamInfos"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.People", b => + { + b.Property("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("Name") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("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("ItemId") + .HasColumnType("TEXT"); + + b.Property("PeopleId") + .HasColumnType("TEXT"); + + b.Property("Role") + .HasColumnType("TEXT"); + + b.Property("ListOrder") + .HasColumnType("INTEGER"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Kind") + .HasColumnType("INTEGER"); + + b.Property("Permission_Permissions_Guid") + .HasColumnType("TEXT"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("Kind") + .HasColumnType("INTEGER"); + + b.Property("Preference_Preferences_Guid") + .HasColumnType("TEXT"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("AccessToken") + .IsRequired() + .HasColumnType("TEXT"); + + b.Property("AppName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property("AppVersion") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("DateCreated") + .HasColumnType("TEXT"); + + b.Property("DateLastActivity") + .HasColumnType("TEXT"); + + b.Property("DateModified") + .HasColumnType("TEXT"); + + b.Property("DeviceId") + .IsRequired() + .HasMaxLength(256) + .HasColumnType("TEXT"); + + b.Property("DeviceName") + .IsRequired() + .HasMaxLength(64) + .HasColumnType("TEXT"); + + b.Property("IsActive") + .HasColumnType("INTEGER"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("DeviceId"); + + 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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("INTEGER"); + + b.Property("CustomName") + .HasColumnType("TEXT"); + + b.Property("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("ItemId") + .HasColumnType("TEXT"); + + b.Property("Width") + .HasColumnType("INTEGER"); + + b.Property("Bandwidth") + .HasColumnType("INTEGER"); + + b.Property("Height") + .HasColumnType("INTEGER"); + + b.Property("Interval") + .HasColumnType("INTEGER"); + + b.Property("ThumbnailCount") + .HasColumnType("INTEGER"); + + b.Property("TileHeight") + .HasColumnType("INTEGER"); + + b.Property("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("Id") + .ValueGeneratedOnAdd() + .HasColumnType("TEXT"); + + b.Property("AudioLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("AuthenticationProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("CastReceiverId") + .HasMaxLength(32) + .HasColumnType("TEXT"); + + b.Property("DisplayCollectionsView") + .HasColumnType("INTEGER"); + + b.Property("DisplayMissingEpisodes") + .HasColumnType("INTEGER"); + + b.Property("EnableAutoLogin") + .HasColumnType("INTEGER"); + + b.Property("EnableLocalPassword") + .HasColumnType("INTEGER"); + + b.Property("EnableNextEpisodeAutoPlay") + .HasColumnType("INTEGER"); + + b.Property("EnableUserPreferenceAccess") + .HasColumnType("INTEGER"); + + b.Property("HidePlayedInLatest") + .HasColumnType("INTEGER"); + + b.Property("InternalId") + .HasColumnType("INTEGER"); + + b.Property("InvalidLoginAttemptCount") + .HasColumnType("INTEGER"); + + b.Property("LastActivityDate") + .HasColumnType("TEXT"); + + b.Property("LastLoginDate") + .HasColumnType("TEXT"); + + b.Property("LoginAttemptsBeforeLockout") + .HasColumnType("INTEGER"); + + b.Property("MaxActiveSessions") + .HasColumnType("INTEGER"); + + b.Property("MaxParentalRatingScore") + .HasColumnType("INTEGER"); + + b.Property("MaxParentalRatingSubScore") + .HasColumnType("INTEGER"); + + b.Property("MustUpdatePassword") + .HasColumnType("INTEGER"); + + b.Property("Password") + .HasMaxLength(65535) + .HasColumnType("TEXT"); + + b.Property("PasswordResetProviderId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("PlayDefaultAudioTrack") + .HasColumnType("INTEGER"); + + b.Property("RememberAudioSelections") + .HasColumnType("INTEGER"); + + b.Property("RememberSubtitleSelections") + .HasColumnType("INTEGER"); + + b.Property("RemoteClientBitrateLimit") + .HasColumnType("INTEGER"); + + b.Property("RowVersion") + .IsConcurrencyToken() + .HasColumnType("INTEGER"); + + b.Property("SubtitleLanguagePreference") + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.Property("SubtitleMode") + .HasColumnType("INTEGER"); + + b.Property("SyncPlayAccess") + .HasColumnType("INTEGER"); + + b.Property("Username") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("TEXT"); + + b.HasKey("Id"); + + b.HasIndex("Username") + .IsUnique(); + + b.ToTable("Users"); + + b.HasAnnotation("Sqlite:UseSqlReturningClause", false); + }); + + modelBuilder.Entity("Jellyfin.Database.Implementations.Entities.UserData", b => + { + b.Property("ItemId") + .HasColumnType("TEXT"); + + b.Property("UserId") + .HasColumnType("TEXT"); + + b.Property("CustomDataKey") + .HasColumnType("TEXT"); + + b.Property("AudioStreamIndex") + .HasColumnType("INTEGER"); + + b.Property("IsFavorite") + .HasColumnType("INTEGER"); + + b.Property("LastPlayedDate") + .HasColumnType("TEXT"); + + b.Property("Likes") + .HasColumnType("INTEGER"); + + b.Property("PlayCount") + .HasColumnType("INTEGER"); + + b.Property("PlaybackPositionTicks") + .HasColumnType("INTEGER"); + + b.Property("Played") + .HasColumnType("INTEGER"); + + b.Property("Rating") + .HasColumnType("REAL"); + + b.Property("RetentionDate") + .HasColumnType("TEXT"); + + b.Property("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/20260130232147_AddBaseItemNameIndex.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260130232147_AddBaseItemNameIndex.cs new file mode 100644 index 0000000000..da57c71662 --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260130232147_AddBaseItemNameIndex.cs @@ -0,0 +1,45 @@ +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace Jellyfin.Database.Providers.Sqlite.Migrations +{ + /// + public partial class AddBaseItemNameIndex : Migration + { + /// + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.CreateIndex( + name: "IX_UserData_UserId_IsFavorite_ItemId", + table: "UserData", + columns: new[] { "UserId", "IsFavorite", "ItemId" }); + + migrationBuilder.CreateIndex( + name: "IX_UserData_UserId_Played_ItemId", + table: "UserData", + columns: new[] { "UserId", "Played", "ItemId" }); + + migrationBuilder.CreateIndex( + name: "IX_BaseItems_Name", + table: "BaseItems", + column: "Name"); + } + + /// + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropIndex( + name: "IX_UserData_UserId_IsFavorite_ItemId", + table: "UserData"); + + migrationBuilder.DropIndex( + name: "IX_UserData_UserId_Played_ItemId", + table: "UserData"); + + migrationBuilder.DropIndex( + name: "IX_BaseItems_Name", + 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 c7d884f15e..865091530b 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs @@ -362,6 +362,8 @@ namespace Jellyfin.Server.Implementations.Migrations b.HasIndex("ExtraType"); + b.HasIndex("Name"); + b.HasIndex("OwnerId"); b.HasIndex("ParentId"); @@ -1446,8 +1448,12 @@ namespace Jellyfin.Server.Implementations.Migrations 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); -- cgit v1.2.3