diff options
Diffstat (limited to 'src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs')
| -rw-r--r-- | src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs | 118 |
1 files changed, 99 insertions, 19 deletions
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index 927ba63b9..da63df8e2 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -1,9 +1,12 @@ using System; +using System.Collections.Generic; using System.Globalization; using System.IO; +using System.Linq; using System.Threading; using System.Threading.Tasks; using Jellyfin.Database.Implementations; +using Jellyfin.Database.Implementations.DbConfiguration; using MediaBrowser.Common.Configuration; using Microsoft.Data.Sqlite; using Microsoft.EntityFrameworkCore; @@ -37,15 +40,59 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider public IDbContextFactory<JellyfinDbContext>? DbContextFactory { get; set; } /// <inheritdoc/> - public void Initialise(DbContextOptionsBuilder options) + public void Initialise(DbContextOptionsBuilder options, DatabaseConfigurationOptions databaseConfiguration) { + static T? GetOption<T>(ICollection<CustomDatabaseOption>? options, string key, Func<string, T> converter, Func<T>? defaultValue = null) + { + if (options is null) + { + return defaultValue is not null ? defaultValue() : default; + } + + var value = options.FirstOrDefault(e => e.Key.Equals(key, StringComparison.OrdinalIgnoreCase)); + if (value is null) + { + return defaultValue is not null ? defaultValue() : default; + } + + return converter(value.Value); + } + + var customOptions = databaseConfiguration.CustomProviderOptions?.Options; + + var sqliteConnectionBuilder = new SqliteConnectionStringBuilder(); + sqliteConnectionBuilder.DataSource = Path.Combine(_applicationPaths.DataPath, "jellyfin.db"); + sqliteConnectionBuilder.Cache = GetOption(customOptions, "cache", Enum.Parse<SqliteCacheMode>, () => SqliteCacheMode.Default); + sqliteConnectionBuilder.Pooling = GetOption(customOptions, "pooling", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase), () => true); + sqliteConnectionBuilder.DefaultTimeout = GetOption(customOptions, "command-timeout", int.Parse, () => 30); + + var connectionString = sqliteConnectionBuilder.ToString(); + + // Log SQLite connection parameters + _logger.LogInformation("SQLite connection string: {ConnectionString}", connectionString); + options .UseSqlite( - $"Filename={Path.Combine(_applicationPaths.DataPath, "jellyfin.db")};Pooling=false", + connectionString, sqLiteOptions => sqLiteOptions.MigrationsAssembly(GetType().Assembly)) // TODO: Remove when https://github.com/dotnet/efcore/pull/35873 is merged & released .ConfigureWarnings(warnings => - warnings.Ignore(RelationalEventId.NonTransactionalMigrationOperationWarning)); + warnings.Ignore(RelationalEventId.NonTransactionalMigrationOperationWarning)) + .AddInterceptors(new PragmaConnectionInterceptor( + _logger, + GetOption<int?>(customOptions, "cacheSize", e => int.Parse(e, CultureInfo.InvariantCulture)), + GetOption(customOptions, "lockingmode", e => e, () => "NORMAL")!, + GetOption(customOptions, "journalsizelimit", int.Parse, () => 134_217_728), + GetOption(customOptions, "tempstoremode", int.Parse, () => 2), + GetOption(customOptions, "syncmode", int.Parse, () => 1), + customOptions?.Where(e => e.Key.StartsWith("#PRAGMA:", StringComparison.OrdinalIgnoreCase)).ToDictionary(e => e.Key["#PRAGMA:".Length..], e => e.Value) ?? [])); + + var enableSensitiveDataLogging = GetOption(customOptions, "EnableSensitiveDataLogging", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase), () => false); + if (enableSensitiveDataLogging) + { + options.EnableSensitiveDataLogging(enableSensitiveDataLogging); + _logger.LogInformation("EnableSensitiveDataLogging is enabled on SQLite connection"); + } } /// <inheritdoc/> @@ -54,16 +101,11 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider var context = await DbContextFactory!.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); await using (context.ConfigureAwait(false)) { - if (context.Database.IsSqlite()) - { - await context.Database.ExecuteSqlRawAsync("PRAGMA optimize", cancellationToken).ConfigureAwait(false); - await context.Database.ExecuteSqlRawAsync("VACUUM", cancellationToken).ConfigureAwait(false); - _logger.LogInformation("jellyfin.db optimized successfully!"); - } - else - { - _logger.LogInformation("This database doesn't support optimization"); - } + await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); + await context.Database.ExecuteSqlRawAsync("PRAGMA optimize", cancellationToken).ConfigureAwait(false); + await context.Database.ExecuteSqlRawAsync("VACUUM", cancellationToken).ConfigureAwait(false); + await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false); + _logger.LogInformation("jellyfin.db optimized successfully!"); } } @@ -76,8 +118,13 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider /// <inheritdoc/> public async Task RunShutdownTask(CancellationToken cancellationToken) { + if (DbContextFactory is null) + { + return; + } + // Run before disposing the application - var context = await DbContextFactory!.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); + var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false); await using (context.ConfigureAwait(false)) { await context.Database.ExecuteSqlRawAsync("PRAGMA optimize", cancellationToken).ConfigureAwait(false); @@ -98,10 +145,7 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider var key = DateTime.UtcNow.ToString("yyyyMMddhhmmss", CultureInfo.InvariantCulture); var path = Path.Combine(_applicationPaths.DataPath, "jellyfin.db"); var backupFile = Path.Combine(_applicationPaths.DataPath, BackupFolderName); - if (!Directory.Exists(backupFile)) - { - Directory.CreateDirectory(backupFile); - } + Directory.CreateDirectory(backupFile); backupFile = Path.Combine(backupFile, $"{key}_jellyfin.db"); File.Copy(path, backupFile); @@ -118,11 +162,47 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider if (!File.Exists(backupFile)) { - _logger.LogCritical("Tried to restore a backup that does not exist."); + _logger.LogCritical("Tried to restore a backup that does not exist: {Key}", key); return Task.CompletedTask; } File.Copy(backupFile, path, true); return Task.CompletedTask; } + + /// <inheritdoc /> + public Task DeleteBackup(string key) + { + var backupFile = Path.Combine(_applicationPaths.DataPath, BackupFolderName, $"{key}_jellyfin.db"); + + if (!File.Exists(backupFile)) + { + _logger.LogCritical("Tried to delete a backup that does not exist: {Key}", key); + return Task.CompletedTask; + } + + File.Delete(backupFile); + return Task.CompletedTask; + } + + /// <inheritdoc/> + public async Task PurgeDatabase(JellyfinDbContext dbContext, IEnumerable<string>? tableNames) + { + ArgumentNullException.ThrowIfNull(tableNames); + + var deleteQueries = new List<string>(); + foreach (var tableName in tableNames) + { + deleteQueries.Add($"DELETE FROM \"{tableName}\";"); + } + + var deleteAllQuery = + $""" + PRAGMA foreign_keys = OFF; + {string.Join('\n', deleteQueries)} + PRAGMA foreign_keys = ON; + """; + + await dbContext.Database.ExecuteSqlRawAsync(deleteAllQuery).ConfigureAwait(false); + } } |