diff options
Diffstat (limited to 'src/Jellyfin.Database')
5 files changed, 162 insertions, 138 deletions
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 fec37ce723..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,207 +107,160 @@ 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<ItemValueMap, ItemValueType>(itemValueTypes, m => m.ItemValue.Type); + ArgumentNullException.ThrowIfNull(context); - // Flat sub-selects + Contains instead of a nested correlated .Any(...Any(...)). + // Flat sub-selects rather than a correlated .Any(...Any(...)). var referencedCleanValues = context.BaseItems - .Where(itemFilter) + .Where(OneOrManyExpressionBuilder<BaseItemEntity, Guid>(referenceIds, e => e.Id)) .Select(e => e.CleanName); var matchingItemIds = context.ItemValuesMap - .Where(typeFilter) + .Where(OneOrManyExpressionBuilder<ItemValueMap, ItemValueType>(itemValueTypes, m => m.ItemValue.Type)) .Where(m => referencedCleanValues.Contains(m.ItemValue.CleanValue)) .Select(m => m.ItemId); - if (invert) - { - return baseQuery.Where(e => !matchingItemIds.Contains(e.Id)); - } - - return baseQuery.Where(e => matchingItemIds.Contains(e.Id)); + return invert + ? baseQuery.Where(e => !matchingItemIds.Contains(e.Id)) + : baseQuery.Where(e => matchingItemIds.Contains(e.Id)); } /// <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); - - // Flat sub-selects + Contains instead of a nested correlated .Any(...Any(...)). - var referencedCleanValues = context.BaseItems - .Where(itemFilter) - .Select(e => e.CleanName); - - var matchingItemIds = context.ItemValuesMap - .Where(m => m.ItemValue.Type == itemValueType && referencedCleanValues.Contains(m.ItemValue.CleanValue)) - .Select(m => m.ItemId); - - if (invert) - { - return item => !matchingItemIds.Contains(item.Id); - } - - return item => matchingItemIds.Contains(item.Id); - } - - /// <summary> - /// 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)); + if (existenceOnly.Count == 0 && specificValues.Count == 0) + { + return baseQuery; + } - // Always wrap the collection in EF.Parameter so EF Core caches a single compiled plan and reuses it across calls. - return Expression.Lambda<Func<TEntity, bool>>( - Expression.Call( - null, - containsMethodInfo, - Expression.Call(null, _efParameterInstruction.MakeGenericMethod(oneOf.GetType()), Expression.Constant(oneOf)), - property.Body), + 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); } 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.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index 044fd0131f..8020fe1f93 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -63,7 +63,11 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider var sqliteConnectionBuilder = new SqliteConnectionStringBuilder { DataSource = GetOption(customOptions, "path", e => e, () => Path.Combine(_applicationPaths.DataPath, "jellyfin.db")), - Cache = GetOption(customOptions, "cache", Enum.Parse<SqliteCacheMode>, () => SqliteCacheMode.Default), + // Private, not Default: sqlite3_enable_shared_cache is process-global, so a plugin + // enabling it makes these connections share a cache too. Contention then surfaces as + // SQLITE_LOCKED ("database table is locked"), which the busy handler does not cover, + // so busy_timeout is skipped and the command fails at CommandTimeout instead. + Cache = GetOption(customOptions, "cache", Enum.Parse<SqliteCacheMode>, () => SqliteCacheMode.Private), Pooling = GetOption(customOptions, "pooling", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase), () => true), DefaultTimeout = GetOption(customOptions, "command-timeout", int.Parse, () => 60) }; |