diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/PragmaConnectionInterceptor.cs | 108 | ||||
| -rw-r--r-- | src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs | 38 |
2 files changed, 140 insertions, 6 deletions
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; + +/// <summary> +/// Injects a series of PRAGMA on each connection starts. +/// </summary> +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<string, string> _customPragma; + + /// <summary> + /// Initializes a new instance of the <see cref="PragmaConnectionInterceptor"/> class. + /// </summary> + /// <param name="logger">The logger.</param> + /// <param name="cacheSize">Cache size.</param> + /// <param name="lockingMode">Locking mode.</param> + /// <param name="journalSizeLimit">Journal Size.</param> + /// <param name="tempStoreMode">The https://sqlite.org/pragma.html#pragma_temp_store pragma.</param> + /// <param name="syncMode">The https://sqlite.org/pragma.html#pragma_synchronous pragma.</param> + /// <param name="customPragma">A list of custom provided Pragma in the list of CustomOptions starting with "#PRAGMA:".</param> + public PragmaConnectionInterceptor(ILogger logger, int? cacheSize, string lockingMode, int? journalSizeLimit, int tempStoreMode, int syncMode, IDictionary<string, string> 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; } + + /// <inheritdoc/> + 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(); + } + } + + /// <inheritdoc/> + 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 /// <inheritdoc/> 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 = Enum.Parse<SqliteCacheMode>(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>, () => 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<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"); |