diff options
Diffstat (limited to 'Emby.Server.Implementations/Data')
4 files changed, 163 insertions, 34 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index b1c99227c..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; /// <summary> /// Initializes a new instance of the <see cref="BaseSqliteRepository"/> class. @@ -98,9 +101,55 @@ namespace Emby.Server.Implementations.Data } } - protected SqliteConnection GetConnection() + protected ManagedConnection GetConnection(bool readOnly = false) { - var connection = new SqliteConnection($"Filename={DbFilePath}"); + 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<string> GetColumnNames(SqliteConnection connection, string table) + protected List<string> GetColumnNames(ManagedConnection connection, string table) { var columnNames = new List<string>(); @@ -174,7 +223,7 @@ namespace Emby.Server.Implementations.Data return columnNames; } - protected void AddColumn(SqliteConnection connection, string table, string columnName, string type, List<string> existingColumnNames) + protected void AddColumn(ManagedConnection connection, string table, string columnName, string type, List<string> 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<SqliteDataReader> 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 a09988fee..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<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples) + private void SaveItemsInTransaction(ManagedConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples) { using (var saveItemStatement = PrepareStatement(db, SaveItemCommandText)) using (var deleteAncestorsStatement = PrepareStatement(db, "delete from AncestorIds where ItemId=@ItemId")) @@ -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<ChapterInfo>(); - 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); @@ -1980,7 +1980,7 @@ namespace Emby.Server.Implementations.Data transaction.Commit(); } - private void InsertChapters(Guid idBlob, IReadOnlyList<ChapterInfo> chapters, SqliteConnection db) + private void InsertChapters(Guid idBlob, IReadOnlyList<ChapterInfo> chapters, ManagedConnection db) { var startIndex = 0; var limit = 100; @@ -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<BaseItem>(); 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<BaseItem>(); var result = new QueryResult<BaseItem>(); - 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<Guid>(); using (new QueryTimeLogger(Logger, commandText)) - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText)) { if (EnableJoinUserData(query)) @@ -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)) { @@ -4509,7 +4509,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type } var list = new List<string>(); - 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<PersonInfo>(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, commandText.ToString())) { // Run this again to bind the params @@ -4632,7 +4632,7 @@ AND Type = @InternalPersonType)"); return whereClauses; } - private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, SqliteConnection db, SqliteCommand deleteAncestorsStatement) + private void UpdateAncestors(Guid itemId, List<Guid> ancestorIds, ManagedConnection db, SqliteCommand deleteAncestorsStatement) { if (itemId.IsEmpty()) { @@ -4787,7 +4787,7 @@ AND Type = @InternalPersonType)"); var list = new List<string>(); 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) { @@ -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<PersonInfo> people, SqliteConnection db) + private void InsertPeople(Guid id, List<PersonInfo> people, ManagedConnection db) { const int Limit = 100; var startIndex = 0; @@ -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<MediaStream>(); @@ -5388,7 +5388,7 @@ AND Type = @InternalPersonType)"); transaction.Commit(); } - private void InsertMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, SqliteConnection db) + private void InsertMediaStreams(Guid id, IReadOnlyList<MediaStream> streams, ManagedConnection db) { const int Limit = 10; var startIndex = 0; @@ -5722,7 +5722,7 @@ AND Type = @InternalPersonType)"); cmdText += " order by AttachmentIndex ASC"; var list = new List<MediaAttachment>(); - using (var connection = GetConnection()) + using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, cmdText)) { statement.TryBind("@ItemId", query.ItemId); @@ -5772,7 +5772,7 @@ AND Type = @InternalPersonType)"); private void InsertMediaAttachments( Guid id, IReadOnlyList<MediaAttachment> 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 20359e4ad..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<User> users) + private void ImportUserIds(ManagedConnection db, IEnumerable<User> users) { var userIdsWithUserData = GetAllUserIdsWithUserData(db); @@ -107,7 +107,7 @@ namespace Emby.Server.Implementations.Data } } - private List<Guid> GetAllUserIdsWithUserData(SqliteConnection db) + private List<Guid> GetAllUserIdsWithUserData(ManagedConnection db) { var list = new List<Guid>(); @@ -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)")) { @@ -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")) { |