diff options
Diffstat (limited to 'src/Jellyfin.Database/Jellyfin.Database.Implementations')
15 files changed, 384 insertions, 255 deletions
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs index 88a2c684ff..6b08f8dd7e 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs @@ -1,18 +1,27 @@ using System; using System.Collections.Generic; using System.Linq; +using System.Linq.Expressions; using Jellyfin.Database.Implementations.Entities; 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 { /// <summary> + /// Gets the predicate identifying items that count toward played/total aggregation: + /// real leaf media, i.e. neither folders nor virtual items (missing or unaired episodes). + /// Shared by the per-item and batched count paths so they cannot diverge. + /// </summary> + public static Expression<Func<BaseItemEntity, bool>> IsCountableLeaf { get; } = + b => !b.IsFolder && !b.IsVirtualItem; + + /// <summary> /// Gets a queryable of all descendant IDs for a parent item. /// Traverses AncestorIds and LinkedChildren to find all descendants. /// </summary> @@ -23,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> @@ -42,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> @@ -67,11 +81,11 @@ 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); + descendants.ExceptWith(parentIds); return descendants; } @@ -87,28 +101,106 @@ public static class DescendantQueryHelper { ArgumentNullException.ThrowIfNull(context); ArgumentNullException.ThrowIfNull(criteria); - var matchingItemIds = criteria switch + + // Both sides of a version group can hold a folder a caller would see as matching: the + // alternate carries its own AncestorIds rows and may sit in a different library than the + // primary it is reported against, and the primary is the item that becomes visible. + var reportedItemIds = MatchingMediaOwnerIds(context, criteria) + .Concat(GetPrimaryVersionIdsMatching(context, criteria)) + .Distinct(); + + // One hop up the closure covers every ancestor level. + var hierarchyAncestors = context.AncestorIds + .Where(e => reportedItemIds.Contains(e.ItemId)) + .Select(e => e.ParentItemId); + + var linkParents = ResolveLinkParents(context, reportedItemIds, hierarchyAncestors); + + // Read back as a sub-select so the result stays composable. Off the primary key, which is one + // row per id: LinkedChildren would yield one row per link and lean on the outer Distinct. + 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); + + // The chain an item carries stops at its collection folders, so this hop crosses that seam to + // the UserRootFolder above them. One statement for both sides beats a sub-select per side. + 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(); + } + + /// <summary> + /// Gets a queryable of the IDs of the primary versions whose alternate version's media matches the + /// criteria. + /// </summary> + /// <param name="context">Database context.</param> + /// <param name="criteria">The matching criteria to apply.</param> + /// <returns>Queryable of primary version item IDs.</returns> + /// <remarks> + /// For callers that already test an item's own media with their own indexed predicate: this covers + /// exactly what such a predicate misses, and the filtered PrimaryVersionId index keeps it to the few + /// items that have versions at all. + /// </remarks> + public static IQueryable<Guid> GetPrimaryVersionIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria) + { + ArgumentNullException.ThrowIfNull(context); + ArgumentNullException.ThrowIfNull(criteria); + + // Anchored on the alternates rather than on the matches: "has a primary version" is served by + // the partial PrimaryVersionId index, which holds only the few items that are second files, so + // this costs a seek each into the stream index instead of a second pass over every stream row. + var alternates = context.BaseItems.Where(v => v.PrimaryVersionId.HasValue); + + if (criteria is HasChapterImages) { - HasSubtitles => context.MediaStreamInfos - .Where(ms => ms.StreamType == MediaStreamTypeEntity.Subtitle) - .Select(ms => ms.ItemId) - .Distinct() - .ToHashSet(), - HasChapterImages => context.Chapters + return alternates + .Where(v => context.Chapters.Any(c => c.ItemId.Equals(v.Id) && c.ImagePath != null)) + .Select(v => v.PrimaryVersionId!.Value); + } + + var matchingStreams = MatchingMediaStreams(context, criteria); + + return alternates + .Where(v => matchingStreams.Any(ms => ms.ItemId.Equals(v.Id))) + .Select(v => v.PrimaryVersionId!.Value); + } + + // The ids of the items whose own media matches. Kept to the stream and chapter tables so their + // covering indexes answer this outright: projecting the BaseItems navigation instead would add a + // primary-key lookup per stream row rather than one per matching item, and the leading key of both + // indexes leaves the ids already grouped, so the Distinct costs no sort. + private static IQueryable<Guid> MatchingMediaOwnerIds(JellyfinDbContext context, FolderMatchCriteria criteria) + => criteria is HasChapterImages + ? context.Chapters .Where(c => c.ImagePath != null) .Select(c => c.ItemId) .Distinct() - .ToHashSet(), - HasMediaStreamType m => GetMatchingMediaStreamItemIds(context, m), + : MatchingMediaStreams(context, criteria) + .Select(ms => ms.ItemId) + .Distinct(); + + // The stream rows a criteria matches. One definition, so the owner projection and the alternate + // projection cannot drift apart despite reading it from opposite ends. + private static IQueryable<MediaStreamInfo> MatchingMediaStreams(JellyfinDbContext context, FolderMatchCriteria criteria) + => criteria switch + { + HasSubtitles => context.MediaStreamInfos + .Where(ms => ms.StreamType == MediaStreamTypeEntity.Subtitle), + HasMediaStreamType m => GetMatchingMediaStreams(context, m), _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}") }; - var ancestors = TraverseHierarchyUp(context, matchingItemIds); - - return ancestors.AsQueryable(); - } - - private static HashSet<Guid> GetMatchingMediaStreamItemIds(JellyfinDbContext context, HasMediaStreamType criteria) + private static IQueryable<MediaStreamInfo> GetMatchingMediaStreams(JellyfinDbContext context, HasMediaStreamType criteria) { var query = context.MediaStreamInfos .Where(ms => ms.StreamType == criteria.StreamType @@ -121,130 +213,128 @@ public static class DescendantQueryHelper query = query.Where(ms => ms.IsExternal == isExternal); } - return query.Select(ms => ms.ItemId).Distinct().ToHashSet(); + return query; } - /// <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(); + // Resolves the folders whose linked children lead, at any depth, to a matching item. + private static List<Guid> ResolveLinkParents(JellyfinDbContext context, IQueryable<Guid> matchingItemIds, IQueryable<Guid> ancestorsOfMatches) + { + // An alternate version is a second file for the item that links it, not a child of it, so that + // edge is not walked. It is also the one link a non-folder owns, and there is one per remuxed + // movie: walking it would swell this list from the BoxSet and Playlist count to the item count, + // and the list is bound into every statement the returned queryable is embedded in. + var containerLinks = context.LinkedChildren + .Where(e => e.ChildType != LinkedChildType.LocalAlternateVersion + && e.ChildType != LinkedChildType.LinkedAlternateVersion); + + // A link sits above the closure and above another link alike, so the hop repeats until nothing + // new turns up. + var resolved = containerLinks + .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 = containerLinks + .WhereOneOrMany(frontier, e => e.ChildId) + .Select(e => e.ParentId); - if (allChildren.Length == 0) - { - break; - } + var indirectLinkParents = containerLinks + .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 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. - /// </summary> - private static HashSet<Guid> TraverseHierarchyDownOwned(JellyfinDbContext context, ICollection<Guid> startIds) + // Resolves the roots the descendant sub-selects are anchored on: those contributing their closure, + // and those contributing their linked children. + 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); + 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; - } - - 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) + // Folders whose own links have to be followed. Driven off LinkedChildren because owning a + // link is the rare property, so the folder check only reaches rows that can qualify. That + // check stays: a non-folder owns links too (a movie and its alternate versions). + var linkOwners = context.LinkedChildren + .Where(e => (closureIds.Contains(e.ParentId) || linkedIds.Contains(e.ParentId)) + && e.Parent!.IsFolder) + .Select(e => e.ParentId) + .Distinct() + .ToArray(); + + 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(); - - var ancestorParents = context.AncestorIds - .WhereOneOrMany(currentItems, e => e.ItemId) - .Select(e => e.ParentItemId) - .ToArray(); + frontier.Add(id); + linkRoots.Add(id); - 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 adds a closure the roots so far do not cover. + if (linkedFolders.Contains(id)) { - itemStack.Add(parentId); + closureRoots.Add(id); } } } - return ancestors; + return (closureRoots, linkRoots); } } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs index 7361775711..be315f1b2c 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs @@ -25,7 +25,7 @@ public class LinkedChildEntity /// <summary> /// Gets or sets the sort order. /// </summary> - public int? SortOrder { get; set; } + public int SortOrder { get; set; } /// <summary> /// Gets or sets the parent item navigation property. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Permission.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Permission.cs index 84b86574cc..eae02dda1c 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Permission.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Permission.cs @@ -37,7 +37,7 @@ namespace Jellyfin.Database.Implementations.Entities /// <summary> /// Gets or sets the id of the associated user. /// </summary> - public Guid? UserId { get; set; } + public Guid UserId { get; set; } /// <summary> /// Gets the type of this permission. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Preference.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Preference.cs index c02ea7375a..9bd159f2cf 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Preference.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/Preference.cs @@ -35,7 +35,7 @@ namespace Jellyfin.Database.Implementations.Entities /// <summary> /// Gets or sets the id of the associated user. /// </summary> - public Guid? UserId { get; set; } + public Guid UserId { get; set; } /// <summary> /// Gets the type of this preference. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/User.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/User.cs index b10e210e5d..bf6568d10a 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/User.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/User.cs @@ -1,7 +1,6 @@ using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; -using System.ComponentModel.DataAnnotations.Schema; using System.Text.Json.Serialization; using Jellyfin.Database.Implementations.Enums; using Jellyfin.Database.Implementations.Interfaces; @@ -326,7 +325,6 @@ namespace Jellyfin.Database.Implementations.Entities /// <summary> /// Gets the list of permissions this user has. /// </summary> - [ForeignKey("Permission_Permissions_Guid")] public virtual ICollection<Permission> Permissions { get; private set; } /* @@ -339,7 +337,6 @@ namespace Jellyfin.Database.Implementations.Entities /// <summary> /// Gets the list of preferences this user has. /// </summary> - [ForeignKey("Preference_Preferences_Guid")] public virtual ICollection<Preference> Preferences { get; private set; } /// <inheritdoc/> diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Jellyfin.Database.Implementations.csproj b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Jellyfin.Database.Implementations.csproj index 0b29a71cbd..887ba114fc 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Jellyfin.Database.Implementations.csproj +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Jellyfin.Database.Implementations.csproj @@ -13,7 +13,6 @@ <PropertyGroup> <Authors>Jellyfin Contributors</Authors> <PackageId>Jellyfin.Database.Implementations</PackageId> - <VersionPrefix>10.11.0</VersionPrefix> <RepositoryUrl>https://github.com/jellyfin/jellyfin</RepositoryUrl> <PackageLicenseExpression>GPL-3.0-only</PackageLicenseExpression> </PropertyGroup> diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs index 1af7460540..0dfce732ce 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinQueryHelperExtensions.cs @@ -14,11 +14,17 @@ namespace Jellyfin.Database.Implementations; /// <summary> /// Contains a number of query related extensions. /// </summary> +/// <remarks> +/// Every helper here binds its values through <see cref="EF.Parameter{T}(T)"/>. Values embedded as bare +/// constants are inlined into the SQL as literals, which gives each distinct value its own entry in EF's +/// compiled query cache and its own statement for the database to plan. +/// </remarks> public static class JellyfinQueryHelperExtensions { private static readonly MethodInfo _containsMethodGenericCache = typeof(Enumerable).GetMethods(BindingFlags.Public | BindingFlags.Static).First(m => m.Name == nameof(Enumerable.Contains) && m.GetParameters().Length == 2); private static readonly MethodInfo _efParameterInstruction = typeof(EF).GetMethod(nameof(EF.Parameter), BindingFlags.Public | BindingFlags.Static)!; private static readonly ConcurrentDictionary<Type, MethodInfo> _containsQueryCache = new(); + private static readonly ConcurrentDictionary<Type, MethodInfo> _efParameterCache = new(); /// <summary> /// Builds an optimised query checking one property against a list of values while maintaining an optimal query. @@ -26,15 +32,69 @@ public static class JellyfinQueryHelperExtensions /// <typeparam name="TEntity">The entity.</typeparam> /// <typeparam name="TProperty">The property type to compare.</typeparam> /// <param name="query">The source query.</param> - /// <param name="oneOf">The list of items to check.</param> + /// <param name="oneOf">The list of items to check. An empty list matches nothing.</param> /// <param name="property">Property expression.</param> /// <returns>A Query.</returns> - public static IQueryable<TEntity> WhereOneOrMany<TEntity, TProperty>(this IQueryable<TEntity> query, IList<TProperty> oneOf, Expression<Func<TEntity, TProperty>> property) + public static IQueryable<TEntity> WhereOneOrMany<TEntity, TProperty>(this IQueryable<TEntity> query, IReadOnlyList<TProperty> oneOf, Expression<Func<TEntity, TProperty>> property) { return query.Where(OneOrManyExpressionBuilder(oneOf, property)); } /// <summary> + /// Builds an optimised query expression checking one property against a list of values while maintaining an optimal query. + /// </summary> + /// <typeparam name="TEntity">The entity.</typeparam> + /// <typeparam name="TProperty">The property type to compare.</typeparam> + /// <param name="oneOf">The list of items to check. An empty list matches nothing.</param> + /// <param name="property">Property expression.</param> + /// <returns>A Query.</returns> + public static Expression<Func<TEntity, bool>> OneOrManyExpressionBuilder<TEntity, TProperty>(this IReadOnlyList<TProperty> oneOf, Expression<Func<TEntity, TProperty>> property) + { + ArgumentNullException.ThrowIfNull(oneOf); + ArgumentNullException.ThrowIfNull(property); + + var parameter = Expression.Parameter(typeof(TEntity), "item"); + property = ParameterReplacer.Replace<Func<TEntity, TProperty>, Func<TEntity, TProperty>>(property, property.Parameters[0], parameter); + + if (oneOf.Count == 0) + { + // Fail closed, and without asking the database to unpack an empty collection to prove it. + return Expression.Lambda<Func<TEntity, bool>>(Expression.Constant(false), parameter); + } + + if (oneOf.Count == 1) + { + var value = Expression.Call( + null, + EfParameterFor(typeof(TProperty)), + Expression.Constant(oneOf[0], typeof(TProperty))); + + return Expression.Lambda<Func<TEntity, bool>>( + typeof(TProperty).IsValueType + ? Expression.Equal(property.Body, value) + : Expression.ReferenceEqual(property.Body, value), + parameter); + } + + var containsMethodInfo = _containsQueryCache.GetOrAdd(typeof(TProperty), static (key) => _containsMethodGenericCache.MakeGenericMethod(key)); + + // Binding the whole collection as one parameter keeps the statement identical for any element + // count, instead of emitting one placeholder per element. + return Expression.Lambda<Func<TEntity, bool>>( + Expression.Call( + null, + containsMethodInfo, + Expression.Call(null, EfParameterFor(oneOf.GetType()), Expression.Constant(oneOf)), + property.Body), + parameter); + } + + private static MethodInfo EfParameterFor(Type type) + { + return _efParameterCache.GetOrAdd(type, static (key) => _efParameterInstruction.MakeGenericMethod(key)); + } + + /// <summary> /// Builds a query that checks referenced ItemValues for a cross BaseItem lookup. /// </summary> /// <param name="baseQuery">The source query.</param> @@ -47,191 +107,161 @@ public static class JellyfinQueryHelperExtensions this IQueryable<BaseItemEntity> baseQuery, JellyfinDbContext context, ItemValueType itemValueType, - IList<Guid> referenceIds, + IReadOnlyList<Guid> referenceIds, bool invert = false) { - return baseQuery.Where(ReferencedItemFilterExpressionBuilder(context, itemValueType, referenceIds, invert)); + return baseQuery.WhereReferencedItem(context, [itemValueType], referenceIds, invert); } /// <summary> - /// Builds a query that checks referenced ItemValues for a cross BaseItem lookup. + /// Builds a query that checks referenced ItemValues of any of the given types for a cross BaseItem lookup. /// </summary> /// <param name="baseQuery">The source query.</param> /// <param name="context">The database context.</param> - /// <param name="itemValueTypes">The type of item value to reference.</param> + /// <param name="itemValueTypes">The types of item value to reference.</param> /// <param name="referenceIds">The list of BaseItem ids to check matches.</param> /// <param name="invert">If set an exclusion check is performed instead.</param> /// <returns>A Query.</returns> - public static IQueryable<BaseItemEntity> WhereReferencedItemMultipleTypes( + /// <remarks> + /// Matching is on CleanName alone. Genre/artist/album etc items do not set an ItemValue of their own + /// type, so the referenced item's Type is never consulted and ids whose names clean to the same value + /// are interchangeable across types. + /// </remarks> + public static IQueryable<BaseItemEntity> WhereReferencedItem( this IQueryable<BaseItemEntity> baseQuery, JellyfinDbContext context, - IList<ItemValueType> itemValueTypes, - IList<Guid> referenceIds, + IReadOnlyList<ItemValueType> itemValueTypes, + IReadOnlyList<Guid> referenceIds, bool invert = false) { - var itemFilter = OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, f => f.Id); - var typeFilter = OneOrManyExpressionBuilder<ItemValue, ItemValueType>(itemValueTypes, iv => iv.Type); - - return baseQuery.Where(item => - context.ItemValues - .Where(typeFilter) - .Join(context.ItemValuesMap, e => e.ItemValueId, e => e.ItemValueId, (itemVal, map) => new { itemVal, map }) - .Any(val => - context.BaseItems.Where(itemFilter).Any(e => e.CleanName == val.itemVal.CleanValue) - && val.map.ItemId == item.Id) == EF.Constant(!invert)); - } + ArgumentNullException.ThrowIfNull(context); - /// <summary> - /// Builds a query expression that checks referenced ItemValues for a cross BaseItem lookup. - /// </summary> - /// <param name="context">The database context.</param> - /// <param name="itemValueType">The type of item value to reference.</param> - /// <param name="referenceIds">The list of BaseItem ids to check matches.</param> - /// <param name="invert">If set an exclusion check is performed instead.</param> - /// <returns>A Query.</returns> - public static Expression<Func<BaseItemEntity, bool>> ReferencedItemFilterExpressionBuilder( - this JellyfinDbContext context, - ItemValueType itemValueType, - IList<Guid> referenceIds, - bool invert = false) - { - // Well genre/artist/album etc items do not actually set the ItemValue of thier specitic types so we cannot match it that way. - /* - "(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@GenreIds and Type=2)))" - */ - - var itemFilter = OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, f => f.Id); - - return item => - context.ItemValues - .Join(context.ItemValuesMap, e => e.ItemValueId, e => e.ItemValueId, (item, map) => new { item, map }) - .Any(val => - val.item.Type == itemValueType - && context.BaseItems.Where(itemFilter).Any(e => e.CleanName == val.item.CleanValue) - && val.map.ItemId == item.Id) == EF.Constant(!invert); + // Flat sub-selects rather than a correlated .Any(...Any(...)). + var referencedCleanValues = context.BaseItems + .Where(OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, e => e.Id)) + .Select(e => e.CleanName); + + var matchingItemIds = context.ItemValuesMap + .Where(OneOrManyExpressionBuilder<ItemValueMap, ItemValueType>(itemValueTypes, m => m.ItemValue.Type)) + .Where(m => referencedCleanValues.Contains(m.ItemValue.CleanValue)) + .Select(m => m.ItemId); + + return invert + ? baseQuery.Where(e => !matchingItemIds.Contains(e.Id)) + : baseQuery.Where(e => matchingItemIds.Contains(e.Id)); } /// <summary> - /// Filters items that match any of the specified (provider name, value) pairs. + /// Filters items that have any of the specified providers, optionally restricted to given values. /// </summary> /// <param name="baseQuery">The source query.</param> - /// <param name="providerIds">Dictionary mapping provider names to arrays of values to match.</param> + /// <param name="providerIds">Dictionary mapping provider names to values to match. An empty value array matches any value for that provider.</param> /// <returns>A filtered query.</returns> public static IQueryable<BaseItemEntity> WhereHasAnyProviderIds( this IQueryable<BaseItemEntity> baseQuery, IReadOnlyDictionary<string, string[]> providerIds) { - var providerKeys = providerIds - .SelectMany(kvp => kvp.Value.Select(v => $"{kvp.Key}:{v}")) - .ToList(); - - if (providerKeys.Count == 0) - { - return baseQuery; - } - - return baseQuery.Where(e => e.Provider!.Any(p => providerKeys.Contains(p.ProviderId + ":" + p.ProviderValue))); + return baseQuery.WhereProviderMatch(Flatten(providerIds), false); } /// <summary> - /// Filters items that have any of the specified providers. Empty/null values match any value for that provider. + /// Filters items that have any of the specified providers, optionally restricted to a given value. /// </summary> /// <param name="baseQuery">The source query.</param> - /// <param name="providerIds">Dictionary mapping provider names to optional values.</param> + /// <param name="providerIds">Dictionary mapping provider names to optional values. An empty value matches any value for that provider.</param> /// <returns>A filtered query.</returns> public static IQueryable<BaseItemEntity> WhereHasAnyProviderId( this IQueryable<BaseItemEntity> baseQuery, IReadOnlyDictionary<string, string> providerIds) { - var existenceOnly = providerIds - .Where(e => string.IsNullOrEmpty(e.Value)) - .Select(e => e.Key) - .ToList(); - - var specificValues = providerIds - .Where(e => !string.IsNullOrEmpty(e.Value)) - .Select(e => $"{e.Key}:{e.Value}") - .ToList(); - - if (existenceOnly.Count == 0 && specificValues.Count == 0) - { - return baseQuery; - } - - if (existenceOnly.Count == 0) - { - return baseQuery.Where(e => e.Provider!.Any(p => - specificValues.Contains(p.ProviderId + ":" + p.ProviderValue))); - } - - if (specificValues.Count == 0) - { - return baseQuery.Where(e => e.Provider!.Any(p => existenceOnly.Contains(p.ProviderId))); - } - - // Single EXISTS over Provider with both predicates OR'd, instead of two separate subqueries. - return baseQuery.Where(e => e.Provider!.Any(p => - existenceOnly.Contains(p.ProviderId) || - specificValues.Contains(p.ProviderId + ":" + p.ProviderValue))); + return baseQuery.WhereProviderMatch(providerIds, false); } /// <summary> - /// Excludes items that match any of the specified (provider name, value) pairs. + /// Excludes items that have any of the specified providers, optionally restricted to a given value. /// </summary> /// <param name="baseQuery">The source query.</param> - /// <param name="providerIds">Dictionary mapping provider names to values to exclude.</param> + /// <param name="providerIds">Dictionary mapping provider names to optional values. An empty value excludes any value for that provider.</param> /// <returns>A filtered query.</returns> public static IQueryable<BaseItemEntity> WhereExcludeProviderIds( this IQueryable<BaseItemEntity> baseQuery, IReadOnlyDictionary<string, string> providerIds) { - var excludeKeys = providerIds - .Select(e => $"{e.Key}:{e.Value}") - .ToList(); + return baseQuery.WhereProviderMatch(providerIds, true); + } + + private static IEnumerable<KeyValuePair<string, string>> Flatten(IReadOnlyDictionary<string, string[]> providerIds) + { + ArgumentNullException.ThrowIfNull(providerIds); - if (excludeKeys.Count == 0) + foreach (var (provider, values) in providerIds) { - return baseQuery; - } + if (values is null || values.Length == 0) + { + yield return new KeyValuePair<string, string>(provider, string.Empty); + continue; + } - return baseQuery.Where(e => e.Provider!.All(p => !excludeKeys.Contains(p.ProviderId + ":" + p.ProviderValue))); + foreach (var value in values) + { + yield return new KeyValuePair<string, string>(provider, value); + } + } } /// <summary> - /// Builds an optimised query expression checking one property against a list of values while maintaining an optimal query. + /// Matches items against a set of (provider, value) pairs, where an empty value means any value for + /// that provider. Emits a single EXISTS over the provider collection with the predicates OR'd, rather + /// than one subquery per predicate group. /// </summary> - /// <typeparam name="TEntity">The entity.</typeparam> - /// <typeparam name="TProperty">The property type to compare.</typeparam> - /// <param name="oneOf">The list of items to check.</param> - /// <param name="property">Property expression.</param> - /// <returns>A Query.</returns> - public static Expression<Func<TEntity, bool>> OneOrManyExpressionBuilder<TEntity, TProperty>(this IList<TProperty> oneOf, Expression<Func<TEntity, TProperty>> property) + private static IQueryable<BaseItemEntity> WhereProviderMatch( + this IQueryable<BaseItemEntity> baseQuery, + IEnumerable<KeyValuePair<string, string>> providerIds, + bool invert) { - var parameter = Expression.Parameter(typeof(TEntity), "item"); - property = ParameterReplacer.Replace<Func<TEntity, TProperty>, Func<TEntity, TProperty>>(property, property.Parameters[0], parameter); - if (oneOf.Count == 1) + ArgumentNullException.ThrowIfNull(providerIds); + + var existenceOnly = new List<string>(); + var specificValues = new List<string>(); + foreach (var (provider, value) in providerIds) { - var value = oneOf[0]; - if (typeof(TProperty).IsValueType) + if (string.IsNullOrEmpty(value)) { - return Expression.Lambda<Func<TEntity, bool>>(Expression.Equal(property.Body, Expression.Constant(value)), parameter); + existenceOnly.Add(provider); } else { - return Expression.Lambda<Func<TEntity, bool>>(Expression.ReferenceEqual(property.Body, Expression.Constant(value)), parameter); + specificValues.Add(provider + ":" + value); } } - var containsMethodInfo = _containsQueryCache.GetOrAdd(typeof(TProperty), static (key) => _containsMethodGenericCache.MakeGenericMethod(key)); - - // Threshold picked from microbenchmarks on SQLite: inline IN(const,...) beats a - // parameterized array lookup by ~5-10% up to ~32 elements. - if (oneOf.Count <= 32) + if (existenceOnly.Count == 0 && specificValues.Count == 0) { - return Expression.Lambda<Func<TEntity, bool>>(Expression.Call(null, containsMethodInfo, Expression.Constant(oneOf), property.Body), parameter); + return baseQuery; } - return Expression.Lambda<Func<TEntity, bool>>(Expression.Call(null, containsMethodInfo, Expression.Call(null, _efParameterInstruction.MakeGenericMethod(oneOf.GetType()), Expression.Constant(oneOf)), property.Body), parameter); + var predicate = ProviderPredicate(existenceOnly, specificValues); + + // NOT EXISTS rather than NOT IN: the latter yields no rows at all if the subquery can produce NULL. + return invert + ? baseQuery.Where(e => !e.Provider!.AsQueryable().Any(predicate)) + : baseQuery.Where(e => e.Provider!.AsQueryable().Any(predicate)); + } + + private static Expression<Func<BaseItemProvider, bool>> ProviderPredicate( + IReadOnlyList<string> existenceOnly, + IReadOnlyList<string> specificValues) + { + var byProvider = existenceOnly.OneOrManyExpressionBuilder<BaseItemProvider, string>(p => p.ProviderId); + var byPair = specificValues.OneOrManyExpressionBuilder<BaseItemProvider, string>(p => p.ProviderId + ":" + p.ProviderValue); + + // Both builders mint their own parameter; rebind so the two bodies can share one lambda. + var parameter = byProvider.Parameters[0]; + var reboundPair = ParameterReplacer.Replace<Func<BaseItemProvider, bool>, Func<BaseItemProvider, bool>>(byPair, byPair.Parameters[0], parameter); + + return Expression.Lambda<Func<BaseItemProvider, bool>>( + Expression.OrElse(byProvider.Body, reboundPair.Body), + parameter); } internal static class ParameterReplacer diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs index 76ffa5a9ea..29a073ff74 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/OptimisticLockBehavior.cs @@ -88,13 +88,13 @@ public class OptimisticLockBehavior : IEntityFrameworkCoreLockingBehavior /// <inheritdoc/> public void OnSaveChanges(JellyfinDbContext context, Action saveChanges) { - _writePolicy.ExecuteAndCapture(saveChanges); + _writePolicy.Execute(saveChanges); } /// <inheritdoc/> public async Task OnSaveChangesAsync(JellyfinDbContext context, Func<Task> saveChanges) { - await _writeAsyncPolicy.ExecuteAndCaptureAsync(saveChanges).ConfigureAwait(false); + await _writeAsyncPolicy.ExecuteAsync(saveChanges).ConfigureAwait(false); } private sealed class TransactionLockingInterceptor : DbTransactionInterceptor diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/PessimisticLockBehavior.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/PessimisticLockBehavior.cs index 404292e8eb..e7a7d5a53f 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/PessimisticLockBehavior.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Locking/PessimisticLockBehavior.cs @@ -17,6 +17,13 @@ namespace Jellyfin.Database.Implementations.Locking; /// <summary> /// A locking behavior that will always block any operation while a write is requested. Mimicks the old SqliteRepository behavior. /// </summary> +/// <remarks> +/// Unsafe with asynchronous transactions; because <see cref="ReaderWriterLockSlim"/> is +/// thread-affine, holding it from <c>TransactionStarting</c> to <c>TransactionCommitted</c> +/// works only while continuations resume inline. A genuinely-async continuation inside a +/// transaction releases on another thread, throwing +/// <see cref="SynchronizationLockException"/> or deadlocking a later write. +/// </remarks> public class PessimisticLockBehavior : IEntityFrameworkCoreLockingBehavior { private readonly ILogger<PessimisticLockBehavior> _logger; @@ -47,7 +54,8 @@ public class PessimisticLockBehavior : IEntityFrameworkCoreLockingBehavior /// <inheritdoc/> public void Initialise(DbContextOptionsBuilder optionsBuilder) { - _logger.LogInformation("The database locking mode has been set to: Pessimistic."); + _logger.LogWarning( + "The database locking mode has been set to: Pessimistic. This mode is not safe with asynchronous transactions and can deadlock."); optionsBuilder.AddInterceptors(new CommandLockingInterceptor(_loggerFactory.CreateLogger<CommandLockingInterceptor>())); optionsBuilder.AddInterceptors(new TransactionLockingInterceptor(_loggerFactory.CreateLogger<TransactionLockingInterceptor>())); } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs index 8556fb7bb3..ee36be035b 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs @@ -61,6 +61,11 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity> builder.HasIndex(e => new { e.TopParentId, e.MediaType, e.IsVirtualItem, e.DateCreated }); // resume builder.HasIndex(e => new { e.MediaType, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey }); + // alternate versions of an item, e.g. resolving the played date of a version onto its primary. + // Filtered: almost no item has a primary version, and an index covering those rows too would tempt + // the planner into serving "PrimaryVersionId IS NULL" - true for the whole library - out of it. + builder.HasIndex(e => e.PrimaryVersionId) + .HasFilter("\"PrimaryVersionId\" IS NOT NULL"); // sorted library queries (e.g., Series sorted by SortName) builder.HasIndex(e => new { e.Type, e.TopParentId, e.SortName }); // NextUp: per-series episode ordering (index seek + range scan on season/episode) diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs index 2abccd41f0..b4013a394f 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs @@ -13,8 +13,7 @@ public class LinkedChildConfiguration : IEntityTypeConfiguration<LinkedChildEnti public void Configure(EntityTypeBuilder<LinkedChildEntity> builder) { builder.ToTable("LinkedChildren"); - builder.HasKey(e => new { e.ParentId, e.ChildId }); - builder.HasIndex(e => new { e.ParentId, e.SortOrder }); + builder.HasKey(e => new { e.ParentId, e.SortOrder }); builder.HasIndex(e => new { e.ParentId, e.ChildType }); builder.HasIndex(e => new { e.ChildId, e.ChildType }); diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs index afa9eee363..48c537bbd3 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs @@ -13,5 +13,9 @@ public class MediaStreamInfoConfiguration : IEntityTypeConfiguration<MediaStream public void Configure(EntityTypeBuilder<MediaStreamInfo> builder) { builder.HasKey(e => new { e.ItemId, e.StreamIndex }); + + // Covering index for the stream filters. ItemId comes second because it is what they project and + // dedupe on; Language and IsExternal follow only to keep their predicates off the table. + builder.HasIndex(e => new { e.StreamType, e.ItemId, e.Language, e.IsExternal }); } } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs index 32ede86c96..7ebdbf4e8b 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs @@ -15,7 +15,7 @@ public class PeopleBaseItemMapConfiguration : IEntityTypeConfiguration<PeopleBas builder.HasKey(e => 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.HasIndex(e => new { e.PeopleId, e.ItemId }); builder.HasOne(e => e.Item); builder.HasOne(e => e.People); } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PermissionConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PermissionConfiguration.cs index d2aed54eb1..ae53a36724 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PermissionConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PermissionConfiguration.cs @@ -14,10 +14,8 @@ namespace Jellyfin.Database.Implementations.ModelConfiguration { // Used to get a user's permissions or a specific permission for a user. // Also prevents multiple values being created for a user. - // Filtered over non-null user ids for when other entities (groups, API keys) get permissions builder .HasIndex(p => new { p.UserId, p.Kind }) - .HasFilter("[UserId] IS NOT NULL") .IsUnique(); } } diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PreferenceConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PreferenceConfiguration.cs index 207051bcd1..5306078ed4 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PreferenceConfiguration.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PreferenceConfiguration.cs @@ -14,7 +14,6 @@ namespace Jellyfin.Database.Implementations.ModelConfiguration { builder .HasIndex(p => new { p.UserId, p.Kind }) - .HasFilter("[UserId] IS NOT NULL") .IsUnique(); } } |