From eccc9a0b647b8cdd7380ea83ddbb77333ce691e3 Mon Sep 17 00:00:00 2001 From: Cody Robibero Date: Fri, 12 Apr 2024 17:44:16 -0600 Subject: Add index for lastPlayedDate (#11342) --- Emby.Server.Implementations/Data/SqliteUserDataRepository.cs | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'Emby.Server.Implementations/Data/SqliteUserDataRepository.cs') diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index a5edcc58c..20359e4ad 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -58,7 +58,8 @@ namespace Emby.Server.Implementations.Data "create unique index if not exists UserDatasIndex1 on UserDatas (key, userId)", "create index if not exists UserDatasIndex2 on UserDatas (key, userId, played)", "create index if not exists UserDatasIndex3 on UserDatas (key, userId, playbackPositionTicks)", - "create index if not exists UserDatasIndex4 on UserDatas (key, userId, isFavorite)")); + "create index if not exists UserDatasIndex4 on UserDatas (key, userId, isFavorite)", + "create index if not exists UserDatasIndex5 on UserDatas (key, userId, lastPlayedDate)")); if (!userDataTableExists) { -- 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/SqliteUserDataRepository.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/SqliteUserDataRepository.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