diff options
9 files changed, 253 insertions, 125 deletions
diff --git a/Emby.Server.Core/Data/SqliteItemRepository.cs b/Emby.Server.Core/Data/SqliteItemRepository.cs index 5b8f18088..97d703602 100644 --- a/Emby.Server.Core/Data/SqliteItemRepository.cs +++ b/Emby.Server.Core/Data/SqliteItemRepository.cs @@ -679,7 +679,7 @@ namespace Emby.Server.Core.Data throw new ArgumentNullException("item"); } - return SaveItems(new[] { item }, cancellationToken); + return SaveItems(new List<BaseItem> { item }, cancellationToken); } /// <summary> @@ -693,7 +693,7 @@ namespace Emby.Server.Core.Data /// or /// cancellationToken /// </exception> - public async Task SaveItems(IEnumerable<BaseItem> items, CancellationToken cancellationToken) + public async Task SaveItems(List<BaseItem> items, CancellationToken cancellationToken) { if (items == null) { diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index 8f64f04db..aaa0b2f5d 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -57,18 +57,21 @@ namespace Emby.Server.Implementations.Activity { connection.RunInTransaction(db => { - var commandText = "replace into ActivityLogEntries (Id, Name, Overview, ShortOverview, Type, ItemId, UserId, DateCreated, LogSeverity) values (?, ?, ?, ?, ?, ?, ?, ?, ?)"; - - db.Execute(commandText, - entry.Id.ToGuidParamValue(), - entry.Name, - entry.Overview, - entry.ShortOverview, - entry.Type, - entry.ItemId, - entry.UserId, - entry.Date.ToDateTimeParamValue(), - entry.Severity.ToString()); + using (var statement = db.PrepareStatement("replace into ActivityLogEntries (Id, Name, Overview, ShortOverview, Type, ItemId, UserId, DateCreated, LogSeverity) values (@Id, @Name, @Overview, @ShortOverview, @Type, @ItemId, @UserId, @DateCreated, @LogSeverity)")) + { + statement.BindParameters.TryBind("@Id", entry.Id.ToGuidParamValue()); + statement.BindParameters.TryBind("@Name", entry.Name); + + statement.BindParameters.TryBind("@Overview", entry.Overview); + statement.BindParameters.TryBind("@ShortOverview", entry.ShortOverview); + statement.BindParameters.TryBind("@Type", entry.Type); + statement.BindParameters.TryBind("@ItemId", entry.ItemId); + statement.BindParameters.TryBind("@UserId", entry.UserId); + statement.BindParameters.TryBind("@DateCreated", entry.Date.ToDateTimeParamValue()); + statement.BindParameters.TryBind("@LogSeverity", entry.Severity.ToString()); + + statement.MoveNext(); + } }); } } diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index 1fbf9b0a9..1c592048e 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -100,14 +100,17 @@ namespace Emby.Server.Implementations.Data private void SaveDisplayPreferences(DisplayPreferences displayPreferences, Guid userId, string client, IDatabaseConnection connection) { - var commandText = "replace into userdisplaypreferences (id, userid, client, data) values (?, ?, ?, ?)"; - var serialized = _jsonSerializer.SerializeToBytes(displayPreferences, _memoryStreamProvider); - - connection.Execute(commandText, - displayPreferences.Id.ToGuidParamValue(), - userId.ToGuidParamValue(), - client, - serialized); + using (var statement = connection.PrepareStatement("replace into userdisplaypreferences (id, userid, client, data) values (@id, @userid, @client, @data)")) + { + var serialized = _jsonSerializer.SerializeToBytes(displayPreferences, _memoryStreamProvider); + + statement.BindParameters.TryBind("@id", displayPreferences.Id.ToGuidParamValue()); + statement.BindParameters.TryBind("@userId", userId.ToGuidParamValue()); + statement.BindParameters.TryBind("@client", client); + statement.BindParameters.TryBind("@data", serialized); + + statement.MoveNext(); + } } /// <summary> @@ -163,16 +166,16 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection(true)) { - var commandText = "select data from userdisplaypreferences where id = ? and userId=? and client=?"; - - var paramList = new List<object>(); - paramList.Add(guidId.ToGuidParamValue()); - paramList.Add(userId.ToGuidParamValue()); - paramList.Add(client); - - foreach (var row in connection.Query(commandText, paramList.ToArray())) + using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) { - return Get(row); + statement.BindParameters.TryBind("@id", guidId.ToGuidParamValue()); + statement.BindParameters.TryBind("@userId", userId.ToGuidParamValue()); + statement.BindParameters.TryBind("@client", client); + + foreach (var row in statement.ExecuteQuery()) + { + return Get(row); + } } return new DisplayPreferences @@ -197,14 +200,14 @@ namespace Emby.Server.Implementations.Data { using (var connection = CreateConnection(true)) { - var commandText = "select data from userdisplaypreferences where userId=?"; - - var paramList = new List<object>(); - paramList.Add(userId.ToGuidParamValue()); - - foreach (var row in connection.Query(commandText, paramList.ToArray())) + using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) { - list.Add(Get(row)); + statement.BindParameters.TryBind("@userId", userId.ToGuidParamValue()); + + foreach (var row in statement.ExecuteQuery()) + { + list.Add(Get(row)); + } } } } diff --git a/Emby.Server.Implementations/Data/SqliteExtensions.cs b/Emby.Server.Implementations/Data/SqliteExtensions.cs index 014211924..1cc8a8a93 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -168,14 +168,54 @@ namespace Emby.Server.Implementations.Data return result[index].ToFloat(); } - public static DateTime GetDateTime(this IReadOnlyList<IResultSetValue> result, int index) + public static Guid GetGuid(this IReadOnlyList<IResultSetValue> result, int index) { - return result[index].ReadDateTime(); + return result[index].ReadGuid(); } - public static Guid GetGuid(this IReadOnlyList<IResultSetValue> result, int index) + public static void TryBind(this IReadOnlyDictionary<string, IBindParameter> bindParameters, string name, string value) { - return result[index].ReadGuid(); + IBindParameter bindParam; + if (bindParameters.TryGetValue(name, out bindParam)) + { + bindParam.Bind(value); + } + } + + public static void TryBind(this IReadOnlyDictionary<string, IBindParameter> bindParameters, string name, bool value) + { + IBindParameter bindParam; + if (bindParameters.TryGetValue(name, out bindParam)) + { + bindParam.Bind(value); + } + } + + public static void TryBind(this IReadOnlyDictionary<string, IBindParameter> bindParameters, string name, byte[] value) + { + IBindParameter bindParam; + if (bindParameters.TryGetValue(name, out bindParam)) + { + bindParam.Bind(value); + } + } + + public static void TryBindNull(this IReadOnlyDictionary<string, IBindParameter> bindParameters, string name) + { + IBindParameter bindParam; + if (bindParameters.TryGetValue(name, out bindParam)) + { + bindParam.BindNull(); + } + } + + public static IEnumerable<IReadOnlyList<IResultSetValue>> ExecuteQuery( + this IStatement This) + { + while (This.MoveNext()) + { + yield return This.Current; + } } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index f0e38f8c0..ee496b669 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -89,11 +89,12 @@ namespace Emby.Server.Implementations.Data { connection.RunInTransaction(db => { - var commandText = "replace into users (guid, data) values (?, ?)"; - - db.Execute(commandText, - user.Id.ToGuidParamValue(), - serialized); + using (var statement = db.PrepareStatement("replace into users (guid, data) values (@guid, @data)")) + { + statement.BindParameters.TryBind("@guid", user.Id.ToGuidParamValue()); + statement.BindParameters.TryBind("@data", serialized); + statement.MoveNext(); + } }); } } @@ -151,10 +152,11 @@ namespace Emby.Server.Implementations.Data { connection.RunInTransaction(db => { - var commandText = "delete from users where guid=?"; - - db.Execute(commandText, - user.Id.ToGuidParamValue()); + using (var statement = db.PrepareStatement("delete from users where guid=@id")) + { + statement.BindParameters.TryBind("@id", user.Id.ToGuidParamValue()); + statement.MoveNext(); + } }); } } diff --git a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs index 2f3bee788..15322dcd3 100644 --- a/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs +++ b/Emby.Server.Implementations/Notifications/SqliteNotificationsRepository.cs @@ -107,17 +107,23 @@ namespace Emby.Server.Implementations.Notifications { using (var connection = CreateConnection(true)) { - foreach (var row in connection.Query("select Level from Notifications where UserId=? and IsRead=?", userId.ToGuidParamValue(), false)) + using (var statement = connection.PrepareStatement("select Level from Notifications where UserId=@UserId and IsRead=@IsRead")) { - var levels = new List<NotificationLevel>(); + statement.BindParameters.TryBind("@IsRead", false); + statement.BindParameters.TryBind("@UserId", userId.ToGuidParamValue()); - levels.Add(GetLevel(row, 0)); + foreach (var row in statement.ExecuteQuery()) + { + var levels = new List<NotificationLevel>(); - result.UnreadCount = levels.Count; + levels.Add(GetLevel(row, 0)); - if (levels.Count > 0) - { - result.MaxUnreadNotificationLevel = levels.Max(); + result.UnreadCount = levels.Count; + + if (levels.Count > 0) + { + result.MaxUnreadNotificationLevel = levels.Max(); + } } } @@ -220,17 +226,21 @@ namespace Emby.Server.Implementations.Notifications { connection.RunInTransaction(conn => { - conn.Execute("replace into Notifications (Id, UserId, Date, Name, Description, Url, Level, IsRead, Category, RelatedId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", - notification.Id.ToGuidParamValue(), - notification.UserId.ToGuidParamValue(), - notification.Date.ToDateTimeParamValue(), - notification.Name, - notification.Description, - notification.Url, - notification.Level.ToString(), - notification.IsRead, - string.Empty, - string.Empty); + using (var statement = conn.PrepareStatement("replace into Notifications (Id, UserId, Date, Name, Description, Url, Level, IsRead, Category, RelatedId) values (@Id, @UserId, @Date, @Name, @Description, @Url, @Level, @IsRead, @Category, @RelatedId)")) + { + statement.BindParameters.TryBind("@Id", notification.Id.ToGuidParamValue()); + statement.BindParameters.TryBind("@UserId", notification.UserId.ToGuidParamValue()); + statement.BindParameters.TryBind("@Date", notification.Date.ToDateTimeParamValue()); + statement.BindParameters.TryBind("@Name", notification.Name); + statement.BindParameters.TryBind("@Description", notification.Description); + statement.BindParameters.TryBind("@Url", notification.Url); + statement.BindParameters.TryBind("@Level", notification.Level.ToString()); + statement.BindParameters.TryBind("@IsRead", notification.IsRead); + statement.BindParameters.TryBind("@Category", string.Empty); + statement.BindParameters.TryBind("@RelatedId", string.Empty); + + statement.MoveNext(); + } }); } } @@ -279,7 +289,13 @@ namespace Emby.Server.Implementations.Notifications { connection.RunInTransaction(conn => { - conn.Execute("update Notifications set IsRead=? where UserId=?", isRead, userId.ToGuidParamValue()); + using (var statement = conn.PrepareStatement("update Notifications set IsRead=@IsRead where UserId=@UserId")) + { + statement.BindParameters.TryBind("@IsRead", isRead); + statement.BindParameters.TryBind("@UserId", userId.ToGuidParamValue()); + + statement.MoveNext(); + } }); } } @@ -295,12 +311,21 @@ namespace Emby.Server.Implementations.Notifications { connection.RunInTransaction(conn => { - var userIdParam = userId.ToGuidParamValue(); - - foreach (var id in notificationIdList) + using (var statement = conn.PrepareStatement("update Notifications set IsRead=@IsRead where UserId=@UserId and Id=@Id")) { - conn.Execute("update Notifications set IsRead=? where UserId=? and Id=?", isRead, userIdParam, id); + statement.BindParameters.TryBind("@IsRead", isRead); + statement.BindParameters.TryBind("@UserId", userId.ToGuidParamValue()); + + foreach (var id in notificationIdList) + { + statement.Reset(); + + statement.BindParameters.TryBind("@Id", id.ToGuidParamValue()); + + statement.MoveNext(); + } } + }); } } diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index f6163b80a..160e0f5d2 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -69,19 +69,30 @@ namespace Emby.Server.Implementations.Security { connection.RunInTransaction(db => { - var commandText = "replace into AccessTokens (Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; - - db.Execute(commandText, - info.Id.ToGuidParamValue(), - info.AccessToken, - info.DeviceId, - info.AppName, - info.AppVersion, - info.DeviceName, - info.UserId, - info.IsActive, - info.DateCreated.ToDateTimeParamValue(), - info.DateRevoked.HasValue ? info.DateRevoked.Value.ToDateTimeParamValue() : null); + using (var statement = db.PrepareStatement("replace into AccessTokens (Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked) values (@Id, @AccessToken, @DeviceId, @AppName, @AppVersion, @DeviceName, @UserId, @IsActive, @DateCreated, @DateRevoked)")) + { + statement.BindParameters.TryBind("@Id", info.Id.ToGuidParamValue()); + statement.BindParameters.TryBind("@AccessToken", info.AccessToken); + + statement.BindParameters.TryBind("@DeviceId", info.DeviceId); + statement.BindParameters.TryBind("@AppName", info.AppName); + statement.BindParameters.TryBind("@AppVersion", info.AppVersion); + statement.BindParameters.TryBind("@DeviceName", info.DeviceName); + statement.BindParameters.TryBind("@UserId", info.UserId); + statement.BindParameters.TryBind("@IsActive", info.IsActive); + statement.BindParameters.TryBind("@DateCreated", info.DateCreated.ToDateTimeParamValue()); + + if (info.DateRevoked.HasValue) + { + statement.BindParameters.TryBind("@DateRevoked", info.DateRevoked.Value.ToDateTimeParamValue()); + } + else + { + statement.BindParameters.TryBindNull("@DateRevoked"); + } + + statement.MoveNext(); + } }); } } @@ -89,6 +100,29 @@ namespace Emby.Server.Implementations.Security private const string BaseSelectText = "select Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked from AccessTokens"; + private void BindAuthenticationQueryParams(AuthenticationInfoQuery query, IStatement statement) + { + if (!string.IsNullOrWhiteSpace(query.AccessToken)) + { + statement.BindParameters.TryBind("@AccessToken", query.AccessToken); + } + + if (!string.IsNullOrWhiteSpace(query.UserId)) + { + statement.BindParameters.TryBind("@UserId", query.UserId); + } + + if (!string.IsNullOrWhiteSpace(query.DeviceId)) + { + statement.BindParameters.TryBind("@DeviceId", query.DeviceId); + } + + if (query.IsActive.HasValue) + { + statement.BindParameters.TryBind("@IsActive", query.IsActive.Value); + } + } + public QueryResult<AuthenticationInfo> Get(AuthenticationInfoQuery query) { if (query == null) @@ -99,7 +133,6 @@ namespace Emby.Server.Implementations.Security using (var connection = CreateConnection(true)) { var commandText = BaseSelectText; - var paramList = new List<object>(); var whereClauses = new List<string>(); @@ -107,26 +140,22 @@ namespace Emby.Server.Implementations.Security if (!string.IsNullOrWhiteSpace(query.AccessToken)) { - whereClauses.Add("AccessToken=?"); - paramList.Add(query.AccessToken); + whereClauses.Add("AccessToken=@AccessToken"); } if (!string.IsNullOrWhiteSpace(query.UserId)) { - whereClauses.Add("UserId=?"); - paramList.Add(query.UserId); + whereClauses.Add("UserId=@UserId"); } if (!string.IsNullOrWhiteSpace(query.DeviceId)) { - whereClauses.Add("DeviceId=?"); - paramList.Add(query.DeviceId); + whereClauses.Add("DeviceId=@DeviceId"); } if (query.IsActive.HasValue) { - whereClauses.Add("IsActive=?"); - paramList.Add(query.IsActive.Value); + whereClauses.Add("IsActive=@IsActive"); } if (query.HasUser.HasValue) @@ -171,20 +200,30 @@ namespace Emby.Server.Implementations.Security var list = new List<AuthenticationInfo>(); - foreach (var row in connection.Query(commandText, paramList.ToArray())) + using (var statement = connection.PrepareStatement(commandText)) { - list.Add(Get(row)); - } + BindAuthenticationQueryParams(query, statement); - var count = connection.Query("select count (Id) from AccessTokens" + whereTextWithoutPaging, paramList.ToArray()) - .SelectScalarInt() - .First(); + foreach (var row in statement.ExecuteQuery()) + { + list.Add(Get(row)); + } - return new QueryResult<AuthenticationInfo>() - { - Items = list.ToArray(), - TotalRecordCount = count - }; + using (var totalCountStatement = connection.PrepareStatement("select count (Id) from AccessTokens" + whereTextWithoutPaging)) + { + BindAuthenticationQueryParams(query, totalCountStatement); + + var count = totalCountStatement.ExecuteQuery() + .SelectScalarInt() + .First(); + + return new QueryResult<AuthenticationInfo>() + { + Items = list.ToArray(), + TotalRecordCount = count + }; + } + } } } @@ -199,16 +238,18 @@ namespace Emby.Server.Implementations.Security { using (var connection = CreateConnection(true)) { - var commandText = BaseSelectText + " where Id=?"; - var paramList = new List<object>(); - - paramList.Add(id.ToGuidParamValue()); + var commandText = BaseSelectText + " where Id=@Id"; - foreach (var row in connection.Query(commandText, paramList.ToArray())) + using (var statement = connection.PrepareStatement(commandText)) { - return Get(row); + statement.BindParameters["@Id"].Bind(id.ToGuidParamValue()); + + foreach (var row in statement.ExecuteQuery()) + { + return Get(row); + } + return null; } - return null; } } } diff --git a/Emby.Server.Implementations/Sync/SyncRepository.cs b/Emby.Server.Implementations/Sync/SyncRepository.cs index 2877a8ffd..bbd23831c 100644 --- a/Emby.Server.Implementations/Sync/SyncRepository.cs +++ b/Emby.Server.Implementations/Sync/SyncRepository.cs @@ -492,14 +492,11 @@ namespace Emby.Server.Implementations.Sync using (var connection = CreateConnection(true)) { var commandText = "select ItemId,Status,Progress from SyncJobItems"; - var whereClauses = new List<string>(); - var paramList = new List<object>(); if (!string.IsNullOrWhiteSpace(query.TargetId)) { - whereClauses.Add("TargetId=?"); - paramList.Add(query.TargetId); + whereClauses.Add("TargetId=@TargetId"); } if (query.Statuses.Length > 0) @@ -514,22 +511,39 @@ namespace Emby.Server.Implementations.Sync commandText += " where " + string.Join(" AND ", whereClauses.ToArray()); } - foreach (var row in connection.Query(commandText, paramList.ToArray())) + using (var statement = connection.PrepareStatement(commandText)) { - AddStatusResult(row, result, false); + if (!string.IsNullOrWhiteSpace(query.TargetId)) + { + statement.BindParameters.TryBind("@TargetId", query.TargetId); + } + + foreach (var row in statement.ExecuteQuery()) + { + AddStatusResult(row, result, false); + } + LogQueryTime("GetSyncedItemProgresses", commandText, now); } - LogQueryTime("GetSyncedItemProgresses", commandText, now); commandText = commandText .Replace("select ItemId,Status,Progress from SyncJobItems", "select ItemIds,Status,Progress from SyncJobs") .Replace("'Synced'", "'Completed','CompletedWithError'"); now = DateTime.UtcNow; - foreach (var row in connection.Query(commandText, paramList.ToArray())) + + using (var statement = connection.PrepareStatement(commandText)) { - AddStatusResult(row, result, true); + if (!string.IsNullOrWhiteSpace(query.TargetId)) + { + statement.BindParameters.TryBind("@TargetId", query.TargetId); + } + + foreach (var row in statement.ExecuteQuery()) + { + AddStatusResult(row, result, true); + } + LogQueryTime("GetSyncedItemProgresses", commandText, now); } - LogQueryTime("GetSyncedItemProgresses", commandText, now); } } diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs index 87937869d..0de048865 100644 --- a/MediaBrowser.Controller/Persistence/IItemRepository.cs +++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs @@ -51,7 +51,7 @@ namespace MediaBrowser.Controller.Persistence /// <param name="items">The items.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> - Task SaveItems(IEnumerable<BaseItem> items, CancellationToken cancellationToken); + Task SaveItems(List<BaseItem> items, CancellationToken cancellationToken); /// <summary> /// Retrieves the item. |