From 24410d8a2e05e7abd552fae2732c337afe0d63bd Mon Sep 17 00:00:00 2001 From: JPVenson Date: Sun, 14 Sep 2025 19:24:35 +0200 Subject: Reenable common PRAGMA setters (#14791) --- .../PragmaConnectionInterceptor.cs | 108 +++++++++++++++++++++ .../SqliteDatabaseProvider.cs | 38 ++++++-- 2 files changed, 140 insertions(+), 6 deletions(-) create mode 100644 src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/PragmaConnectionInterceptor.cs (limited to 'src') diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/PragmaConnectionInterceptor.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/PragmaConnectionInterceptor.cs new file mode 100644 index 000000000..47e44d97b --- /dev/null +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/PragmaConnectionInterceptor.cs @@ -0,0 +1,108 @@ +using System.Collections.Generic; +using System.Data.Common; +using System.Globalization; +using System.Text; +using System.Threading; +using System.Threading.Tasks; +using Microsoft.EntityFrameworkCore.Diagnostics; +using Microsoft.Extensions.Logging; + +namespace Jellyfin.Database.Providers.Sqlite; + +/// +/// Injects a series of PRAGMA on each connection starts. +/// +public class PragmaConnectionInterceptor : DbConnectionInterceptor +{ + private readonly ILogger _logger; + private readonly int? _cacheSize; + private readonly string _lockingMode; + private readonly int? _journalSizeLimit; + private readonly int _tempStoreMode; + private readonly int _syncMode; + private readonly IDictionary _customPragma; + + /// + /// Initializes a new instance of the class. + /// + /// The logger. + /// Cache size. + /// Locking mode. + /// Journal Size. + /// The https://sqlite.org/pragma.html#pragma_temp_store pragma. + /// The https://sqlite.org/pragma.html#pragma_synchronous pragma. + /// A list of custom provided Pragma in the list of CustomOptions starting with "#PRAGMA:". + public PragmaConnectionInterceptor(ILogger logger, int? cacheSize, string lockingMode, int? journalSizeLimit, int tempStoreMode, int syncMode, IDictionary customPragma) + { + _logger = logger; + _cacheSize = cacheSize; + _lockingMode = lockingMode; + _journalSizeLimit = journalSizeLimit; + _tempStoreMode = tempStoreMode; + _syncMode = syncMode; + _customPragma = customPragma; + + InitialCommand = BuildCommandText(); + _logger.LogInformation("SQLITE connection pragma command set to: \r\n {PragmaCommand}", InitialCommand); + } + + private string? InitialCommand { get; set; } + + /// + public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData) + { + base.ConnectionOpened(connection, eventData); + + using (var command = connection.CreateCommand()) + { +#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities + command.CommandText = InitialCommand; +#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities + command.ExecuteNonQuery(); + } + } + + /// + public override async Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default) + { + await base.ConnectionOpenedAsync(connection, eventData, cancellationToken).ConfigureAwait(false); + + var command = connection.CreateCommand(); + await using (command.ConfigureAwait(false)) + { +#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities + command.CommandText = InitialCommand; +#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities + await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false); + } + } + + private string BuildCommandText() + { + var sb = new StringBuilder(); + if (_cacheSize.HasValue) + { + sb.AppendLine(CultureInfo.InvariantCulture, $"PRAGMA cache_size={_cacheSize.Value};"); + } + + if (!string.IsNullOrWhiteSpace(_lockingMode)) + { + sb.AppendLine(CultureInfo.InvariantCulture, $"PRAGMA locking_mode={_lockingMode};"); + } + + if (_journalSizeLimit.HasValue) + { + sb.AppendLine(CultureInfo.InvariantCulture, $"PRAGMA journal_size_limit={_journalSizeLimit};"); + } + + sb.AppendLine(CultureInfo.InvariantCulture, $"PRAGMA synchronous={_syncMode};"); + sb.AppendLine(CultureInfo.InvariantCulture, $"PRAGMA temp_store={_tempStoreMode};"); + + foreach (var item in _customPragma) + { + sb.AppendLine(CultureInfo.InvariantCulture, $"PRAGMA {item.Key}={item.Value};"); + } + + return sb.ToString(); + } +} diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index d51e8fd64..2b000b257 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -42,10 +42,28 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider /// public void Initialise(DbContextOptionsBuilder options, DatabaseConfigurationOptions databaseConfiguration) { + static T? GetOption(ICollection? options, string key, Func converter, Func? 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 = Enum.Parse(databaseConfiguration.CustomProviderOptions?.Options.FirstOrDefault(e => e.Key.Equals("cache", StringComparison.OrdinalIgnoreCase))?.Value ?? nameof(SqliteCacheMode.Default)); - sqliteConnectionBuilder.Pooling = (databaseConfiguration.CustomProviderOptions?.Options.FirstOrDefault(e => e.Key.Equals("pooling", StringComparison.OrdinalIgnoreCase))?.Value ?? bool.TrueString).Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase); + sqliteConnectionBuilder.Cache = GetOption(customOptions, "cache", Enum.Parse, () => SqliteCacheMode.Default); + sqliteConnectionBuilder.Pooling = GetOption(customOptions, "pooling", e => e.Equals(bool.TrueString, StringComparison.OrdinalIgnoreCase), () => true); var connectionString = sqliteConnectionBuilder.ToString(); @@ -58,10 +76,18 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider sqLiteOptions => sqLiteOptions.MigrationsAssembly(GetType().Assembly)) // TODO: Remove when https://github.com/dotnet/efcore/pull/35873 is merged & released .ConfigureWarnings(warnings => - warnings.Ignore(RelationalEventId.NonTransactionalMigrationOperationWarning)); - - var enableSensitiveDataLoggingOption = databaseConfiguration.CustomProviderOptions?.Options.FirstOrDefault(e => e.Key.Equals("EnableSensitiveDataLogging", StringComparison.OrdinalIgnoreCase))?.Value; - if (!string.IsNullOrEmpty(enableSensitiveDataLoggingOption) && bool.TryParse(enableSensitiveDataLoggingOption, out bool enableSensitiveDataLogging) && enableSensitiveDataLogging) + warnings.Ignore(RelationalEventId.NonTransactionalMigrationOperationWarning)) + .AddInterceptors(new PragmaConnectionInterceptor( + _logger, + GetOption(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"); -- cgit v1.2.3