diff options
Diffstat (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs')
| -rw-r--r-- | Emby.Server.Implementations/Data/BaseSqliteRepository.cs | 217 |
1 files changed, 65 insertions, 152 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 6f23a0888..bf079d90c 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -4,10 +4,9 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Threading; +using Jellyfin.Extensions; +using Microsoft.Data.Sqlite; using Microsoft.Extensions.Logging; -using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data { @@ -27,32 +26,24 @@ namespace Emby.Server.Implementations.Data /// <summary> /// Gets or sets the path to the DB file. /// </summary> - /// <value>Path to the DB file.</value> protected string DbFilePath { get; set; } /// <summary> - /// Gets the logger. - /// </summary> - /// <value>The logger.</value> - protected ILogger<BaseSqliteRepository> Logger { get; } - - /// <summary> - /// Gets the default connection flags. + /// Gets or sets the number of write connections to create. /// </summary> - /// <value>The default connection flags.</value> - protected virtual ConnectionFlags DefaultConnectionFlags => ConnectionFlags.NoMutex; + /// <value>Path to the DB file.</value> + protected int WriteConnectionsCount { get; set; } = 1; /// <summary> - /// Gets the transaction mode. + /// Gets or sets the number of read connections to create. /// </summary> - /// <value>The transaction mode.</value>> - protected TransactionMode TransactionMode => TransactionMode.Deferred; + protected int ReadConnectionsCount { get; set; } = 1; /// <summary> - /// Gets the transaction mode for read-only operations. + /// Gets the logger. /// </summary> - /// <value>The transaction mode.</value> - protected TransactionMode ReadTransactionMode => TransactionMode.Deferred; + /// <value>The logger.</value> + protected ILogger<BaseSqliteRepository> Logger { get; } /// <summary> /// Gets the cache size. @@ -61,10 +52,22 @@ namespace Emby.Server.Implementations.Data protected virtual int? CacheSize => null; /// <summary> - /// Gets the journal mode. <see href="https://www.sqlite.org/pragma.html#pragma_journal_mode" /> + /// Gets the locking mode. <see href="https://www.sqlite.org/pragma.html#pragma_locking_mode" />. + /// </summary> + protected virtual string LockingMode => "NORMAL"; + + /// <summary> + /// Gets the journal mode. <see href="https://www.sqlite.org/pragma.html#pragma_journal_mode" />. /// </summary> /// <value>The journal mode.</value> - protected virtual string JournalMode => "TRUNCATE"; + protected virtual string JournalMode => "WAL"; + + /// <summary> + /// Gets the journal size limit. <see href="https://www.sqlite.org/pragma.html#pragma_journal_size_limit" />. + /// The default (-1) is overriden to prevent unconstrained WAL size, as reported by users. + /// </summary> + /// <value>The journal size limit.</value> + protected virtual int? JournalSizeLimit => 134_217_728; // 128MiB /// <summary> /// Gets the page size. @@ -77,107 +80,86 @@ namespace Emby.Server.Implementations.Data /// </summary> /// <value>The temp store mode.</value> /// <see cref="TempStoreMode"/> - protected virtual TempStoreMode TempStore => TempStoreMode.Default; + protected virtual TempStoreMode TempStore => TempStoreMode.Memory; /// <summary> /// Gets the synchronous mode. /// </summary> /// <value>The synchronous mode or null.</value> /// <see cref="SynchronousMode"/> - protected virtual SynchronousMode? Synchronous => null; + protected virtual SynchronousMode? Synchronous => SynchronousMode.Normal; - /// <summary> - /// Gets or sets the write lock. - /// </summary> - /// <value>The write lock.</value> - protected SemaphoreSlim WriteLock { get; set; } = new SemaphoreSlim(1, 1); - - /// <summary> - /// Gets or sets the write connection. - /// </summary> - /// <value>The write connection.</value> - protected SQLiteDatabaseConnection WriteConnection { get; set; } - - protected ManagedConnection GetConnection(bool _ = false) + public virtual void Initialize() { - WriteLock.Wait(); - if (WriteConnection != null) + // Configuration and pragmas can affect VACUUM so it needs to be last. + using (var connection = GetConnection()) { - return new ManagedConnection(WriteConnection, WriteLock); + connection.Execute("VACUUM"); } + } - WriteConnection = SQLite3.Open( - DbFilePath, - DefaultConnectionFlags | ConnectionFlags.Create | ConnectionFlags.ReadWrite, - null); + protected SqliteConnection GetConnection() + { + var connection = new SqliteConnection($"Filename={DbFilePath}"); + connection.Open(); if (CacheSize.HasValue) { - WriteConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + connection.Execute("PRAGMA cache_size=" + CacheSize.Value); + } + + if (!string.IsNullOrWhiteSpace(LockingMode)) + { + connection.Execute("PRAGMA locking_mode=" + LockingMode); } if (!string.IsNullOrWhiteSpace(JournalMode)) { - WriteConnection.Execute("PRAGMA journal_mode=" + JournalMode); + connection.Execute("PRAGMA journal_mode=" + JournalMode); + } + + if (JournalSizeLimit.HasValue) + { + connection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); } if (Synchronous.HasValue) { - WriteConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); + connection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); } if (PageSize.HasValue) { - WriteConnection.Execute("PRAGMA page_size=" + PageSize.Value); + connection.Execute("PRAGMA page_size=" + PageSize.Value); } - WriteConnection.Execute("PRAGMA temp_store=" + (int)TempStore); + connection.Execute("PRAGMA temp_store=" + (int)TempStore); - // Configuration and pragmas can affect VACUUM so it needs to be last. - WriteConnection.Execute("VACUUM"); - - return new ManagedConnection(WriteConnection, WriteLock); + return connection; } - public IStatement PrepareStatement(ManagedConnection connection, string sql) - => connection.PrepareStatement(sql); - - public IStatement PrepareStatement(IDatabaseConnection connection, string sql) - => connection.PrepareStatement(sql); - - public IStatement[] PrepareAll(IDatabaseConnection connection, IReadOnlyList<string> sql) + public SqliteCommand PrepareStatement(SqliteConnection connection, string sql) { - int len = sql.Count; - IStatement[] statements = new IStatement[len]; - for (int i = 0; i < len; i++) - { - statements[i] = connection.PrepareStatement(sql[i]); - } - - return statements; + var command = connection.CreateCommand(); + command.CommandText = sql; + return command; } - protected bool TableExists(ManagedConnection connection, string name) + protected bool TableExists(SqliteConnection connection, string name) { - return connection.RunInTransaction( - db => + using var statement = PrepareStatement(connection, "select DISTINCT tbl_name from sqlite_master"); + foreach (var row in statement.ExecuteQuery()) { - using (var statement = PrepareStatement(db, "select DISTINCT tbl_name from sqlite_master")) + if (string.Equals(name, row.GetString(0), StringComparison.OrdinalIgnoreCase)) { - foreach (var row in statement.ExecuteQuery()) - { - if (string.Equals(name, row.GetString(0), StringComparison.OrdinalIgnoreCase)) - { - return true; - } - } + return true; } + } - return false; - }, ReadTransactionMode); + return false; } - protected List<string> GetColumnNames(IDatabaseConnection connection, string table) + protected List<string> GetColumnNames(SqliteConnection connection, string table) { var columnNames = new List<string>(); @@ -192,9 +174,9 @@ namespace Emby.Server.Implementations.Data return columnNames; } - protected void AddColumn(IDatabaseConnection connection, string table, string columnName, string type, List<string> existingColumnNames) + protected void AddColumn(SqliteConnection connection, string table, string columnName, string type, List<string> existingColumnNames) { - if (existingColumnNames.Contains(columnName, StringComparer.OrdinalIgnoreCase)) + if (existingColumnNames.Contains(columnName, StringComparison.OrdinalIgnoreCase)) { return; } @@ -228,76 +210,7 @@ namespace Emby.Server.Implementations.Data return; } - if (dispose) - { - WriteLock.Wait(); - try - { - WriteConnection?.Dispose(); - } - finally - { - WriteLock.Release(); - } - - WriteLock.Dispose(); - } - - WriteConnection = null; - WriteLock = null; - _disposed = true; } } - - /// <summary> - /// The disk synchronization mode, controls how aggressively SQLite will write data - /// all the way out to physical storage. - /// </summary> - public enum SynchronousMode - { - /// <summary> - /// SQLite continues without syncing as soon as it has handed data off to the operating system. - /// </summary> - Off = 0, - - /// <summary> - /// SQLite database engine will still sync at the most critical moments. - /// </summary> - Normal = 1, - - /// <summary> - /// SQLite database engine will use the xSync method of the VFS - /// to ensure that all content is safely written to the disk surface prior to continuing. - /// </summary> - Full = 2, - - /// <summary> - /// EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal - /// is synced after that journal is unlinked to commit a transaction in DELETE mode. - /// </summary> - Extra = 3 - } - - /// <summary> - /// Storage mode used by temporary database files. - /// </summary> - public enum TempStoreMode - { - /// <summary> - /// The compile-time C preprocessor macro SQLITE_TEMP_STORE - /// is used to determine where temporary tables and indices are stored. - /// </summary> - Default = 0, - - /// <summary> - /// Temporary tables and indices are stored in a file. - /// </summary> - File = 1, - - /// <summary> - /// Temporary tables and indices are kept in as if they were pure in-memory databases memory. - /// </summary> - Memory = 2 - } } |