From 7d28d08e08a412ab88ede368220562799f2bd7c0 Mon Sep 17 00:00:00 2001 From: Bond-009 Date: Sat, 13 Apr 2024 01:45:01 +0200 Subject: Enable more warnings as errors (#11288) --- Emby.Server.Implementations/Data/BaseSqliteRepository.cs | 5 +---- 1 file changed, 1 insertion(+), 4 deletions(-) (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index bf079d90c..b1c99227c 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -186,10 +186,7 @@ namespace Emby.Server.Implementations.Data protected void CheckDisposed() { - if (_disposed) - { - throw new ObjectDisposedException(GetType().Name, "Object has been disposed and cannot be accessed."); - } + ObjectDisposedException.ThrowIf(_disposed, this); } /// -- cgit v1.2.3 From 953bd5f210629b6e6739b301ba368851fe476450 Mon Sep 17 00:00:00 2001 From: Bond_009 Date: Wed, 5 Jun 2024 11:58:21 +0200 Subject: Remove old connection count props Not used with the new SQLite lib --- Emby.Server.Implementations/Data/BaseSqliteRepository.cs | 11 ----------- Emby.Server.Implementations/Data/SqliteItemRepository.cs | 1 - 2 files changed, 12 deletions(-) (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index b1c99227c..ee1598e7f 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -28,17 +28,6 @@ namespace Emby.Server.Implementations.Data /// protected string DbFilePath { get; set; } - /// - /// Gets or sets the number of write connections to create. - /// - /// Path to the DB file. - protected int WriteConnectionsCount { get; set; } = 1; - - /// - /// Gets or sets the number of read connections to create. - /// - protected int ReadConnectionsCount { get; set; } = 1; - /// /// Gets the logger. /// diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index a09988fee..a4c6b2d31 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -327,7 +327,6 @@ namespace Emby.Server.Implementations.Data DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); CacheSize = configuration.GetSqliteCacheSize(); - ReadConnectionsCount = Environment.ProcessorCount * 2; } /// -- cgit v1.2.3 From 78e8eae5adc3744fc2a4c86152bac42ce40b4960 Mon Sep 17 00:00:00 2001 From: Bond-009 Date: Thu, 6 Jun 2024 14:30:36 -0400 Subject: Backport pull request #11969 from jellyfin/release-10.9.z Create readonly DB connections when possible Original-merge: a46c17e19fd593591b733208bc9b0488f8df9539 Merged-by: joshuaboniface Backported-by: Joshua M. Boniface --- .../Data/BaseSqliteRepository.cs | 4 ++-- .../Data/SqliteItemRepository.cs | 28 +++++++++++----------- .../Data/SqliteUserDataRepository.cs | 2 +- 3 files changed, 17 insertions(+), 17 deletions(-) (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index b1c99227c..a313d7cb3 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -98,9 +98,9 @@ namespace Emby.Server.Implementations.Data } } - protected SqliteConnection GetConnection() + protected SqliteConnection GetConnection(bool readOnly = false) { - var connection = new SqliteConnection($"Filename={DbFilePath}"); + var connection = new SqliteConnection($"Filename={DbFilePath}" + (readOnly ? ";Mode=ReadOnly" : string.Empty)); connection.Open(); if (CacheSize.HasValue) diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index a09988fee..c2d31b474 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -1261,7 +1261,7 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, _retrieveItemColumnsSelectQuery)) { statement.TryBind("@guid", id); @@ -1887,7 +1887,7 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); var chapters = new List(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) { statement.TryBind("@ItemId", item.Id); @@ -1906,7 +1906,7 @@ namespace Emby.Server.Implementations.Data { CheckDisposed(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) { statement.TryBind("@ItemId", item.Id); @@ -2469,7 +2469,7 @@ namespace Emby.Server.Implementations.Data var commandText = commandTextBuilder.ToString(); using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText)) { if (EnableJoinUserData(query)) @@ -2537,7 +2537,7 @@ namespace Emby.Server.Implementations.Data var commandText = commandTextBuilder.ToString(); var items = new List(); using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText)) { if (EnableJoinUserData(query)) @@ -2745,7 +2745,7 @@ namespace Emby.Server.Implementations.Data var list = new List(); var result = new QueryResult(); - using var connection = GetConnection(); + using var connection = GetConnection(true); using var transaction = connection.BeginTransaction(); if (!isReturningZeroItems) { @@ -2927,7 +2927,7 @@ namespace Emby.Server.Implementations.Data var commandText = commandTextBuilder.ToString(); var list = new List(); using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText)) { if (EnableJoinUserData(query)) @@ -4509,7 +4509,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } var list = new List(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText.ToString())) { // Run this again to bind the params @@ -4547,7 +4547,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } var list = new List(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText.ToString())) { // Run this again to bind the params @@ -4787,7 +4787,7 @@ AND Type = @InternalPersonType)"); var list = new List(); using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText)) { foreach (var row in statement.ExecuteQuery()) @@ -4987,8 +4987,8 @@ AND Type = @InternalPersonType)"); var list = new List<(BaseItem, ItemCounts)>(); var result = new QueryResult<(BaseItem, ItemCounts)>(); using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection()) - using (var transaction = connection.BeginTransaction(deferred: true)) + using (var connection = GetConnection(true)) + using (var transaction = connection.BeginTransaction()) { if (!isReturningZeroItems) { @@ -5335,7 +5335,7 @@ AND Type = @InternalPersonType)"); cmdText += " order by StreamIndex ASC"; - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) { var list = new List(); @@ -5722,7 +5722,7 @@ AND Type = @InternalPersonType)"); cmdText += " order by AttachmentIndex ASC"; var list = new List(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, cmdText)) { statement.TryBind("@ItemId", query.ItemId); diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 20359e4ad..66d009fd4 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -267,7 +267,7 @@ namespace Emby.Server.Implementations.Data ArgumentException.ThrowIfNullOrEmpty(key); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) { using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where key =@Key and userId=@UserId")) { -- cgit v1.2.3 From b4e32a5ede6807849d618966f92a61a765b80180 Mon Sep 17 00:00:00 2001 From: Bond-009 Date: Thu, 6 Jun 2024 14:30:40 -0400 Subject: Backport pull request #11986 from jellyfin/release-10.9.z Use only 1 write connection/DB Original-merge: cc4563a4779ff7e86526b09f5ac5a2e7ec71e56b Merged-by: crobibero Backported-by: Joshua M. Boniface --- .../Data/BaseSqliteRepository.cs | 81 ++++++++++++++++++++-- .../Data/ManagedConnection.cs | 62 +++++++++++++++++ .../Data/SqliteItemRepository.cs | 18 ++--- .../Data/SqliteUserDataRepository.cs | 6 +- 4 files changed, 148 insertions(+), 19 deletions(-) create mode 100644 Emby.Server.Implementations/Data/ManagedConnection.cs (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index a313d7cb3..4305e4b39 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -4,6 +4,7 @@ using System; using System.Collections.Generic; +using System.Threading; using Jellyfin.Extensions; using Microsoft.Data.Sqlite; using Microsoft.Extensions.Logging; @@ -13,6 +14,8 @@ namespace Emby.Server.Implementations.Data public abstract class BaseSqliteRepository : IDisposable { private bool _disposed = false; + private SemaphoreSlim _writeLock = new SemaphoreSlim(1, 1); + private SqliteConnection _writeConnection; /// /// Initializes a new instance of the class. @@ -98,9 +101,55 @@ namespace Emby.Server.Implementations.Data } } - protected SqliteConnection GetConnection(bool readOnly = false) + protected ManagedConnection GetConnection(bool readOnly = false) { - var connection = new SqliteConnection($"Filename={DbFilePath}" + (readOnly ? ";Mode=ReadOnly" : string.Empty)); + if (!readOnly) + { + _writeLock.Wait(); + if (_writeConnection is not null) + { + return new ManagedConnection(_writeConnection, _writeLock); + } + + var writeConnection = new SqliteConnection($"Filename={DbFilePath};Pooling=False"); + writeConnection.Open(); + + if (CacheSize.HasValue) + { + writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + } + + if (!string.IsNullOrWhiteSpace(LockingMode)) + { + writeConnection.Execute("PRAGMA locking_mode=" + LockingMode); + } + + if (!string.IsNullOrWhiteSpace(JournalMode)) + { + writeConnection.Execute("PRAGMA journal_mode=" + JournalMode); + } + + if (JournalSizeLimit.HasValue) + { + writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); + } + + if (Synchronous.HasValue) + { + writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); + } + + if (PageSize.HasValue) + { + writeConnection.Execute("PRAGMA page_size=" + PageSize.Value); + } + + writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore); + + return new ManagedConnection(_writeConnection = writeConnection, _writeLock); + } + + var connection = new SqliteConnection($"Filename={DbFilePath};Mode=ReadOnly"); connection.Open(); if (CacheSize.HasValue) @@ -135,17 +184,17 @@ namespace Emby.Server.Implementations.Data connection.Execute("PRAGMA temp_store=" + (int)TempStore); - return connection; + return new ManagedConnection(connection, null); } - public SqliteCommand PrepareStatement(SqliteConnection connection, string sql) + public SqliteCommand PrepareStatement(ManagedConnection connection, string sql) { var command = connection.CreateCommand(); command.CommandText = sql; return command; } - protected bool TableExists(SqliteConnection connection, string name) + protected bool TableExists(ManagedConnection connection, string name) { using var statement = PrepareStatement(connection, "select DISTINCT tbl_name from sqlite_master"); foreach (var row in statement.ExecuteQuery()) @@ -159,7 +208,7 @@ namespace Emby.Server.Implementations.Data return false; } - protected List GetColumnNames(SqliteConnection connection, string table) + protected List GetColumnNames(ManagedConnection connection, string table) { var columnNames = new List(); @@ -174,7 +223,7 @@ namespace Emby.Server.Implementations.Data return columnNames; } - protected void AddColumn(SqliteConnection connection, string table, string columnName, string type, List existingColumnNames) + protected void AddColumn(ManagedConnection connection, string table, string columnName, string type, List existingColumnNames) { if (existingColumnNames.Contains(columnName, StringComparison.OrdinalIgnoreCase)) { @@ -207,6 +256,24 @@ namespace Emby.Server.Implementations.Data return; } + if (dispose) + { + _writeLock.Wait(); + try + { + _writeConnection.Dispose(); + } + finally + { + _writeLock.Release(); + } + + _writeLock.Dispose(); + } + + _writeConnection = null; + _writeLock = null; + _disposed = true; } } diff --git a/Emby.Server.Implementations/Data/ManagedConnection.cs b/Emby.Server.Implementations/Data/ManagedConnection.cs new file mode 100644 index 000000000..860950b30 --- /dev/null +++ b/Emby.Server.Implementations/Data/ManagedConnection.cs @@ -0,0 +1,62 @@ +#pragma warning disable CS1591 + +using System; +using System.Collections.Generic; +using System.Threading; +using Microsoft.Data.Sqlite; + +namespace Emby.Server.Implementations.Data; + +public sealed class ManagedConnection : IDisposable +{ + private readonly SemaphoreSlim? _writeLock; + + private SqliteConnection _db; + + private bool _disposed = false; + + public ManagedConnection(SqliteConnection db, SemaphoreSlim? writeLock) + { + _db = db; + _writeLock = writeLock; + } + + public SqliteTransaction BeginTransaction() + => _db.BeginTransaction(); + + public SqliteCommand CreateCommand() + => _db.CreateCommand(); + + public void Execute(string commandText) + => _db.Execute(commandText); + + public SqliteCommand PrepareStatement(string sql) + => _db.PrepareStatement(sql); + + public IEnumerable Query(string commandText) + => _db.Query(commandText); + + public void Dispose() + { + if (_disposed) + { + return; + } + + if (_writeLock is null) + { + // Read connections are managed with an internal pool + _db.Dispose(); + } + else + { + // Write lock is managed by BaseSqliteRepository + // Don't dispose here + _writeLock.Release(); + } + + _db = null!; + + _disposed = true; + } +} diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index c2d31b474..d6a074195 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -601,7 +601,7 @@ namespace Emby.Server.Implementations.Data transaction.Commit(); } - private void SaveItemsInTransaction(SqliteConnection db, IEnumerable<(BaseItem Item, List AncestorIds, BaseItem TopParent, string UserDataKey, List InheritedTags)> tuples) + private void SaveItemsInTransaction(ManagedConnection db, IEnumerable<(BaseItem Item, List AncestorIds, BaseItem TopParent, string UserDataKey, List InheritedTags)> tuples) { using (var saveItemStatement = PrepareStatement(db, SaveItemCommandText)) using (var deleteAncestorsStatement = PrepareStatement(db, "delete from AncestorIds where ItemId=@ItemId")) @@ -1980,7 +1980,7 @@ namespace Emby.Server.Implementations.Data transaction.Commit(); } - private void InsertChapters(Guid idBlob, IReadOnlyList chapters, SqliteConnection db) + private void InsertChapters(Guid idBlob, IReadOnlyList chapters, ManagedConnection db) { var startIndex = 0; var limit = 100; @@ -4476,7 +4476,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type transaction.Commit(); } - private void ExecuteWithSingleParam(SqliteConnection db, string query, Guid value) + private void ExecuteWithSingleParam(ManagedConnection db, string query, Guid value) { using (var statement = PrepareStatement(db, query)) { @@ -4632,7 +4632,7 @@ AND Type = @InternalPersonType)"); return whereClauses; } - private void UpdateAncestors(Guid itemId, List ancestorIds, SqliteConnection db, SqliteCommand deleteAncestorsStatement) + private void UpdateAncestors(Guid itemId, List ancestorIds, ManagedConnection db, SqliteCommand deleteAncestorsStatement) { if (itemId.IsEmpty()) { @@ -5148,7 +5148,7 @@ AND Type = @InternalPersonType)"); return list; } - private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, SqliteConnection db) + private void UpdateItemValues(Guid itemId, List<(int MagicNumber, string Value)> values, ManagedConnection db) { if (itemId.IsEmpty()) { @@ -5167,7 +5167,7 @@ AND Type = @InternalPersonType)"); InsertItemValues(itemId, values, db); } - private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, SqliteConnection db) + private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, ManagedConnection db) { const int Limit = 100; var startIndex = 0; @@ -5239,7 +5239,7 @@ AND Type = @InternalPersonType)"); transaction.Commit(); } - private void InsertPeople(Guid id, List people, SqliteConnection db) + private void InsertPeople(Guid id, List people, ManagedConnection db) { const int Limit = 100; var startIndex = 0; @@ -5388,7 +5388,7 @@ AND Type = @InternalPersonType)"); transaction.Commit(); } - private void InsertMediaStreams(Guid id, IReadOnlyList streams, SqliteConnection db) + private void InsertMediaStreams(Guid id, IReadOnlyList streams, ManagedConnection db) { const int Limit = 10; var startIndex = 0; @@ -5772,7 +5772,7 @@ AND Type = @InternalPersonType)"); private void InsertMediaAttachments( Guid id, IReadOnlyList attachments, - SqliteConnection db, + ManagedConnection db, CancellationToken cancellationToken) { const int InsertAtOnce = 10; diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 66d009fd4..634eaf85e 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -86,7 +86,7 @@ namespace Emby.Server.Implementations.Data } } - private void ImportUserIds(SqliteConnection db, IEnumerable users) + private void ImportUserIds(ManagedConnection db, IEnumerable users) { var userIdsWithUserData = GetAllUserIdsWithUserData(db); @@ -107,7 +107,7 @@ namespace Emby.Server.Implementations.Data } } - private List GetAllUserIdsWithUserData(SqliteConnection db) + private List GetAllUserIdsWithUserData(ManagedConnection db) { var list = new List(); @@ -176,7 +176,7 @@ namespace Emby.Server.Implementations.Data } } - private static void SaveUserData(SqliteConnection db, long internalUserId, string key, UserItemData userData) + private static void SaveUserData(ManagedConnection db, long internalUserId, string key, UserItemData userData) { using (var statement = db.PrepareStatement("replace into UserDatas (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)")) { -- cgit v1.2.3