diff options
Diffstat (limited to 'Emby.Server.Implementations/Data')
4 files changed, 174 insertions, 57 deletions
diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index b1c99227c..5291999dc 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. @@ -29,17 +32,6 @@ namespace Emby.Server.Implementations.Data protected string DbFilePath { get; set; } /// <summary> - /// Gets or sets the number of write connections to create. - /// </summary> - /// <value>Path to the DB file.</value> - protected int WriteConnectionsCount { get; set; } = 1; - - /// <summary> - /// Gets or sets the number of read connections to create. - /// </summary> - protected int ReadConnectionsCount { get; set; } = 1; - - /// <summary> /// Gets the logger. /// </summary> /// <value>The logger.</value> @@ -98,9 +90,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 +173,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 +197,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 +212,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 +245,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 34d753093..81ee55d26 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; } /// <inheritdoc /> @@ -601,7 +600,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 +1260,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); @@ -1298,16 +1297,15 @@ namespace Emby.Server.Implementations.Data && type != typeof(Book) && type != typeof(LiveTvProgram) && type != typeof(AudioBook) - && type != typeof(Audio) && type != typeof(MusicAlbum); } private BaseItem GetItem(SqliteDataReader reader, InternalItemsQuery query) { - return GetItem(reader, query, HasProgramAttributes(query), HasEpisodeAttributes(query), HasServiceName(query), HasStartDate(query), HasTrailerTypes(query), HasArtistFields(query), HasSeriesFields(query)); + return GetItem(reader, query, HasProgramAttributes(query), HasEpisodeAttributes(query), HasServiceName(query), HasStartDate(query), HasTrailerTypes(query), HasArtistFields(query), HasSeriesFields(query), false); } - private BaseItem GetItem(SqliteDataReader reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool hasServiceName, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields) + private BaseItem GetItem(SqliteDataReader reader, InternalItemsQuery query, bool enableProgramAttributes, bool hasEpisodeAttributes, bool hasServiceName, bool queryHasStartDate, bool hasTrailerTypes, bool hasArtistFields, bool hasSeriesFields, bool skipDeserialization) { var typeString = reader.GetString(0); @@ -1320,7 +1318,7 @@ namespace Emby.Server.Implementations.Data BaseItem item = null; - if (TypeRequiresDeserialization(type)) + if (TypeRequiresDeserialization(type) && !skipDeserialization) { try { @@ -1888,7 +1886,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); @@ -1907,7 +1905,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); @@ -1981,7 +1979,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; @@ -2470,7 +2468,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)) @@ -2538,7 +2536,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)) @@ -2562,7 +2560,7 @@ namespace Emby.Server.Implementations.Data foreach (var row in statement.ExecuteQuery()) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields, query.SkipDeserialization); if (item is not null) { items.Add(item); @@ -2746,7 +2744,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) { @@ -2774,7 +2772,7 @@ namespace Emby.Server.Implementations.Data foreach (var row in statement.ExecuteQuery()) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields, false); if (item is not null) { list.Add(item); @@ -2928,7 +2926,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)) @@ -4477,7 +4475,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)) { @@ -4510,7 +4508,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 @@ -4548,7 +4546,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 @@ -4633,7 +4631,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()) { @@ -4788,7 +4786,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()) @@ -4988,8 +4986,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) { @@ -5021,7 +5019,7 @@ AND Type = @InternalPersonType)"); foreach (var row in statement.ExecuteQuery()) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields, false); if (item is not null) { var countStartColumn = columns.Count - 1; @@ -5149,7 +5147,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()) { @@ -5168,7 +5166,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; @@ -5222,24 +5220,25 @@ AND Type = @InternalPersonType)"); throw new ArgumentNullException(nameof(itemId)); } - ArgumentNullException.ThrowIfNull(people); - CheckDisposed(); using var connection = GetConnection(); using var transaction = connection.BeginTransaction(); - // First delete chapters + // Delete all existing people first using var command = connection.CreateCommand(); command.CommandText = "delete from People where ItemId=@ItemId"; command.TryBind("@ItemId", itemId); command.ExecuteNonQuery(); - InsertPeople(itemId, people, connection); + if (people is not null) + { + InsertPeople(itemId, people, connection); + } 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 +5334,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 +5387,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 +5721,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 +5771,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")) { |