diff options
Diffstat (limited to 'MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs')
| -rw-r--r-- | MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs | 322 |
1 files changed, 168 insertions, 154 deletions
diff --git a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs index da0f584d5..6edacba53 100644 --- a/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs +++ b/MediaBrowser.Server.Implementations/Persistence/SqliteUserDataRepository.cs @@ -15,11 +15,18 @@ namespace MediaBrowser.Server.Implementations.Persistence { public class SqliteUserDataRepository : BaseSqliteRepository, IUserDataRepository { + private IDbConnection _connection; + public SqliteUserDataRepository(ILogManager logManager, IApplicationPaths appPaths, IDbConnector connector) : base(logManager, connector) { DbFilePath = Path.Combine(appPaths.DataPath, "userdata_v2.db"); } + protected override bool EnableConnectionPooling + { + get { return false; } + } + /// <summary> /// Gets the name of the repository /// </summary> @@ -36,23 +43,27 @@ namespace MediaBrowser.Server.Implementations.Persistence /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public async Task Initialize(IDbConnector dbConnector) + public async Task Initialize() { - using (var connection = await CreateConnection().ConfigureAwait(false)) - { - string[] queries = { + _connection = await CreateConnection(false).ConfigureAwait(false); + + string[] queries = { "create table if not exists userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)", "create index if not exists idx_userdata on userdata(key)", - "create unique index if not exists userdataindex on userdata (key, userId)" + "create unique index if not exists userdataindex on userdata (key, userId)", + + //pragmas + "pragma temp_store = memory", + + "pragma shrink_memory" }; - connection.RunQueries(queries, Logger); + _connection.RunQueries(queries, Logger); - connection.AddColumn(Logger, "userdata", "AudioStreamIndex", "int"); - connection.AddColumn(Logger, "userdata", "SubtitleStreamIndex", "int"); - } + _connection.AddColumn(Logger, "userdata", "AudioStreamIndex", "int"); + _connection.AddColumn(Logger, "userdata", "SubtitleStreamIndex", "int"); } /// <summary> @@ -114,63 +125,64 @@ namespace MediaBrowser.Server.Implementations.Persistence { cancellationToken.ThrowIfCancellationRequested(); - using (var connection = await CreateConnection().ConfigureAwait(false)) - { - IDbTransaction transaction = null; - - try - { - transaction = connection.BeginTransaction(); - - using (var cmd = connection.CreateCommand()) - { - cmd.CommandText = "replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)"; - - cmd.Parameters.Add(cmd, "@key", DbType.String).Value = key; - cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; - cmd.Parameters.Add(cmd, "@rating", DbType.Double).Value = userData.Rating; - cmd.Parameters.Add(cmd, "@played", DbType.Boolean).Value = userData.Played; - cmd.Parameters.Add(cmd, "@playCount", DbType.Int32).Value = userData.PlayCount; - cmd.Parameters.Add(cmd, "@isFavorite", DbType.Boolean).Value = userData.IsFavorite; - cmd.Parameters.Add(cmd, "@playbackPositionTicks", DbType.Int64).Value = userData.PlaybackPositionTicks; - cmd.Parameters.Add(cmd, "@lastPlayedDate", DbType.DateTime).Value = userData.LastPlayedDate; - cmd.Parameters.Add(cmd, "@AudioStreamIndex", DbType.Int32).Value = userData.AudioStreamIndex; - cmd.Parameters.Add(cmd, "@SubtitleStreamIndex", DbType.Int32).Value = userData.SubtitleStreamIndex; + await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - cmd.Transaction = transaction; + IDbTransaction transaction = null; - cmd.ExecuteNonQuery(); - } + try + { + transaction = _connection.BeginTransaction(); - transaction.Commit(); - } - catch (OperationCanceledException) + using (var cmd = _connection.CreateCommand()) { - if (transaction != null) - { - transaction.Rollback(); - } + cmd.CommandText = "replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)"; - throw; + cmd.Parameters.Add(cmd, "@key", DbType.String).Value = key; + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@rating", DbType.Double).Value = userData.Rating; + cmd.Parameters.Add(cmd, "@played", DbType.Boolean).Value = userData.Played; + cmd.Parameters.Add(cmd, "@playCount", DbType.Int32).Value = userData.PlayCount; + cmd.Parameters.Add(cmd, "@isFavorite", DbType.Boolean).Value = userData.IsFavorite; + cmd.Parameters.Add(cmd, "@playbackPositionTicks", DbType.Int64).Value = userData.PlaybackPositionTicks; + cmd.Parameters.Add(cmd, "@lastPlayedDate", DbType.DateTime).Value = userData.LastPlayedDate; + cmd.Parameters.Add(cmd, "@AudioStreamIndex", DbType.Int32).Value = userData.AudioStreamIndex; + cmd.Parameters.Add(cmd, "@SubtitleStreamIndex", DbType.Int32).Value = userData.SubtitleStreamIndex; + + cmd.Transaction = transaction; + + cmd.ExecuteNonQuery(); } - catch (Exception e) + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) { - Logger.ErrorException("Failed to save user data:", e); + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); - } + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save user data:", e); - throw; + if (transaction != null) + { + transaction.Rollback(); } - finally + + throw; + } + finally + { + if (transaction != null) { - if (transaction != null) - { - transaction.Dispose(); - } + transaction.Dispose(); } + + WriteLock.Release(); } } @@ -185,68 +197,69 @@ namespace MediaBrowser.Server.Implementations.Persistence { cancellationToken.ThrowIfCancellationRequested(); - using (var connection = await CreateConnection().ConfigureAwait(false)) - { - IDbTransaction transaction = null; + await WriteLock.WaitAsync(cancellationToken).ConfigureAwait(false); - try - { - transaction = connection.BeginTransaction(); + IDbTransaction transaction = null; - foreach (var userItemData in userData) - { - using (var cmd = connection.CreateCommand()) - { - cmd.CommandText = "replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)"; - - cmd.Parameters.Add(cmd, "@key", DbType.String).Value = userItemData.Key; - cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; - cmd.Parameters.Add(cmd, "@rating", DbType.Double).Value = userItemData.Rating; - cmd.Parameters.Add(cmd, "@played", DbType.Boolean).Value = userItemData.Played; - cmd.Parameters.Add(cmd, "@playCount", DbType.Int32).Value = userItemData.PlayCount; - cmd.Parameters.Add(cmd, "@isFavorite", DbType.Boolean).Value = userItemData.IsFavorite; - cmd.Parameters.Add(cmd, "@playbackPositionTicks", DbType.Int64).Value = userItemData.PlaybackPositionTicks; - cmd.Parameters.Add(cmd, "@lastPlayedDate", DbType.DateTime).Value = userItemData.LastPlayedDate; - cmd.Parameters.Add(cmd, "@AudioStreamIndex", DbType.Int32).Value = userItemData.AudioStreamIndex; - cmd.Parameters.Add(cmd, "@SubtitleStreamIndex", DbType.Int32).Value = userItemData.SubtitleStreamIndex; - - cmd.Transaction = transaction; - - cmd.ExecuteNonQuery(); - } - - cancellationToken.ThrowIfCancellationRequested(); - } + try + { + transaction = _connection.BeginTransaction(); - transaction.Commit(); - } - catch (OperationCanceledException) + foreach (var userItemData in userData) { - if (transaction != null) + using (var cmd = _connection.CreateCommand()) { - transaction.Rollback(); + cmd.CommandText = "replace into userdata (key, userId, rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex) values (@key, @userId, @rating,@played,@playCount,@isFavorite,@playbackPositionTicks,@lastPlayedDate,@AudioStreamIndex,@SubtitleStreamIndex)"; + + cmd.Parameters.Add(cmd, "@key", DbType.String).Value = userItemData.Key; + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@rating", DbType.Double).Value = userItemData.Rating; + cmd.Parameters.Add(cmd, "@played", DbType.Boolean).Value = userItemData.Played; + cmd.Parameters.Add(cmd, "@playCount", DbType.Int32).Value = userItemData.PlayCount; + cmd.Parameters.Add(cmd, "@isFavorite", DbType.Boolean).Value = userItemData.IsFavorite; + cmd.Parameters.Add(cmd, "@playbackPositionTicks", DbType.Int64).Value = userItemData.PlaybackPositionTicks; + cmd.Parameters.Add(cmd, "@lastPlayedDate", DbType.DateTime).Value = userItemData.LastPlayedDate; + cmd.Parameters.Add(cmd, "@AudioStreamIndex", DbType.Int32).Value = userItemData.AudioStreamIndex; + cmd.Parameters.Add(cmd, "@SubtitleStreamIndex", DbType.Int32).Value = userItemData.SubtitleStreamIndex; + + cmd.Transaction = transaction; + + cmd.ExecuteNonQuery(); } - throw; + cancellationToken.ThrowIfCancellationRequested(); } - catch (Exception e) + + transaction.Commit(); + } + catch (OperationCanceledException) + { + if (transaction != null) { - Logger.ErrorException("Failed to save user data:", e); + transaction.Rollback(); + } - if (transaction != null) - { - transaction.Rollback(); - } + throw; + } + catch (Exception e) + { + Logger.ErrorException("Failed to save user data:", e); - throw; + if (transaction != null) + { + transaction.Rollback(); } - finally + + throw; + } + finally + { + if (transaction != null) { - if (transaction != null) - { - transaction.Dispose(); - } + transaction.Dispose(); } + + WriteLock.Release(); } } @@ -272,25 +285,22 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("key"); } - using (var connection = CreateConnection(true).Result) + using (var cmd = _connection.CreateCommand()) { - using (var cmd = connection.CreateCommand()) - { - cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key = @key and userId=@userId"; + cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where key = @key and userId=@userId"; - cmd.Parameters.Add(cmd, "@key", DbType.String).Value = key; - cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@key", DbType.String).Value = key; + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + { + if (reader.Read()) { - if (reader.Read()) - { - return ReadRow(reader); - } + return ReadRow(reader); } - - return null; } + + return null; } } @@ -305,41 +315,38 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("keys"); } - using (var connection = CreateConnection(true).Result) + using (var cmd = _connection.CreateCommand()) { - using (var cmd = connection.CreateCommand()) + var index = 0; + var excludeIds = new List<string>(); + var builder = new StringBuilder(); + foreach (var key in keys) { - var index = 0; - var excludeIds = new List<string>(); - var builder = new StringBuilder(); - foreach (var key in keys) - { - var paramName = "@Key" + index; - excludeIds.Add("Key =" + paramName); - cmd.Parameters.Add(cmd, paramName, DbType.String).Value = key; - builder.Append(" WHEN Key=" + paramName + " THEN " + index); - index++; - } + var paramName = "@Key" + index; + excludeIds.Add("Key =" + paramName); + cmd.Parameters.Add(cmd, paramName, DbType.String).Value = key; + builder.Append(" WHEN Key=" + paramName + " THEN " + index); + index++; + } - var keyText = string.Join(" OR ", excludeIds.ToArray()); + var keyText = string.Join(" OR ", excludeIds.ToArray()); - cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@userId AND (" + keyText + ") "; + cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@userId AND (" + keyText + ") "; - cmd.CommandText += " ORDER BY (Case " + builder + " Else " + keys.Count.ToString(CultureInfo.InvariantCulture) + " End )"; - cmd.CommandText += " LIMIT 1"; + cmd.CommandText += " ORDER BY (Case " + builder + " Else " + keys.Count.ToString(CultureInfo.InvariantCulture) + " End )"; + cmd.CommandText += " LIMIT 1"; - cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow)) + { + if (reader.Read()) { - if (reader.Read()) - { - return ReadRow(reader); - } + return ReadRow(reader); } - - return null; } + + return null; } } @@ -355,27 +362,20 @@ namespace MediaBrowser.Server.Implementations.Persistence throw new ArgumentNullException("userId"); } - var list = new List<UserItemData>(); - - using (var connection = CreateConnection(true).Result) + using (var cmd = _connection.CreateCommand()) { - using (var cmd = connection.CreateCommand()) - { - cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@userId"; + cmd.CommandText = "select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from userdata where userId=@userId"; - cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; + cmd.Parameters.Add(cmd, "@userId", DbType.Guid).Value = userId; - using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult)) + { + while (reader.Read()) { - while (reader.Read()) - { - list.Add(ReadRow(reader)); - } + yield return ReadRow(reader); } } } - - return list; } /// <summary> @@ -416,5 +416,19 @@ namespace MediaBrowser.Server.Implementations.Persistence return userData; } + + protected override void CloseConnection() + { + if (_connection != null) + { + if (_connection.IsOpen()) + { + _connection.Close(); + } + + _connection.Dispose(); + _connection = null; + } + } } }
\ No newline at end of file |