From 613f4296e395b0442984e472a996eaadc07915fe Mon Sep 17 00:00:00 2001 From: cvium Date: Mon, 21 Aug 2023 12:13:32 +0200 Subject: loading works --- .../Data/SqliteExtensions.cs | 360 ++++++++------------- 1 file changed, 128 insertions(+), 232 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 4055b0ba1..9e5cb1702 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -1,11 +1,12 @@ -#nullable disable +#nullable enable #pragma warning disable CS1591 using System; using System.Collections.Generic; +using System.Data; using System.Diagnostics; using System.Globalization; -using SQLitePCL.pretty; +using Microsoft.Data.Sqlite; namespace Emby.Server.Implementations.Data { @@ -52,7 +53,68 @@ namespace Emby.Server.Implementations.Data "yy-MM-dd" }; - public static void RunQueries(this SQLiteDatabaseConnection connection, string[] queries) + private static void EnsureOpen(this SqliteConnection sqliteConnection) + { + if (sqliteConnection.State == ConnectionState.Closed) + { + sqliteConnection.Open(); + } + } + + public static IEnumerable Query(this SqliteConnection sqliteConnection, string commandText) + { + if (sqliteConnection.State != ConnectionState.Open) + { + sqliteConnection.Open(); + } + + var command = sqliteConnection.CreateCommand(); + command.CommandText = commandText; + using (var reader = command.ExecuteReader()) + { + while (reader.Read()) + { + yield return reader; + } + } + } + + public static void Execute(this SqliteConnection sqliteConnection, string commandText) + { + sqliteConnection.EnsureOpen(); + var command = sqliteConnection.CreateCommand(); + command.CommandText = commandText; + command.ExecuteNonQuery(); + } + + public static void RunInTransaction(this SqliteConnection sqliteConnection, Action action) + { + sqliteConnection.EnsureOpen(); + + using var transaction = sqliteConnection.BeginTransaction(); + action(sqliteConnection); + transaction.Commit(); + } + + public static bool RunInTransaction(this SqliteConnection sqliteConnection, Func action) + { + sqliteConnection.EnsureOpen(); + using var transaction = sqliteConnection.BeginTransaction(); + var result = action(sqliteConnection); + transaction.Commit(); + return result; + } + + public static void ExecuteAll(this SqliteConnection sqliteConnection, string commandText) + { + sqliteConnection.EnsureOpen(); + + var command = sqliteConnection.CreateCommand(); + command.CommandText = commandText; + command.ExecuteNonQuery(); + } + + public static void RunQueries(this SqliteConnection connection, string[] queries) { ArgumentNullException.ThrowIfNull(queries); @@ -62,11 +124,6 @@ namespace Emby.Server.Implementations.Data }); } - public static Guid ReadGuidFromBlob(this ResultSetValue result) - { - return new Guid(result.ToBlob()); - } - public static string ToDateTimeParamValue(this DateTime dateValue) { var kind = DateTimeKind.Utc; @@ -83,27 +140,26 @@ namespace Emby.Server.Implementations.Data private static string GetDateTimeKindFormat(DateTimeKind kind) => (kind == DateTimeKind.Utc) ? DatetimeFormatUtc : DatetimeFormatLocal; - public static DateTime ReadDateTime(this ResultSetValue result) + public static DateTime ReadDateTime(this SqliteDataReader result) { var dateText = result.ToString(); return DateTime.ParseExact( - dateText, + dateText!, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AdjustToUniversal); } - public static bool TryReadDateTime(this IReadOnlyList reader, int index, out DateTime result) + public static bool TryReadDateTime(this SqliteDataReader reader, int index, out DateTime result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - var dateText = item.ToString(); + var dateText = reader.GetString(index); if (DateTime.TryParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AdjustToUniversal, out var dateTimeResult)) { @@ -115,335 +171,175 @@ namespace Emby.Server.Implementations.Data return false; } - public static bool TryGetGuid(this IReadOnlyList reader, int index, out Guid result) + public static bool TryGetGuid(this SqliteDataReader reader, int index, out Guid result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ReadGuidFromBlob(); + result = reader.GetGuid(index); return true; } - public static bool IsDbNull(this ResultSetValue result) + public static bool TryGetString(this SqliteDataReader reader, int index, out string result) { - return result.SQLiteType == SQLiteType.Null; - } - - public static string GetString(this IReadOnlyList result, int index) - { - return result[index].ToString(); - } + result = string.Empty; - public static bool TryGetString(this IReadOnlyList reader, int index, out string result) - { - result = null; - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { return false; } - result = item.ToString(); + result = reader.GetString(index); return true; } - public static bool GetBoolean(this IReadOnlyList result, int index) + public static bool TryGetBoolean(this SqliteDataReader reader, int index, out bool result) { - return result[index].ToBool(); - } - - public static bool TryGetBoolean(this IReadOnlyList reader, int index, out bool result) - { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToBool(); + result = reader.GetBoolean(index); return true; } - public static bool TryGetInt32(this IReadOnlyList reader, int index, out int result) + public static bool TryGetInt32(this SqliteDataReader reader, int index, out int result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToInt(); + result = reader.GetInt32(index); return true; } - public static long GetInt64(this IReadOnlyList result, int index) + public static bool TryGetInt64(this SqliteDataReader reader, int index, out long result) { - return result[index].ToInt64(); - } - - public static bool TryGetInt64(this IReadOnlyList reader, int index, out long result) - { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToInt64(); + result = reader.GetInt64(index); return true; } - public static bool TryGetSingle(this IReadOnlyList reader, int index, out float result) + public static bool TryGetSingle(this SqliteDataReader reader, int index, out float result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToFloat(); + result = reader.GetFloat(index); return true; } - public static bool TryGetDouble(this IReadOnlyList reader, int index, out double result) + public static bool TryGetDouble(this SqliteDataReader reader, int index, out double result) { - var item = reader[index]; - if (item.IsDbNull()) + if (reader.IsDBNull(index)) { result = default; return false; } - result = item.ToDouble(); + result = reader.GetDouble(index); return true; } - public static Guid GetGuid(this IReadOnlyList result, int index) - { - return result[index].ReadGuidFromBlob(); - } - [Conditional("DEBUG")] private static void CheckName(string name) { throw new ArgumentException("Invalid param name: " + name, nameof(name)); } - public static void TryBind(this IStatement statement, string name, double value) + public static void TryBind(this SqliteCommand statement, string name, Guid value) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + if (statement.Parameters.Contains(name)) { - bindParam.Bind(value); + statement.Parameters[name].Value = value; } else { - CheckName(name); + statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob) { Value = value }); } } - public static void TryBind(this IStatement statement, string name, string value) + public static void TryBind(this SqliteCommand statement, string name, object? value) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + var preparedValue = value ?? DBNull.Value; + if (statement.Parameters.Contains(name)) { - if (value is null) - { - bindParam.BindNull(); - } - else - { - bindParam.Bind(value); - } + statement.Parameters[name].Value = preparedValue; } else { - CheckName(name); + statement.Parameters.AddWithValue(name, preparedValue); } } - public static void TryBind(this IStatement statement, string name, bool value) + public static void TryBind(this SqliteCommand statement, string name, byte[] value) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + if (statement.Parameters.Contains(name)) { - bindParam.Bind(value); + statement.Parameters[name].Value = value; } else { - CheckName(name); + statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob, value.Length) { Value = value }); } } - public static void TryBind(this IStatement statement, string name, float value) + public static void TryBindNull(this SqliteCommand statement, string name) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, int value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, Guid value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - Span byteValue = stackalloc byte[16]; - value.TryWriteBytes(byteValue); - bindParam.Bind(byteValue); - } - else - { - CheckName(name); - } + statement.TryBind(name, DBNull.Value); } - public static void TryBind(this IStatement statement, string name, DateTime value) + public static IEnumerable ExecuteQuery(this SqliteCommand command) { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) + using (var reader = command.ExecuteReader()) { - bindParam.Bind(value.ToDateTimeParamValue()); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, long value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, ReadOnlySpan value) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.Bind(value); - } - else - { - CheckName(name); - } - } - - public static void TryBindNull(this IStatement statement, string name) - { - if (statement.BindParameters.TryGetValue(name, out IBindParameter bindParam)) - { - bindParam.BindNull(); - } - else - { - CheckName(name); - } - } - - public static void TryBind(this IStatement statement, string name, DateTime? value) - { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); + while (reader.Read()) + { + yield return reader; + } } } - public static void TryBind(this IStatement statement, string name, Guid? value) + public static int SelectScalarInt(this SqliteCommand command) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } + var result = command.ExecuteScalar(); + return Convert.ToInt32(result!, CultureInfo.InvariantCulture); } - public static void TryBind(this IStatement statement, string name, double? value) + public static SqliteCommand PrepareStatement(this SqliteConnection sqliteConnection, string sql) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } + sqliteConnection.EnsureOpen(); + var command = sqliteConnection.CreateCommand(); + command.CommandText = sql; + return command; } - public static void TryBind(this IStatement statement, string name, int? value) + // Hacky + public static void MoveNext(this SqliteCommand sqliteCommand) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } + sqliteCommand.Prepare(); + var result = sqliteCommand.ExecuteNonQuery(); } - public static void TryBind(this IStatement statement, string name, float? value) + public static byte[] GetBlob(this SqliteDataReader reader, int index) { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } - } - - public static void TryBind(this IStatement statement, string name, bool? value) - { - if (value.HasValue) - { - TryBind(statement, name, value.Value); - } - else - { - TryBindNull(statement, name); - } - } - - public static IEnumerable> ExecuteQuery(this IStatement statement) - { - while (statement.MoveNext()) - { - yield return statement.Current; - } + // Have to reset to casting as there isn't a publicly available GetBlob method + return (byte[])reader.GetValue(index); } } } -- cgit v1.2.3 From 493229cc15ea865d151b54d5d6d5c1eea831219a Mon Sep 17 00:00:00 2001 From: cvium Date: Mon, 21 Aug 2023 12:27:07 +0200 Subject: fix guid blobs --- .../Data/SqliteExtensions.cs | 20 ++++----- .../Data/SqliteItemRepository.cs | 51 ++++++++-------------- 2 files changed, 29 insertions(+), 42 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 9e5cb1702..541153af2 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -264,17 +264,10 @@ namespace Emby.Server.Implementations.Data public static void TryBind(this SqliteCommand statement, string name, Guid value) { - if (statement.Parameters.Contains(name)) - { - statement.Parameters[name].Value = value; - } - else - { - statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob) { Value = value }); - } + statement.TryBind(name, value, true); } - public static void TryBind(this SqliteCommand statement, string name, object? value) + public static void TryBind(this SqliteCommand statement, string name, object? value, bool isBlob = false) { var preparedValue = value ?? DBNull.Value; if (statement.Parameters.Contains(name)) @@ -283,7 +276,14 @@ namespace Emby.Server.Implementations.Data } else { - statement.Parameters.AddWithValue(name, preparedValue); + if (isBlob) + { + statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob) { Value = value }); + } + else + { + statement.Parameters.AddWithValue(name, preparedValue); + } } } diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 4ceaafa12..f3ad1121a 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -4891,11 +4891,6 @@ AND Type = @InternalPersonType)"); CheckDisposed(); - // TODO how to handle span? - Span itemIdBlob2 = stackalloc byte[16]; - itemId.TryWriteBytes(itemIdBlob2); - var itemIdBlob = Encoding.ASCII.GetBytes(itemId.ToString()); - // First delete // TODO deleteAncestorsStatement.Parameters.Clear(); deleteAncestorsStatement.TryBind("@ItemId", itemId); @@ -4921,14 +4916,13 @@ AND Type = @InternalPersonType)"); using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", itemIdBlob); + statement.TryBind("@ItemId", itemId); for (var i = 0; i < ancestorIds.Count; i++) { var index = i.ToString(CultureInfo.InvariantCulture); var ancestorId = ancestorIds[i]; - itemIdBlob = Encoding.ASCII.GetBytes(itemId.ToString()); statement.TryBind("@AncestorId" + index, ancestorId); statement.TryBind("@AncestorIdText" + index, ancestorId.ToString("N", CultureInfo.InvariantCulture)); @@ -5416,17 +5410,15 @@ AND Type = @InternalPersonType)"); CheckDisposed(); - var guidBlob = itemId.ToByteArray(); - // First delete using var command = db.PrepareStatement("delete from ItemValues where ItemId=@Id"); - command.TryBind("@Id", guidBlob); + command.TryBind("@Id", itemId); command.ExecuteNonQuery(); - InsertItemValues(guidBlob, values, db); + InsertItemValues(itemId, values, db); } - private void InsertItemValues(byte[] idBlob, List<(int MagicNumber, string Value)> values, SqliteConnection db) + private void InsertItemValues(Guid id, List<(int MagicNumber, string Value)> values, SqliteConnection db) { const int Limit = 100; var startIndex = 0; @@ -5450,7 +5442,7 @@ AND Type = @InternalPersonType)"); using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { @@ -5496,20 +5488,18 @@ AND Type = @InternalPersonType)"); connection.RunInTransaction( db => { - var itemIdBlob = itemId.ToByteArray(); - // First delete chapters using var command = db.CreateCommand(); command.CommandText = "delete from People where ItemId=@ItemId"; - command.TryBind("@ItemId", itemIdBlob); + command.TryBind("@ItemId", itemId); command.ExecuteNonQuery(); - InsertPeople(itemIdBlob, people, db); + InsertPeople(itemId, people, db); }); } } - private void InsertPeople(byte[] idBlob, List people, SqliteConnection db) + private void InsertPeople(Guid id, List people, SqliteConnection db) { const int Limit = 100; var startIndex = 0; @@ -5533,7 +5523,7 @@ AND Type = @InternalPersonType)"); using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { @@ -5651,19 +5641,17 @@ AND Type = @InternalPersonType)"); connection.RunInTransaction( db => { - var itemIdBlob = id.ToByteArray(); - // Delete existing mediastreams using var command = db.PrepareStatement("delete from mediastreams where ItemId=@ItemId"); - command.TryBind("@ItemId", itemIdBlob); + command.TryBind("@ItemId", id); command.ExecuteNonQuery(); - InsertMediaStreams(itemIdBlob, streams, db); + InsertMediaStreams(id, streams, db); }); } } - private void InsertMediaStreams(byte[] idBlob, IReadOnlyList streams, SqliteConnection db) + private void InsertMediaStreams(Guid id, IReadOnlyList streams, SqliteConnection db) { const int Limit = 10; var startIndex = 0; @@ -5695,7 +5683,7 @@ AND Type = @InternalPersonType)"); using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { @@ -5731,6 +5719,7 @@ AND Type = @InternalPersonType)"); statement.TryBind("@PixelFormat" + index, stream.PixelFormat); statement.TryBind("@BitDepth" + index, stream.BitDepth); + statement.TryBind("@IsAnamorphic" + index, stream.IsAnamorphic); statement.TryBind("@IsExternal" + index, stream.IsExternal); statement.TryBind("@RefFrames" + index, stream.RefFrames); @@ -6000,7 +5989,7 @@ AND Type = @InternalPersonType)"); using (var connection = GetConnection(true)) using (var statement = PrepareStatement(connection, cmdText)) { - statement.TryBind("@ItemId", query.ItemId.ToByteArray()); + statement.TryBind("@ItemId", query.ItemId); if (query.Index.HasValue) { @@ -6036,19 +6025,17 @@ AND Type = @InternalPersonType)"); connection.RunInTransaction( db => { - var itemIdBlob = id.ToByteArray(); - using var command = db.PrepareStatement("delete from mediaattachments where ItemId=@ItemId"); - command.TryBind("@ItemId", itemIdBlob); + command.TryBind("@ItemId", id); command.ExecuteNonQuery(); - InsertMediaAttachments(itemIdBlob, attachments, db, cancellationToken); + InsertMediaAttachments(id, attachments, db, cancellationToken); }); } } private void InsertMediaAttachments( - byte[] idBlob, + Guid id, IReadOnlyList attachments, SqliteConnection db, CancellationToken cancellationToken) @@ -6084,7 +6071,7 @@ AND Type = @InternalPersonType)"); using (var statement = PrepareStatement(db, insertText.ToString())) { - statement.TryBind("@ItemId", idBlob); + statement.TryBind("@ItemId", id); for (var i = startIndex; i < endIndex; i++) { -- cgit v1.2.3 From 0867812c1fabfce52abb1f8fdb17edad822a61af Mon Sep 17 00:00:00 2001 From: cvium Date: Mon, 21 Aug 2023 12:46:30 +0200 Subject: more using --- Emby.Server.Implementations/Data/SqliteExtensions.cs | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 541153af2..027771a5a 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -68,7 +68,7 @@ namespace Emby.Server.Implementations.Data sqliteConnection.Open(); } - var command = sqliteConnection.CreateCommand(); + using var command = sqliteConnection.CreateCommand(); command.CommandText = commandText; using (var reader = command.ExecuteReader()) { @@ -82,7 +82,7 @@ namespace Emby.Server.Implementations.Data public static void Execute(this SqliteConnection sqliteConnection, string commandText) { sqliteConnection.EnsureOpen(); - var command = sqliteConnection.CreateCommand(); + using var command = sqliteConnection.CreateCommand(); command.CommandText = commandText; command.ExecuteNonQuery(); } @@ -109,7 +109,7 @@ namespace Emby.Server.Implementations.Data { sqliteConnection.EnsureOpen(); - var command = sqliteConnection.CreateCommand(); + using var command = sqliteConnection.CreateCommand(); command.CommandText = commandText; command.ExecuteNonQuery(); } @@ -333,7 +333,7 @@ namespace Emby.Server.Implementations.Data public static void MoveNext(this SqliteCommand sqliteCommand) { sqliteCommand.Prepare(); - var result = sqliteCommand.ExecuteNonQuery(); + sqliteCommand.ExecuteNonQuery(); } public static byte[] GetBlob(this SqliteDataReader reader, int index) -- cgit v1.2.3 From 061d79c113404359068e94256104f955720bd1eb Mon Sep 17 00:00:00 2001 From: cvium Date: Mon, 21 Aug 2023 14:12:49 +0200 Subject: remove runintransaction --- .../Data/BaseSqliteRepository.cs | 24 +- .../Data/SqliteExtensions.cs | 56 +- .../Data/SqliteItemRepository.cs | 652 ++++++++++----------- .../Data/SqliteUserDataRepository.cs | 100 ++-- 4 files changed, 367 insertions(+), 465 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 2ce87f5b4..6ee2d800c 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -146,22 +146,16 @@ namespace Emby.Server.Implementations.Data 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()) + { + if (string.Equals(name, row.GetString(0), StringComparison.OrdinalIgnoreCase)) { - using (var statement = PrepareStatement(db, "select DISTINCT tbl_name from sqlite_master")) - { - foreach (var row in statement.ExecuteQuery()) - { - if (string.Equals(name, row.GetString(0), StringComparison.OrdinalIgnoreCase)) - { - return true; - } - } - } - - return false; - }); + return true; + } + } + + return false; } protected List GetColumnNames(SqliteConnection connection, string table) diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 027771a5a..40cecbb6b 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -87,24 +87,6 @@ namespace Emby.Server.Implementations.Data command.ExecuteNonQuery(); } - public static void RunInTransaction(this SqliteConnection sqliteConnection, Action action) - { - sqliteConnection.EnsureOpen(); - - using var transaction = sqliteConnection.BeginTransaction(); - action(sqliteConnection); - transaction.Commit(); - } - - public static bool RunInTransaction(this SqliteConnection sqliteConnection, Func action) - { - sqliteConnection.EnsureOpen(); - using var transaction = sqliteConnection.BeginTransaction(); - var result = action(sqliteConnection); - transaction.Commit(); - return result; - } - public static void ExecuteAll(this SqliteConnection sqliteConnection, string commandText) { sqliteConnection.EnsureOpen(); @@ -117,11 +99,9 @@ namespace Emby.Server.Implementations.Data public static void RunQueries(this SqliteConnection connection, string[] queries) { ArgumentNullException.ThrowIfNull(queries); - - connection.RunInTransaction(conn => - { - conn.ExecuteAll(string.Join(';', queries)); - }); + using var transaction = connection.BeginTransaction(); + connection.ExecuteAll(string.Join(';', queries)); + transaction.Commit(); } public static string ToDateTimeParamValue(this DateTime dateValue) @@ -140,17 +120,6 @@ namespace Emby.Server.Implementations.Data private static string GetDateTimeKindFormat(DateTimeKind kind) => (kind == DateTimeKind.Utc) ? DatetimeFormatUtc : DatetimeFormatLocal; - public static DateTime ReadDateTime(this SqliteDataReader result) - { - var dateText = result.ToString(); - - return DateTime.ParseExact( - dateText!, - _datetimeFormats, - DateTimeFormatInfo.InvariantInfo, - DateTimeStyles.AdjustToUniversal); - } - public static bool TryReadDateTime(this SqliteDataReader reader, int index, out DateTime result) { if (reader.IsDBNull(index)) @@ -256,12 +225,6 @@ namespace Emby.Server.Implementations.Data return true; } - [Conditional("DEBUG")] - private static void CheckName(string name) - { - throw new ArgumentException("Invalid param name: " + name, nameof(name)); - } - public static void TryBind(this SqliteCommand statement, string name, Guid value) { statement.TryBind(name, value, true); @@ -328,18 +291,5 @@ namespace Emby.Server.Implementations.Data command.CommandText = sql; return command; } - - // Hacky - public static void MoveNext(this SqliteCommand sqliteCommand) - { - sqliteCommand.Prepare(); - sqliteCommand.ExecuteNonQuery(); - } - - public static byte[] GetBlob(this SqliteDataReader reader, int index) - { - // Have to reset to casting as there isn't a publicly available GetBlob method - return (byte[])reader.GetValue(index); - } } } diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 0434ed89d..7e1c3bb4c 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -440,122 +440,123 @@ namespace Emby.Server.Implementations.Data { connection.RunQueries(queries); - connection.RunInTransaction( - db => - { - var existingColumnNames = GetColumnNames(db, "AncestorIds"); - AddColumn(db, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); - - existingColumnNames = GetColumnNames(db, "TypedBaseItems"); - - AddColumn(db, "TypedBaseItems", "Path", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Name", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "MediaType", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Overview", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Genres", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SortName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "LockedFields", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Studios", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Audio", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Tags", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "UnratedType", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "TopParentId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CriticRating", "Float", existingColumnNames); - AddColumn(db, "TypedBaseItems", "CleanName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Album", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "LUFS", "Float", existingColumnNames); - AddColumn(db, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeasonName", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Tagline", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Images", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Artists", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ExternalId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "ShowId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Width", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Height", "INT", existingColumnNames); - AddColumn(db, "TypedBaseItems", "Size", "BIGINT", existingColumnNames); - - existingColumnNames = GetColumnNames(db, "ItemValues"); - AddColumn(db, "ItemValues", "CleanValue", "Text", existingColumnNames); - - existingColumnNames = GetColumnNames(db, ChaptersTableName); - AddColumn(db, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames); - - existingColumnNames = GetColumnNames(db, "MediaStreams"); - AddColumn(db, "MediaStreams", "IsAvc", "BIT", existingColumnNames); - AddColumn(db, "MediaStreams", "TimeBase", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "Title", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "Comment", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "CodecTag", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "BitDepth", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "RefFrames", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); - - AddColumn(db, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); - AddColumn(db, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); - - AddColumn(db, "MediaStreams", "DvVersionMajor", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "DvVersionMinor", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "DvProfile", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "DvLevel", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "RpuPresentFlag", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "ElPresentFlag", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "BlPresentFlag", "INT", existingColumnNames); - AddColumn(db, "MediaStreams", "DvBlSignalCompatibilityId", "INT", existingColumnNames); - - AddColumn(db, "MediaStreams", "IsHearingImpaired", "BIT", existingColumnNames); - }); + using (var transaction = connection.BeginTransaction()) + { + var existingColumnNames = GetColumnNames(connection, "AncestorIds"); + AddColumn(connection, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "TypedBaseItems"); + + AddColumn(connection, "TypedBaseItems", "Path", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Name", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "MediaType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Overview", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Genres", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SortName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "LockedFields", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Studios", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Audio", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Tags", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "UnratedType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TopParentId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CriticRating", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CleanName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Album", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "LUFS", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeasonName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Tagline", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Images", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Artists", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ShowId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Width", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Height", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Size", "BIGINT", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "ItemValues"); + AddColumn(connection, "ItemValues", "CleanValue", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, ChaptersTableName); + AddColumn(connection, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "MediaStreams"); + AddColumn(connection, "MediaStreams", "IsAvc", "BIT", existingColumnNames); + AddColumn(connection, "MediaStreams", "TimeBase", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "Title", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "Comment", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "CodecTag", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "BitDepth", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "RefFrames", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "DvVersionMajor", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvVersionMinor", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvProfile", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvLevel", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "RpuPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ElPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "BlPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvBlSignalCompatibilityId", "INT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "IsHearingImpaired", "BIT", existingColumnNames); + + transaction.Commit(); + } connection.RunQueries(postQueries); } @@ -567,20 +568,14 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - using (var saveImagesStatement = PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) - { - saveImagesStatement.TryBind("@Id", item.Id); - saveImagesStatement.TryBind("@Images", SerializeImages(item.ImageInfos)); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + using var saveImagesStatement = PrepareStatement(connection, "Update TypedBaseItems set Images=@Images where guid=@Id"); + saveImagesStatement.TryBind("@Id", item.Id); + saveImagesStatement.TryBind("@Images", SerializeImages(item.ImageInfos)); - saveImagesStatement.MoveNext(); - } - }); - } + saveImagesStatement.ExecuteNonQuery(); + transaction.Commit(); } /// @@ -616,14 +611,10 @@ namespace Emby.Server.Implementations.Data tuples[i] = (item, ancestorIds, topParent, userdataKey, inheritedTags); } - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - SaveItemsInTransaction(db, tuples); - }); - } + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + SaveItemsInTransaction(connection, tuples); + transaction.Commit(); } private void SaveItemsInTransaction(SqliteConnection db, IEnumerable<(BaseItem Item, List AncestorIds, BaseItem TopParent, string UserDataKey, List InheritedTags)> tuples) @@ -1030,7 +1021,7 @@ namespace Emby.Server.Implementations.Data saveItemStatement.TryBind("@OwnerId", ownerId); } - saveItemStatement.MoveNext(); + saveItemStatement.ExecuteNonQuery(); } internal static string SerializeProviderIds(Dictionary providerIds) @@ -2060,19 +2051,15 @@ namespace Emby.Server.Implementations.Data ArgumentNullException.ThrowIfNull(chapters); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - // First delete chapters - using var command = db.PrepareStatement($"delete from {ChaptersTableName} where ItemId=@ItemId"); - command.TryBind("@ItemId", id); - command.ExecuteNonQuery(); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + // First delete chapters + using var command = connection.PrepareStatement($"delete from {ChaptersTableName} where ItemId=@ItemId"); + command.TryBind("@ItemId", id); + command.ExecuteNonQuery(); - InsertChapters(id, chapters, db); - }); - } + InsertChapters(id, chapters, connection); + transaction.Commit(); } private void InsertChapters(Guid idBlob, IReadOnlyList chapters, SqliteConnection db) @@ -2115,7 +2102,7 @@ namespace Emby.Server.Implementations.Data } // TODO statement.Parameters.Clear(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += limit; @@ -2848,68 +2835,65 @@ namespace Emby.Server.Implementations.Data var list = new List(); var result = new QueryResult(); - using (var connection = GetConnection(true)) + using var connection = GetConnection(true); + using var transaction = connection.BeginTransaction(); + if (!isReturningZeroItems) { - connection.RunInTransaction( - db => + using (new QueryTimeLogger(Logger, itemQuery, "GetItems.ItemQuery")) + using (var statement = PrepareStatement(connection, itemQuery)) + { + if (EnableJoinUserData(query)) { - if (!isReturningZeroItems) + statement.TryBind("@UserId", query.User.InternalId); + } + + BindSimilarParams(query, statement); + BindSearchParams(query, statement); + + // Running this again will bind the params + GetWhereClauses(query, statement); + + var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasServiceName = HasServiceName(query); + var hasProgramAttributes = HasProgramAttributes(query); + var hasStartDate = HasStartDate(query); + var hasTrailerTypes = HasTrailerTypes(query); + var hasArtistFields = HasArtistFields(query); + var hasSeriesFields = HasSeriesFields(query); + + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item is not null) { - using (new QueryTimeLogger(Logger, itemQuery, "GetItems.ItemQuery")) - using (var statement = PrepareStatement(db, itemQuery)) - { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - - // Running this again will bind the params - GetWhereClauses(query, statement); - - var hasEpisodeAttributes = HasEpisodeAttributes(query); - var hasServiceName = HasServiceName(query); - var hasProgramAttributes = HasProgramAttributes(query); - var hasStartDate = HasStartDate(query); - var hasTrailerTypes = HasTrailerTypes(query); - var hasArtistFields = HasArtistFields(query); - var hasSeriesFields = HasSeriesFields(query); - - foreach (var row in statement.ExecuteQuery()) - { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item is not null) - { - list.Add(item); - } - } - } + list.Add(item); } + } + } + } - if (query.EnableTotalRecordCount) - { - using (new QueryTimeLogger(Logger, totalRecordCountQuery, "GetItems.TotalRecordCount")) - using (var statement = PrepareStatement(db, totalRecordCountQuery)) - { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + if (query.EnableTotalRecordCount) + { + using (new QueryTimeLogger(Logger, totalRecordCountQuery, "GetItems.TotalRecordCount")) + using (var statement = PrepareStatement(connection, totalRecordCountQuery)) + { + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.InternalId); + } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); + BindSimilarParams(query, statement); + BindSearchParams(query, statement); - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - result.TotalRecordCount = statement.SelectScalarInt(); - } - } - }); + result.TotalRecordCount = statement.SelectScalarInt(); + } } + transaction.Commit(); + result.StartIndex = query.StartIndex ?? 0; result.Items = list; return result; @@ -4662,28 +4646,18 @@ namespace Emby.Server.Implementations.Data public void UpdateInheritedValues() { - string sql = string.Join( - ';', - new string[] - { - "delete from ItemValues where type = 6", - - "insert into ItemValues (ItemId, Type, Value, CleanValue) select ItemId, 6, Value, CleanValue from ItemValues where Type=4", - - @"insert into ItemValues (ItemId, Type, Value, CleanValue) select AncestorIds.itemid, 6, ItemValues.Value, ItemValues.CleanValue + const string Statements = """ +delete from ItemValues where type = 6; +insert into ItemValues (ItemId, Type, Value, CleanValue) select ItemId, 6, Value, CleanValue from ItemValues where Type=4; +insert into ItemValues (ItemId, Type, Value, CleanValue) select AncestorIds.itemid, 6, ItemValues.Value, ItemValues.CleanValue FROM AncestorIds LEFT JOIN ItemValues ON (AncestorIds.AncestorId = ItemValues.ItemId) -where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type = 4 " - }); - - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - connection.ExecuteAll(sql); - }); - } +where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type = 4; +"""; + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + connection.ExecuteAll(Statements); + transaction.Commit(); } public void DeleteItem(Guid id) @@ -4695,42 +4669,36 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - Span idBlob = stackalloc byte[16]; - id.TryWriteBytes(idBlob); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + // Delete people + ExecuteWithSingleParam(connection, "delete from People where ItemId=@Id", id); - // Delete people - ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", idBlob); + // Delete chapters + ExecuteWithSingleParam(connection, "delete from " + ChaptersTableName + " where ItemId=@Id", id); - // Delete chapters - ExecuteWithSingleParam(db, "delete from " + ChaptersTableName + " where ItemId=@Id", idBlob); + // Delete media streams + ExecuteWithSingleParam(connection, "delete from mediastreams where ItemId=@Id", id); - // Delete media streams - ExecuteWithSingleParam(db, "delete from mediastreams where ItemId=@Id", idBlob); + // Delete ancestors + ExecuteWithSingleParam(connection, "delete from AncestorIds where ItemId=@Id", id); - // Delete ancestors - ExecuteWithSingleParam(db, "delete from AncestorIds where ItemId=@Id", idBlob); + // Delete item values + ExecuteWithSingleParam(connection, "delete from ItemValues where ItemId=@Id", id); - // Delete item values - ExecuteWithSingleParam(db, "delete from ItemValues where ItemId=@Id", idBlob); + // Delete the item + ExecuteWithSingleParam(connection, "delete from TypedBaseItems where guid=@Id", id); - // Delete the item - ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", idBlob); - }); - } + transaction.Commit(); } - private void ExecuteWithSingleParam(SqliteConnection db, string query, ReadOnlySpan value) + private void ExecuteWithSingleParam(SqliteConnection db, string query, Guid value) { using (var statement = PrepareStatement(db, query)) { - statement.TryBind("@Id", value.ToArray()); + statement.TryBind("@Id", value); - statement.MoveNext(); + statement.ExecuteNonQuery(); } } @@ -4894,7 +4862,7 @@ AND Type = @InternalPersonType)"); // First delete // TODO deleteAncestorsStatement.Parameters.Clear(); deleteAncestorsStatement.TryBind("@ItemId", itemId); - deleteAncestorsStatement.MoveNext(); + deleteAncestorsStatement.ExecuteNonQuery(); if (ancestorIds.Count == 0) { @@ -4929,7 +4897,7 @@ AND Type = @InternalPersonType)"); } // TODO statement.Parameters.Clear(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } } @@ -5238,75 +5206,74 @@ AND Type = @InternalPersonType)"); var result = new QueryResult<(BaseItem, ItemCounts)>(); using (new QueryTimeLogger(Logger, commandText)) using (var connection = GetConnection(true)) + using (var transaction = connection.BeginTransaction()) { - connection.RunInTransaction( - db => + if (!isReturningZeroItems) + { + using (var statement = PrepareStatement(connection, commandText)) { - if (!isReturningZeroItems) + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) { - using (var statement = PrepareStatement(db, commandText)) - { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - if (typeSubQuery is not null) - { - GetWhereClauses(typeSubQuery, null); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); - - var hasEpisodeAttributes = HasEpisodeAttributes(query); - var hasProgramAttributes = HasProgramAttributes(query); - var hasServiceName = HasServiceName(query); - var hasStartDate = HasStartDate(query); - var hasTrailerTypes = HasTrailerTypes(query); - var hasArtistFields = HasArtistFields(query); - var hasSeriesFields = HasSeriesFields(query); - - foreach (var row in statement.ExecuteQuery()) - { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item is not null) - { - var countStartColumn = columns.Count - 1; - - list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); - } - } - } + statement.TryBind("@UserId", query.User.InternalId); + } + + if (typeSubQuery is not null) + { + GetWhereClauses(typeSubQuery, null); } - if (query.EnableTotalRecordCount) + BindSimilarParams(query, statement); + BindSearchParams(query, statement); + GetWhereClauses(innerQuery, statement); + GetWhereClauses(outerQuery, statement); + + var hasEpisodeAttributes = HasEpisodeAttributes(query); + var hasProgramAttributes = HasProgramAttributes(query); + var hasServiceName = HasServiceName(query); + var hasStartDate = HasStartDate(query); + var hasTrailerTypes = HasTrailerTypes(query); + var hasArtistFields = HasArtistFields(query); + var hasSeriesFields = HasSeriesFields(query); + + foreach (var row in statement.ExecuteQuery()) { - using (var statement = PrepareStatement(db, countText)) + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item is not null) { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } - - if (typeSubQuery is not null) - { - GetWhereClauses(typeSubQuery, null); - } - - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); - - result.TotalRecordCount = statement.SelectScalarInt(); + var countStartColumn = columns.Count - 1; + + list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); } } - }); + } + } + + if (query.EnableTotalRecordCount) + { + using (var statement = PrepareStatement(connection, countText)) + { + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) + { + statement.TryBind("@UserId", query.User.InternalId); + } + + if (typeSubQuery is not null) + { + GetWhereClauses(typeSubQuery, null); + } + + BindSimilarParams(query, statement); + BindSearchParams(query, statement); + GetWhereClauses(innerQuery, statement); + GetWhereClauses(outerQuery, statement); + + result.TotalRecordCount = statement.SelectScalarInt(); + } + } + + transaction.Commit(); } if (result.TotalRecordCount == 0) @@ -5464,7 +5431,7 @@ AND Type = @InternalPersonType)"); } // TODO statement.Parameters.Clear(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += Limit; @@ -5483,20 +5450,17 @@ AND Type = @InternalPersonType)"); CheckDisposed(); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - // First delete chapters - using var command = db.CreateCommand(); - command.CommandText = "delete from People where ItemId=@ItemId"; - command.TryBind("@ItemId", itemId); - command.ExecuteNonQuery(); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + // First delete chapters + using var command = connection.CreateCommand(); + command.CommandText = "delete from People where ItemId=@ItemId"; + command.TryBind("@ItemId", itemId); + command.ExecuteNonQuery(); - InsertPeople(itemId, people, db); - }); - } + InsertPeople(itemId, people, connection); + + transaction.Commit(); } private void InsertPeople(Guid id, List people, SqliteConnection db) @@ -5540,7 +5504,7 @@ AND Type = @InternalPersonType)"); listIndex++; } - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += Limit; @@ -5636,19 +5600,16 @@ AND Type = @InternalPersonType)"); cancellationToken.ThrowIfCancellationRequested(); - using (var connection = GetConnection()) - { - connection.RunInTransaction( - db => - { - // Delete existing mediastreams - using var command = db.PrepareStatement("delete from mediastreams where ItemId=@ItemId"); - command.TryBind("@ItemId", id); - command.ExecuteNonQuery(); + using var connection = GetConnection(); + using var transaction = connection.BeginTransaction(); + // Delete existing mediastreams + using var command = connection.PrepareStatement("delete from mediastreams where ItemId=@ItemId"); + command.TryBind("@ItemId", id); + command.ExecuteNonQuery(); - InsertMediaStreams(id, streams, db); - }); - } + InsertMediaStreams(id, streams, connection); + + transaction.Commit(); } private void InsertMediaStreams(Guid id, IReadOnlyList streams, SqliteConnection db) @@ -5749,7 +5710,7 @@ AND Type = @InternalPersonType)"); } // TODO statement.Parameters.Clear(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } startIndex += Limit; @@ -6021,16 +5982,15 @@ AND Type = @InternalPersonType)"); cancellationToken.ThrowIfCancellationRequested(); using (var connection = GetConnection()) + using (var transaction = connection.BeginTransaction()) + using (var command = connection.PrepareStatement("delete from mediaattachments where ItemId=@ItemId")) { - connection.RunInTransaction( - db => - { - using var command = db.PrepareStatement("delete from mediaattachments where ItemId=@ItemId"); - command.TryBind("@ItemId", id); - command.ExecuteNonQuery(); + command.TryBind("@ItemId", id); + command.ExecuteNonQuery(); - InsertMediaAttachments(id, attachments, db, cancellationToken); - }); + InsertMediaAttachments(id, attachments, connection, cancellationToken); + + transaction.Commit(); } } @@ -6088,7 +6048,7 @@ AND Type = @InternalPersonType)"); } // TODO statement.Parameters.Clear(); - statement.MoveNext(); + statement.ExecuteNonQuery(); } insertText.Length = _mediaAttachmentInsertPrefix.Length; diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index bc3863a65..619a64487 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -44,43 +44,45 @@ namespace Emby.Server.Implementations.Data var userDataTableExists = TableExists(connection, "userdata"); var users = userDatasTableExists ? null : _userManager.Users; + using var transaction = connection.BeginTransaction(); + connection.ExecuteAll(string.Join(';', new[] + { + "create table if not exists UserDatas (key nvarchar not null, userId INT not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null, AudioStreamIndex INT, SubtitleStreamIndex INT)", + + "drop index if exists idx_userdata", + "drop index if exists idx_userdata1", + "drop index if exists idx_userdata2", + "drop index if exists userdataindex1", + "drop index if exists userdataindex", + "drop index if exists userdataindex3", + "drop index if exists userdataindex4", + "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)" + })); + + if (!userDataTableExists) + { + return; + } - connection.RunInTransaction( - db => - { - db.ExecuteAll(string.Join(';', new[] - { - "create table if not exists UserDatas (key nvarchar not null, userId INT not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null, AudioStreamIndex INT, SubtitleStreamIndex INT)", - - "drop index if exists idx_userdata", - "drop index if exists idx_userdata1", - "drop index if exists idx_userdata2", - "drop index if exists userdataindex1", - "drop index if exists userdataindex", - "drop index if exists userdataindex3", - "drop index if exists userdataindex4", - "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)" - })); - - if (userDataTableExists) - { - var existingColumnNames = GetColumnNames(db, "userdata"); - - AddColumn(db, "userdata", "InternalUserId", "int", existingColumnNames); - AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames); - AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); - - if (!userDatasTableExists) - { - ImportUserIds(db, users); - - db.ExecuteAll("INSERT INTO UserDatas (key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex) SELECT key, InternalUserId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex from userdata where InternalUserId not null"); - } - } - }); + var existingColumnNames = GetColumnNames(connection, "userdata"); + + AddColumn(connection, "userdata", "InternalUserId", "int", existingColumnNames); + AddColumn(connection, "userdata", "AudioStreamIndex", "int", existingColumnNames); + AddColumn(connection, "userdata", "SubtitleStreamIndex", "int", existingColumnNames); + + if (userDatasTableExists) + { + return; + } + + ImportUserIds(connection, users); + + connection.ExecuteAll("INSERT INTO UserDatas (key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex) SELECT key, InternalUserId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex from userdata where InternalUserId not null"); + + transaction.Commit(); } } @@ -99,7 +101,6 @@ namespace Emby.Server.Implementations.Data statement.TryBind("@UserId", user.Id); statement.TryBind("@InternalUserId", user.InternalId); - statement.Prepare(); statement.ExecuteNonQuery(); } @@ -168,12 +169,10 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); using (var connection = GetConnection()) + using (var transaction = connection.BeginTransaction()) { - connection.RunInTransaction( - db => - { - SaveUserData(db, internalUserId, key, userData); - }); + SaveUserData(connection, internalUserId, key, userData); + transaction.Commit(); } } @@ -225,7 +224,7 @@ namespace Emby.Server.Implementations.Data statement.TryBindNull("@SubtitleStreamIndex"); } - statement.MoveNext(); + statement.ExecuteNonQuery(); } } @@ -237,15 +236,14 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); using (var connection = GetConnection()) + using (var transaction = connection.BeginTransaction()) { - connection.RunInTransaction( - db => - { - foreach (var userItemData in userDataList) - { - SaveUserData(db, internalUserId, userItemData.Key, userItemData); - } - }); + foreach (var userItemData in userDataList) + { + SaveUserData(connection, internalUserId, userItemData.Key, userItemData); + } + + transaction.Commit(); } } -- cgit v1.2.3 From cf04b43fa4396a67705a2c276cc76d633a075c46 Mon Sep 17 00:00:00 2001 From: cvium Date: Mon, 21 Aug 2023 21:37:18 +0200 Subject: simplify extension methods --- .../Data/BaseSqliteRepository.cs | 1 + .../Data/SqliteExtensions.cs | 40 +--- .../Data/SqliteItemRepository.cs | 244 ++++++++++----------- .../Data/SqliteUserDataRepository.cs | 4 +- 4 files changed, 125 insertions(+), 164 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 2152edaf4..e257c9edc 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -134,6 +134,7 @@ namespace Emby.Server.Implementations.Data connection.Execute("PRAGMA temp_store=" + (int)TempStore); + connection.Open(); return connection; } diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 40cecbb6b..14f0f5830 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -53,14 +53,6 @@ namespace Emby.Server.Implementations.Data "yy-MM-dd" }; - private static void EnsureOpen(this SqliteConnection sqliteConnection) - { - if (sqliteConnection.State == ConnectionState.Closed) - { - sqliteConnection.Open(); - } - } - public static IEnumerable Query(this SqliteConnection sqliteConnection, string commandText) { if (sqliteConnection.State != ConnectionState.Open) @@ -81,29 +73,11 @@ namespace Emby.Server.Implementations.Data public static void Execute(this SqliteConnection sqliteConnection, string commandText) { - sqliteConnection.EnsureOpen(); - using var command = sqliteConnection.CreateCommand(); - command.CommandText = commandText; - command.ExecuteNonQuery(); - } - - public static void ExecuteAll(this SqliteConnection sqliteConnection, string commandText) - { - sqliteConnection.EnsureOpen(); - using var command = sqliteConnection.CreateCommand(); command.CommandText = commandText; command.ExecuteNonQuery(); } - public static void RunQueries(this SqliteConnection connection, string[] queries) - { - ArgumentNullException.ThrowIfNull(queries); - using var transaction = connection.BeginTransaction(); - connection.ExecuteAll(string.Join(';', queries)); - transaction.Commit(); - } - public static string ToDateTimeParamValue(this DateTime dateValue) { var kind = DateTimeKind.Utc; @@ -239,6 +213,7 @@ namespace Emby.Server.Implementations.Data } else { + // Blobs aren't always detected automatically if (isBlob) { statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob) { Value = value }); @@ -250,18 +225,6 @@ namespace Emby.Server.Implementations.Data } } - public static void TryBind(this SqliteCommand statement, string name, byte[] value) - { - if (statement.Parameters.Contains(name)) - { - statement.Parameters[name].Value = value; - } - else - { - statement.Parameters.Add(new SqliteParameter(name, SqliteType.Blob, value.Length) { Value = value }); - } - } - public static void TryBindNull(this SqliteCommand statement, string name) { statement.TryBind(name, DBNull.Value); @@ -286,7 +249,6 @@ namespace Emby.Server.Implementations.Data public static SqliteCommand PrepareStatement(this SqliteConnection sqliteConnection, string sql) { - sqliteConnection.EnsureOpen(); var command = sqliteConnection.CreateCommand(); command.CommandText = sql; return command; diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index ad7ade54e..d30d35b25 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -437,128 +437,126 @@ namespace Emby.Server.Implementations.Data }; using (var connection = GetConnection()) + using (var transaction = connection.BeginTransaction()) { - connection.RunQueries(queries); - - using (var transaction = connection.BeginTransaction()) - { - var existingColumnNames = GetColumnNames(connection, "AncestorIds"); - AddColumn(connection, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); - - existingColumnNames = GetColumnNames(connection, "TypedBaseItems"); - - AddColumn(connection, "TypedBaseItems", "Path", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Name", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "MediaType", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Overview", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Genres", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "SortName", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "LockedFields", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Studios", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Audio", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Tags", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "UnratedType", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "TopParentId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "CriticRating", "Float", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "CleanName", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Album", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "LUFS", "Float", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "SeasonName", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Tagline", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Images", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Artists", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ExternalId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "ShowId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Width", "INT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Height", "INT", existingColumnNames); - AddColumn(connection, "TypedBaseItems", "Size", "BIGINT", existingColumnNames); - - existingColumnNames = GetColumnNames(connection, "ItemValues"); - AddColumn(connection, "ItemValues", "CleanValue", "Text", existingColumnNames); - - existingColumnNames = GetColumnNames(connection, ChaptersTableName); - AddColumn(connection, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames); - - existingColumnNames = GetColumnNames(connection, "MediaStreams"); - AddColumn(connection, "MediaStreams", "IsAvc", "BIT", existingColumnNames); - AddColumn(connection, "MediaStreams", "TimeBase", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "Title", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "Comment", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "CodecTag", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "BitDepth", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "RefFrames", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); - - AddColumn(connection, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); - AddColumn(connection, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); - - AddColumn(connection, "MediaStreams", "DvVersionMajor", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "DvVersionMinor", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "DvProfile", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "DvLevel", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "RpuPresentFlag", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "ElPresentFlag", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "BlPresentFlag", "INT", existingColumnNames); - AddColumn(connection, "MediaStreams", "DvBlSignalCompatibilityId", "INT", existingColumnNames); - - AddColumn(connection, "MediaStreams", "IsHearingImpaired", "BIT", existingColumnNames); - - transaction.Commit(); - } - - connection.RunQueries(postQueries); + connection.Execute(string.Join(';', queries)); + + var existingColumnNames = GetColumnNames(connection, "AncestorIds"); + AddColumn(connection, "AncestorIds", "AncestorIdText", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "TypedBaseItems"); + + AddColumn(connection, "TypedBaseItems", "Path", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ChannelId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CustomRating", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Name", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "MediaType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Overview", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ParentId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Genres", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SortName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "LockedFields", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Studios", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Audio", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Tags", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "UnratedType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TopParentId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CriticRating", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "CleanName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Album", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "LUFS", "Float", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeasonName", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Tagline", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Images", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExtraType", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Artists", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ExternalId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "ShowId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "OwnerId", "Text", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Width", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Height", "INT", existingColumnNames); + AddColumn(connection, "TypedBaseItems", "Size", "BIGINT", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "ItemValues"); + AddColumn(connection, "ItemValues", "CleanValue", "Text", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, ChaptersTableName); + AddColumn(connection, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames); + + existingColumnNames = GetColumnNames(connection, "MediaStreams"); + AddColumn(connection, "MediaStreams", "IsAvc", "BIT", existingColumnNames); + AddColumn(connection, "MediaStreams", "TimeBase", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "Title", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "Comment", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "CodecTag", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "BitDepth", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "RefFrames", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "DvVersionMajor", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvVersionMinor", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvProfile", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvLevel", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "RpuPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "ElPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "BlPresentFlag", "INT", existingColumnNames); + AddColumn(connection, "MediaStreams", "DvBlSignalCompatibilityId", "INT", existingColumnNames); + + AddColumn(connection, "MediaStreams", "IsHearingImpaired", "BIT", existingColumnNames); + + connection.Execute(string.Join(';', postQueries)); + + transaction.Commit(); } } @@ -674,7 +672,7 @@ namespace Emby.Server.Implementations.Data if (TypeRequiresDeserialization(type)) { - saveItemStatement.TryBind("@data", JsonSerializer.SerializeToUtf8Bytes(item, type, _jsonOptions)); + saveItemStatement.TryBind("@data", JsonSerializer.SerializeToUtf8Bytes(item, type, _jsonOptions), true); } else { @@ -4656,7 +4654,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type """; using var connection = GetConnection(); using var transaction = connection.BeginTransaction(); - connection.ExecuteAll(Statements); + connection.Execute(Statements); transaction.Commit(); } diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 619a64487..45da8fd3f 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -45,7 +45,7 @@ namespace Emby.Server.Implementations.Data var users = userDatasTableExists ? null : _userManager.Users; using var transaction = connection.BeginTransaction(); - connection.ExecuteAll(string.Join(';', new[] + connection.Execute(string.Join(';', new[] { "create table if not exists UserDatas (key nvarchar not null, userId INT not null, rating float null, played bit not null, playCount int not null, isFavorite bit not null, playbackPositionTicks bigint not null, lastPlayedDate datetime null, AudioStreamIndex INT, SubtitleStreamIndex INT)", @@ -80,7 +80,7 @@ namespace Emby.Server.Implementations.Data ImportUserIds(connection, users); - connection.ExecuteAll("INSERT INTO UserDatas (key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex) SELECT key, InternalUserId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex from userdata where InternalUserId not null"); + connection.Execute("INSERT INTO UserDatas (key, userId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex) SELECT key, InternalUserId, rating, played, playCount, isFavorite, playbackPositionTicks, lastPlayedDate, AudioStreamIndex, SubtitleStreamIndex from userdata where InternalUserId not null"); transaction.Commit(); } -- cgit v1.2.3 From 0d3d9490e5997824cfd97c6776ca000be127deef Mon Sep 17 00:00:00 2001 From: cvium Date: Tue, 22 Aug 2023 07:27:21 +0200 Subject: remove unused deps --- Emby.Server.Implementations/Data/SqliteExtensions.cs | 1 - Emby.Server.Implementations/Data/SqliteItemRepository.cs | 1 - 2 files changed, 2 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 14f0f5830..f3b3da64f 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -4,7 +4,6 @@ using System; using System.Collections.Generic; using System.Data; -using System.Diagnostics; using System.Globalization; using Microsoft.Data.Sqlite; diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 96870a719..ca121f8a2 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -3,7 +3,6 @@ #pragma warning disable CS1591 using System; -using System.Buffers.Text; using System.Collections.Generic; using System.Diagnostics; using System.Globalization; -- cgit v1.2.3 From 05e40ecb933f1b8734e026dd3cefe9e05122b712 Mon Sep 17 00:00:00 2001 From: cvium Date: Tue, 22 Aug 2023 08:31:34 +0200 Subject: review comments --- Emby.Server.Implementations/Data/SqliteExtensions.cs | 1 + Jellyfin.Server/Migrations/Routines/RemoveDuplicateExtras.cs | 5 +++-- 2 files changed, 4 insertions(+), 2 deletions(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index f3b3da64f..30e334393 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -243,6 +243,7 @@ namespace Emby.Server.Implementations.Data public static int SelectScalarInt(this SqliteCommand command) { var result = command.ExecuteScalar(); + // Can't be null since the method is used to retrieve Count return Convert.ToInt32(result!, CultureInfo.InvariantCulture); } diff --git a/Jellyfin.Server/Migrations/Routines/RemoveDuplicateExtras.cs b/Jellyfin.Server/Migrations/Routines/RemoveDuplicateExtras.cs index 98017e3ef..6c34e1f5b 100644 --- a/Jellyfin.Server/Migrations/Routines/RemoveDuplicateExtras.cs +++ b/Jellyfin.Server/Migrations/Routines/RemoveDuplicateExtras.cs @@ -39,11 +39,11 @@ namespace Jellyfin.Server.Migrations.Routines var dataPath = _paths.DataPath; var dbPath = Path.Combine(dataPath, DbFilename); using (var connection = new SqliteConnection($"Filename={dbPath}")) + using (var transaction = connection.BeginTransaction()) { // Query the database for the ids of duplicate extras var queryResult = connection.Query("SELECT t1.Path FROM TypedBaseItems AS t1, TypedBaseItems AS t2 WHERE t1.Path=t2.Path AND t1.Type!=t2.Type AND t1.Type='MediaBrowser.Controller.Entities.Video'"); - // TODO does this LINQ execute before the reader is disposed? - var bads = string.Join(", ", queryResult.Select(x => x.GetString(0)).ToList()); + var bads = string.Join(", ", queryResult.Select(x => x.GetString(0))); // Do nothing if no duplicate extras were detected if (bads.Length == 0) @@ -75,6 +75,7 @@ namespace Jellyfin.Server.Migrations.Routines // Delete all duplicate extras _logger.LogInformation("Removing found duplicated extras for the following items: {DuplicateExtras}", bads); connection.Execute("DELETE FROM TypedBaseItems WHERE rowid IN (SELECT t1.rowid FROM TypedBaseItems AS t1, TypedBaseItems AS t2 WHERE t1.Path=t2.Path AND t1.Type!=t2.Type AND t1.Type='MediaBrowser.Controller.Entities.Video')"); + transaction.Commit(); } } } -- cgit v1.2.3 From cb48fe02c2cef7270bb071b90d565cf63744c32d Mon Sep 17 00:00:00 2001 From: cvium Date: Tue, 22 Aug 2023 20:12:16 +0200 Subject: remove nullable enable --- Emby.Server.Implementations/Data/SqliteExtensions.cs | 1 - 1 file changed, 1 deletion(-) (limited to 'Emby.Server.Implementations/Data/SqliteExtensions.cs') diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 30e334393..01b5fdaee 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -1,4 +1,3 @@ -#nullable enable #pragma warning disable CS1591 using System; -- cgit v1.2.3