diff options
| author | Bond-009 <bond.009@outlook.com> | 2019-02-20 14:26:49 +0100 |
|---|---|---|
| committer | Bond_009 <bond.009@outlook.com> | 2019-06-28 12:12:54 +0200 |
| commit | cec22ad10daf7abef2f27f846e4022d5a35faccf (patch) | |
| tree | 62c70f5bbf3a7dc2d997ccfc185dc77b89738a0d | |
| parent | b3b08fecb29f22c9d8550b49bd2bca18c339ef7b (diff) | |
Simplify db code
9 files changed, 589 insertions, 927 deletions
diff --git a/Emby.Server.Implementations/Activity/ActivityRepository.cs b/Emby.Server.Implementations/Activity/ActivityRepository.cs index aeed8b6f1..495d96977 100644 --- a/Emby.Server.Implementations/Activity/ActivityRepository.cs +++ b/Emby.Server.Implementations/Activity/ActivityRepository.cs @@ -57,7 +57,7 @@ namespace Emby.Server.Implementations.Activity } } - private void TryMigrate(ManagedConnection connection) + private void TryMigrate(SQLiteDatabaseConnection connection) { try { @@ -85,7 +85,6 @@ namespace Emby.Server.Implementations.Activity throw new ArgumentNullException(nameof(entry)); } - using (WriteLock.Write()) using (var connection = CreateConnection()) { connection.RunInTransaction(db => @@ -124,7 +123,6 @@ namespace Emby.Server.Implementations.Activity throw new ArgumentNullException(nameof(entry)); } - using (WriteLock.Write()) using (var connection = CreateConnection()) { connection.RunInTransaction(db => @@ -159,7 +157,6 @@ namespace Emby.Server.Implementations.Activity public QueryResult<ActivityLogEntry> GetActivityLogEntries(DateTime? minDate, bool? hasUserId, int? startIndex, int? limit) { - using (WriteLock.Read()) using (var connection = CreateConnection(true)) { var commandText = BaseActivitySelectText; diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index fba81306b..29edddb3a 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -3,6 +3,7 @@ using System.Collections.Generic; using System.Globalization; using System.Linq; using System.Threading; +using System.Threading.Tasks; using Microsoft.Extensions.Logging; using SQLitePCL; using SQLitePCL.pretty; @@ -12,15 +13,12 @@ namespace Emby.Server.Implementations.Data public abstract class BaseSqliteRepository : IDisposable { protected string DbFilePath { get; set; } - protected ReaderWriterLockSlim WriteLock; protected ILogger Logger { get; private set; } protected BaseSqliteRepository(ILogger logger) { Logger = logger; - - WriteLock = new ReaderWriterLockSlim(LockRecursionPolicy.NoRecursion); } protected TransactionMode TransactionMode => TransactionMode.Deferred; @@ -31,130 +29,96 @@ namespace Emby.Server.Implementations.Data static BaseSqliteRepository() { - SQLite3.EnableSharedCache = false; - - int rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MEMSTATUS, 0); - //CheckOk(rc); - - rc = raw.sqlite3_config(raw.SQLITE_CONFIG_MULTITHREAD, 1); - //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SINGLETHREAD, 1); - //rc = raw.sqlite3_config(raw.SQLITE_CONFIG_SERIALIZED, 1); - //CheckOk(rc); - - rc = raw.sqlite3_enable_shared_cache(1); - ThreadSafeMode = raw.sqlite3_threadsafe(); } private static bool _versionLogged; private string _defaultWal; - protected ManagedConnection _connection; - - protected virtual bool EnableSingleConnection => true; - protected ManagedConnection CreateConnection(bool isReadOnly = false) + protected SQLiteDatabaseConnection CreateConnection(bool isReadOnly = false) { - if (_connection != null) + if (!_versionLogged) { - return _connection; + _versionLogged = true; + Logger.LogInformation("Sqlite version: " + SQLite3.Version); + Logger.LogInformation("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions)); } - lock (WriteLock) + ConnectionFlags connectionFlags; + + if (isReadOnly) { - if (!_versionLogged) - { - _versionLogged = true; - Logger.LogInformation("Sqlite version: " + SQLite3.Version); - Logger.LogInformation("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray())); - } + //Logger.LogInformation("Opening read connection"); + //connectionFlags = ConnectionFlags.Create; + connectionFlags = ConnectionFlags.ReadOnly; + } + else + { + //Logger.LogInformation("Opening write connection"); + connectionFlags = ConnectionFlags.Create; + connectionFlags |= ConnectionFlags.ReadWrite; + } + + connectionFlags |= ConnectionFlags.SharedCached; + connectionFlags |= ConnectionFlags.FullMutex; - ConnectionFlags connectionFlags; + var db = SQLite3.Open(DbFilePath, connectionFlags, null); - if (isReadOnly) + try + { + if (string.IsNullOrWhiteSpace(_defaultWal)) { - //Logger.LogInformation("Opening read connection"); - //connectionFlags = ConnectionFlags.ReadOnly; - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; + _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); + + Logger.LogInformation("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); } - else + + var queries = new List<string> + { + //"PRAGMA cache size=-10000" + //"PRAGMA read_uncommitted = true", + //"PRAGMA synchronous=Normal" + }; + + if (CacheSize.HasValue) { - //Logger.LogInformation("Opening write connection"); - connectionFlags = ConnectionFlags.Create; - connectionFlags |= ConnectionFlags.ReadWrite; + queries.Add("PRAGMA cache_size=" + CacheSize.Value.ToString(CultureInfo.InvariantCulture)); } - if (EnableSingleConnection) + if (EnableTempStoreMemory) { - connectionFlags |= ConnectionFlags.PrivateCache; + queries.Add("PRAGMA temp_store = memory"); } else { - connectionFlags |= ConnectionFlags.SharedCached; + queries.Add("PRAGMA temp_store = file"); } - connectionFlags |= ConnectionFlags.NoMutex; - - var db = SQLite3.Open(DbFilePath, connectionFlags, null); - - try + foreach (var query in queries) { - if (string.IsNullOrWhiteSpace(_defaultWal)) - { - _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); - - Logger.LogInformation("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); - } - - var queries = new List<string> - { - //"PRAGMA cache size=-10000" - //"PRAGMA read_uncommitted = true", - "PRAGMA synchronous=Normal" - }; - - if (CacheSize.HasValue) - { - queries.Add("PRAGMA cache_size=" + CacheSize.Value.ToString(CultureInfo.InvariantCulture)); - } - - if (EnableTempStoreMemory) - { - queries.Add("PRAGMA temp_store = memory"); - } - else - { - queries.Add("PRAGMA temp_store = file"); - } - - foreach (var query in queries) - { - db.Execute(query); - } + db.Execute(query); } - catch + } + catch + { + using (db) { - using (db) - { - } - - throw; } - _connection = new ManagedConnection(db, false); - - return _connection; + throw; } + + return db; } - public IStatement PrepareStatement(ManagedConnection connection, string sql) + public IStatement PrepareStatement(SQLiteDatabaseConnection connection, string sql) { return connection.PrepareStatement(sql); } - public IStatement PrepareStatementSafe(ManagedConnection connection, string sql) + public IStatement PrepareStatementSafe(SQLiteDatabaseConnection connection, string sql) { return connection.PrepareStatement(sql); } @@ -179,7 +143,7 @@ namespace Emby.Server.Implementations.Data return sql.Select(connection.PrepareStatement).ToList(); } - protected bool TableExists(ManagedConnection connection, string name) + protected bool TableExists(SQLiteDatabaseConnection connection, string name) { return connection.RunInTransaction(db => { @@ -199,7 +163,7 @@ namespace Emby.Server.Implementations.Data }, ReadTransactionMode); } - protected void RunDefaultInitialization(ManagedConnection db) + protected void RunDefaultInitialization(SQLiteDatabaseConnection db) { var queries = new List<string> { @@ -243,7 +207,7 @@ namespace Emby.Server.Implementations.Data public void Dispose() { - _disposed = true; + Dispose(true); } @@ -255,42 +219,13 @@ namespace Emby.Server.Implementations.Data /// <param name="dispose"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param> protected virtual void Dispose(bool dispose) { - if (dispose) - { - DisposeConnection(); - } - } - - private void DisposeConnection() - { - try - { - lock (_disposeLock) - { - using (WriteLock.Write()) - { - if (_connection != null) - { - using (_connection) - { - _connection.Close(); - } - _connection = null; - } - - CloseConnection(); - } - } - } - catch (Exception ex) + if (_disposed) { - Logger.LogError(ex, "Error disposing database"); + return; } - } - protected virtual void CloseConnection() - { + _disposed = true; } protected List<string> GetColumnNames(IDatabaseConnection connection, string table) @@ -320,60 +255,4 @@ namespace Emby.Server.Implementations.Data connection.Execute("alter table " + table + " add column " + columnName + " " + type + " NULL"); } } - - public static class ReaderWriterLockSlimExtensions - { - private sealed class ReadLockToken : IDisposable - { - private ReaderWriterLockSlim _sync; - public ReadLockToken(ReaderWriterLockSlim sync) - { - _sync = sync; - sync.EnterReadLock(); - } - public void Dispose() - { - if (_sync != null) - { - _sync.ExitReadLock(); - _sync = null; - } - } - } - private sealed class WriteLockToken : IDisposable - { - private ReaderWriterLockSlim _sync; - public WriteLockToken(ReaderWriterLockSlim sync) - { - _sync = sync; - sync.EnterWriteLock(); - } - public void Dispose() - { - if (_sync != null) - { - _sync.ExitWriteLock(); - _sync = null; - } - } - } - - public static IDisposable Read(this ReaderWriterLockSlim obj) - { - //if (BaseSqliteRepository.ThreadSafeMode > 0) - //{ - // return new DummyToken(); - //} - return new WriteLockToken(obj); - } - - public static IDisposable Write(this ReaderWriterLockSlim obj) - { - //if (BaseSqliteRepository.ThreadSafeMode > 0) - //{ - // return new DummyToken(); - //} - return new WriteLockToken(obj); - } - } } diff --git a/Emby.Server.Implementations/Data/ManagedConnection.cs b/Emby.Server.Implementations/Data/ManagedConnection.cs deleted file mode 100644 index b8f1e581a..000000000 --- a/Emby.Server.Implementations/Data/ManagedConnection.cs +++ /dev/null @@ -1,79 +0,0 @@ -using System; -using System.Collections.Generic; -using SQLitePCL.pretty; - -namespace Emby.Server.Implementations.Data -{ - public class ManagedConnection : IDisposable - { - private SQLiteDatabaseConnection db; - private readonly bool _closeOnDispose; - - public ManagedConnection(SQLiteDatabaseConnection db, bool closeOnDispose) - { - this.db = db; - _closeOnDispose = closeOnDispose; - } - - public IStatement PrepareStatement(string sql) - { - return db.PrepareStatement(sql); - } - - public IEnumerable<IStatement> PrepareAll(string sql) - { - return db.PrepareAll(sql); - } - - public void ExecuteAll(string sql) - { - db.ExecuteAll(sql); - } - - public void Execute(string sql, params object[] values) - { - db.Execute(sql, values); - } - - public void RunQueries(string[] sql) - { - db.RunQueries(sql); - } - - public void RunInTransaction(Action<IDatabaseConnection> action, TransactionMode mode) - { - db.RunInTransaction(action, mode); - } - - public T RunInTransaction<T>(Func<IDatabaseConnection, T> action, TransactionMode mode) - { - return db.RunInTransaction(action, mode); - } - - public IEnumerable<IReadOnlyList<IResultSetValue>> Query(string sql) - { - return db.Query(sql); - } - - public IEnumerable<IReadOnlyList<IResultSetValue>> Query(string sql, params object[] values) - { - return db.Query(sql, values); - } - - public void Close() - { - using (db) - { - - } - } - - public void Dispose() - { - if (_closeOnDispose) - { - Close(); - } - } - } -} diff --git a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs index 47552806d..86a7c1551 100644 --- a/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteDisplayPreferencesRepository.cs @@ -98,15 +98,12 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - SaveDisplayPreferences(displayPreferences, userId, client, db); - }, TransactionMode); - } + SaveDisplayPreferences(displayPreferences, userId, client, db); + }, TransactionMode); } } @@ -142,18 +139,15 @@ namespace Emby.Server.Implementations.Data cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + foreach (var displayPreference in displayPreferences) { - foreach (var displayPreference in displayPreferences) - { - SaveDisplayPreferences(displayPreference, userId, displayPreference.Client, db); - } - }, TransactionMode); - } + SaveDisplayPreferences(displayPreference, userId, displayPreference.Client, db); + } + }, TransactionMode); } } @@ -174,27 +168,24 @@ namespace Emby.Server.Implementations.Data var guidId = displayPreferencesId.GetMD5(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) { - using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where id = @id and userId=@userId and client=@client")) - { - statement.TryBind("@id", guidId.ToGuidBlob()); - statement.TryBind("@userId", userId.ToGuidBlob()); - statement.TryBind("@client", client); - - foreach (var row in statement.ExecuteQuery()) - { - return Get(row); - } - } + statement.TryBind("@id", guidId.ToGuidBlob()); + statement.TryBind("@userId", userId.ToGuidBlob()); + statement.TryBind("@client", client); - return new DisplayPreferences + foreach (var row in statement.ExecuteQuery()) { - Id = guidId.ToString("N") - }; + return Get(row); + } } + + return new DisplayPreferences + { + Id = guidId.ToString("N") + }; } } @@ -208,18 +199,15 @@ namespace Emby.Server.Implementations.Data { var list = new List<DisplayPreferences>(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) { - using (var statement = connection.PrepareStatement("select data from userdisplaypreferences where userId=@userId")) - { - statement.TryBind("@userId", userId.ToGuidBlob()); + statement.TryBind("@userId", userId.ToGuidBlob()); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(Get(row)); - } + 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 a486cb1a0..c0f27b25a 100644 --- a/Emby.Server.Implementations/Data/SqliteExtensions.cs +++ b/Emby.Server.Implementations/Data/SqliteExtensions.cs @@ -141,7 +141,7 @@ namespace Emby.Server.Implementations.Data } } - public static void Attach(ManagedConnection db, string path, string alias) + public static void Attach(SQLiteDatabaseConnection db, string path, string alias) { var commandText = string.Format("attach @path as {0};", alias); diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 8841a9a50..baa5b713a 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -319,7 +319,7 @@ namespace Emby.Server.Implementations.Data connection.RunQueries(postQueries); } - userDataRepo.Initialize(WriteLock, _connection, userManager); + userDataRepo.Initialize(userManager); } private static readonly string[] _retriveItemColumns = @@ -551,21 +551,18 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var saveImagesStatement = PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) { - using (var saveImagesStatement = PrepareStatement(db, "Update TypedBaseItems set Images=@Images where guid=@Id")) - { - saveImagesStatement.TryBind("@Id", item.Id.ToGuidBlob()); - saveImagesStatement.TryBind("@Images", SerializeImages(item)); + saveImagesStatement.TryBind("@Id", item.Id.ToGuidBlob()); + saveImagesStatement.TryBind("@Images", SerializeImages(item)); - saveImagesStatement.MoveNext(); - } - }, TransactionMode); - } + saveImagesStatement.MoveNext(); + } + }, TransactionMode); } } @@ -605,16 +602,12 @@ namespace Emby.Server.Implementations.Data tuples.Add((item, ancestorIds, topParent, userdataKey, inheritedTags)); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - SaveItemsInTranscation(db, tuples); - - }, TransactionMode); - } + SaveItemsInTranscation(db, tuples); + }, TransactionMode); } } @@ -1193,23 +1186,20 @@ namespace Emby.Server.Implementations.Data CheckDisposed(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatementSafe(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) { - using (var statement = PrepareStatementSafe(connection, "select " + string.Join(",", _retriveItemColumns) + " from TypedBaseItems where guid = @guid")) - { - statement.TryBind("@guid", id); + statement.TryBind("@guid", id); - foreach (var row in statement.ExecuteQuery()) - { - return GetItem(row, new InternalItemsQuery()); - } + foreach (var row in statement.ExecuteQuery()) + { + return GetItem(row, new InternalItemsQuery()); } - - return null; } } + + return null; } private bool TypeRequiresDeserialization(Type type) @@ -1909,24 +1899,21 @@ namespace Emby.Server.Implementations.Data { CheckDisposed(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + var list = new List<ChapterInfo>(); + + using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) { - var list = new List<ChapterInfo>(); + statement.TryBind("@ItemId", item.Id); - using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc")) + foreach (var row in statement.ExecuteQuery()) { - statement.TryBind("@ItemId", item.Id); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetChapter(row, item)); - } + list.Add(GetChapter(row, item)); } - - return list; } + + return list; } } @@ -1941,22 +1928,20 @@ namespace Emby.Server.Implementations.Data { CheckDisposed(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) { - using (var statement = PrepareStatementSafe(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex")) - { - statement.TryBind("@ItemId", item.Id); - statement.TryBind("@ChapterIndex", index); + statement.TryBind("@ItemId", item.Id); + statement.TryBind("@ChapterIndex", index); - foreach (var row in statement.ExecuteQuery()) - { - return GetChapter(row, item); - } + foreach (var row in statement.ExecuteQuery()) + { + return GetChapter(row, item); } } } + return null; } @@ -2012,21 +1997,18 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException(nameof(chapters)); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var idBlob = id.ToGuidBlob(); + var idBlob = id.ToGuidBlob(); // First delete chapters db.Execute("delete from " + ChaptersTableName + " where ItemId=@ItemId", idBlob); - InsertChapters(idBlob, chapters, db); + InsertChapters(idBlob, chapters, db); - }, TransactionMode); - } + }, TransactionMode); } } @@ -2551,29 +2533,25 @@ namespace Emby.Server.Implementations.Data commandText += " where " + string.Join(" AND ", whereClauses); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatementSafe(connection, commandText)) { - using (var statement = PrepareStatementSafe(connection, commandText)) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - var count = statement.ExecuteQuery().SelectScalarInt().First(); - LogQueryTime("GetCount", commandText, now); - return count; - } + var count = statement.ExecuteQuery().SelectScalarInt().First(); + LogQueryTime("GetCount", commandText, now); + return count; } - } } @@ -2624,67 +2602,64 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) - { - var list = new List<BaseItem>(); + var list = new List<BaseItem>(); - using (var statement = PrepareStatementSafe(connection, commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) + { + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - 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); + 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()) + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item != null) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) - { - list.Add(item); - } + list.Add(item); } } + } - // Hack for right now since we currently don't support filtering out these duplicates within a query - if (query.EnableGroupByMetadataKey) + // Hack for right now since we currently don't support filtering out these duplicates within a query + if (query.EnableGroupByMetadataKey) + { + var limit = query.Limit ?? int.MaxValue; + limit -= 4; + var newList = new List<BaseItem>(); + + foreach (var item in list) { - var limit = query.Limit ?? int.MaxValue; - limit -= 4; - var newList = new List<BaseItem>(); + AddItem(newList, item); - foreach (var item in list) + if (newList.Count >= limit) { - AddItem(newList, item); - - if (newList.Count >= limit) - { - break; - } + break; } - - list = newList; } - LogQueryTime("GetItemList", commandText, now); - - return list; + list = newList; } + + LogQueryTime("GetItemList", commandText, now); + + return list; } } @@ -2845,75 +2820,72 @@ namespace Emby.Server.Implementations.Data statementTexts.Add(commandText); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => - { - var result = new QueryResult<BaseItem>(); - var statements = PrepareAllSafe(db, statementTexts); + var result = new QueryResult<BaseItem>(); + var statements = PrepareAllSafe(db, statementTexts); - if (!isReturningZeroItems) + if (!isReturningZeroItems) + { + using (var statement = statements[0]) { - using (var statement = statements[0]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - 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); + 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()) + foreach (var row in statement.ExecuteQuery()) + { + var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); + if (item != null) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) - { - list.Add(item); - } + list.Add(item); } } } + } - if (query.EnableTotalRecordCount) + if (query.EnableTotalRecordCount) + { + using (var statement = statements[statements.Count - 1]) { - using (var statement = statements[statements.Count - 1]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); } + } - LogQueryTime("GetItems", commandText, now); + LogQueryTime("GetItems", commandText, now); - result.Items = list.ToArray(); - return result; + result.Items = list.ToArray(); + return result; - }, ReadTransactionMode); - } + }, ReadTransactionMode); } } @@ -3080,35 +3052,32 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) - { - var list = new List<Guid>(); + var list = new List<Guid>(); - using (var statement = PrepareStatementSafe(connection, commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) + { + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row[0].ReadGuidFromBlob()); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row[0].ReadGuidFromBlob()); } + } - LogQueryTime("GetItemList", commandText, now); + LogQueryTime("GetItemList", commandText, now); - return list; - } + return list; } } @@ -3149,39 +3118,36 @@ namespace Emby.Server.Implementations.Data } } - using (WriteLock.Read()) + var list = new List<Tuple<Guid, string>>(); + using (var connection = CreateConnection(true)) { - var list = new List<Tuple<Guid, string>>(); - using (var connection = CreateConnection(true)) + using (var statement = PrepareStatementSafe(connection, commandText)) { - using (var statement = PrepareStatementSafe(connection, commandText)) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - // Running this again will bind the params - GetWhereClauses(query, statement); + // Running this again will bind the params + GetWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - var id = row.GetGuid(0); - string path = null; + foreach (var row in statement.ExecuteQuery()) + { + var id = row.GetGuid(0); + string path = null; - if (!row.IsDBNull(1)) - { - path = row.GetString(1); - } - list.Add(new Tuple<Guid, string>(id, path)); + if (!row.IsDBNull(1)) + { + path = row.GetString(1); } + list.Add(new Tuple<Guid, string>(id, path)); } } + } - LogQueryTime("GetItemIdsWithPath", commandText, now); + LogQueryTime("GetItemIdsWithPath", commandText, now); - return list; - } + return list; } public QueryResult<Guid> GetItemIds(InternalItemsQuery query) @@ -3265,64 +3231,61 @@ namespace Emby.Server.Implementations.Data statementTexts.Add(commandText); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => - { - var result = new QueryResult<Guid>(); + var result = new QueryResult<Guid>(); - var statements = PrepareAllSafe(db, statementTexts); + var statements = PrepareAllSafe(db, statementTexts); - if (!isReturningZeroItems) + if (!isReturningZeroItems) + { + using (var statement = statements[0]) { - using (var statement = statements[0]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row[0].ReadGuidFromBlob()); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row[0].ReadGuidFromBlob()); } } + } - if (query.EnableTotalRecordCount) + if (query.EnableTotalRecordCount) + { + using (var statement = statements[statements.Count - 1]) { - using (var statement = statements[statements.Count - 1]) + if (EnableJoinUserData(query)) { - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + 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); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - } + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); } + } - LogQueryTime("GetItemIds", commandText, now); + LogQueryTime("GetItemIds", commandText, now); - result.Items = list.ToArray(); - return result; + result.Items = list.ToArray(); + return result; - }, ReadTransactionMode); - } + }, ReadTransactionMode); } } @@ -4899,14 +4862,12 @@ namespace Emby.Server.Implementations.Data private void UpdateInheritedTags(CancellationToken cancellationToken) { - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + connection.ExecuteAll(string.Join(";", new string[] { - connection.ExecuteAll(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", @@ -4916,10 +4877,9 @@ FROM AncestorIds LEFT JOIN ItemValues ON (AncestorIds.AncestorId = ItemValues.ItemId) where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type = 4 " - })); + })); - }, TransactionMode); - } + }, TransactionMode); } } @@ -4965,13 +4925,11 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var idBlob = id.ToGuidBlob(); + var idBlob = id.ToGuidBlob(); // Delete people ExecuteWithSingleParam(db, "delete from People where ItemId=@Id", idBlob); @@ -4990,8 +4948,7 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type // Delete the item ExecuteWithSingleParam(db, "delete from TypedBaseItems where guid=@Id", idBlob); - }, TransactionMode); - } + }, TransactionMode); } } @@ -5025,23 +4982,20 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += " order by ListOrder"; - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + var list = new List<string>(); + using (var statement = PrepareStatementSafe(connection, commandText)) { - var list = new List<string>(); - using (var statement = PrepareStatementSafe(connection, commandText)) - { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(row.GetString(0)); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(row.GetString(0)); } - return list; } + return list; } } @@ -5065,25 +5019,22 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += " order by ListOrder"; - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + var list = new List<PersonInfo>(); + + using (var statement = PrepareStatementSafe(connection, commandText)) { - var list = new List<PersonInfo>(); + // Run this again to bind the params + GetPeopleWhereClauses(query, statement); - using (var statement = PrepareStatementSafe(connection, commandText)) + foreach (var row in statement.ExecuteQuery()) { - // Run this again to bind the params - GetPeopleWhereClauses(query, statement); - - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetPerson(row)); - } + list.Add(GetPerson(row)); } - - return list; } + + return list; } } @@ -5294,27 +5245,24 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type commandText += " Group By CleanValue"; - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) - { - var list = new List<string>(); + var list = new List<string>(); - using (var statement = PrepareStatementSafe(connection, commandText)) + using (var statement = PrepareStatementSafe(connection, commandText)) + { + foreach (var row in statement.ExecuteQuery()) { - foreach (var row in statement.ExecuteQuery()) + if (!row.IsDBNull(0)) { - if (!row.IsDBNull(0)) - { - list.Add(row.GetString(0)); - } + list.Add(row.GetString(0)); } } + } - LogQueryTime("GetItemValueNames", commandText, now); + LogQueryTime("GetItemValueNames", commandText, now); - return list; - } + return list; } } @@ -5483,100 +5431,97 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type statementTexts.Add(countText); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => - { - var list = new List<(BaseItem, ItemCounts)>(); - var result = new QueryResult<(BaseItem, ItemCounts)>(); + var list = new List<(BaseItem, ItemCounts)>(); + var result = new QueryResult<(BaseItem, ItemCounts)>(); - var statements = PrepareAllSafe(db, statementTexts); + var statements = PrepareAllSafe(db, statementTexts); - if (!isReturningZeroItems) + if (!isReturningZeroItems) + { + using (var statement = statements[0]) { - using (var statement = statements[0]) + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - if (typeSubQuery != 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()) + if (typeSubQuery != 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 != null) { - var item = GetItem(row, query, hasProgramAttributes, hasEpisodeAttributes, hasServiceName, hasStartDate, hasTrailerTypes, hasArtistFields, hasSeriesFields); - if (item != null) - { - var countStartColumn = columns.Count - 1; + var countStartColumn = columns.Count - 1; - list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); - } + list.Add((item, GetItemCounts(row, countStartColumn, typesToCount))); } - - LogQueryTime("GetItemValues", commandText, now); } + + LogQueryTime("GetItemValues", commandText, now); } + } - if (query.EnableTotalRecordCount) - { - commandText = "select " - + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) - + GetFromText() - + GetJoinUserDataText(query) - + whereText; + if (query.EnableTotalRecordCount) + { + commandText = "select " + + string.Join(",", GetFinalColumnsToSelect(query, new[] { "count (distinct PresentationUniqueKey)" })) + + GetFromText() + + GetJoinUserDataText(query) + + whereText; - using (var statement = statements[statements.Count - 1]) + using (var statement = statements[statements.Count - 1]) + { + statement.TryBind("@SelectType", returnType); + if (EnableJoinUserData(query)) { - statement.TryBind("@SelectType", returnType); - if (EnableJoinUserData(query)) - { - statement.TryBind("@UserId", query.User.InternalId); - } + statement.TryBind("@UserId", query.User.InternalId); + } - if (typeSubQuery != null) - { - GetWhereClauses(typeSubQuery, null); - } - BindSimilarParams(query, statement); - BindSearchParams(query, statement); - GetWhereClauses(innerQuery, statement); - GetWhereClauses(outerQuery, statement); + if (typeSubQuery != null) + { + GetWhereClauses(typeSubQuery, null); + } + BindSimilarParams(query, statement); + BindSearchParams(query, statement); + GetWhereClauses(innerQuery, statement); + GetWhereClauses(outerQuery, statement); - result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); + result.TotalRecordCount = statement.ExecuteQuery().SelectScalarInt().First(); - LogQueryTime("GetItemValues", commandText, now); - } + LogQueryTime("GetItemValues", commandText, now); } + } - if (result.TotalRecordCount == 0) - { - result.TotalRecordCount = list.Count; - } - result.Items = list.ToArray(); + if (result.TotalRecordCount == 0) + { + result.TotalRecordCount = list.Count; + } + result.Items = list.ToArray(); - return result; + return result; - }, ReadTransactionMode); - } + }, ReadTransactionMode); } } @@ -5753,22 +5698,18 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type CheckDisposed(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var itemIdBlob = itemId.ToGuidBlob(); + var itemIdBlob = itemId.ToGuidBlob(); // First delete chapters db.Execute("delete from People where ItemId=@ItemId", itemIdBlob); - InsertPeople(itemIdBlob, people, db); - - }, TransactionMode); + InsertPeople(itemIdBlob, people, db); - } + }, TransactionMode); } } @@ -5874,34 +5815,31 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type cmdText += " order by StreamIndex ASC"; - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + var list = new List<MediaStream>(); + + using (var statement = PrepareStatementSafe(connection, cmdText)) { - var list = new List<MediaStream>(); + statement.TryBind("@ItemId", query.ItemId.ToGuidBlob()); - using (var statement = PrepareStatementSafe(connection, cmdText)) + if (query.Type.HasValue) { - statement.TryBind("@ItemId", query.ItemId.ToGuidBlob()); - - if (query.Type.HasValue) - { - statement.TryBind("@StreamType", query.Type.Value.ToString()); - } - - if (query.Index.HasValue) - { - statement.TryBind("@StreamIndex", query.Index.Value); - } + statement.TryBind("@StreamType", query.Type.Value.ToString()); + } - foreach (var row in statement.ExecuteQuery()) - { - list.Add(GetMediaStream(row)); - } + if (query.Index.HasValue) + { + statement.TryBind("@StreamIndex", query.Index.Value); } - return list; + foreach (var row in statement.ExecuteQuery()) + { + list.Add(GetMediaStream(row)); + } } + + return list; } } @@ -5921,21 +5859,18 @@ where AncestorIdText not null and ItemValues.Value not null and ItemValues.Type cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - var itemIdBlob = id.ToGuidBlob(); + var itemIdBlob = id.ToGuidBlob(); // First delete chapters db.Execute("delete from mediastreams where ItemId=@ItemId", itemIdBlob); - InsertMediaStreams(itemIdBlob, streams, db); + InsertMediaStreams(itemIdBlob, streams, db); - }, TransactionMode); - } + }, TransactionMode); } } diff --git a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs index 4109b7ad1..f544dfac4 100644 --- a/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserDataRepository.cs @@ -7,7 +7,6 @@ using MediaBrowser.Common.Configuration; using MediaBrowser.Controller.Entities; using MediaBrowser.Controller.Library; using MediaBrowser.Controller.Persistence; -using MediaBrowser.Model.IO; using Microsoft.Extensions.Logging; using SQLitePCL.pretty; @@ -33,13 +32,8 @@ namespace Emby.Server.Implementations.Data /// Opens the connection to the database /// </summary> /// <returns>Task.</returns> - public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection, IUserManager userManager) + public void Initialize(IUserManager userManager) { - _connection = managedConnection; - - WriteLock.Dispose(); - WriteLock = writeLock; - using (var connection = CreateConnection()) { var userDatasTableExists = TableExists(connection, "UserDatas"); @@ -178,15 +172,12 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => - { - SaveUserData(db, internalUserId, key, userData); - }, TransactionMode); - } + SaveUserData(db, internalUserId, key, userData); + }, TransactionMode); } } @@ -249,18 +240,15 @@ namespace Emby.Server.Implementations.Data { cancellationToken.ThrowIfCancellationRequested(); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + foreach (var userItemData in userDataList) { - foreach (var userItemData in userDataList) - { - SaveUserData(db, internalUserId, userItemData.Key, userItemData); - } - }, TransactionMode); - } + SaveUserData(db, internalUserId, userItemData.Key, userItemData); + } + }, TransactionMode); } } @@ -281,28 +269,26 @@ namespace Emby.Server.Implementations.Data { throw new ArgumentNullException(nameof(internalUserId)); } + if (string.IsNullOrEmpty(key)) { throw new ArgumentNullException(nameof(key)); } - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where key =@Key and userId=@UserId")) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where key =@Key and userId=@UserId")) - { - statement.TryBind("@UserId", internalUserId); - statement.TryBind("@Key", key); + statement.TryBind("@UserId", internalUserId); + statement.TryBind("@Key", key); - foreach (var row in statement.ExecuteQuery()) - { - return ReadRow(row); - } + foreach (var row in statement.ExecuteQuery()) + { + return ReadRow(row); } - - return null; } + + return null; } } @@ -335,18 +321,15 @@ namespace Emby.Server.Implementations.Data var list = new List<UserItemData>(); - using (WriteLock.Read()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where userId=@UserId")) { - using (var statement = connection.PrepareStatement("select key,userid,rating,played,playCount,isFavorite,playbackPositionTicks,lastPlayedDate,AudioStreamIndex,SubtitleStreamIndex from UserDatas where userId=@UserId")) - { - statement.TryBind("@UserId", internalUserId); + statement.TryBind("@UserId", internalUserId); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(ReadRow(row)); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(ReadRow(row)); } } } @@ -397,10 +380,5 @@ namespace Emby.Server.Implementations.Data { // handled by library database } - - protected override void CloseConnection() - { - // handled by library database - } } } diff --git a/Emby.Server.Implementations/Data/SqliteUserRepository.cs b/Emby.Server.Implementations/Data/SqliteUserRepository.cs index 5957b2903..785452ad3 100644 --- a/Emby.Server.Implementations/Data/SqliteUserRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteUserRepository.cs @@ -60,7 +60,7 @@ namespace Emby.Server.Implementations.Data } } - private void TryMigrateToLocalUsersTable(ManagedConnection connection) + private void TryMigrateToLocalUsersTable(SQLiteDatabaseConnection connection) { try { @@ -119,31 +119,28 @@ namespace Emby.Server.Implementations.Data var serialized = _jsonSerializer.SerializeToBytes(user); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("insert into LocalUsersv2 (guid, data) values (@guid, @data)")) { - using (var statement = db.PrepareStatement("insert into LocalUsersv2 (guid, data) values (@guid, @data)")) - { - statement.TryBind("@guid", user.Id.ToGuidBlob()); - statement.TryBind("@data", serialized); + statement.TryBind("@guid", user.Id.ToGuidBlob()); + statement.TryBind("@data", serialized); - statement.MoveNext(); - } + statement.MoveNext(); + } - var createdUser = GetUser(user.Id, false); + var createdUser = GetUser(user.Id, connection); - if (createdUser == null) - { - throw new ApplicationException("created user should never be null"); - } + if (createdUser == null) + { + throw new ApplicationException("created user should never be null"); + } - user.InternalId = createdUser.InternalId; + user.InternalId = createdUser.InternalId; - }, TransactionMode); - } + }, TransactionMode); } } @@ -156,39 +153,30 @@ namespace Emby.Server.Implementations.Data var serialized = _jsonSerializer.SerializeToBytes(user); - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("update LocalUsersv2 set data=@data where Id=@InternalId")) { - using (var statement = db.PrepareStatement("update LocalUsersv2 set data=@data where Id=@InternalId")) - { - statement.TryBind("@InternalId", user.InternalId); - statement.TryBind("@data", serialized); - statement.MoveNext(); - } + statement.TryBind("@InternalId", user.InternalId); + statement.TryBind("@data", serialized); + statement.MoveNext(); + } - }, TransactionMode); - } + }, TransactionMode); } } - private User GetUser(Guid guid, bool openLock) + private User GetUser(Guid guid, SQLiteDatabaseConnection connection) { - using (openLock ? WriteLock.Read() : null) + using (var statement = connection.PrepareStatement("select id,guid,data from LocalUsersv2 where guid=@guid")) { - using (var connection = CreateConnection(true)) - { - using (var statement = connection.PrepareStatement("select id,guid,data from LocalUsersv2 where guid=@guid")) - { - statement.TryBind("@guid", guid); + statement.TryBind("@guid", guid); - foreach (var row in statement.ExecuteQuery()) - { - return GetUser(row); - } - } + foreach (var row in statement.ExecuteQuery()) + { + return GetUser(row); } } @@ -218,14 +206,11 @@ namespace Emby.Server.Implementations.Data { var list = new List<User>(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + foreach (var row in connection.Query("select id,guid,data from LocalUsersv2")) { - foreach (var row in connection.Query("select id,guid,data from LocalUsersv2")) - { - list.Add(GetUser(row)); - } + list.Add(GetUser(row)); } } @@ -245,19 +230,16 @@ namespace Emby.Server.Implementations.Data throw new ArgumentNullException(nameof(user)); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("delete from LocalUsersv2 where Id=@id")) { - using (var statement = db.PrepareStatement("delete from LocalUsersv2 where Id=@id")) - { - statement.TryBind("@id", user.InternalId); - statement.MoveNext(); - } - }, TransactionMode); - } + statement.TryBind("@id", user.InternalId); + statement.MoveNext(); + } + }, TransactionMode); } } } diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs index 29b8dfd3d..abc23239e 100644 --- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs +++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs @@ -48,7 +48,7 @@ namespace Emby.Server.Implementations.Security } } - private void TryMigrate(ManagedConnection connection, bool tableNewlyCreated) + private void TryMigrate(SQLiteDatabaseConnection connection, bool tableNewlyCreated) { try { @@ -87,31 +87,28 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException(nameof(info)); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("insert into Tokens (AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, UserName, IsActive, DateCreated, DateLastActivity) values (@AccessToken, @DeviceId, @AppName, @AppVersion, @DeviceName, @UserId, @UserName, @IsActive, @DateCreated, @DateLastActivity)")) { - using (var statement = db.PrepareStatement("insert into Tokens (AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, UserName, IsActive, DateCreated, DateLastActivity) values (@AccessToken, @DeviceId, @AppName, @AppVersion, @DeviceName, @UserId, @UserName, @IsActive, @DateCreated, @DateLastActivity)")) - { - statement.TryBind("@AccessToken", info.AccessToken); - - statement.TryBind("@DeviceId", info.DeviceId); - statement.TryBind("@AppName", info.AppName); - statement.TryBind("@AppVersion", info.AppVersion); - statement.TryBind("@DeviceName", info.DeviceName); - statement.TryBind("@UserId", (info.UserId.Equals(Guid.Empty) ? null : info.UserId.ToString("N"))); - statement.TryBind("@UserName", info.UserName); - statement.TryBind("@IsActive", true); - statement.TryBind("@DateCreated", info.DateCreated.ToDateTimeParamValue()); - statement.TryBind("@DateLastActivity", info.DateLastActivity.ToDateTimeParamValue()); - - statement.MoveNext(); - } - - }, TransactionMode); - } + statement.TryBind("@AccessToken", info.AccessToken); + + statement.TryBind("@DeviceId", info.DeviceId); + statement.TryBind("@AppName", info.AppName); + statement.TryBind("@AppVersion", info.AppVersion); + statement.TryBind("@DeviceName", info.DeviceName); + statement.TryBind("@UserId", (info.UserId.Equals(Guid.Empty) ? null : info.UserId.ToString("N"))); + statement.TryBind("@UserName", info.UserName); + statement.TryBind("@IsActive", true); + statement.TryBind("@DateCreated", info.DateCreated.ToDateTimeParamValue()); + statement.TryBind("@DateLastActivity", info.DateLastActivity.ToDateTimeParamValue()); + + statement.MoveNext(); + } + + }, TransactionMode); } } @@ -122,31 +119,28 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException(nameof(info)); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("Update Tokens set AccessToken=@AccessToken, DeviceId=@DeviceId, AppName=@AppName, AppVersion=@AppVersion, DeviceName=@DeviceName, UserId=@UserId, UserName=@UserName, DateCreated=@DateCreated, DateLastActivity=@DateLastActivity where Id=@Id")) { - using (var statement = db.PrepareStatement("Update Tokens set AccessToken=@AccessToken, DeviceId=@DeviceId, AppName=@AppName, AppVersion=@AppVersion, DeviceName=@DeviceName, UserId=@UserId, UserName=@UserName, DateCreated=@DateCreated, DateLastActivity=@DateLastActivity where Id=@Id")) - { - statement.TryBind("@Id", info.Id); - - statement.TryBind("@AccessToken", info.AccessToken); - - statement.TryBind("@DeviceId", info.DeviceId); - statement.TryBind("@AppName", info.AppName); - statement.TryBind("@AppVersion", info.AppVersion); - statement.TryBind("@DeviceName", info.DeviceName); - statement.TryBind("@UserId", (info.UserId.Equals(Guid.Empty) ? null : info.UserId.ToString("N"))); - statement.TryBind("@UserName", info.UserName); - statement.TryBind("@DateCreated", info.DateCreated.ToDateTimeParamValue()); - statement.TryBind("@DateLastActivity", info.DateLastActivity.ToDateTimeParamValue()); - - statement.MoveNext(); - } - }, TransactionMode); - } + statement.TryBind("@Id", info.Id); + + statement.TryBind("@AccessToken", info.AccessToken); + + statement.TryBind("@DeviceId", info.DeviceId); + statement.TryBind("@AppName", info.AppName); + statement.TryBind("@AppVersion", info.AppVersion); + statement.TryBind("@DeviceName", info.DeviceName); + statement.TryBind("@UserId", (info.UserId.Equals(Guid.Empty) ? null : info.UserId.ToString("N"))); + statement.TryBind("@UserName", info.UserName); + statement.TryBind("@DateCreated", info.DateCreated.ToDateTimeParamValue()); + statement.TryBind("@DateLastActivity", info.DateLastActivity.ToDateTimeParamValue()); + + statement.MoveNext(); + } + }, TransactionMode); } } @@ -157,20 +151,17 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException(nameof(info)); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("Delete from Tokens where Id=@Id")) { - using (var statement = db.PrepareStatement("Delete from Tokens where Id=@Id")) - { - statement.TryBind("@Id", info.Id); + statement.TryBind("@Id", info.Id); - statement.MoveNext(); - } - }, TransactionMode); - } + statement.MoveNext(); + } + }, TransactionMode); } } @@ -257,45 +248,42 @@ namespace Emby.Server.Implementations.Security var list = new List<AuthenticationInfo>(); - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => - { - var result = new QueryResult<AuthenticationInfo>(); + var result = new QueryResult<AuthenticationInfo>(); - var statementTexts = new List<string>(); - statementTexts.Add(commandText); - statementTexts.Add("select count (Id) from Tokens" + whereTextWithoutPaging); + var statementTexts = new List<string>(); + statementTexts.Add(commandText); + statementTexts.Add("select count (Id) from Tokens" + whereTextWithoutPaging); - var statements = PrepareAllSafe(db, statementTexts) - .ToList(); + var statements = PrepareAllSafe(db, statementTexts) + .ToList(); - using (var statement = statements[0]) - { - BindAuthenticationQueryParams(query, statement); + using (var statement = statements[0]) + { + BindAuthenticationQueryParams(query, statement); - foreach (var row in statement.ExecuteQuery()) - { - list.Add(Get(row)); - } + foreach (var row in statement.ExecuteQuery()) + { + list.Add(Get(row)); + } - using (var totalCountStatement = statements[1]) - { - BindAuthenticationQueryParams(query, totalCountStatement); + using (var totalCountStatement = statements[1]) + { + BindAuthenticationQueryParams(query, totalCountStatement); - result.TotalRecordCount = totalCountStatement.ExecuteQuery() - .SelectScalarInt() - .First(); - } + result.TotalRecordCount = totalCountStatement.ExecuteQuery() + .SelectScalarInt() + .First(); } + } - result.Items = list.ToArray(); - return result; + result.Items = list.ToArray(); + return result; - }, ReadTransactionMode); - } + }, ReadTransactionMode); } } @@ -358,31 +346,28 @@ namespace Emby.Server.Implementations.Security public DeviceOptions GetDeviceOptions(string deviceId) { - using (WriteLock.Read()) + using (var connection = CreateConnection(true)) { - using (var connection = CreateConnection(true)) + return connection.RunInTransaction(db => { - return connection.RunInTransaction(db => + using (var statement = PrepareStatementSafe(db, "select CustomName from Devices where Id=@DeviceId")) { - using (var statement = PrepareStatementSafe(db, "select CustomName from Devices where Id=@DeviceId")) - { - statement.TryBind("@DeviceId", deviceId); + statement.TryBind("@DeviceId", deviceId); - var result = new DeviceOptions(); + var result = new DeviceOptions(); - foreach (var row in statement.ExecuteQuery()) + foreach (var row in statement.ExecuteQuery()) + { + if (row[0].SQLiteType != SQLiteType.Null) { - if (row[0].SQLiteType != SQLiteType.Null) - { - result.CustomName = row[0].ToString(); - } + result.CustomName = row[0].ToString(); } - - return result; } - }, ReadTransactionMode); - } + return result; + } + + }, ReadTransactionMode); } } @@ -393,30 +378,27 @@ namespace Emby.Server.Implementations.Security throw new ArgumentNullException(nameof(options)); } - using (WriteLock.Write()) + using (var connection = CreateConnection()) { - using (var connection = CreateConnection()) + connection.RunInTransaction(db => { - connection.RunInTransaction(db => + using (var statement = db.PrepareStatement("replace into devices (Id, CustomName, Capabilities) VALUES (@Id, @CustomName, (Select Capabilities from Devices where Id=@Id))")) { - using (var statement = db.PrepareStatement("replace into devices (Id, CustomName, Capabilities) VALUES (@Id, @CustomName, (Select Capabilities from Devices where Id=@Id))")) - { - statement.TryBind("@Id", deviceId); - - if (string.IsNullOrWhiteSpace(options.CustomName)) - { - statement.TryBindNull("@CustomName"); - } - else - { - statement.TryBind("@CustomName", options.CustomName); - } + statement.TryBind("@Id", deviceId); - statement.MoveNext(); + if (string.IsNullOrWhiteSpace(options.CustomName)) + { + statement.TryBindNull("@CustomName"); + } + else + { + statement.TryBind("@CustomName", options.CustomName); } - }, TransactionMode); - } + statement.MoveNext(); + } + + }, TransactionMode); } } } |