diff options
7 files changed, 2717 insertions, 108 deletions
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs index bfd0fac34a..9a42c86f7d 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs @@ -8,7 +8,7 @@ using Jellyfin.Database.Implementations.MatchCriteria; namespace Jellyfin.Database.Implementations; /// <summary> -/// Provides methods for querying item hierarchies using iterative traversal. +/// Provides methods for querying item hierarchies. /// Uses AncestorIds and LinkedChildren tables for parent-child traversal. /// </summary> public static class DescendantQueryHelper @@ -32,11 +32,18 @@ public static class DescendantQueryHelper { ArgumentNullException.ThrowIfNull(context); - var descendants = TraverseHierarchyDown(context, [parentId]); + var (closureRoots, linkRoots) = ResolveLinkedRoots(context, parentId); - descendants.Remove(parentId); + var hierarchyDescendants = ClosureDescendants(context, closureRoots); - return descendants.AsQueryable(); + var linkedDescendants = context.LinkedChildren + .WhereOneOrMany(linkRoots, e => e.ParentId) + .Select(e => e.ChildId); + + return hierarchyDescendants + .Concat(linkedDescendants) + .Where(e => !e.Equals(parentId)) + .Distinct(); } /// <summary> @@ -51,11 +58,9 @@ public static class DescendantQueryHelper { ArgumentNullException.ThrowIfNull(context); - var descendants = TraverseHierarchyDownOwned(context, [parentId]); - - descendants.Remove(parentId); - - return descendants.AsQueryable(); + return ClosureDescendants(context, [parentId]) + .Where(e => !e.Equals(parentId)) + .Distinct(); } /// <summary> @@ -76,11 +81,12 @@ public static class DescendantQueryHelper return []; } - var seedSet = new HashSet<Guid>(parentIds); - var descendants = TraverseHierarchyDownOwned(context, seedSet); + var descendants = ClosureDescendants(context, parentIds) + .Distinct() + .ToHashSet(); - // Remove the seed IDs — callers want only descendants - descendants.ExceptWith(seedSet); + // The callers want only descendants, and an item is never its own descendant. + descendants.ExceptWith(parentIds); return descendants; } @@ -96,28 +102,48 @@ public static class DescendantQueryHelper { ArgumentNullException.ThrowIfNull(context); ArgumentNullException.ThrowIfNull(criteria); + var matchingItemIds = criteria switch { HasSubtitles => context.MediaStreamInfos .Where(ms => ms.StreamType == MediaStreamTypeEntity.Subtitle) - .Select(ms => ms.ItemId) - .Distinct() - .ToHashSet(), + .Select(ms => ms.ItemId), HasChapterImages => context.Chapters .Where(c => c.ImagePath != null) - .Select(c => c.ItemId) - .Distinct() - .ToHashSet(), + .Select(c => c.ItemId), HasMediaStreamType m => GetMatchingMediaStreamItemIds(context, m), _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}") }; - var ancestors = TraverseHierarchyUp(context, matchingItemIds); + // One hop up the closure covers every ancestor level. + var hierarchyAncestors = context.AncestorIds + .Where(e => matchingItemIds.Contains(e.ItemId)) + .Select(e => e.ParentItemId); - return ancestors.AsQueryable(); + var linkParents = ResolveLinkParents(context, matchingItemIds, hierarchyAncestors); + + // The link parents are resolved ids, so they are read back as a sub-select to keep the result + // composable. An id without a BaseItem row could never match a caller's row anyway. + var linkedParents = context.BaseItems + .WhereOneOrMany(linkParents, e => e.Id) + .Select(e => e.Id); + + var linkedParentAncestors = context.AncestorIds + .WhereOneOrMany(linkParents, e => e.ItemId) + .Select(e => e.ParentItemId); + + var seamAncestors = context.AncestorIds + .Where(e => hierarchyAncestors.Contains(e.ItemId) || linkedParentAncestors.Contains(e.ItemId)) + .Select(e => e.ParentItemId); + + return hierarchyAncestors + .Concat(linkedParents) + .Concat(linkedParentAncestors) + .Concat(seamAncestors) + .Distinct(); } - private static HashSet<Guid> GetMatchingMediaStreamItemIds(JellyfinDbContext context, HasMediaStreamType criteria) + private static IQueryable<Guid> GetMatchingMediaStreamItemIds(JellyfinDbContext context, HasMediaStreamType criteria) { var query = context.MediaStreamInfos .Where(ms => ms.StreamType == criteria.StreamType @@ -130,130 +156,131 @@ public static class DescendantQueryHelper query = query.Where(ms => ms.IsExternal == isExternal); } - return query.Select(ms => ms.ItemId).Distinct().ToHashSet(); + return query.Select(ms => ms.ItemId); } - /// <summary> - /// Traverses DOWN the hierarchy from parent folders to find all descendants. - /// </summary> - private static HashSet<Guid> TraverseHierarchyDown(JellyfinDbContext context, ICollection<Guid> startIds) + private static IQueryable<Guid> ClosureDescendants(JellyfinDbContext context, IReadOnlyList<Guid> roots) { - var visited = new HashSet<Guid>(startIds); - var folderStack = new HashSet<Guid>(startIds); + var direct = context.AncestorIds + .WhereOneOrMany(roots, e => e.ParentItemId) + .Select(e => e.ItemId); - while (folderStack.Count != 0) - { - var currentFolders = folderStack.ToArray(); - folderStack.Clear(); + // An item carries its own chain plus its collection folders, never the UserRootFolder. + var indirect = context.AncestorIds + .Where(e => direct.Contains(e.ParentItemId)) + .Select(e => e.ItemId); - var directChildren = context.AncestorIds - .WhereOneOrMany(currentFolders, e => e.ParentItemId) - .Select(e => e.ItemId) - .ToArray(); + return direct.Concat(indirect); + } - var linkedChildren = context.LinkedChildren - .WhereOneOrMany(currentFolders, e => e.ParentId) - .Select(e => e.ChildId) - .ToArray(); + /// <summary> + /// Resolves every folder that reaches one of the matching items through a linked edge. + /// </summary> + /// <returns>The ids of the folders whose linked children lead, at any depth, to a matching item.</returns> + private static List<Guid> ResolveLinkParents(JellyfinDbContext context, IQueryable<Guid> matchingItemIds, IQueryable<Guid> ancestorsOfMatches) + { + // A link sits above the closure as well as above another link: a BoxSet holds a Series whose + // episode matches, and another BoxSet holds that BoxSet. So the hop repeats until it stops + // finding anything new, and each hop takes the links landing on the set itself or on a folder + // that contains it. Only folders owning linked children are ever collected, which bounds this + // by the number of BoxSets and Playlists rather than by the item count. + var resolved = context.LinkedChildren + .Where(e => matchingItemIds.Contains(e.ChildId) || ancestorsOfMatches.Contains(e.ChildId)) + .Select(e => e.ParentId) + .Distinct() + .ToHashSet(); + + var frontier = resolved.ToList(); + + while (frontier.Count != 0) + { + var containingFolders = context.AncestorIds + .WhereOneOrMany(frontier, e => e.ItemId) + .Select(e => e.ParentItemId); - var allChildren = directChildren.Concat(linkedChildren).Distinct().ToArray(); + var directLinkParents = context.LinkedChildren + .WhereOneOrMany(frontier, e => e.ChildId) + .Select(e => e.ParentId); - if (allChildren.Length == 0) - { - break; - } + var indirectLinkParents = context.LinkedChildren + .Where(e => containingFolders.Contains(e.ChildId)) + .Select(e => e.ParentId); - var childFolders = context.BaseItems - .WhereOneOrMany(allChildren, e => e.Id) - .Where(e => e.IsFolder) - .Select(e => e.Id) - .ToHashSet(); + var next = directLinkParents + .Concat(indirectLinkParents) + .Distinct() + .ToArray(); - foreach (var childId in allChildren) + frontier = []; + foreach (var id in next) { - if (visited.Add(childId) && childFolders.Contains(childId)) + // Cyclic links (a BoxSet holding itself, directly or not) terminate on the resolved set. + if (resolved.Add(id)) { - folderStack.Add(childId); + frontier.Add(id); } } } - return visited; + return [.. resolved]; } /// <summary> - /// Traverses DOWN the hierarchy using only AncestorIds (ownership), not LinkedChildren. + /// Resolves the roots the descendant sub-selects have to be anchored on. /// </summary> - private static HashSet<Guid> TraverseHierarchyDownOwned(JellyfinDbContext context, ICollection<Guid> startIds) + /// <returns> + /// The roots whose AncestorIds closure belongs to the result, and the roots whose LinkedChildren + /// belong to the result. + /// </returns> + private static (List<Guid> ClosureRoots, List<Guid> LinkRoots) ResolveLinkedRoots(JellyfinDbContext context, Guid parentId) { - var visited = new HashSet<Guid>(startIds); - var folderStack = new HashSet<Guid>(startIds); + // A folder found through the closure needs no closure hop of its own. + var closureRoots = new List<Guid> { parentId }; + var linkRoots = new List<Guid> { parentId }; + var visited = new HashSet<Guid> { parentId }; + var frontier = new List<Guid> { parentId }; - while (folderStack.Count != 0) + while (frontier.Count != 0) { - var currentFolders = folderStack.ToArray(); - folderStack.Clear(); + var closureIds = ClosureDescendants(context, frontier); - var directChildren = context.AncestorIds - .WhereOneOrMany(currentFolders, e => e.ParentItemId) - .Select(e => e.ItemId) - .ToArray(); + var linkedIds = context.LinkedChildren + .WhereOneOrMany(frontier, e => e.ParentId) + .Select(e => e.ChildId); - if (directChildren.Length == 0) - { - break; - } + // Folders that own linked children, i.e. the only items whose links are worth following. + var linkOwners = context.BaseItems + .Where(e => e.IsFolder + && (closureIds.Contains(e.Id) || linkedIds.Contains(e.Id)) + && context.LinkedChildren.Any(l => l.ParentId.Equals(e.Id))) + .Select(e => e.Id) + .ToArray(); - var childFolders = context.BaseItems - .WhereOneOrMany(directChildren, e => e.Id) - .Where(e => e.IsFolder) + var linkedFolders = context.BaseItems + .Where(e => e.IsFolder && linkedIds.Contains(e.Id)) .Select(e => e.Id) .ToHashSet(); - foreach (var childId in directChildren) + frontier = []; + foreach (var id in linkOwners.Concat(linkedFolders)) { - if (visited.Add(childId) && childFolders.Contains(childId)) + if (!visited.Add(id)) { - folderStack.Add(childId); + continue; } - } - } - - return visited; - } - - /// <summary> - /// Traverses UP the hierarchy from items to find all ancestor folders. - /// </summary> - private static HashSet<Guid> TraverseHierarchyUp(JellyfinDbContext context, ICollection<Guid> startIds) - { - var ancestors = new HashSet<Guid>(); - var itemStack = new HashSet<Guid>(startIds); - while (itemStack.Count != 0) - { - var currentItems = itemStack.ToArray(); - itemStack.Clear(); + frontier.Add(id); + linkRoots.Add(id); - var ancestorParents = context.AncestorIds - .WhereOneOrMany(currentItems, e => e.ItemId) - .Select(e => e.ParentItemId) - .ToArray(); - - var linkedParents = context.LinkedChildren - .WhereOneOrMany(currentItems, e => e.ChildId) - .Select(e => e.ParentId) - .ToArray(); - - foreach (var parentId in ancestorParents.Concat(linkedParents)) - { - if (ancestors.Add(parentId)) + // Only a folder reached through a link contributes a closure that is not covered by + // the roots already collected. + if (linkedFolders.Contains(id)) { - itemStack.Add(parentId); + closureRoots.Add(id); } } } - return ancestors; + return (closureRoots, linkRoots); } } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs index afa9eee363..fc4e96cf8a 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs @@ -13,5 +13,6 @@ public class MediaStreamInfoConfiguration : IEntityTypeConfiguration<MediaStream public void Configure(EntityTypeBuilder<MediaStreamInfo> builder) { builder.HasKey(e => new { e.ItemId, e.StreamIndex }); + builder.HasIndex(e => new { e.StreamType, e.ItemId }); } } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260811145224_AddMediaStreamTypeItemIdIndex.Designer.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260811145224_AddMediaStreamTypeItemIdIndex.Designer.cs new file mode 100644 index 0000000000..79362985be --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260811145224_AddMediaStreamTypeItemIdIndex.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.Database.Providers.Sqlite.Migrations +{ + [DbContext(typeof(JellyfinDbContext))] + [Migration("20260811145224_AddMediaStreamTypeItemIdIndex")] + partial class AddMediaStreamTypeItemIdIndex + { + /// <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<int>("SortOrder") + .HasColumnType("INTEGER"); + + b.Property<Guid>("ChildId") + .HasColumnType("TEXT"); + + b.Property<int>("ChildType") + .HasColumnType("INTEGER"); + + b.HasKey("ParentId", "SortOrder"); + + b.HasIndex("ChildId", "ChildType"); + + b.HasIndex("ParentId", "ChildType"); + + 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.HasIndex("StreamType", "ItemId"); + + 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("ItemId", "ListOrder"); + + b.HasIndex("ItemId", "SortOrder"); + + b.HasIndex("PeopleId", "ItemId"); + + 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/20260811145224_AddMediaStreamTypeItemIdIndex.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260811145224_AddMediaStreamTypeItemIdIndex.cs new file mode 100644 index 0000000000..e8f0514952 --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260811145224_AddMediaStreamTypeItemIdIndex.cs @@ -0,0 +1,27 @@ +using Microsoft.EntityFrameworkCore.Migrations; + +#nullable disable + +namespace Jellyfin.Database.Providers.Sqlite.Migrations +{ + /// <inheritdoc /> + public partial class AddMediaStreamTypeItemIdIndex : Migration + { + /// <inheritdoc /> + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.CreateIndex( + name: "IX_MediaStreamInfos_StreamType_ItemId", + table: "MediaStreamInfos", + columns: ["StreamType", "ItemId"]); + } + + /// <inheritdoc /> + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropIndex( + name: "IX_MediaStreamInfos_StreamType_ItemId", + table: "MediaStreamInfos"); + } + } +} 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 cdf5c84826..ca19a85fc1 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs @@ -1012,6 +1012,8 @@ namespace Jellyfin.Server.Implementations.Migrations b.HasKey("ItemId", "StreamIndex"); + b.HasIndex("StreamType", "ItemId"); + b.ToTable("MediaStreamInfos"); b.HasAnnotation("Sqlite:UseSqlReturningClause", false); diff --git a/tests/Jellyfin.Server.Implementations.Tests/Item/BaseItemRepositoryStreamFilterTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Item/BaseItemRepositoryStreamFilterTests.cs new file mode 100644 index 0000000000..717085b440 --- /dev/null +++ b/tests/Jellyfin.Server.Implementations.Tests/Item/BaseItemRepositoryStreamFilterTests.cs @@ -0,0 +1,224 @@ +using System; +using System.Linq; +using Emby.Server.Implementations.Data; +using Jellyfin.Database.Implementations; +using Jellyfin.Database.Implementations.Entities; +using Jellyfin.Database.Implementations.Locking; +using Jellyfin.Database.Providers.Sqlite; +using Jellyfin.Server.Implementations.Item; +using MediaBrowser.Controller; +using MediaBrowser.Controller.Configuration; +using MediaBrowser.Controller.Entities; +using MediaBrowser.Model.Configuration; +using Microsoft.Data.Sqlite; +using Microsoft.EntityFrameworkCore; +using Microsoft.Extensions.Logging.Abstractions; +using Moq; +using Xunit; +using LinkedChildType = Jellyfin.Database.Implementations.Entities.LinkedChildType; + +namespace Jellyfin.Server.Implementations.Tests.Item; + +/// <summary> +/// Covers the filters that resolve "folders with a matching descendant" through +/// <see cref="DescendantQueryHelper.GetFolderIdsMatching"/>, both in their positive and their +/// negated form, so the sub-selects they build stay translatable on the SQLite provider. +/// </summary> +public sealed class BaseItemRepositoryStreamFilterTests : IDisposable +{ + private const string FolderType = "MediaBrowser.Controller.Entities.Folder"; + private const string BoxSetType = "MediaBrowser.Controller.Entities.Movies.BoxSet"; + private const string MovieType = "MediaBrowser.Controller.Entities.Movies.Movie"; + + private readonly SqliteConnection _connection; + private readonly DbContextOptions<JellyfinDbContext> _dbOptions; + private readonly BaseItemRepository _repository; + + private readonly Guid _library = Guid.NewGuid(); + private readonly Guid _withSubtitles = Guid.NewGuid(); + private readonly Guid _withoutSubtitles = Guid.NewGuid(); + private readonly Guid _collection = Guid.NewGuid(); + private readonly Guid _linkedSeries = Guid.NewGuid(); + private readonly Guid _linkedEpisode = Guid.NewGuid(); + + public BaseItemRepositoryStreamFilterTests() + { + _connection = new SqliteConnection("Data Source=:memory:"); + _connection.Open(); + + _dbOptions = new DbContextOptionsBuilder<JellyfinDbContext>() + .UseSqlite(_connection) + .Options; + + using (var ctx = CreateDbContext()) + { + ctx.Database.EnsureCreated(); + Seed(ctx); + } + + var factory = new Mock<IDbContextFactory<JellyfinDbContext>>(); + factory.Setup(f => f.CreateDbContext()).Returns(CreateDbContext); + + var serverConfigurationManager = new Mock<IServerConfigurationManager>(); + serverConfigurationManager.Setup(c => c.Configuration).Returns(new ServerConfiguration()); + + _repository = new BaseItemRepository( + factory.Object, + new Mock<IServerApplicationHost>().Object, + new ItemTypeLookup(), + serverConfigurationManager.Object, + NullLogger<BaseItemRepository>.Instance); + } + + public void Dispose() => _connection.Dispose(); + + [Fact] + public void HasSubtitles_MatchesTheItemAndItsParentFolder() + { + var ids = _repository.GetItemIdsList(new InternalItemsQuery { HasSubtitles = true }); + + Assert.Contains(_withSubtitles, ids); + // The library is a folder, and it has a descendant with subtitles. + Assert.Contains(_library, ids); + Assert.DoesNotContain(_withoutSubtitles, ids); + } + + [Fact] + public void HasSubtitles_Negated_ExcludesTheItemAndItsParentFolder() + { + var ids = _repository.GetItemIdsList(new InternalItemsQuery { HasSubtitles = false }); + + Assert.Contains(_withoutSubtitles, ids); + Assert.DoesNotContain(_withSubtitles, ids); + Assert.DoesNotContain(_library, ids); + } + + [Fact] + public void SubtitleLanguages_MatchesTheRequestedLanguageOnly() + { + Assert.Contains(_withSubtitles, _repository.GetItemIdsList(new InternalItemsQuery { SubtitleLanguages = ["ger"] })); + Assert.DoesNotContain(_withSubtitles, _repository.GetItemIdsList(new InternalItemsQuery { SubtitleLanguages = ["fre"] })); + } + + [Fact] + public void HasNoSubtitleTrackWithLanguage_ExcludesTheMatchingItemAndFolder() + { + var ids = _repository.GetItemIdsList(new InternalItemsQuery { HasNoSubtitleTrackWithLanguage = "ger" }); + + Assert.Contains(_withoutSubtitles, ids); + Assert.DoesNotContain(_withSubtitles, ids); + Assert.DoesNotContain(_library, ids); + } + + [Fact] + public void HasSubtitles_MatchesACollectionLinkingAFolder() + { + var ids = _repository.GetItemIdsList(new InternalItemsQuery { HasSubtitles = true }); + + // The collection links the series, and the subtitles hang off the series' episode. + Assert.Contains(_linkedSeries, ids); + Assert.Contains(_collection, ids); + } + + [Fact] + public void HasSubtitles_Negated_ExcludesACollectionLinkingAFolder() + { + var ids = _repository.GetItemIdsList(new InternalItemsQuery { HasSubtitles = false }); + + Assert.DoesNotContain(_linkedSeries, ids); + Assert.DoesNotContain(_collection, ids); + } + + [Fact] + public void HasChapterImages_MatchesTheItemAndItsParentFolder() + { + var ids = _repository.GetItemIdsList(new InternalItemsQuery { HasChapterImages = true }); + + Assert.Contains(_withSubtitles, ids); + Assert.Contains(_library, ids); + Assert.DoesNotContain(_withoutSubtitles, ids); + } + + private void Seed(JellyfinDbContext context) + { + context.BaseItems.Add(new BaseItemEntity { Id = _library, Type = FolderType, Name = "Library", IsFolder = true }); + context.BaseItems.Add(new BaseItemEntity { Id = _withSubtitles, Type = MovieType, Name = "With subtitles" }); + context.BaseItems.Add(new BaseItemEntity { Id = _withoutSubtitles, Type = MovieType, Name = "Without subtitles" }); + + foreach (var itemId in new[] { _withSubtitles, _withoutSubtitles }) + { + context.AncestorIds.Add(new AncestorId + { + ItemId = itemId, + ParentItemId = _library, + Item = null!, + ParentItem = null! + }); + + context.MediaStreamInfos.Add(new MediaStreamInfo + { + ItemId = itemId, + StreamIndex = 0, + StreamType = MediaStreamTypeEntity.Video, + Item = null! + }); + } + + context.MediaStreamInfos.Add(new MediaStreamInfo + { + ItemId = _withSubtitles, + StreamIndex = 1, + StreamType = MediaStreamTypeEntity.Subtitle, + Language = "ger", + Item = null! + }); + + // A collection linking a folder: the match is two edges away, one link then one closure hop. + context.BaseItems.Add(new BaseItemEntity { Id = _collection, Type = BoxSetType, Name = "Collection", IsFolder = true }); + context.BaseItems.Add(new BaseItemEntity { Id = _linkedSeries, Type = FolderType, Name = "Linked series", IsFolder = true }); + context.BaseItems.Add(new BaseItemEntity { Id = _linkedEpisode, Type = MovieType, Name = "Linked episode" }); + + context.AncestorIds.Add(new AncestorId + { + ItemId = _linkedEpisode, + ParentItemId = _linkedSeries, + Item = null!, + ParentItem = null! + }); + + context.LinkedChildren.Add(new LinkedChildEntity + { + ParentId = _collection, + ChildId = _linkedSeries, + ChildType = LinkedChildType.Manual, + SortOrder = 0 + }); + + context.MediaStreamInfos.Add(new MediaStreamInfo + { + ItemId = _linkedEpisode, + StreamIndex = 0, + StreamType = MediaStreamTypeEntity.Subtitle, + Language = "ger", + Item = null! + }); + + context.Chapters.Add(new Chapter + { + ItemId = _withSubtitles, + ChapterIndex = 0, + StartPositionTicks = 0, + ImagePath = "/chapter.jpg", + Item = null! + }); + + context.SaveChanges(); + } + + private JellyfinDbContext CreateDbContext() + => new JellyfinDbContext( + _dbOptions, + NullLogger<JellyfinDbContext>.Instance, + new SqliteDatabaseProvider(null!, NullLogger<SqliteDatabaseProvider>.Instance), + new NoLockBehavior(NullLogger<NoLockBehavior>.Instance)); +} diff --git a/tests/Jellyfin.Server.Implementations.Tests/Item/DescendantQueryHelperTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Item/DescendantQueryHelperTests.cs new file mode 100644 index 0000000000..fae2dd0628 --- /dev/null +++ b/tests/Jellyfin.Server.Implementations.Tests/Item/DescendantQueryHelperTests.cs @@ -0,0 +1,515 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using Jellyfin.Database.Implementations; +using Jellyfin.Database.Implementations.Entities; +using Jellyfin.Database.Implementations.Locking; +using Jellyfin.Database.Implementations.MatchCriteria; +using Jellyfin.Database.Providers.Sqlite; +using Microsoft.Data.Sqlite; +using Microsoft.EntityFrameworkCore; +using Microsoft.Extensions.Logging.Abstractions; +using Xunit; + +namespace Jellyfin.Server.Implementations.Tests.Item; + +/// <summary> +/// Verifies the descendant traversals against the SQLite provider: the sets they resolve, and that +/// they stay sub-selects instead of inlining every descendant id into the statement. +/// </summary> +public sealed class DescendantQueryHelperTests : IDisposable +{ + private const string FolderType = "MediaBrowser.Controller.Entities.Folder"; + private const string BoxSetType = "MediaBrowser.Controller.Entities.Movies.BoxSet"; + private const string MovieType = "MediaBrowser.Controller.Entities.Movies.Movie"; + + private readonly Dictionary<Guid, int> _linkCounters = new(); + private readonly SqliteConnection _connection; + private readonly DbContextOptions<JellyfinDbContext> _dbOptions; + + public DescendantQueryHelperTests() + { + _connection = new SqliteConnection("Data Source=:memory:"); + _connection.Open(); + + _dbOptions = new DbContextOptionsBuilder<JellyfinDbContext>() + .UseSqlite(_connection) + .Options; + + using var ctx = CreateDbContext(); + ctx.Database.EnsureCreated(); + } + + public void Dispose() => _connection.Dispose(); + + [Fact] + public void GetAllDescendantIds_Hierarchy_ReturnsEveryLevelWithoutTheParent() + { + var library = Guid.NewGuid(); + var series = Guid.NewGuid(); + var season = Guid.NewGuid(); + var episode = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddFolder(ctx, library); + AddFolder(ctx, series); + AddFolder(ctx, season); + AddItem(ctx, episode, MovieType); + + // AncestorIds is a closure: production writes one row per ancestor, not just the parent. + AddAncestors(ctx, series, library); + AddAncestors(ctx, season, series, library); + AddAncestors(ctx, episode, season, series, library); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var descendants = DescendantQueryHelper.GetAllDescendantIds(ctx, library).ToHashSet(); + + Assert.Equal(new[] { series, season, episode }.Order(), descendants.Order()); + Assert.DoesNotContain(library, descendants); + } + } + + [Fact] + public void GetAllDescendantIds_LinkedFolder_IncludesItsOwnDescendants() + { + var boxSet = Guid.NewGuid(); + var series = Guid.NewGuid(); + var episode = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddItem(ctx, boxSet, BoxSetType, isFolder: true); + AddFolder(ctx, series); + AddItem(ctx, episode, MovieType); + + AddAncestors(ctx, episode, series); + AddLink(ctx, boxSet, series); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var descendants = DescendantQueryHelper.GetAllDescendantIds(ctx, boxSet).ToHashSet(); + + // The link reaches the series, and the series' own closure reaches the episode. + Assert.Contains(series, descendants); + Assert.Contains(episode, descendants); + } + } + + // Timeout so that a missing termination guard fails the test instead of hanging the run. + [Fact(Timeout = 30000)] + public void GetAllDescendantIds_NestedLinks_AreFollowedAndCyclesTerminate() + { + var outer = Guid.NewGuid(); + var inner = Guid.NewGuid(); + var movie = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddItem(ctx, outer, BoxSetType, isFolder: true); + AddItem(ctx, inner, BoxSetType, isFolder: true); + AddItem(ctx, movie, MovieType); + + AddLink(ctx, outer, inner); + AddLink(ctx, inner, movie); + // Cycle back to the outer set: the traversal must not spin on it. + AddLink(ctx, inner, outer); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var descendants = DescendantQueryHelper.GetAllDescendantIds(ctx, outer).ToHashSet(); + + Assert.Contains(inner, descendants); + Assert.Contains(movie, descendants); + Assert.DoesNotContain(outer, descendants); + } + } + + [Fact] + public void GetAllDescendantIds_LinksOfNonFolders_AreNotFollowed() + { + var library = Guid.NewGuid(); + var movie = Guid.NewGuid(); + var alternateVersion = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddFolder(ctx, library); + AddItem(ctx, movie, MovieType); + AddItem(ctx, alternateVersion, MovieType); + + AddAncestors(ctx, movie, library); + // An alternate version hangs off the movie by link, and the movie is not a folder. + AddLink(ctx, movie, alternateVersion); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var descendants = DescendantQueryHelper.GetAllDescendantIds(ctx, library).ToHashSet(); + + Assert.Contains(movie, descendants); + Assert.DoesNotContain(alternateVersion, descendants); + } + } + + [Fact] + public void GetAllDescendantIds_ClosureSeamAboveTheCollectionFolder_IsCrossed() + { + var userRoot = Guid.NewGuid(); + var collectionFolder = Guid.NewGuid(); + var series = Guid.NewGuid(); + var episode = Guid.NewGuid(); + var boxSet = Guid.NewGuid(); + var linkedMovie = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddFolder(ctx, userRoot); + AddFolder(ctx, collectionFolder); + AddFolder(ctx, series); + AddItem(ctx, episode, MovieType); + AddItem(ctx, boxSet, BoxSetType, isFolder: true); + AddItem(ctx, linkedMovie, MovieType); + + // How production writes it: an item carries its own chain plus its collection folder, but + // not the user root above that folder - so one hop from the user root stops there. + AddAncestors(ctx, collectionFolder, userRoot); + AddAncestors(ctx, series, collectionFolder); + AddAncestors(ctx, episode, series, collectionFolder); + AddAncestors(ctx, boxSet, collectionFolder); + // The box set is only reachable across the seam, and its links have to be followed too. + AddLink(ctx, boxSet, linkedMovie); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var descendants = DescendantQueryHelper.GetAllDescendantIds(ctx, userRoot).ToHashSet(); + + Assert.Equal( + new[] { collectionFolder, series, episode, boxSet, linkedMovie }.Order(), + descendants.Order()); + } + } + + [Fact] + public void GetOwnedDescendantIds_ClosureSeamAboveTheCollectionFolder_IsCrossed() + { + var userRoot = Guid.NewGuid(); + var collectionFolder = Guid.NewGuid(); + var series = Guid.NewGuid(); + var episode = Guid.NewGuid(); + var boxSet = Guid.NewGuid(); + var linkedMovie = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddFolder(ctx, userRoot); + AddFolder(ctx, collectionFolder); + AddFolder(ctx, series); + AddItem(ctx, episode, MovieType); + AddItem(ctx, boxSet, BoxSetType, isFolder: true); + AddItem(ctx, linkedMovie, MovieType); + + AddAncestors(ctx, collectionFolder, userRoot); + AddAncestors(ctx, series, collectionFolder); + AddAncestors(ctx, episode, series, collectionFolder); + AddAncestors(ctx, boxSet, collectionFolder); + AddLink(ctx, boxSet, linkedMovie); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + // Owned only: the linked movie stays out, or deleting a library would delete it. + var expected = new[] { collectionFolder, series, episode, boxSet }.Order(); + + Assert.Equal(expected, DescendantQueryHelper.GetOwnedDescendantIds(ctx, userRoot).ToHashSet().Order()); + Assert.Equal(expected, DescendantQueryHelper.GetOwnedDescendantIdsBatch(ctx, [userRoot]).Order()); + } + } + + [Fact] + public void GetFolderIdsMatching_LinkAboveAClosure_ReturnsTheLinkingFolder() + { + var collections = Guid.NewGuid(); + var boxSet = Guid.NewGuid(); + var library = Guid.NewGuid(); + var series = Guid.NewGuid(); + var episode = Guid.NewGuid(); + var otherLibrary = Guid.NewGuid(); + var otherBoxSet = Guid.NewGuid(); + var silentMovie = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddFolder(ctx, collections); + AddItem(ctx, boxSet, BoxSetType, isFolder: true); + AddFolder(ctx, library); + AddFolder(ctx, series); + AddItem(ctx, episode, MovieType); + + AddAncestors(ctx, boxSet, collections); + AddAncestors(ctx, series, library); + AddAncestors(ctx, episode, series, library); + // The link lands on the series, not on the episode that carries the subtitles. + AddLink(ctx, boxSet, series); + AddStream(ctx, episode, MediaStreamTypeEntity.Subtitle); + + // A second collection, over an item without subtitles, must not be picked up. + AddFolder(ctx, otherLibrary); + AddItem(ctx, otherBoxSet, BoxSetType, isFolder: true); + AddItem(ctx, silentMovie, MovieType); + AddAncestors(ctx, otherBoxSet, collections); + AddAncestors(ctx, silentMovie, otherLibrary); + AddLink(ctx, otherBoxSet, silentMovie); + // A stream of another type: the criteria, not the mere presence of a stream, decides. + AddStream(ctx, silentMovie, MediaStreamTypeEntity.Video); + + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var folders = DescendantQueryHelper.GetFolderIdsMatching(ctx, new HasSubtitles()).ToHashSet(); + + Assert.Equal(new[] { library, series, boxSet, collections }.Order(), folders.Order()); + } + } + + [Fact(Timeout = 30000)] + public void GetFolderIdsMatching_NestedLinks_AreFollowedAndCyclesTerminate() + { + var outer = Guid.NewGuid(); + var inner = Guid.NewGuid(); + var movie = Guid.NewGuid(); + var silentSet = Guid.NewGuid(); + var silentMovie = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddItem(ctx, outer, BoxSetType, isFolder: true); + AddItem(ctx, inner, BoxSetType, isFolder: true); + AddItem(ctx, movie, MovieType); + + AddLink(ctx, outer, inner); + AddLink(ctx, inner, movie); + // Cycle back to the outer set: resolving the link parents must not spin on it. + AddLink(ctx, inner, outer); + AddStream(ctx, movie, MediaStreamTypeEntity.Subtitle); + + AddItem(ctx, silentSet, BoxSetType, isFolder: true); + AddItem(ctx, silentMovie, MovieType); + AddLink(ctx, silentSet, silentMovie); + AddStream(ctx, silentMovie, MediaStreamTypeEntity.Video); + + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var folders = DescendantQueryHelper.GetFolderIdsMatching(ctx, new HasSubtitles()).ToHashSet(); + + Assert.Equal(new[] { inner, outer }.Order(), folders.Order()); + } + } + + [Fact] + public void GetFolderIdsMatching_ClosureSeamAboveTheCollectionFolder_IsCrossed() + { + var userRoot = Guid.NewGuid(); + var collectionFolder = Guid.NewGuid(); + var series = Guid.NewGuid(); + var episode = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddFolder(ctx, userRoot); + AddFolder(ctx, collectionFolder); + AddFolder(ctx, series); + AddItem(ctx, episode, MovieType); + + // How production writes it: an item carries its own chain plus its collection folder, but + // not the user root above that folder - so the closure is not transitive at this seam. + AddAncestors(ctx, episode, series, collectionFolder); + AddAncestors(ctx, series, collectionFolder); + AddAncestors(ctx, collectionFolder, userRoot); + AddStream(ctx, episode, MediaStreamTypeEntity.Subtitle); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var folders = DescendantQueryHelper.GetFolderIdsMatching(ctx, new HasSubtitles()).ToHashSet(); + + Assert.Equal(new[] { series, collectionFolder, userRoot }.Order(), folders.Order()); + } + } + + [Fact] + public void GetFolderIdsMatching_LinkedFolder_MatchesOnLanguageOnly() + { + var boxSet = Guid.NewGuid(); + var series = Guid.NewGuid(); + var episode = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddItem(ctx, boxSet, BoxSetType, isFolder: true); + AddFolder(ctx, series); + AddItem(ctx, episode, MovieType); + + AddAncestors(ctx, episode, series); + AddLink(ctx, boxSet, series); + AddStream(ctx, episode, MediaStreamTypeEntity.Subtitle, "ger"); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + var german = new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, ["ger"]); + var french = new HasMediaStreamType(MediaStreamTypeEntity.Subtitle, ["fre"]); + + Assert.Equal(new[] { series, boxSet }.Order(), DescendantQueryHelper.GetFolderIdsMatching(ctx, german).ToHashSet().Order()); + Assert.Empty(DescendantQueryHelper.GetFolderIdsMatching(ctx, french).ToArray()); + } + } + + [Fact] + public void GetOwnedDescendantIds_IgnoresLinkedChildren() + { + var boxSet = Guid.NewGuid(); + var owned = Guid.NewGuid(); + var linked = Guid.NewGuid(); + + using (var ctx = CreateDbContext()) + { + AddItem(ctx, boxSet, BoxSetType, isFolder: true); + AddItem(ctx, owned, MovieType); + AddItem(ctx, linked, MovieType); + + AddAncestors(ctx, owned, boxSet); + AddLink(ctx, boxSet, linked); + ctx.SaveChanges(); + } + + using (var ctx = CreateDbContext()) + { + Assert.Equal([owned], DescendantQueryHelper.GetOwnedDescendantIds(ctx, boxSet).ToArray()); + Assert.Equal([owned], DescendantQueryHelper.GetOwnedDescendantIdsBatch(ctx, [boxSet]).ToArray()); + } + } + + [Fact] + public void GetAllDescendantIds_StatementSizeDoesNotGrowWithTheLibrary() + { + var small = SeedLibrary(10); + var large = SeedLibrary(500); + + using var ctx = CreateDbContext(); + + var smallSql = CountingQuery(ctx, small).ToQueryString(); + var largeSql = CountingQuery(ctx, large).ToQueryString(); + + // Reading the ids into memory and passing them back as AsQueryable() makes EF inline one + // literal per descendant, which is what made a large library allocate megabytes per call. + Assert.Equal(smallSql.Length, largeSql.Length); + Assert.Contains("AncestorIds", smallSql, StringComparison.Ordinal); + Assert.Equal(10, CountingQuery(ctx, small).Count()); + Assert.Equal(500, CountingQuery(ctx, large).Count()); + } + + private static IQueryable<BaseItemEntity> CountingQuery(JellyfinDbContext context, Guid libraryId) + { + var descendantIds = DescendantQueryHelper.GetAllDescendantIds(context, libraryId); + + return context.BaseItems + .AsNoTracking() + .Where(b => descendantIds.Contains(b.Id)) + .Where(DescendantQueryHelper.IsCountableLeaf); + } + + private Guid SeedLibrary(int childCount) + { + var library = Guid.NewGuid(); + + using var ctx = CreateDbContext(); + AddFolder(ctx, library); + for (var i = 0; i < childCount; i++) + { + var child = Guid.NewGuid(); + AddItem(ctx, child, MovieType); + AddAncestors(ctx, child, library); + } + + ctx.SaveChanges(); + + return library; + } + + private static void AddFolder(JellyfinDbContext context, Guid id) + => AddItem(context, id, FolderType, isFolder: true); + + private static void AddItem(JellyfinDbContext context, Guid id, string type, bool isFolder = false) + => context.BaseItems.Add(new BaseItemEntity + { + Id = id, + Type = type, + Name = type + " " + id, + IsFolder = isFolder + }); + + private static void AddStream(JellyfinDbContext context, Guid itemId, MediaStreamTypeEntity type, string? language = null) + => context.MediaStreamInfos.Add(new MediaStreamInfo + { + ItemId = itemId, + StreamIndex = 0, + StreamType = type, + Language = language, + Item = null! + }); + + private static void AddAncestors(JellyfinDbContext context, Guid itemId, params Guid[] ancestorIds) + { + foreach (var ancestorId in ancestorIds) + { + context.AncestorIds.Add(new AncestorId + { + ItemId = itemId, + ParentItemId = ancestorId, + Item = null!, + ParentItem = null! + }); + } + } + + // LinkedChildren is keyed on (ParentId, SortOrder), so every link of a parent needs its own slot. + private void AddLink(JellyfinDbContext context, Guid parentId, Guid childId) + { + _linkCounters.TryGetValue(parentId, out var sortOrder); + _linkCounters[parentId] = sortOrder + 1; + + context.LinkedChildren.Add(new LinkedChildEntity + { + ParentId = parentId, + ChildId = childId, + ChildType = LinkedChildType.Manual, + SortOrder = sortOrder + }); + } + + private JellyfinDbContext CreateDbContext() + => new JellyfinDbContext( + _dbOptions, + NullLogger<JellyfinDbContext>.Instance, + new SqliteDatabaseProvider(null!, NullLogger<SqliteDatabaseProvider>.Instance), + new NoLockBehavior(NullLogger<NoLockBehavior>.Instance)); +} |