From e01202030dcd16cd9c7c3327b4e411be7de02614 Mon Sep 17 00:00:00 2001 From: Luke Pulverenti Date: Tue, 21 May 2013 11:52:59 -0400 Subject: removed sql delayed writer in favor of prepared statements --- .../Sqlite/SQLiteItemRepository.cs | 171 +++++++++++++++++---- 1 file changed, 142 insertions(+), 29 deletions(-) (limited to 'MediaBrowser.Server.Implementations/Sqlite/SQLiteItemRepository.cs') diff --git a/MediaBrowser.Server.Implementations/Sqlite/SQLiteItemRepository.cs b/MediaBrowser.Server.Implementations/Sqlite/SQLiteItemRepository.cs index 62268c0c3..23ab10499 100644 --- a/MediaBrowser.Server.Implementations/Sqlite/SQLiteItemRepository.cs +++ b/MediaBrowser.Server.Implementations/Sqlite/SQLiteItemRepository.cs @@ -8,6 +8,7 @@ using MediaBrowser.Server.Implementations.Reflection; using System; using System.Collections.Generic; using System.Data; +using System.Data.SQLite; using System.IO; using System.Linq; using System.Threading; @@ -53,6 +54,19 @@ namespace MediaBrowser.Server.Implementations.Sqlite /// private readonly IApplicationPaths _appPaths; + /// + /// The _save item command + /// + private SQLiteCommand _saveItemCommand; + /// + /// The _delete children command + /// + private SQLiteCommand _deleteChildrenCommand; + /// + /// The _save children command + /// + private SQLiteCommand _saveChildrenCommand; + /// /// Initializes a new instance of the class. /// @@ -100,6 +114,8 @@ namespace MediaBrowser.Server.Implementations.Sqlite }; RunQueries(queries); + + PrepareStatements(); } //cascade delete triggers @@ -116,6 +132,39 @@ namespace MediaBrowser.Server.Implementations.Sqlite DELETE FROM children WHERE children.child = old.child; END"; + /// + /// The _write lock + /// + private readonly SemaphoreSlim _writeLock = new SemaphoreSlim(1,1); + + /// + /// Prepares the statements. + /// + private void PrepareStatements() + { + _saveItemCommand = new SQLiteCommand + { + CommandText = "replace into items (guid, obj_type, data) values (@1, @2, @3)" + }; + + _saveItemCommand.Parameters.Add(new SQLiteParameter("@1")); + _saveItemCommand.Parameters.Add(new SQLiteParameter("@2")); + _saveItemCommand.Parameters.Add(new SQLiteParameter("@3")); + + _deleteChildrenCommand = new SQLiteCommand + { + CommandText = "delete from children where guid = @guid" + }; + _deleteChildrenCommand.Parameters.Add(new SQLiteParameter("@guid")); + + _saveChildrenCommand = new SQLiteCommand + { + CommandText = "replace into children (guid, child) values (@guid, @child)" + }; + _saveChildrenCommand.Parameters.Add(new SQLiteParameter("@guid")); + _saveChildrenCommand.Parameters.Add(new SQLiteParameter("@child")); + } + /// /// Save a standard item in the repo /// @@ -123,7 +172,7 @@ namespace MediaBrowser.Server.Implementations.Sqlite /// The cancellation token. /// Task. /// item - public Task SaveItem(BaseItem item, CancellationToken cancellationToken) + public async Task SaveItem(BaseItem item, CancellationToken cancellationToken) { if (item == null) { @@ -137,19 +186,51 @@ namespace MediaBrowser.Server.Implementations.Sqlite cancellationToken.ThrowIfCancellationRequested(); - return Task.Run(() => + var serialized = _jsonSerializer.SerializeToBytes(item); + + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); + + SQLiteTransaction transaction = null; + + try { - var serialized = _jsonSerializer.SerializeToBytes(item); + transaction = Connection.BeginTransaction(); - cancellationToken.ThrowIfCancellationRequested(); + _saveItemCommand.Parameters[0].Value = item.Id; + _saveItemCommand.Parameters[1].Value = item.GetType().FullName; + _saveItemCommand.Parameters[2].Value = serialized; - var cmd = connection.CreateCommand(); - cmd.CommandText = "replace into items (guid, obj_type, data) values (@1, @2, @3)"; - cmd.AddParam("@1", item.Id); - cmd.AddParam("@2", item.GetType().FullName); - cmd.AddParam("@3", serialized); - QueueCommand(cmd); - }); + _saveItemCommand.Transaction = transaction; + + await _saveItemCommand.ExecuteNonQueryAsync(cancellationToken); + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); + } + } + catch (Exception e) + { + Logger.ErrorException("Failed to save item:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + + _writeLock.Release(); + } } /// @@ -157,6 +238,7 @@ namespace MediaBrowser.Server.Implementations.Sqlite /// /// The id. /// BaseItem. + /// id /// public BaseItem GetItem(Guid id) { @@ -189,6 +271,7 @@ namespace MediaBrowser.Server.Implementations.Sqlite /// /// The id. /// BaseItem. + /// id /// protected BaseItem RetrieveItemInternal(Guid id) { @@ -197,7 +280,7 @@ namespace MediaBrowser.Server.Implementations.Sqlite throw new ArgumentNullException("id"); } - using (var cmd = connection.CreateCommand()) + using (var cmd = Connection.CreateCommand()) { cmd.CommandText = "select obj_type,data from items where guid = @guid"; var guidParam = cmd.Parameters.Add("@guid", DbType.Guid); @@ -240,7 +323,7 @@ namespace MediaBrowser.Server.Implementations.Sqlite throw new ArgumentNullException(); } - using (var cmd = connection.CreateCommand()) + using (var cmd = Connection.CreateCommand()) { cmd.CommandText = "select obj_type,data from items where guid in (select child from children where guid = @guid)"; var guidParam = cmd.Parameters.Add("@guid", DbType.Guid); @@ -281,7 +364,7 @@ namespace MediaBrowser.Server.Implementations.Sqlite /// The cancellation token. /// Task. /// id - public Task SaveChildren(Guid id, IEnumerable children, CancellationToken cancellationToken) + public async Task SaveChildren(Guid id, IEnumerable children, CancellationToken cancellationToken) { if (id == Guid.Empty) { @@ -300,27 +383,57 @@ namespace MediaBrowser.Server.Implementations.Sqlite cancellationToken.ThrowIfCancellationRequested(); - return Task.Run(() => - { - var cmd = connection.CreateCommand(); + await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); + + SQLiteTransaction transaction = null; - cmd.CommandText = "delete from children where guid = @guid"; - cmd.AddParam("@guid", id); + try + { + transaction = Connection.BeginTransaction(); - QueueCommand(cmd); + // Delete exising children + _deleteChildrenCommand.Parameters[0].Value = id; + _deleteChildrenCommand.Transaction = transaction; + await _deleteChildrenCommand.ExecuteNonQueryAsync(cancellationToken); + // Save new children foreach (var child in children) { - var guid = child.Id; - cmd = connection.CreateCommand(); - cmd.AddParam("@guid", id); - cmd.CommandText = "replace into children (guid, child) values (@guid, @child)"; - var childParam = cmd.Parameters.Add("@child", DbType.Guid); - - childParam.Value = guid; - QueueCommand(cmd); + _saveChildrenCommand.Transaction = transaction; + + _saveChildrenCommand.Parameters[0].Value = id; + _saveChildrenCommand.Parameters[1].Value = child.Id; + + await _saveChildrenCommand.ExecuteNonQueryAsync(cancellationToken); + } + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) + { + transaction.Rollback(); } - }); + } + catch (Exception e) + { + Logger.ErrorException("Failed to save item:", e); + + if (transaction != null) + { + transaction.Rollback(); + } + } + finally + { + if (transaction != null) + { + transaction.Dispose(); + } + + _writeLock.Release(); + } } /// -- cgit v1.2.3