diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs | 9 | ||||
| -rw-r--r-- | src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs | 81 |
2 files changed, 77 insertions, 13 deletions
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs index 0a72287ba9..6efced6556 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs @@ -45,6 +45,15 @@ public interface IJellyfinDatabaseProvider Task RunScheduledOptimisation(CancellationToken cancellationToken); /// <summary> + /// If supported this should refresh the query planner statistics, e.g. after a library scan changed the data. + /// Unlike <see cref="RunScheduledOptimisation(CancellationToken)"/> it should not reclaim space, so that it stays + /// cheap enough to run after every scan. + /// </summary> + /// <param name="cancellationToken">The token to abort the operation.</param> + /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns> + Task RefreshStatistics(CancellationToken cancellationToken) => Task.CompletedTask; + + /// <summary> /// If supported this should perform any actions that are required on stopping the jellyfin server. This runs /// against a deadline imposed by the service manager, so unlike /// <see cref="RunScheduledOptimisation(CancellationToken)"/> it should only do work whose cost does not grow with diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index 3330b64b69..a3fbe42e02 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -110,6 +110,35 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } /// <inheritdoc/> + public async Task RefreshStatistics(CancellationToken cancellationToken) + { + if (DbContextFactory is null) + { + return; + } + + var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); + await using (context.ConfigureAwait(false)) + { + await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false); + try + { + if (!await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false)) + { + return; + } + + _logger.LogInformation("Analyzing jellyfin.db"); + await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false); + } + finally + { + await context.Database.CloseConnectionAsync().ConfigureAwait(false); + } + } + } + + /// <inheritdoc/> public void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.SetDefaultDateTimeKind(DateTimeKind.Utc); @@ -161,14 +190,11 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider try { long? tempStore; - long? analysisLimit; var pragmaCommand = context.Database.GetDbConnection().CreateCommand(); await using (pragmaCommand.ConfigureAwait(false)) { pragmaCommand.CommandText = "PRAGMA temp_store"; tempStore = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false); - pragmaCommand.CommandText = "PRAGMA analysis_limit"; - analysisLimit = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false); } await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); @@ -192,19 +218,15 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } } - await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false); - try + // Statistics taken while the library is empty make the planner treat every table as one row and + // pick full scans once it fills up; no statistics at all plan far better until there is data. + if (await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false)) { - await context.Database.ExecuteSqlRawAsync("ANALYZE", cancellationToken).ConfigureAwait(false); + await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false); } - finally + else { - if (analysisLimit is not null) - { - await context.Database.ExecuteSqlRawAsync( - FormattableString.Invariant($"PRAGMA analysis_limit={analysisLimit.Value}"), - CancellationToken.None).ConfigureAwait(false); - } + _logger.LogInformation("Not analyzing jellyfin.db, the library holds no items yet"); } await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); @@ -217,6 +239,39 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } } + private static Task<bool> HasLibraryItemsAsync(JellyfinDbContext context, CancellationToken cancellationToken) + { + // Folders and the seeded placeholder exist before any library has been scanned. + return context.BaseItems.AnyAsync(e => !e.IsFolder && e.Type != "PLACEHOLDER", cancellationToken); + } + + private static async Task AnalyzeAsync(JellyfinDbContext context, CancellationToken cancellationToken) + { + long? analysisLimit; + var pragmaCommand = context.Database.GetDbConnection().CreateCommand(); + await using (pragmaCommand.ConfigureAwait(false)) + { + pragmaCommand.CommandText = "PRAGMA analysis_limit"; + analysisLimit = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false); + } + + await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false); + try + { + await context.Database.ExecuteSqlRawAsync("ANALYZE", cancellationToken).ConfigureAwait(false); + } + finally + { + // The connection goes back to the pool, so hand it over the way it was handed to us. + if (analysisLimit is not null) + { + await context.Database.ExecuteSqlRawAsync( + FormattableString.Invariant($"PRAGMA analysis_limit={analysisLimit.Value}"), + CancellationToken.None).ConfigureAwait(false); + } + } + } + private static async Task<long?> ReadPragmaValueAsync(DbCommand command, CancellationToken cancellationToken) { var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false); |