diff options
Diffstat (limited to 'src')
3 files changed, 95 insertions, 50 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/Migrations/20260113203012_ChangeOwnerIdToGuid.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs index 379da0e9be..8c03347736 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260113203012_ChangeOwnerIdToGuid.cs @@ -33,12 +33,24 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations -- deletes an item: reattach it to the placeholder item instead of letting the -- FK_UserData_BaseItems_ItemId cascade wipe it. The placeholder can only hold one row -- per (UserId, CustomDataKey), so resolve collisions before repointing anything. + DROP TABLE IF EXISTS "DoomedUserDataKeys"; + CREATE TEMPORARY TABLE "DoomedUserDataKeys" ( + "UserId" TEXT NOT NULL, + "CustomDataKey" TEXT NOT NULL, + PRIMARY KEY ("UserId", "CustomDataKey")); + + -- Collect the colliding keys up front: correlating against "UserData" directly makes + -- the delete below re-scan every row of that user once per placeholder row. + INSERT OR IGNORE INTO "DoomedUserDataKeys" ("UserId", "CustomDataKey") + SELECT Doomed."UserId", Doomed."CustomDataKey" + FROM "UserData" AS Doomed + INNER JOIN "OrphanedBaseItemIds" AS Orphan ON Orphan."Id" = Doomed."ItemId"; + DELETE FROM "UserData" WHERE "ItemId" = '00000000-0000-0000-0000-000000000001' AND EXISTS ( SELECT 1 - FROM "UserData" AS Doomed - INNER JOIN "OrphanedBaseItemIds" AS Orphan ON Orphan."Id" = Doomed."ItemId" + FROM "DoomedUserDataKeys" AS Doomed WHERE Doomed."UserId" = "UserData"."UserId" AND Doomed."CustomDataKey" = "UserData"."CustomDataKey"); @@ -63,6 +75,7 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations DELETE FROM "BaseItems" WHERE "Id" IN (SELECT "Id" FROM "OrphanedBaseItemIds"); + DROP TABLE "DoomedUserDataKeys"; DROP TABLE "OrphanedBaseItemIds"; """); @@ -106,41 +119,9 @@ namespace Jellyfin.Database.Providers.Sqlite.Migrations columns: new[] { "BaseItemEntityId", "Name", "OwnerId" }, values: new object[] { null, "This is a placeholder item for UserData that has been detached from its original item", null }); - migrationBuilder.CreateIndex( - name: "IX_BaseItems_BaseItemEntityId", - table: "BaseItems", - column: "BaseItemEntityId"); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_ExtraType", - table: "BaseItems", - column: "ExtraType"); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_ExtraType_OwnerId", - table: "BaseItems", - columns: new[] { "ExtraType", "OwnerId" }); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_OwnerId", - table: "BaseItems", - column: "OwnerId"); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_TopParentId_IsFolder_IsVirtualItem_DateCreated", - table: "BaseItems", - columns: new[] { "TopParentId", "IsFolder", "IsVirtualItem", "DateCreated" }); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_TopParentId_MediaType_IsVirtualItem_DateCreated", - table: "BaseItems", - columns: new[] { "TopParentId", "MediaType", "IsVirtualItem", "DateCreated" }); - - migrationBuilder.CreateIndex( - name: "IX_BaseItems_TopParentId_Type_IsVirtualItem_DateCreated", - table: "BaseItems", - columns: new[] { "TopParentId", "Type", "IsVirtualItem", "DateCreated" }); - + // No CreateIndex calls here on purpose: AddForeignKey rebuilds BaseItems on SQLite and + // recreates every index of the target model afterwards, so building them first only + // pays for a full index pass that the rebuild immediately throws away. migrationBuilder.AddForeignKey( name: "FK_BaseItems_BaseItems_BaseItemEntityId", table: "BaseItems", 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); |